目录
1、 从titles表中获取title并按照title分组
2、从titles表中获取title并按照title分组,重复的emp_no忽略
3 查找employees表
4 统计平均工资
5 获取第二多的薪水
6 获取第二多的薪水不使用order by
7 查找员工的名字和对应的部门名字
8 查找薪水涨幅
9 查找所有员工的薪水涨幅
10 对所有员工薪水排序
11 获取员工其当前的薪水比其manager当前薪水还高的相关信息
12 汇总各个部门当前员工的title类型的分配数目
13 给出每个员工每年薪水涨幅超过5000的员工编号emp_no
1、 从titles表中获取title并按照title分组
题目描述:
从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
输出描述:
1 /*
2 此题应注意以下三点:3 1、用COUNT()函数和GROUP BY语句可以统计同一title值的记录条数4 2、根据题意,输出每个title的个数为t,故用AS语句将COUNT(title)的值转换为t5 3、由于WHERE后不可跟COUNT()函数,故用HAVING语句来限定t>=2的条件6 */
7
8 SELECT title, COUNT(title) AS t FROMtitles9 GROUP BY title HAVING t >= 2
2、从titles表中获取title并按照title分组,重复的emp_no忽略
题目描述:
从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。此时忽略同一title下的emp_no。
输出描述:
1 /*
2 此题应注意以下三点:3 1、先用GROUP BY title将表格以title分组,再用COUNT(DISTINCT emp_no)可以统计同一title值且不包含重复emp_no值的记录条数4 2、根据题意,输出每个title的个数为t,故用AS语句将COUNT(DISTINCT emp_no)的值转换为t5 3、由于WHERE后不可跟COUNT()函数,故用HAVING语句来限定t>=2的条件6 */
7
8 SELECT title, COUNT(DISTINCT emp_no) AS t FROMtitles9 GROUP BY title HAVING t >= 2
3 查找employees表
题目描述:
查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列
输出描述:
1 /*
2 三点需要注意:3 1、员工号为奇数,则emp_no取余应为14 2、last_name不为Mary,用‘!=’表示也可以用<>表示5 3.根据hire_date逆序排列,用desc6 */
7
8 SELECT * FROMemployees9 WHERE emp_no%2 =1
10 AND last_name <> 'Mary'
11 ORDER BY hire_date DESC;
4 统计平均工资
题目描述:
统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。
输出描述:
SELECT t.title,AVG(s.salary)FROM salaries AS s INNER JOIN titles AStON s.emp_no =t.emp_noAND s.to_date = '9999-01-01'
AND t.to_date = '9999-01-01'
GROUP BY title
5 获取第二多的薪水
题目描述
获取当前(to_date='9999-01-01')薪水第二多的员工的emp_no以及其对应的薪水salary
输出描述:
1 select emp_no, salary fromsalaries2 where to_date = '9999-01-01' and salary = (select distinct salary from salaries order by salary desc limit 1,1)3 避免了2个问题:4 (1) 首先这样可以解决多个人工资相同的问题;5 (2) 另外,筛选出第二多的工资时要注意distinct salary,否则不能选出第二多的工资。
6 获取第二多的薪水不使用order by
题目描述:
查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不准使用order by
输出描述:
#要求次高的,那么一个思路便是从在去除掉最高的数据中选择最高的即可,有两种实现方法SELECT s.emp_no,MAX(s.salary) ASsalary,e.last_name,e.first_nameFROM salaries AS s,employees ASeWHERE s.salary NOT IN(SELECT MAX(salary) FROMsalaries)AND e.emp_no =s.emp_noAND s.to_date = '9999-01-01'#第二种方法也是这个思路,不过用的不是NOTIN而是小于符合SELECT s.emp_no,MAX(s.salary) ASsalary,e.last_name,e.first_nameFROM salaries AS s,employees ASeWHERE s.salary < (SELECT MAX(salary) FROMsalaries)AND e.emp_no =s.emp_noAND s.to_date = '9999-01-01'
7 查找员工的名字和对应的部门名字
题目描述:
查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
输出描述:
#在这里就涉及到三个表的查询了,employees表提供姓名,部门员工表提供员工对应的部门编号
#部门表则根据部门编号提供部门名称
#根据题目要求要包含还没有分配部门的员工,所以用where进行联结肯定是不行的,所以采取左联或者右联的方式
#在第二次联结的时候,是在第一次员工和部门员工表连接的基础上进行,此时也不能用whereSELECTe.last_name,e.first_name,d.dept_nameFROM (employees AS e LEFT OUTER JOIN dept_emp AS de ON e.emp_no =de.emp_no)LEFT OUTER JOIN departments AS d ON de.dept_no = d.dept_no
也可以先让后面两张表连在一起:
SELECTe.last_name,e.first_name,d.dept_nameFROM employees AS e LEFT OUTER JOIN( departmentsAS d INNER JOIN dept_emp AS de ON d.dept_no =de.dept_no)ON e.emp_no = de.emp_no
8 查找薪水涨幅
题目描述:
查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
输出描述:
#根据日期选择出日期最大即最新的工资减去最旧的日期即可SELECT(
(SELECT salary FROM salaries WHERE emp_no = '10001' ORDER BY to_date DESC LIMIT 1) -(SELECT salary FROM salaries WHERE emp_no = '10001' ORDER BY to_date ASC LIMIT 1)
)AS growth
9 查找所有员工的薪水涨幅
题目描述:
查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
输出描述:
#我一直想着分组去处理,因为每一个emp_no有很多条数据,但后来发现这条路走不通,因为分组时要分别对应不同的emp_no计算
#对于这个问题无法解决因此考虑其他方法。观察两张表就可以发现from_date和hire_date之间的联系了,利用这个可以快速定义查询条件SELECT e.emp_no,s1.salary-s2.salary AS growth FROMemployees eINNER JOIN salaries s1 ON s1.to_date="9999-01-01" AND s1.emp_no=e.emp_noINNER JOIN salaries s2 ON s2.from_date=e.hire_date AND s2.emp_no=e.emp_noORDER BY growth ASC;
10 对所有员工薪水排序
题目要求:
对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名,相同salary并列且按照emp_no升序排列。
输出要求:
题目分析:
(1)从两张相同的salaries表(分别为s1与s2)进行对比分析,先将两表限定条件设为to_date = '9999-01-01',挑选出当前所有员工的薪水情况。
(2)本题的精髓在于 s1.salary <= s2.salary,意思是在输出s1.salary的情况下,有多少个s2.salary大于等于s1.salary,比如当s1.salary=94409时,有3个s2.salary(分别为94692,94409,94409)大于等于它,但由于94409重复,利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。
(3)千万不要忘了GROUP BY s1.emp_no,否则输出的记录只有一条(可能是第一条或者最后一条,根据不同的数据库而定),因为用了合计函数COUNT(
(4)最后先以 s1.salary 逆序排列,再以 s1.emp_no 顺序排列输出结果
--这里用count的计数来实现排序
SELECT s1.emp_no,s1.salary,COUNT(DISTINCT s2.salary) ASrankFROM salaries AS s1,salaries ASs2--s2的salary去重后,寻找小于等于的有多少个
WHERE s1.to_date='9999-01-01' AND s2.to_date='9999-01-01' AND s1.salary <=s2.salary--使用count一般与group by连用来分组求和
GROUP BYs1.emp_no--第一个要求是按照salary降序,当相同时才进行升序排列,所以应该把降序的要求放在首位
ORDER BY s1.salary DESC, s1.emp_no ASC
11 获取员工其当前的薪水比其manager当前薪水还高的相关信息
题目描述:
获取员工其当前的薪水比其manager当前薪水还高的相关信息,当前表示to_date='9999-01-01',结果第一列给出员工的emp_no,第二列给出其manager的manager_no,第三列给出该员工当前的薪水emp_salary,第四列给该员工对应的manager当前的薪水manager_salary
输出描述:
题解思路:
本题主要思想是创建两张表(一张记录当前所有员工的工资,另一张只记录部门经理的工资)进行比较,具体思路如下:
(1)先用INNER JOIN连接salaries和demp_emp,建立当前所有员工的工资记录sem
(2)再用INNER JOIN连接salaries和demp_manager,建立当前所有员工的工资记录sdm
(3)最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary找出同一部门中工资比经理高的员工,并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary
SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, sem.salary AS emp_salary, sdm.salary ASmanager_salaryFROM (SELECT s.salary, s.emp_no, de.dept_no FROM salaries s INNER JOINdept_emp deON s.emp_no = de.emp_no AND s.to_date = '9999-01-01' ) ASsem,
(SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s INNER JOINdept_manager dmON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' ) ASsdmWHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary
12 汇总各个部门当前员工的title类型的分配数目
题目描述:
汇总各个部门当前员工的title类型的分配数目,结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
输出描述:
题解思路:
(1)先用 INNER JOIN 连接 dept_emp 与 salaries,根据测试数据添加限定条件 de.to_date = '9999-01-01' AND t.to_date = '9999-01-01',即当前员工的当前头衔
(2)再用 INNER JOIN 连接departments,限定条件为 de.dept_no = dp.dept_no,即部门编号相同
(3)最后用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组,用 COUNT(t.title) 统计相同部门下相同头衔的员工个数
SELECT de.dept_no, dp.dept_name, t.title, COUNT(t.title) AS `count`FROM titles AS t INNER JOIN dept_emp ASdeON t.emp_no = de.emp_no AND de.to_date = '9999-01-01' AND t.to_date = '9999-01-01'
INNER JOIN departments ASdpON de.dept_no =dp.dept_noGROUP BY de.dept_no, t.title
对于为什么要有两个分组:
如果只按dept_no分组,那么每个dept_no只会出现一条记录,则不能体现同一dept_no中的不同title。GROUP BY de.dept_no, t.title 的作用是同时将de.dept_no和t.title分组,即要两者都相同才能判别为相同的分组,只要一个不同就算不同的分组。
13 给出每个员工每年薪水涨幅超过5000的员工编号emp_no
题目描述:
给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth,并按照salary_growth逆序排列。
提示:在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
输出描述:
题目思路:
(1)假设s1是涨薪水前的表,s2是涨薪水后的表,因为每个员工涨薪水的时间不全固定,有可能一年涨两次,有可能两年涨一次,所以每年薪水的涨幅,应该理解为两条薪水记录的from_date相同或to_date相同。如果只限定to_date相同则可能会造成数据丢失。
(2)找到s1与s2符合要求的记录后,用s2的薪水减去s1的薪水,用salary_growth表示,加上限定条件 s1.emp_no = s2.emp_no AND salary_growth > 5000,即同一员工每年涨幅超过5000的记录
(3)最后依次输出emp_no、from_date、salary_growth,并以salary_growth逆序排列
SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) ASsalary_growthFROM salaries AS s1, salaries ASs2WHERE s1.emp_no =s2.emp_noAND salary_growth > 5000
AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1
OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1)ORDER BY salary_growth DESC
0