连接+嵌套查询练习
S,C,SC关系表
设有学生-课程关系数据库,它由三个关系组成,它们的模式是:
S(学号SNO,姓名SN,所在系DEPT,年龄AGE)、
C(课程号CNO,课程名CN,先修课号CPNO)、
SC(学号SNO,课程号CNO,成绩SCORE)。
请用SQL语句写出下列查询:
(1)检索选修了课程号C1成绩为A的所有学生姓名。
SELECT SN
FROM S
WHERE SNO IN
(SELECT SNO
FROM SC
WHERE CNO='C1' AND SCORE='A') --检索选修C1课程并且成绩为A的学生SNO
(2)检索学号为S1的学生修读的所有课程名及先修课号。
SELECT CN,CPNO
FROM C
WHERE CNO IN
(SELECT CNO
FROM SC
WHERE SNO='S1') --检索学号为S1同学选修课程的CNO
(3)检索年龄为23岁的学生所修读的课程名。
SELECT CN
FROM C
WHERE CNO IN
(SELECT CNO
FROM SC --用SC关系将S和C关系联系
WHERE SNO IN
(SELECT SNO
FROM S
WHERE AGE=23)) --检索年龄为23岁学生的SNO
(4)检索至少修读了学号为S5的学生修读的一门课的学生的姓名。
SELECT DISTINCT SN --由于该学生可能修读S5同学多门课程因此需要唯一性约束
FROM S INNER JOIN SC ON S.Sno=SC.Sno
WHERE Cno = ANY
(SELECT Cno
FROM SC
WHERE Sno='S5') --检索出至少修读S5同学一门课的学生姓名
EXCEPT --将S5该同学从表中去除掉
SELECT SN
FROM S
WHERE SNO='S5'
S,J,P,SPJ关系表
设有四个关系:
S(SNO,SNAME, ADDRESS,TEL)其中,SNO:供应商代码 SNAME:姓名 ADDRESS:地址 TEL:电话;
J(JNO,JNAME,LEADER,BG)其中,JNO:工程代码 JNAME:工程名 LEADER:负责人 BG:预算;
P(PNO,PNAME,SPEC,CITY,COLOR)其中,PNO:零件代码 PNAME:零件名 SPEC:规格 CITY:产地 COLOR:颜色;
SPJ(SNO,PNO,JNO,QTY)其中,SNO:供应商代码 JNO:工程代码 PNO:零件代码 QTY:数量;
完成如下查询:
(5)找出工程代码为J2的工程使用的所有零件名称、数量。
SELECT PNAME,QTY
FROM P INNER JOIN SPJ ON P.PNO=SPJ.PNO --实现P和SPJ的连接
WHERE JNO='J2'
(6)找出使用上海产的零件的工程名称。
SELECT JNAME
FROM J
WHERE JNO IN
(SELECT JNO
FROM SPJ --SPJ实现J和P的联系
WHERE PNO IN
(SELECT PNO
FROM P
WHERE CITY='上海')) --检索上海产的PNO
(7)找出没有使用天津产的零件的工程号。
SELECT JNO
FROM J
WHERE JNO NOT IN --除天津产的以外的JNO
(SELECT JNO --天津产的JNO
FROM SPJ
WHERE PNO IN
(SELECT PNO
FROM P
WHERE CITY='天津'))
(8)求没有使用天津产的红色零件的工程号。
SELECT JNO
FROM J
WHERE JNO NOT IN
(SELECT JNO
FROM SPJ
WHERE PNO IN
(SELECT PNO
FROM P
WHERE CITY='天津' AND COLOR='红')) --新添的红色的条件直接加在后面即可
(9)检索姓刘的学生选修的所有课程名与教师名。
SELECT CN,TH
FROM C,SC,S --C,SC,S关系的连接
WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND SN LIKE '刘%'
简易S,C,SC关系表
对于简易教学管理数据库有如下3个基本表:
S(SNO,SN,AGE,SEX)、
SC(SNO,CNO,SCORE)、
C(CNO,CN,TH),
其含义为SNO(学号),SN(姓名),AGE(年龄),SEX(性别),SCORE(成绩),CNO(课程号),CN(课程名),TH(教师名)。
(10)检索没有选修数据库课程的学生的学号与姓名。
SELECT SNO,SN
FROM S
WHERE SNO NOT IN --不选修数据库的SNO
(SELECT SNO --选修数据库的SNO
FROM SC
WHERE CNO IN
(SELECT CNO
FROM C
WHERE CN='数据库')) --检索选修课程名是数据库的CNO
(11)检索至少选修两门课程的学生的学号与姓名。
SELECT SNO,SN
FROM S
WHERE SNO IN
(SELECT SNO
FROM SC
GROUP BY SNO --按学生学号进行分组
HAVING COUNT(CNO)>=2) --统计选修两门及两门以上的SNO