牛客《数据库SQL实战》练习总结(一)
1.查找最晚入职员工的所有信息
难点:最晚,即排序后只选一条,可用LIMIT
SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 1
2.查找入职员工时间排名倒数第三的员工所有信息
难点:倒数第三,还是可以用LIMIT。
SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 2,1
注意,LIMIT的用法有两种:1. LIMIT m,表示返回不多于m行记录。2. LIMIT m-1,n-(m-1),表示返回m到n的记录,可以理解为从m-1开始(不包括第m-1个)偏移多少个记录。例如要返回第三行,则可以是LIMIT 2,1;要返回第2到第4行,则可以是LIMIT 1,3
3.查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no
SELECT s.*,d.dept_no FROM salaries AS s,dept_manager AS d
WHERE s.emp_no = d.emp_no
AND s.to_date = '9999-01-01'
AND d.to_date = '9999-01-01'
4.查找所有已经分配部门的员工的last_name和first_name
SELECT e.last_name,e.first_name,d.dept_no
FROM employees AS e,dept_emp AS d
WHERE d.emp_no = e.emp_no
5.查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括展示没有分配具体部门的员工
难点:包括没有分配部门的员工,即有些员工在employees表但不在dept_emp表,可以用left join/right join
SELECT e.last_name,e.first_name,d.dept_no
FROM employees AS e
LEFT JOIN dept_emp AS d
ON d.emp_no = e.emp_no
6.查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序
难点:入职时候,即薪水的from_date = hire_date
SELECT e.emp_no,s.salary
FROM employees AS e,salaries AS s
WHERE e.hire_date = s.from_date
AND e.emp_no = s.emp_no
ORDER BY e.emp_no DESC
7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
难点:这里认为薪水涨一次就会多一条记录,涨幅次数可以通过count函数来统计
注意:SQL语句执行顺序,GROUP BY -> 聚合函数(sum,count,max...) -> HAVING,执行count的时候已经group by了,所以count()所有记录就ok了。
SELECT emp_no,count(*) AS t FROM salaries
GROUP BY emp_no
HAVING t>15
8.找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
难点:相同薪水只显示一次,DISTINCT
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,d.emp_no,s.salary
FROM dept_manager AS d,salaries AS s
WHERE d.emp_no = s.emp_no
AND s.to_date = '9999-01-01'
AND d.to_date = '9999-01-01'
10.获取所有非manager的员工emp_no
难点:非manager。非manager的员工的不在dept_manager表,可以通过left join/right join将两表关联,再选出dept_no为空的记录
SELECT emp_no
FROM (SELECT * FROM employees AS e
LEFT JOIN dept_manager AS d
ON e.emp_no = d.emp_no)
WHERE dept_no is NULL
11.获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date='9999-01-01'。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。
难点:manager不能是自己。可以通过员工号不能等于部门经理表的员工号来筛选
SELECT e.emp_no,m.emp_no AS 'manager_no'
FROM dept_emp AS e,dept_manager AS m
WHERE e.dept_no = m.dept_no
AND e.emp_no<>m.emp_no
AND m.to_date = '9999-01-01'
12.获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
难点:不同部门薪水最高的员工,group by 再 max()
SELECT dept_no,d.emp_no,MAX(salary) AS salary
FROM dept_emp AS d
INNER JOIN salaries AS s
ON d.emp_no = s.emp_no
WHERE d.to_date = '9999-01-01'
GROUP BY d.dept_no
13.从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t
难点:分组后每组的个数。与第7题类似,group by后再统计每组的记录数,只选取记录数大于等于2的。由于已分组,这里的count(emp_no)等价于count(*),但有空值的时候不能用,count(*)会把空值统计进去。
SELECT title,Count(emp_no) AS t
FROM titles
GROUP BY title
HAVING COUNT(emp_no) >= 2
14.从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
难点:计数的时候忽略重复的emp_no。要对重复值进行筛选,故这里count()的一定要为emp_no不能是*了。去重可以用distinct
SELECT title,COUNT(DISTINCT emp_no) AS t
FROM titles
GROUP BY title
HAVING COUNT(DISTINCT emp_no) >= 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 title,avg(salary)
FROM titles AS t,salaries AS s
WHERE s.emp_no = t.emp_no
AND s.to_date = '9999-01-01'
and t.to_date = '9999-01-01'
GROUP BY t.title
17.获取当前(to_date='9999-01-01')薪水第二多的员工的emp_no以及其对应的薪水salary
难点:第二多。LIMIT
SELECT emp_no,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
难点:选第二多的,但不能用order by排序。
有两种思路:(1).选出最多的,排除掉(NOT IN) 。(2).选出最多的,然后再选比最多的要小的。
(1)
SELECT e.emp_no,MAX(salary),last_name,first_name
FROM employees AS e,salaries AS s
WHERE e.emp_no = s.emp_no
AND to_date = '9999-01-01'
AND salary Not IN (SELECT MAX(salary) FROM salaries AS s
WHERE s.to_date = '9999-01-01')
(2)
SELECT e.emp_no,MAX(salary),last_name,first_name
FROM employees AS e,salaries AS s
WHERE e.emp_no = s.emp_no
AND to_date = '9999-01-01'
AND s.salary < (SELECT MAX(salary) FROM salaries AS s
WHERE s.to_date = '9999-01-01')
19.查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
难点:多表联结还要用left join。先把departments和dept_emp两个表联结(where/join)成表t,再将表employeesleft join 表t获得没有分配部门的员工。
SELECT e.last_name,e.first_name,t.dept_name
FROM employees e LEFT JOIN
(SELECT * FROM departments d,dept_emp de
WHERE d.dept_no = de.dept_no) t
ON e.emp_no = t.emp_no
20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
难点:查出第一天入职和最后一天的薪水。可以用order by,也可以用 min,max
SELECT (a.salary - b.salary) AS growth
FROM (SELECT salary FROM salaries WHERE emp_no = 10001
ORDER BY to_date DESC LIMIT 1 ) AS a,
(SELECT salary FROM salaries WHERE emp_no = 10001
ORDER BY to_date LIMIT 1 ) AS b
21.查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
难点:要将所有员工的入职薪水建一个表,当前薪水一个表,最后再相减。入职则是from_date=hire_date,当前则是to_date='9999-01-01'
SELECT now.emp_no,(now.salary - old.salary) AS growth
FROM (SELECT s.emp_no,s.salary FROM employees AS e INNER JOIN salaries AS s
ON e.emp_no = s.emp_no AND s.from_date = e.hire_date) AS old,
(SELECT s.emp_no,s.salary FROM employees AS e INNER JOIN salaries AS s
ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01') AS now
WHERE old.emp_no = now.emp_no
ORDER BY growth
上一篇: Scrapy连接MySQL数据库
下一篇: css3【语法要点】