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

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

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

• 题目描述：

获取所有部门中当前员工薪水最高的相关信息，
给出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`));

• 输出：

dept_no	emp_no	salary
d001	10001	88958
d002	10006	43311
d003	10005	94692
d004	10004	74057
d005	10007	88070
d006	10009	95409

• 代码：

SELECT dep.dept_no, dep.emp_no, MAX(s.salary)
FROM dept_emp as dep
INNER JOIN salaries as s
ON dep.emp_no=s.emp_no
WHERE dep.to_date='9999-01-01'
AND s.to_date='9999-01-01'
GROUP BY dep.dept_no

• 思路：

1. 利用MAX()函数，找出最高薪水；
2. 限制两张表的emp_no相同，用INNER JOIN连接两张表；
3. 当前日期即to_date为‘9999-01-01’；
4. 因为要找出每个部门最高薪水的员工，所以用GROUP BY给部门分组

2. 题目：从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);

• 输出：

title	t
Assistant Engineer	2
Engineer	4
省略	省略
Staff	3

• 代码：

SELECT title, COUNT(title) as t
FROM titles
GROUP BY title
HAVING COUNT(title)>=2

• 思路：
  利用COUNT()函数计算每个title出现的次数并记为t；HAVING 函数和合计函数一起使用找出title次数大于等于两次的

3. 题目：从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略

• 题目描述：

从titles表获取按照title进行分组，每组个数大于等于2，
给出title以及对应的数目t。
注意对于重复的title进行忽略。
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);

• 输出：

title	t
Assistant Engineer	2
Engineer	3
省略	省略
Staff	3

• 代码：

SELECT title, COUNT(DISTINCT emp_no) as t
FROM titles
GROUP BY title
HAVING t>=2

• 思路：
  这题题目没写清楚，是对于同一title的重复的emp_no省略哇。所以只要用DISTINCT找出不同的emp_no就好了。

4. 题目：查找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	birth_date	first_name	last_name	gender	hire_date
10011	1953-11-07	Mary	Sluis	F	1990-01-22
10005	1955-01-21	Kyoichi	Maliniak	M	1989-09-12
10007	1957-05-23	Tzvetan	Zielinski	F	1989-02-10
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10009	1952-04-19	Sumant	Peac	F	1985-02-18

• 代码：

SELECT * FROM employees
WHERE employees.emp_no%2=1
AND last_name <> "Mary"
ORDER BY hire_date DESC

• 思路：
  奇数：number%2=1或mod(number,2)=1
  偶数：number%2=0或mod(number,2)=0
  <>表示不等于