資料庫查詢題目11-20

資料庫查詢題目11-20

11　用子查詢實作查詢選修“高等數學”課的全部學生的所有課程總成績

12　請用兩種方法實作：查找所有成績都在68分以上的學生姓名

13　查找至少2門成績在80分以上的學生姓名

14　查詢至少有一門課與張三同學所學相同的同學的學号和姓名

15　沒有選修“數學”課的學生的姓名

16　查詢個人總成績小于平均總成績的學生姓名

17　用子查詢實作張三在其各科成績中最高分成績所對應的課程号和成績

18　找出張三的最高分和最低分以及對應的課程名

19　哪些學生的各科成績均高于張三

20　按平均成績從高到低顯示所有學生的“數學”、“英語”、“國文”三門的課程成績 (按如下形式顯示:學生ID,高等數學,計算機數學,英語,有效課程數,有效平均分 )

11　用子查詢實作查詢選修“高等數學”課的全部學生的所有課程總成績

select sid,sum(cmark) from  mark group by sid having sid in (
	select sid from mark where cid = (
		select cid from course where cname='高等數學'
	)
)
order by sid
           

12　請用兩種方法（邏輯思路）實作：查找所有成績都在68分以上的學生姓名

--方法一(直接做)：
select sname from student where sid in (
	select sid from mark group by sid having min(cmark)>68
)
           

--方法二（排除法）：
select distinct sname from student s join mark m on s.sid=m.sid where s.sid not in (
	select sid from mark where cmark<=68
)
           

13　查找至少2門成績在80分以上的學生姓名

select sname from student s join mark m on s.sid=m.sid 
	where cmark>80 group by m.sid,sname having count(*) >=2
           

14　查詢至少有一門課與張三同學所學相同的同學的學号和姓名

step1:先找張三的所選的課程cid

step2:在mark表中找出選課落在step1中的sid

step3:step2的sid換成sname,并去掉張三

select sid,sname from student where sid in(
	select sid from mark where cid in(
		select cid from mark m join student s on s.sid=m.sid 
		where sname='張三') ) and not sname='張三' 
order by sid
           

15　沒有選修“數學”課的學生的姓名

select sname from student where sid not in (
	select sid from mark m join course c on m.cid=c.cid where cname='數學')
           

16　查詢個人總成績小于平均總成績的學生姓名（沒有學生缺考，學生選課相同）

select sname from student s join mark m on s.sid=m.sid 
	group by m.sid,sname having sum(cmark)<(
		select sum(cmark)/count(distinct sid) from mark 
	)
           

17　用子查詢實作張三在其各科成績中最高分成績所對應的課程号和成績

select cid,cmark from mark where sid=(
	select sid from student where sname='張三'
) and cmark=(
		select max(cmark) from mark where sid = (
			select sid from student where sname='張三'
		)
)
           

18　找出張三的最高分和最低分以及對應的課程名

select cname,cmark from mark m join course c on m.cid=c.cid 
where sid = (select sid from student where sname='張三') 
and ((cmark>=all(select cmark from mark m join student s on m.sid = s.sid where sname='張三')) 
or (cmark <= all(select cmark from mark m join student s on m.sid = s.sid where sname='張三')))
--或
select cname,cmark from mark m join course c on m.cid=c.cid 
where sid = (select sid from student where sname='張三') 
and ((cmark=(select max(cmark) from mark m join student s on m.sid = s.sid where sname='張三')) 
or (cmark =(select min(cmark) from mark m join student s on m.sid = s.sid where sname='張三')))
--或
select cname,cmark from mark m join course c on m.cid=c.cid 
where sid = (select sid from student where sname='張三') 
and cmark in ((select max(cmark) from mark m join student s on m.sid = s.sid where sname='張三') 
union (select min(cmark) from mark m join student s on m.sid = s.sid where sname='張三'))
           

19　哪些學生的各科成績均高于張三

--排除有成績比張三低或等的學生
select distinct sid from mark where sid not in (
select y.sid from mark x join mark y on x.cid=y.cid 
where x.sid=(select sid from student where sname='張三') 
and y.cmark<=x.cmark )
           

20　按平均成績從高到低顯示所有學生的“數學”、“英語”、“國文”三門的課程成績 (按如下形式顯示:學生ID,高等數學,計算機數學,英語,有效課程數,有效平均分 )

select sid 學生id,
(select cmark from mark m join course c on m.cid=c.cid where cname='數學' and sid=y.sid ) 數學,
(select cmark from mark m join course c on m.cid=c.cid where cname='國文' and sid=y.sid) 國文,
(select cmark from mark m join course c on m.cid=c.cid where cname='英語' and sid=y.sid) 英語,
count(*) 有效課程數,avg(cmark) 有效平均分 
from  mark y group by sid order by sid