第三章 关系数据库标准语言SQL语言(2)

FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE GRADE<80 AND S.S#=SC.S#)

（3） DELETE FROM SC WHERE GRADE IS NULL （4） DELETE FROM SC WHERE S# IN

(SELECT S# FROM S WHERE SNAME='WANG') （5） UPDATE SC SET GRADE=NULL WHERE GRADE<60 AND C# IN

(SELECT C# FROM C WHERE CNAME='MATHS') （6） UPDATE SC SET GRADE=GRADE*1.05

WHERE GRADE<(SELECT AVG(GRADE) FROM SC) AND S# IN (SELECT S# FROM S WHERE SEX='F')

（7） UPDATE SC SET GRADE=GRADE*1.05 WHERE C#='C4' AND GRADE<=75 UPDATE SC SET GRADE=GRADE*1.04 WHERE C#='C4' AND GRADE>75 8. 用SQL语句建立第二章习题5中的4个表。 建立表S(SNO,SNAME,STATUS,CITY)

CREATE TABLE S(SNO CHAR(3) primary key, SNAME CHAR(10),

STATUS CHAR(2),CITY CHAR(10)); 建立表P(PNO,PNAME,COLOR,WETGHT)

CREATE TABLE P(PNO CHAR(3) primary key,PNAME CHAR(10), COLOR CHAR(4),WEIGHT INT);

建立表J(JNO,JNAME,CITY)

CREATE TABLE J(JNO CHAR(3) primary key, JNAME CHAR(10), CITY CHAR(10)); 建立表SPJ(SNO,PNO,JNO,QTY)

CREATE TABLE SPJ(SNO CHAR(3), PNO CHAR(3), JNO CHAR(3),QTY INT,

primary key (sno,jno,pno));

9. 针对上题建立的4个表试用SQL语言完成下列查询 ⑴求供应工程J1零件的供应商号SNO。 ⑵求供应工程J1零件P1的供应商号SNO。 ⑶求供应工程J1红色零件的供应商号SNO。

⑷求没有使用天津供应商生产的红色零件的工程号JNO。 ⑸求至少用了S1供应商所供应的全部零件的工程号JNO。 ⑴SELECT SNO FROM SPJ WHERE JNO=‘J1’;

⑵SELECT SNO FROM SPJ WHERE JNO=‘J1’ AND PNO=‘P1’;

6 ⑶SELECT SNO FROM SPJ WHERE JNO=‘J1’ AND PNO IN (SELECT PNO FROM P WHERE COLOR=‘红’);

或SELECT SNO FROM SPJ,P WHERE JNO=‘J1’ AND SPJ.PNO=P.PNO AND COLOR=‘红’);

⑷解析：从J表入手，以包含那些尚未使用任何零件的工程号。 SELECT JNO FROM J WHERE NOT EXISTS

(SELECT * FROM SPJ WHERE SPJ.JNO=J.JNO

AND SNO IN (SELECT SNO FROM S WHERE CITY=‘天津’)

AND PNO IN (SELECT PNO FROM P WHERE COLOR=‘红’)); 或SELECT JNO FROM J WHERE NOT EXISTS

(SELECT * FROM SPJ,S,P WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND S.CITY=‘天津’ AND P.COLOR=‘红’); ⑸SELECT DISTINCT JNO FROM SPJ SPJZ WHERE NOT EXISTS (SELECT * FROM SPJ SPJX WHERE SNO=‘S1’ AND NOT EXISTS

(SELECT * FROM SPJ SPJY WHERE SPJY.PNO=SPJX.PNO AND SPJY.JNO=SPJZ.JNO));

解析：上述查询可以抽象为：要求这样的工程x，使(\y)p→q为真。即对于所有的零件y，满足逻辑蕴涵p→q：p为谓词“供应商S1供应了零件y”；q为谓词“工程x选用了零件y”。即，只要“供应商S1供应了零件y”为真，则“工程x选用了零件y”就为真。

针对11题中的4个表试用SQL语言完成以下各项操作：

⑴找出所有供应商的姓名和所在城市。 ⑵找出所有零件的名称、颜色、重量。 ⑶找出使用供应商S1所供应零件的工程号码。 ⑷找出工程项目J2使用的各种零件的名称及其数量。 ⑸找出上海厂商供应的所有零件号码。 ⑹找出使用上海产的零件的工程名称。 ⑺找出没有使用天津产的零件的工程号码。 ⑻把全部红色零件的颜色改为蓝色。

⑼由S5供给J4的零件P6改为由S3供应，请做必要的修改。 ⑽从供应商表中删除S2的记录，并从供应情况表中删除相应记录 ⑾请将(S2,J6,P4,200)插入供应情况表。 ⑴SELECT SNAME,CITY FROM S;

⑵SELECT PNAME,COLOR,WEIGHT FROM P;

7 10.

⑶SELECT JNO FROM SPJ WHERE SNO=‘S1’; ⑷SELECT P.PNAME,SPJ.QTY FROM P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.JNO=‘J2’; ⑸SELECT DISTINCT PNO FROM SPJ

WHERE SNO IN (SELECT SNO FROM S WHERE CITY=‘上海’); ⑹SELECT JNAME FROM J,SPJ,S

WHERE J.JNO=SPJ.JNO AND SPJ.SNO=S.SNO AND S.CITY=‘上海’; 或：SELECT JNAME FROM J WHERE JNO IN (SELECT JNO FROM SPJ,S WHERE SPJ.SNO=S.SNO AND S.CITY=‘上海’); ⑺SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM SPJ WHERE SPJ.JNO=J.JNO

AND SNO IN (SELECT SNO FROM S WHERE CITY=‘天津’)); 或：SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM SPJ,S

WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND S.CITY=天津’); ⑻UPDATE P SET COLOR=‘蓝’ WHERE COLOR=‘红’; ⑼UPDATE SPJ SET SNO=‘S3’

WHERE SNO=‘S5’ AND JNO=‘J4’ AND PNO=‘P6’; ⑽DELETE FROM SPJ WHERE SNO=‘S2’; DELETE FROM S WHERE SNO=‘S2’;

解析：注意删除顺序，应先从SPJ表中删除供应商S2的供应零件的记录，然后从S表中删除S2。

⑾INSERT INTO SPJ(SNO,JNO,PNO,QTY) VALUES (‘S2’,’J6’,’P4’,200); 或：INSERT INTO SPJ VALUES (‘S2’,’J6’,’P4’,200); 11. 给出以下三个基本表。各表的记录如下:

Student（学生表）的字段按顺序为学号、姓名、性别、年龄、所属院系； Course（课程表）的字段按顺序为课程编号、课程名、先行课程、课程学分； SC（选课表）的字段按顺序为学号、课程号、成绩。

StudentSCSnoSnameSsexSageSdeptSnoCnoGrade95001李勇男20CS9500119295002刘晨女19IS9500128595003王明女18MA9500138895004张立男19IS95002290950033808

试用SQL语言完成以下各项操作，

(1)写出创建学生表Student的SQL命令，各字段的类型及长度应根据实际情况确定。其中学号属性不能为空，并且其值是唯一的。并在Sno列上建立一个聚簇索引。

(2)检索信息系（IS）和计算机科学系（CS）的学生的姓名和性别。

CourseCnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL64(3)检索各个课程号及相应的选课人数。

(4)检索每个学生及其选修的课程名和成绩。 (1) CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20), Ssex CHAR(1), Sage INT,

Sdept CHAR(15));

CREATE CLUSTED INDEX Stusname ON Student(Sname); (2)SELECT Sname, Ssex FROM Student

WHERE Sdept IN ('IS','CS') (3)SELECT Cno, COUNT(Sno) FROM SC

GROUP BY Cno;

(4)SELECT Student.Sno, Sname, Course.Cname, SC.Grade FROM Student, SC, Course

WHERE Student.Sno=SC.Sno and SC.Cno=Course.Cno;

12. 请为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码(PNO)、

供应数量(QTY)。针对该视图完成下列查询： ⑴找出三建工程项目使用的各种零件代码及其数量。 ⑵找出供应商S1的供应情况。 建立视图：