牛客网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);