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 這些對於我來說太簡單了