数据库——SQL-SERVER练习（5）供应关系

时间：2019-05-10 18:15:48 阅读：223 评论：0 收藏：0 [点我收藏+]

以下题目用到工程供应数据库关系模式：
供应商(供应商号，供应商名，城市）
        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,COUNT(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
)

技术分享图片

数据库——SQL-SERVER练习（5）供应关系

原文：https://www.cnblogs.com/wkfvawl/p/10845615.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)

数据库——SQL-SERVER练习（5） 供应关系

数据库——SQL-SERVER练习（5）供应关系