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

一.实验准备

1.复制实验要求文件及“CREATE-TABLES.SQL”文件， 粘贴到本地机桌面。

2．启动SQL-SERVER服务。

3. 运行查询分析器， 点击菜单《文件》/《打开》， 打开CREATE-TABLES.SQL， 并运行， 建立学生数据库STUDB及表STUDENT, COURSE, SC。

4. 点击菜单《文件》/《新建》， 建立一个空查询窗口。开始实验。

5.本次实验环境是xp虚拟机，SQL-SERVER迷你版。

“CREATE-TABLES.SQL”文件 https://www.cnblogs.com/wkfvawl/p/10843788.html

二.实验内容

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

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
    )
)