SQL语句

10-1 查询没有选修’C语言’课程的学生 (10分)

select sno as 学号,sname as 姓名 from stu where not exists
(select * from sc where cno=( select cno from cou where cname = ‘C语言’) and stu.sno=sc.sno ) order by sno asc

10-2 查询S001学生选修而S003学生未选修的课程 (10分)

select distinct cno as 课程号 from sc where cno in(select cno from sc where sno=‘s001’)
and cno not in(select cno from sc where sno=‘s003’)

10-4 查询平均分高于80分的学生 (10分)

select sname from stu where sno in
(select sno from sc group by sno having avg(grade) > 80)

10-5 查询选修张老师讲授所有课程的学生 (10分)

select sname from stu where
not exists(select * from cou where not exists(select * from sc where stu.sno = sc.sno and sc.cno=cou.cno) and teacher=‘张老师’)

10-6 计算并填写学生获得的总学分 (10分)

UPDATE stu,(select sno,sum(credit) SUM
from(
select stu.sno sno,case when sc.grade>=60 then credit else NULL end credit
from stu left outer join sc on stu.sno=sc.sno left outer join cou on sc.cno=cou.cno
group by stu.sno,credit,grade) a
group by sno) B
SET stu.totalcredit = B.SUM
where stu.sno = B.sno

10-7 通过图书表和借阅表，查询图书的借阅情况，要求结果中包括以下几列：账号，条形码，书名和借书日期 (10分)

select 借阅.账号,借阅.条形码,图书.书名,借阅.借书日期 from 图书 join 借阅 on 图书.条形码=借阅.条形码

10-8 查询软件工程专业中年龄最大的同学姓名 (10分)

select distinct sname from stu join major on major.mno = stu.mno
where datediff( now(),stu.birdate ) in
(select max(datediff(now(),stu.birdate)) from stu join major on major.mno = stu.mno )
and major.mname = ‘软件工程’

10-9 查询选修了“C语言”课程，但是没有选修“数据结构”课程的学生 (10分)

select sname from stu where
sno in (select distinct sno from sc where
cno = (select cno from cou where cname =‘C语言’ ))
and sno not in (select distinct sno from sc where
cno = (select cno from cou where cname =‘数据结构’ ))

10-10 查询选修课程超过2门且成绩都在80分以上的学生 (10分)

select stu.sname as 姓名 , stu.mno as 专业,sum(cou.credit) as 总学分
from stu join major on stu.mno = major.mno join sc on stu.sno
=sc.sno join cou on cou.cno = sc.cno
where sc.grade >=80
group by sname,stu.mno having count(sc.cno)>=2

10-11 查询选修人数超过2人且成绩都在60分以上的课程 (10分)

select sc.cno as 课程号 ,cou.cname as 课程名,max(sc.grade) as
最高成绩,min(sc.grade) as 最低成绩, avg(sc.grade) as 平均成绩
from stu join major on stu.mno = major.mno join sc on stu.sno
=sc.sno join cou on cou.cno = sc.cno
where cname not in
(select distinct cname from sc join cou on sc.cno = cou.cno
where sc.grade is null ) and sc.grade>=60
group by sc.cno,cou.cname having count(sc.cno)>=2