SQL|牛客刷题笔记

最新推荐文章于 2022-05-07 20:15:39 发布

除了学习无所事事

最新推荐文章于 2022-05-07 20:15:39 发布

阅读量468

点赞数

分类专栏： sql 文章标签： sql 数据库数据分析

本文链接：https://blog.csdn.net/qq_41011449/article/details/105711466

版权

sql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

查找入职员工时间排名倒数第三的员工所有信息
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));
总结：严谨的话需考虑到可能同一天又多个人入职，所以用distinct 去重

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

200423-练习10题

1.查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号(200423-练习10题)
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));
在这里插入图片描述
总结：1.当前领导、当前薪水-两个都需限制；因为按年计算薪水，一个人可能存在多次加薪、领导可能在今年早些时候离职
2.当用join连接表时，结果先是salaries的信息，需将salaries作为主表放在前面

select s.*, d.dept_no
from salaries as s, dept_manager as d
where s.emp_no=d.emp_no
and d.to_date='9999-01-01'
and s.to_date='9999-01-01'

200424-练习10题

2.从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);
总结：根据emp_no进行计数，从而去除重复值

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

2.1查找当前薪水(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), e.last_name, e.first_name
from employees as e, salaries as s
where e.emp_no = s.emp_no
and s.to_date = '9999-01-01'
and s.salary <(select max(salary)
              from salaries  
               where to_date = '9999-01-01')

200426-练习10题

2.2查找员工编号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 from salaries
where emp_no = 10001
and to_date = '9999-01-01')-(select salary from salaries 
where emp_no = 10001
order by from_date limit 1)
) as growth

3.查找所有员工自入职以来的薪水涨幅情况，给出员工编号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));
总结：思路是将salaries进行连接，得到初始工资、现在工资；需通过employees筛选出现在的员工，不能只用salaries表

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 e.hire_date = s2.from_date
order by growth asc

4.对所有员工的当前(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));
总结：rank相当于一个变量，通过select 得到。不同rank排序差别
在这里插入图片描述

select emp_no, salary, dense_rank() over(order by salary desc) as rank
from salaries
where to_date = '9999-01-01'
order by salary desc, emp_no

5.汇总各个部门当前员工的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);
总结：除了按dept_no分组外，还需要按title分组，否则每个title单独分开
在这里插入图片描述

select d.dept_no, d.dept_name, t.title, count(t.title)
from departments as d, dept_emp as de, titles as t
where d.dept_no = de.dept_no
and de.emp_no = t.emp_no
and de.to_date = '9999-01-01'
and t.to_date = '9999-01-01'
group by d.dept_no, t.title

6.查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部
总结：该分类>=5部，与前面没有关系，不是包含robot的电影>=5部；
包含robot的描述用 like ‘%robot%’
首先将不同分类先统计数量，<5部的电影剔除，再统计含robot的中不同分类电影数量

select c.name, count(f.film_id)
from category as c, film as f, film_category as fc
where c.category_id = fc.category_id
and fc.film_id = f.film_id
and f.description like '%robot%'
and fc.category_id in 
(select fc.category_id 
from film_category as fc 
group by fc.category_id
having count(fc.film_id) >= 5)
group by fc.category_id
或者
select c.name, count(f.film_id)
from (select category_id
from film_category
 group by category_id
having count(film_id)>=5) as cc,
film as f, category as c, film_category as fc
where f.film_id = fc.film_id
and fc.category_id = c.category_id
and c.category_id = cc.category_id #通过where条件连接相当于inner join
and f.description like '%robot%'
group by c.category_id

6.1 使用join查询方式找出没有分类的电影id以及名称
总结：注意null的使用，是 is null 不是！=null

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.film_id is null

7.获取select * from employees对应的执行计划
总结：常用于描述表的细节，优化程序（不常用）
https://www.runoob.com/sqlite/sqlite-explain.html

explain select * from employees

200428(练习10题）

将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));
总结：SQLite中使用 || 进行连接，空格用 “ ”表示
SQL中使用concat，空格用’ ’ 表示

#SQLite
SELECT last_name||" "||first_name AS Name FROM employees
#SQL
select concat(last_name, '', first_name) as name from employees

9.创建一个actor表，包含如下列信息
在这里插入图片描述
总结：1.必须要在末尾加primary key的信息
2. last_update末尾加上 default 是为该字段设置默认值，且默认值为(datetime(‘now’,‘localtime’))，即获得系统时间

create table 'actor' ('actor_id' smallint(5) not null, 
                     '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))

10.对于表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’)))
在这里插入图片描述
总结：插入信息用 Insert Into 表名 values (数据1），（数据2）

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

11.对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作,内容如上题
总结：

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

在这里插入图片描述
总结：可以直接创建新表时加入字段；或者先创建新表，再插入内容

#sqlite3
create table 'actor_name' as 
select first_name,last_name from actor
#mysql
create table 'actor_name'
select first_name,last_name from actor
#或者
create table 'actor_name' ('first_name' varchar(45) not null,
                           'last_name' varchar(45) not null,
                           primary key('first_name')); #注意这里的 ；
insert into actor_name
select first_name, last_name from actor

13.针对如下表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
总结：1. 创建普通索引 create index indexname on mytable(username)
2. 创建唯一索引（两行不允许有相同的索引值）create unique index index_name on mytable(username)

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);不知道为什么末尾必须加分号

14. 针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
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 index idx_emp_no on salaries(emp_no);
总结：1. sqlite3 强制索引用 indexed by 索引名
2. mysql 强制索引用 force index (索引名）
3. 思路：先创建索引，再查询-本题默认已经设置了索引

#sqlite3
select * from salaries indexed by idx_emp_no where emp_no = 10005
#mysql
# create index idx_emp_no on salaries(emp_no);该题已经设置了索引
select * from salaries FORCE INDEX (idx_emp_no) where emp_no = 10005

15. 针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：
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’)))
总结：创建视图用（与12题创建table有点类似）

create view view_name as 
select column_name from table
where condition
# 创建表并引用其他表的字段
create table as 
select column_name from table

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

16.构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);
总结：构造触发器-https://www.runoob.com/sqlite/sqlite-trigger.html

create trigger 触发器名称 after/before event_name(即发生什么事触发，可以用insert delete update）
begin 
触发什么结果
end

create trigger audit_log after insert on employees_test
begin
insert into audit values(new.ID,new.name); #注意末尾的分号，old和new分别表示在insert之前和之后的数据
end

17.现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’0000 00:00:00’
存在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’)));
总结：添加新列

# 添加新列
alter table 表名 add '列名' 数据格式
# 修改表名-表名不需要加引号
alter table 表名 rename to 新表名

alter table actor 
add 'create_date' datetime not null default '0000-00-00 00:00:00'

17.2 audit表上创建外键约束，其emp_no对应employees_test表的主键id。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);
总结：增加外键约束 https://www.runoob.com/sql/sql-foreignkey.html。本题 sqlite 只支持先删除表，在创建新表和增加约束

alter table 表名
add foreign key (字段名）
references 关联表名（关联字段）


```sql
alter table audit 
add foreign key (emp_no)
references employees_test(id)
#sqlite3
drop table audit;
create table audit(
    emp_no int not null,
    create_date datetime not null,
    foreign key(emp_no) references employees_test(id))

200429(练习7题）

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

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’);
总结：首先筛选出同一个emp_no对应的最小id，其次，将剩余的删除

delete from titles_test
where id not in 
(select min(id) from titles_test
group by emp_no)

19.将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
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’);
总结：更新表中记录的基本语法，参考https://www.runoob.com/sql/sql-update.html

update 表名
set 列名1 = value1, 列名2 = value2
where 条件

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

20.将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’);
总结：替换字符串 replace (字段名，要被替换的值，替换后的值）

update titles_test
set emp_no = replace(emp_no,10001,10005)
where id = 5

22.将所有获取奖金的员工当前的薪水增加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));
总结：1. 只需要将表更新，不需要选出来形成新的表。
2. 首先筛选出获奖员工当前薪水对应的行，再更新薪水。

update salaries
set salary = salary*1.1
where to_date = '9999-01-01'
and emp_no in (select e.emp_no from emp_bonus as e)

200430(练习6题）

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

#sqlite3中获取所有表名 https://blog.csdn.net/xingfeng0501/article/details/7804378
SELECT name FROM sqlite_master 
WHERE type=’table’ 
ORDER BY name; 
#mysql中获取所有表名 https://blog.csdn.net/susan19890313/article/details/7575310
select table_name from information_schema.tables 
where table_schema='database_name' and table_type='base table';
#sql中获取所有表名
select * from sysobjects where xtype='U';
xtype='U':表示所有用户表，xtype='S':表示所有系统表。

select "select count(*) from "||name||";" as cnts
from sqlite_master 
WHERE type= 'table' 
#mysql 先将table name生成一个新表，在取其中的字段
select concat("select count(*) from "," ",table_name,";") as cnts
from (select table_name from information_schema.tables) as new;

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

#sqlit3 中用||表示连接
select last_name|| "'" ||first_name
from employees
#mysql sql中用concat函数表示连接
select concat(last_name,"'",first_name) from employees
#group_concat可以将分组后的一组连接起来，https://blog.csdn.net/Mary19920410/article/details/76545053

24.查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。
总结：用replace将逗号替换为空格后，通过字符串长度详见计算出现次数。若子串长度大于1，还需要除以字符串长度
https://www.nowcoder.com/questionTerminal/e3870bd5d6744109a902db43c105bd50?f=discussion

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

25.获取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));
总结：substr-用来截取数据库中某个字段的一部分

SUBSTR(str,pos,len): 从pos开始的位置，截取len个字符。pos为负数时，从后往前数，例如-2是从倒数第二个字符开始
MySQL: SUBSTR( ), SUBSTRING( )
SQL Server: SUBSTRING( )

select first_name 
from employees 
order by substr(first_name,-2)

26.按照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));
总结：group_concat (要链接的字段 separator ‘分隔符默认为，’）https://blog.csdn.net/u011341352/article/details/48262329

select dept_no, group_concat(emp_no) as employees 
from dept_emp
group by dept_no

27.查找排除当前最大、最小salary之后的员工的平均工资avg_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然后排除计算平均工资

select avg(salary) from salaries
where to_date = '9999-01-01'
and salary not in
(select max(salary) #不能同时选出max，min，因为salary只能对应一个值
from salaries where to_date = '9999-01-01')
and salary not in
(select min(salary)
from salaries where to_date = '9999-01-01')

200501（练习8题）

28.分页查询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));
总结：直接返回5-10行的数据，不需要分好页数

select * from employees
limit 5 offset 5

29.使用含有关键字exists查找未分配具体部门的员工的所有信息。
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));
总结：exists 的使用
在这里插入图片描述

select * from employees as e
where not exists #注意 not exists 前没有字段名称
(select d.emp_no 
 from dept_emp as d 
where d.emp_no = e.emp_no)
#或者用In
select * from employees as e
where d.emp_no in
(select d.emp_no from dept_emp as d

30.按照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));
在这里插入图片描述
总结：思路将两个salaries表连接，条件是其中一个表的emp_no<另外一个表的emp_no，然后分组对salaries求和

select a.emp_no, a.salary, sum(b.salary)
from salaries as a inner join salaries as b
on a.emp_no >= b.emp_no
where a.to_date = '9999-01-01'
and b.to_date = '9999-01-01'
group by a.emp_no
或者使用聚合函数作为窗口函数使用
select emp_no, salary, sum(salary) over (order by emp_no)
from salaries
where to_date = '9999-01-01'

31.（原61题）对于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));
总结：1. 首先通过where语句括号里的部分进行编号，再筛选奇数行
2. 编号的逻辑是根据e1中 first_name 在 e2中first_name之前的行数

select e1.first_name from employees as e1
where (select count(*) from employees as e2
where e1.first_name >=e2.first_name)%2 = 1

5.4 练习1、最后2题
5.5 练习（获取employees中的行数据，且这些行也存在于emp_v中）
5.7 练习3题 (查找入职员工时间排名倒数第三的员工所有信息、
获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received ，没有分配具体的员工不显示)
5.8 练习5题（查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no\查找排除当前最大、最小salary之后的员工的平均工资avg_salary）
5.9 练习5题（查找所有已经分配部门的员工的last_name和first_name以及dept_no\将employees表中的所有员工的last_name和first_name通过(’)连接起来）
5.10 练习4题（查找所有员工的last_name和first_name以及对应部门编号dept_no/如何获取emp_v和employees有相同的数据？
5.11 练习4题（查找所有员工入职时候的薪水情况/将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005）
5.13 练习3题（查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t/删除emp_no重复的记录，只保留最小的id对应的记录）、
5.14 洋葱学院笔试5题
试题：https://blog.csdn.net/qq_41011449/article/details/105152213
解答： https://blog.csdn.net/qq_41011449/article/details/106119404
5.15 练习5题（找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示/针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v)
5.16 练习5题（获取所有部门当前manager的当前薪水情况，/对于表actor批量插入如下数据）
5.22 练习5题 (获取所有非manager的员工emp_no/使用子查询的方式找出属于Action分类的所有电影对应的title,description)
5.23 练习3题（获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager/查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部)
5.24 练习3题（获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工薪水最高的相关信息/汇总各个部门当前员工的title类型的分配数目)
5.25 练习3题（从titles表获取按照title进行分组/获取所有非manager员工当前的薪水情况)
5.27 练习3题（从titles表获取按照title进行分组,注意对于重复的emp_no进行忽略/统计各个部门的工资记录数）
5.30 练习3题（查找employees表/查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth)
5.31 练习4题（统计出当前各个title类型对应的员工当薪水对应的平均工资/获取当前薪水第二多的员工的emp_no以及其对应的薪水salary）

第二轮刷题完成
6.1 SQL测试（给出每个员工每年薪水涨幅超过5000的员工编号emp_no）
6.2 SQL测试（对first_name创建唯一索引uniq_idx_firstname）
6.8 SQL测试（全部完成）

重点记忆：
1、查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工（join的使用）
2、查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)（join的使用）
3、38题针对actor表创建视图actor_name_view
4、39题针对salaries表emp_no字段创建索引idx_emp_no
5、40题在last_update后面新增加一列名字为create_date,
6、41题构造一个触发器audit_log
7、43 将所有to_date为9999-01-01的全部更新为NULL,
8、53 按照dept_no进行汇总

除了学习无所事事

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL|牛客刷题笔记

查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号(200423-练习10题)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 KE...
复制链接

扫一扫