数据库系统概论实验五——数据库单表查询

一、实验目的

1. 掌握SELECT语句的基本语法和查询条件表示方法；
2. 掌握查询条件表达式和使用方法；
3. 掌握GROUP BY 子句的作用和使用方法；
4. 掌握HAVING子句的作用和使用方法；
5. 掌握ORDER BY子句的作用和使用方法。

二、实验学时

2学时

三、实验要求

1. 熟练数据库查询语句；
2. 掌握数据库查询的实现方式；
3. 完成实验报告；

四、实验内容

以SQL实验4数据库中数据为基础，请使用T-SQL 语句实现以下操作：
1.列出所有不姓“张”的所有学生的基本信息；

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

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

select *
from student
where sname  like '王%' and len(sname)=3

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

     select sno,sname
from student
where 2021-sage>1986

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

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

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

select sno,sname
from student
where sdept='NULL'

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

     select sno,sname,ssex,sage,sdept
from student
where sno like '_______[12349]%'

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

select sno,grade
from SC 
where cno='1'
order by grade desc
![在这里插入图片描述](https://img-blog.csdnimg.cn/e5c4508ea5c340dd93ca44b3180cbb1a.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBARG9nYm9zczIzMw==,size_13,color_FFFFFF,t_70,g_se,x_16#pic_center)

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

```sql
select sno,cno,grade
from SC 
where 70<=grade and grade<=80
order by grade,cno desc

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

SELECT 院系=
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 as 姓名,ssex as 性别,sage as 年龄
FROM student
ORDER BY 院系 desc

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

select top 2 sno
from SC 
where cno='2' 
order by grade desc 

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

select sno
from SC 
where cno='1'  
Intersect
select sno 
from SC 
where cno='2'

方法二：

select sno
from SC 
where cno='1' 
and sno in(select sno 
from SC 
where cno='2')

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

select  院系=sdept,院系规模=
case
when count(sno)>=5 then '规模很大'
when count(sno)>=4 then '规模一般'
when count(sno)>=2 then '规模较小'
else '规模很小'
end
FROM student
where sdept is not NULL
group by sdept 

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

select 学生总人数=count(*),平均年龄=AVG(sage)
from student 

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

      select sno,count(sno)
from SC
group by sno having count(sno)>2

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

select cno,总人数=count(*),最高成绩=max(grade),
最低成绩=min(grade),平均成绩=AVG(grade)
from SC
group by cno
order by cno desc

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

select sno,平均成绩=AVG(grade)
from SC
group by sno
having AVG(grade)>(select AVG(grade)
from SC,student
where sname='赵菁菁')
order by AVG(grade) desc

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

select sno,sname,ssex,sage
from student
select distinct IS学生人数=count(*)
from student 
where sdept='IS'

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

select top 1 sno
from SC
group by sno
order by count(*)
select top 1 sno
from SC
group by sno
order by count(*) desc

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

select student.* into ceshi
from student 
where sno in (select top 2 sno
from student 
where sdept=student.sdept
order by sdept desc)

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

select cno,及格人数=count(*)
from SC
where grade>=60
group by cno

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

select 院系名称=sdept,
男生人数=count(case when ssex='男' then 1 end),
女生人数=count(case when ssex='女'then 1 end)
from student
group by sdept

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

select sno,平均成绩=AVG(grade)
from 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 ‘刘%’