sql 经典试题

 emp dept是oracle中的两张默认表
每天都花上十分钟写一写，保证自己不会忘记啊~

--连接到scott用户
connect scott/tiger;
--查看当前用户下的所有表
select * from tab where tabType='TABLE';
--查看表结构
desc dept;
desc emp;
--1 列出emp表中各部门的部门号，最高工资，最低工资
select deptno as 部门号,max(sal) 最高工资,min(sal) as "最低 工资"
       from emp group by deptno;
--2 列出emp表中各部门job为'CLERK'的员工的最低工资，最高工资
select deptno as 部门号,max(sal) 最高工资,min(sal) as "最低 工资"
       from emp where job='CLERK' group by deptno;

--3 对于emp中最低工资小于1000的部门，列出job为'CLERK'的员工的部门号，最低工资，最高工资
--查询出最低工资小于1000的部门号及最低工资值
--select deptno,min(sal) from emp group by deptno having min(sal)<1000
--查询出最低工资小于1000的部门号
--select deptno from emp group by deptno having min(sal)<1000;
--子查询
select deptno as 部门号,max(sal) 最高工资,min(sal) as "最低 工资"
       from emp where job='CLERK' 
       and deptno in (select deptno from emp group by deptno having min(sal)<1000)
       group by deptno;
--4 写出对上题的另一解决方法

--where+group by+having

select deptno as 部门号,max(sal) 最高工资,min(sal) as "最低 工资"
       from emp where job='CLERK' group by deptno having min(sal)<1000;
--子查询和父查询关联起来,做联接查询
select  deptno as 部门号,max(sal) 最高工资,min(sal) as "最低 工资"
       from emp a where job='CLERK' 
       and 1000>(select min(sal) from emp b where a.deptno=b.deptno)
       group by deptno;

--5 根据部门号由高而低，工资由低而高列出每个员工的姓名，部门号，工资
select ename 姓名,deptno 部门号,sal 工资 from emp order by deptno desc,sal asc;
--6 列出'张三'所在部门中每个员工的姓名与部门号

select ename,deptno from emp where 
       deptno in (select deptno from emp where ename='张三');
       
--select ename,deptno from emp where deptno=20 or deptno=30;
--select ename,deptno from emp where deptno in (20,30);

--7 列出每个员工的姓名，工作，部门号，部门名
--内联接:返回两个表匹配的数据
--1.
select e.ename,e.job,d.deptno,d.dname from emp e
       inner join 
       dept d
       on e.deptno = d.deptno;
--2.
select e.ename,e.job,d.deptno,d.dname from emp e,dept d where e.deptno = d.deptno;
--8 列出emp中工作为'CLERK'的员工的姓名，工作，部门号，部门名
--1.
select e.ename,e.job,d.deptno,d.dname from dept d
       inner join 
       emp e
       on e.deptno = d.deptno
       where e.job='CLERK';
--2.
select e.ename,e.job,d.deptno,d.dname from emp e,dept d 
       where e.deptno = d.deptno and e.job='CLERK';

--9 对于emp中有管理者的员工，列出姓名，管理者姓名(mgr列是管理者员工号）
--内联接的特列:自联接

select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;

--10 对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作
--外联接: 左外联:返回左表所有数据和右表匹配的数据
--1.通用的左外连接写法
select d.dname,d.deptno,e.ename,e.job from dept d
       left join 
       (select * from emp where emp.job='CLERK') e
       on d.deptno=e.deptno;


--2.oracle支持的左外联接写法

select d.dname,d.deptno,e.ename,e.job from dept d,
       (select * from emp where emp.job='CLERK') e
       where d.deptno=e.deptno(+);

--11 对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序


select deptno,ename,sal from emp a
       where a.sal>(select avg(sal) from emp b where a.deptno=b.deptno)
       order by deptno;


--12 对于emp，列出各个部门中平均工资高于本部门平均水平的员工数和部门号，按部门号排序
select deptno,count(*) from emp a
       where a.sal>(select avg(sal) from emp b where a.deptno=b.deptno)
       group by deptno order by deptno;

--13 对于emp中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，按部门号排序
select deptno,count(*) from emp a
       where a.sal>(select avg(sal) from emp b where a.deptno=b.deptno)
       group by deptno having count(*)>1 order by deptno;

--14 对于emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，
--以及工资少于自己的人数
select deptno,ename,sal,
       (select count(empno) from emp b where b.sal<a.sal) as num
       from emp a where (select count(empno) from emp b where b.sal<a.sal)>=5;