牛客数据库题解

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

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

https://0x9.me/mZ7NW

Description

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10000 | 1956-01-02 | echo       | ruiz      | M      | 1979-01-02 |
|  10001 | 1956-07-02 | lucy       | young     | F      | 1989-01-02 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
+--------+------------+------------+-----------+--------+------------+

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

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
+--------+------------+------------+-----------+--------+------------+

SQL Schema

DROP TABLE
IF
    EXISTS employees;

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

insert into employees 
values
    (10000,'1956-01-02','echo','ruiz','M','1979-01-02'),
    (10001,'1956-07-02','lucy','young','F','1989-01-02'),
    (10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');

Solution

select * from employees order by hire_date desc limit 1;

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

https://0x9.me/8rMBF

Description

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10000 | 1956-01-02 | echo       | ruiz      | M      | 1979-01-02 |
|  10001 | 1956-07-02 | lucy       | young     | F      | 1989-01-02 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
+--------+------------+------------+-----------+--------+------------+

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

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10000 | 1956-01-02 | echo       | ruiz      | M      | 1979-01-02 |
+--------+------------+------------+-----------+--------+------------+

SQL Schema

DROP TABLE
IF
    EXISTS employees;

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

insert into employees 
values
    (10000,'1956-01-02','echo','ruiz','M','1979-01-02'),
    (10001,'1956-07-02','lucy','young','F','1989-01-02'),
    (10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');

Solution

select * from employees order by hire_date desc limit 2,1;

3. 查找各个部门当前(to_date=’9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no

Description

salaries表
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
...

dept_manager表
+---------+--------+------------+------------+
| dept_no | emp_no | from_date  | to_date    |
+---------+--------+------------+------------+
| d001    |  10002 | 1996-08-03 | 9999-01-01 |
| d004    |  10004 | 1986-12-01 | 9999-01-01 |
| d003    |  10005 | 1989-09-12 | 9999-01-01 |
| d002    |  10006 | 1990-08-05 | 9999-01-01 |
| d005    |  10010 | 1996-11-24 | 2000-06-26 |
| d006    |  10010 | 2000-06-26 | 9999-01-01 |
+---------+--------+------------+------------+

查找各个部门当前(to_date=’9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no

+--------+--------+------------+------------+---------+
| emp_no | salary | from_date  | to_date    | dept_no |
+--------+--------+------------+------------+---------+
|  10002 |  72527 | 2001-08-02 | 9999-01-01 | d001    |
|  10004 |  74057 | 2001-11-27 | 9999-01-01 | d004    |
|  10005 |  94692 | 2001-09-09 | 9999-01-01 | d003    |
|  10006 |  43311 | 2001-08-02 | 9999-01-01 | d002    |
+--------+--------+------------+------------+---------+

SQL Schema

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

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

Solution

select dm.emp_no, s.salary, dm.from_date, dm.to_date, dm.dept_no
     from dept_manager dm , salaries s where dm.emp_no = s.emp_no
     and dm.to_date = '9999-01-01' and s.to_date = '9999-01-01' ;

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

Description

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

SQL Schema

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

Solution

select e.last_name, e.first_name, dm.dept_no from employees e 
   join dept_emp dm one.emp_no = dm.emp_no ;