牛客网-数据库SQL实战1-30

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

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

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

SELECT *
FROM employees
WHERE emp_no in (SELECT emp_no 
                 FROM employees
                 ORDER BY hire_date DESC
                  LIMIT 2,1)

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

SELECT s.*, d.dept_no 
FROM salaries s JOIN dept_manager d ON s.emp_no=d.emp_no
WHERE s.to_date='9999-01-01' AND d.to_date='9999-01-01' 

注：这里JOIN的时候两个表的位置不能换，换了就不通过了

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

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

5. 查找所有员工的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

注：只能左联接，把两个表位置换一下用右联接是不能通过的，不知道为啥，反正还是按逻辑最好理解的方式写吧

6. 查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

SELECT e.emp_no,s.salary
FROM employees e JOIN salaries s ON e.emp_no = s.emp_no
WHERE e.hire_date = s.from_date
ORDER BY e.emp_no DESC

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

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

注：只涨薪不降薪

8. 找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

SELECT DISTINCT salary
FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY salary DESC

9. 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'

SELECT d.dept_no, s.emp_no, s.salary
FROM salaries s JOIN dept_manager d ON s.emp_no=d.emp_no
WHERE s.to_date='9999-01-01' AND d.to_date='9999-01-01' 

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

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

11. 获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'，结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no

SELECT e.emp_no,m.emp_no
FROM dept_emp e LEFT JOIN dept_manager m ON (e.dept_no = m.dept_no AND e.emp_no != m.emp_no)
WHERE e.to_date = '9999-01-01' AND m.to_date = '9999-01-01'

12. 获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

SELECT d.dept_no,s.emp_no ,s.salary
FROM dept_emp d JOIN salaries s ON d.emp_no = s.emp_no
WHERE s.salary IN (SELECT MAX(b.salary)
                  FROM dept_emp a JOIN salaries b ON a.emp_no = b.emp_no
                  WHERE a.to_date ='9999-01-01' AND b.to_date ='9999-01-01'
                 AND a.dept_no = d.dept_no
                  ) 
GROUP BY d.dept_no

13. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t

SELECT title, count(*)
FROM titles
GROUP BY title
HAVING count(*)>=2

14. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t，注意对于重复的emp_no进行忽略。

SELECT title, count(DISTINCT emp_no) AS t
FROM titles
GROUP BY title
HAVING t>=2

15. 查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

SELECT *
FROM employees
WHERE last_name != 'mary' AND emp_no %2 !=0
ORDER BY hire_date DESC

16. 统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg

SELECT t.title, avg(s.salary)
FROM titles t JOIN salaries s ON t.emp_no = s.emp_no
WHERE t.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
GROUP BY t.title

17. 获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary

SELECT emp_no, salary
FROM salaries
WHERE salary = (SELECT salary 
                FROM salaries 
                WHERE to_date = '9999-01-01' 
                ORDER BY salary DESC 
                LIMIT 1,1
               )

18. 查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

SELECT s.emp_no, s.salary, e.last_name, e.first_name
FROM employees e JOIN salaries s ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01'
AND s.salary = (SELECT MAX(salary) 
                FROM salaries 
                WHERE to_date = '9999-01-01'
                AND salary <> (SELECT MAX(salary)
                                 FROM salaries
                                 WHERE to_date = '9999-01-01'
                                 )
               )

19. 查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

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

20. 查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth

SELECT MAX(salary)-MIN(salary) AS growth
FROM salaries
WHERE emp_no ='10001'

21. 查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_noy以及其对应的薪水涨幅growth，并按照growth进行升序

SELECT e.emp_no,(s.salary-(SELECT ss.salary 
                           FROM  employees ee LEFT JOIN salaries ss ON ss.emp_no = ee.emp_no 
                           WHERE ss.from_date = ee.hire_date AND ss.emp_no = s.emp_no))AS growth
FROM  employees e LEFT JOIN salaries s ON s.emp_no = e.emp_no
WHERE s.to_date ='9999-01-01'
ORDER BY growth 

22. 统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum

SELECT d.dept_no,d.dept_name,COUNT(e.emp_no) AS sum
FROM dept_emp e LEFT JOIN departments d ON e.dept_no = d.dept_no
LEFT JOIN salaries s ON e.emp_no = s.emp_no
GROUP BY d.dept_no,d.dept_name

23. 对所有员工的当前(to_date='9999-01-01')薪水按照salary进行排名，相同salary并列按降序且emp_no按升序

SELECT a.emp_no,a.salary,COUNT(DISTINCT b.salary)
FROM salaries a, salaries b
WHERE a.to_date = '9999-01-01'
AND b.to_date = '9999-01-01'
AND a.salary <= b.salary
GROUP BY a.emp_no,a.salary
ORDER BY a.salary DESC, a.emp_no

24. 获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'

SELECT d.dept_no,e.emp_no,s.salary
FROM employees e LEFT JOIN dept_emp d ON e.emp_no = d.emp_no
JOIN salaries s ON e.emp_no = s.emp_no
WHERE d.to_date ='9999-01-01' AND s.to_date ='9999-01-01'
AND e.emp_no NOT IN(SELECT m.emp_no FROM dept_manager m WHERE d.dept_no = m.dept_no AND m.to_date = '9999-01-01')

25. 获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',结果第一列给出员工的emp_no，第二列给出其manager的manager_no，第三列给出该员工当前的薪水emp_salary,第四列给该员工对应的manager当前的薪水

SELECT d.emp_no,m.emp_no,a.salary,b.salary
FROM dept_emp d JOIN dept_manager m ON m.dept_no =d.dept_no
JOIN salaries a ON a.emp_no = d.emp_no
JOIN salaries b ON m.emp_no = b.emp_no
WHERE d.to_date ='9999-01-01' AND m.to_date ='9999-01-01'
AND a.to_date ='9999-01-01' AND b.to_date ='9999-01-01'
AND a.salary > b.salary

26. 汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

SELECT d.dept_no,d.dept_name,t.title,count(t.title)
FROM  departments d JOIN dept_emp e ON d.dept_no = e.dept_no,titles t
WHERE e.to_date='9999-01-01' AND t.to_date='9999-01-01'
AND t.emp_no = e.emp_no
GROUP BY d.dept_no,d.dept_name,t.title

总觉得这个题目里的表设计的有问题，titile表里应该再包含一个部门ID字段吧，不然怎么知道某个title对应的是在哪个部门的时候

27. 给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)

SELECT b.emp_no,b.from_date,(b.salary-a.salary) AS salary_growth
FROM salaries a,salaries b
WHERE a.emp_no = b.emp_no
AND (STRFTIME('%Y',b.to_date)-STRFTIME('%Y',a.to_date)=1 
     OR STRFTIME('%Y',b.from_date)-STRFTIME('%Y',a.from_date)=1) 
AND salary_growth>5000
ORDER BY salary_growth DESC

题目关于每年的定义存在歧义，写SQL的时候要看着测试样例写

28. 查找描述信息中包括robot的电影对应的分类名称以及电影数目，且该分类下的电影数量>=5部

SELECT c.name,count(fc.film_id) #这里的count指的是电影描述中有robot的电影的数量
FROM category c JOIN (SELECT category_id,count(film_id) AS cc
                      FROM film_category
                      GROUP BY category_id
                      HAVING count(film_id)>=5) ffc #这里的count指的是每个分类里电影的数量
ON ffc.category_id = c.category_id,film f,film_category fc
WHERE fc.film_id = f.film_id
AND fc.category_id = c.category_id
AND f.description LIKE('%robot%')
GROUP BY c.name

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 fc.category_id is NULL

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

SELECT title,description
FROM film 
WHERE film_id IN (SELECT film_id
                 FROM film_category
                 WHERE category_id IN (SELECT category_id 
                                       FROM category
                                      WHERE name = 'Action'
                                      )
                 )