1. 问题41.1. 最多选修两门课程的学生，没有选修任何课程的学生应该被排除在外1.2. sql

select distinct s.*  from student s, take t where s.sno = t.sno   and s.sno not in ( select t1.sno                        from take t1, take t2, take t3                       where t1.sno = t2.sno                         and t2.sno = t3.sno                         and t1.cno ＜ t2.cno                         and t2.cno ＜ t3.cno )

1.3. 两次自连接的解决方案避免了聚合运算1.4. 基于SNO的内连接操作能够确保子查询返回的每一行都是针对同一个学生的数据1.5. 子查询就是为了找出选修了3门以上课程的学生1.6. 外层查询则负责返回至少选修了一门课程，并且SNO不存在于子查询返回结果的学生1.7. DB21.8. Oracle1.9. SQL Server1.10. 窗口函数COUNT OVER1.10.1. sql

select distinct sno,sname,age    from (  select s.sno,s.sname,s.age,         count(*) over (           partition by s.sno,s.sname,s.age         ) as cnt    from student s, take t    where s.sno = t.sno         )x  where cnt ＜= 2

1.11. PostgreSQL1.12. MySQL1.13. 聚合函数COUNT判断哪些学生最多选修了两门课程1.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

1.14. 计算出TAKE表中每个SNO出现的次数1.15. STUDENT表和TAKE表的内连接操作能够确保剔除掉没有选修任何课程的学生2. 问题52.1. 年龄最多大于其他两名同学的学生2.1.1. 比其他0个、1个或者2个学生年龄大的学生2.2. sql

select *  from student where 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--- ---------- ---  6 JING        18  4 MAGGIE      19  1 AARON       20  9 GILLIAN     20  8 KAY         20  3 DOUG        20

2.3. 找出比其他3个或更多学生年龄大的学生集合2.3.1. 大于具有传递性2.4. 为提高可读性，使用DISTINCT压缩结果集2.5. 在子查询中使用NOT IN就可以筛选出除了上述4人之外的那些学生2.6. DB22.7. Oracle2.8. SQL Server2.9. 窗口函数DENSE_RANK2.9.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

2.10. 窗口函数DENSE_RANK根据有多少人比当前学生年龄小计算出每个学生对应的排名2.11. DENSE_RANK不仅允许Tie的存在，还能保证名次连续，中间不留空白2.12. PostgreSQL2.13. MySQL2.14. 聚合函数COUNT和关联子查询2.14.1. sql

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

2.15. 聚合函数解决方案使用标量子查询筛选出最多比其他两名学生年龄大的学生3. 问题63.1. 至少选修了两门课程的学生3.2. sql

select *  from student where sno in (select t1.sno from take t1,      take t2 where t1.sno = t2.sno   and t1.cno ＞ t2.cno)SNO SNAME             AGE--- ---------- ----------  1 AARON              20  3 DOUG               20  4 MAGGIE             19  6 JING               18

3.3. 子查询里的SNO相等条件能够确保每个学生只与自己的选课信息相比较3.4. CNO大于比较条件，只有在一个学生至少选修了一门课程的情况下才会成立，否则CNO会等于另一个CNO3.4.1. 只有一门课程，只能和自身比较3.5. DB23.6. Oracle3.7. SQL Server3.8. 窗口函数COUNT OVER3.8.1. sql

select distinct sno,sname,age   from ( select s.sno,s.sname,s.age,        count(*) over (          partition by s.sno,s.sname,s.age        ) as cnt   from student s, take t  where s.sno = t.sno        ) x  where cnt ＞= 2

3.9. 使用STUDENT表的全部列定义分区并执行COUNT OVER操作3.10. 只要保留那些CNT大于或者等于2的行即可3.11. PostgreSQL3.12. MySQL3.13. 聚合函数COUNT3.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

3.14. HAVING子句中使用COUNT筛选出那些选修了两门以上课程的学生4. 问题74.1. 同时选修了CS112和CS114两门课程的学生4.2. sql

select s.*  from student s,       take t1,       take t2 where s.sno = t1.sno   and t1.sno = t2.sno   and t1.cno = 'CS112'   and t2.cno = 'CS114'SNO SNAME       AGE--- ---------- ----  1 AARON        20  3 DOUG         20

4.3. sql

select s.*  from take t1, student s where s.sno   = t1.sno   and t1.cno  = 'CS114'   and 'CS112' = any (select t2.cno                        from take t2                       where t1.sno = t2.sno                         and t2.cno != 'CS114')SNO SNAME       AGE--- ---------- ----  1 AARON        20  3 DOUG         20

4.4. DB24.5. Oracle4.6. SQL Server4.7. 窗口函数MIN OVER和MAX OVER4.7.1. sql

select distinct sno, sname, age   from ( select s.sno, s.sname, s.age,        min(cno) over (partition by s.sno) as min_cno,        max(cno) over (partition by s.sno) as max_cno   from student s, take t  where s.sno = t.sno    and t.cno in ('CS114','CS112')        ) x  where min_cno != max_cno

4.8. PostgreSQL4.9. MySQL4.10. 聚合函数MIN和MAX4.10.1. sql

select s.sno, s.sname, s.age   from student s, take t  where s.sno = t.sno    and t.cno in ('CS114','CS112')  group by s.sno, s.sname, s.age having min(t.cno) != max(t.cno)

4.11. IN列表确保只有选修CS112或CS114，或者同时两门都选了的学生才会被保留下来4.12. 如果一个学生没有同时选修这两门课程，那么MIN(CNO)就会等于MAX(CNO)，进而该学生会被排除在外5. 问题85.1. 至少比其他两位学生年龄大的学生5.2. sql

select distinct s1.*  from student s1,       student s2,       student s3 where s1.age ＞ s2.age   and s2.age ＞ s3.ageSNO SNAME             AGE--- ---------- ----------  1 AARON              20  2 CHUCK              21  3 DOUG               20  5 STEVE              22  7 BRIAN              21  8 KAY                20  9 GILLIAN            20 10 CHAD               21

5.3. DB25.4. Oracle5.5. SQL Server5.6. 窗口函数DENSE_RANK5.6.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

5.7. PostgreSQL5.8. MySQL5.9. 聚合函数COUNT和关联子查询5.9.1. sql

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