几道经典的SQL语句题

1.Consider the following schema:

Students (sid: integer, sname: string)51Testing软件测试网 w"w%vy] M%d]6S

Students_Taking_Courses (sid: integer, cid: integer, score: real)51Testing软件测试网
cK`VT4T.LY}d

In the schema, (sid, cid) is the primary key for Students_Taking_Courses. Write the following queries in SQL.51Testing软件测试网]Io7Tq

a.51Testing软件测试网)b!gO%BG,nz3~_

Find the names of all the students who have taken both course #3160 and course #2214.

Solution 151Testing软件测试网#KL0yA;?m
O2{

SELECT S.sname

FROM Students S, Students_Taking_Courses T1, Students_Taking_Courses T2

WHERE S.sid=T1.sid AND S.sid=T2.sid AND T1.cid=3160 AND T2.cid=2214

Solution 2

(SELECT S.sname

FROM Students S, Students_Taking_Courses T51Testing软件测试网 N
e0{I9E7jLW9]

WHERE S.sid=T.sid AND T.cid=3160 )

INTERSECT

(SELECT *

FROM Students S, Students_Taking_Courses T

WHERE S.sid=T.sid AND T.cid=2214 )51Testing软件测试网!~/vYrvv$m/TP

b.51Testing软件测试网K#G-U0TWR

Find the names of all the students who have never taken any courses.

Solution 1

SELECT S.sname

FROM Students S51Testing软件测试网'}5MwZ8Aj

WHERE NOT EXISTS ( SELECT *FROM Students_Taking_Courses T51Testing软件测试网#\K2U+_-o

WHERE S.sid=T.sid )

 51Testing软件测试网"q6D K6J-sB}-H6F

Solution 251Testing软件测试网e;W8|s6IAt s

SELECT S.sname

FROM Students S

WHERE S.sid NOT IN ( SELECT T.sid FROM Students_Taking_Courses T)

 51Testing软件测试网7J%Me"A$D*e!w

c.51Testing软件测试网n [HW
h3v5w}tf P

Find the names of all the students who have no scores below 75, and display in an alphabetical order.51Testing软件测试网c:M+~j)S7QJjQ

Solution 151Testing软件测试网?B7Z;YQ#]

SELECT S.sname51Testing软件测试网S? h&fO+qJc8X?

FROM Students S51Testing软件测试网^IK1v(Jm

WHERE NOT EXISTS ( SELECT *FROM Students_Taking_Courses T51Testing软件测试网h#@
yM+Dpx@

WHERE S.sid=T.sid AND Score <75)

ORDER BY S.sname

 51Testing软件测试网'K8zN9P5YIZ

Solution 2

SELECT S.sname51Testing软件测试网 @*X8w,l#~ T2j;u2Fq&l

FROM Students S51Testing软件测试网#a'[*ZpNI3y%u0v

WHERE S.sid NOT IN ( SELECT T.sid51Testing软件测试网0^.T%cmp-g"iY

FROM Students_Taking_Courses T

WHERE Score <75)

ORDER BY S.sname

 51Testing软件测试网.x_ `w m

d.51Testing软件测试网P{b-o#L

Find the average score for all the students who have taken more than 3 courses.

Solution

SELECT S.sname, AVG(T.score) AS average51Testing软件测试网5[6^j'F0b'P

FROM Students S, Students_Taking_Courses T

WHERE S.sid=T.sid51Testing软件测试网.aPSWHO-N

GROUP BY S.sid, S.sname

HAVING COUNT(T.sid)>351Testing软件测试网!a9x&DkIi

e.

Find the grades of all courses taken by a student whose sid is 123, and display the sid and the cid.51Testing软件测试网(g0uL[4O+|q}

Solution51Testing软件测试网/nF%YSf!F

SELECT T.sid, T.cid, T.score51Testing软件测试网] d/~&EK+uh+aS

FROM Students_Taking_Courses T51Testing软件测试网V j-s[.Zs7b LV

WHERE T.sid = 12351Testing软件测试网s+Czg kH3a

 51Testing软件测试网(R'w-h ? z%xx#\uP3C

f.

Find the names of all the students who are on the honor roll (average of scores greater than 90),51Testing软件测试网Wr%UsYf+e*fub

and display in increasing alphabetical order by name.51Testing软件测试网}[HO;X#r5g

Solution51Testing软件测试网lTOX;Z)Q @w(wLY

SELECT S.sname51Testing软件测试网KxI/V6zp2I

FROM Students S, Students_Taking_Courses T51Testing软件测试网*cC8EIlS;i:fJ$r x

WHERE S.sid=T.sid51Testing软件测试网x"O{et8{w

GROUP BY S.sid,S.sname

HAVING AVG(T.score)>90

ORDER BY S.sname51Testing软件测试网:HW%\?
wtv

g.51Testing软件测试网z.Zk3q8]

Find the course that has been taken by the most students.

Solution51Testing软件测试网8V5sd4?QoyX5g

SELECT Temp.cid, Temp.count51Testing软件测试网#ZrJm+~2Y8i2p^

FROM (SELECT COUNT(sid) AS count, T.cid AS cid

FROM Students_Taking_Courses T51Testing软件测试网rP J{hF

GROUP BY T.cid) Temp

WHERE Temp.count = (SELECT MAX(Temp2.count)

FROM (SELECT COUNT(sid) AS count

FROM Students_Taking_Courses T51Testing软件测试网1nwByJ9Oz

GROUP BY T.cid ) Temp2)

h.

Find the names of all students who have failed more than one fifth of their classes (score < 70).51Testing软件测试网
L3XEp[,I{T

Solution1

SELECT S.sname51Testing软件测试网/rVj:](T8x}

FROM Students S, Students_Taking_Courses T51Testing软件测试网#urjkF o8i9Sr

WHERE T.score<70 AND S.sid=T.sid51Testing软件测试网)j[Oz
P mz

GROUP BY T.sid, S.sname51Testing软件测试网J:s#B_7n!bKy

HAVING count(cid) > 0.2*(SELECT COUNT(*) FROM Students_Taking_Courses T2 WHERE T2.sid=T.sid GROUP BY T.sid)

 51Testing软件测试网J\f(@I!Y

Solution2

SELECT S.sname
9AT G!oaz` d)X9k }0FROM Students S, Students_Taking_Courses T,Students_Taking_Courses T251Testing软件测试网&_UuoX6Zuu Dh!A9q]
WHERE T.score<70 AND S.sid=T.sid AND T2.sid=T.sid
/IoAnC0GROUP BY T.sid, S.sname
]8~p4w%L:W0HAVING count(T.cid) > 0.2*(COUNT(T2.cid))51Testing软件测试网3a([3^@w$? vQ

i.51Testing软件测试网;U5h"HabJSKT

Find the average of all students taking 3160.

Solution

SELECT AVG(T.score) AS average

FROM Students_Taking_Courses T

WHERE T.cid=316051Testing软件测试网P+x;|6bD`H4TEW

j.51Testing软件测试网v*r%ixy

Find the names of all students making grade below 60.51Testing软件测试网Qv.j&ZI M

Solution

SELECT DISTINCT S.sname51Testing软件测试网Z(r!{5b
b4]5`

FROM Students S, Students_Taking_Courses T51Testing软件测试网(vwYZ tnAh

WHERE T.score<60 AND S.sid=T.sid

 51Testing软件测试网(\]0R;v4l_z#B

k.51Testing软件测试网N"HtqTo

Find the best performance class and the worst.

Solution for the best class

SELECT Temp.cid, Temp.average51Testing软件测试网oiS5JTH(x

FROM (SELECT AVG(T.score) AS average, T.cid

FROM Students_Taking_Courses T

GROUP BY T.cid) Temp

WHERE Temp.average = (SELECT MAX(Temp2.average)51Testing软件测试网^S
EU%K4P?

FROM (SELECT AVG(T.score) AS average51Testing软件测试网5qM
_ x eX

FROM Students_Taking_Courses T

GROUP BY T.cid ) Temp2)51Testing软件测试网_ DD&AC O Uj.L

Solution for the worst class

SELECT Temp.cid, Temp.average

FROM (SELECT AVG(T.score) AS average, T.cid

FROM Students_Taking_Courses T

GROUP BY T.cid) Temp51Testing软件测试网]$n~ LdQA ?

WHERE Temp.average = (SELECT MIN(Temp2.average)51Testing软件测试网P{'Vfx"p

FROM (SELECT AVG(T.score) AS average51Testing软件测试网%l @,Z*Ud b

FROM Students_Taking_Courses T

GROUP BY T.cid ) Temp2)51Testing软件测试网DG&p$gc*u'VN

l.51Testing软件测试网5^D.{!Lm4yW|v?)hv

Find the total number of the course the university has provided.51Testing软件测试网gQ~$`?j#Rq.z

Solution51Testing软件测试网YopCdJ3D8U_

SELECT COUNT(DISTINCT T.cid)

FROM Students_Taking_Courses T

 51Testing软件测试网M(S@0S+r(J_["Nj^

m.51Testing软件测试网]/s&X(n:](d1~\&e)v

Find the total number of the course that a student has taken for every student, and display in increasing order by students name.51Testing软件测试网%VJ5?cU

Solution51Testing软件测试网memv7AL

SELECT S.sname, COUNT(T.cid)

FROM Students S, Students_Taking_Courses T51Testing软件测试网)Vf
s/Hl}Zt

WHERE S.sid=T.sid

GROUP BY S.sid, S.sname

ORDER BY S.sname51Testing软件测试网&S9j*R)F0l7U1hYd3gS

附：oracle数据库下的例子：51Testing软件测试网g;\l"h2H

create table STUDENTS

(

 SID  NUMBER not null,51Testing软件测试网)S^ `kJq[

 SNAME VARCHAR2(10)51Testing软件测试网%NYo/s/s5e&W

);51Testing软件测试网^ y.kc|

alter table STUDENTS51Testing软件测试网-K.Y"S}zZt){3si

 add constraint SIDCIDPK primary key (SID);51Testing软件测试网 d"e6B lx:n6lxW

create table STUDENTS_TAKING_COURSES

(

 SID  NUMBER,

 CID  NUMBER,51Testing软件测试网-B$dq*b-y$Lm7z"D7u

 SCORE VARCHAR2(5)

);

insert into STUDENTS (SID, SNAME)51Testing软件测试网&~j6r-Eu#T,b3P

values (1, 'zhushy');51Testing软件测试网O*["h!O2o/x!F

insert into STUDENTS (SID, SNAME)

values (2, 'zhushy2');51Testing软件测试网&e:Z*t'Gj

insert into STUDENTS (SID, SNAME)51Testing软件测试网zJxe3s-YJx @

values (3, 'zhushy3');

insert into STUDENTS (SID, SNAME)

values (4, 'zhushy4');51Testing软件测试网_7lM?+T ~3~

insert into STUDENTS (SID, SNAME)

values (5, 'zhushy4');51Testing软件测试网&W%iF[9}+c$D

insert into STUDENTS_TAKING_COURSES (SID, CID, SCORE)

values (1, 1, '77');

insert into STUDENTS_TAKING_COURSES (SID, CID, SCORE)51Testing软件测试网1C,s'@#MO-_6Q;n*f

values (2, 2, '62');51Testing软件测试网)|Z+[4} }y!l c7G

insert into STUDENTS_TAKING_COURSES (SID, CID, SCORE)

values (2, 3, '52');

insert into STUDENTS_TAKING_COURSES (SID, CID, SCORE)

values (3, 4, '94');51Testing软件测试网~~RqZ

insert into STUDENTS_TAKING_COURSES (SID, CID, SCORE)

values (3, 5, '84');51Testing软件测试网N+\
ku.p4{e_+P

insert into STUDENTS_TAKING_COURSES (SID, CID, SCORE)

values (3, 6, '84');

insert into STUDENTS_TAKING_COURSES (SID, CID, SCORE)51Testing软件测试网2o#S}DDd+zd

values (4, 7, '78');

insert into STUDENTS_TAKING_COURSES (SID, CID, SCORE)

values (4, 8, '99');51Testing软件测试网1Rp0S2@ T5^

insert into STUDENTS_TAKING_COURSES (SID, CID, SCORE)

values (4, 9, '100');51Testing软件测试网?&N.Q1?'wD{

insert into STUDENTS_TAKING_COURSES (SID, CID, SCORE)51Testing软件测试网M#m;y,h.A.B)`FF0S

values (4, 8, '64');

commit;