sql实例应用

前面对基本的sql查询语句做了一个简单的总结，下面结合网上的常用查询做一个实例。

1.查询每个月倒数第2天入职的员工信息
2.查询出last_name为‘Chen’的manager信息
3.查询平均工资高于8000的部门id和它的平均工资
4.查询工资最低的员工信息：last_name，salary
5.查询平均工资最低的部门信息
6.查询平均工资最低的部门信息和该部门的平均工资
7.查询平均工资最高的job信息
8.查询平均工资高于公司平均工资的部门有哪些
9.查询出公司中所有manager的详细信息
10.各个部门中最高工资中最低的那个部门的最低工资是多少
11.查询平均工资最高的部门的manager的详细信息：last_name，department_iid，email，salary
12.查询1999年来公司的员工中，最高工资的员工信息
13.返回其它部门中比job_id为’IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

---

1.查询每个月倒数第2天入职的员工的信息

SELECT last_name,first_name,hire_date
FROM employees 
WHERE hire_date = last_day(hire_date) - 1;

2.查询出last_name为‘Chen’的manager的信息

通过子查询先查出last_name为‘Chen’的管理员id

SELECT * 
FROM employees
WHERE employee_id = (
                    SELECT manager_id 
                    FROM employees 
                    WHERE TRIM(last_name)='Chen'
)

3.查询平均工资高于8000的部门id和它的平均工资

SELECT department_id,avg(salary) as avg_salary 
FROM employees 
GROUP BY department_id 
HAVING avg(salary) > 8000; 

4.查询工资最低的员工信息

SELECT last_name,salary 
FROM employees 
WHERE salary = (
                SELECT MIN(salary) 
                FROM employees
                );

5.查询平均工资最低的部门信息

SELECT * 
FROM departments 
WHERE department_id = (
                       SELECT department_id 
                       FROM employees 
                       GROUP BY department_id
                       HAVING AVG(salary) = (SELECT MIN(AVG(salary))
                       FROM employees GROUP BY department_id)
                    );

6.查询平均工资最低的部门信息和该部门的平均工资

SELECT d.*,(SELECT AVG(salary) FROM employee e WHERE d.id = e.id) 
FROM departments d
WHERE department_id = (SELECT department_id
                       FROM employees 
                       GROUP BY department_id
                       HAVING AVG(salary) = (SELECT MIN(AVG(salary))
                                             FROM employee
                                             GROUP BY department_id)
);

7.查询平均工资最高的job信息

SELECT job_id,job_title 
FROM jobs
WHERE job_id = (SELECT job_id 
                FROM employees
                GROUP BY job_id
                HAVING AVG(salary) = (SELECT MAX(AVG(salary))
                                      FROM employees
                                      GROUP BY job_id
                )) ;

8.查询平均工资高于公司平均工资的部门有哪些

SELECT department_id,department_name
FROM departments 
WHERE department_id IN(SELECT department_id 
                       FROM employees
                       GROUP BY department_id
                       HAVING AVG(salary) > (SELECT AVG(salary) 
                                             FROM employees)
                    );

9.查询出公司中所有manager的详细信息

SELECT employee_id,last_name
FROM employee
WHERE employee_id IN(SELECT DISTINCT manager_id
                     FROM employees
);

10.各个部门汇总，最高工资中最低的那个部门的最低工资是多少

(1)查询各个部门的最高工资
SELECT MAX(salary) FROM employee GROUP BY department_id

(2)查询出1中对应的最低结果
SELECT MIN(MAX(salary)) FROM employees GROUP BY department_id

(3)查询出2中对应的部门id是多少
SELECT department_id 
FORM employees
GROUP BY department_id
HAVING MAX(salay) = (SELECT MIN(MAX(salary)) 
                     FROM employees 
                     GROUP BY department_id)

(4)查询出3中所对应部门的最低工资
SELECT MIN(salary)，departme_id 
FROM employees
WHERE department_id = (SELECT department_id 
                       FORM employees
                       GROUP BY department_id
                       HAVING MAX(salay) = (SELECT MIN(MAX(salary)) 
                                            FROM employees 
                                            GROUP BY department_id)); 

11.查询平均工资最高的那个部门的manager的详细信息

（1）查询出平均工资最高的部门id
SELECT department_id 
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT MAX(AVG(salary)) 
                      FROM employee
                      GROUP BY employee)
(2)根据1中的department_id得到对应的manger_id
SELECT manager_id 
FROM departments
WHERE department_id = (SELECT department_id 
                       FROM employees
                       GROUP BY department_id
                       HAVING AVG(salary) = (SELECT MAX(AVG(salary)) 
                                            FROM employee
                                            GROUP BY employee));
(3)根据2的结果给出manger的详细信息
SELECT last_name,department_id,email,salary 
FROM employee
WHERE employee_id = (SELECT manager_id 
                     FROM departments
                     WHERE department_id = (SELECT department_id 
                                            FROM employees
                                            GROUP BY department_id
                                            HAVING AVG(salary) = (SELECT MAX(AVG(salary)) 
                                                                  FROM employee
                                                                  GROUP BY employee)));

12.查询1999年以来公司所有员工中工资最高的那个员工信息

（1）查询出1999年来公司的最大salary
SELECT MAX(salary)
FROM employees
WHERE YEAR(hire_date) = 1999

（2）查询出2中对应的1999年入职员工信息
SELECT * 
FROM employees
WHERE YEAR(hire_date) = 1999 AND salary = (SELECT MAX(salary)
                                           FROM employees
                                           WHERE YEAR(hire_date) = 1999);

13.返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号，姓名，job_id以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (SELECT MIN(salary)
                FROM employee
                WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

---

总结：后续继续（采用的是尚硅谷总结的常用sql语句）