实验五答案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,https://www.360docs.net/doc/d419204785.html,o,cname,grade,ccredit

from sc,course

where cname like'数据%'and https://www.360docs.net/doc/d419204785.html,o=https://www.360docs.net/doc/d419204785.html,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)