XYZ公司员工数据库SQL查询习题及参考解答

本次习题场景为XYZ私人有限公司的员工数据库，库表结构参考示意图：

______________习题列表________________

具体习题内容参考示意图：

已完成习题参考解答 #

• 习题1
  要求：请编写SQL查询语句，统计高薪员工所属的前5个国家，按薪资总金额降序排列
  参考代码：

  select top 5 (c.name, sum(s.amount) as high_sal)
  from countries as c
  join salary as s on c.id=s.id
  group by c.name
  order by high_sal desc
  

• 习题2
  要求：请编写SQL查询语句，分别统计18-25岁、26-40岁、41-60岁、60岁以上各年龄段的员工数量，同时查询每个年龄段的最高薪资。
  第一种参考写法：

  Select  DATEDIFF(YY,E.dob,GETDATE()) AS age,max(salary),
  Case 
   when age >=18 and age<=25  then ‘18-25’ 
  when age >=26 and age<=40 then ‘26-40’
  when age >=41 and age<=60 then ‘41-60’
  when age >=61 then ‘60 plus’
  END AS category,
  count(category)
  From Employees  AS E 
  JOIN SALARY AS S
  GROUP BY category
  Order by age
  

  第二种参考实现写法：

  SELECT COUNT(*) as '18-25', MAX(salary) as 'Highest Paid in 18-25'
  FROM users
  WHERE age BETWEEN 18 AND 25
  UNION
  SELECT COUNT(*) as '26-40', MAX(salary) as 'Highest Paid in 26-40'
  FROM users
  WHERE age BETWEEN 26 AND 40
  UNION
  SELECT COUNT(*) as '41-60', MAX(salary) as 'Highest Paid in 41-60'
  FROM users
  WHERE age BETWEEN 41 AND 60
  UNION
  SELECT COUNT(*) as '60+', MAX(salary) as 'Highest Paid in 60+'
  FROM users
  WHERE age>60;
  

• 习题3
  要求：已知Previous_Employment_Details表存储员工所有过往任职公司的详细信息，请编写SQL查询语句，提取入职当前公司前最后一份工作任职于TCS的员工列表。
  参考代码：

  SELECT u.name
  FROM users u
  INNER JOIN Previous_Employment_Details p 
  ON u.user_id = p.user_id
  WHERE p.company_name = 'TCS'
  ORDER BY p.date_of_joining DESC
  LIMIT 1
  

• 习题5
  要求：请编写SQL查询语句，提取员工过往任职经历中薪资最高的前3家公司及其所在地，同时返回对应员工的职位与薪资信息。
  参考代码：

  SELECT p.company_name, p.location, p.designation, p.salary
  FROM Previous_Employment_Details p
  ORDER BY p.salary DESC
  LIMIT 3;
  

内容的提问来源于stack exchange，提问作者mac