mysql牛客网_牛客网数据库sql实战-基于MYSQL实现（一）

最新推荐文章于 2024-03-13 09:47:23 发布

林大路

最新推荐文章于 2024-03-13 09:47:23 发布

阅读量177

点赞数

文章标签： mysql牛客网

本文链接：https://blog.csdn.net/weixin_42467661/article/details/113631299

版权

1 找最晚入职员工的所有信息

SELECT

FROM

employees

WHERE

hire_date = ( SELECT max( hire_date ) FROM employees );

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

SELECT

FROM

employees

WHERE

hire_date = ( SELECT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2, 1 );

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

SELECT

a.*,

b.dept_no

FROM

salaries a,

dept_manager b

WHERE

a.emp_no = b.emp_no

AND a.to_date = '9999-01-01'

AND b.to_date = '9999-01-01';

4 查找所有已经分配部门的员工的last_name和first_name以及dept_no

SELECT

b.last_name,

b.first_name,

a.dept_no

FROM

employees AS b

INNER JOIN dept_emp AS a ON a.emp_no = b.emp_no;

5 查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

SELECT

a.last_name,

a.first_name,

b.dept_no

FROM

employees AS a

LEFT JOIN dept_emp AS b ON a.emp_no = b.emp_no;

6 查找所有员工入职时候的薪水情况，给出emp_no以及salary，并按照emp_no进行逆序

SELECT

a.emp_no,

b.salary

FROM

employees AS a

LEFT JOIN salaries AS b ON a.emp_no = b.emp_no

GROUP BY

a.emp_no

HAVING

max( a.hire_date )

ORDER BY

a.emp_no DESC;

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

SELECT

emp_no,

count( to_date ) AS t

FROM

salaries

GROUP BY

emp_no

HAVING

count( to_date ) > 15;

8 找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

SELECT

salary

FROM

salaries

WHERE

to_date = '9999-01-01'

GROUP BY

salary

ORDER BY

salary DESC;

9 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'

SELECT

d.dept_no,

d.emp_no,

s.salary

FROM

dept_manager AS d

INNER JOIN salaries AS s ON d.emp_no = s.emp_no

AND d.to_date = '9999-01-01'

AND s.to_date = '9999-01-01'

ORDER BY

d.emp_no;

10 获取所有非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`));

解：

SELECT SELECT

a.emp_no

FROM

employees a

LEFT JOIN dept_manager b ON a.emp_no = b.emp_no

WHERE

b.emp_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`));

解：

SELECT

a.emp_no,

b.emp_no AS manager_no

FROM

dept_emp a

LEFT JOIN dept_manager b ON a.dept_no = b.dept_no

WHERE

b.to_date = '9999-01-01'

AND a.emp_no <> b.emp_no;

12 获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

解：

SELECT

d.dept_no,

d.emp_no,

MAX( s.salary )

FROM

dept_emp d

LEFT JOIN salaries s ON d.emp_no = s.emp_no

WHERE

d.to_date = '9999-01-01'

AND s.to_date = '9999-01-01'

GROUP BY

d.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);

解：

SELECT

title,

count( title )

FROM

titles

GROUP BY

title

HAVING

count( title ) >= 2;

14 从titles表获取按照title进行分组，每组emp_no个数大于等于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);

解：

SELECT

title,

count( distinct emp_no ) as t

FROM

titles

GROUP BY

title

HAVING

t > 1;

15 查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

解：

SELECT

FROM

employees

WHERE

emp_no % 2 = 1

AND last_name <> 'Mary'

ORDER BY

hire_date DESC;

#法2

SELECT

FROM

employees

WHERE

MOD ( emp_no, 2 ) = 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);

解：

SELECT

a.title,

avg( b.salary ) AS `avg`

FROM

titles a

LEFT JOIN salaries b ON a.emp_no = b.emp_no

WHERE

b.to_date = '9999-01-01'

AND a.to_date = '9999-01-01'

GROUP BY

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

解：

SELECT

emp_no,

salary

FROM

salaries

WHERE

to_date = '9999-01-01'

ORDER BY

salary DESC

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

解：

SELECT

e.emp_no,

max( s.salary ) AS salary_2,

e.last_name,

e.first_name

FROM

employees AS e

INNER JOIN salaries AS s ON e.emp_no = s.emp_no

WHERE

s.to_date = '9999-01-01'

AND s.salary <> ( SELECT max( salary ) FROM salaries WHERE to_date = '9999-01-01' );

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

解：

SELECT

a.last_name,

a.first_name,

d.dept_name

FROM

(

SELECT

e.last_name,

e.first_name,

e.emp_no,

de.dept_no

FROM

employees AS e

LEFT JOIN dept_emp AS de ON e.emp_no = de.emp_no

) AS a

LEFT JOIN departments AS d ON a.dept_no = d.dept_no;

#法2

SELECT

e.last_name,

e.first_name,

d.dept_name

FROM

employees AS e

LEFT JOIN dept_emp AS de ON e.emp_no = de.emp_no

LEFT JOIN departments AS d ON de.dept_no = d.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`));

解：

SELECT

(

( SELECT salary AS end_salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1 ) - ( SELECT salary AS start_salary FROM salaries WHERE emp_no = 10001 ORDER BY from_date ASC LIMIT 1 )

) AS growth;

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

解：

SELECT

e.emp_no,

( s1.salary - s2.salary ) AS growth

FROM

employees AS e

INNER JOIN salaries AS s1 ON e.emp_no = s1.emp_no

AND s1.to_date = '9999-01-01'

INNER JOIN salaries AS s2 ON e.emp_no = s2.emp_no

AND s2.from_date = e.hire_date

ORDER BY

growth ASC;

22 统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及次数sum

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

解：

SELECT

d.dept_no,

d.dept_name,

count( s.salary ) AS `sum`

FROM

salaries AS s

INNER JOIN dept_emp AS dp ON s.emp_no = dp.emp_no

INNER JOIN departments AS d ON d.dept_no = dp.dept_no

GROUP BY

d.dept_no;

23 对所有员工的当前(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`));

解1：

SELECT

s1.emp_no,

s1.salary,

COUNT( DISTINCT s2.salary ) AS rank1

FROM

salaries AS s1,

salaries AS s2

WHERE

s1.to_date = '9999-01-01'

AND s2.to_date = '9999-01-01'

AND s1.salary <= s2.salary

GROUP BY

s1.emp_no

ORDER BY

s1.salary DESC,

s1.emp_no ASC;

解2：

SELECT

emp_no,

salary,

DENSE_RANK() OVER(ORDER BY salary DESC) AS rank_1

FROM

salaries

WHERE

to_date = '9999-01-01'

GROUP BY

emp_no

ORDER BY

salary DESC,

emp_no ASC;

24 获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'

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

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

解：

SELECT

d_e.dept_no,

e.emp_no,

s.salary

FROM

dept_emp AS d_e

INNER JOIN employees AS e ON d_e.emp_no = e.emp_no

LEFT JOIN salaries AS s ON e.emp_no = s.emp_no

WHERE

e.emp_no NOT IN ( SELECT emp_no FROM dept_manager )

AND s.to_date = '9999-01-01'

25 获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',

结果第一列给出员工的emp_no，

第二列给出其manager的manager_no，

第三列给出该员工当前的薪水emp_salary,

第四列给该员工对应的manager当前的薪水manager_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 `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`));

解：

SELECT

emp_no,

manager_no,

emp_salary,

manager_salary

FROM

(

SELECT

de.emp_no AS emp_no,

s.salary AS emp_salary,

de.dept_no AS bumen

FROM

dept_emp AS de

INNER JOIN salaries AS s ON de.emp_no = s.emp_no

WHERE

de.to_date = '9999-01-01'

AND s.to_date = '9999-01-01'

) AS yg_xz

INNER JOIN (

SELECT

dm.emp_no AS manager_no,

s.salary AS manager_salary,

dm.dept_no AS bumen

FROM

dept_manager AS dm

INNER JOIN salaries AS s ON dm.emp_no = s.emp_no

WHERE

dm.to_date = '9999-01-01'

AND s.to_date = '9999-01-01'

) AS jl_xz ON yg_xz.bumen = jl_xz.bumen

WHERE

yg_xz.emp_salary > jl_xz.manager_salary;

26 汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

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

解：

SELECT

d.dept_no,

d.dept_name,

t.title,

count( de.emp_no ) AS `count`

FROM

departments AS d

LEFT JOIN dept_emp AS de ON d.dept_no = de.dept_no

AND de.to_date = '9999-01-01'

LEFT JOIN titles AS t ON t.emp_no = de.emp_no

AND t.to_date = '9999-01-01'

GROUP BY

d.dept_no,

t.title;

27 给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)

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

解：

SELECT

s1.emp_no,

s1.from_date,

s1.salary - s2.salary AS salary_growth

FROM

salaries s1

INNER JOIN salaries s2 ON date_format( s1.to_date, '%Y' ) - date_format( s2.to_date, '%Y' ) = 1

AND s1.emp_no = s2.emp_no

ORDER BY

salary_growth DESC;

28 查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

CREATE TABLE IF NOT EXISTS film (

film_id smallint(5) NOT NULL DEFAULT '0',

title varchar(255) NOT NULL,

description text,

PRIMARY KEY (film_id));

CREATE TABLE category (

category_id tinyint(3) NOT NULL ,

name varchar(25) NOT NULL, `last_update` timestamp,

PRIMARY KEY ( category_id ));

CREATE TABLE film_category (

film_id smallint(5) NOT NULL,

category_id tinyint(3) NOT NULL, `last_update` timestamp);

解：

SELECT

c.NAME,

count( fc.film_id )

FROM

film_category AS fc

INNER JOIN category AS c ON fc.category_id = c.category_id

INNER JOIN film AS f ON fc.film_id = f.film_id

INNER JOIN ( SELECT category_id, count( film_id ) AS cf FROM film_category GROUP BY category_id HAVING count( film_id ) >= 5 ) AS tmp1 ON tmp1.category_id = c.category_id

WHERE

f.description LIKE '%robot%'

GROUP BY

c.category_id

28 使用join查询方式找出没有分类的电影id以及名称

SELECT

f.film_id,

f.title

FROM

film as f

left join

film_category AS fc

ON f.film_id=fc.film_id

where fc.category_id is null;

29 使用子查询的方式找出属于Action分类的所有电影对应的title,description

select

title,

description

from film as f

where film_id in

(

select

f.film_id

from film as f

left join

film_category as fc

f.film_id=fc.film_id

left join

category as c

fc.category_id=c.category_id

where c.name='Action'

)

30 获取select * from employees对应的执行计划

explain select * from employees；

林大路

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql牛客网_牛客网数据库sql实战-基于MYSQL实现（一）

1 找最晚入职员工的所有信息SELECT*FROMemployeesWHEREhire_date = ( SELECT max( hire_date ) FROM employees );2 查找入职员工时间排名倒数第三的员工所有信息SELECT*FROMemployeesWHEREhire_date = ( SELECT hire_date FROM employees ORDER BY h...
复制链接

扫一扫