1. 问题91.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. DB21.6. Oracle1.7. SQL Server1.8. 窗口函数COUNT OVER1.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. PostgreSQL1.10. MySQL1.11. 聚合函数COUNT1.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.agehaving count(*) = 1

1.12. 内连接PROFESSOR表和TEACH表能够确保把不讲授任何课程的教授都排除掉1.13. PARTITION是动态的、更灵活的GROUP BY2. 问题102.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. DB22.8. Oracle2.9. SQL Server2.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 OVER2.10.3. 使用了窗口函数ROW_NUMBER，从而避免使用DISTINCT2.11. PostgreSQL2.12. MySQL2.13. CASE表达式和聚合函数COUNT2.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.agehaving 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结果才可能等于23. 问题113.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. DB23.6. Oracle3.7. SQL Server3.8. 窗口函数DENSE_RANK3.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. PostgreSQL3.10. MySQL3.11. 聚合函数COUNT和关联子查询3.11.1. sql

select s1.* from student s1where 2 = ( select count(*)              from student s2             where s2.age ＜s1.age )