实验五答案SQL语句查询

1、列出所有不姓刘的所有学生
select sname,sno
from student
where sname not like'刘%'
2、列出姓“沈“且全名为3个汉字的学生
select sname
from student
where sname like'沈____'
3、显示在1985年以后出生的基本信息
select sno学号,sname姓名,出生年份=year(getdate())-sage
from student
where year(GETDATE())>1985
4、按照“性别、学号、姓名、年龄、院系“的顺序列出学生信息，其中性别按以下规定显示：性别为男显示为男生，性别为女显示为女生，其它显示为”条件不明“
select ssex=
case
when ssex='男'then'男生'
when ssex='女'then'女生'
else'情况不明'
end,sno,sname,sage,sdept
from student
5、查询出课程名含有‘数据‘字串的所有课程基本信息
select sno,o,cname,grade,ccredit
from sc,course
where cname like'数据%'and o=o
6、显示学号第八位或者第九位是1、2、3、4或9的学生的学号、姓名、性别、年龄、及院系select sno,sname,ssex,sage,sdept
from student
where sno like'2005150[1-4]%'or sno like'2005150[0-9][1-4]'
or sno like'20051509%'or sno like'2005150[0-9]9'
7、列出选修了‘1‘课程的学生，按成绩的降序排列
select student.sno,sname,grade
from student,sc
where Cno='1'
order by Grade desc
8、列出同时选修‘1‘号课程和’2‘号课程的所有学生的学号
select sno,Cno
from sc
where cno=1 or cno=2
9、列出课程表中全部信息，按先选修课的升序排列
select*
from course
order by cpno asc
10、列出年龄超过平均值的所有学生名单，按年龄的降序显示
select sno,sname
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 asc
12、按照院系降序显示所有学生的“院系、学号、姓名、性别、年龄“等信息，其中院系按照以下规定显示：院系为CS显示为计算机，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明
select sno,sname,ssex,sage,sdept=
case
when sdept='CS'then'计算机系'
when sdept='IS'then'信息系'
when sdept='MA'then'数学系'
when sdept='EN'then'外语系'
when sdept='CM'then'西医系'
when sdept not in('CS','IS','MA','EN','CM')then'院系不明'
end
from student
13、显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模“，其中若该院系人数>=5则该字段值为”规模很大“，若该院系人数大于等于4小于5则该字段为”规模一般“，该院系人数大于等于2小于4则该字段值为”规模稍小“，否则显示“规模很小”select sdept,sdept=
case
when COUNT(distinct sno)>=5then'规模很大'
when COUNT(distinct sno)>=4and COUNT(distinct sno)<5 then'规模一般' when COUNT(distinct sno)>=2and COUNT(distinct sno)<4 then'规模小' when COUNT(distinct sno)<2 then'规模很小'
end
from student
group by sdept
14、按照课程号、成绩降序显示课程成绩在70——80之间的学号、课程号及成绩
select sno,cno,grade
from sc
where Grade>=70 and Grade<=80
order by Cno desc,Grade desc
15、显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄“
select学生总人数=COUNT(distinct sno),平均年龄=AVG(sage)
from student
16、显示选修的课程数大于3的各个学生的选修课程数
select sno,选修的课程数=COUNT(Cno)
from sc
group by sno
having COUNT(Cno)>3
17、按照课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均年龄
select cno,总人数=COUNT(distinct sno),最高成绩=MAX(grade),最低成绩
=MIN(grade),平均成绩=AVG(grade)
from sc
group by cno
order by cno desc
18、显示平均成绩大于“200515001“学生平均成绩的各个学生的学号、平均成绩
select sno,平均成绩=AVG(grade)
from sc
group by sno
having AVG(Grade)>(select AVG(Grade)from sc where sno='200515001') 20、显示选修课程数最多的学号及选修课程数最少的学号
select sno,选课数=
case
when MAX(Cno)=COUNT(Cno)then'课程数最多'
when MIN(Cno)=COUNT(Cno)then'课程数最少'
end
from sc
group by sno
having MAX(Cno)=COUNT(Cno)or MIN(Cno)=COUNT(Cno)
22、列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩
select sno,平均成绩=AVG(grade)
from sc
where Grade<60
group by sno
having COUNT(*)>=2
19、显示选修各个课程的及格人数及几个比率
21、显示各个院系男女生人数，其中在结果集中列标题分别为“院系名称、男生人数、女生人数“
select sdept院系名称,
男生人数=sum(case when ssex='男'then 1 else 0 end),
女生人数=sum(case when ssex='女'then 1 else 0 end)。