子查询连接查询练习

--1、查询和ford相同部门的员工姓名和雇用日期

select last_name,hire_date from employees where 
department_id=(select department_id from employees where last_name='ford') and last_name<>'ford';

--2、查询工资比公司平均工资高的员工的员工号，姓名和工资。

select employee_id,last_name,salary from employees where 
salary>(select avg(salary) from employees);

--3、查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

select employee_id,last_name from employees 
where department_id in(select department_id from employees where last_name like '%u%') and last_name not like '%u%';

--4、查询在部门的location_id为1700部门工作的员工的员工号

select employee_id from employees where department_id 
in(select department_id from departments where location_id = 1700);

--5、查询管理者是king的员工姓名和工资

select last_name,salary from employees where manager_id in (select employee_id from employees where lower(last_name)='king');

--6、列出至少有一个雇员的所有部门

select d.department_id,count(e.employee_id) count 
from employees e, departments d 
where e.department_id=d.department_id group by d.department_id;

--7、列出薪金比"SMITH"多的所有雇员

select last_name,salary from employees where salary>all(select salary from employees where upper(last_name)='SMITH');

--8、列出所有雇员的姓名及其直接上级的姓名

select e1.last_name "employee",e2.last_name "manager" from employees e1,employees e2 where e1.manager_id=e2.employee_id;

--9、列出入职日期早于其直接上级的所有雇员

select * from employees e1,employees e2 where e1.manager_id=e2.employee_id and hire_date>e2.hire_date;

--10、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门

select d.department_name department,e.last_name employee from DEPARTMENTS d,EMPLOYEES e where d.DEPARTMENT_ID=e.DEPARTMENT_ID(+);

--11、列出所有“CLERK”（办事员）的姓名及其部门名称

select e.last_name,d.department_name from employees e,departments d where e.job_id like '%CLERK%' and e.department_id=d.department_id;

--12、列出各种工作类别的最低薪金，显示最低薪金大于1500的记录

select job_id,min(salary) from employees group by job_id having(min(salary)>1500);

--13、列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号

select ename,job from emp where deptno=(select deptno from dept where dname='SALES');

--14、列出薪金高于公司平均水平的所有雇员

select * from emp where sal>all(select avg(sal) from emp);

--15、列出与“SCOTT”从事相同工作的所有雇员

select * from emp where job=(select job from emp where ename='SCOTT') and ename<>'SCOTT';

--16、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金

select ename,(sal+nvl(comm,0)) pay from emp 
where (sal+nvl(comm,0))=any(select (sal+nvl(comm,0)) from emp where deptno=30);

--17、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金

select ename,(sal+nvl(comm,0)) pay from emp where (sal+nvl(comm,0))>ALL(select (sal+nvl(comm,0)) from emp where deptno=30);

--18、列出每个部门的信息以及该部门中雇员的数量

select d.deptno,d.dname,d.loc,count(*) from dept d,emp e 
where e.deptno=d.deptno group by d.deptno,d.dname,d.loc; 

--19、列出所有雇员的雇员名称、部门名称和薪金

select e.ename,d.dname,e.sal from emp e,dept d where e.deptno=d.deptno;

--20、列出从事同一种工作但属于不同部门的雇员的不同组合

select * from emp e1,emp e2 where e1.job=e2.job and e1.deptno<>e2.deptno;

--21、列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员

select d.deptno,d.dname,d.loc,count(empno) from emp e,dept d 
where e.deptno=d.deptno group by d.deptno,d.dname,d.loc;

--22、列出各种类别工作的最低工资

select distinct deptno,min(sal) from emp group by job;

--23、列出各个部门的MANAGER（经理）的最低薪金

select min(sal) from emp where job='MANAGER' group by deptno;

--24、列出按年薪排序的所有雇员的年薪

select (sal+nvl(comm,0))*12 asn from emp order by asn;

--25、列出薪金水平处于第四位的雇员

select * from (select empno,sal,rank() over(order by sal desc) grade from emp) where grade=4;