数据库系统概论 实验报告答案 实验五：数据库单表查询

实验五：数据库单表查询

一、实验目的

1. 掌握SELECT语句的基本语法和查询条件表示方法；

2. 掌握查询条件表达式和使用方法；

3. 掌握GROUP BY 子句的作用和使用方法；

4. 掌握HAVING子句的作用和使用方法；

5. 掌握ORDER BY子句的作用和使用方法。

二、实验学时

2学时

三、实验要求

1. 熟练数据库查询语句；

2. 掌握数据库查询的实现方式；

3. 完成实验报告；

四、实验内容

以SQL实验4数据库中数据为基础，请使用T-SQL 语句实现以下操作：

1.    列出所有不姓“张”的所有学生的基本信息；

select*

from XSKC.student

where sname not like'张%';

2.    列出姓“王”且全名为3个汉字的学生的基本信息；

select * 

from XSKC.student 

where sname like '王__';

出现的问题:1.name的类型应为 varchar 或者 navarchar 而非char

2.sname的数据由于复制粘贴出现了很多空格 应当删去

3.    显示在1986年以后出生的学生的学号和姓名；

select sno,sname 

from XSKC.student 

where (2021-Sage)>=1986;

4.    按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，并重新命名各列；

select ssex 性别,sno 学号,sname 姓名,sage 年龄,sdept 院系

from XSKC.student

5.    查询没有分配院系的学生的姓名和学号；

select sname,sno

from XSKC.student

where sdept is null;

6.    显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；

select sno,sname,ssex,sage,sdept

from XSKC.student

where sno like '_______[12349]_' or sno like '%[12349]';

7.    列出选修了01号课程的学生的学号和成绩，按成绩的降序排列；

select sno

from XSKC.sc

where cno=1

order by grade desc;

8.    按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；

select sno,cno,grade

from XSKC.sc

where grade between 70 and 80

order by sno,grade desc;

9.    按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：院系为CS显示为计算机系，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明；

select

sdept=case

when sdept='CS' then '计算机系'

when sdept='IS' then '信息系'

when sdept='MA' then '数学系'

when sdept='EN' then '外语系'

when sdept='CM' then '中医系'

when sdept='WM' then '西医系'

else '院系不明' end,sno,sname,ssex,sage

from XSKC.student

order by sdept desc;

10.  显示选修02号课程的成绩前两名的学生学号。

select top 2 sno

from XSKC.sc

where cno='2'

order by grade desc;

11.  列出同时选修“1”号课程和“2”号课程的所有学生的学号；

select sno

from XSKC.sc

where cno='1' and sno in (select sno from XSKC.sc where cno='2');

12.  显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；

SELECT '院系'=sdept, '院系规模'=CASE

WHEN COUNT(*)>=5 THEN '规模很大'

WHEN COUNT(*)>=4 AND COUNT(*)<5 THEN '规模一般'

WHEN COUNT(*)>=2 AND COUNT(*)<4 THEN '规模稍小'

ELSE '规模很小' END

FROM XSKC.student

GROUP BY sdept 

13.  显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；

SELECT COUNT(*) '学生总人数',AVG(sage) '平均年龄'

FROM XSKC.student;

14.  显示选修的课程数大于3的各个学生的选修课程数；

SELECT sno,COUNT(cno) '选修课程数'

FROM XSKC.sc

GROUP BY sno

HAVING COUNT(*)>3;

15.  按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；

SELECT COUNT(*) 总人数,MAX(grade) 最高成绩,MIN(grade) 最低成绩,AVG(grade) 平均成绩

FROM XSKC.sc

GROUP BY cno

ORDER BY cno DESC;

出现问题:char类型 进行按课程号降序排序有误

建议改为int

16.  显示平均成绩大于“赵菁菁”平均成绩的各个学生的学号、平均成绩；

SELECT sno 学号,AVG(grade) 平均成绩

FROM XSKC.sc

GROUP BY sno

HAVING AVG(grade)>(SELECT AVG(grade) FROM XSKC.sc WHERE sno in

(SELECT sno FROM XSKC.student WHERE sname='赵菁菁'));

17.  显示IS系学生的学号、姓名、性别、年龄，并统计出IS系的学生个数；

select count(sno) 人数

from XSKC.student

where sdept='IS'

go                    

select sno,sname,ssex,sage

from XSKC.student

where sdept='IS'

18.  显示选修课程数最多的学号及选修课程数最少的学号；

SELECT sno 选修课程数最多的学号

FROM XSKC.sc

GROUP BY sno

HAVING COUNT(sno)>= ALL (SELECT COUNT(sno) FROM XSKC.sc GROUP BY sno)

SELECT sno 选修课程数最少的学号

FROM XSKC.sc

GROUP BY Sno

HAVING COUNT(sno)<= ALL (SELECT COUNT(sno) FROM XSKC.sc GROUP BY sno)

19.  显示每个院系的学生前两条记录，并组成新表ceshi；

SELECT a.*

INTO ceshi

FROM XSKC.student a

WHERE sno IN

(SELECT top 2 sno

FROM XSKC.student

WHERE sdept=a.sdept

order by sno)

20.  *显示选修各个课程的及格的人数；

select COUNT

(case

when grade>=60

then '及格'

end)  

各课程及格人数

from XSKC.sc

group by cno

21.  *显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；

select sdept 院系名称,

count(case when ssex='男' then 'nan' end) 男生人数,

count(case when ssex='女' then 'nv' end) 女生人数

from XSKC.student

group by sdept

22.  *列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

SELECT sno,AVG(grade) 平均成绩

FROM XSKC.SC

WHERE grade <60

GROUP BY Sno

Having COUNT(Sno)>=2

注：需要在实验报告册中写出每道题的T-SQL语句。

五、实验步骤

命令方式：在【SQL Server Management Studio】窗口左上方选择【新建查询】按钮，启动SQL编辑器窗口，在光标处输入T-SQL语句，单击【执行】按钮。例如第一题可输入：

select * from student where sname not like '刘%'

仅供参考