oracle-hr表查询命令练习（超完整的select命令大全）(二)
rst_name)) "姓名",trunc(months_between(sysdate,hire_date),0) "在职时间" from employees;
15. 查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列
Select initcap(concat(last_name,first_name)) "姓名",trunc(months_between(sysdate,hire_date),0) "在职时间" from employees order by trunc(months_between(sysdate,hire_date),0) desc;
16.做一个查询，产生下面的结果
Dream Salary
King earns $24000 monthly but wants $72000
Select last_name||' earns '||to_char(salary,'$99999')||' monthly but wants '||to_char(salary*3,'$99999') as "Dream Salary" from employees;
17.使用decode函数，按照下面的条件：
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
Others F
产生下面的结果
Last_name
Job_id
Grade
king
AD_PRES
A
写法一：
select last_name,job_id,decode(job_id,'AD_PRES','A','ST_MAN','B','IT_PROG','C','SA_REP ','D','ST_CLERK','E','F')Grade from employees;
Select last_name,job_id,
case job_id
when 'AD_PRES' then 'A'
when 'ST_MAN' then 'B'
when 'IT_PROG' then 'C'
when 'SA_REP' then 'D'
when 'ST_CLERK' then 'E'
else 'F' end "Grage" from employees;
18.查询公司员工工资的最大值，最小值，平均值，总和
select max(salary) "最大值",min(salary) "最小值",avg(salary) "平均值",sum(salary) "总和" from employees;
19.查询各job_id的员工工资的最大值，最小值，平均值，总和
select job_id,max(salary) "最大值",min(salary) "最小值",avg(salary) "平均值",sum(salary) "总和" from employees group by job_id;
20.选择具有各个job_id的员工人数
Select job_id,count(*) from employees group by job_id;
21.查询员工最高工资和最低工资的差距（DIFFERENCE）
Select max(salary)-min(salary) as "DIFFERENCE" from employees;
22.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
Select manager_id,min(salary) from employees group by manager_id having min(salary)>=6000 and manager_id is not null;
23.查询所有部门的名字，location_id，员工数量和工资平均值
Select department_name,location_id,count(e.job_id) as "部门总人数",avg(e.salary) as "平均工资" from departments d,employees e where d.department_id=e.department_id group by department_name,location_id;