数据库——SQL-SERVER练习（5）供应关系
以下题⽬⽤到⼯程供应数据库关系模式：
供应商(供应商号，供应商名，城市）
S(Sno，Sname，City)
零件（零件号，零件名，零件颜⾊）
P(Pno，Pname，Color)
⼯程(⼯程号，⼯程名 )
J(Jno，Jname)
供应（供应商号，零件号，⼯程号，供应数量）
SPJ(Sno，Pno，Jno，Qty)
1. 求供应⼯程号‘J1’零件的供应商名SNAME。(⽤IN ⼦查询)
结果应该是S-A
SELECT SNAME
FROM S
WHERE SNO IN
(
SELECT SNO
FROM SPJ
WHERE JNO='J1'
)
2. 求供应⼯程号‘J1’零件的供应商名SNAME。(⽤连接,  查询结果要消除重复)
结果应该是S-A
SELECT DISTINCT SNAME
FROM S,SPJ
WHERE S.SNO=SPJ.SNO AND JNO='J1'
3. 求供应⼯程号‘J1’零件的供应商名SNAME。(EXISTS⼦查询)
结果应该是S-A
SELECT SNAME
FROM S
WHERE EXISTS
(
SELECT*
FROM SPJ
WHERE S.SNO=SPJ.SNO AND JNO='J1'
)
4求没有供应过零件的供应商名。（NOT IN）
结果应是S-C, S-D
SELECT SNAME
FROM S
WHERE SNO NOT IN
(
SELECT SNO
FROM SPJ
WHERE QTY!=0
)
5求没有供应过零件的供应商名。（NOT EXISTS）
结果应是S-C, S-D
SELECT SNAME
FROM S
WHERE NOT EXISTS
(
SELECT*
FROM SPJ
WHERE S.SNO=SPJ.SNO AND QTY!=0
)
6. 求使⽤所有零件的⼯程名JNAME。
(查询⼯程中的⼯程名, 条件是不存在这样的零件, 该⼯程不⽤)
结果应该是J-A
SELECT JNAME
FROM J
WHERE NOT EXISTS
(
SELECT*
FROM P
WHERE NOT EXISTS
(
SELECT*
FROM SPJ
WHERE JNO=J.JNO AND PNO=P.PNO
)
)
7.求⾄少⽤了⼯程号‘J2’ ⼯程所使⽤所有零件的⼯程名JNAME。
(从J中查询JNAME, 条件是不存在这样的零件:  J2使⽤并且该⼯程不⽤)  结果应该是J-A, J-B
SELECT JNAME
FROM J
WHERE NOT EXISTS
(
SELECT*
FROM P
WHERE EXISTS
(
SELECT*
FROM SPJ
WHERE JNO='J2'AND PNO=P.PNO
)
AND
NOT EXISTS
(
SELECT*
FROM SPJ
WHERE JNO=J.JNO AND PNO=P.PNO
)
)
8. 求供应了供应商号为‘S2’所供应所有零件的供应商名SNAME
(查询供应商名，条件是不存在这样的零件:  S2供应并且该供应商不供应)
SELECT SNAME
FROM S
WHERE NOT EXISTS
( SELECT*
FROM P
WHERE EXISTS(
SELECT*
FROM SPJ
WHERE SPJ.SNO='S2'
AND SPJ.PNO=P.PNO
)
AND NOT EXISTS(
SELECT*exists子查询
FROM SPJ
WHERE SPJ.SNO=S.SNO
AND SPJ.PNO=P.PNO
)
)
9. 查询供应了所有⼯程的供应商名。
（查询供应商名，条件是不存在这样的⼯程, 该供应商不供应)
SELECT SNAME
FROM S
WHERE NOT EXISTS
(
SELECT*
FROM J
WHERE NOT EXISTS
(
SELECT*
FROM  SPJ
WHERE S.SNO=SNO
AND J.JNO=JNO
)
)
10.    查询没有使⽤北京供应商供应的’红’⾊零件的⼯程名(NOT IN)
结果应该是J-A, J-B,J-C
SELECT JNAME
FROM J
WHERE JNO NOT IN
(
SELECT JNO
FROM SPJ,S,P
WHERE SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO
AND CITY='北京'AND COLOR ='红'
)
11. 查询供应商号，供应商名，⼯程号，零件号，数量。包括未供应过零件的供应商。SELECT S.SNO,SNAME,SPJ.JNO,SPJ.PNO,QTY
FROM S,SPJ
WHERE S.SNO *=SPJ.SNO
12 查询供应过颜⾊为’⿊’的零件的供应商名
SELECT SNAME
FROM S,P,SPJ
WHERE S.SNO =SPJ.SNO AND P.PNO=SPJ.PNO AND COLOR='⿊'
13查询供应商号，⼯程号，供应零件数量合计
SELECT SNO,JNO,sum(QTY)
FROM SPJ
GROUP BY SNO,JNO
额，下⾯截图中的查询语句写错了。
14，查询⼯程号，⼯程名，供应的次数，供应的零件数量合计SELECT J.JNO,JNAME,COUNT(*),SUM(QTY)
FROM J,SPJ
WHERE J.JNO=SPJ.JNO
GROUP BY J.JNO,JNAME
因为要统计供应次数，要按照供应的对象⼯程号和⼯程名来分组。
15查询与供应商名‘S-C’同城市的供应商名
SELECT SNAME
FROM S
WHERE CITY=
(
SELECT CITY
FROM S
WHERE SNAME='S-C'
)
16查询供应过>=3次的供应商名
SELECT SNAME
FROM S
WHERE SNO IN
(
SELECT SNO
FROM SPJ
GROUP BY SNO
HAVING COUNT(*)>=3
)
17. 查询供应次数⼤于S2供应次数的供应商号SELECT SNO
FROM SPJ
GROUP BY SNO
HAVING COUNT(*)>
(
SELECT COUNT(*)
FROM SPJ
WHERE SNO='S2'
)
18查询供应给⼯程号’J2’ 零件数>=10的供应商名SELECT SNAME
FROM S
WHERE SNO IN
(
SELECT SNO
FROM SPJ
WHERE JNO='J2'
GROUP BY SNO
HAVING SUM(QTY)>=10
)