近期,笔者学习了数据库单表查询,将实验题目整理了一下,与大家分享~
实验一共有三张表
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
本实验中一道题目有多种解法,有兴趣的伙伴可以尝试其他做法。欢迎评论区留言补充~