牛客数据库SQL实战刷题记录 D-2

SQL刷题记录 D-2

1. 题目：查找当前薪水详情以及部门编号dept_no

• 题目描述

查找各个部门当前(to_date='9999-01-01')领导
当前薪水详情以及其对应部门编号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`));
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`));

• 输出：

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
10010	94409	2001-11-23	9999-01-01	d006

• 代码

SELECT s.*,dep.dept_no
FROM salaries as s
INNER JOIN dept_manager as dep
ON s.emp_no=dep.emp_no
WHERE s.to_date='9999-01-01'
and dep.to_date='9999-01-01'

• 思路
  利用INNER JOIN连接两个表，其中主表是salaries，因为输出中dept_no在最后一列。找出 “当前领导” 和 “当前薪水”。

2. 题目：查找所有已经分配部门的员工的last_name和first_name

• 题目描述

查找所有已经分配部门的员工的last_name和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`));

• 输出

last_name	first_name	dept_no
Facello	Georgi	d001
省略	省略	省略
Piveteau	Duangkaew	d006

• 代码：

SELECT e.last_name, e.first_name,dep.dept_no
FROM employees as e
JOIN dept_emp as dep
ON e.emp_no=dep.emp_no

• 思路：
  使用inner join连接两个表中需要输出的列

3. 题目：查找所有员工的last_name和first_name以及对应部门编号dept_no

• 题目描述

查找所有员工的last_name和first_name以及对应部门编号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`));

• 输出：

last_name	first_name	dept_no
Facello	Georgi	d001
省略	省略	省略
Sluis	Mary	Mary

• 代码：

SELECT e.last_name,e.first_name,dep.dept_no
FROM employees as e
LEFT JOIN dept_emp as dep
ON e.emp_no=dep.emp_no

• 思路：
  使用LEFT JOIN连接左右两表，无论右表是否有值，左表都会全部读取

下图描述了INNER JOIN，FULL OUTER JOIN,LEFT JOIN 和RIGHT JOIN的区别
来源：SQL 连接(JOIN)用法

4. 题目：查找所有员工入职时候的薪水情况

• 题目描述：

查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_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`));
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`));

• 输出：

emp_no	salary
10011	25828
省略	省略
10001	60117

• 代码：

SELECT e.emp_no,s.salary
FROM employees as e
INNER JOIN salaries as s
ON e.emp_no=s.emp_no
AND e.hire_date=s.from_date 
ORDER BY e.emp_no DESC

• 思路：
  由于会涨薪，每个人的薪水可能对应多个值，所以salaries表中emp_no不是唯一的，但是employees表中emp_no唯一，这样可以根据employees表中的emp_no找到salaries表中的对应emp_no。
  对于入职时薪水，salaries的时间应该和employees表中的hire_date相同。
  最后按照emp_no降序排列。

2019/11/13 好像JOIN的题暂告一段落了？按你胃，明天继续吧