a****o
发帖数: 37 | 1 Well, if you are sure that courseCode is unique in
enrollment and course,then use count(*) and group by would
be efficient.
But sometimes we may have one student enrolled in different
section, so that is not always true.
Following query seems clumsy, but it works. Any better way?
select s.name from student inner join enrollment e
on e.sid = s.sid where
not exists (select * from course where coursecode not
in(select coursecode from enrollment where sid = e.sid)) |
|
s*****g
发帖数: 17 | 2 We have three tables as follow;
STUDENT(SID,Name,Major,Grade,age)
ENROLLMENT(SID,CourseCode,Marks)
COURSE(CourseCode,Time,RoomNumber)
What are the SQL statements for finding the names of
students who are taking all courses?
Thanks! |
|
s*****g
发帖数: 17 | 3 I change ur query a little bit and it works on my machines now.:)
select s.name
from student s,enrollment e
where s.sid=e.sid
and not exists
(select *
from course c
where c.coursecode not in
(select coursecode
from enrollment e
where s.sid=e.sid))
But still, I don't understnad the logic behind it.:( Would you
please explain it? |
|
a****o
发帖数: 37 | 4 Yeah. that's stupid.
Should be:
select s.name from student s
where
not exists (select * from course where coursecode not
in(select coursecode from enrollment e where s.sid = e.sid)) |
|
s*****g
发帖数: 17 | 5 oops! sorry about it, as I changed the Q a little bit. :P
But I think it is pretty similar. You can replace class with course
and ClassName with CourseCode.:))) |
|
s*****g
发帖数: 17 | 6
CourseCode is unique in COURSE, but not in ENROLLMENT.
Exactly!
Unfortunatley, it doesn't work at all on my machine.:( May I know
logic behind the query?
Thanks! |
|
