数据库第三章SQL课后习题及答案

第6题和第7题题目：

第6题答案：

--1.查询考试成绩不及格的学生的学号及成绩
select student.sno,grade
from student,sc
where student.sno=sc.sno and grade<60
go
--2.查询年龄在19-25岁（包括19和25岁）之间的学生的姓名、院系和年龄，并按年龄的降序排序
select sname,dept,age
from student
where age between 19 and 25
order by age
go
--3.查询姓名中含‘国’字的学生档案情况
select *
from student
where sname like '国'
go
--4.按院系查询学生总人数
select count(distinct sno),dept
from student
group by dept
go
--5.计算选修了001号课程的平均成绩和最高分及最低分
select avg(grade) as avg_g,max(grade) as max_g,min(grade) as min_g
from sc
where cno=001
go
--6.求平均成绩在85分以上（含85）的各门课程号及平均成绩
select cno,avg(grade) as avg_g
from sc
group by cno
having avg(grade)>=85
go

第7题答案：

--1.查询'张林'老师所授课程号和课程名
select cno,cname
from c
where teacher='张林'
go
--2.查询选修课程名为'C语言'或'数据库'的学生的学号
select s.sno
from s,sc
where cno in (select cno from c where cname='C语言' or cname='数据库') and s.sno=sc.sno
go
--3.查询'高灵'同学所选修课程的课程号及课程名
select sc.cno,cname
from sc,c
where sc.cno=c.cno and sno in(select sno from s where sname='高灵')
go
--4.查询至少选修课程号为c3和c4的学生姓名
select sname
from s
where sno in (select sno from sc where cno='c2') and sno in(select sno from sc where cno='c3')
go
--5.用EXISTS来查询学习课程号为c5的学生学号与姓名
select sno,sname
from s
where exists(select * from sc where cno='c5' and sc.sno=s.sno)
go
--6.查询不学c2课程的学生姓名与性别
select sname,sex
from s
where not exists(select * from sc where cno='c2' and sc.sno=s.sno)
go