sql场景（2）

SQL22 统计各个部门的工资记录数

有一个部门表departments简况如下:

有一个，部门员工关系表dept_emp简况如下:

有一个薪水表salaries简况如下:

请你统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序，以上例子输出如下:

解答：

select a.dept_no, a.dept_name, count(c.salary) as sum
from departments as a 
left join dept_emp b 
on a.dept_no = b.dept_no
left join salaries as c
on b.emp_no = c.emp_no
group by a.dept_no

必须用left join ,都向第一张表看齐！

SQL23 对所有员工的薪水按照salary降序进行1-N的排名

有一个薪水表salaries简况如下:

对所有员工的薪水按照salary降序进行1-N的排名，要求相同salary并列且按照emp_no升序排列：

解答：
此题用到了一个特殊的方法！需要记忆！！！

select emp_no,
salary,
DENSE_RANK() over(order by salary desc) as t_rank
from salaries

DENSE_RANK()是一个窗口函数，它为分区或结果集中的每一行分配排名，而排名值没有间隙。
行的等级从行前的不同等级值的数量增加1。

SQL24 获取所有非manager员工当前的薪水情况

有一个，部门员工关系表dept_emp简况如下:

有一个部门经理表dept_manager简况如下:

有一个薪水表salaries简况如下:

获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary，以上例子输出:

解答：
不能=null , 必须 is null

select de.dept_no,de.emp_no,s.salary
from dept_emp as de  inner join salaries as s on de.emp_no =s.emp_no
left join dept_manager as dm on dm.emp_no=de.emp_no
where dm.emp_no is null

SQL25 获取员工其当前的薪水比其manager当前薪水还高的相关信息 (难)

有一个，部门关系表dept_emp简况如下:

有一个部门经理表dept_manager简况如下:

有一个薪水表salaries简况如下:

获取员工其当前的薪水比其manager当前薪水还高的相关信息，
第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
以上例子输出如下:

解答：

select  s.emp_no as emp_no, 
            dm.emp_no as manager_no, 
            s.salary as emp_salary, 
            ss.salary as manager_salary
from salaries as s

join dept_emp as de
on s.emp_no=de.emp_no

join dept_manager as dm
on de.dept_no=dm.dept_no

join salaries as ss
on ss.emp_no=dm.emp_no

where     
      s.to_date='9999-01-01' 
      and ss.to_date='9999-01-01'
      and s.salary>ss.salary;

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

解答：

select title,description from film
where film_id in
(select film_id from film_category 
where category_id in 
(select category_id from category 
where name like 'action'))

SQL38 针对actor表创建视图actor_name_view

SQL39 针对上面的salaries表emp_no字段创建索引idx_emp_no

针对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

存在actor表，包含如下列信息：
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 datetime NOT NULL);
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’2020-10-01 00:00:00’

解答：

alter table actor add  column create_date  datetime NOT NULL 
default '2020-10-01 00:00:00' after last_update

SQL42 删除emp_no重复的记录，只保留最小的id对应的记录。

SQL44 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

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

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

alter table audit
add foreign key(emp_no)
references employees_test(id)

SQL50 将employees表中的所有员工的last_name和first_name通过引号连接起来。

select concat (last_name,"’",first_name) as name
from employees

SQL53 按照dept_no进行汇总

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

SQL54 查找排除当前最大、最小salary之后的员工的平均工资avg_salary

select avg(salary) as avg_salary
from salaries
where salary>(select min(salary) from salaries)
and salary<(SELECT max(salary) from salaries)
and to_date=‘9999-01-01’

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

获取有奖金的员工相关信息。
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,
received 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,
    eb.btype,
    s.salary,
    (case when eb.btype=1 then s.salary*0.1
    when eb.btype=2 then s.salary*0.2
    else s.salary*0.3
    end
    )as bonus
from employees e join emp_bonus eb on e.emp_no=eb.emp_no
          join salaries s on eb.emp_no=s.emp_no
where s.to_date="9999-01-01"

case语法
(case
when…then…
when…then…
…
else…
end) as …

SQL60 统计salary的累计和running_total

实时累加和：
sum（salary） over(order by emp_no) as running_total