MySQL-子查询-练习

练习

---

1.写一个查询显示与 Zlotkey 在同一部门的雇员的 last name 和hire date，结果中不包括 Zlotkey。

SELECT
e1.LAST_NAME,e1.HIRE_DATE
FROM employees e1
where e1.DEPARTMENT_ID =
(select e.DEPARTMENT_ID 
FROM employees e
where e.LAST_NAME ='Zlotkey')
AND e1.LAST_NAME <> 'Zlotkey';

2.创建一个查询显示所有其薪水高于平均薪水的雇员的雇员号和名字。按薪水的升序排序。

SELECT
e1.EMPLOYEE_ID,e1.LAST_NAME
FROM employees e1
WHERE e1.SALARY >
(select AVG(e.SALARY) 
from employees e)
ORDER BY e1.SALARY;

3.写一个查询显示所有工作在有任一雇员的名字中包含一个 u 的部门的雇员的雇员号和名字。

SELECT
e1.EMPLOYEE_ID,e1.LAST_NAME
FROM employees e1
WHERE e1.DEPARTMENT_ID IN
(SELECT e.DEPARTMENT_ID 
FROM employees e
WHERE e.LAST_NAME like '%u%');

4.显示所有部门地点号 (department location ID ) 是 1700 的雇员的 last name、department number 和 job ID。

SELECT
e.LAST_NAME,e.DEPARTMENT_ID,e.JOB_ID
FROM employees e
WHERE e.DEPARTMENT_ID IN
(SELECT
d.DEPARTMENT_ID
FROM departments d
WHERE d.LOCATION_ID = 1700);

5.显示每个向 King 报告的雇员的名字和薪水。

SELECT
e1.LAST_NAME,e1.SALARY
FROM employees e1
where e1.MANAGER_ID IN
(select e.EMPLOYEE_ID 
from employees e where
e.LAST_NAME ='King');

6.显示在 Executive 部门的每个雇员的 department number、lastname 和 job ID。

SELECT
e.DEPARTMENT_ID,e.LAST_NAME,e.JOB_ID
FROM employees e
WHERE e.DEPARTMENT_ID =
(select d.DEPARTMENT_ID 
FROM departments d
WHERE d.DEPARTMENT_NAME = 'Executive');