在工程数据库“SPJ”中,编写SQL,在表“SPJ”插入数据,插入条件是:表“J”工程号未在表“SPJ”中出现,零件号(PNO)、供应商号(SNO)、数量(QTY)均为’S5’、’P6’、500。 ⑦编写SQL,查询至少使用三种零件的工程名称。
时间: 2024-06-03 18:11:59 浏览: 99
通过查询不同数据库资料插入数据
3星 · 编辑精心推荐
可以回答该问题,以下是SQL语句:
INSERT INTO SPJ (SNO, PNO, JNO, QTY)
SELECT 'S5', 'P6', J.JNO, 500
FROM J
WHERE J.JNO NOT IN (SELECT SPJ1.JNO FROM SPJ AS SPJ1 WHERE SPJ1.PNO = 'P6' AND SPJ1.SNO = 'S5')
GROUP BY J.JNO;
SELECT J.JNAME FROM J, SPJ
WHERE J.JNO = SPJ.JNO
GROUP BY J.JNO, SPJ.PNO
HAVING COUNT(DISTINCT SPJ.PNO) >= 3;
阅读全文