sql练习题归纳总结中（5）

21.查找所有员工自入职以来的薪水涨幅情况

题目描述：

查找所有员工自入职以来的薪水涨幅情况，给出员工编号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));

Cue：

①创建第一个表，存储现在的薪水和员工号//其实就是连接

②创建第二个表，存储入职的薪水和员工号

③将两个表连接

④计算涨薪

select shire.emp_no,(snow.salary-shire.salary) as growth//选择最初表中的员工号和现在薪水减去之前薪水
from (select s.emp_no,s.salary from  employees as e left join salaries as s on s.emp_no=e.emp_no WHERE s.to_date = '9999-01-01') as snow//e左连接s，取新的表中员工号，当前时间薪水建表snow
inner join (select s.emp_no,s.salary from employees as e left join salaries as s on s.emp_no=e.emp_no WHERE s.from_date =e.hire_date ) as shire//e左连接s，调用e中hire_date,取s员工号和入职时薪水建表shire

on snow.emp_no=shire.emp_no//用emp_no连接表
order by growth//用growth排序

22.统计各个部门对应员工涨幅的次数总和

题目描述：

统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum

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 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 s1.dept_no,dm.dept_name,count(s1.dept_no) as sum

from (select s.emp_no,d.dept_no from salaries as s left join dept_emp on s.emp_no=d.emp_no) as s1

inner join departments as dm

on s1.dept_no=dm.dept_no

group by dept_no

SELECT de.dept_no, dp.dept_name, COUNT(s.salary) AS sum 
FROM (dept_emp AS de INNER JOIN salaries AS s ON de.emp_no = s.emp_no) 
INNER JOIN departments AS dp ON de.dept_no = dp.dept_no 
GROUP BY de.dept_no

23.对所有员工的薪水按照salary进行按照1-N的排名

题目描述: 对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列 CREATE TABLEsalaries(

emp_noint(11) NOT NULL,

salaryint(11) NOT NULL,

from_datedate NOT NULL,

to_datedate NOT NULL,

PRIMARY KEY (emp_no,from_date));

 

//rank函数，

select emp_no,salary,rank() over(order by salary desc)

from salaries

where to_date='9999-01-01'

select s1.emp_no,s1.salary,count(distinct s2.salary)
from salaries s1,salaries s2
where s1.to_date='9999-01-01' and s2.to_date='9999-01-01' and s1.salary<= s2.salary//通过判断在表1中，有多少比当前大的salary来判断排名
group by s1.emp_no
order by s1.salary desc,s1.emp_no asc

 

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

题目描述：

获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'

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

cue：

①将dept_emp和salaries连接，salaeies中就有所有的员工名单，所以不用employees。

②过滤掉经理

select de.dept_no,s.emp_no,ssalary

from (salaries as s inner join employees as e on s.emp_no = e.emp_no and to_date= '9999-01-01')

inner join dept_emp as de

on de.emp_no=e.emp_no

where de.emp_no not in (select emp_no from dept_manager where to_date='9999-01-01')

 

25.获取员工其当前的薪水比其manager当前薪水还高的相关信息

题目描述：

获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',

结果第一列给出员工的emp_no，

第二列给出其manager的manager_no，

第三列给出该员工当前的薪水emp_salary,

第四列给该员工对应的manager当前的薪水manager_salary

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

Cue：

①提取de.emp_no,de.dept_no,s.salary获得一个员工薪水表

②提取dm.emp_no,dm.dept_no,s.salary获得一个经理薪水表

③合并INNER JOIN获得一个目标薪水表

④选出员工薪水大于经理的列

 

select es.emp_no as emp_no,ms.emp_no as manager_no,es.salary as emp_salary,ms.salary as manager_salary

from (select de.emp_no,de.dept_no,s.salary from dept_emp as de inner join salaries as s 
      on s.emp_no=de.emp_no and s.to_date='9999-01-01') as es

inner join (select dm.emp_no,dm.dept_no,s.salary from dept_manager as dm inner join salaries as s 
            on dm.emp_no=s.emp_no and s.to_date='9999-01-01') as ms

on es.dept_no =ms.dept_no

where   es.salary >ms.salary