数据库SQL进阶练习3（基于Oracle,个别语法与mysql有差别）

数据库SQL进阶练习3（基于Oracle,个别语法与mysql有差别）

使用scott/tiger用户下的emp表和dept表完成下列练习
–1．列出至少有一个员工的所有部门
select * from dept where dept.deptno in(select deptno from emp);
–思路(select deptno from emp group by deptno having count()>0)
–2．列出薪金比“SMITH”多的所有员工。
select * from emp where sal>(select sal from emp where ename=‘SMITH’);
–3．列出所有员工的姓名及其直接上级的姓名。(自连接)
select p.ename ,p.mgr,pm.ename from emp p ,emp pm where pm.empno=p.mgr;
–4．列出受雇日期晚于其直接上级的所有员工。(自连接)
select * from emp p where p.hiredate-(select hiredate from emp where empno=p.mgr)>0;
–5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(外连接)
select d.dname,e. from dept d left join emp e on d.deptno=e.deptno;
–6．列出所有“CLERK”（办事员）的姓名及其部门名称。
select e.ename,d.dname from emp e , dept d where d.deptno=e.deptno and e.job=‘CLERK’;
–7．列出最低薪金大于1500的各种工作。
select job from emp group by job having min(sal)>1500;
–8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select ename from emp where deptno=(select deptno from dept where dname=‘SALES’);
–9．列出薪金高于公司平均薪金的所有员工。
select e.* from emp e where sal>(select avg(sal) from emp);
–10．列出与“SCOTT”从事相同工作的所有员工。
select e.* from emp e where job=(select job from emp where ename=‘SCOTT’);
–11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select e.ename,e.sal from emp e where sal in(select sal from emp where deptno=‘30’);
–12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select e.ename,e.sal from emp e where sal >(select max(sal) from emp where deptno=‘30’);
–13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptno,count(ename),round(avg(sal),2),
round(avg(sysdate-hiredate),2)as"平均服务期限" from emp group by deptno;
–14．列出所有员工的姓名、部门名称和工资。 内连接
select e.ename,d.dname,e.sal from dept d inner join emp e on d.deptno=e.deptno;
–15.!列出所有部门的详细信息和部门人数。select e.ename,d.* from emp e join dept d on e.deptno=d.deptno;
select * from dept left join (select count(ename),deptno from emp group by deptno)
using(deptno);
–16．列出各种工作的最低工资。
select job,min(sal) from emp group by job;
–17．列出MANAGER（经理）的最低薪金。
select min(sal) from emp where job=‘MANAGER’;
–18．列出所有员工的年工资，按年薪从低到高排序。
select ename,sal*12 “年工资” from emp order by sal;
–19. 查询职员表中的职员姓名、薪水、奖金，使用函数处理空值。 注：NULL参与运算，结果为NULL，所以有些情况需要处理NULL
select ename,sal,nvl(comm,0) from emp;
–20.在使用UNION这类集合操作符时，如果select列表的列名不同，必须使用列位置来排序。
–查询部门表和职员表，列出所有的部门编码和部门名字，以及所有的职员编码和职员名字，并以部门彪马和职员编码
–作为升序排列的标准
select d.deptno,d.dname,e.empno,e.ename from emp e join dept d on d.deptno=e.deptno order by d.deptno,e.empno;

–union交集
SELECT deptno a, dname FROM dept
UNION
SELECT empno a,ename FROM emp
ORDER BY a;
–21.多列分组计算每个部门每个职位的平均薪水和最高薪水
select deptno,job, avg(sal),max(sal) from emp group by deptno,job order by deptno;
–22.查询出薪水比本部门平均薪水高的员工信息
–select * from emp e natural join (select avg(sal)as"平均薪水",deptno from emp group by deptno)pp;
select e.* from emp e where e.sal>(select avg(p.sal)from emp p group by deptno having p.deptno=e.deptno);