数据库——SQL-SERVER练习（2）连接与子查询

 

1查询每门课的课号和先行课的先行课号（自我连接）

SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

SELECT FIRST.CNO,SECOND.CPNO
FROM COURSE FIRST,COURSE SECOND
WHERE FIRST.CPNO=SECOND.CNO

 

 

2 查询每门课的课号，课名，先行课号，先行课名（自我连接）

SELECT FIRST.CNO,FIRST.CNAME,SECOND.CPNO,SECOND.CNAME CPNAME
FROM COURSE FIRST,COURSE SECOND
WHERE FIRST.CPNO*=SECOND.CNO

 

 

3 查询年龄比CS系平均年龄大的学生名（>子查询）

SELECT SNAME
FROM STUDENT
WHERE SAGE >
(
      SELECT AVG(SAGE)
      FROM STUDENT
      WHERE SDEPT='CS'
)

 

 

4 查询选修学生人数>=2的课程名 （IN子查询）

SELECT CNAME
FROM COURSE
WHERE CNO IN
(
      SELECT COUNT(*)
      FROM SC
      GROUP BY CNO
      HAVING COUNT(*)>=2
)

 

 

5   查询所有选修了1号课程的学生姓名（用EXISTS子查询）

SELECT SNAME
FROM STUDENT
WHERE EXISTS
(
      SELECT *
      FROM SC
      WHERE SNO=STUDENT.SNO AND CNO='1'
)

 

 

6  查询所有选修了1号课程的学生姓名（IN 子查询）

SELECT SNAME
FROM STUDENT
WHERE SNO IN
(
      SELECT SNO
      FROM SC
      WHERE CNO='1'
)

 

 

7  查询所有选修了1号课程的学生姓名（用连接）

SELECT SNAME
FROM STUDENT,SC
WHERE  SC.SNO=STUDENT.SNO AND CNO='1'

 

 

8. 查询未选1号课的学生号，学生名（用NOT EXISTS子查询）

SELECT SNO,SNAME
FROM STUDENT
WHERE NOT EXISTS
(
      SELECT *
      FROM SC
      WHERE SNO=STUDENT.SNO AND CNO='1'
)

 

 

9  选修了所有课的学生名
  (查询学生名, 条件是不存在这样的课, 该生不选) :

 

SELECT SNAME
FROM STUDENT
WHERE NOT EXISTS
(
   SELECT *
   FROM COURSE
   WHERE NOT EXISTS
        (
          SELECT *
          FROM SC
          WHERE SNO=STUDENT.SNO
            AND CNO=COURSE.CNO
         )
)

 

10 . 被所有学生选修的课名
  (查询课程名, 条件是不存在这样的学生, 不选该课)

 换个说法，不存在这样的学生, 该课不选？

SELECT CNAME
FROM COURSE
WHERE NOT EXISTS
(
   SELECT *
   FROM STUDENT
   WHERE NOT EXISTS
        (
          SELECT *
          FROM SC
          WHERE SNO=STUDENT.SNO
                AND CNO=COURSE.CNO
         )
)

 

11.选修了95002选修所有课的学生名
查询语义: 查询学生名, 条件是对于95002选了的课中不存在这样的课，该生不选

不存在这样的课, 95002选并且该生不选.

 

SELECT SNAME
FROM STUDENT
WHERE NOT EXISTS
(
   SELECT *
   FROM COURSE
   WHERE EXISTS
   (
      SELECT *
      FROM SC
      WHERE SNO='95002' AND CNO=COURSE.CNO
    )
    AND
    NOT EXISTS
    (
      SELECT *
      FROM SC
      WHERE SNO=STUDENT.SNO AND CNO=COURSE.CNO
    )
)

 

 

 以下题目用到工程供应数据库关系模式：
供应商(供应商号，供应商名，城市）
        S(Sno，Sname，City)
零件（零件号，零件名，零件颜色）
     P(Pno，Pname，Color)
工程(工程号，工程名 )
     J(Jno，Jname)
供应（供应商号，零件号，工程号，供应数量）
     SPJ(Sno，Pno，Jno，Qty)

 

12. 求供应工程号‘J1’零件的供应商名SNAME。(用IN 子查询)
   结果应该是S-A

SELECT SNAME
FROM S
WHERE SNO IN
(
   SELECT SNO
   FROM SPJ
   WHERE JNO='J1'
)

 

 

13. 求供应工程号‘J1’零件的供应商名SNAME。(用连接,  查询结果要消除重复)
   结果应该是S-A

SELECT DISTINCT SNAME
FROM S,SPJ
WHERE S.SNO=SPJ.SNO AND JNO='J1'

 

 

14. 求供应工程号‘J1’零件的供应商名SNAME。(EXISTS子查询)
   结果应该是S-A

SELECT SNAME
FROM S
WHERE EXISTS
(
   SELECT *
   FROM SPJ
   WHERE S.SNO=SPJ.SNO AND JNO='J1'
)

 

 

15求没有供应过零件的供应商名。（NOT IN）
   结果应是S-C, S-D

SELECT SNAME
FROM S
WHERE SNO NOT IN
(
   SELECT SNO
   FROM SPJ
   WHERE QTY!=0
)

 

 

16求没有供应过零件的供应商名。（NOT EXISTS）
   结果应是S-C, S-D

SELECT SNAME
FROM S
WHERE NOT EXISTS
(
   SELECT *
   FROM SPJ
   WHERE S.SNO=SPJ.SNO AND QTY!=0
)

 

 

17. 求使用所有零件的工程名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
   )

)

 

 

18.求至少用了 工程号‘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
    )
)

 

 

19.    查询没有使用北京供应商供应的’红’色零件的工程名(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 ='红'
)

 

 

扫码关注我们

微信号：SRE实战

拒绝背锅 运筹帷幄