作业7 SQL查询-更新（3）

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

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

 检索修读了学号为S4的学生所修读的所有课程的学生的姓名。

SELECT S.SN
FROM S
WHERE NOT EXISTS
     (SELECT * 
     FROM SC X
     WHERE X.SNO='S4' AND 
          NOT EXISTS
          (SELECT * 
           FROM SC Y
           WHERE S.SNO=Y.SNO AND Y.CNO=X.CNO))

检索选修所有课程的学生的学号。

SELECT S.SNO
FROM S
WHERE NOT EXISTS
     (SELECT * 
     FROM C CX
     WHERE NOT EXISTS
          (SELECT * 
           FROM SC SCX
           WHERE S.SNO=SCX.SNO AND SCX.CNO=CX.CNO))
或者
select sno
from s
where not exists (select * 
                  from c 
                  where not exists 
                  (select * 
                   from sc 
                   where sno =s.sno and cno=c.cno))

 检索不选修任何课程的学生的学号。

SELECT SNO
FROM S
except
SELECT DISTINCT SNO  
FROM SC

在关系C中增添一门新课（新课号：C9，课程名：软件工程，先修课程号：C1）。

INSERT INTO C VALUES('C9','软件工程', 'C1');

学号为S17的学生因故退学请在S与SC中将其信息去除。

DELETE FROM SC WHERE SNO ='S17';
DELETE FROM S WHERE SNO ='S17';

将关系S中学生S6的年龄改为22岁。

UPDATE S SET AGE=22 WHERE SNO='S6';

将关系S中学生的年龄均增加1岁。

UPDATE S SET AGE=AGE+1;

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

完成如下查询

找出使用供应商S2供应的全部零件的工程号。

SELECT JNO
FROM J 
WHERE NOT EXISTS
     (SELECT *
      FROM SPJ X
      WHERE X.SNO='S2' AND NOT EXISTS
                           (SELECT * 
                              FROM SPJ Y
                              WHERE Y.JNO=J.JNO AND Y.PNO=X.PNO ))

把全部红色零件的颜色改成蓝色。

UPDATE P SET COLOR='蓝' WHERE COLOR='红'

由S10供给J4的零件P6改为由S8供应，请作必要的修改。

Insert into spj 
select 'S8', pno,jno, qty 
from spj 
where SNO='S10' AND PNO='P6' AND JNO='J4';
delete from spj where SNO='S10' AND PNO='P6' AND JNO='J4'

从供应商关系中删除S2的记录，并从供应零件关系中删除相应的记录。

Delete from spj where sno='s2'
Delete from s where sno='s2'

将工程J2的预算改为40万元。

Update J SET BG=40 WHERE JNO='J2'

删除工程J8订购的由S4提供零件的所有供应信息。

DELETE FROM SPJ WHERE JNO='J8' AND SNO='S4'

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

检索选修课程包含姓张老师所授全部课程的学生的学号与姓名。

SELECT SNO,SN
FROM S
WHERE NOT EXISTS
  (SELECT *
   FROM C
   WHERE TH LIKE '张%' 
      AND NOT EXISTS
         (SELECT *
          FROM SC
          WHERE SNO=S.SNO AND C.CNO=CNO))
或者
select sno,sn
from s
where not exists
(select * 
from c
where not exists
(select *
from sc 
where sc.sno=s.sno and sc.cno=c.cno) and c.th like '张%')

把王非同学的学生信息及其选课情况等全部删除

DELETE FROM SC
WHERE SNO in
(SELECT SNO
 FROM S
 WHERE SN='王非');
DELETE FROM S WHERE SN='王非';

在课程表中添加一门新课程，其信息为：（'C8','信息系统概论','孙力'）。

INSERT INTO C VALUES('C8', '信息系统概论', '孙力')

在选修关系表SC中添加所有学生对'C8'课程的选修关系记录，成绩暂定为60，请用一条命令完成本批量添加任务。

--INSERT INTO SC(SNO,CNO,SCORE) SELECT SNO, 'C8',60 FROM S
INSERT INTO SC(SNO,CNO,SCORE) SELECT SNO, CNO ,60 FROM S,c where cno='C8'

把选“信息系统概论”课程的男学生的成绩暂全部初始化重新设置为0

UPDATE SC 
SET SCORE=0 
WHERE  '男'= 
(SELECT SEX 
FROM S
WHERE SNO=SC.SNO) AND '信息系统概论'= 
(SELECT CN  
FROM C 
WHERE CNO=SC.CNO)

/* 或者
UPDATE SC 
SET SCORE=0
WHERE SNO IN 
(SELECT SNO 
FROM S 
WHERE SEX= '男') AND CNO IN 
(SELECT CNO  
FROM C 
WHERE CN='信息系统概论')
*/
或者
update sc
set score=0
where cno in(select cno from c where cn='信息系统概论' )and 
sno in (select sno from s where sex='男')