选读SQL经典实例笔记18_Exactly

独木难支

1. 问题9

1.1. 只讲授一门课程的教授

1.2. sql

1. select p.*
2.   from professor p,
3.        teach t
4. where p.lname = t.lname
5.    and p.lname not in (
6. select t1.lname
7.   from teach t1,
8.        teach t2
9. where t1.lname = t2.lname
10.    and t1.cno ＞ t2.cno
11. )
12. LNAME      DEPT           SALARY        AGE
13. ---------- ---------- ---------- ----------
14. 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

1. select lname, dept, salary, age
2.   from (
3. select p.lname,p.dept,p.salary,p.age,
4.        count(*) over (partition by p.lname) as cnt
5.   from professor p, teach t
6. where p.lname = t.lname
7.        ) x
8. where cnt = 1

复制代码

1.9. PostgreSQL

1.10. MySQL

1.11. 聚合函数COUNT

1.11.1. sql

1. select p.lname,p.dept,p.salary,p.age
2.   from professor p, teach t
3. where p.lname = t.lname
4. group by p.lname,p.dept,p.salary,p.age
5. having count(*) = 1

复制代码

1.12. 内连接PROFESSOR表和TEACH表能够确保把不讲授任何课程的教授都排除掉

1.13. PARTITION是动态的、更灵活的GROUP BY

2. 问题10

2.1. 只选修CS112和CS114课程的学生

2.1.1. 只选了这两门，没有选其他课程

2.2. sql

1. select s.*
2.   from student s, take t
3. where s.sno = t.sno
4.    and t.cno = 'CS112'
5.    and t.cno = 'CS114'

复制代码

2.3. sql

1. select s1.*
2.   from student s1,
3.        take t1,
4.        take t2
5. where s1.sno = t1.sno
6.    and s1.sno = t2.sno
7.    and t1.cno = 'CS112'
8.    and t2.cno = 'CS114'
9.    and s1.sno not in (
10. select s2.sno
11.   from student s2,
12.        take t3,
13.        take t4,
14.        take t5
15. where s2.sno = t3.sno
16.    and s2.sno = t4.sno
17.    and s2.sno = t5.sno
18.    and t3.cno ＞ t4.cno
19.    and t4.cno ＞ t5.cno
20. )
21. SNO SNAME      AGE
22. --- ---------- ---
23.   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

1. select sno,sname,age
2.   from (
3. select s.sno,
4.        s.sname,
5.        s.age,
6.        count(*) over (partition by s.sno) as cnt,
7.        sum(case when t.cno in ( 'CS112', 'CS114' )
8.                 then 1 else 0
9.            end)
10.        over (partition by s.sno) as both,
11.        row_number()
12.        over (partition by s.sno order by s.sno) as rn
13.   from student s, take t
14. where s.sno = t.sno
15.        ) x
16. where cnt = 2
17.    and both = 2
18.    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

1. select s.sno, s.sname, s.age
2.   from student s, take t
3. where s.sno = t.sno
4. group by s.sno, s.sname, s.age
5. having count(*) = 2
6.    and max(case when cno = 'CS112' then 1 else 0 end) +
7.        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

1. select s5.*
2.   from student s5,
3.        student s6,
4.        student s7
5. where s5.age ＞ s6.age
6.    and s6.age ＞ s7.age
7.    and s5.sno not in (
8. select s1.sno
9.   from student s1,
10.        student s2,
11.        student s3,
12.        student s4
13. where s1.age ＞ s2.age
14.    and s2.age ＞ s3.age
15.    and s3.age ＞ s4.age
16. )
17. SNO SNAME      AGE
18. --- ---------- ---
19.   1 AARON       20
20.   3 DOUG        20
21.   9 GILLIAN     20
22.   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

1. select sno,sname,age
2.   from (
3. select sno,sname,age,
4.        dense_rank()over(order by age) as dr
5.   from student
6.        ) x
7. where dr = 3

复制代码

3.8.2. 不仅允许Tie的存在，也能保证名次连续，中间不留空白

3.9. PostgreSQL

3.10. MySQL

3.11. 聚合函数COUNT和关联子查询

3.11.1. sql

1. select s1.*
2. from student s1
3. where 2 = ( select count(*)
4.               from student s2
5.              where s2.age ＜s1.age )

复制代码

来源:https://www.cnblogs.com/lying7/p/17603001.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！