Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    42

    Unanswered: how to write sql?

    a table table_course has three columns:student_num,course_num,grade.
    how to finish the sql to select the student_num,he (she) at least learns 'maths' 'law' two course??????????

  2. #2
    Join Date
    Sep 2003
    Posts
    17
    Hi,

    If you just want the student_num for whose students which attend(ed) two or more courses, you can use:

    1> select * from courses
    2> go
    student_num course_num grade
    ----------- ----------- -----------
    1 22 2
    1 33 3
    2 22 2
    2 33 2
    10 44 2

    (5 rows affected)
    1> select student_num
    2> from courses
    3> group by student_num
    4> having count(*) >= 2
    5> go
    student_num
    -----------
    1
    2

    (2 rows affected)

    If you like to specify two courses (you could use variables) you can try:

    1> select student_num
    2> from courses c1
    3> where c1.course_num = 22
    4> and exists (select * from courses c2 where c2.student_num = c1.student_num and c2.course_num = 33)
    5>
    6> go
    student_num
    -----------
    1
    2

    Regards,
    Ulrike

  3. #3
    Join Date
    Nov 2003
    Posts
    42
    many thanks,i don not know the concepts of alias, the alias is great

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •