1.求部门中哪些人薪水最高：
select ename,sal
from emp join
(
select max(sal) max_sal, deptno
from emp
group by deptno
) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
2.求部门平均薪水的等级：
select deptno, avg_sal, grade
from
(
select deptno, avg(sal) avg_sal
from emp
group by deptno
) t join salgrade s
on (t.avg_sal between s.losal and s.hisal);
3.求部门平均的薪水等级：
select deptno, avg(grade)
from (
select deptno, ename, grade
from emp join salgrade s
on emp.sal between s.losal and s.hisal
) t
group by deptno;
4.求哪些人是经理人：
select ename from emp where empno in (select distinct mgr from emp);
5.不准用聚集函数，求薪水的最高值：
----Distinct 去重复 distinct
select distinct sal
from emp
where sal not in
(
select distinct e1.sal
from emp e1 join emp e2 on (e1.sal < e2.sal) //得出 最大数 以外的数
);
6.求平均薪水最高部门的部门编号：
select deptno, avg_sal
from (
select deptno, avg(sal) avg_sal
from emp
group by deptno
) where avg_sal = (
select max(avg_sal)
from (
select deptno, avg(sal) avg_sal
from emp
group by deptno
)
);
7.求平均薪水最高部门的部门名称：
select deptno,dname
from dept
where deptno = (
select deptno
from (
select deptno, avg(sal) avg_sal
from emp
group by deptno
) where avg_sal = (
select max(avg_sal)
from (
select deptno, avg(sal) avg_sal
from emp
group by deptno
)
)
);
#：聚集函数嵌套（最多只能嵌套两层）：
select deptno,dname
from dept
where deptno = (
select deptno
from (
select deptno, avg(sal) avg_sal
from emp
group by deptno
) where avg_sal = (
select max(avg(sal))
from emp
group by deptno
)
);
8.求平均薪水的等级最低部门的部门名称：
select dname, t1.deptno, grade, avg_sal
from (
select deptno, grade, avg_sal
from (
select deptno, avg(sal) avg_sal
from emp
group by deptno
) t join salgrade s
on (t.avg_sal between s.losal and s.hisal)
)t1 join dept
on (t1.deptno = dept.deptno)
where t1.grade = (
select min(grade)
from (
select deptno, grade, avg_sal
from (
select deptno, avg(sal) avg_sal
from emp
group by deptno
) t join salgrade s
on(t.avg_sal between s.losal and s.hisal)
)
);
#：简化办法-创建视图：
1)create view v$_dept_avg_sal_info as
select deptno, grade, avg_sal
from (
select deptno, avg(sal) avg_sal
from emp
group by deptno
) t join salgrade s
on (t.avg_sal between s.losal and s.hisal);
2)select dname, t1.deptno, grade, avg_sal
from v$_dept_avg_sal_info t1 join dept
on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade)
from v$_dept_avg_sal_info t2
);
3)若权限不足，则：
conn sys/密码 as sysdba;
grant create table, creat view to scott;
9.求比普通员工的最高薪水还要高的经理人名称：
select ename
from emp
where empno in (
select distinct mgr
from emp
where mgr is not null
) and sal > (
select max(sal)
from emp
where empno not in (
select distinct mgr
from emp
where mgr is not null
)
);
10. 求薪水最高的前5名雇员
select ename, sal
from
(
select ename, sal
from emp
order by sal desc
) where rownum <= 5;
11. 求薪水最高的第六到第十名雇员
select ename, sal
from (
select ename, sal, rownum r
from (
select ename, sal
from emp
order by sal desc
)
) where r >=6 and r <= 10;
12.有三个表格S,C,SC
S(SNO, SNAME) 代表（学号，姓名）
C(CNO, CNAM, CTEACHER)代表（课号，课名，教师）
SC(SNO, CNO, SCGRADE)代表（学号，课号，课号成绩）
问题：
⑴.找出没选过“liming”老师课程的所有学生姓名
select sname
from s join sc on (s.sno = sc.sno)
join c on (c.cno = sc.cno)
where c.cteacher <> 'liming';
⑵.列出两门以上（含两门）不及格学生姓名及平均成绩
select sname
from s
where sno in (
select sno
from sc
where scgrade < 60
group by sno
having count(*) >= 2 //having 对统计结果进行筛选
);
⑶.既学过1号课程又学过2号课程所有学生的姓名
select sno from sc where cno = 1 and ---------此行多余？保留意见！
select sname
from s
where sno in (
select sno
from sc
where cno = 1 and sno in (
select sno from sc where cno = 2
)
);
--*********************************连接查询*************************************
select * from salgrade;
--查询所有员工的工资级别，员工名称，工资
select b.grade,a.ename,a.sal
from salgrade b,
(
select ename,sal
from emp
)a
where a.sal between b.losal and b.hisal
;
--查询员工的上司名字
select a.ename,b.ename
from emp a inner join emp b
on a. mgr=b.empno;
--插叙部门编号为10的员工名称和部门名称
select dname,ename
from dept a inner join emp b
on a.DEPTNO=b.DEPTNO and a.deptno=10;
--用自然连接查询部门编号为10的员工名称和部门名称
select dname,ename
from dept natural join emp
where deptno=10;
--查询scott的同事
select emp.ename
from emp
where emp.deptno=
(
select emp.deptno
from emp
where lower(emp.ename)='scott'
);
--查询不在部门编号为10的所有职位
select *
from emp
where job in
(
select job
from emp
where emp.deptno=10
)
and emp.deptno<>10;
--all和any的应用
select *
from emp
where hiredate<all
(
select hiredate from emp
where deptno=10
);
select *
from emp
where hiredate<any
(
select hiredate from emp
where deptno=10
);
--
select ename,job,sal,deptno
from emp
where(deptno,job)=
(
select deptno,job
from emp
where lower(ename)='smith'
);
--
--*********************************************分组查询********************************
--聚合函数
select max(sal),min(sal)
from emp;
select avg(sal),sum(sal)
from emp;
select count(*)
from emp;
--不计算空值
select count(comm)
from emp;
select count(distinct deptno) as distinctdeptcount
from emp;
--组合查询
select deptno, avg(sal),max(sal)
from emp
group by deptno;
--round 和trunc区别
select deptno,round( avg(sal),2),trunc(max(sal),2)
from emp
group by deptno;
--组合查询的应用 ，注意HAVING 和ORDER BY 的顺序
--分组列一定要出现在查询表达式中
select deptno,job, avg(sal),max(sal)
from emp
group by deptno,job
having avg(sal)>2000
order by deptno;
--rollup 和cube的含义
--rollup是对所有数据在查询的基础上横向统计
--cube是对所有数据在查询的基础上立体统计
select deptno,job, avg(sal),max(sal)
from emp
group by rollup( deptno,job);
select job,deptno, avg(sal),max(sal)
from emp
group by rollup( job,deptno);
select job,deptno, avg(sal),max(sal)
from emp
group by cube( job,deptno);
--group sets函数:分组结果集
select job,deptno, avg(sal),max(sal)
from emp
group by grouping sets( job,deptno);
--统计每个部门的平均工资和工资等级，要求显示部门名，平均工资，工资等级
select *from emp;
select *from dept;
select *from salgrade;
select d.grade ,c.dname,c.avgsal
from salgrade d,
(
select dname,avgsal
from dept b,
(
select avg(sal)as avgsal,deptno
from emp
group by deptno
)a
where b.deptno=a.deptno
)c
where c.avgsal between d.losal and hisal;
;