作业6 SQL查询（2）

1.设有学生-课程关系数据库，它由三个关系组成，它们的模式是：学生S（学号SNO，姓名SN，所在系DEPT，年龄AGE）、课程C（课程号CNO，课程名CN，先修课号CPNO）、SC（学号SNO，课程号CNO，成绩SCORE）。

请用SQL语句写出下列查询：

  （1）检索选修了课程号C1成绩为A的所有学生姓名。

SELECT SN
FROM S, SC
WHERE S.SNO=SC.SNO AND CNO='C1' AND SCORE='A'
或者
SELECT SN
FROM S
WHERE SNO IN
    (SELECT SNO
    FROM SC
    WHERE CNO='C1' AND SCORE='A')

（2）检索学号为S1的学生修读的所有课程名及先修课号。

SELECT CN, CPNO
FROM C
WHERE CNO IN (SELECT  CNO FROM SC WHERE SNO='S1')
或者
select cn,cpno
from c,s
where sno='S1'

（3）检索年龄为23岁的学生所修读的课程名。

SELECT DISTINCT CN
FROM S,SC,C
WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND AGE=23
或者
select cn
from c
where cno in 
(select cno 
 from sc where sno in (select sno from s where age=23))

（4）检索至少修读了学号为S5的学生修读的一门课的学生的姓名

SELECT S.SN
FROM S
WHERE sno in 
(select SNO 
from sc 
where cno in 
(select cno 
from sc 
where SNO='S5')) and sno<>'S5'
或者
SELECT DISTINCT SN  --由于该学生可能修读S5同学多门课程因此需要唯一性约束
FROM S,SC 
WHERE S.Sno=SC.Sno and Cno = ANY
(SELECT Cno
FROM SC
WHERE Sno='S5') --检索出至少修读S5同学一门课的学生姓名
AND S.SNO!='S5'--去除s5

2.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：数量；

完成如下查询

（1）找出工程代码为J2的工程使用的所有零件名称、数量。

SELECT PNAME,QTY
FROM SPJ,P 
WHERE SPJ.PNO=P.PNO AND JNO='J2'

（2）找出使用上海产的零件的工程名称.

SELECT distinct JNAME 
FROM P,SPJ,J 
WHERE P.CITY='上海' AND P.PNO=SPJ.PNO AND SPJ.JNO=J.JNO

（3）找出没有使用天津产的零件的工程号

SELECT JNO
FROM J 
WHERE JNO NOT IN 
(SELECT JNO 
FROM SPJ,P 
WHERE SPJ.PNO=P.PNO AND P.CITY='天津')
或者
select jno
from j
where jno  not in 
(select  jno 
 from spj 
 where pno in
 (select pno 
  from p 
  where city='天津'))

(4)求没有使用天津产的红色零件的工程号。

SELECT JNO
FROM J 
WHERE JNO NOT IN 
(SELECT JNO 
FROM SPJ,P 
WHERE SPJ.PNO=P.PNO AND P.CITY='天津' AND P.COLOR='红')
或者
select jno
from j
where jno  not in 
(select  jno 
 from spj 
 where pno in
 (select pno 
  from p 
  where city='天津' and color ='红'))

3.

对于简易教学管理数据库有如下3个基本表：S(SNO,SN,AGE,SEX)、SC(SNO,CNO,SCORE)、C(CNO,CN,TH)，其含义为SNO(学号),SN(姓名),AGE(年龄),SEX(性别)，SCORE(成绩)，CNO(课程号),CN(课程名),TH(教师名)。试用SQL语言表达如下查询及操作：

（1）检索姓刘的学生选修的所有课程名与教师名。

SELECT CN,TH
    FROM C
WHERE  CNO IN
        (SELECT CNO
         FROM SC
         WHERE SNO IN
            (SELECT SNO
             FROM S
             WHERE SN like '刘%'))
--或者
--SELECT DISTINCT CN,TH
--    FROM S,SC,C
--    WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND SN like '刘%'

（2）检索没有选修数据库课程的学生的学号与姓名

SELECT SNO,SN
FROM S
WHERE SNO NOT IN
  (SELECT  SNO
   FROM S
   WHERE  SNO IN
      (SELECT  SNO
       FROM SC
       WHERE  CNO IN
       (SELECT  CNO
        FROM C
        WHERE CN='数据库')))

（3）检索至少选修两门课程的学生的学号与姓名

SELECT SNO,SN
FROM S
WHERE SNO IN 
(SELECT SNO
FROM SC
GROUP BY SNO HAVING COUNT(*)>=2)