牛客网数据库SQL实战剖析(11-20)「终于解决」

牛客网数据库SQL实战剖析(11-20)「终于解决」牛客网数据库SQL实战剖析(11-20)

牛客网数据库SQL实战剖析(11-20)

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

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

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`)
);

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`)
);

代码100分

解析:首先要查询员工的领导,只需要dept_emp左连接dept_manager。此外还有两个条件,当前的manager是自己的话结果不显示,以及to_date="9999-01-01",将其写成 where 子句。

代码100分select e.emp_no, m.emp_no as manager_no
from dept_emp e
         left join dept_manager m on e.dept_no = m.dept_no
where e.to_date = "9999-01-01"
  and m.to_date = "9999-01-01"
  and e.emp_no != m.emp_no;

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

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

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解题思路:首先按照部门进行分组,然后用 MAX() 函数获取部门中工资最大值。

代码100分select d.dept_no, s.emp_no, MAX(s.salary) AS salary
from dept_emp d
         join salaries s on d.emp_no = s.emp_no
where s.to_date = "9999-01-01"
  and d.to_date = "9999-01-01"
GROUP BY d.dept_no;

13. 从titles表获取按照title进行分组

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

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

解题思路:分组使用group by,分组之后还要判断每组个数,则用 having count(*)

select title, count(*) AS t
from titles
group by title
having t >= 2;

14. 从titles表获取按照title进行分组,注意对于重复的emp_no进行忽略。

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

CREATE TABLE IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

解题思路:在13题基础上,原来是统计整个组的格式,本题要根据 emp_no 进行去重,所以使用 count(DISTINCT emp_no)

select title, count(DISTINCT emp_no) AS t
from titles
group by title
having t >= 2;

15. 查找employees表

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

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

解题思路:这道题思路很直接,emp_no为奇数,即取余之后不等于0,last_name != "Mary",再用order by xxx DESC逆序排列。

select * from employees
where emp_no%2 != 0 AND last_name != "Mary"
order by hire_date DESC ;

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

统计出当前各个title类型对应的员工当前(to_date=”9999-01-01″)薪水对应的平均工资。结果给出title以及平均工资avg。

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

解题思路:这道题和第12题是一个意思,先通过 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. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

获取当前(to_date=”9999-01-01″)薪水第二多的员工的emp_no以及其对应的薪水salary

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解题思路:这道题前面有类似的,在子查询里面,通过排序查出第二多的薪水。然后查询该薪水的员工。

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
);

但看输出样例只有一行记录,感觉题目本意应该是薪水第二多的人只有一个,让我们直接查询之后用薪水进行排序,再逆序查到这个人。

select emp_no, salary
from salaries
order by salary DESC
LIMIT 1,1;

两种都能AC,第二种应该是题目想要我们给出的答案。


18. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary,不准使用order by

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

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解题思路:薪水第二多的,就是除掉薪水最多的数据之后,新数据集中,薪水最多的。说法有点绕,看SQL就能看明白。

SELECT e.emp_no, MAX(s.salary) AS 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");

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

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

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

解题思路:本题考察三个表的关联查询,departments employees 通过中间表dept_emp 关联在一起。

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_no为10001其自入职以来的薪水salary涨幅值growth

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

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解题思路:涨幅即最大值-最小值,简单这样理解即可。

select MAX(salary)-MIN(salary) AS growth
from salaries
where emp_no=10001;

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/9145.html

(0)
上一篇 2023-02-14
下一篇 2023-02-14

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注