数据库——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 *
        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
)