Oracle 联合查询语句练习

1. 查询人数最多的部门信息

   select *
   from scott.dept
   where no = (
   	select deptno
       from scott.epm
       group by deptno
       having count(*) = (
       	select max(count(*))
           from scott.emp
           group by depton
       )
   );
   

2. 查询工资比员工SMITH 工资高的所有员工的信息

   select * from scott.emp
   where sal > (
   	select sal from scott.epm
       where ename like 'SMITH'
   ) and ename not like 'SMITH';
   

3. 使用union将工资大于2500的员工信息与工作为’ANALYST’ 的员工信息合并

   select * from scott.emp where sal > 2500
   union
   select * from scott.emp job = 'ANALYST';
   

4. 使用minus查询工资大于2500的员工信息,但工作不是’ANALYST’ 的员工信息

   select * from scott.emp where sal > 2500
   minus
   select * from scott.emp job = 'ANALYST';
   

5. 查询比工作是SALESMAN 的所有员工工作早的那些员工的信息；

   select * from scott.emp
   where hiredate < all(
   	select * from scott.emp
       where job = 'SALESMAN'
   );
   

6. 查询入职时间早于其上级领导的所有员工信息

   -- 查询其上级领导的入职时间
   select a.hiredate
   from scott.emp a, scott.emp b
   where b.mgr = a.eno;
   
   select b.* from scott.epm b
   where b.hiredate < (
   	select a.hiredate
   	from scott.emp a
   	where b.mgr = a.eno;
   );
   

7. 查询已经入职25年多的员工基本信息

   select * from scott.emp
   where sysdate >= add_month(hiredate, 25 * 12);
   

8. 查询平均工资低于2000的部门及其员工信息

   select e.eno, e.ename, e.sal, e.deptno, d.depname
   from scott.emp e, scott.dept d
   where e.deptno = d.no and e.sal < (
   	select avg(sal) from scott.emp group by deptno
   );
   

9. 查询员工信息，要求以首字母大写的方式显示所有员工的姓名

   select initcap(lower(ename)) from scott.emp; 
   

10. 查询每个部门中的员工数量、平均工资和平均工作年限

    select count(*), avg(sal), avg((sysdate - hiredate) / 365)
    from scott.emp
    group by deptno;
    

11. 查询工作不是MANAGER和CLERK ，并且工资大于或等于2000的员工信息

    select * from scott.emp
    where job not in ('MANAGER', 'CLERK') and sal >= 2000;
    

12. 查询所有员工工资都大于2000的部门信息

    select * from scott.dept
    where no = (
    	select deptno from scott.emp
        group by deptno
        having min(sal) > 2000
    );
    

13. 查询办公地点坐落于‘NEW YORK’的1981年以前入职的员工的员工编号、员工姓名、工资和入职日期。

    select e.* from scott.emp e, scott.dept d
    where e.dept = d.no and d.loc = 'NEW YORK' and hiredate < to_date('1981-1-1', 'yyyy-mm-dd');
    

14. 查询每个部门中工资最高的员工信息。

    select * from scott.emp
    where sal in (
    	select max(sal)
        from scott.emp
        group by deptno
    );
    

15. 查询所有部门名称是SALES和RESEARCH的员工编号、姓名、工资和工作。

    select * from scott.emp e, scott.dept d
    where e.deptno = d.no and d.deptname in ('SALES', 'RESEARCH');