select p.*
from professor p,
teach t
where p.lname = t.lname
and p.lname not in (
select t1.lname
from teach t1,
teach t2
where t1.lname = t2.lname
and t1.cno ＞ t2.cno
)
LNAME DEPT SALARY AGE
---------- ---------- ---------- ----------
POMEL SCIENCE 500 65
1.3. 找到講授了兩門以上課程的教授
1.4. 找到講授了至少一門課程但不存在于第一步傳回結果集的教授
1.5. DB2
1.6. Oracle
1.7. SQL Server
1.8. 視窗函數COUNT OVER
1.8.1. sql
select lname, dept, salary, age
from (
select p.lname,p.dept,p.salary,p.age,
count(*) over (partition by p.lname) as cnt
from professor p, teach t
where p.lname = t.lname
) x
where cnt = 1
1.9. PostgreSQL
1.10. MySQL
1.11. 聚合函數COUNT
1.11.1. sql
select p.lname,p.dept,p.salary,p.age
from professor p, teach t
where p.lname = t.lname
group by p.lname,p.dept,p.salary,p.age
having count(*) = 1
1.12. 内連接配接PROFESSOR表和TEACH表能夠確定把不講授任何課程的教授都排除掉
1.13. PARTITION是動态的、更靈活的GROUP BY
2. 問題10
2.1. 隻選修CS112和CS114課程的學生
2.1.1. 隻選了這兩門，沒有選其他課程
2.2. sql
select s.*
from student s, take t
where s.sno = t.sno
and t.cno = 'CS112'
and t.cno = 'CS114'
2.3. sql
select s1.*
from student s1,
take t1,
take t2
where s1.sno = t1.sno
and s1.sno = t2.sno
and t1.cno = 'CS112'
and t2.cno = 'CS114'
and s1.sno not in (
select s2.sno
from student s2,
take t3,
take t4,
take t5
where s2.sno = t3.sno
and s2.sno = t4.sno
and s2.sno = t5.sno
and t3.cno ＞ t4.cno
and t4.cno ＞ t5.cno
)
SNO SNAME AGE
--- ---------- ---
3 DOUG 20
2.4. 找到至少選修了3門課程的學生
2.5. 使用TAKE表的自連接配接找出選修CS112和CS114的學生
2.6. 篩選出選修CS112和CS114，但選修課程數量又不多于兩門的學生
2.7. DB2
2.8. Oracle
2.9. SQL Server
2.10. 視窗函數COUNT OVER和CASE表達式
2.10.1. sql
select sno,sname,age
from (
select s.sno,
s.sname,
s.age,
count(*) over (partition by s.sno) as cnt,
sum(case when t.cno in ( 'CS112', 'CS114' )
then 1 else 0
end)
over (partition by s.sno) as both,
row_number()
over (partition by s.sno order by s.sno) as rn
from student s, take t
where s.sno = t.sno
) x
where cnt = 2
and both = 2
and rn = 1
2.10.2. CASE表達式的結果計算出來之後會被傳遞給視窗函數SUM OVER
2.10.3. 使用了視窗函數ROW_NUMBER，進而避免使用DISTINCT
2.11. PostgreSQL
2.12. MySQL
2.13. CASE表達式和聚合函數COUNT
2.13.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 count(*) = 2
and max(case when cno = 'CS112' then 1 else 0 end) +
max(case when cno = 'CS114' then 1 else 0 end) = 2
2.13.2. STUDENT表和TAKE表的内連接配接能夠確定沒有選修任何課程的學生被排除掉
2.13.3. HAVING子句的COUNT表達式隻保留選修兩門課程的學生
2.13.4. 隻有選修了CS112和CS114兩門課程的學生，其SUM結果才可能等于2
3. 問題11
3.1. 找出比其他兩位學生年齡大的學生
3.1.1. 找出按照年齡從小到大排序排在第三位的學生
3.2. sql
select s5.*
from student s5,
student s6,
student s7
where s5.age ＞ s6.age
and s6.age ＞ s7.age
and s5.sno not in (
select s1.sno
from student s1,
student s2,
student s3,
student s4
where s1.age ＞ s2.age
and s2.age ＞ s3.age
and s3.age ＞ s4.age
)
SNO SNAME AGE
--- ---------- ---
1 AARON 20
3 DOUG 20
9 GILLIAN 20
8 KAY 20
3.3. 找出比3名以上學生年齡大的學生
3.4. 找出比兩位以上學生年齡大的學生，但又不屬于第一步傳回結果集的學生
3.5. DB2
3.6. Oracle
3.7. SQL Server
3.8. 視窗函數DENSE_RANK
3.8.1. sql
select sno,sname,age
from (
select sno,sname,age,
dense_rank()over(order by age) as dr
from student
) x
where dr = 3
3.8.2. 不僅允許Tie的存在，也能保證名次連續，中間不留白白
3.9. PostgreSQL
3.10. MySQL
3.11. 聚合函數COUNT和關聯子查詢
3.11.1. sql
select s1.*
from student s1
where 2 = ( select count(*)
from student s2
where s2.age ＜s1.age )
