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'
)
)