SQL题目2 - oracle

1. 相关子查询

1. 查询比所在职位平均工资高的员工姓名，职位

select ename, job
    from emp emp1
    where sal > ( select avg(sal)
                                from emp emp2
                                where emp2.job = emp1.job);

 

2. 查询工资为其部门最低工资的员工编号，姓名，工资

select empno, ename, sal
    from emp emp1
    where sal = ( select min(sal) 
                                from emp emp2
                                where emp1.deptno = emp2.deptno );

 

3. 查询所有雇员编号，名字和部门名字

select empno, ename, ( select dname from dept where emp.deptno = deptno )
    from emp

 

4. 查询哪些员工是经理

select emp1.*
    from emp emp1
    where ( select count(*) from emp where mgr = emp1.empno  ) > 0;

 

5. 查询哪些员工不是经理

select emp1.*
    from emp emp1
    where ( select count(*) from emp where mgr = emp1.empno  ) = 0;

 

6. 查询每个部门工资最低的两个员工编号，姓名，工资。

select empno, ename, sal
    from emp emp1
    where ( select count(*) 
                     from emp emp2 
                     where emp2.deptno = emp1.deptno 
                        and emp1.sal > emp2.sal  ) < 2
    order by deptno;

 

2. exitsts、not exists操作符

1. 列出至少有一个雇员的所有部门名称

select dname
    from dept
    where exists ( select 1 
                                from emp
                                where deptno = dept.deptno 
                                having count(*) > 0); 

 

2. 列出一个雇员都没有的所有部门名称

select dname
    from dept
    where exists ( select 1
                                from emp
                                where deptno = dept.deptno
                                hacing count(*) = 0 );

 

3. 练习

1. 查询薪水多于他所在部门平均薪水的雇员名字，部门号

select ename, deptno
    from emp
    where  

 

2. 查询员工姓名和直接上级的名字

select ename, ( select ename from emp where   empno = emp1.mgr )
    from emp emp1;

 

3. 查询每个部门工资最高的员工姓名，工资

select ename, sal
    from emp emp1
    where sal = ( select max(sal) 
                              from emp
                              where deptno = emp1.deptno);

 

4. 查询每个部门工资前两名高的员工姓名，工资

select ename, sal
    from emp emp1
    where ( select count(*) 
                      from emp 
                      where emp1.sal < sal 
                        and deptno = emp1.deptno ) < 2
    order by deptno;

 

4. 集合运算

1. 使用多表连接，查询每个部门的部门编号，部门人数，没有人数的部门显示0。

select dept.empno, count( empno )
    from emp, dept
    where dept.deptno = emp.deptno(+)
    group by dept.deptno

 

2. 使用联合运算，查询每个部门的部门编号，部门人数，没有人数的部门显示0。

select emp.deptno, count(*)
    from emp, dept
    where emp.deptno = dept.deptno
    group by emp.deptno
union 
select deptno, 0
    from dept
    where deptno not in ( select deptno 
                                            from emp 
                                            where deptno is not null );

 

3. 使用联合运算，查询10号部门及20号部门的员工姓名，部门编号。

select ename, deptno
    from emp
    where deptno = 10
union
select ename, deptno
    from emp
    where deptno = 20

 

4. 用集合运算，列出不包含job为SALESMAN 的部门的部门号

select deptno
    from emp
    where job != 'SALESMAN'
        and deptno is not null
union
select deptno
    from dept
    where deptno not in ( select deptno from emp );

 

5. 写一个联合查询，列出下面的信息：EMP表中所有雇员的名字和部门编号,不管他们是否属于任何部门。DEPT表中的所有部门编号和部门名称，不管他们是否有员工

select deptno, ename
    from emp
union
select deptno, dname
    from dept;

 

6. 用集合运算查询出职位为SALESMAN和部门编号为10的人员编号、姓名姓名、职位，不排除重复结果

select empno, ename, job
    from emp
    where job = 'SALESMAN'
union all
select empno, ename, job
    from emp
    where deptno = 10;

 

7. 用集合查询出部门为10和20的所有人员编号、姓名、所在部门名称

select empno, ename, (select dname from dept where deptno = 10)
    from emp
    where deptno = 10
union 
select empno, ename, (select dname from dept where deptno = 20)
    from emp
    where deptno = 20;