SQL Server数据库基础实验——实验四 子查询

实验四 子查询

一 实验目的
（1） 掌握带IN的子查询
（2） 掌握带有比较运算符的子查询
（3） 掌握带有ANY/ALL的子查询
（4） 掌握带有EXISTS的子查询
二 实验要求
利用SQL SERVER 2008书写select语句，利用子查询实现相关任务。
三 实验内容
1、查询与”莫小贝”同个专业的学生信息。

SELECT*
FROM 学生表
WHERE 专业编号 IN(
      SELECT 专业编号
      FROM 学生表
      WHERE 姓名='莫小贝')

2、查找最高分的学生，显示字段为学号，姓名，课程名，成绩。

SELECT 学生表.学号,姓名,课程名,成绩
FROM 学生表, 课程表 ,成绩表 
WHERE 成绩 IN 
      (SELECT MAX(成绩)
      FROM 成绩表
      WHERE   课程名 IN
           (SELECT 课程名
           FROM 课程表
           ) 
      )
      AND 学生表.学号=成绩表.学号 AND 成绩表.课程号=课程表.课程号

3、查找选修了3门课程的同学学号，姓名，课程数目。（in）

SELECT 学生表.学号,姓名,COUNT(课程号)AS 选课数目
FROM 学生表,成绩表
WHERE 学生表.学号 IN
      (SELECT 学号
      FROM 成绩表
      GROUP BY 学号
      HAVING COUNT (课程号)=3)
      AND 学生表.学号=成绩表.学号
GROUP BY  学生表.学号,姓名

4、查找选修了于朵老师的课程的学生姓名。（使用子查询和多表连接查询两种方法）

子查询：
SELECT 姓名
FROM 学生表
WHERE 学号 IN
    (SELECT 学号
    FROM 成绩表
    WHERE 课程号 IN
     (SELECT 课程号
      FROM 授课表
      WHERE 教师号 IN
        (SELECT 教师号
          FROM 教师表
          WHERE 教师表.姓名='于朵'
         )
      )
)

多表连接查询:
SELECT DISTINCT 学生表.姓名/*DISTINCT用于去重*/
FROM  学生表,成绩表,授课表,教师表
WHERE 学生表.学号=成绩表.学号 AND 成绩表.课程号=授课表.课程号 AND 授课表.教师号=教师表.教师号 AND 教师表.姓名 = '于朵'

5、查询有一门及以上的成绩低于吕秀才的最低成绩的学生名字（用min和all两种方法）。

/*过几天有空了更新需要在学习学习*/

6、查找成绩大于80的学生的学号和姓名。（使用exists和连接两种方法）

SELECT 学号，姓名 From 学生表  
where  exists (SELECT 成绩
                  From 成绩表  
                  Where  成绩>80 AND 学生表.学号 =成绩表.学号)

连接：
SELECT DISTINCT 学生表.学号，姓名 From 学生表,成绩表
where  成绩>80 AND 学生表.学号 =成绩表.学号

7、查找没有选课的学生，显示字段为学号和姓名（not exsits和not in两种方法）

SELECT  学号,姓名 From 学生表  
where not exists (SELECT 成绩
                  From 成绩表  
                  Where 学生表.学号 =成绩表.学号)

SELECT 学号,姓名
FROM 学生表 
WHERE 学号 not in
        (SELECT 学号 
        FROM 成绩表 )

8、以学生表、成绩表、课程表做生成表查询，结果保存在新表“学生成绩”中，新表字段包括：学号、姓名、课程名和成绩。

SELECT 学号,姓名,课程名,成绩
  into 学生成绩
  FROM (SELECT 学生表.学号,姓名,课程名,成绩
        FROM 学生表,课程表,成绩表 
        WHERE 学生表.学号=成绩表.学号 AND 成绩表.课程号=课程表.课程号) a

9、在“学生成绩”中查找每位同学课程的最高分结果按照成绩降序排序，显示字段：学号,姓名,课程名,成绩。（exists）

SELECT * 
FROM 学生成绩 a
 WHERE NOT EXISTS /*此处用的是not exists 用exists的就自己探索啦*/
       (SELECT *
          FROM 学生成绩 
          WHERE 姓名=a.姓名 AND 成绩>a.成绩)
          ORDER BY 成绩 DESC

/*再给你们提供一种方法虽然不是要求的方法*/
SELECT * 
FROM 学生成绩
WHERE 成绩 =
(SELECT MAX(成绩)  
FROM 学生成绩 t1 
WHERE t1.姓名 = 学生成绩.姓名)
ORDER BY 成绩 DESC

数据是实验三多表查询的数据，相信做这些题的同学们都有数据，没有的话可以私信或留言，我会发给你们的

之前的有些漏了字段，有些写错了，现在已经更新，很多方法的大家多探索，欢迎私聊探讨