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 這些對于我來說太簡單了
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/239570.html
標籤：其他
上一篇：資料庫三種故障及其恢復手段