牛客网题目 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_nobirth_datefirst_namelast_namegenderhire_date
100081958-02-19SaniyaKalloufiM1994-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_nobirth_datefirst_namelast_namegenderhire_date
100051955-01-21KyoichiMaliniakM1989-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_nosalaryfrom_dateto_datedept_no
10002725272001-08-029999-01-01d001
10004740572001-11-279999-01-01d004
10005946922001-09-099999-01-01d003
10006433112001-08-029999-01-01d002
10010944092001-11-239999-01-01d006
  • 答案:
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_namefirst_namedept_no
FacelloGeorgid001
省略省略省略
PiveteauDuangkaewd006
  • 答案:
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_namefirst_namedept_no
FacelloGeorgid001
省略省略省略
SluisMaryNULL(在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_nosalary
1001125828
省略省略
1000160117
  • 答案:
    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_not
1000117
1000416
1000918
  • 答案:
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_noemp_nosalary
d0011000272527
d0041000474057
d0031000594692
d0021000643311
d0061001094409
  • 答案:
    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_nomanager_no
1000110002
1000310004
1000910010
  • 答案:
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_noemp_nosalary
d0011000188958
d0021000643311
d0031000594692
d0041000474057
d0051000788070
d0061000995409
  • 答案:
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);
  • 输出示例:
titlet
Assistant Engineer2
Engineer4
省略省略
Staff3
  • 答案:
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);
  • 输出示例:
titlet
Assistant Engineer2
Engineer3
省略省略
Staff3
  • 答案:
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_nobirth_datefirst_namelast_namegenderhire_date
100111953-11-07MarySluisF1990-01-22
100051955-01-21KyoichiMaliniakM1989-09-12
100071957-05-23TzvetanZielinskiF1989-02-10
100031959-12-03PartoBamfordM1986-08-28
100011953-09-02GeorgiFacelloM1986-06-26
100091952-04-19SumantPeacF1985-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);
  • 输出示例:
titleavg
Engineer94409.0
Senior Engineer69009.2
Senior Staff91381.0
Staff72527.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_nosalary
1000994409
  • 答案:
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_nosalarylast_namefirst_name
1000994409PeacSumant
  • 答案:
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_namefirst_namedept_name
FacelloGeorgiMarketing
省略省略省略
SluisMaryNULL
  • 答案:
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_nogrowth
100110
省略省略
1001054496
1000434003
  • 答案:
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_nosalaryrank
10005946921
10009944092
10010944092
10001889583
10007880704
10004740575
10002725276
10003433117
10006433117
10011258288
  • 答案:
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_nodept_namesum
d001Marketing24
d002Finance14
d003Human Resources13
d004Production24
d005Development25
d006Quality Management25
  • 答案:
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仓库