SQL练习-白红宇

SQL练习

阅读量：4092 次

发布时间：2019-05-25

本文共 8526 字，大约阅读时间需要 28 分钟。

1、查找入职最晚的员工的所有信息

表结构：

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

查找结果：

emp_no	birth_date	first_name	last_name	gender	hire_date
10008	1958-02-19	Saniya	Kalloufi	M	1994-09-15

这里限定所有员工没有在同一天入职的，不过如果入职时间采用的是时间戳类型，其实也可以查到。MySQL时间类型有：Date、Datetime、Timestamp，前一种只有日期，后两种既有日期又有具体的时间。

select * from employees order by hire_date desc limit 1;##错误#select * from employees where hire_date in (select max(hire_date) from employees);#SELECT * FROM employees WHERE hire_date = (SELECT MAX(hire_date) FROM employees);

2、查找入职员工时间排名倒数第三的员工所有信息

select * from employees order by hire_date desc limit 2, 1; #limit 2, 1表示从编号为2的记录开始，选一条数据

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

select s.*, d.dept_no from salaries s, dept_manager d where s.emp_no = d.emp_no and s.to_date = '9999-01-01' and d.to_date = '9999-01-01' order by s.emp_no;SELECT s.emp_no,        s.salary,       s.from_date,       s.to_date,       d.dept_noFROM dept_manager d INNER JOIN salaries sON d.emp_no = s.emp_noWHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'GROUP BY s.emp_no;##错误#select salaries.emp_no, salary, from_date, to_date, dept_no from salaries, dept_manager where salaries.emp_no = dept_manager.emp_no and salaries.to_date = '9999-01-01' and dept_manager.to_date = '9999-01-01' order by salaries.emp_no;

参考：

4、查找所有已经分配部门的员工的last_name和first_name以及dept_no

select last_name, first_name, dept_no from dept_emp d, employees e where d.emp_no = e.emp_no;##交换顺序则出错select last_name, first_name, dept_no from employees e，dept_emp d where d.emp_no = e.emp_no;

参考：

5、查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工

INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。

LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。

select last_name, first_name, dept_no from employees e left join dept_emp d on d.emp_no = e.emp_no;##错误select last_name, first_name, dept_no from dept_emp d, employees e where d.emp_no = e.emp_no;

参考：

6、查找所有员工入职时候的薪水情况

select e.emp_no, salary from employees e, salaries s where e.emp_no = s.emp_no and hire_date = from_date order by e.emp_no desc;SELECT e.emp_no, s.salary FROM employees AS e INNER JOIN salaries AS sON e.emp_no = s.emp_no AND e.hire_date = s.from_dateORDER BY e.emp_no DESC

7、查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

注意，count()函数不能在where子句中使用

select emp_no, count(*) from salaries group by emp_no having count(*) > 15;SELECT emp_no, COUNT(emp_no) AS t FROM salaries GROUP BY emp_no HAVING t > 15;

8、找出所有员工当前薪水salary情况

用group by也可以达到distinct的效果

select distinct(salary) from salaries where to_date = '9999-01-01' order by salary desc;select salary from salaries where to_date='9999-01-01' group by salary order by salary desc;

9、获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary

select d.dept_no, s.emp_no, s.salary from dept_manager d, salaries s where d.emp_no = s.emp_no and s.to_date = '9999-01-01' and d.to_date = '9999-01-01';

参考：

10、获取所有非manager的员工emp_no

select emp_no from employees where emp_no not in (    select emp_no from dept_manager);SELECT employees.emp_no FROM employees LEFT JOIN dept_managerON employees.emp_no = dept_manager.emp_noWHERE dept_no IS NULL;

11、获取所有员工当前的manager

select e.emp_no, m.emp_no from dept_emp e, dept_manager m where e.dept_no = m.dept_no and e.emp_no <> m.emp_no and e.to_date = '9999-01-01' and m.to_date = '9999-01-01';

12、获取所有部门中当前员工薪水最高的相关信息

SELECT e.dept_no, e.emp_no, s.salaryFROM dept_emp AS e INNER JOIN salaries AS sON e.emp_no = s.emp_noWHERE e.to_date = '9999-01-01' AND s.to_date = '9999-01-01'GROUP BY e.dept_noHAVING s.salary = MAX(s.salary);select de.dept_no, de.emp_no, s.salary from dept_emp de inner join salaries son de.emp_no = s.emp_no and de.to_date = '9999-01-01' and s.to_date = '9999-01-01'where s.salary = (    select max(s2.salary) from dept_emp de2 inner join salaries s2    on de2.emp_no = s2.emp_no and de2.to_date = '9999-01-01' and s2.to_date = '9999-01-01'    where de2.dept_no = de.dept_no    group by de2.dept_no)order by de.dept_no;SELECT r1.dept_no, r1.emp_no, r1.salary FROM    --创建r1表用于存放当前每个部门每个员工的薪水    (    SELECT d.dept_no, d.emp_no, s1.salary    FROM dept_emp d, salaries s1    WHERE d.to_date='9999-01-01'         AND s1.to_date='9999-01-01'        AND d.emp_no = s1.emp_no)r1JOIN  --创建r2表用于存放当前每个部门薪水的最大值    (    SELECT d.dept_no, MAX(s2.salary) as maxsalary    FROM dept_emp d    --为了避免GROUP BY默认取非聚合数据的第一条记录，先把salary排好序    JOIN (SELECT * FROM salaries ORDER BY salary DESC)s2    ON d.emp_no = s2.emp_no    WHERE d.to_date='9999-01-01'         AND s2.to_date='9999-01-01'    GROUP BY d.dept_no)r2ON r1.salary = r2.maxsalary    AND r1.dept_no = r2.dept_noORDER BY r2.dept_no##错误select e.dept_no, e.emp_no, salary from dept_emp e, salaries s where e.to_date = '9999-01-01' and s.to_date = '9999-01-01' group by e.dept_no having salary = max(salary);

参考：

13、从titles表获取按照title进行分组

select title, count(*) from titles group by title;SELECT title, COUNT(title) AS t FROM titlesGROUP BY title HAVING t >= 2;

14、从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略

select title, count(distinct(emp_no)) as t from titlesgroup by title having t >= 2;##错误select title, count(to_date) as t from titleswhere to_date = '9999-01-01' group by title having t >= 2;

15、查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)

select * from employees where emp_no%2 = 1 and last_name <> 'Marry' order by hire_date desc;select * from employees ewhere e.emp_no & 1 = 1 and e.last_name != 'Mary'ORDER BY e.hire_date desc;

16、统计出当前各个title类型对应的员工当前薪水对应的平均工资

select title, avg(salary) from salaries s, titles t where s.emp_no = t.emp_no and s.to_date = '9999-01-01' and t.to_date = '9999-01-01' group by title;

17、获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no, salary from salaries order by salary desc limit 1, 1;select emp_no, salary from salarieswhere to_date = '9999-01-01' and salary = (select distinct salary from salaries order by salary desc limit 1,1);select emp_no, salary from salarieswhere salary = (    select salary from salaries     group by salary    order by salary desc limit 1,1) and to_date = '9999-01-01';##找到成绩前几名的记录select emp_no,max(salary) from salarieswhere salary < (select max(salary) from salaries);select emp_no, salary from salarieswhere salary = (    select max(salary) from salaries where salary < (select max(salary) from salaries));

18、获取当前薪水第二多的员工的emp_no以及其对应的薪水salary（不使用order by）

select e.emp_no, salary, last_name, first_namefrom employees e, salaries swhere e.emp_no = s.emp_no and to_date = '9999-01-01'and salary = (    select max(salary) from salaries     where to_date = '9999-01-01'     and salary < (select max(salary) from salaries where to_date = '9999-01-01'));##通用的查找前几的方式select e.emp_no,s.salary,e.last_name,e.first_name from employees ejoin salaries s on e.emp_no=s.emp_no and  s.to_date='9999-01-01' and s.salary = (     select s1.salary     from salaries s1     join salaries s2      on s1.salary<=s2.salary and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'     group by s1.salary     having count(distinct s2.salary)=2 );

19、查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

1、第一次LEFT JOIN连接employees表与dept_emp表，得到所有员工的last_name和first_name以及对应的dept_no，也包括暂时没有分配部门的员工

2、第二次LEFT JOIN连接上表与departments表，即连接dept_no与dept_name，得到所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

SELECT em.last_name, em.first_name, dp.dept_nameFROM (employees AS em LEFT JOIN dept_emp AS de ON em.emp_no = de.emp_no)LEFT JOIN departments AS dp ON de.dept_no = dp.dept_no;##错误select last_name, first_name, d.dept_namefrom departments d, dept_emp de, employees ewhere d.dept_no = de.dept_no and de.emp_no = e.emp_no;

20、查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)

##最大的工资-最小的工资，不考虑中途降薪select max(salary) - min(salary) as growthfrom salarieswhere emp_no = 10001; ##最后的工资-最早的工资SELECT ( (SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1) -(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date ASC LIMIT 1)) AS growth;

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

1) 找出每个员工当前工资

2) 找出每个员工入职时的工资

3) 结合找出growth

select t1.emp_no,(t1.sTo-t2.sHire) as growth from (select e.emp_no,s.salary as sTo from employees as eleft join salaries as s on e.emp_no=s.emp_no where s.to_date='9999-01-01') as t1 join(select e.emp_no,s.salary as sHire from employees as eleft join  salaries as s on e.emp_no=s.emp_no and s.from_date=e.hire_date) as t2 on t1.emp_no=t2.emp_noorder by growth asc;

参考：

22、