 # 1. 示例數據 ## 1.1. student ```sql insert into student val ...
1. 示例數據
1.1. student
insert into student values (1,'AARON',20)
insert into student values (2,'CHUCK',21)
insert into student values (3,'DOUG',20)
insert into student values (4,'MAGGIE',19)
insert into student values (5,'STEVE',22)
insert into student values (6,'JING',18)
insert into student values (7,'BRIAN',21)
insert into student values (8,'KAY',20)
insert into student values (9,'GILLIAN',20)
insert into student values (10,'CHAD',21)
1.2. courses
insert into courses values ('CS112','PHYSICS',4)
insert into courses values ('CS113','CALCULUS',4)
insert into courses values ('CS114','HISTORY',4)
1.3. professor
insert into professor values ('CHOI','SCIENCE',400,45)
insert into professor values ('GUNN','HISTORY',300,60)
insert into professor values ('MAYER','MATH',400,55)
insert into professor values ('POMEL','SCIENCE',500,65)
insert into professor values ('FEUER','MATH',400,40)
1.4. take
insert into take values (1,'CS112')
insert into take values (1,'CS113')
insert into take values (1,'CS114')
insert into take values (2,'CS112')
insert into take values (3,'CS112')
insert into take values (3,'CS114')
insert into take values (4,'CS112')
insert into take values (4,'CS113')
insert into take values (5,'CS113')
insert into take values (6,'CS113')
insert into take values (6,'CS114')
1.5. teach
insert into teach values ('CHOI','CS112')
insert into teach values ('CHOI','CS113')
insert into teach values ('CHOI','CS114')
insert into teach values ('POMEL','CS113')
insert into teach values ('MAYER','CS112')
insert into teach values ('MAYER','CS114')
2. 問題1：沒有選修過CS112課程的學生
2.1. sql
select *
from student
where sno in ( select sno
from take
where cno != 'CS112' )
2.2. sql
select *
from student
where sno not in (select sno
from take
where cno = 'CS112')
2.3. 要記住真正的邏輯否定要求兩個步驟，即為了找出‘哪些人不是’，就要先找出‘哪些人是’，然後再排除掉他們
2.4. PostgreSQL
2.5. MySQL
2.6. 使用CASE表達式和聚合函數MAX標識一個學生是否選修了CS112課程
2.6.1. sql
select s.sno,s.sname,s.age
from student s left join take t
on (s.sno = t.sno)
group by s.sno,s.sname,s.age
having max(case when t.cno = 'CS112'
then 1 else 0 end) = 0
2.7. Oracle
2.7.1. group by解決方案
select s.sno,s.sname,s.age
from student s, take t
where s.sno = t.sno (+)
group by s.sno,s.sname,s.age
having max(case when t.cno = 'CS112'
then 1 else 0 end) = 0
2.7.2. 視窗函數解決方案
select distinct sno,sname,age
from (
select s.sno,s.sname,s.age,
max(case when t.cno = 'CS112'
then 1 else 0 end)
over(partition by s.sno,s.sname,s.age) as takes_CS112
from student s, take t
where s.sno = t.sno (+)
) x
where takes_CS112 = 0
2.8. DB2
2.9. SQL Server
2.10. 使用CASE表達式和視窗函數MAX OVER
2.10.1. sql
select distinct sno,sname,age
from (
select s.sno,s.sname,s.age,
max(case when t.cno = 'CS112'
then 1 else 0 end)
over(partition by s.sno,s.sname,s.age) as takes_CS112
from student s, take t
on (s.sno = t.sno)
) x
where takes_CS112 = 0
2.11. 外連接到TAKE表是為了確保把那些沒有選修任何課程的學生也能被篩選出來
2.12. 調用MAX函數找出最大的CASE表達式返回值
3. 問題2：只選修了CS112和CS114中的一門，而不是兩門都選的學生
3.1. sql
select *
from student
where sno in ( select sno
from take
where cno != 'CS112'
and cno != 'CS114' )
3.2. sql
select *
from student s, take t
where s.sno = t.sno
and t.cno in ( 'CS112', 'CS114' )
and s.sno not in ( select a.sno
from take a, take b
where a.sno = b.sno
and a.cno = 'CS112'
and b.cno = 'CS114' )
3.3. 使用自連接找出同時選修了CS112和CS114的學生
3.4. 使用子查詢從選修了CS112或CS114的學生中把同時選了兩門的學生剔除掉
3.5. DB2
3.6. Oracle
3.7. SQL Server
3.8. CASE表達式和視窗函數SUM OVER
3.8.1. sql
select distinct sno,sname,age
from (
select s.sno,s.sname,s.age,
sum(case when t.cno in ('CS112','CS114') then 1 else 0 end)
over (partition by s.sno,s.sname,s.age) as takes_either_or
from student s, take t
where s.sno = t.sno
)x
where takes_either_or = 1
3.9. PostgreSQL
3.10. MySQL
3.11. CASE表達式和聚合函數SUM
3.11.1. sql
select s.sno,s.sname,s.age
from student s, take t
where s.sno = t.sno
group by s.sno,s.sname,s.age
having sum(case when t.cno in ('CS112','CS114')
then 1 else 0 end) = 1
3.12. 內連接STUDENT表和TAKE表，這樣就排除了那些沒有選修任何課程的學生
3.13. 使用CASE表達式標記一個學生是否選修了這兩門課程中的一門
3.14. 函數SUM會把每個學生對應的1都累加起來
4. 問題3:選修了CS112，而且沒有選修其他課程的學生
4.1. sql
select s.*
from student s, take t
where s.sno = t.sno
and t.cno = 'CS112'
4.2. sql
select s.*
from student s, take t
where s.sno = t.sno
and s.sno not in ( select sno
from take
where cno != 'CS112' )
4.3. 子查詢負責找出至少選修了一門課，但又沒有選修CS112的所有學生
4.4. 外層查詢負責找出選修了一門課程（任意課程），並且不在上述子查詢的返回結果的學生
4.5. STUDENT表和TAKE表之間的連接操作過濾掉沒有選修任何課程的學生
4.6. PostgreSQL
4.7. MySQL
4.8. 使用聚合函數COUNT確保下列查詢返回的學生只選修了一門課程
4.8.1. sql
select s.*
from student s,
take t1,
(
select sno
from take
group by sno
having count(*) = 1
) t2
where s.sno = t1.sno
and t1.sno = t2.sno
and t1.cno = 'CS112'
4.8.2. 使用內嵌視圖T2找出只選修了一門課程的學生
4.8.3. 連接內嵌視圖T2到TAKE表，並且篩選出選修CS112課程的學生
4.8.4. 在內嵌視圖T2和TAKE表連接查詢的基礎上再次連接STUDENT表，找出匹配的學生
4.9. DB2
4.10. Oracle
4.11. SQL Server
4.12. 使用視窗函數COUNT OVER
4.12.1. sql
select sno,sname,age
from (
select s.sno,s.sname,s.age,t.cno,
count(t.cno) over (
partition by s.sno,s.sname,s.age
) as cnt
from student s, take t
where s.sno = t.sno
) x
where cnt = 1
and cno = 'CS112'
