SQL進(jìn)階練習(xí)題1-5
大背景和建表秸架、插入語(yǔ)句就不啰嗦了闺魏，參考第一篇。
四張表概要：
- 學(xué)生表
student(sid,sname,sage,ssex) --sid 學(xué)生編號(hào),sname 學(xué)生姓名,sage 出生年月,ssex 學(xué)生性別 - 課程表
course(cid,cname,tid) --cid 課程編號(hào),cname 課程名稱(chēng),tid 教師編號(hào) - 教師表
teacher(tid,tname) --tid 教師編號(hào),tname 教師姓名 -
成績(jī)表
sc(sid,cid,score) --sid 學(xué)生編號(hào),cid 課程編號(hào),score 分?jǐn)?shù)
為了方便查看栗涂，我把四個(gè)表截了圖：
student
course
teacher
sc
題目：
- 查詢(xún)" 01 "課程比" 02 "課程成績(jī)高的所有學(xué)生的學(xué)號(hào)
- 查詢(xún)同時(shí)存在" 01 "課程和" 02 "課程的情況
- 查詢(xún)存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時(shí)顯示為 null )
- 查詢(xún)不存在" 01 "課程但存在" 02 "課程的情況
- 查詢(xún)平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
sql
查詢(xún)" 01 "課程比" 02 "課程成績(jī)高的所有學(xué)生的學(xué)號(hào)
--比較成績(jī)的要素有三個(gè)：成績(jī)锅尘、科目、同一人伦腐。
--成績(jī)和科目在一張表里灾搏，所以需要把sc表別名成兩張表進(jìn)行比較
select a.sid from
(select * from sc where cid='01') a,(select * from sc where cid='02') b
where a.score>b.score and a.sid=b.sid;
--也可以用join語(yǔ)句。from a,b where a.sid=b.sid與join語(yǔ)句區(qū)別在于：join語(yǔ)句有一個(gè)主體表殴蓬，join后輔助表可能有空值匿级，而另一方只顯示都有的
select a.sid from
(select * from sc where cid='01') a join (select * from sc where cid='02') b
on a.sid=b.sid
where a.score>b.score;
查詢(xún)同時(shí)存在" 01 "課程和" 02 "課程的情況
--兩個(gè)答案，第一個(gè)用a做主體表染厅，第二個(gè)從一個(gè)聯(lián)合表查詢(xún)
select a.*,b.* from sc a left join sc b
on a.sid=b.sid
where a.cid='01' and b.cid='02'and
b.sid is not null;
select * from (select * from sc where cid='01')A
left join (select * from sc where cid='02')B on A.sid=B.sid
where B.sid is not null
查詢(xún)存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時(shí)顯示為 null )
select a.*,b.* from sc a left join (select * from sc where cid='02') b
on a.sid=b.sid
where a.cid='01';
查詢(xún)不存在" 01 "課程但存在" 02 "課程的情況
select * from sc where cid='02' and sid not in
(select sid from sc where cid='01');
查詢(xún)平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
select sid,avg(score) avg from sc
group by sid
having avg>60;
