1.查找最晚入职员工的所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为–,mysql为comment)
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
));
/*
select * from employees
order by hire_date desc
limit 1;
*/
/* 使用limit 与 offset关键字 */
/*
select * from employees
order by hire_date desc
limit 1 offset 0;
*/
/* 使用limit关键字 从第0条记录 向后读取一个,也就是第一条记录 */
/*
select * from employees
order by hire_date desc
limit 0,1;
*/
/* 使用子查询,最后一天的时间有多个员工信息 */
/*
select * from employees
where hire_date = (select max(hire_date) from employees);
*/
2.查找入职员工时间排名倒数第三的员工所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天
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
));
select * from employees order by hire_date desc limit 1 offset 2;
SELECT *
FROM employees
WHERE hire_date = (
SELECT DISTINCT hire_date
FROM employees
ORDER BY hire_date DESC -- 倒序
LIMIT 1 OFFSET 2 -- 去掉排名倒数第一第二的时间,取倒数第三
);
3.查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no
(注:请以salaries表为主表进行查询,输出结果以salaries.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
));
CREATE TABLE dept_manager
(
dept_no
char(4) NOT NULL, – ‘部门编号’
emp_no
int(11) NOT NULL, – ‘员工编号’
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
select s.*,d.dept_no from salaries as s
inner join dept_manager as d
on s.emp_no=d.emp_no
where s.to_date='9999-01-01' and d.to_date='9999-01-01';
4.查找所有已经分配部门的员工的last_name和first_name以及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 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
));
select e.last_name, e.first_name, d.dept_no from dept_emp as d
inner join employees as e
on d.emp_no=e.emp_no;
5.查找所有员工的last_name和first_name以及对应部门编号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 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
));
select e.last_name, e.first_name, d.dept_no from employees as e
left join dept_emp as d
on e.emp_no=d.emp_no;
6.查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_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
));
select e.emp_no, s.salary from employees as e
left join salaries as s
on e.emp_no=s.emp_no
and e.hire_date=s.from_date
order by e.emp_no desc;
7.查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
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 emp_no, count(emp_no) as t from salaries
group by emp_no
having count(emp_no) > 15;
8.找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
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 distinct salary from salaries
where to_date='9999-01-01'
order by salary desc;
9.获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况,给出dept_no, emp_no以及salary(请注意,同一个人可能有多条薪水情况记录)
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
));
select d.dept_no, s.emp_no, s.salary from dept_manager as d
inner join salaries as s
on d.emp_no=s.emp_no
where d.to_date='9999-01-01'
and s.to_date='9999-01-01';
10.获取所有非manager的员工emp_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
));
select emp_no from employees
where emp_no not in
(select emp_no from dept_manager);
11.获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager,如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的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 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
));
select dept_emp.emp_no, dept_manager.emp_no from dept_emp
left join dept_manager on dept_emp.dept_no=dept_manager.dept_no
where dept_emp.emp_no not in
(select distinct emp_no from dept_manager)
and dept_manager.to_date='9999-01-01';
12.获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息,给出dept_no, emp_no以及其对应的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 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 d.dept_no, d.emp_no, max(s.salary) from salaries as s
inner join dept_emp as d
on s.emp_no=d.emp_no
and d.to_date='9999-01-01'
and s.to_date='9999-01-01'
group by d.dept_no
order by d.dept_no;
13.从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
CREATE TABLE IF NOT EXISTS “titles” (
emp_no
int(11) NOT NULL,
title
varchar(50) NOT NULL,
from_date
date NOT NULL,
to_date
date DEFAULT NULL);
select title, count(title) as t from titles
group by title
having count(title) >= 2;
14.从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
注意对于重复的emp_no进行忽略(即emp_no重复的title不计算,title对应的数目t不增加)。
CREATE TABLE IF NOT EXISTS titles
(
emp_no
int(11) NOT NULL,
title
varchar(50) NOT NULL,
from_date
date NOT NULL,
to_date
date DEFAULT NULL);
select title, count(title) as t from titles
group by title
having count(title) >= 2;
15.查找employees表所有emp_no为奇数,且last_name不为Mary(注意大小写)的员工信息,并按照hire_date逆序排列(题目不能使用mod函数)
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
));
select * from employees
where emp_no%2=1
and last_name<>'Mary'
order by hire_date desc;
16.统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。
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
));
CREATE TABLE IF NOT EXISTS “titles” (
emp_no
int(11) NOT NULL,
title
varchar(50) NOT NULL,
from_date
date NOT NULL,
to_date
date DEFAULT NULL);
select t.title, avg(s.salary)
from salaries as s
inner join titles as t
on s.emp_no=t.emp_no
where t.to_date='9999-01-01'
and s.to_date='9999-01-01'
group by title;
17.获取当前(to_date=‘9999-01-01’)薪水第二多的员工的emp_no以及其对应的薪水salary
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 emp_no, salary from salaries
order by salary desc
limit 1
offset 1;
18.查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,你可以不使用order by完成吗
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
));
select s.emp_no, s.salary, e.last_name, e.first_name
from salaries as s
inner join employees as e
on s.emp_no=e.emp_no
where s.salary=
(
select max(salary) from salaries where salary <
(
select max(salary) from salaries
where to_date='9999-01-01'
)
and to_date='9999-01-01'
)
and s.to_date='9999-01-01'
19.查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
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 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
));
select e.last_name, e.first_name, d.dept_name from employees as e
left join
(
select dept_emp.*, departments.* from dept_emp
inner join departments
on dept_emp.dept_no=departments.dept_no
) as d
on e.emp_no=d.emp_no;
20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪,没有降薪)
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
(
select max(salary) from salaries
where emp_no=10001
)
-
(
select min(salary) from salaries
where emp_no=10001
)
as growth;
21.查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
(注:可能有employees表和salaries表里存在记录的员工,有对应的员工编号和涨薪记录,但是已经离职了,离职的员工salaries表的最新的to_date!=‘9999-01-01’,这样的数据不显示在查找结果里面)
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
));
# 查询当前薪资,和入职时候的薪资,相减即可
select s1.emp_no,(s1.salary-e1.salary) as growth
from
(select emp_no,salary
from salaries
where to_date='9999-01-01') as s1
inner join
(select s.emp_no,s.salary
from salaries as s
inner join employees as e
on s.emp_no = e.emp_no and s.from_date=e.hire_date) as e1
on s1.emp_no=e1.emp_no
order by growth;
22.统计各个部门的工资记录数,给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录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
));
# 先将dept_emp和departments连接,再和salaries连接,dept_no分组
select d.dept_no, d.dept_name, count(*)
from
(
select dept_emp.*, departments.*
from dept_emp
inner join departments
on dept_emp.dept_no=departments.dept_no
) as d
inner join salaries as s
on s.emp_no=d.emp_no
group by d.dept_no;
23.对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名,相同salary并列且按照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
));
select emp_no, salary, dense_rank() over (order by salary desc) as rank
from salaries
where to_date='9999-01-01'
order by rank, emp_no;
# 窗口函数的用法
下面介绍三种用于进行排序的专用窗口函数:
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······
24.获取所有非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
));
select de.dept_no, de.emp_no, s.salary
from dept_emp de inner join employees e on de.emp_no = e.emp_no
inner join salaries s on de.emp_no = s.emp_no
where s.to_date='9999-01-01'
and de.emp_no not in (select emp_no from dept_manager);
25.获取员工其当前的薪水比其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
));
select t1.emp_no,manager_no,emp_salary,manager_salary
from(
select dept_no,d1.emp_no,salary as emp_salary
from dept_emp as d1 inner join salaries as s on d1.emp_no=s.emp_no
where d1.to_date='9999-01-01' and s.to_date='9999-01-01'
) t1
inner join(
select dept_no,d2.emp_no as manager_no,salary as manager_salary
from dept_manager as d2 inner join salaries as s on d2.emp_no=s.emp_no
where d2.to_date='9999-01-01' and s.to_date='9999-01-01'
) t2
on t1.dept_no=t2.dept_no
where t1.emp_salary>t2.manager_salary;