|
《数据库系统概论》第五版 第三章 课后习题 第五题 习题答案 第四题以及数据库表自动建立的代码点此处 下面是第五题的代码实现-- (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');
|