数据库——牛客题霸SQL篇之通关秘籍1-36

说明

• 入门、简单、中等题：只给出sql语句
• 较难、困难题：思路+sql语句

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

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

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

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2,1；

3. 查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no

SELECT salaries.*,dept_manager.dept_no FROM salaries INNER JOIN dept_manager 
ON salaries.emp_no = dept_manager.emp_no ORDER BY salaries.emp_no;

4. 查找所有已经分配部门的员工的last_name和first_name

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

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

SELECT e.last_name,e.first_name,d.dept_no
FROM employees e,dept_emp d 
WHERE e.emp_no = d.emp_no AND e.emp_no IN (SELECT emp_no FROM dept_emp);

7. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

SELECT emp_no,COUNT(emp_no)
FROM salaries 
GROUP BY emp_no HAVING COUNT(emp_no)>15;

8. 找出所有员工当前具体的薪水salary情况

SELECT DISTINCT salary FROM salaries ORDER BY salary DESC;

10. 获取所有非manager的员工emp_no

SELECT emp_no FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager);

11. 获取所有员工当前的manager

SELECT d.emp_no,dm.emp_no 
FROM dept_emp d,dept_manager dm
WHERE d.emp_no NOT IN (SELECT emp_no FROM dept_manager) AND d.dept_no = dm.dept_no ;

12.获取所有部门中当前员工薪水最高的相关信息

• 思路：题目有问题应该是，获取每个部门中当前员工薪水最高的相关信息，

SELECT d.dept_no,d.emp_no, ss.ms
FROM dept_emp d,salaries s,(SELECT d.dept_no,MAX(s.salary) ms FROM dept_emp d,salaries s WHERE d.emp_no = s.emp_no GROUP BY d.dept_no) ss 
WHERE d.emp_no = s.emp_no AND s.salary = ss.ms AND d.dept_no = ss.dept_no ORDER BY d.dept_no ;

15. 查找employees表所有emp_no为奇数

SELECT *
FROM employees
WHERE emp_no%2 = 1 AND last_name != 'Mary' ORDER BY hire_date DESC;

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

SELECT title,AVG(s.salary)
FROM titles t,salaries s
WHERE t.emp_no = s.emp_no GROUP BY title;

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

SELECT emp_no,salary 
FROM salaries ORDER BY salary DESC LIMIT 1,1;

18.查找当前薪水排名第二多的员工编号emp_no

思路：要求的是薪水排名第二多的，所以关键是找到第二多的薪水对应的salary值，利用MAX函数以及NOT IN过滤掉薪水第一多的人

SELECT e.emp_no,s.salary,e.last_name,e.first_name
FROM employees e,salaries s
WHERE e.emp_no = s.emp_no AND s.salary = (SELECT MAX(salary) FROM salaries WHERE salary NOT IN (SELECT MAX(salary) FROM salaries));

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

SELECT e.last_name,e.first_name,dd.dept_name
FROM employees e LEFT JOIN 
(SELECT da.dept_name,de.emp_no FROM dept_emp de,departments da WHERE de.dept_no = da.dept_no) dd
ON e.emp_no = dd.emp_no ;

21.查找所有员工自入职以来的薪水涨幅情况

• 思路：薪水涨幅，也就是员工现在的最高工资减去员工的最低工资，翻译一下也就是MAX(salary)-MIN(salary),那就将薪水表中每个员工的最大工资和最小工资筛选出来，再将最大工资和最小工资连接到一个表中，再进行工资涨幅的计算。
• 注意：
  1.离职员工不在查找结果中。
  2.结果表按工资涨幅排序。

SELECT s1.emp_no,(s1.max - s2.min) growth
FROM (SELECT emp_no,MAX(salary) max FROM salaries WHERE to_date ='9999-01-01' GROUP BY emp_no) s1 LEFT JOIN
(SELECT emp_no,MIN(salary) min FROM salaries GROUP BY emp_no) s2 ON s1.emp_no = s2.emp_no ORDER BY growth;

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

SELECT de.dept_no ,de.dept_name,count(de.dept_no) sum
FROM departments de,dept_emp d,salaries s
WHERE de.dept_no = d.dept_no AND d.emp_no = s.emp_no GROUP BY d.dept_no ORDER BY d.dept_no;

23. 对所有员工的当前薪水按照salary进行按照1-N的排名

思路：利用dense_rank()函数
DENSE_RANK()
这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，2····

SELECT emp_no,salary, dense_rank() over(order by salary desc) as t_rank
FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY t_rank, emp_no asc

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

• 思路：用emp_no连接三张表,用NOT IN过滤掉manager

SELECT de.dept_no,e.emp_no,s.salary
FROM employees e,dept_emp de,salaries s 
WHERE e.emp_no = de.emp_no AND e.emp_no = s.emp_no AND e.emp_no NOT IN (SELECT emp_no FROM dept_manager);

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

• 思路：子查询
  1.连接dept_emp,salaries，查找非manager员工的薪水情况，需要利用dept_manager表过滤掉manager
  2.连接dept_manager,salaries，查找manager的薪水情况
  3.连接1，2查找的两张子表，找出利用dept_no属性连接两张子表，然后再筛选出员工工资高的

SELECT a.emp_no,b.emp_no,a.salary,b.salary
FROM (SELECT de.emp_no,s.salary,de.dept_no FROM dept_emp de,salaries s WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager) AND de.emp_no = s.emp_no) a,
(SELECT dm.emp_no,s.salary,dm.dept_no FROM dept_manager dm,salaries s WHERE dm.emp_no = s.emp_no ) b
WHERE a.dept_no = b.dept_no AND a.salary > b.salary;

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

• 思路：三个表连接，分组排序，统计title
• 注意：分组是只依据de.dept_no或者t.title是不行的

SELECT de.dept_no,de.dept_name,t.title,COUNT(title) 
FROM dept_emp d,titles t,departments de
WHERE d.emp_no = t.emp_no AND de.dept_no = d.dept_no GROUP BY de.dept_no,t.title ORDER BY de.dept_no;

28. 查找描述信息中包括robot的电影对应的分类名称以及电影数目

• 思路：
  1.先在film表中用模糊查询查出包含robot的电影
  2.将三个表连接
  3.过滤掉每个电影类型下小于五个电影的类型

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

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

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

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

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

32.将employees表的所有员工的last_name和first_name拼接起来作为Name

SELECT CONCAT(last_name, ' ', first_name)Name FROM employees;

33.创建一个actor表，包含如下列信息

CREATE TABLE actor
(actor_id smallint(5) NOT NULL PRIMARY KEY, 
    first_name varchar(45) NOT NULL,
   last_name varchar(45) NOT NULL,
   last_update date NOT NULL);

34.批量插入数据

INSERT INTO actor(actor_id,first_name,last_name,last_update)
VALUES(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'), (2,'NICK','WAHLBERG','2006-02-15 12:34:33');

35. 批量插入数据,如果数据已经存在，请忽略，不使用replace操作

insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");

36.创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表

CREATE TABLE IF NOT EXISTS actor_name
(first_name varchar(45) not null,last_name varchar(45) not null)
SELECT first_name,last_name FROM actor