One Q for DB expert - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

One Q for DB expert

One Q for DB expert# Database - 数据库

s*g2000-11-07 08:11

1 楼

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*g2000-11-07 08:11

2 楼

No, it doesn't work, though u r pretty close to it.:)

s*g2000-11-07 08:11

3 楼

The right statements should be:
select student.name
from student
where exists
(select *
from enrollment
where student.sid=enrollment.studentnumber
group by sid
having count(enrollment.classname)in
(select count(class.name)
from class))
Thanks! BTW, I figured it out after I posted the Q. So it is
not a hole definitely.:)))

【在 s*****g 的大作中提到】

: No, it doesn't work, though u r pretty close to it.:)

a*o2000-11-07 08:11

4 楼

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*g2000-11-07 08:11

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*g2000-11-07 08:11

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!

【在 a****o 的大作中提到】

: 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*g2000-11-07 08:11

7 楼

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 的大作中提到】

a*o2000-11-07 08:11

8 楼

Are you using Oracle? My code is Ansi sql, Oracle does not
support inner join.
the logic is if for each sid, if there's a courseid in
course table that is not in enrollment for this sid, then we
don't select it. the join to student is trivial though.
Clear?

【在 s*****g 的大作中提到】

: 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

s*g2000-11-07 08:11

9 楼

//nod & bow
Thanks a lot, aaajoo!:)

【在 a****o 的大作中提到】

: Are you using Oracle? My code is Ansi sql, Oracle does not
: support inner join.
: the logic is if for each sid, if there's a courseid in
: course table that is not in enrollment for this sid, then we
: don't select it. the join to student is trivial though.
: Clear?

p*s2000-11-07 08:11

10 楼

double negation: (using pure relational algebra conforming operations)
For each student, he/she is qualified if
there exists no such a course that
the course is not in his/her enrollment list.

SELECT S.name
FROM students S
WHERE NOT EXISTS
(SELECT *
FROM course C
WHERE NOT EXISTS
(SELECT *
FROM

【在 s*****g 的大作中提到】

: 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!

p*s2000-11-07 08:11

11 楼

enrollment is accessed double times. The first enrollment access
in the inner join is not necessary.

【在 a****o 的大作中提到】

p*s2000-11-07 08:11

12 楼

Oracle supports inner join, only not the syntactical name.
ANSI SQL is majorly defined by IBM guys, so it looks much like
DB2's SQL.

【在 a****o 的大作中提到】

s*g2000-11-07 08:11

13 楼

Wow! //admire & bow

【在 p****s 的大作中提到】

:
: Oracle supports inner join, only not the syntactical name.
: ANSI SQL is majorly defined by IBM guys, so it looks much like
: DB2's SQL.

a*o2000-11-07 08:11

14 楼

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))

【在 p****s 的大作中提到】

:
: Oracle supports inner join, only not the syntactical name.
: ANSI SQL is majorly defined by IBM guys, so it looks much like
: DB2's SQL.