牛客网sql练习
SQL15 查找employees表emp_no与last_name的员工信息
SELECT *
from employees
where emp_no%2 !=0 and last_name !='Mary'
order BY hire_date DESC
SQL16 统计出当前各个title类型对应的员工当前薪水对应的平均工资
请你统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg,并且以avg升序排序,以上例子输出如下:
SELECT distinct title,avg(salary)
from titles JOIN salaries on titles.emp_no=salaries.emp_no
GROUP by title ORDER BY AVG(salary) ASC
SQL17 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary.
SELECT emp_no,salary
FROM salaries
where salary = (SELECT DISTINCT salary from salaries ORDER by salary desc LIMIT 1,1)
order by emp_no ASC
SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
mysql运行错误,Sqlite运行正确
select
e.emp_no,
max(salary),
e.last_name,
e.first_name
from employees as e
join salaries as s
on e.emp_no = s.emp_no
where to_date = '9999-01-01'
and
salary != (
select distinct max(salary) from salaries as s where s.to_date = '9999-01-01'
)
SQL19 查找所有员工的last_name和first_name以及对应的dept_name
select last_name,first_name,dept_name
from employees as e left join dept_emp as p on e.emp_no=p.emp_no left join departments
as d on d.dept_no=p.dept_no
SQL21 查找在职员工自入职以来的薪水涨幅情况
select
s1.emp_no,
s2.salary-s1.salary as growth
from
(select
e.emp_no,
s.salary
from
employees e
join salaries s
on e.emp_no=s.emp_no
where e.hire_date=s.from_date) as s1 --入职薪水
join
(select
e.emp_no,
s.salary
from
employees e
join salaries s
on e.emp_no = s.emp_no
where s.to_date = '9999-01-01') as s2 ---现在薪水
on s1.emp_no = s2.emp_no
order by growth
SQL22 统计各个部门的工资记录数
select d.dept_no,d.dept_name,count(s.salary)
from (salaries as s join dept_emp as de
on s.emp_no= de.emp_no)
join departments as d on d.dept_no=de.dept_no
group by d.dept_no,d.dept_name
order BY dept_no
SQL23 对所有员工的薪水按照salary降序进行1-N的排名
# 下面介绍三种用于进行排序的专用窗口函数:
# 1、RANK()
# 在计算排序时,若存在相同位次,会跳过之后的位次。
# 例如,有3条排在第1位时,排序为:1,1,1,4······
# 2、DENSE_RANK()
# 这就是题目中所用到的函数,在计算排序时,若存在相同位次,不会跳过之后的位次。
# 例如,有3条排在第1位时,排序为:1,1,1,2······
# 3、ROW_NUMBER()
# 这个函数赋予唯一的连续位次。
# 例如,有3条排在第1位时,排序为:1,2,3,4······
# 窗口函数用法:
# <窗口函数> OVER ( [PARTITION BY <列清单> ]
# ORDER BY <排序用列清单> )
# *其中[ ]中的内容可以忽略
SELECT emp_no,salary,
DENSE_RANK() over (order by salary desc) as 'rank'
from salaries
order by salary desc ,emp_no asc
SQL24 获取所有非manager员工当前的薪水情况
select de.dept_no,e.emp_no,s.salary
from employees as e join salaries as s on e.emp_no = s.emp_no
join dept_emp as de on de.emp_no = e.emp_no
where e.emp_no not in (select emp_no from dept_manager)
SQL25 获取员工其当前的薪水比其manager当前薪水还高的相关信息
SELECT s1.emp_no,s2.emp_no as manager_no,s2.s1.salary as emp_salary, s2.salary as manager_salary
from (SELECT s.emp_no,s.salary,de.to_date,de.dept_no from dept_emp as de join salaries as s on de.emp_no=s.emp_no) as s1
join (SELECT s.emp_no,s.salary,de.to_date,de.dept_no from dept_emp as de join salaries as s on de.emp_no=s.emp_no
where s.emp_no in (select emp_no from dept_manager)) as s2 on s1.dept_no=s2.dept_no
where s1.emp_no!=s2.emp_no and s1.salary>s2.salary and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
SQL26 汇总各个部门当前员工的title类型的分配数目
select de.dept_no,d.dept_name,t.title,count(title)
from departments as d join dept_emp as de on
d.dept_no=de.dept_no
join titles as t on de.emp_no=t.emp_no
where de.to_date='9999-01-01'
group by de.dept_no,d.dept_name,t.title
order by de.dept_no,t.title
SQL29 使用join查询方式找出没有分类的电影id以及名称
select film_id,title
from film
where film_id not in (select film_id from film_category)
SQL30 使用子查询的方式找出属于Action分类的所有电影对应的title,description…
select title,description
from film as f join film_category as fc
on f.film_id=fc.film_id
where fc.category_id in
(select category_id from category where name='Action')
SQL32 将employees表的所有员工的last_name和first_name拼接起来作为Name
sqllite版:
select last_name||' '||first_name as Name
from employees
mysql版:
select concat(last_name,' ',first_name) as Name
from employees
SQL33 创建一个actor表,包含如下列信息
drop table if exists `actor` ;
CREATE TABLE `actor` (
`actor_id` smallint(5) NOT NULL,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` date NOT NULL,
PRIMARY KEY (`actor_id`));
SQL34 批量插入数据
INSERT INTO
actor
VALUES(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33');
SQL35 批量插入数据,不使用replace操作
#igonre忽略已存在的行数据,插入其他新数据
#mysql版:
insert ignore into actor values(3,'ED','CHASE','2006-02-15 12:34:33');
#sqlite 版 只是多了个 or
insert or ignore into actor values(3,'ED','CHASE','2006-02-15 12:34:33');
SQL36 创建一个actor_name表
请你创建一个actor_name表,并且将actor表中的所有first_name以及last_name导入该表.
create table if not exists actor_name
(
first_name varchar(45) not null,
last_name varchar(45) not null
)select first_name,last_name
from actor
SQL37 对first_name创建唯一索引uniq_idx_firstname原题
#mysql版
alter TABLE actor ADD UNIQUE uniq_idx_firstname(first_name);
alter TABLE actor ADD INDEX idx_lastname(last_name);
#sqlite版
create index idx_lastname on actor(last_name);
create unique index uniq_idx_firstname on actor(first_name);
SQL38 针对actor表创建视图actor_name_view
create view actor_name_view(first_name_v,last_name_v)
as select first_name,last_name from actor;
select * from actor_name_view;
SQL39 针对salaries表emp_no字段创建索引idx_emp_no,查询emp_no为10005,使用强制索引。
select *
from salaries
force INDEX(idx_emp_no)
where emp_no=10005
SQL40 现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL,默认值为’2020-10-01 00:00:00’
alter TABLE actor
add
create_date datetime
not null
default '2020-10-01 00:00:00'
AFTER last_update
SQL41 构造一个触发器audit_log,在向employees_test表中插入一条数据的时候,触发插入相关的数据(注意:不是全部)到audit中。
create trigger audit_log after
insert
on employees_test for each row
insert into audit values(new.id,new.name)
SQL42 删除emp_no重复的记录,只保留最小的id对应的记录。
MySQL中不允许在子查询的同时删除表数据(不能一边查一边把查的表删了)把得出的表重命名那就不是原表了。
DELETE FROM titles_test WHERE
emp_no IN
(select s.dis_emp_no from (SELECT emp_no as dis_emp_no FROM titles_test GROUP BY emp_no HAVING COUNT(emp_no) > 1)as s)
AND id NOT IN
(select t.minid from (SELECT MIN(id) as minid FROM tit1les_test GROUP BY emp_no HAVING COUNT(1) > 1)as t);
SQL43 将所有to_date为9999-01-01的全部更新为NULL
UPDATE titles_test SET to_date=null ,from_date='2001-01-01' where to_date='9999-01-01'
SQL44 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005
REPLACE into titles_test VALUES(5,10005,'Senior Engineer', '1986-06-26', '9999-01-01')
SQL45 将titles_test表名修改为titles_2017
alter TABLE titles_test RENAME TO titles_2017
alter TABLE titles_test RENAME TO titles_2017
SQL46 在audit表上创建外键约束,其emp_no对应employees_test表的主键id。
alter table audit
add constraint fk_emp_no_id foreign key(emp_no) references employees_test(id);
SQL48 将所有获取奖金的员工当前的薪水增加10%
update salaries as s join emp_bonus as e on s.emp_no = e.emp_no
set salary=salary*1.1 where e.btype!=0 and s.to_date='9999-01-01'
SQL50 将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持concat,请用||实现,mysql支持concat)
select CONCAT(last_name,"'",first_name)
from employees
SQL51 查找字符串中逗号出现的次数
select id,
LENGTH(STRING) - LENGTH(REPLACE(string,',','')) as cnt
from strings
SQL52 获取employees中的first_name
三种方法都可以用
LEFT(s,n)返回字符串 s 的前 n 个字符
RIGHT(s,n)返回字符串 s 的后 n 个字符
SELECT first_name
from employees
order by right(first_name,2)
SQL53 按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
该CONCAT()方法用于连接,而GROUP_CONCAT()用于连接单个字符串中来自组的字符串。
select dept_no,GROUP_CONCAT(emp_no) as employees
from dept_emp
group by dept_no
SQL54 平均工资
查找排除在职(to_date = ‘9999-01-01’ )员工的最大、最小salary之后,其他的在职员工的平均工资avg_salary
select avg(salary) as avg_salary
from salaries
where salary not in(select min(salary) from salaries where to_date='9999-01-01'
)
and
salary not in(select max(salary) from salaries where to_date='9999-01-01'
)
and to_date='9999-01-01'
SQL55 分页查询employees表,每5行一页,返回第2页的数据
select * from employees
limit 5,5
SQL57 使用含有关键字exists查找未分配具体部门的员工的所有信息。
select * from employees e
where not exists
(select emp_no from dept_emp d where d.emp_no = e.emp_no);