SQL牛客40题总结

最新推荐文章于 2022-04-17 18:15:12 发布

LKlk7>|

最新推荐文章于 2022-04-17 18:15:12 发布

阅读量313

点赞数

分类专栏：数据库学习笔记文章标签： sql 数据库刷题

本文链接：https://blog.csdn.net/woshizhu67/article/details/102336335

版权

学习笔记同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

数据库

1 篇文章 0 订阅

订阅专栏

SQL牛客题目总结

1

题目

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

解答

explain select * from employees

解释

在SQLite数据库中，可以用 "EXPLAIN" 关键字或 "EXPLAIN QUERY PLAN" 短语，用于描述表的细节

2

题目

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
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 concat(last_name,' ',first_name) from employees order by Name;

答案

SELECT last_name||" "||first_name AS Name FROM employees

解释

Mysql可以使用concat但是，sqlite只能使用||进行连接

3

题目

创建一个actor表，包含如下列信息
actor_idsmallint(5) not null 主键id

first_namevarchar(45) not null 名字 

last_namevarchar(45) not null 姓氏

last_updatetime stampnot null 最后更新时间，默认是系统的当前时间

解答

create table actor
(
actor_id smallint(5) not null PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')) -- ,
-- PRIMARY KEY(actor_id)
)

注意

sql不区分大小写，但是不同的行之间要加逗号

4

题目

对于表actor批量插入如下数据
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

解答

insert into actor values(1,"PENELOPE","GUINESS","2006-02-15 12:34:33"),(2,"NICK","WAHLBERG","2006-02-15 12:34:33");

解释

insert into 表名 values(数据1),(数据2)

5

题目

存在如下的视图：


create view emp_v as select * from employees where emp_no >10005;

如何获取emp_v和employees有相同的数据？


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 * from employees where emp_no >10005

注意

你可以将视图看作是一些表的数据总结，或者摘抄，它常用于简化sql操作，最常见的是简化复杂的联结，

如

CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

这条语句创建一个名为 ProductCustomers 的视图，它联结三个表，返回已订购了任意产品的所有顾客的列表。

6

题目

将所有获取奖金的员工当前的薪水增加10%。


create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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`));

解答

update salaries set
salary = salary*1.1

注

显然sql对分行并不敏感，也直接直接的进行列乘法操作

7

题目

针对库中的所有表生成select count(*)对应的SQL语句

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 emp_bonus(

emp_no int not null,

recevied datetime not null,

btype smallint not null);

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`));输出格式:cnts

解答

select 'select count(*) from ' || name ||';' as cnts
from sqlite_master
where type='table';

注

1、在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，而且对于自己创建的表而言，字段 type 永远是 'table'，详情可参考：
http://blog.csdn.net/xingfeng0501/article/details/7804378
2、在 SQLite 中用 “||” 符号连接字符串

在SQL中concat和||都是python里dataframe的concat的意思

注2：

这里简单说下两个表的外连接和内连接，

通过比较符号进行连接的表都是内连接，特点是只返回满足条件的两个表都有的数据

外连接，分为左外连接和右外连接，特点是返回主场表的所有数据，对客场表没有的数据进行NULL填充

内连接

select * from Student s
inner join Course c on s.C_S_Id=c.C_Id

外连接

select * from Student s
left join Course c on s.C_S_Id=c.C_Id

8

题目

--将employees表中的所有员工的last_name和first_name通过(')连接起来。
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 last_name || "'" || first_name as name from employees

疑问

--为什么不可以使用view
creat view name from employees.last_name || "'" || employees.first_name;
select * from name;

9

题目

查找字符串'10,A,B' 中逗号','出现的次数cnt。

解答

select (length('10,A,B')-length(replace('10,A,B',',',''))) as cnt

注：sql中没有统计字符串的函数，只能通过替换来求两个字符串的差值来统计某个字符串的出现的个数

10

题目

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

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 first_name FROM employees
order by substr(first_name,length(first_name)-1)

注

substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

类似于python中的str[X:Y];

order by 是正序排序如果要求倒序我们应加入desc（descending order）语句

11

题目

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
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`));

解答

SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp GROUP BY dept_no

本题要用到SQLite的聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。

12

题目

分页查询employees表，每5行一页，返回第2页的数据

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 * FROM employees LIMIT 5,5

注：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。

13

题目

获取有奖金的员工相关信息。

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 `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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'

解答

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, 
(CASE b.btype 
 WHEN 1 THEN s.salary * 0.1
 WHEN 2 THEN s.salary * 0.2
 ELSE s.salary * 0.3 END) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'

SQLite 中 CASE 表达式的用法，就类似于python中if的用法，可以加入else使用，也可以不加，但是值得注意的是在case结束时要加入end进行标注。即当 btype = 1 时，得到 salary * 0.1；当 btype = 2 时，得到 salary * 0.2；其他情况得到 salary * 0.3。

注：在这个题目中我们使用了多个inner join，说明inner join 是可以多个连续使用的，case要配合括号使用

14

题目

按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。
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.salary, 
(SELECT SUM(s2.salary) FROM salaries AS s2 
 WHERE s2.emp_no <= s1.emp_no AND s2.to_date = '9999-01-01') AS running_total 
FROM salaries AS s1 WHERE s1.to_date = '9999-01-01' ORDER BY s1.emp_no

分析

1、输出的第三个字段，是由一个 SELECT 子查询构成。将子查询内复用的 salaries 表记为 s2，主查询的 salaries 表记为 s1，当主查询的 s1.emp_no 确定时，对子查询中不大于 s1.emp_no 的 s2.emp_no 所对应的薪水求和

2、注意是对员工当前的薪水求和，所以在主查询和子查询内都要加限定条件 to_date = ‘9999-01-01’

15

题目

film表

字段	说明
film_id	电影id
title	电影名称
description	电影描述信息

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

category表

字段	说明
category_id	电影分类id
name	电影分类名称
last_update	电影分类最后更新时间

CREATE TABLE category (

category_id tinyint(3) NOT NULL ,

name varchar(25) NOT NULL, last_update timestamp,

PRIMARY KEY ( category_id ));

film_category表

字段	说明
film_id	电影id
category_id	电影分类id
last_update	电影id和分类id对应关系的最后更新时间

CREATE TABLE film_category (

film_id smallint(5) NOT NULL,

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

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

解答

子查询解法：

`select f.title,f.description from film as f``where f.film_id in (select fc.film_id from film_category as fc``               ``where fc.category_id in (select c.category_id from category as c``                                        ``where c.name = ``'Action'``));`

非子查询解法：

`select f.title,f.description``from film as f inner join film_category as fc on f.film_id = fc.film_id``               ``inner join category as c on c.category_id = fc.category_id``where c.name = ``'Action'``;`

我的代码

select f.film_id,f.description from  film as f inner join
(select * as f_CC from film_category as f_c inner join category as c 
on f_c.category_id = c.category_id) on f.film_id = f_CC.film_id 
where f_CC.name = 'Action'

16

题目

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

解答

UPDATE titles_test SET emp_no = REPLACE(emp_no,10001,10005) WHERE id = 5

解析

方法一：

全字段更新替换。由于 REPLACE 的新记录中 id=5，与表中的主键 id=5 冲突，故会替换掉表中 id=5 的记录，否则会插入一条新记录（例如新插入的记录 id = 10）。并且要将所有字段的值写出，否则将置为空。

`REPLACE INTO titles_test VALUES (``5``, ``10005``, ``'Senior Engineer'``, ``'1986-06-26'``, ``'9999-01-01'``)`

方法二：

运用REPLACE(X,Y,Z)函数。其中X是要处理的字符串，Y是X中将要被替换的字符串，Z是用来替换Y的字符串，最终返回替换后的字符串。以下语句用 UPDATE和REPLACE 配合完成，用REPLACE函数替换后的新值复制给 id=5 的 emp_no。REPLACE的参数为整型时也可通过。

`UPDATE titles_test SET emp_no = REPLACE(emp_no,``10001``,``10005``) WHERE id = ``5`

方法二才是比较合适的操作，首先这是一个更新表中某行的内容的操作，所以我们应该使用update语句对表来进行更新

17

题目

将titles_test表名修改为titles_2017

解答

alter table titles_test rename to titles_2017

解析

更新表的定义的更新都可以使用alter

18

题目

--删除emp_no重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

解答

DELETE FROM titles_test WHERE id NOT IN 
(SELECT MIN(id) FROM titles_test GROUP BY emp_no)

用 GROUP BY 和 MIN() 选出每个 emp_no 分组中最小的 id，然后用 DELETE FROM … WHERE … NOT IN … 语句删除 “非每个分组最小id对应的所有记录”

其中DELETE 语句：保留数据表，仅删除全部数据行

 DELETE FROM < 表名 >;

19

题目

将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

解答

update  titles_test set to_date=null,from_date='2001-01-01' where
to_date='9999-01-01';

但是有一个疑问

若改为

update  table titles_test set to_date=null,from_date='2001-01-01' where
to_date='9999-01-01';

则无法通过

20

题目

在audit表上创建外键约束，其emp_no对应employees_test表的主键id。

解答

ALTER TABLE tablename ADD FOREIGN KEY…REFERENCES…

注：外键是表中的一列，其值必须列在另一表的主键中

主要是为了保证数据的统一性，举例来说：1班成绩单里出现的同学必须是在1班的同学名单里

注注：sqlite没有这样的操作，只能删除表，重新做一个==

21

题目

从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(*) from titles group by title

22

题目


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

解答

select title,count(DISTINCT emp_no) as c from titles group by title having c>=2

疑问：count内是否也可以使用max，min，=，>等函数和比较符？或者说count里面可以加入where的内容吗？

23

题目

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

解答

select * from employees where emp_no%2=1 and last_name!='Mary' 
order by  hire_date desc

注：desc加在命令的最后

24

题目

--统计出当前各个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 t.title,avg(s.salary) from salaries as s  join titles  as t 
on s.emp_no = t.emp_no and s.to_date='9999-01-01' and t.to_date = '9999-01-01'
group by t.title

注：注意一点在数据库两个表可能都存储了同一员工不同时间的记录，所以不能在最后再使用having进行限制，而是要在连接条件中使用on

25

题目

获取当前（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,1

注：where要写在order by前面

26

题目

--查找当前薪水(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, 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 NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')

注sql提取数据都是以行为单位的，一旦对某一列做出了限制后，也就对行做出了一定的限制，比如本题中salary使用max后选出的emp_no等列数据也只能是max的一行

27

题目

对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作

actor_id	first_name	last_name	last_update
‘3’	‘ED’	‘CHASE’	‘2006-02-15 12:34:33’

解答

insert or ignore into actor values ('3','ED','CHASE','2006-02-15 12:34:33')

注：在 SQLite 中，用 INSERT OR IGNORE 来插入记录，或忽略插入与表内UNIQUE字段都相同的记录；用 INSERT OR REPLACE 来插入记录，或更新替代与表内UNIQUE字段都相同的记录

28

题目

actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：

firtst_name	varchar(45)	not null
last_name	varchar(45)	not null

解答

CREATE TABLE actor_name
(
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL
);
INSERT INTO actor_name SELECT first_name, last_name FROM actor;

其他方法

create table actor_name as
select first_name,last_name from actor;

注：as的写法一般是 A as B ,解释为将A记做B

29

题目

--针对如下表actor结构创建索引：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
--对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

解答

CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

注：本题要用两条语句完成，先用 CREATE UNIQUE INDEX … ON … 对first_name创建唯一索引值，再用 CREATE INDEX … ON … 对last_name创建普通索引值

30

题目

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

解答

create view actor_name_view as 
select first_name as first_name_v,last_name as last_name_v from actor

31

题目

--查找薪水涨幅超过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`));

解答

SELECT emp_no, COUNT(emp_no) AS t FROM salaries 
GROUP BY emp_no HAVING t > 15

注：select后的属性是可以直接用到where或者having里的

32

题目

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

解答

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

33

题目

获取所有部门当前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`));

解答

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=s.to_date where d.to_date='9999-01-01'

34

题目

--获取所有非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 emp_no from employees where emp_no not in (select emp_no from dept_manager)

35

题目

--获取所有员工当前的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 de.emp_no, dm.emp_no AS manager_no 
FROM dept_emp AS de INNER JOIN dept_manager AS dm
ON de.dept_no = dm.dept_no 
WHERE dm.to_date = '9999-01-01' AND de.to_date = '9999-01-01' AND de.emp_no != dm.emp_no

36

题目

--获取所有部门中当前员工薪水最高的相关信息，给出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`));

解答

select d.dept_no,d.emp_no,max(s.salary) from dept_emp as d inner join salaries  as s
on d.emp_no = s.emp_no and d.to_date = s.to_date group by d.dept_no

37

题目

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

解答

select title,count(*) as t from titles group by title having t>1

38

题目

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的title进行忽略。从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的title进行忽略。

解答

select  title ,count(distinct emp_no) as t from titles group by title having t>1

39

题目

查找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

40

题目

--统计出当前各个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 t.title,avg(s.salary) as avg
from salaries as s inner join titles as t 
on s.emp_no=t.emp_no and s.to_date = t.to_date
group by t.title having s.to_date= '9999-01-01'

LKlk7>|

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL牛客40题总结

SQL牛客题目总结1题目获取select * from employees对应的执行计划解答explain select * from employees解释在SQLite数据库中，可以用 "EXPLAIN" 关键字或 "EXPLAIN QUERY PLAN" 短语，用于描述表的细节2题目将employees表的所有员工的last_name和first_name拼接起来作...
复制链接

扫一扫

专栏目录