56 lines
761 B
SQL
56 lines
761 B
SQL
USE SPJ;
|
|
|
|
-- ①
|
|
SELECT SNO,CITY FROM S;
|
|
|
|
-- ②
|
|
SELECT PNAME,COLOR,WEIGHT FROM P;
|
|
|
|
-- ③
|
|
SELECT JNO FROM SPJ WHERE SNO='S1';
|
|
|
|
-- ④
|
|
SELECT P.PNAME,SPJ.QTY
|
|
FROM SPJ
|
|
JOIN J ON SPJ.JNO=J.JNO
|
|
JOIN P ON SPJ.PNO=P.PNO
|
|
WHERE J.JNO='J2';
|
|
|
|
-- ⑤
|
|
SELECT SPJ.PNO
|
|
FROM SPJ
|
|
JOIN S ON SPJ.SNO=S.SNO
|
|
WHERE S.CITY='上海';
|
|
|
|
-- ⑥
|
|
SELECT J.JNAME
|
|
FROM SPJ
|
|
JOIN J ON SPJ.JNO=J.JNO
|
|
JOIN S ON SPJ.SNO=S.SNO
|
|
WHERE S.CITY='上海';
|
|
|
|
-- ⑦
|
|
SELECT SPJ.JNO
|
|
FROM SPJ
|
|
JOIN S ON SPJ.SNO=S.SNO
|
|
WHERE S.CITY != '天津';
|
|
|
|
-- ⑧
|
|
UPDATE P
|
|
SET COLOR='蓝'
|
|
WHERE COLOR='红';
|
|
|
|
-- ⑨
|
|
UPDATE SPJ
|
|
SET SNO='S3'
|
|
WHERE SNO='S5' AND PNO='P6' AND JNO='J2';
|
|
|
|
-- ⑩
|
|
DELETE FROM SPJ
|
|
WHERE SNO='S2';
|
|
DELETE FROM S
|
|
WHERE SNO='S2';
|
|
|
|
-- ⑪
|
|
INSERT INTO SPJ(SNO,JNO,PNO,QTY)
|
|
VALUES('S2','J6','P4',200); |