【数据库系统概论课程作业】实验四：数据库数据查询

实验四：数据库数据查询

一、实验目的
1.掌握使用T-SQL语句管理数据表数据。
2.掌握SELECT语句的基本语法和查询条件表示方法；
3. 掌握查询条件表达式和使用方法；
4. 掌握GROUP BY 子句、HAVING子句的作用和使用方法；
5. 掌握ORDER BY子句的作用和使用方法。
二、实验学时
2学时
三、实验要求

1. 熟练数据库数据更新语句的使用；
2. 掌握数据库查询的实现方式；
3. 完成实验报告；
   四、实验内容
   1.请使用T-SQL 语句实现以下操作：
   1)列出姓“王”且全名为3个汉字的学生的基本信息；
   2)查询在1986年以后出生的学生的学号和姓名；
   3)查询没有分配院系的学生的姓名和学号；
   4)按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；
   5)按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：院系为CS显示为计算机系，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明；
   6)查询同时选修“1”号课程和“2”号课程的所有学生的学号；
   7)查询所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；
   8)查询学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；
   9)查询选修的课程数大于3的各个学生的选修课程数；
   10)查询IS系学生的学号、姓名、性别、年龄，并统计出IS系的学生个数；
   11)查询每个院系的学生前两条记录，并组成新表ceshi；
   12)查询平均成绩大于“赵菁菁”平均成绩的各个学生的学号、平均成绩；
   注：需要在实验报告册中写出每道题的T-SQL语句。

五、实验步骤
命令方式：在【SQL Server Management Studio】窗口左上方选择【新建查询】按钮，启动SQL编辑器窗口，在光标处输入T-SQL语句，单击【执行】按钮。例如第一题可输入：
select * from student where sname not like ‘刘%’

答案

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

use student
select sname
from XSKC.student
where sname like '王__'

2)查询在1986年以后出生的学生的学号和姓名；

use student
select sname
from XSKC.student
where 2022-sage>1986

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

use student
select sname,sno
from XSKC.student
where sdept is null

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

use student
select sno,cno,grade
from XSKC.sc
where grade>=70 and grade<80 
order by cno desc,grade desc

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

use student
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 '西医系'
WHEN Sdept NOT IN ('CS','IS','MA','CM','WM') THEN '不明院系'
end,sno,sname,ssex,sage
from XSKC.student
order by sdept desc

6)查询同时选修“1”号课程和“2”号课程的所有学生的学号；

use student
select sno
from XSKC.sc
where cno='1' and cno='2' 

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

use student
select sdept,count(*) 人数,'院系规模'=
case 
when count(*)>=5 then '规模很大'
when count(*)<5 and count(*)>=4 then '规模一般'
when count(*)<4 and count(*)>=2 then '规模稍小'
when count(*)<2 then '规模很小'
end
from XSKC.student 
where sdept in (
	select sdept
	from XSKC.student
)
group by sdept

8)查询学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；

use student
select '学生总人数'=count(*),'平均年龄'=avg(sage)
from XSKC.student 

9)查询选修的课程数大于3的各个学生的选修课程数；

use student
select sno,count(cno)
from XSKC.sc
group by sno
having count(*)>3

10)查询IS系学生的学号、姓名、性别、年龄，并统计出IS系的学生个数；

use student
select sno,sname,ssex,sage
from XSKC.student
where sdept='is'

select count(*) 'is人数'
from XSKC.student
where sdept='is'

11)查询每个院系的学生前两条记录，并组成新表ceshi；

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

12)查询平均成绩大于“赵菁菁”平均成绩的各个学生的学号、平均成绩；

select sno 学号,avg(grade) 平均成绩
from XSKC.sc  
group by sno 
having avg(grade) >(
	select avg(grade)   
	from XSKC.sc,XSKC.student  
	where XSKC.student.sno=XSKC.sc.sno and XSKC.student.sname='赵菁菁'
)