SQL实战-数据分析-每日一练

1、简单查询

题目描述：
查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_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`));
如：
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');

输出格式:

代码实现：

select avg(salary) as avg_salary 
from salaries 
where salary not in ( select max(salary) from salaries 
                     where to_date='9999-01-01')
and salary not in (select min(salary) from salaries 
                   where to_date='9999-01-01')
and to_date='9999-01-01'

2、分页查询 limit

这个有点儿意思，以前没遇见过，表示对新题型很欢喜。

题目描述：
分页查询employees表，每5行一页，返回第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
limit 5,5

解题思路：
其实这也就是平时我们遇到的limit的跳着进行查询。换这种说法而已。
第一页5行，我们查找第二页的5行，所以将跳过与第一页的5行，查找第二页的5行。
limit 5,5
第一个5 表示：跳过与第一页的5行；
第二个5表示：查找第二页的5行。

3、题目描述

获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received，没有分配奖金的员工不显示对应的bonus类型btype和received

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(11) NOT NULL,
received datetime NOT NULL,
btype smallint(5) NOT NULL);

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 de.emp_no,de.dept_no,eb.btype,eb.received 
from dept_emp de left join emp_bonus eb on de.emp_no=eb.emp_no 
where de.emp_no in (select emp_no from employees )

4、exists

题目描述：
使用含有关键字exists查找未分配具体部门的员工的所有信息。

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

输出格式:

代码实现：

select * from 
employees 
where not exists 
(select dept_emp.emp_no from 
 dept_emp 
 where  dept_emp.emp_no=employees.emp_no )

5、视图

存在如下的视图：

create view emp_v as select * from employees where emp_no >10005;

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

获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。
(你能不用select * from employees where emp_no >10005 这条语句完成吗，挑战一下自己对视图的理解)
输出格式:

代码实现：

select * from emp_v