牛客网SQL题目

发表于 更新于 分类于 Waline： 阅读次数：

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

第01题

  • 题目描述：查找最晚入职员工的所有信息
1
2
3
4
5
6
7
8
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题

  • 题目描述：查找入职员工时间排名倒数第三的员工所有信息
1
2
3
4
5
6
7
8
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
  • 答案：
1
2
3
4
5
6
7
8
9
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
1
2
3
4
5
6
7
8
9
10
11
12
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
  • 答案：
1
2
3
4
5
6
7
8
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
  • 答案：
1
2
3
4
5
6
7
8
9
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，也包括展示没有分配具体部门的员工
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)
  • 答案：
1
2
3
4
5
6
7
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进行逆序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
  • 答案：
1
2
3
4
5
6
7
8
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
1
2
3
4
5
6
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
  • 答案：
1
2
3
4
5
6
select
    emp_no,
    count(1) as t
from salaries
group by emp_no
having t>15;

第08题

  • 题目描述：找出所有员工当前(to_date=’9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
1
2
3
4
5
6
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
  • 答案：
1
2
3
4
5
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’
1
2
3
4
5
6
7
8
9
10
11
12
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
  • 答案：
1
2
3
4
5
6
7
8
9
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
  • 答案：
1
2
3
4
5
6
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。
1
2
3
4
5
6
7
8
9
10
11
12
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
  • 答案：
1
2
3
4
5
6
7
8
9
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
1
2
3
4
5
6
7
8
9
10
11
12
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
  • 答案：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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。
1
2
3
4
5
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
  • 答案：
1
2
3
4
5
6
select
    title,
    count(1) as t
from titles
group by title
having t>=2;

第14题

  • 题目描述：从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
    注意对于重复的emp_no进行忽略。
1
2
3
4
5
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
  • 答案：
1
2
3
4
5
select
    title,
    count(distinct emp_no) as t
from titles
group by title;

第15题

  • 题目描述：查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
1
2
3
4
5
6
7
8
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
  • 答案：
1
2
3
4
5
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。
1
2
3
4
5
6
7
8
9
10
11
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
  • 答案：
1
2
3
4
5
6
7
8
9
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
1
2
3
4
5
6
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
  • 答案：
1
2
3
4
5
6
7
8
9
10
11
12
13
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
  • 答案：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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，也包括暂时没有分配部门的员工
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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
  • 答案：
1
2
3
4
5
6
7
8
9
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
1
2
3
4
5
6
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进行升序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
  • 答案：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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升序排列
1
2
3
4
5
6
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
  • 答案：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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
  • 答案：
1
2
3
4
5
6
7
8
9
10
11
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仓库