操作HR账户下的employees表：
1. 简单解释如下几个SQL关键词的书写顺序、执行顺序、关键词作用？
作用:
select 选择要显示的内容(列,单行函数处理,列的运算,组函数处理...)
from 确定SQL语句要处理的表.
where 条件过滤: 对from后的表数据进行条件过滤.
having 条件过滤: 对数据分组后的数据进行过滤.
注意: 优先使用where条件过滤.
group by 对数据进行分组操作
order by 对最终的查询结果进行排序.
书写顺序
select ... from 表 where 条件 group by 字段 having 组数据过滤条件 order by 字段 asc|desc
执行顺序
from--->where---->group by---->having---->select---->order by
2. 查询工资排名第5到第10的员工信息? [1分]
功能: 排序 序号伪列 范围查询
思路:
1. 按照薪资降序排序
2. 对查询结果表添加需要的列
3. 截取5~10数据,
select employee_id,first_name,salary
from
(select e.employee_id,e.first_name,e.salary,rownum as rn
from
(select * from employees order by salary desc) e
)
where rn >=5 and rn<=10;3. 查询50号部门,60号部门,70号部门的平均工资?
① 过滤保留50 60 70 部门信息
② 确定分组依据,进行分组操作. group by department_id
③ 对每组进行平均薪资统计 avg(salary)
select department_id,avg(salary)
from employees
where department_id in (50,60,70)
group by department_id;
4. 查询平均工资高于8000元的部门的最高工资.
① 按照部门分组 group by
② 统计各个部门的平均薪资>8000组保留. having
③ 统计过滤后的部门的最高薪资? max()
select department_id,max(salary)
from employees
group by department_id
having avg(salary)>8000;
5. 显示与30号部门first_name为’Guy’员工工资相同的员工姓名和工资
显示员工信息条件: salary = (select salary from ... where first_name = 'Guy' and department_id=30)
① 获得30号部门first_name为’Guy’员工工资 guySalary
select salary from employees where first_name = 'Guy' and department_id=30;
② 查询员工薪资=guySalary的员工信息?
select first_name,salary
from employees
where salary = (GuySalary)
③ 合并
select first_name,salary
from employees
where salary = (select salary from employees where first_name = 'Guy' and department_id=30)