1. 問題9

1.1. 只講授一門課程的教授

1.2. sql

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 )