牛客网数据库SQL实战（一）

SQL1 查找最晚入职员工的所有信息

SELECT * FROM employees
WHERE hire_date = (
    SELECT MAX(hire_date) FROM employees
)

SQL2 查找入职员工时间排名倒数第三的员工所有信息

SELECT * FROM employees
WHERE hire_date = (
    SELECT DISTINCT hire_date FROM employees
    ORDER BY hire_date DESC
    LIMIT 2, 1
)

SQL3 查找当前薪水详情以及部门编号dept_no

SELECT salaries.emp_no, salary, salaries.from_date, salaries.to_date, dept_no
FROM salaries INNER JOIN dept_manager ON salaries.emp_no = dept_manager.emp_no
ORDER BY salaries.emp_no

SQL4 查找所有已经分配部门的员工的last_name和first_name以及dept_no

SELECT last_name, first_name, dept_no
FROM dept_emp INNER JOIN employees ON dept_emp.emp_no = employees.emp_no

SQL5 查找所有员工的last_name和first_name以及对应部门编号dept_no

SELECT last_name, first_name, dept_no
FROM employees LEFT JOIN dept_emp ON employees.emp_no = dept_emp.emp_no

SQL7 查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t

SELECT emp_no, COUNT(1) AS t
FROM salaries
GROUP BY emp_no
HAVING COUNT(1) > 15

SQL8 找出所有员工当前薪水salary情况

SELECT DISTINCT salary
FROM salaries
ORDER BY salary DESC

SQL10 获取所有非manager的员工emp_no

-- 解法一：
SELECT emp_no FROM employees
WHERE emp_no NOT IN (
    SELECT emp_no FROM dept_manager
)

-- 解法二：
SELECT e.emp_no
FROM employees e LEFT JOIN dept_manager d ON e.emp_no = d.emp_no
WHERE d.emp_no IS NULL

SQL11 获取所有员工当前的manager

SELECT e.emp_no, m.emp_no AS manager
FROM dept_emp e LEFT JOIN dept_manager m ON e.dept_no = m.dept_no
WHERE e.emp_no <> m.emp_no

SQL12 获取每个部门中当前员工薪水最高的相关信息

-- 解法一：
SELECT dept_no, d1.emp_no, salary
FROM dept_emp d1 INNER JOIN salaries s1 ON d1.emp_no = s1.emp_no
WHERE salary = (
    SELECT MAX(salary)
    FROM dept_emp d2 INNER JOIN salaries s2 ON d2.emp_no = s2.emp_no
    WHERE d2.dept_no = d1.dept_no
)
ORDER BY dept_no

-- 解法二：
SELECT temp.dept_no, temp.emp_no, temp.salary
FROM (
    SELECT
        dept_no, 
        d.emp_no, 
        salary, 
        rank() OVER (PARTITION BY dept_no ORDER BY salary DESC) AS salary_rank
    FROM dept_emp d INNER JOIN salaries s ON d.emp_no = s.emp_no
) temp
WHERE temp.salary_rank = 1
ORDER BY temp.dept_no ASC

SQL15 查找employees表emp_no与last_name的员工信息

SELECT * FROM employees
WHERE MOD(emp_no, 2) = 1 AND last_name <> 'Mary'
ORDER BY hire_date DESC

SQL16 统计出当前各个title类型对应的员工当前薪水对应的平均工资

SELECT title, AVG(s.salary)
FROM titles t INNER JOIN salaries s ON t.emp_no = s.emp_no
GROUP BY t.title
ORDER BY AVG(s.salary) ASC

SQL17 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

SELECT temp.emp_no, temp.salary
FROM (
    SELECT emp_no, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
    FROM salaries
) temp
WHERE temp.r = 2

SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

SELECT e.emp_no, salary, last_name, first_name
FROM employees e INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE salary = (
    SELECT MAX(salary) FROM salaries
    WHERE salary <> (
        SELECT MAX(salary) FROM salaries
    )
)

SQL19 查找所有员工的last_name和first_name以及对应的dept_name

SELECT last_name, first_name, dept_name
FROM 
    employees e LEFT JOIN dept_emp r ON e.emp_no = r.emp_no
    LEFT JOIN departments d ON d.dept_no = r.dept_no

SQL21 查找在职员工自入职以来的薪水涨幅情况

WITH 
    in_service AS (
        SELECT DISTINCT emp_no
        FROM salaries
        WHERE to_date = '9999-01-01'
    ),
    salary_begin AS (
        SELECT s.emp_no, s.salary
        FROM salaries s INNER JOIN employees e ON s.emp_no = e.emp_no
        WHERE s.emp_no IN (SELECT * FROM in_service) AND s.from_date = e.hire_date
    ),
    salary_now AS (
        SELECT s.emp_no, s.salary
        FROM salaries s INNER JOIN employees e ON s.emp_no = e.emp_no
        WHERE s.emp_no IN (SELECT * FROM in_service) AND s.to_date = '9999-01-01'
    )
SELECT s1.emp_no, s2.salary - s1.salary AS growth
FROM salary_begin s1 INNER JOIN salary_now s2 ON s1.emp_no = s2.emp_no
ORDER BY growth ASC

SQL22 统计各个部门的工资记录数

SELECT d.dept_no, d.dept_name, COUNT(*) AS `sum`
FROM
    departments d INNER JOIN dept_emp r ON d.dept_no = r.dept_no
    INNER JOIN salaries s ON s.emp_no = r.emp_no
GROUP BY d.dept_no
ORDER BY d.dept_no ASC

SQL23 对所有员工的薪水按照salary降序进行1-N的排名

SELECT emp_no, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS t_rank
FROM salaries

SQL24 获取所有非manager员工当前的薪水情况

SELECT r.dept_no, r.emp_no, s.salary
FROM 
    employees e INNER JOIN dept_emp r ON e.emp_no = r.emp_no
    INNER JOIN salaries s ON s.emp_no = r.emp_no
WHERE r.emp_no NOT IN (
    SELECT emp_no FROM dept_manager
)

SQL25 获取员工其当前的薪水比其manager当前薪水还高的相关信息

SELECT s1.emp_no, s2.emp_no AS manager_no, s1.salary AS emp_salary, s2.salary AS manager_salary
FROM salaries s1, salaries s2
WHERE s2.emp_no = (
    SELECT m.emp_no
    FROM dept_emp r INNER JOIN dept_manager m ON r.dept_no = m.dept_no
    WHERE s1.emp_no = r.emp_no
) AND s1.salary > s2.salary

SQL26 汇总各个部门当前员工的title类型的分配数目

SELECT d.dept_no, d.dept_name, t.title, COUNT(*) AS `count`
FROM
    departments d INNER JOIN dept_emp r ON d.dept_no = r.dept_no
    INNER JOIN titles t ON t.emp_no = r.emp_no
GROUP BY d.dept_no, d.dept_name, t.title
ORDER BY d.dept_no ASC

SQL28 查找描述信息中包含robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

SELECT c.name, COUNT(f.film_id)
FROM 
    film f INNER JOIN film_category fc ON f.film_id = fc.film_id
    INNER JOIN category c ON c.category_id = fc.category_id
WHERE fc.category_id IN (
    SELECT category_id FROM film_category
    GROUP BY category_id
    HAVING COUNT(category_id) >= 5
) AND f.description LIKE '%robot%'

SQL29 使用join查询方式找出没有分类的电影id以及名称

SELECT f.film_id, f.title
FROM film f LEFT JOIN film_category fc ON f.film_id = fc.film_id
WHERE fc.category_id IS NULL

SQL30 使用子查询的方式找出属于Action分类的所有电影对应的title,description

SELECT f.title, f.description
FROM film AS f
WHERE f.film_id IN (
    SELECT fc.film_id
    FROM film_category AS fc
    WHERE fc.category_id = (
        SELECT c.category_id
        FROM category AS c
        WHERE c.name = 'Action'
    )
)