数据库实验三 多表查询

实验三 多表查询

1．实验目的

1） 熟悉SQL Server 2008对象资源管理器环境。

2） 掌握基本的SELECT查询及其相关子句的使用。

3） 掌握复杂的SELECT查询，如多表查询、子查询、连接和联合查询。

2．实验内容

1） 启动SQL Server 2008 对象资源管理器环境。

2） 涉及多表的简单查询。

3） 涉及多表的复杂查询。

3. 实验步骤

1） 启动SQL Server对象资源管理器，打开“SQL对象资源管理器”窗口。

2） 在“SQL对象资源管理器”窗口中选择要操作的数据库，如“XSCJ”数据库。

3） 在KC表中查询学分低于3的课程信息，并按课程号升序排列。

   在查询命令窗口中输入以下SQL查询命令并执行：

SELECT * FROM  KC

WHERE  KC.学分<3 

ORDER  BY 课程号

4） 在XS_KC表中按学号分组汇总学生的平均分，并按平均分的降序排列。

SELECT 学号,平均分=AVG(成绩) FROM  XS_KC

GROUP BY  学号 

ORDER BY  平均分 DESC

5） 在XS_KC表中查询选修了3门以上课程的学生学号。

SELECT 学号 FROM XS_KC

GROUP BY  学号

HAVING COUNT(*)>3

6） 按学号对不及格的成绩记录进行明细汇总。

SELECT 学号,课程号,成绩 FROM  XS_KC

WHERE  成绩<60

ORDER BY 学号

COMPUTE  COUNT(成绩)

BY  学号

7） 分别用子查询和连接查询，求107号课程不及格的学生信息。

用子查询：

SELECT  学号,姓名,联系电话  FROM XSQK

WHERE 学号 IN

( SELECT 学号

  FROM XS_KC

  WHERE  课程号=’107’AND  成绩<60)

用连接查询：

SELECT 学号,姓名,联系电话  FROM XSQK 

JOIN XS_KC  ON XSQK.学号=XS_KC.学号

WHERE课程号=’107’AND  成绩<60

8） 用连接查询在XSQK表中查询住在同一寝室的学生，即其联系电话相同

SELECT A.学号,A.姓名,A.联系电话  FROM  XSQK A JOIN XSQK  B

ON  A.联系电话=B.联系电话  WHERE   A.学号!=B.学号

请自已完成以下的查询:

9） 查询XSQK表中所有的系名。

SELECT DISTINCT 所在系 
FROM XSQK

10） 查询有多少同学选修了课程。

SELECT  COUNT(DISTINCT 学号)
FROM XS_KC

11） 查询有多少同学没有选课。

SELECT  COUNT(XSQK.学号)
FROM XSQK
WHERE XSQK.学号 NOT IN (
    SELECT DISTINCT    XSQK.学号
    from XS_KC,XSQK 
    WHERE XS_KC.学号 = XSQK.学号
)

12） 查询与杨颖同一个系的同学姓名。

SELECT 姓名
FROM XSQK
WHERE 所在系=(
SELECT 所在系
FROM XSQK
WHERE XSQK.姓名='杨颖'
) AND 姓名 != '杨颖'

13） 查询选修了课程的学生的姓名、课程名与成绩。

SELECT DISTINCT 姓名,课程名,成绩
FROM XSQK,XS_KC,KC
WHERE XS_KC.学号=XSQK.学号 AND XS_KC.课程号=KC.课程号

14） 统计每门课程的选课人数和最高分。

SELECT 课程号,COUNT(学号) AS 课程人数,MAX(成绩) AS 最高分
FROM XS_KC
GROUP BY 课程号

15） 统计每个学生的选课门数和考试总成绩，并按选课门数的降序排列。

SELECT XSQK.学号 AS 学号,COUNT(XS_KC.课程号) AS 选课门数,SUM(XS_KC.成绩) AS 总成绩
FROM XSQK FULL OUTER JOIN XS_KC ON XSQK.学号 = XS_KC.学号
GROUP BY XSQK.学号
ORDER BY 选课门数 DESC