项目-工程-零件供应数据库练习及答案
- 格式：doc
- 大小：98.00 KB
- 文档页数：4
在SQL Server数据库管理系统中用SQL的DDL语言在创建S、P、J、SPJ 四个基本表，并输入相应的数据。
S、P、J和SPJ表中的数据分别如表S、P、J和SPJ所示。
根据上一题给出的供应商表S、零件表P、工程表J和供应表SJP结构和数据，用SQL完成下列操作。
1.查询所有工程的全部细节。
select * FROM j
2.查询在上海的工程的全部信息。
select * FROM j WHERE CITY='上海'
3.查询重量最轻的那些零件的零件号。
select pno FROM p WHERE WEIGHT=(select min(weight) FROM p)
select pno FROM p WHERE WEIGHT<= all (select weight FROM p)
4.查询为工程Jl 提供零件P1 的供应商号。
select SNO FROM SPJ WHERE JNO='J1' and PNO='P1'
5.查询由供应商号为S1 的供应商提供零件的工程名。
SELECT JNAME FROM J,SPJ WHERE J.JNO = SPJ.JNO AND SNO= 'S1'
或者
SELECT JNAME FROMJ
WHERE JNO IN (SELECT JNO FROM SPJ WHERE SNO ='S1' )
或者
SELECT JNAME FROMJ
WHERE EXISTS ( SELECT * FROM SPJ WHERE SNO = 'S1' AND JNO=J.JNO )
6.查询为工程J1 提供红色零件的供应商号。
SELECT SNO FROM SPJ,P
WHERE SPJ.PNO=P.PNO AND JNO='J1' AND COLOR='红'
或者
SELECT SNO FROM SPJ
WHERE JNO = 'J1' AND PNO = ANY ( SELECT PNO FROM P WHERE COLOR='红')
或者
SELECT SNO FROM SPJ WHERE JNO='J1' AND EXISTS (SELECT * FROM P WHERE COLOR='红' AND PNO=SPJ.PNO )
7.查询同时为工程J1 和J2 提供零件的供应商号。
SELECT SNO FROM JNO = 'J1' AND SNO IN
(SELECT SNO FROM SPJ WHERE JNO='J2' )
8.查询为所在城市为上海或北京的工程提供红色零件的供应商号。
SELECT SNO FROM SPJ,J,P
WHERE SPJ.JNO=J.JNO AND P.PNO=SPJ.PNO
AND (CITY='上海' OR CITY='北京') AND COLOR='红'
或者
SELECT SNO FROM SPJ
WHERE JNO IN (SELECT JNO FROM J WHERE CITY IN ('上海','北京'))
AND PNO IN (SELECT PNO FROM P WHERE COLOR='红')
9.查询与工程所在城市相同的供应商提供的零件号。
SELECT DISTINCT PNO FROM SPJ,J,S
WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND J.CITY=S.CITY
10.查询上海的供应商提供给上海的任一工程的零件号。
SELECT DISTINCT PNO FROM SPJ,J,S
WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO
AND J.CITY=S.CITY AND S.CITY='上海'
或者
SELECT DISTINCT PNO FROM SPJ
WHERE JNO IN (SELECT JNO FROM J WHERE CITY = '上海，)
AND SNO IN ( SELECT SNO FROM S WHERE CITY = '上海')
11.查询不使用上海供应商提供任何零件的工程号。
SELECT DISTINCT JNO FROM SPJ WHERE JNO NOT IN
(SELECT JNO FROM SPJ,S WHERE SPJ.SNO=S.SNO AND CITY='上海')
或者
SELECT DISTINCT JNO FROM SPJ WHERE JNO NOT IN
(SELECT JNO FROM SPJ WHERE SNO IN
(SELECT SNO FROM S WHERE S.CITY='上海'))
12.查询能够提供至少一种红色零件的供应商号。
SELECT DISTINCT SNO FROM SPJ
WHERE PNO IN (SELECT PNO FROM P WHERE COLOR='红')
或者
SELECT DISTINCT SNO FROM SPJ,P
WHERE SPJ.PNO=P.PNO AND COLOR='红'
13.查询为所有工程提供同样零件的供应商号和零件号。
SELECT DISTINCT SNO,PNO FROM SPJ X WHERE NOT EXISTS
(SELECT * FROM J WHERE NOT EXISTS
(SELECT * FROM SPJ WHERE SNO=X.SNO AND PNO=X.PNO AND JNO=J.JNO))
14.查询提供给上海的所有工程的零件号。
SELECT PNO FROM P WHERE NOT EXISTS
(SELECT * FROM J WHERE CITY='上海' AND NOT EXISTS
(SELECT * FROM SPJ WHERE PNO = P.PNO AND JNO=J.JNO))
15.查询由供应商号为Sl 的供应商提供所有零件的工程号。
SELECT JNO FROM J WHERE NOT EXISTS
(SELECT * FRoM SPJ x WHERE x.SNO='S1' AND NOT EXISTS
(SELECT * FROM SPJ Y WHERE Y.PNO=X.PNO AND Y.JNO=J.JNO))
16.查询所需的零件总数大于1000 的工程号和工程名。
SELECT JNO,SUM(QTY) AS SUM_QTY FROM SPJ
GROUP BY JNO HA VING SUM(QTY)>1000
(17)删除所有红色零件和对应的SPJ记录。
DELETE FROM SJP WHERE PNO IN (SELECT PNO FROM P WHERE COLOR='红') (18)把所有红色零件改成橙色。
UPDATE P SET COLOR='橙' WHERE PNO IN (SELECT PNO FROM P WHERE COLOR='红') (19)在SPJ表中加入一行记录，其中SNO为S1，JNO为J3，PNO为P3，QTY为500。
INSERT INTO SPJ V ALUES('S1','J3','P3',500)
(20)建立名为SVIEW的视图，查询由供应商号为S1的供应商提供零件的工程号、工程名和城市。
CREATE VIEW SVIEW(工程号,工程名,城市)
AS
SELECT JNO,JNAME,CITY
FROM J
WHERE JNO IN(SELECT JNO FROM SPJ WHERE SNO='S1')。
