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

目录

1、简单查询2、分页查询 limit3、题目描述4、exists5、视图

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