数据库实验2_SQL的数据查询

实验2、SQL的数据查询

1. 实验目的

熟悉SQL语句的数据查询语言，能够SQL语句对数据库进行单表查询、连接查询、嵌套查询、集合查询和分组统计查询。

1. 实验内容

实验内容主要是对数据库的数据进行查询操作，包括如下四类查询方式：

1. 单表查询
   1. 集合分组使用集函数进行各项统计。
   2. 对查询结果排序和分组。
   3. 使用DISTINCT保留字消除重复行。
   4. 查询的目标表达式为所有列、指定的列或指定的列的运算三种不同。
   5. 连接查询
      1. 笛卡儿连接和等值连接。
      2. 自连接。
      3. 外连接
      4. 复合条件连接。
      5. 多表连接。
   6. 嵌套查询
      1. 通过实验验证对子查询的两个限制条件。
      2. 体会相关子查询和不相关子查询的不同。
      3. 考察四类谓词的用法，包括：

第一类，IN、NOT IN；

第二类，带有比较运算符的子查询；

第三类，SOME、ANY或ALL谓词的子查询，查询最大值和最小值；

第四类，带有EXISTS谓词的子查询，实现“所有”等情况（如王宏的“所有”课程，“所有”女生选修的课程）

1. 集合运算
   1. 采用逻辑运算符AND或OR来实现集合交和减运算。
   2. 使用保留字UNION进行集合并运算。

1. 实验步骤

以Db_Uni数据库为例，该数据库中有四张如实验1，其中Score是每门课的考试成绩，Scredit是学生所有考试合格课程所获得的积分总数，Ccredit每门课程的学分数。

在数据库中，存在这样的联系：学生可以选择课程，一个课程对应一个教师。在表Reports中保存学生的选课记录和考试成绩。

请先通过DataStudio的SQL终端输入执行SQL语句完成如下符合条件的元组插入后，再对数据库进行有关的查询操作：

--向Students表如下数据

INSERT INTO Students

VALUES ('S01','王建平','WJP@zjut.edu.cn', 23.1,'男'),

        ('S02','刘华','LH@zjut.edu.cn', 24.6,'女'),

        ('S03','范林军','FLJ@zjut.edu.cn', 16.6,'女'),

        ('S04','李伟','LW@zjut.edu.cn', 15.8,'男'),

        ('S26','黄河','HUanghe@zjut.edu.cn', 13.4,'男'),

        ('S52','长江','Changjiang@zjut.edu.cn', 12.4,'男');

--向Teachers表如下数据

INSERT INTO Teachers

VALUES ('T01','刘涛','LT@zjut.edu.cn', 4300),

        ('T02','吴碧艳','WBY@zjut.edu.cn', 2500),

        ('T03','张莹','ZY@zjut.edu.cn', 3000),

        ('T04','张宁雅','ZNY@zjut.edu.cn', 5500),

        ('T05','叶帅','YS@zjut.edu.cn', 3800),

        ('T06','杨光美','YGM@zjut.edu.cn', 3500),

        ('T07','程潜','CQ@zjut.edu.cn', 5000);

--向Courses表如下数据

INSERT INTO Courses

  VALUES ('C01','C++',4),

        ('C02','UML',4),

        ('C03','JAVA',3),

        ('C04','算法分析与设计',3),

        ('C05','数据库原理及应用',3),

        ('C06','数据结构与算法',4),

        ('C07','计算机组成原理',4),

        ('C08','英语',6),

        ('C09','数字生活',2),

        ('C10','音乐鉴赏',2),

        ('C11','体育1',2);

--向Reports表如下数据

INSERT INTO Reports

VALUES ('S01','T01', 'C01',83),

      ('S01','T03', 'C03',85),

      ('S02','T01', 'C01',75),

      ('S02','T02', 'C02',45),

      ('S02','T03', 'C03',NULL),

      ('S02','T04', 'C04',NULL),

      ('S02','T05', 'C05',70),

      ('S02','T04', 'C06',83),

      ('S02','T05', 'C07',90),

      ('S02','T01', 'C08',83),

      ('S02','T02', 'C09',77),

      ('S02','T07', 'C10',83),

      ('S02','T06', 'C11',88),

      ('S03','T01', 'C08',63),

      ('S03','T02', 'C02',93),

      ('S03','T01', 'C01',78),

      ('S04','T06', 'C06',89),

      ('S04','T05', 'C05',93),

      ('S26','T07', 'C10',45),

      ('S26','T04', 'C04',86),

      ('S52','T07', 'C10',91),

      ('S52','T06', 'C11',90),

      ('S52','T05', 'C05',NULL),

      ('S52','T01', 'C08',64),

      ('S52','T02', 'C09',81);

图1.1、Students表

图1.2、Teachers表

图1.3、Courses表

图1.4、Reports表

1. 查询性别为“男”的所有学生的名称并按学号升序排列。
2. 查询学生的选课成绩合格的课程成绩，并把成绩换算为积分。积分的计算公式为：[1+(考试成绩-60)*0.1]*Ccredit。考试成绩>=60 否则=0
3. 查询学分是3或4的课程的名称。
4. 查询所有课程名称中含有“算法”的课程编号。
5. 查询所有选课记录的课程号（不重复显示）。
6. 统计所有老师的平均工资。
7. 查询所有教师的编号及选修其课程的学生的平均成绩，按平均成绩降序排列。
8. 统计各个课程的选课人数和平均成绩。
9. 查询至少选修了三门课程的学生编号和姓名。
10. 查询编号S26的学生所选的全部课程的课程名和成绩。
11. 查询所有选修了“数据库原理及应用”课程的学生编号和姓名。
12. 求出选修了同一个课程的学生对。
13. 求出至少被两名学生选修的课程编号。
14. 查询选修了编号S26的学生所选的某个课程的学生编号。
15. 查询学生的基本信息及选修课程编号和成绩。
16. 查询学号S52的学生的姓名和选修的课程名称及成绩。
17. 查询和学号S52的学生同性别的所有学生资料。
18. 查询所有选课的学生的详细信息。
19. 查询没有学生选的课程的编号和名称。
20. 查询选修了课程名为C++的学生学号和姓名。
21. 找出选修课程UML或者课程C++的学生学号和姓名。
22. 找出和课程UML或课程C++的学分一样课程名称。
23. 查询所有选修编号C01的课程的学生的姓名。
24. 查询选修了所有课程的学生姓名。
25. 利用集合并运算，查询选修课程C++或选择课程JAVA的学生的编号、姓名和积分。
26. 实现集合交运算，查询既选修课程C++又选修课程JAVA的学生的编号、姓名和积分。
27. 实现集合减运算，查询选修课程C++而没有选修课程JAVA的学生的编号
    1. 实验要求

(1) 实验之前请细细阅读实验总体要求与说明指导书

(2)在DataStudio的SQL终端环境中，完成以上实验(1)-(27)步所有SQL操作，并将其中粗体步骤的操作窗口剪贴到实验报告中。

1. 实验结果

学生表

老师表

Courses表

Reports表

2.5.1

1. 查询性别为“男”的所有学生的名称并按学号升序排列。

1. 查询学生的选课成绩合格的课程成绩，并把成绩换算为积分。积分的计算公式为：[1+(考试成绩-60)*0.1]*Ccredit。考试成绩>=60 否则=0

1. 查询学分是3或4的课程的名称。

1. 查询所有课程名称中含有“算法”的课程编号。

1. 查询所有选课记录的课程号（不重复显示）。

1. 统计所有老师的平均工资。

1. 查询所有教师的编号及选修其课程的学生的平均成绩，按平均成绩降序排列。

1. 统计各个课程的选课人数和平均成绩。

1. 查询至少选修了三门课程的学生编号和姓名。

1. 查询编号S26的学生所选的全部课程的课程名和成绩。

1. 查询所有选修了“数据库原理及应用”课程的学生编号和姓名。

1. 求出选修了同一个课程的学生对。

1. 求出至少被两名学生选修的课程编号。

1. 查询选修了编号S26的学生所选的某个课程的学生编号。

1. 查询学生的基本信息及选修课程编号和成绩。

1. 查询学号S52的学生的姓名和选修的课程名称及成绩。

1. 查询和学号S52的学生同性别的所有学生资料。

1. 查询所有选课的学生的详细信息。

1. 查询没有学生选的课程的编号和名称。

1. 查询选修了课程名为C++的学生学号和姓名。

1. 找出选修课程UML或者课程C++的学生学号和姓名。

1. 找出和课程UML或课程C++的学分一样课程名称。

1. 查询所有选修编号C01的课程的学生的姓名。

1. 查询选修了所有课程的学生姓名。

1. 利用集合并运算，查询选修课程C++或选择课程JAVA的学生的编号、姓名和积分。

1. 实现集合交运算，查询既选修课程C++又选修课程JAVA的学生的编号、姓名和积分。

1. 实现集合减运算，查询选修课程C++而没有选修课程JAVA的学生的编号。