设有一个SPJ数据库,包括S、P、J、SPJ 4个关系模式。 S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY) 试用SQL语句完成下列各项查询操作: (1)找出所有供应商的姓名和所在城市; (2)找出所有零件的名称、颜色、重量; (3)找出使用供应商S1所供零件的工程号码; (4)找出工程项目J2使用的各种零件的名称及其数量; (5)找出上海厂商供应的所有零件号码; (6)找出使用上海产的零件的工程号码; (7)找出没有使用天津产的零件的工程号码; (8)把全部红色零件的颜色改成蓝色; (9)由S5供给J4的零件P6改为由S3供应; (10)从供应商关系S中删除S2的记录,并从供应情况关系SPJ中删除相应的记录; (11)请将(S2,J6,P4,200)插入供应情况关系。
时间: 2023-06-14 09:04:14 浏览: 203
(1)SELECT SNAME, CITY FROM S;
(2)SELECT PNAME, COLOR, WEIGHT FROM P;
(3)SELECT JNO FROM SPJ WHERE SNO = 'S1';
(4)SELECT P.PNAME, SPJ.QTY FROM SPJ INNER JOIN P ON SPJ.PNO = P.PNO WHERE JNO = 'J2';
(5)SELECT PNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY = '上海');
(6)SELECT JNO FROM SPJ WHERE PNO IN (SELECT PNO FROM P WHERE COLOR = '上海');
(7)SELECT DISTINCT JNO FROM SPJ WHERE PNO NOT IN (SELECT PNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY = '天津'));
(8)UPDATE P SET COLOR = '蓝色' WHERE COLOR = '红色';
(9)UPDATE SPJ SET SNO = 'S3' WHERE SNO = 'S5' AND JNO = 'J4' AND PNO = 'P6';
(10)DELETE FROM S WHERE SNO = 'S2';
DELETE FROM SPJ WHERE SNO = 'S2';
(11)INSERT INTO SPJ(SNO, JNO, PNO, QTY) VALUES ('S2', 'J6', 'P4', 200);