SQL数据查询——综合练习（一）

连接+嵌套查询练习

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