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. 练习
- 查询部门平均工资在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
多表连接
- 写一个查询,显示所有员工姓名,部门编号,部门名称
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标准写法 - 多表连接
- 创建一个员工表和部门表的交叉连接 - 笛卡儿积
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. 练习
- 显示员工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';
- 显示员工KING和FORD管理的员工姓名及其经理姓名
select emp2.ename, emp1.ename
from emp emp1, emp emp2
where emp1.empno = emp2.mgr
and emp1.ename in ('KING', 'FORD');
- 显示员工姓名,参加工作时间,经理名,参加工作时间,要求参加时间比经理早
select emp1.ename, emp1.hiredate, emp2.ename, emp2.hiredate
from emp emp1, emp emp2
where emp1.mgr = emp2.empno
and emp1.hiredate < emp2.hiredate;