删除选修c语言课程的女生成绩记录,pta10-1 查询没有选修‘C语言‘课程的学生 (10分)...

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

我是yxh 这些对于我来说太简单了

文章来源: blog.csdn.net，作者：icebearpandagrey，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/lida944104439/article/details/111570779