使用HR数据库进行练习之子查询练习
Practice 1
编写一条SQL查询语句，获取salary大于last_name为‘Bull’的employee的name（first_name，last_name）和salary
|
1 2 |
SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Bull'); |
Practice 2
编写一条SQL查询语句，获取所有在IT department工作的employee的名字（first_name，last_name）
|
1 2 3 |
SELECT first_name, last_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'IT'); |
Practice 3
编写一条SQL查询语句，获取拥有一个manager以及该manager在USA based department工作的的所有employees的名字（last_name，first_name）
|
1 2 3 4 5 |
SELECT first_name, last_name FROM employees WHERE manager_id IN (SELECT employee_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id IN (SELECT location_id FROM locations WHERE country_id = 'US'))); |
Practice 4
编写一条SQL查询语句，获取managers的名字（first_name，last_name）
|
1 2 |
SELECT first_name, last_name FROM employees WHERE employee_id IN (SELECT manager_id FROM employees); |
Practice 5
编写一条SQL查询语句，获取salary大于平均salary的employee的name（first_name，last_name）和salary
|
1 2 |
SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); |
Practice 6
编写一条SQL查询语句，获取其工作等级中最低工资的员工的名字（first_name，last_name）和薪水。
|
1 2 3 |
SELECT first_name, last_name, salary FROM employees WHERE salary = (SELECT min_salary FROM jobs WHERE employees.job_id = jobs.job_id); |
Practice 7
编写一条SQL查询语句，获取薪水高于平均薪水而且在IT department工作的employee的名字（first_name，last_name）和薪水
|
1 2 3 |
SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) AND department_id IN (SELECT department_id FROM departments WHERE department_name LIKE 'IT%'); |
Practice 8
编写一条SQL查询语句，获取获取薪水高于Mr.Bell的employee的名字（first_name，last_name）和薪水
|
1 2 3 |
SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Bell'); |
Practice 9
编写一条SQL查询语句，获取在所有部门中的薪水最低的employees的名字（first_name，last_name）和薪水
|
1 2 |
SELECT first_name, last_name, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees); |
Practice 10
编写一条SQL查询语句，获取salary高于所有部门平均salary的employee的名字（first_name，last_name）和salary
|
1 2 |
SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); |
Practice 11
编写一条SQL查询语句，获取salary高于所有Shipping CLerk（job_id=‘SH_CLERK’）的employee的名字（first_name，last_name），job_id和salary，并按照salary由低到高排列输出
|
1 2 3 |
SELECT first_name, last_name, job_id, salary FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE job_id = 'SH_CLERK') ORDER BY salary; |
或者使用ALL关键字
|
1 2 3 |
SELECT first_name, last_name, job_id, salary FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE job_id = 'SH_CLERK') ORDER BY salary; |
Practice 12
编写一条SQL查询语句，获取所有不是管理者的employee的名字（first_name，last_name）
|
1 2 |
SELECT first_name, last_name FROM employees b WHERE NOT EXISTS ( SELECT 'A' FROM employees a WHERE a.manager_id = b.employee_id); |
Practice 13
编写一条SQL查询语句，获取所有employee的employee ID，first_name，last_name和department name
|
1 2 3 4 |
SELECT employee_id, first_name, last_name, (SELECT department_name FROM departments d WHERE d.department_id = e.department_id) department_name FROM employees e; |
Practice 14
编写一条SQL查询语句，获取所有salary高于其部门平均salary的employees的employee ID，first name，last name和salary
|
1 2 3 |
SELECT e.employee_id, e.first_name, e.last_name, e.salary FROM employees e WHERE e.salary > ( SELECT AVG(salary) FROM employees a WHERE e.department_id = a.department_id); |
Practice 15
编写一条SQL查询语句，获取employees表中偶数编号的记录
这个要定义变量，有点懵逼，毕竟没了解过
|
1 2 3 4 |
SET @i = 0; SELECT i, employee_id, first_name, last_name FROM (SELECT @i := @i + 1 AS i, employee_id ,first_name, last_name FROM employees) a WHERE MOD(a.i, 2) = 0; |
Practice 16
编写一条SQL查询语句，获取employees表中第五高salary的employee
简单的方法就是使用LIMIT关键字，如下面
|
1 |
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 4, 1; |
另一种方法
|
1 2 3 4 |
SELECT DISTINCT salary FROM employees e1 WHERE 5 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary >= e1.salary); |
Practice 17
编写一条SQL查询语句，获取employees表中salary为倒数第四的employee
和上一个练习一样，简单的方法如下
|
1 |
SELECT DISTINCT salary FROM employees ORDER BY salary LIMIT 3, 1; |
另一个方法：
|
1 2 3 |
SELECT DISTINCT salary FROM employees e1 WHERE 4 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e1.salary >= e2.salary); |
Practice 18
编写一条SQL查询语句，获取employees表最后十条记录
先使用子查询降序查询最后十条记录，然后在外侧查询中通过升序查询进行排序
|
1 2 3 |
SELECT * FROM (SELECT * FROM employees ORDER BY employee_id DESC LIMIT 10) sub ORDER BY employee_id ASC; |
Practice 19
编写一条SQL查询语句，列出没有employee的department ID和名字
|
1 2 3 |
SELECT department_id, department_name FROM departments d WHERE NOT EXISTS (SELECT 'A' FROM employees e WHERE d.department_id = e.department_id); |
上面的稍显复杂，下面的就简单点
|
1 2 3 |
SELECT * FROM departments WHERE department_id NOT IN (select department_id FROM employees); |
Practice 20
编写一条SQL查询语句，获取三个最高salary的employee
|
1 |
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 3; |
Practice 21
编写一条SQL查询语句，获取三个最低salary的employee
|
1 |
SELECT DISTINCT salary FROM employees ORDER BY salary LIMIT 3; |
