查詢最低工資大于50號部門最低工資的部門id和其最低工資
1查詢5號部門最低工資

SELECT
    MIN(salary)
FROM
    employees e
WHERE
    department_id = 50

2查詢每個部門最低工資

SELECT
    MIN(salary) Minsalary,
    department_id
FROM
    employees e
GROUP BY
    department_id

3在2的基礎上做篩選囚衔，滿足min(salary)>1

SELECT
    MIN(salary) Minsalary,
    department_id
FROM
    employees e
GROUP BY
    department_id
HAVING
    MIN(salary) > (
        SELECT
            MIN(salary)
        FROM
            employees
        WHERE
            department_id = 50
    )

多行子查詢
in/not in 等于列表中任意一個
any| some 和子查詢返回的某一個值比較
all 和子查詢返回的所有制比較
2查詢location_id是1400或者1700的部門編號

多表左外連接

SELECT
    last_name
FROM
    employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN locations l ON d.location_id = l.location_id
WHERE
    l.location_id IN (1400, 1700)

使用子查詢

SELECT
    last_name
FROM
    employees e
WHERE
    department_id IN (
        SELECT
            department_id
        FROM
            departments
        WHERE
            location_id IN (1400, 1700)
    )

當然也可使用where查詢
3返回其他工種比job_id為'IT-PROG'部任一工資低的員工號，姓名冶匹，job_id

SELECT
    employee_id,
    last_name,
    job_id,
    salary
FROM
    employees e
WHERE
    salary < ANY (
        SELECT
            salary
        FROM
            employees
        WHERE
            job_id = 'IT_PROG'
    )
AND job_id <> 'IT_PROG'

思考
not in 與 all
in any

行子查詢

查詢員工編號最小并且工資最高的員工信息
傳統(tǒng)做法

*

SELECT
    *
FROM
    employees
WHERE
    (employee_id, salary) = (
        SELECT
            min(employee_id),
            MAX(salary)
        FROM
            employees
    )

查詢每個部門的平均工資等級

SELECT
    ag_dep.*, jg.grade_level
FROM
    (
        SELECT
            avg(salary) avgSal,
            department_id
        FROM
            employees e
        GROUP BY
            department_id
    ) ag_dep
INNER JOIN job_grades jg ON ag_dep.avgSal BETWEEN jg.lowest_sal
AND highest_sal

練習
1查詢和Zlotkey相同部門的員工姓名和工資

SELECT
    last_name,
    salary
FROM
    employees
WHERE
    department_id = (
        SELECT
            department_id
        FROM
            employees
        WHERE
            last_name = "Zlotkey"
    )

2查詢各部門工資中比本部門平均工資高的員工的員工號,姓名和工資

SELECT
    employee_id,
    last_name,
    salary,
    e.department_id
FROM
    employees e
INNER JOIN (
    SELECT
        avg(salary) avgSal,
        department_id
    FROM
        employees
    GROUP BY
        department_id
) ag_dep ON e.department_id = ag_dep.department_id
WHERE
    salary > ag_dep.avgSal

3查詢平均工資最高的job信息

SELECT
    *
FROM
    jobs
WHERE
    job_id = (
        SELECT
            job_id
        FROM
            employees e
        GROUP BY
            job_id
        ORDER BY
            salary DESC
        LIMIT 1
    )

4查詢平均工資高于公司平均工資的部門有哪些

SELECT
    avg(salary) sal,
    department_id
FROM
    employees
GROUP BY
    department_id
HAVING
    sal > (
        SELECT
            avg(salary)
        FROM
            employees
    )

5查詢公司中所有manager的詳細信息

SELECT
    *
FROM
    employees
WHERE
    employee_id IN (
        SELECT
            manager_id
        FROM
            employees
    )

6查詢各部門中最高工資中最低的那個部門的最低工資

SELECT
    min(salary) salary
FROM
    employees
WHERE
    department_id = (
        SELECT
            department_id
        FROM
            employees e
        GROUP BY
            department_id
        ORDER BY
            max(salary) ASC
        LIMIT 1
    ) #查詢10號部門最低工資

7查詢平均工資最高的部門的manager的詳細信息

SELECT
    last_name,
    salary,
    email
FROM
    employees ee
INNER JOIN departments d ON d.manager_id = ee.employee_id
WHERE
    d.department_id = (
        SELECT
            department_id
        FROM
            employees e
        GROUP BY
            department_id
        ORDER BY
            AVG(salary) DESC
        LIMIT 1
    )

union 默認會去重
union All不去重
語法: A與B字段必須對應

SELECT
    學號,
    姓名,
    成績
FROM
    A
UNION
    SELECT
        學號,
        姓名,
        成績
    FROM
        B