牛客网 数据库SQL试题

目录

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

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

Sql3.查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no

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

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

Sql6.查找所有员工入职时候的薪水情况

Sql7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

Sql8.找出所有员工当前具体的薪水salary情况

Sql10. 获取所有非manager的员工emp_no

---

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

查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为--,mysql为comment)
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`));

解法一：子查询，这种方法（先找出 hire_date 字段的最大值，再把该值当成  employees 表的 hire_date 查询条件。 ）

SELECT * FROM employees WHERE hire_date = (SELECT MAX(hire_date) FROM employees);

解法二：排序，降序（这个方法比较局限，如果同一天有多个员工入职就有问题了）。

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 0,1;

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

查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天
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`));

思路：按入职时间倒序排列，然后用limit选择第三个即可。

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

可能有多个记录，所有最佳结果写成如下形式：

select * from employees where hire_date=
(select distinct hire_date from employees order by `hire_date` desc limit 2,1);

Sql3.查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no

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

(注:输出结果以salaries.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`));

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, --  '员工编号'
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

内连接

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

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

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

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

SELECT e.last_name,e.first_name,de.dept_no 
FROM employees e,dept_emp de where e.emp_no=de.emp_no;

SELECT e.last_name,e.first_name,de.dept_no 
FROM employees e JOIN dept_emp de ON e.emp_no=de.emp_no;

Sql5.查找所有员工的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`));
注意：
INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。

SELECT e.last_name,e.first_name,de.dept_no FROM 
employees e LEFT JOIN dept_emp de ON e.emp_no=de.emp_no;

Sql6.查找所有员工入职时候的薪水情况

查找所有员工入职时候的薪水情况，给出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`));

解法一：只调用1表，先按照员工编号分组，再用having过滤出 最小开始日期的情况，获取salaries中from_data中最小的对应的salary和emp_no，因为salaries可能涨薪，所以这张表一个员工可能有多条记录。

SELECT emp_no,salary FROM salaries GROUP BY emp_no
HAVING min(from_date) ORDER BY emp_no DESC;

解法二：连接两表，因为薪水表的员工编号不唯一，所以限制条件：雇佣日期 = 薪水开始日期，即hire_data时间和from_data时间一致

SELECT e.emp_no,s.salary
FROM employees e,salaries s WHERE e.emp_no=s.emp_no AND e.hire_date = s.from_date
ORDER BY e.emp_no DESC;

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

解法三：子查询

SELECT t.* FROM (SELECT e.emp_no,s.salary
FROM employees e,salaries s WHERE e.emp_no=s.emp_no AND e.hire_date = s.from_date) t ORDER BY t.emp_no DESC;

Sql7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
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`));

此题应注意以下四点：

1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数

2、根据题意，输出的涨幅次数为t，故用AS语句将COUNT(emp_no)的值转换为t

3、由于COUNT()函数不可用于WHERE语句中，故使用HAVING语句来限定t>15的条件

4、最后存在一个理解误区，涨幅超过15次，salaries中相应的记录数应该超过16（从第2条记录开始算作第1次涨幅），不过题目为了简单起见，将第1条记录当作第1次涨幅，所以令t>15即可

/**  注意： 严格来说，下一条salary高于本条才算涨幅，但本题只要出现了一条记录就算一次涨幅，salary相同可以理解为涨幅为0，salary变少理解为涨幅为负 **/

select emp_no,count(*) t from salaries 
group by emp_no having t>15

Sql8.找出所有员工当前具体的薪水salary情况

找出所有员工当前(to_date='9999-01-01')具体的薪水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`));

去重时：distinct与group by的使用

1、当对系统的性能高并数据量大时使用group by

2、当对系统的性能不高时使用数据量少时两者皆可

3、尽量使用group by  

解法一：根据group by 去重 （推荐）

select emp_no,count(*) t from salaries group by emp_no having t>15;

解法二：根据distinct去重

select distinct salary from salaries where to_date='9999-01-01' order by salary desc;

Sql10. 获取所有非manager的员工emp_no

获取所有非manager的员工emp_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 `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 dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10006,'1990-08-05','9999-01-01');
INSERT INTO dept_manager VALUES('d003',10005,'1989-09-12','9999-01-01');
INSERT INTO dept_manager VALUES('d004',10004,'1986-12-01','9999-01-01');
INSERT INTO dept_manager VALUES('d005',10010,'1996-11-24','2000-06-26');
INSERT INTO dept_manager VALUES('d006',10010,'2000-06-26','9999-01-01');

INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

解法一：使用not in选出在employees但不在dept_manager中的emp_no记录

SELECT emp_no from employees WHERE emp_no 
NOT IN (SELECT emp_no from dept_manager);

解法二：使用not exists选出在employees但不在dept_manager中的emp_no记录

SELECT e.emp_no from employees e WHERE 
NOT EXISTS 
(SELECT d.emp_no from dept_manager d WHERE e.emp_no = d.emp_no);

解法三：使用left join，employees作为主表，dept_manager为从表，从表dept_manager的emp_no 为null

SELECT em.emp_no from employees em 
LEFT JOIN dept_manager dm on em.emp_no=dm.emp_no 
WHERE dm.emp_no is null;