SQL题目3 - oracle

1. 分组函数

1. 查询部门20的员工，每个月的工资总和及平均工资。

select sum(sal), avg(sal)
    from emp
    where deptno = 20;

 
2. 查询工作在CHICAGO的员工人数，最高工资及最低工资。

select count(*), max(sal), min(sal)
    from emp
    where deptno = ( select deptno 
                                        from dept
                                        where loc = 'CHICAGO');

 
3. 查询员工表中一共有几种岗位类型

select count( distinct job ) 
    from emp;

 
4. 查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和 ，平均工资

select deptno, 
           ( select dname from dept where deptno = emp.deptno ), 
           count(*), 
           max(sal), 
           min(sal), 
           sum(sal), 
           avg(sal)
    from emp
    group by deptno;

 
5. 查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息

select emp1.empno, emp1.ename, count(emp2.empno)
    from emp emp1, emp emp2
    where emp1.empno = emp2.mgr(+)
    group by  emp1.empno, emp1.ename

 
6. 查询部门人数大于2的部门编号，部门名称，部门人数

select emp.deptno, dname, count(*)
    from emp, dept
    where emp.deptno = dept.deptno
    group by emp.deptno, dname
    where count(*) > 2;

 
7. 查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。

select emp.deptno, dname, count(*) as  people_number , avg(sal)
    from dept, emp
    where dept.deptno = emp.deptno
    group by emp.deptno, dname
    having avg(sal) > 2000 
        and count(*) >  2
    order by people_number asc;

 

2. 练习

1. 查询部门平均工资在2500元以上的部门名称及平均工资

select dname, avg(sal)
    from emp, dept
    where emp.deptno = dept.deptno
    group by emp.deptno, dname
    having avg(sal) > 2500;

 
2. 查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序

select job, avg(sal) worker_sal
    from emp
    where job not like 'SA%'
    group by job
    having avg(sal) > 2500
    order by worker_sal desc;

 
3. 查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。

select (select dname from dept where emp.deptno = deptno), 
            round(min(sal), 0), 
            round(max(sal), 0)
    from emp
    group by deptno
    having count(*) > 2;

 
4. 查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

select job, sum(sal)
    from emp
    where job != 'SALESMAN'
    group by job
    having sum(sal) > 2500;

 
5. 显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排

select emp1.empno, emp1.ename, min(emp2.sal) min_sal
    from emp emp1, emp emp2
    where emp2.mgr = emp1.empno(+)
    group by emp1.empno, emp1.ename
    having min(emp2.sal) >= 3000;
    order by min_sal desc;

 
6. 写一个查询，显示每个部门最高工资和最低工资的差额

select deptno, max(sal), min(sal), max(sal) - min(sal)
    from emp
    group by deptno

 

多表连接

1. 写一个查询，显示所有员工姓名，部门编号，部门名称

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

 
2. .写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金

select ename, loc, comm
    from emp, dept
    where emp.deptno = dept.deptno
        and loc = 'CHICAGO'
        and comm is not null;

 
3. 写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点

select ename, loc
    from emp, dept
    where emp.deptno = dept.deptno
        and ename like '%A%';

 
4. 查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号

select emp1.ename, emp1.empno, emp2.ename, emp2.empno
    from emp emp1, emp emp2, dept
    where emp1.mgr = emp2.empno
        and emp1.deptno = dept.deptno
        and loc in ( 'NEW YORK', 'CHICAGO');

 
5. 第上一题的基础上，添加没有经理的员工King，并按照员工编号排序。

select emp1.ename, emp1.empno, emp2.ename, emp2.empno
    from emp emp1, emp emp2, dept
    where emp1.mgr = emp2.empno(+)
        and emp1.deptno = dept.deptno
        and loc in ( 'NEW YORK', 'CHICAGO')
    order by emp1.empno;

 
2. 查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来

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

 

SQL-99标准写法 - 多表连接

1. 创建一个员工表和部门表的交叉连接 - 笛卡儿积

select emp.*, dept.*
    from emp
    cross join dept;

 
2. 使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期

相同名称、数据类型的列进行等值连接

select ename, dname, hiredate
    from emp natural join dept
     where hiredate > '1-5月-80';

 
3. 使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点

using所使用的列不能使用 表名、别名作为前缀

select ename, dname, loc
    from emp  join  dept using (deptno)
    where loc = 'CHICAGO';

 
4. 使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点

select ename, dname, loc
    from emp join dept
    on emp.deptno = dept.deptno
    where loc = 'CHICAGO';

 
5. 使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来

select emp1.ename, emp2.ename
    from emp emp1
    left outer join emp emp2
    on emp1.mgr = emp2.empno;

 
6. 使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来

select emp1.ename, emp2.ename
    from emp emp2
    right outer join emp emp1
    on emp1.mgr = emp2.empno;

 

4. 练习

1. 显示员工SMITH的姓名，部门名称，直接上级名称

select emp1.ename, dname, emp2.ename
    from emp emp1, emp emp2, dept
    where emp1.mgr = emp2.empno
        and emp1.deptno = dept.deptno
        and emp1.ename = 'SMITH';   

 

2. 显示员工KING和FORD管理的员工姓名及其经理姓名

select emp2.ename, emp1.ename
    from emp emp1, emp emp2
    where emp1.empno = emp2.mgr
        and emp1.ename in ('KING', 'FORD');

 

3. 显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早

select emp1.ename, emp1.hiredate, emp2.ename, emp2.hiredate
    from emp emp1, emp emp2
    where emp1.mgr = emp2.empno
        and emp1.hiredate < emp2.hiredate;