1 找最晚入职员工的所有信息
SELECT
*
FROM
employees
WHERE
hire_date = ( SELECT max( hire_date ) FROM employees );
2 查找入职员工时间排名倒数第三的员工所有信息
SELECT
*
FROM
employees
WHERE
hire_date = ( SELECT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2, 1 );
3 查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no
SELECT
a.*,
b.dept_no
FROM
salaries a,
dept_manager b
WHERE
a.emp_no = b.emp_no
AND a.to_date = '9999-01-01'
AND b.to_date = '9999-01-01';
4 查找所有已经分配部门的员工的last_name和first_name以及dept_no
SELECT
b.last_name,
b.first_name,
a.dept_no
FROM
employees AS b
INNER JOIN dept_emp AS a ON a.emp_no = b.emp_no;
5 查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括展示没有分配具体部门的员工
SELECT
a.last_name,
a.first_name,
b.dept_no
FROM
employees AS a
LEFT JOIN dept_emp AS b ON a.emp_no = b.emp_no;
6 查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序
SELECT
a.emp_no,
b.salary
FROM
employees AS a
LEFT JOIN salaries AS b ON a.emp_no = b.emp_no
GROUP BY
a.emp_no
HAVING
max( a.hire_date )
ORDER BY
a.emp_no DESC;
7 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
SELECT
emp_no,
count( to_date ) AS t
FROM
salaries
GROUP BY
emp_no
HAVING
count( to_date ) > 15;
8 找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
SELECT
salary
FROM
salaries
WHERE
to_date = '9999-01-01'
GROUP BY
salary
ORDER BY
salary DESC;
9 获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date='9999-01-01'
SELECT
d.dept_no,
d.emp_no,
s.salary
FROM
dept_manager AS d
INNER JOIN salaries AS s ON d.emp_no = s.emp_no
AND d.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
ORDER BY
d.emp_no;
10 获取所有非manager的员工emp_no
题目描述
获取所有非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 SELECT
a.emp_no
FROM
employees a
LEFT JOIN dept_manager b ON a.emp_no = b.emp_no
WHERE
b.emp_no IS NULL;
11获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date='9999-01-01'。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_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
a.emp_no,
b.emp_no AS manager_no
FROM
dept_emp a
LEFT JOIN dept_manager b ON a.dept_no = b.dept_no
WHERE
b.to_date = '9999-01-01'
AND a.emp_no <> b.emp_no;
12 获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
解:
SELECT
d.dept_no,
d.emp_no,
MAX( s.salary )
FROM
dept_emp d
LEFT JOIN salaries s ON d.emp_no = s.emp_no
WHERE
d.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
GROUP 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 )
FROM
titles
GROUP BY
title
HAVING
count( title ) >= 2;
14 从titles表获取按照title进行分组,每组emp_no个数大于等于2,给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
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( distinct emp_no ) as t
FROM
titles
GROUP BY
title
HAVING
t > 1;
15 查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列
解:
SELECT
*
FROM
employees
WHERE
emp_no % 2 = 1
AND last_name <> 'Mary'
ORDER BY
hire_date DESC;
#法2
SELECT
*
FROM
employees
WHERE
MOD ( emp_no, 2 ) = 1
AND last_name <> 'Mary'
ORDER BY
hire_date DESC;
16 统计出当前各个title类型对应的员工当前(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
a.title,
avg( b.salary ) AS `avg`
FROM
titles a
LEFT JOIN salaries b ON a.emp_no = b.emp_no
WHERE
b.to_date = '9999-01-01'
AND a.to_date = '9999-01-01'
GROUP BY
a.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
WHERE
to_date = '9999-01-01'
ORDER BY
salary DESC
LIMIT 1,
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
e.emp_no,
max( s.salary ) AS salary_2,
e.last_name,
e.first_name
FROM
employees AS e
INNER JOIN salaries AS s ON e.emp_no = s.emp_no
WHERE
s.to_date = '9999-01-01'
AND s.salary <> ( SELECT max( salary ) FROM salaries WHERE 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
a.last_name,
a.first_name,
d.dept_name
FROM
(
SELECT
e.last_name,
e.first_name,
e.emp_no,
de.dept_no
FROM
employees AS e
LEFT JOIN dept_emp AS de ON e.emp_no = de.emp_no
) AS a
LEFT JOIN departments AS d ON a.dept_no = d.dept_no;
#法2
SELECT
e.last_name,
e.first_name,
d.dept_name
FROM
employees AS e
LEFT JOIN dept_emp AS de ON e.emp_no = de.emp_no
LEFT JOIN departments AS d ON de.dept_no = d.dept_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 salary AS end_salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1 ) - ( SELECT salary AS start_salary FROM salaries WHERE emp_no = 10001 ORDER BY from_date ASC LIMIT 1 )
) AS growth;
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`));
解:
SELECT
e.emp_no,
( s1.salary - s2.salary ) AS growth
FROM
employees AS e
INNER JOIN salaries AS s1 ON e.emp_no = s1.emp_no
AND s1.to_date = '9999-01-01'
INNER JOIN salaries AS s2 ON e.emp_no = s2.emp_no
AND s2.from_date = e.hire_date
ORDER BY
growth ASC;
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
d.dept_no,
d.dept_name,
count( s.salary ) AS `sum`
FROM
salaries AS s
INNER JOIN dept_emp AS dp ON s.emp_no = dp.emp_no
INNER JOIN departments AS d ON d.dept_no = dp.dept_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`));
解1:
SELECT
s1.emp_no,
s1.salary,
COUNT( DISTINCT s2.salary ) AS rank1
FROM
salaries AS s1,
salaries AS s2
WHERE
s1.to_date = '9999-01-01'
AND s2.to_date = '9999-01-01'
AND s1.salary <= s2.salary
GROUP BY
s1.emp_no
ORDER BY
s1.salary DESC,
s1.emp_no ASC;
解2:
SELECT
emp_no,
salary,
DENSE_RANK() OVER(ORDER BY salary DESC) AS rank_1
FROM
salaries
WHERE
to_date = '9999-01-01'
GROUP BY
emp_no
ORDER BY
salary DESC,
emp_no ASC;
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
d_e.dept_no,
e.emp_no,
s.salary
FROM
dept_emp AS d_e
INNER JOIN employees AS e ON d_e.emp_no = e.emp_no
LEFT JOIN salaries AS s ON e.emp_no = s.emp_no
WHERE
e.emp_no NOT IN ( SELECT emp_no FROM dept_manager )
AND s.to_date = '9999-01-01'
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
emp_no,
manager_no,
emp_salary,
manager_salary
FROM
(
SELECT
de.emp_no AS emp_no,
s.salary AS emp_salary,
de.dept_no AS bumen
FROM
dept_emp AS de
INNER JOIN salaries AS s ON de.emp_no = s.emp_no
WHERE
de.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
) AS yg_xz
INNER JOIN (
SELECT
dm.emp_no AS manager_no,
s.salary AS manager_salary,
dm.dept_no AS bumen
FROM
dept_manager AS dm
INNER JOIN salaries AS s ON dm.emp_no = s.emp_no
WHERE
dm.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
) AS jl_xz ON yg_xz.bumen = jl_xz.bumen
WHERE
yg_xz.emp_salary > jl_xz.manager_salary;
26 汇总各个部门当前员工的title类型的分配数目,结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
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 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
d.dept_no,
d.dept_name,
t.title,
count( de.emp_no ) AS `count`
FROM
departments AS d
LEFT JOIN dept_emp AS de ON d.dept_no = de.dept_no
AND de.to_date = '9999-01-01'
LEFT JOIN titles AS t ON t.emp_no = de.emp_no
AND t.to_date = '9999-01-01'
GROUP BY
d.dept_no,
t.title;
27 给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth,并按照salary_growth逆序排列。
提示:在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
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.from_date,
s1.salary - s2.salary AS salary_growth
FROM
salaries s1
INNER JOIN salaries s2 ON date_format( s1.to_date, '%Y' ) - date_format( s2.to_date, '%Y' ) = 1
AND s1.emp_no = s2.emp_no
ORDER BY
salary_growth DESC;
28 查找描述信息中包括robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5部
CREATE TABLE IF NOT EXISTS film (
film_id smallint(5) NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, `last_update` timestamp);
解:
SELECT
c.NAME,
count( fc.film_id )
FROM
film_category AS fc
INNER JOIN category AS c ON fc.category_id = c.category_id
INNER JOIN film AS f ON fc.film_id = f.film_id
INNER JOIN ( SELECT category_id, count( film_id ) AS cf FROM film_category GROUP BY category_id HAVING count( film_id ) >= 5 ) AS tmp1 ON tmp1.category_id = c.category_id
WHERE
f.description LIKE '%robot%'
GROUP BY
c.category_id
28 使用join查询方式找出没有分类的电影id以及名称
SELECT
f.film_id,
f.title
FROM
film as f
left join
film_category AS fc
ON f.film_id=fc.film_id
where fc.category_id is null;
29 使用子查询的方式找出属于Action分类的所有电影对应的title,description
select
title,
description
from film as f
where film_id in
(
select
f.film_id
from film as f
left join
film_category as fc
on
f.film_id=fc.film_id
left join
category as c
on
fc.category_id=c.category_id
where c.name='Action'
)
30 获取select * from employees对应的执行计划
explain select * from employees;