数据库复习

– 查询部门20的员工，每个月的工资总和及平均工资。
SELECT AVG(sal),SUM(sal)
FROM emp
WHERE deptno=20
– 查询工作在CHICAGO的员工人数，最高工资及最低工资。
SELECT COUNT(ename),MAX(sal),MIN(sal)
FROM emp JOIN dept ON (emp.DEPTNO=dept.DEPTNO)
WHERE dept.loc=‘chicago’
– 查询员工表中一共有几种岗位类型。
SELECT COUNT(DISTINCT job)FROM emp
– 查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
SELECT emp.deptno,dname,COUNT(ename),MAX(sal),MIN(sal),SUM(sal),AVG(sal)
FROM emp JOIN dept ON(emp.deptno=dept.deptno)
GROUP BY emp.deptno
– 查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。
SELECT emp.deptno,dname,job,COUNT(ename),MAX(sal),MIN(sal),SUM(sal),AVG(sal)
FROM emp JOIN dept ON(emp.deptno=dept.deptno)
GROUP BY emp.job
!!-- 查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。
SELECT COUNT(*),manager.empno,manager.ename
FROM emp employer LEFT OUTER JOIN emp manager ON (employer.mgr=,manager.empno)
GROUP BY employer.mgr
– 查询部门人数大于2的部门编号，部门名称，部门人数。
SELECT emp.deptno,dname,COUNT(ename)
FROM emp JOIN dept ON(emp.deptno=dept.deptno)
GROUP BY emp.deptno
HAVING COUNT(ename)>2
– 查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。
SELECT dept.deptno,dname,COUNT(ename),AVG(sal)
FROM emp JOIN dept ON(emp.deptno=dept.deptno)
GROUP BY emp.deptno
HAVING AVG(sal)>2000 AND COUNT(ename)>2
ORDER BY COUNT(ename)
– 查询入职日期最早的员工姓名，入职日期
SELECT ename,hiredate FROM emp
WHERE hiredate = (SELECT MIN(hiredate) from emp)
– 查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
SELECT ename,sal,dname
FROM emp JOIN dept ON (emp.deptno=dept.deptno)
WHERE sal>(SELECT sal FROM emp WHERE ename=‘smith’)
AND loc=‘chicago’
– 查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期
SELECT ename,hiredate
FROM emp
WHERE hiredate > ((SELECT MIN(hiredate) from emp WHERE deptno=20))
!!-- 查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数
SELECT emp.deptno,dname,COUNT(ename)
FROM emp JOIN dept ON(emp.deptno=dept.deptno)
WHERE COUNT(ename)>ALL(SELECT AVG(COUNT(ename))FROM emp)
– 查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工
SELECT ename,hiredate
FROM emp
WHERE hiredate >=ANY(SELECT MAX(hiredate) from emp WHERE deptno=10)AND deptno!=10
– 查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
SELECT ename,hiredate
FROM emp
WHERE hiredate >=ALL(SELECT MAX(hiredate) from emp WHERE deptno!=10)AND deptno!=10
– 查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工
SELECT ename,job
FROM emp
WHERE job = ALL(SELECT job from emp )AND deptno!=10
– 查询部门平均工资在2500元以上的部门名称及平均工资。
SELECT dname,AVG(sal)
FROM emp JOIN dept ON (emp.deptno=dept.deptno)
GROUP BY emp.deptno
HAVING AVG(sal)>2500
– 查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

– 查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
– 查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。
– 显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。
– 查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。
SELECT empno,ename,sal
FROM emp

– 查询工资最高的员工姓名和工资。
SELECT ename,sal
FROM emp
WHERE sal=(SELECT MAX(sal) FROM emp )
– 查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
SELECT
– 查询员工工资为其部门最低工资的员工的编号和姓名及工资。
SELECT deptno,empno,ename,sal
FROM emp e1,(SELECT MIN(sal) min FROM emp GROUP BY deptno) e2
WHERE e1.sal=e2.min
– 显示经理是KING的员工姓名，工资。
SELECT ename,sal
FROM emp
WHERE mgr=(SELECT empno from emp WHERE ename=‘king’)
– 显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
SELECT ename,sal,hiredate
FROM emp
WHERE hiredate >(SELECT hiredate from emp WHERE ename=‘smith’)
– 使用子查询的方式查询哪些职员在NEW YORK工作。
SELECT ename
FROM emp e1,(SELECT deptno,loc FROM dept WHERE loc =‘new york’) e2
WHERE e1.deptno=e2.deptno
– 写一个查询显示和员工SMITH工作在同一个部门的员工姓名，入职日期，查询结果中排除SMITH。
SELECT ename,hiredate
FROM emp e1,(SELECT deptno FROM emp WHERE ename=‘smith’ ) e2
WHERE e1.deptno=e2.deptno AND ename!=‘smith’
– 写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
SELECT empno,ename,sal
FROM emp
WHERE sal>ALL(SELECT AVG(sal) FROM emp)
– 显示部门名称和人数
SELECT dname,COUNT(ename)
FROM emp JOIN dept ON(emp.deptno=dept.deptno)
GROUP BY dept.deptno
– 显示每个部门的最高工资的员工
SELECT ename,sal
FROM emp e1,(SELECT deptno, MAX(sal) max FROM emp GROUP BY deptno) e2
WHERE e1.sal=e2.max AND e1.deptno =e2.deptno
– 显示出和员工号7369部门相同的员工姓名，工资
SELECT ename,sal
FROM emp e1,(SELECT empno,ename n1,deptno FROM emp WHERE empno=‘7369’ ) e2
WHERE e1.deptno=e2.deptno
– 显示出和姓名中包含“W”的员工相同部门的员工姓名
SELECT ename
FROM emp e1,(SELECT ename nname,deptno FROM emp WHERE ename LIKE ‘%w%’ OR ename LIKE ‘%w’ OR ename LIKE ‘w%’) e2
WHERE e1.deptno =e2.deptno