《数据库系统概论》第五版 第三章 课后习题 第五题 习题答案

《数据库系统概论》第五版 第三章 课后习题 第五题 习题答案

第四题以及数据库表自动建立的代码点此处

下面是第五题的代码实现

-- (1)
SELECT SNAME,CITY FROM S;

-- (2)
SELECT PNAME, COLOR,WEIGHT FROM P;

-- (3)
SELECT DISTINCT JNO from SPJ WHERE SNO='S1';

-- (4)
SELECT DISTINCT P.PNAME,SPJ.QTY FROM P,SPJ WHERE SPJ.JNO = 'J2' AND P.PNO =SPJ.PNO;

-- (5)
SELECT DISTINCT SPJ.PNO FROM S,SPJ WHERE S.SNO = SPJ.SNO AND S.CITY='上海' ;

-- (6) 
SELECT DISTINCT JNAME FROM S,SPJ,J WHERE S.CITY='上海' AND S.SNO=SPJ.SNO AND SPJ.JNO=J.JNO;

-- (7)
SELECT DISTINCT J.JNO FROM J WHERE J.JNO NOT IN (
SELECT J.JNO FROM S,SPJ,J WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY='天津');

-- (8)
UPDATE P SET COLOR ='蓝' WHERE COLOR='红';

-- (9)
UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND PNO='P6' AND JNO='J4';

-- (10)
#方法一：写两个语句
DELETE from S WHERE S.SNO='S2';
DELETE from SPJ WHERE SPJ.SNO='S2';

#方法二：将两个表连起来写一个删除语句
DELETE S,SPJ from S LEFT JOIN SPJ ON S.SNO=SPJ.SNO WHERE S.SNO='S2';

#方法三：写一个触发器，有兴趣的同学可以写在评论区

-- (11)
INSERT INTO SPJ VALUES('S2','J6','P4','200');