SQL数据库期末考试复习题(8)

书上第4章例题：

查询语句的基本语句格式:

Select [distinct] 输出列表 From 表名列表 [where 查询条件]

[Group by 列名1 [having 内部函数表达式]] [Order by 排序表达式[asc|desc]]

【例4.5】 查询XSB表中计算机系各同学的学号、姓名和总学分，对其总学分按以下规则进行替换：若总学分为空值，替换为“尚未选课”；若总学分小于50，替换为“不及格”；若总学分在50与52之间，替换为“合格”；若总学分大于52，替换为“优秀”。列标题更改为“等级”。

22

SELECT 学号, 姓名, 等级= CASE WHEN 总学分 IS NULL THEN '尚未选课' WHEN 总学分 < 50 THEN '不及格' WHEN 总学分 >=50 and 总学分<=52 THEN '合格' ELSE '优秀' END FROM XSB WHERE 专业= '计算机'

【例4.8】 对PXSCJ数据库的XSB表选择姓名、专业和总学分，只返回结果集的前6行。 SELECT TOP 6 姓名,专业,总学分 FROM XSB

SELECT TOP 6 PERCENT 姓名,专业,总学分 FROM XSB 【例4.14】统计学分在50分以上的学生的人数。

SELECT count( 总学分 ) AS '总学分大于50' from xsb WHERE 总学分 >50 (2)

【例4.20】 查询XSB表中学号中倒数第3个数字为1且倒数第1个数在1到5之间的 学生学号，姓名及专业。

SELECT 学号,姓名,专业 FROM XSB

WHERE 学号 LIKE '%1_[12345]'

【例4.22】 查询XSB表中不在1989年出生的学生情况。 SELECT 学号, 姓名, 专业, 出生时间 FROM XSB

WHERE 出生时间 NOT BETWEEN '1989-1-1' and '1989-12-31'

【例4.23】 查询XSB表中专业为“计算机”或“通信工程”或“无线电”的学生的情况。 SELECT * FROM XSB WHERE 专业 IN ('计算机', '通信工程', '无线电') 该语句与下列语句等价： SELECT * FROM XSB WHERE 专业= '计算机' or 专业= '通信工程' or专业='无线电'

查询XSB表中专业不为“计算机” 或“无线电”的学生的情况。 SELECT * FROM XSB WHERE 专业 not IN ('计算机', '无线电' )

【例4.27】 查找选修了课程号为206的课程的学生的情况。 在SQL Server Management Studio中新建查询，并在“查询分析器”窗口中输入查询脚本如下： SELECT * FROM XSB WHERE 学号 IN ( SELECT 学号 FROM CJB WHERE 课程号 = '206') 【例4.28】 查找未选修离散数学的学生的情况。 SELECT * FROM XSB

23

WHERE 学号 NOT IN ( SELECT 学号 FROM CJB WHERE 课程号 IN ( SELECT 课程号 FROM KCB WHERE 课程名 = '离散数学' ) ) 【例4.30】 查找比所有计算机系的学生年龄都大的学生。 SELECT * FROM XSB WHERE 出生时间

【例4.31】 查找课程号206的成绩不低于课程号101的最低成绩的学生的学号。 SELECT 学号 FROM CJB WHERE 课程号 = '206' AND 成绩 !< ANY ( SELECT 成绩 FROM CJB WHERE 课程号 = '101' )

【例4.37】 查找选修了学号为081102同学所选修的全部课程的同学的学号。

分析：本例即要查找这样的学号y，对所有的课程号x，若081102号同学选修了该课，那么y也选修了该课。

SELECT DISTINCT 学号 FROM CJB AS CJ1 WHERE NOT EXISTS ( SELECT * FROM CJB AS CJ2 WHERE CJ2.学号 = '081102' AND NOT EXISTS ( SELECT * FROM CJB AS CJ3 WHERE CJ3. 学号= CJ1.学号 AND CJ3.课程号 = CJ2.课程号) )

【例4.45】 查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。

SELECT XSB.学号, 姓名, 课程名, 成绩 FROM XSB , KCB , CJB WHERE XSB.学号 = CJB.学号 AND KCB.课程号 = CJB.课程号 AND 课程名 = '计算机基础' AND 成绩 >= 80

【例4.55】 求被选修的各门课程的平均成绩和选修该课程的人数。

SELECT 课程号, AVG(成绩) AS '平均成绩' , COUNT(学号) AS '选修人数' FROM CJB GROUP BY 课程号

【例4.57】 在PXSCJ数据库上产生一个结果集，包括各专业每门课程的平均成绩、每门课程的总平均成绩和所有课程的总平均成绩。

SELECT 课程名,专业, AVG(成绩) AS '平均成绩' FROM CJB, KCB, XSB

24

WHERE CJB.课程号 = KCB.课程号 AND CJB.学号 = XSB.学号 GROUP BY 课程名, 专业 WITH ROLLUP

【例4.58】 在PXSCJ数据库上产生一个结果集，包括每个专业的男生、女生人数、总人数及男生总数、女生总数、学生总人数。

SELECT 专业, 性别, COUNT(*) AS '人数' FROM XSB GROUP BY 专业,性别 WITH CUBE

【例4.59】 在PXSCJ数据库上产生一个结果集，包括各专业每门课程的平均成绩、每门课程的总平均成绩、每个专业的总平均成绩和所有课程的总平均成绩。 SELECT 课程名,专业,AVG(成绩) AS '平均成绩' FROM CJB, KCB, XSB WHERE CJB.课程号 = KCB.课程号 AND CJB.学号 = XSB.学号 GROUP BY 课程名,专业 WITH CUBE

【例4.61】 查找平均成绩在85分以上的学生的学号和平均成绩。

SELECT 学号, AVG(成绩) AS '平均成绩' FROM CJB GROUP BY 学号 HAVING AVG(成绩) > =85

【例4.62】 查找选修课程超过2门且成绩都在80分以上的学生的学号。 SELECT 学号 FROM CJB WHERE 成绩 >= 80 GROUP BY 学号 HAVING COUNT(*) > 2

【例4.63】 查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。 SELECT 学号, AVG(成绩) AS '平均成绩‘ FROM CJB WHERE 学号 IN (SELECT 学号 FROM XSB WHERE 专业 = '通信工程‘ ) GROUP BY 学号 HAVING AVG(成绩) > =85

【例4.65】 将计算机专业学生的“计算机基础”课程成绩按降序排列。 SELECT 姓名, 课程名, 成绩 FROM XSB, KCB, CJB WHERE XSB.学号 = CJB.学号 AND CJB.课程号 = KCB.课程号 AND 课程名= '计算机基础' AND 专业= '计算机' ORDER BY 成绩 DESC

【例4.66】 查找通信工程专业学生的学号、姓名、出生时间，并产生一个学生总人数行。 SELECT 学号, 姓名, 出生时间 FROM XSB

25