数据库单表查询----select、group by、having、order by 的使用

近期，笔者学习了数据库单表查询，将实验题目整理了一下，与大家分享~

实验一共有三张表
student (Sno, Sname, Ssex, Sage ,Sdept)
course (Cno,Cname,Cpno,Ccredit)
sc (Sno,Cno,Grade)

1、列出所有不姓刘的所有学生；
select * from student where Sname not like('刘%')

2、	列出姓“沈”且全名为3个汉字的学生；
  select * from student where Sname like('沈__')
  
3、显示在1985年以后出生的学生的基本信息；
select * from student where Year(getDate())-Sage>1985
#在学数据库时，学生（student）、课程（course）、选课（sc）三张表是常用表。其中学生（student）表中没有出生年份，所以此处要转换一下。

4、	按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示：性别为男显示为男 生，性别为女显示为女 生，其他显示为“条件不明”；
select case when(Ssex='男') then '男生' 
            when(Ssex='女') then '女生' 
        else '条件不明' end Ssex,Sno,Sname,Sage,Sdept from student
        
5、查询出课程名含有“数据”字串的所有课程基本信息；
select * from course where Cname like ('%数据%')

6、显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；
select Sno,Sname,Ssex,Year(GETDATE())-Sage from student where Sno like '_______[1,2,3,4,9]%' or Sno like '________[1,2,3,4,9]%'

7、	列出选修了‘1’课程的学生，按成绩的降序排列；
select * from sc where cno=1 order by Grade DESC   

8、	列出同时选修“1”号课程和“2”号课程的所有学生的学号；
select Sno from sc where cno = 1 or cno = 2 group by sno having count(*) = 2; 

9、	列出课程表中全部信息，按先修课的升序排列；
select * from course order by Cpno ASC

10、列出年龄超过平均值的所有学生名单，按年龄的降序显示；
select Sname,Sage from student where Sage > (select avg(Sage) as 平均年龄 from student) order by Sage Desc

11、按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；
select Sno as 学号,Sname,Ssex,Year(getDate())-Sage as 出生年份,Sdept as 院系 from student order by 出生年份 ASC

12、按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：院系为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 Sdept,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)>=4 and count(Sno)<5  then '规模一般'
	 when count(Sno)>=2 and count(Sno)<4  then '规模稍小'
     else '规模很小' end) from student group by Sdept having Sdept is not null
     
14、按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；
select * from sc where Grade BETWEEN  70 and 80 order by Cno,Grade DESC 

15、显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；
select count(Sno) as 学生总人数,avg(Sage) as 平均年龄 from student

16、显示选修的课程数大于3的各个学生的选修课程数；
select Sno,count(Cno) as 选修课程数 from sc group by Sno having count(Cno)>3

17、按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；
select Cno, count(Sno)as 课程总人数,max(Grade) as 最高成绩,
min(Grade) as 最低成绩,avg(Grade) 平均成绩 
from sc group by Cno order by Cno desc

18、显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平均成绩；
select Sno,avg(Grade) as 平均成绩 from sc group by Sno having avg(Grade)>(select avg(Grade) from sc where Sno='20051001')

19、显示选修各个课程的及格的人数、及格比率；
select Cno,count(Sno) as 及格人数,
sum(case when Grade>60 then 1 else 0 end)*1.0/count(Sno) as 及格率
from sc group by Cno

20、显示选修课程数最多的学号及选修课程数最少的学号；
select Sno from sc group by Sno 
having count(Cno)>=all(select count(Cno) from sc group by Sno)

select Sno from sc group by Sno having 
count(Cno)<=all(select count(Cno) from sc group by Sno)

21、显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；
select Sdept,sum(case when Ssex='男' then 1 else 0 end) as '男生人数',
             sum(case when Ssex='女' then 1 else 0 end) as '女生人数'
	         from student  group by Sdept having Sdept is not null

22、列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；
select Sno,avg(Grade) as 平均分 from sc group by Sno
  having sum(case when Grade <60 then 1 else 0 end)>=2
  

本实验中一道题目有多种解法，有兴趣的伙伴可以尝试其他做法。欢迎评论区留言补充~