数据库SQL实战-查找排除当前最大、最小salary之后的员工的平均工资avg_salary(mysql)

1. 查找字符串’10,A,B’

1.1 题目描述

查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。

1.2 语句实现

select length("10,A,B") - length(replace("10,A,B",",",""))
as cnt;

2. 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

2.1 题目描述

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
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));
输出格式：

2.2 语句实现

select first_name 
from employees
order by substr(first_name,-2);

3. 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

3.1 题目描述

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
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));
输出格式:

3.2 语句实现

select dept_no, group_concat(emp_no) as employees
from dept_emp
group by dept_no;

4. 查找排除当前最大、最小salary之后的员工的平均工资avg_salary

4.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’);
输出格式:

4.2 语句实现

select avg(salary) as avg_salary
from salaries
where to_date = '9999-01-01'
and 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');

5. 分页查询employees表，每5行一页，返回第2页的数据

5.1 题目描述

分页查询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));

5.2 语句实现

select * from employees
limit 5 offset 5;