简单数据查询

一、实验目的

      熟悉和掌握对数据表中数据的查询操作和SQL命令的使用，学会灵活熟练地使用SQL 语句的各种形式。

二、实验平台

操作系统：Windows x64 

DBMS：SQL Server 2016

三、程序清单

1、简单查询操作

（1）列出所有教授的姓名和工资：

SELECT 姓名,工资

FROM 教工

WHERE 职称= '教授'

（2）列出教授的所有信息。

SELECT  *

FROM 教工

WHERE 职称='教授'

（3）列出教工表中的系编号并消除重复的元组。

SELECT  DISTINCT  系编号

FROM  教工

（4）已知学分=学时/17，计算每一门课程的学分数。

SELECT 课程名称,学时/17  AS 学分

FROM  课程

（5）显示教授的工资和提高10%的工资额。

SELECT 姓名,工资,工资*1.1  AS  '工资*1.1'

FROM  教工 WHERE 职称='教授'

（6）显示田平平同学出生100天的日期。

SELECT  姓名, 出生年月+100  AS  '出生年月+100'

FROM   学生

WHERE  姓名='田平平'

（7）列出教工表中工资在3000元以上的名单。

SELECT 姓名

FROM   教工

WHERE  工资>= 3000

（8）列出学生表中1980年1月1日之后出生的学生名单。

SELECT 姓名

FROM  学生

WHERE 出生年月>= '1980-1-1'

（9）列出学生表中在1980年1月1日之后出生的男同学名单。

SELECT 姓名

FROM  学生

WHERE 出生年月>= '1980-1-1' AND 性别='男'

（10）列出教工表中教授或副教授中工资低于3000元的名单。

SELECT 姓名

FROM  教工

WHERE (职称='教授' OR 职称='副教授')AND 工资<3000

（11）查出所有不是教授的教工姓名。

SELECT 姓名

FROM  教工

WHERE 职称 NOT LIKE '教授'

（12）检索工资在1000元到2000元范围内的职工信息。

SELECT  *  FROM 职工

WHERE 工资 BETWEEN  1000  AND  2000

（13）按学号的升序，显示系编号等于101的学生信息

SELECT  *

FROM    学生

WHERE  系编号=101

ORDER BY 学号

（14）求教工表中教授工资的平均值。

SELECT  AVG (工资) AS 平均工资

FROM  教工

WHERE 职称='教授'

（15）求所有教工的工资总和。

SELECT  SUM (工资)  AS 工资总和

FROM  教工

（16）找出教工中最高工资和最低工资。

SELECT  MAX(工资)AS 最高工资,MIN(工资)AS 最低工资

FROM  教工

（17）显示学生中最早和最晚的出生日期。

SELECT  MIN (出生年月)AS 最早日期,MAX (出生年月) AS 最晚日期

FROM   学生

（18）查询学生的总人数。  

SELECT  COUNT(*)AS 人数

FROM  学生

2、复杂查询

（19）如果要统计成绩表中所有选课的学生人数，即不管一名学生选了几门课程，都只计算一次

SELECT  COUNT (DISTINCT (学号)) AS 人数

FROM  成绩

（20）统计学生表中男生和女生的人数。

SELECT 性别, COUNT (*)AS 人数

FROM  学生

GROUP BY 性别

（21）统计成绩表中，每一门课程的平均成绩

SELECT 课程编号,AVG (分数)AS 平均成绩

FROM  成绩

GROUP BY 课程编号

（22）查询教工表中每一种职称的最高工资和最低工资

SELECT 职称, MAX (工资)AS 最高工资, MIN (工资) AS 最低工资

FROM  教工

GROUP BY 职称

（23）统计成绩表中选修人数超过2以上的课程编号和人数   

SELECT 课程编号,COUNT (*)AS 人数

FROM  成绩

GROUP BY 课程编号

HAVING  COUNT(*)>2

（24）列出成绩表中分数在60以上、选课数大于2且平均分超过70的学号、选课数目和平均分

SELECT 学号,COUNT(课程编号)AS 选课数目, AVG (分数)AS 平均分

FROM 成绩

WHERE 分数>=60

GROUP BY 学号

HAVING COUNT(课程编号)>2 AND AVG(分数) >70

（25）统计各系学生的人数，结果按升序排列

SELECT 系编号,COUNT(学号)AS 人数

FROM 学生

GROUP BY 系编号

ORDER BY COUNT(学号)

（26）按系统计各系学生的平均年龄，结果按降序排列(2020-year(出生年月））

SELECT 系.系编号,AVG(2020-year(出生年月)) AS 平均年龄

FROM 学生,系

WHERE 学生.系编号=系.系编号

GROUP BY 学生.系编号,系.系编号

ORDER BY 平均年龄 DESC 

可以修改年份

(27)查询每门课程的课程名；

SELECT 课程编号,课程名称

FROM 课程

（28）统计每位学生选修课程的门数、平均成绩；

SELECT 姓名,成绩.学号,COUNT(课程编号)AS 选课门数,AVG(分数)AS 平均分

FROM 成绩,学生

GROUP BY 成绩.学号,姓名,学生.学号

Having 成绩.学号=学生.学号

(32) 统计选修每门课程的学生人数及各门课程的平均成绩；

SELECT 课程.课程编号,课程名称,COUNT(*)AS 选课人数,AVG(分数)AS 平均成绩

FROM 成绩,课程

GROUP BY 成绩.课程编号,课程.课程名称,课程.课程编号

Having 课程.课程编号=成绩.课程编号

(33) 找出平均成绩在85分以上的学生，结果按平均成绩的升序排列；

SELECT 姓名,成绩.学号,COUNT(课程编号)AS 选课门数,AVG(分数)AS 平均分

FROM 成绩,学生

GROUP BY 成绩.学号,姓名,学生.学号

Having 成绩.学号=学生.学号 AND AVG(分数)>=75

ORDER BY AVG(分数)

(34) 查询选修了“C101”或“C102”号课程的学生学号和姓名；

SELECT 成绩.学号,姓名           

FROM 成绩,学生

WHERE 课程编号='C101' AND 成绩.学号=学生.学号

UNION

SELECT 成绩.学号,姓名  

FROM 成绩,学生            

WHERE 课程编号='C102' AND 成绩.学号=学生.学号

（35）查询选修了“C101”和“C102”号课程的学生学号和姓名；

SELECT 成绩.学号,姓名           

FROM 成绩,学生

WHERE 课程编号='C101' AND 成绩.学号=学生.学号

INTERSECT

SELECT 成绩.学号,姓名  

FROM 成绩,学生            

WHERE 课程编号='C102' AND 成绩.学号=学生.学号

(36) 查询选修了3门以上课程的学生学号；

SELECT 学号            

FROM 成绩 

GROUP BY 学号              

HAVING COUNT(*)>=3

（37）查询选修课程成绩至少有一门在80分以上的学生学号

SELECT 成绩.学号

FROM 学生,成绩

WHERE 分数>=80

GROUP BY 成绩.学号

HAVING COUNT(课程编号)>=1

实验分析：

（1）常见错误：因为该列没有包含在聚合函数或 GROUP BY 子句中。这是因为即指定 GROUP BY 时，选择列表中任一非聚合表达式内的所有列都应包含在 GROUP BY 列表中，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。

（2）注意：在各个表中插入数据的顺序，首先在父表中插入数据，然后在子表中插入数据。另外，由于表的定义中包含完整性约束的定义，所以，当主码重复，或者外码不是被参照表的有效值时，系统将拒绝插入的数据。

(3)where与having的区别：

• where是一个约束声明，是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据。where字句中不能包含聚组函数。where语句显示特定的行。
• having是一个过滤声明，是筛选满足条件的组，即在分组之后过滤数据。having字句中可以包含聚组函数。having语句显示特定的组。