短实训-数据库基础（二）

1.分组函数

常用的SUM,AVG,MAX,MIN,COUNT

*练习
1.查询最高工资以及最低工资*

SELECT MIN(SAL),MAX(SAL) FROM EMP

2.查询职位SALESMAN的所有员工的平均工资，最低工资，最高工资，工资和**

SELECT AVG(SAL),MIN(SAL),MAX(SAL),SUM(SAL) FROM EMP 
WHERE JOB = 'SALESMAN'

3.查询部门30有多少个员工

SELECT COUNT(*) FROM EMP 
WHERE DEPTNO = 30

4.查询有员工的部门数量

SELECT COUNT(DISTINCT DEPTNO) FROM EMP 

5.查询职位以SALE开头的所有员工的平均工资，最高工资

SELECT AVG(SAL),MAX(SAL) FROM EMP 
WHERE JOB LIKE 'SALE%'

6.查询部门30有多少个员工领取奖金

SELECT COUNT(DEPTNO) FROM EMP 
WHERE DEPTNO = 30 AND COMM IS NOT NULL

SELECT COUNT(COMM) FROM EMP WHERE DEPTNO = 30 

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

SELECT SUM(SAL),AVG(SAL) FROM EMP WHERE DEPTNO = 20

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

SELECT COUNT(EMPNO),MAX(SAL),MIN(SAL) FROM EMP,DEPT 
WHERE LOC = 'CHICAGO' AND EMP.DEPTNO = DEPT.DEPTNO  

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

SELECT COUNT(DISTINCT JOB) FROM EMP  

4.查询每个部门的平均工资？

SELECT DEPTNO,AVG(SAL) FROM EMP 
GROUP BY DEPTNO

5.查询每个部门的编号，平均工资,员工姓名

SELECT AVG(SAL),DEPTNO,ENAME FROM EMP 
GROUP BY DEPTNO,ENAME

2.GROUP BY 按照指定条件分组

（SELECT语句后的项，除了分组函数所有的必须包含在GROUP BY 子句中）

3.按多个列分组

查询每个部门每个岗位的工资总和

SELECT SUM(SAL) FROM EMP 
GROUP BY DEPTNO,JOB

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

SELECT DEPTNO,DNAME,COUNT(EMPNO),MAX(SAL),MIN(SAL),SUM(SAL),AVG(SAL) FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY E.DEPTNO,DNAME

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

SELECT DEPTNO,DNAME,JOB,COUNT(EMPNO),MAX(SAL),MIN(SAL),SUM(SAL),AVG(SAL) FROM EMP E ,DEPT D 
WHERE E.DEPTNO = D.DEPTNO 
GROUP BY E.DEPTNO,DNAME,JOB

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

SELECT COUNT(E.EMPNO),E.MGR 经理编号,M.ENAME 经理姓名 FROM EMP E,EMP M
WHERE E.MGR = M.EMPNO(+)
GROUP BY E.MGR,M.ENAME

4.HAVING子句

1.查询每个部门最高工资大于2900的部门编号，最高工资

SELECT DEPTNO,MAX(SAL) FROM EMP HAVING MAX(SAL)>2900 GROUP BY DEPTNO

2.查询每个部门的部门编号，部门名称，部门人数

SELECT DEPTNO,DNAME,COUNT(EMPNO) FROM EMP,DEPT 
WHERE EMP.DEPTNO = DEPT.DEPTNO
GROUP BU DEPT.DEPTNO,DNAME

3.查询职位不是以SALE开头的每个岗位的工资总和以及岗位,工资总和大于5000的并且按照工资总和降序排序

SELECT SUM(SAL),JOB FROM EMP 
WHERE JOB NOT LIKE 'SALE%' 
HAVING SUM(SAL)>5000 
GROUP BY JOB 
ORDER BY SUM(SAL) DESC

4.显示平均薪水的最大值（函数嵌套）

SELECT  MAX(AVG(SAL)) FROM EMP 
GROUP BY DEPTNO

*练习
1.查询部门人数大于2的部门编号，部门名称，部门人数*

SELECT EMP.DEPTNO,DNAME,COUNT(EMPNO) FROM EMP,DEPT 
WHERE EMP.DEPTNO= DEPT.DEPTNO
HAVING COUNT(EMPNO)>2
GROUP BY EMP.DEPTNO,DNAME

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

SELECT EMP.DEPTNO,DNAME,COUNT(EMPNO),AVG(SAL) FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
HAVING AVG(SAL)>2000 AND COUNT(EMPNO)>2
GROUP BY EMP.DEPTNO,DNAME
ORDER BY COUNT(EMPNO) 

5.子查询

1.查询工资比JONES工资高的员工信息

SELECT ENAME,EMPNO,SAL,JOB FROM EMP 
WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME= 'JONES')

2.查询工资最低的员工姓名
SELECT ENAME FROM EMP WHERE SAL =
(SELECT MIN(SAL) FROM EMP)

3.显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作

SELECT JOB,SAL,ENAME FROM EMP 
WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7369)
AND SAL>(SELECT SAL FROM EMP WHERE EMPNO = 7876)

4.查询工资最低的员工姓名，岗位及工资（子查询中使用组函数）

SELECT ENAME,JOB,SAL FROM EMP 
WHERE SAL =(SELECT MIN(SAL) FROM EMP)

5.查询部门最低工资比20号部门最低工资高的部门编号及最低工资（HAVING子句中使用子查询）

SELECT DEPTNO,MIN(SAL) FROM EMP 
HAVING MIN(SAL)>(SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 20) 
GROUP BY DEPTNO 

6.查询哪个部门的员工人数，高于各部门平均人数

SELECT COUNT(EMPNO),DEPTNO FROM EMP 
GROUP BU DEPTNO 
HAVING COUNT(EMPNO)>
(SELECT AVG(COUNT(EMPNO)) FROM EMP) GROUP BY DEPTNO)

*–练习
1.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称*

SELECT ENAME,SAL,DNAME FROM EMP 
JOIN DEPT USING 

2.查询工资比20部门最低工资高的员工姓名，工资

SELECCT ENAME,SAL FROM EMP 
WHERE SAL>(SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 20)

3.查询部门人数大于所有部门平均人数的部门编号，部门名称，部门人数

SELECT DEPT.DEPTNO,DNAME,COUNT(DEPT.EMPNO) FROM EMP,DEPT 
WHERE EMP.DEPTNO = DEPT.DEPTNO
HAVING COUNT(DEPT.EMPNO)>(SELECT AVG(COUNT(EMPNO)) FROM EMP GROUP BY EMP.DEPTNO)
GROUP BY EMP.DEPTNO,DNAME

6.多行子查询

IN ANY ALL
1.查询是经理的员工姓名，工资

SELECT ENAME,SAL FROM EMP 
WHERE EMPNO IN(SELECT DISTINCT MGR FROM EMP)

SELECT ENAME,SAL FROM EMP 
WHERE EMPON = ANY(SELECT DISTINCT MGR FROM EMP)

<ANY和结果集中任意一个结果比较，小于最大值即可
>ANY和结果集中任意一个结果比较，大于最小值即可
=ANY和IN的作用一样

2.查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资。

SELECT EMPNO,ENAME,JOB,SAL FROM EMP 
WHERE SAL > ANY（SELECT SLA FROM EMP WHERE DEPTNO = 10） 
AND DEPTNO <> 10

3.查询部门编号不为10，且工资比10部门所有员工工资高的员工编号，姓名，职位，工资。

SELECT EMPNO,ENAME,JOB,SAL FROM EMP 
WHERE SAL > ALL（SELECT SLA FROM EMP WHERE DEPTNO = 10） 
AND DEPTNO <> 10

*练习4
查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工*

SELECT ENAME,JOB FROM EMP 
WHERE JOB IN(SELECT JOB FROM EMP WHERE DEPTNO = 10)
AND DEPTNO <>10

7.多列子查询

*查询出和1981年入职的任意一个员工的部门和职位完全相同员工姓名，部门，职位,
不包括1981年入职员工*

SELECT ENAME,DEPTNO,JOB,HIREDATE FROM EMP 
WHERE (DEPTNO,JOB) IN(SELECT DEPTNO,JOB FROM EMP 
WHERE HIREDATE(1981))

8.子查询中的空值问题

1.查询不是经理的员工姓名，编号

SELECT ENAME,EMPNO FROM EMP 
WHERE EMPNI NOT IN(SELECT MGR FROM EMP)
WHERE MGR IS NULL

9.FROM中使用子查询

查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资
//SELECT AVGSAL,DEPTNO FROM T_AVGSAL

SELECT A.ENAME,A.SAL,A.DEPTNO, B.AVGSAL FROM EMP A,
(SELECT AVG(SAL) AVGSAL FROM EMP 
GROUP BY DEPTNO ) B
WHERE A.DEPTNO=B.DEPTNO AND A.SAL>B.AVGSAL

查询比自己职位平均工资高的员工姓名，职位，部门名称，职位平均工资

SELECT ENAME,E.JOB,D.DNAME A.AVGSLA FROM EMP E,DEPT D , 
(SELECT JOB,AVG(SAL) AVGSAL FROM EMP GROUP BY JOB) A
WHERE E.DEPTNO= D.DEPTNO AND
E.JOB = A.JOB AND 
E.SAL > A.AVGSAL

10.课后作业

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

SELECT D.DNAME,AVG(SAL) FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME
HAVING AVG(SAL)>2500

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

SELECT JOB,AVG(SAL)
FROM EMP  
WHERE JOB NOT LIKE 'SA%'
GROUP BY JOB
HAVING AVG(SAL)>2500
ORDER BY AVG(SAL) DESC

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

SELECT DNAME,ROUND(MIN(SAL),0),ROUND(MAX(SAL),0)
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
GROUP BY DNAME
HAVING COUNT(EMPNO)>2

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

SELECT JOB,SUM(SAL)
FROM EMP
WHERE JOB<>'SALESMAN'
GROUP BY JOB
HAVING SUM(SAL)>=2500

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

SELECT M.EMPNO,MIN(E.SAL)
FROM EMP E
LEFT JOIN EMP M
ON E.MGR = M.EMPNO
GROUP BY M.EMPNO
HAVING MIN(E.SAL)>=3000
ORDER BY MIN(E.SAL) DESC

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

SELECT MAX(SAL)-MIN(SAL)
FROM EMP
GROUP BY DEPTNO

7.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位，不包括10部门员工

SELECT MGR,JOB FROM EMP
WHERE (MGR,JOB) =ANY (
SELECT MGR,JOB FROM EMP WHERE DEPTNO=10)
AND DEPTNO<>10

8.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位，不包括10部门员工

SELECT JOB,MGR FROM EMP
WHERE (JOB IN(
SELECT JOB FROM EMP WHERE DEPTNO=10)
OR  MGR IN(
SELECT MGR FROM EMP WHERE DEPTNO=10))
AND DEPTNO<>10

9.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位，不包括SCOOT和BLAKE本人。

SELECT E.ENAME,E.JOB FROM EMP E,
     (SELECT JOB,MGR FROM EMP WHERE ENAME IN('SCOTT','BLAKE') ) B
WHERE E.JOB =B.JOB
    AND E.MGR =B.MGR
    AND E.ENAME NOT IN('SCOTT','BLAKE')

10.查询工资最高的员工姓名和工资。

SELECT ENAME,SAL FROM EMP WHERE SAL=
(SELECT MAX(SAL) FROM EMP)

2017.9.7