SQL 牛客网刷题02——中等

目录

1、 查找当前薪水详情以及部门编号dept_no

2、查找已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工

 3、获取员工和对应的经理，如果员工本身是经理的话则不显示

4、各个title类型对应的员工薪水对应的平均工资avg

5、所有员工的last_name和first_name以及对应的dept_name，包括未分配员工

6、计各个部门的工资记录数

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

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

9、创建一个actor表

10、批量插入数据，不使用replace操作

11、对first_name创建唯一索引，对last_name创建普通索引

12、针对actor表创建视图actor_name_view

13、针对salaries表emp_no字段创建强制索引idx_emp_no

14、在last_update后面新增加一列名字为create_date

15、构造一个触发器audit_log

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

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

18、所有员工的last_name和first_name通过(')连接起来

19、查找字符串中逗号出现的次数

 20、获取employees中的first_name，按最后两个字母升序

21、按照dept_no进行汇总

22、平均工资

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

24、使用含有关键字exists查找未分配具体部门的员工的信息

25、刷题通过的题目排名

 26、考试分数(二)

27、牛客的课程订单分析(二)

28、牛客的课程订单分析(三)

29、牛客的课程订单分析(六)

30、实习广场投递简历分析(二)

31、 最差是第几名(一)

32、获得积分最多的人(一)

33、商品交易(网易校招笔试真题

34、今天的刷题量(一)

35、 创建一个actor_name表

---

1、 查找当前薪水详情以及部门编号dept_no

 各个部门当前领导的薪水详情以及其对应部门编号dept_no，salaries.emp_no升序：

# 方法一
select s., d.dept_no from dept_manager d, salaries s
where d.emp_no=s.emp_no
and d.to_date='9999-01-01' and s.to_date='9999-01-01'
order by emp_no;

# 方法二：内联结
select s.*, d.dept_no from salaries as s
inner join dept_manager as d on s.emp_no = d.emp_no
order by s.emp_no;

2、查找已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工

# 部分员工还没有分配到部门，因此不能使用内连接，使用左连接确保员工没有缺失
select last_name, first_name, dept_no
from employees em
left join dept_emp dm
on em.emp_no = dm.emp_no

 3、获取员工和对应的经理，如果员工本身是经理的话则不显示

select d.emp_no, de.emp_no as manager from dept_emp d
inner join dept_manager de 
on d.dept_no = de.dept_no 
where d.emp_no != de.emp_no;

4、各个title类型对应的员工薪水对应的平均工资avg

# 经过 group by 之后，select 中只能包含分组的字段以及聚合函数
select t.title, avg(s.salary) from titles t
inner join salaries s
on t.emp_no = s.emp_no
group by title
order by avg(s.salary);

5、所有员工的last_name和first_name以及对应的dept_name，包括未分配员工

题目有三个表：

【部门的信息】departments: dept_no, dept_name

【部门员工所属】dept_emp: emp_no, dept_no,...

【员工的信息】employees: emp_no, first_name, last_name,...

# 方法一：两次left join嵌套
SELECT last_name, first_name, dept_name
FROM employees AS e LEFT JOIN (SELECT emp_no, dept_name
                               FROM dept_emp AS de LEFT JOIN departments AS d
                               ON de.dept_no = d.dept_no)k
ON e.emp_no = k.emp_no;

# 方法二：两次LEFT JOIN连接
select e.last_name, e.first_name, dept_name from employees e
left join dept_emp de on e.emp_no = de.emp_no
left join departments d on de.dept_no = d.dept_no;

6、计各个部门的工资记录数

给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序

# 方法一：窗口函数count()over()
select distinct d.dept_no , d.dept_name,
count(s.salary)over(partition by d.dept_no order by d.dept_no)
from departments as d join dept_emp as de on d.dept_no = de.dept_no
                      join salaries as s  on s.emp_no = de.emp_no;

# 方法二：三表连结再分组使用聚合函数
# 当group by 后面跟上主键或者不为空唯一索引时，查询是有效的，因为此时的每一笔数据都具有唯一性。
select de.dept_no, de.dept_name,count(s.salary) from departments de
inner join dept_emp d on de.dept_no = d.dept_no
inner join salaries s on d.emp_no = s.emp_no
group by de.dept_no
order by de.dept_no;

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

# 方法一：内连接+not in
select film_id as '电影id',title as '名称'
from film
where film_id not in(select f.film_id
from film f inner join film_category fc on f.film_id=fc.film_id)；

# 方法二:左连接+is null
select f.film_id, f.title from film f 
left join film_category fc on f.film_id=fc.film_id
where fc.category_id is null;

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

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

9、创建一个actor表

create table actor
(actor_id smallint(5) not null COMMENT '主键id',
 first_name varchar(45) not null COMMENT '名字',
 last_name	varchar(45) not null COMMENT '姓氏',
 last_update date not null COMMENT '日期',
 PRIMARY KEY (actor_id)
);

10、批量插入数据，不使用replace操作

# mysql中常用的三种插入数据的语句:
# insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
# replace into表示插入替换数据，需求表中有PrimaryKey，
#             或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；

# insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");

11、对firs