【SQL】SQL高级查询

student表

take表

• 查询只选了CS112课程，而没有选其他课程的学生信息；

select s.*
 from student s, take t
 where s.sno = t.sno
 and s.sno not in ( select sno
 from take
 where cno != 'CS112' )

• 使用聚合函数 COUNT 判断哪些学生最多选修了两门课程

 from student s, take t
where s.sno = t.sno
group by s.sno,s.sname,s.age
having count(*) <= 2

• 找出年龄最多大于其他两名同学的学生

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

使用窗口函数 DENSE_RANK 找出比其他 0 个、1 个或 2 个学生年龄大的学生。

select sno,sname,age
from (
	select sno,sname,age,
	dense_rank()over(order by age) as dr
	 from student) x
where dr <= 3

• 至少选修了两门课程的学生。

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

使用窗口函数 COUNT OVER 筛选出至少选修了两门课程的学生。

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

• 使用聚合函数 MIN 和 MAX 找出同时选修了 CS112 和 CS114 课程的学生。

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.age6 
having min(t.cno) != max(t.cno)

teach表

professor表

• 找出只讲授一门课程的教授

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

• 找出选修了全部课程的学生

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(t.cno) = (select count(*) from courses)

• 找出比任何其他学生年龄都大的学生

select *
from student
where age = (select max(age) from student)

在内嵌视图中使用窗口函数 MAX OVER 找出年龄最大的学生。

select sno,sname,age
from (
select s.*,
max(s.age)over() as oldest 
from student s
x
where age = oldest

EMP表

• 返回奇数行员工姓名

select ename
from (
select row_number() over (order by ename) rn,
ename
from emp
) x
where mod(rn,2) = 1

mysql

select x.ename
from (
select a.ename,
	(select count(*)
	from emp b
	where b.ename <= a.ename) as rn
	from emp a ) x
where mod(x.rn,2) = 1

• 返回 5 个工资最高的员工的姓名和工资

select ename,sal
from (
	select ename, sal,
	dense_rank() over (order by sal desc) dr 
	from emp 
) x 
where dr <= 5

select ename,sal
from (
	select (select count(distinct b.sal)
	from emp b
	where a.sal <= b.sal) as rnk,
	a.sal,
	a.ename
	from emp a
)
where rnk <= 5

• EMP 表中工资最高和最低的员工

select ename
from emp
where sal in ( (select min(sal) from emp),
(select max(sal) from emp) )