/1.查询emp表中的数据/
select * from employees;
/2.求出emp表中最大工资,最小工资,总工资和平均工资/
select MAX(salary),MIN(salary),SUM(salary),AVG(salary) from employees
/3.查询emp表中有多少条记录/
select COUNT(*) from employees
select COUNT(1) from employees
select COUNT(manager_id) from employees
/4.查询每个部门的总工资和平均工资 department_id:组标识/
select department_id,SUM(salary),AVG(salary),MAX(salary),MIN(salary)
from employees
group by department_id
/5.查询emp表中每个职位的最大工资和最小工资/
select job_id,MAX(salary),MIN(salary) from employees
group by job_id
/6.查询10部门中每个职位的最大工资,最小工资以及总工资/
select job_id,MAX(salary),MIN(salary),SUM(salary) from employees
where department_id = 10
group by job_id
/7.查询emp表中每个部门中,每个职位的最大工资,最小工资,平均工资/
select e.department_id,e.job_id,MAX(e.salary) ,min(e.salary) , AVG(e.salary)
from employees e
group by e.department_id,e.job_id
/8.查询emp表中平均工资大于2000的部门的最高工资,最低工资和平均工资/
select department_id,MAX(salary),MIN(salary),AVG(salary)
from employees
group by department_id
having AVG(salary) > 2000
/9.查询emp表中SALESMAN职位的,查询每个部门中平均工资大于1000的部门的最高工资,
最低工资以及平均工资/
select e.department_id,avg(salary),min(salary),MAX(salary)
from employees e
where e.job_id=‘PU_CLERK’
group by e.department_id HAVING avg(salary) > 1000
/10.查询平均工资大于15000的职位的最高工资以及最低工资,并且按照职位降序排序/
select e.job_id,max(salary),min(salary)
from employees e
group by e.job_id HAVING avg(salary)>15000
order by e.job_id desc
/11.查询平均工资大于7000,平均奖金大于0.2的职位。/
select e.job_id from employees e GROUP BY e.job_id
HAVING avg(salary)>7000 and avg(IFNULL(e.commission_pct,0))>0.2
/13.分页查询emp表中的数据,要求每页5条,查询第3页/
select * from employees LIMIT 10,5
select * from employees LIMIT 0,5
select * from employees LIMIT 5
/14.查询emp表中工资大于1500的员工信息,要求每页显示3条,显示第2页数据/
/15.查询emp表中30部门的员工信息,要求按照工资进行降序排列,然后每页2条,查询第2页数据/
select * from employees
where department_id = 30
order by salary desc
limit 2,2
/16.查询每个部门的