1. -- 1.    查询和Zlotkey相同部门的员工姓名和工资
    2. -- ①查询Zlotkey的部门
    3. SELECT department_id
    4. FROM employees
    5. WHERE last_name = 'Zlotkey'
    7. -- ②查询部门号=①的姓名和工资
    8. SELECT last_name,salary
    9. FROM employees
    10. WHERE department_id = (
    11.     SELECT department_id
    12.     FROM employees
    13.     WHERE last_name = 'Zlotkey'
    14. )
    16. -- 2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
    17. -- ①查询平均工资
    18. SELECT AVG(salary)
    19. FROM employees
    21. -- ②查询工资>①的员工号，姓名和工资。
    22. SELECT last_name,employee_id,salary
    23. FROM employees
    24. WHERE salary>(
    25.     SELECT AVG(salary)
    26.     FROM employees
    27. );
    29. -- 3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
    30. -- ①查询各部门的平均工资
    31. SELECT AVG(salary),department_id
    32. FROM employees
    33. GROUP BY department_id
    35. -- ②连接①结果集和employees表，进行筛选
    36. SELECT employee_id,last_name,salary,e.department_id
    37. FROM employees e
    38. INNER JOIN (
    39.     SELECT AVG(salary) ag,department_id
    40.     FROM employees
    41.     GROUP BY department_id
    42. ) ag_dep
    43. ON e.department_id = ag_dep.department_id
    44. WHERE salary>ag_dep.ag ;
    48. -- 4.    查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
    49. -- ①查询姓名中包含字母u的员工的部门
    50. SELECT  DISTINCT department_id
    51. FROM employees
    52. WHERE last_name LIKE '%u%'
    54. -- ②查询部门号=①中的任意一个的员工号和姓名
    55. SELECT last_name,employee_id
    56. FROM employees
    57. WHERE department_id IN(
    58.     SELECT  DISTINCT department_id
    59.     FROM employees
    60.     WHERE last_name LIKE '%u%'
    61. );
    64. -- 5. 查询在部门的location_id为1700的部门工作的员工的员工号
    65. -- ①查询location_id为1700的部门
    66. SELECT DISTINCT department_id
    67. FROM departments 
    68. WHERE location_id  = 1700
    70. -- ②查询部门号=①中的任意一个的员工号
    71. SELECT employee_id
    72. FROM employees
    73. WHERE department_id =ANY(
    74.     SELECT DISTINCT department_id
    75.     FROM departments 
    76.     WHERE location_id  = 1700
    77. );
    79. -- 6.查询管理者是King的员工姓名和工资
    80. -- ①查询姓名为king的员工编号
    81. SELECT employee_id
    82. FROM employees
    83. WHERE last_name  = 'K_ing'
    85. -- ②查询哪个员工的manager_id = ①
    86. SELECT last_name,salary
    87. FROM employees
    88. WHERE manager_id IN(
    89.     SELECT employee_id
    90.     FROM employees
    91.     WHERE last_name  = 'K_ing'
    92. );
    94. -- 7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
    95. -- ①查询最高工资
    96. SELECT MAX(salary)
    97. FROM employees
    99. -- ②查询工资=①的姓.名
    100. SELECT CONCAT(first_name,last_name) "姓.名"
    101. FROM employees
    102. WHERE salary=(
    103.     SELECT MAX(salary)
    104.     FROM employees
    105. );