牛客网SQL题目

发表于 2020-01-04 更新于 2020-03-12 分类于 SQL Waline：

牛客网题目 https://www.nowcoder.com/ta/sql
共计61道题目，现已全部完成

第01题

题目描述：查找最晚入职员工的所有信息

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
10008	1958-02-19	Saniya	Kalloufi	M	1994-09-15

答案：

1
2
3

select * 
from employees
where hire_date=(select hire_date from employees order by hire_date desc limit 1 )

第02题

题目描述：查找入职员工时间排名倒数第三的员工所有信息

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
10005	1955-01-21	Kyoichi	Maliniak	M	1989-09-12

答案：

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

第03题

题目描述：查找各个部门当前(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 
    t2.*,
    t1.dept_no
from salaries as t2
join dept_manager as t1
on t1.emp_no=t2.emp_no
where t1.to_date='9999-01-01'
and t2.to_date='9999-01-01';

第04题

题目描述：查找所有已经分配部门的员工的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
    t1.last_name,
    t1.first_name,
    t2.dept_no
from
    employees as t1,
    dept_emp as t2
where t1.emp_no=t2.emp_no
and t2.dept_no is not null;

第05题

题目描述：查找所有员工的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	NULL(在sqlite中此处为空,MySQL为NULL)

答案：

select
    t2.last_name,
    t2.first_name,
    t1.dept_no
from employees as t2
left join dept_emp as t1
on t1.emp_no=t2.emp_no;

第06题

题目描述：查找所有员工入职时候的薪水情况，给出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
    t1.emp_no,
    t2.salary
from employees as t1
join salaries as t2
on t1.emp_no=t2.emp_no
where t1.hire_date=t2.from_date
order by t1.emp_no desc;

第07题

题目描述：查找薪水涨幅超过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`));

输出示例：

emp_no	t
10001	17
10004	16
10009	18

答案：

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

第08题

题目描述：找出所有员工当前(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`));

输出示例：

salary
94692
94409
88958
88070
74057
72527
59755
43311
25828

答案：

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

第09题

题目描述：获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=’9999-01-01’

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

输出示例：

dept_no	emp_no	salary
d001	10002	72527
d004	10004	74057
d003	10005	94692
d002	10006	43311
d006	10010	94409

答案：

select
    t2.dept_no,
    t2.emp_no,
    t1.salary
from salaries as t1
join dept_manager as t2
on t1.emp_no=t2.emp_no
where t1.to_date='9999-01-01'
and t2.to_date='9999-01-01'

第10题

题目描述：获取所有非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`));

输出示例：

emp_no
10001
10003
10007
10008
10009
10011

答案：

select
    t1.emp_no
from employees as t1
left join dept_manager as t2
on t1.emp_no=t2.emp_no
where t2.dept_no is null;

第11题

题目描述：获取所有员工当前的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`));

输出示例：

emp_no	manager_no
10001	10002
10003	10004
10009	10010

答案：

select
    t1.emp_no,
    t2.emp_no as manager_no
from dept_emp as t1
left join dept_manager as t2
on t1.dept_no=t2.dept_no
where t1.to_date='9999-01-01'
and t2.to_date='9999-01-01'
and t1.emp_no<>t2.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`));

输出示例：

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

答案：

select
    t1.dept_no, 
    t1.emp_no, 
    t2.salary
from dept_emp as t1 
inner join salaries as t2
on t1.emp_no = t2.emp_no
and t1.to_date = '9999-01-01'
and t2.to_date = '9999-01-01'
where t2.salary = (
    select max(t3.salary)
    from dept_emp as t4
    inner join salaries as t3
    on t3.emp_no = t4.emp_no
    and t3.to_date = '9999-01-01'
    and t4.to_date = '9999-01-01'
    where t4.dept_no = t1.dept_no
    group by t4.dept_no)
order by t1.dept_no;

第13题

题目描述：从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(1) as t
from titles
group by title
having t>=2;

第14题

题目描述：从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);

输出示例：

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

答案：

select
    title,
    count(distinct emp_no) as t
from titles
group by title;

第15题

题目描述：查找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 emp_no&1=1
and last_name<>"Mary"
order by hire_date desc;

第16题

题目描述：统计出当前各个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);

输出示例：

title	avg
Engineer	94409.0
Senior Engineer	69009.2
Senior Staff	91381.0
Staff	72527.0

答案：

select
    title,
    avg(salary) as avg
from salaries as t1
join titles as t2
on t1.emp_no=t2.emp_no
and t1.to_date='9999-01-01'
and t2.to_date='9999-01-01'
group by t2.title;

第17题

题目描述：获取当前（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`));

输出示例：

emp_no	salary
10009	94409

答案：

select
    emp_no,
    salary
from salaries
where to_date='9999-01-01'
and salary=(
    select salary
    from salaries
    where to_date='9999-01-01'
    group by salary
    order by salary desc
    limit 1,1
);

第18题

题目描述：查找当前薪水(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`));

输出示例：

emp_no	salary	last_name	first_name
10009	94409	Peac	Sumant

答案：

SELECT
    t2.emp_no,
    t1.salary,
    t2.last_name,
    t2.first_name
FROM salaries AS t1
JOIN employees AS t2
ON t1.emp_no=t2.emp_no
AND t1.to_date='9999-01-01'
WHERE (
    SELECT COUNT(DISTINCT t3.salary)
    FROM salaries AS t3
    WHERE t3.to_date='9999-01-01'
    AND t3.salary>t1.salary
)=1
group by salary;

第19题

题目描述：查找所有员工的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`));

输出示例：

last_name	first_name	dept_name
Facello	Georgi	Marketing
省略	省略	省略
Sluis	Mary	NULL

答案：

SELECT
    last_name,
    first_name,
    dept_name
FROM employees AS t1
LEFT JOIN dept_emp AS t2
ON t1.emp_no=t2.emp_no
LEFT JOIN departments AS t3
ON t2.dept_no=t3.dept_no;

第20题

查找员工编号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`));

输出示例：

growth
28841

答案：

1
2
3

select max(salary)-min(salary) as growth
from salaries
where emp_no=10001;

第21题

题目描述：查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

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	growth
10011	0
省略	省略
10010	54496
10004	34003

答案：

SELECT
	emp_no,
	growth
FROM(SELECT
		a.emp_no,
		b.salary-a.salary AS growth
	FROM
		(SELECT
			t1.emp_no,
			salary
		FROM 
			employees AS t1,
			salaries AS t2
		WHERE t1.emp_no=t2.emp_no
		AND t1.hire_date=t2.from_date) AS a,
		(SELECT
			t1.emp_no,
			salary
		FROM 
			employees AS t1,
			salaries AS t2
		WHERE t1.emp_no=t2.emp_no
		AND t2.to_date='9999-01-01') AS b
	WHERE a.emp_no=b.emp_no)AS c
ORDER BY c.growth

第22题

对所有员工的当前(to_date=’9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照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	rank
10005	94692	1
10009	94409	2
10010	94409	2
10001	88958	3
10007	88070	4
10004	74057	5
10002	72527	6
10003	43311	7
10006	43311	7
10011	25828	8

答案：

SELECT
	emp_no,
	growth
FROM(SELECT
		a.emp_no,
		b.salary-a.salary AS growth
	FROM
		(SELECT
			t1.emp_no,
			salary
		FROM 
			employees AS t1,
			salaries AS t2
		WHERE t1.emp_no=t2.emp_no
		AND t1.hire_date=t2.from_date) AS a,
		(SELECT
			t1.emp_no,
			salary
		FROM 
			employees AS t1,
			salaries AS t2
		WHERE t1.emp_no=t2.emp_no
		AND t2.to_date='9999-01-01') AS b
	WHERE a.emp_no=b.emp_no)AS c
ORDER BY c.growth

输出示例：

dept_no	dept_name	sum
d001	Marketing	24
d002	Finance	14
d003	Human Resources	13
d004	Production	24
d005	Development	25
d006	Quality Management	25

答案：

select
    t1.dept_no,
    t3.dept_name,
    count(t2.salary) as sum
from
    dept_emp as t1,
    salaries as t2,
    departments as t3
where t3.dept_no=t1.dept_no
and t1.emp_no=t2.emp_no
group by t1.dept_no

第22-61题

后面太多，就不罗列上去了，附上仓库地址

Github仓库