数据库单表查询
实验五：数据库单表查询
一、实验目的
1.掌握SELECT语句的基本语法和查询条件表示方法；
2.掌握查询条件表达式和使用方法；
3.掌握GROUP BY 子句的作用和使用方法；
4.掌握HAVING子句的作用和使用方法；
5.掌握ORDER BY子句的作用和使用方法。
请使用T-SQL 语句实现以下操作：
1.列出所有不姓刘的所有学生；
select * from student where Sname not like '刘%
2.列出姓?沈?且全名为3个汉字的学生；
select * from student1 where Sname like'沈__'
3.显示在1985年以后出生的学生的基本信息；
select * from student where YEAR(GETDATE())-Sage>1985
4.按照?性别、学号、姓名、年龄、院系?的顺序列出学生信息，其中性别按
以下规定显示：性别为男显示为男生，性别为女显示为女生，其他显示为?条件不明?；
select 性别= case when Ssex='男' then'男生' when Ssex='女' then'女生' else '条件不明' end,Sno 学号,Sname 码,Sage 年龄,Sdept 院系 from student
5.查询出课程名含有?数据?字串的所有课程基本信息；
select * from course where Cname like '%数据%”
6.显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性
别、年龄及院系；
from student where Sno like '_______[1,2,3,4,9][1,2,3,4,9]%”
7.列出选修了‘1’课程的学生，按成绩的降序排列；
select sc from sc where https://www.wendangku.net/doc/f51276235.html,o='1' order by Grade DES
8.列出同时选修?1?号课程和?2?号课程的所有学生的学号；
select Sno from SC where Cno = '1' Intersetc select Sno
from SC where Cno = '2'
9.列出课程表中全部信息，按先修课的升序排列；
select * from course order by Cpno Asc
10.列出年龄超过平均值的所有学生名单，按年龄的降序显示；
select * from student where Sage> ( select AVG(Sage) from student ) order by Sage DESC
11.按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在
结果集中列标题分别指定为?学号，姓名，性别，出生年份，院系?；select Sno 学号,Sname 姓名,Ssex 性别,YEAR(GETDATE ())-Sage 出生年份,Sdept 所在院系 from student order by YEAR(GETDATE ())-Sage
12.按照院系降序显示所有学生的?院系，学号、姓名、性别、年龄?等信息，
其中院系按照以下规定显示：院系为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 student order by Sdept DESC
13.显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段
?院系规模?，其中若该院系人数>=5则该字段值为?规模很大?，若该院系人数大于等于4小于5则该字段值为?规模一般?，若该院系人数大于等于2小于4则该字段值为?规模稍小?，否则显示?规模很小?；
select Sdept ,院系规模= case when COUNT(Sno)>=5 then'规模很大' when COUNT(Sno)>=4then'规模一般' when COUNT(Sno)>=2then'规模稍小' else '规模很小' end from student where Sdept is not Null group by Sdept
14.按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及
成绩；
select Sno,Cno,Grade from sc where Grade between 70 and 80 order by Cno,Grade DESE
15.显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为
?学生总人数，平均年龄?；
select count(*)学生总人数,AVG(Sage) 平均年龄 from student
16.显示选修的课程数大于3的各个学生的选修课程数；
select Sno 学号,COUNT(Sno)选修课程数from sc group by Sno having COUNT(*)>=3
17.按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；use student select Cno '课程号',COUNT(*)'总人数',MAX(Grade)'最高分
',MIN(Grade)'最低分',AVG(Grade)'平均分' from sc group by Cno order by Cno desc
18.显示平均成绩大于?200515001?学生平均成绩的各个学生的学号、平均成
绩；
use student select Sno '学号',AVG(Grade)'平均成绩' from sc group by Sno having AVG(Grade)>( select AVG(Grade) from sc where Sno='200515001”19.显示选修各个课程的及格的人数、及格比率；
use student select Cno'课程号' ,COUNT(*)'及格人数',cast(cast(COUNT(case when Grade>=60 then 1 end)as float)/COUNT(*)AS float(1))'及格率' from sc group by Cno
20.显示选修课程数最多的学号及选修课程数最少的学号；
use student select Sno '学号',COUNT(*)'选修课程数' from sc group by Sno having COUNT(Cno)>=all ( select COUNT(*) from sc group by Sno ) union select Sno '学号',COUNT(*)'选修课程数' from sc group by Sno having COUNT(Cno)<=all ( select COUNT(*) from sc group by Sno)
21.显示各个院系男女生人数，其中在结果集中列标题分别指定为?院系名称、
男生人数、女生人数?；
select Sdept ,COUNT(case when Ssex='女'then 1 end)'女生人数', COUNT(case when Ssex='男'then 1 end)'男生人数' from student group by Sdept,Ssex 22.列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；select Sno 学号,AVG(Grade)平均成绩from sc group by Sno having COUNT(case when Grade<60 then 1 end)>=2
六、出现问题及解决办法
如：某些查询操作无法执行，如何解决？