hr用户数据查询练习一

1.让SELECT TO_CHAR(SALARY,’L99,999.99’) FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是￥和$。

select to_char(salary,'L99,999.99') from employees where rownum<5;
select to_char(salary,'$99,999.99') from employees where rownum<5;

2.列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。

select first_name ||' '|| last_name,salary||'元',round(salary*1.8)||'元' from employees where rownum<6;

3.找出谁是最高领导，将名字按大写形式显示。

select upper(first_name ||' '|| last_name) from employees where manager_id is null;

4.找出First_Name 为David，Last_Name为Austin 的直接领导名字。
子查询

select first_name ||' '|| last_name from employees
where employee_id=(select manager_id from employees 
where first_name='David' and last_name='Austin');

连接查询

select b.first_name ||' '|| b.last_name from employees a,employees b
where a.first_name='David' and a.last_name='Austin' and a.manager_id=b.employee_id;

5.First_Name 为Alexander，Last_Name为Hunold领导谁。（谁向David 报告）。

select b.first_name ||' '|| b.last_name from employees a,employees b
where a.first_name='Alexander' and a.last_name='Hunold' and a.employee_id=b.manager_id;

6.哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。

select a.first_name ||' '|| a.last_name,a.salary,
b.first_name ||' '|| b.last_name,b.salary from employees a,employees b
where a.salary>b.salary and a.manager_id=b.employee_id;

7.哪些员工和Chen(LAST_NAME)同部门。

select * from employees where department_id=
(select department_id from employees where last_name='Chen');

8.哪些员工跟De Haan(LAST_NAME)做一样职位。

select * from employees where job_id=
(select job_id from employees where last_name='Hall');

9.哪些员工跟Hall(LAST_NAME)不在同一个部门。

select * from employees where job_id=
(select job_id from employees where last_name='Hall');

10.哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。

select * from employees where job_id!=
(select job_id from employees where first_name='William' and last_name='Smith');

11.显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。

select e.first_name ||' '|| e.last_name,e.commission_pct,d.department_name,r.region_name
from employees e,countries c,departments d ,locations l,regions r
where e.department_id=d.department_id and c.country_id=l.country_id 
and d.location_id=l.location_id and r.region_id=c.region_id
and e.commission_pct>0;

12.显示Executive部门有哪些职位。

select distinct job_id from employees where department_id=
(select department_id from departments where department_name='Executive');

13.整个公司中，最高工资和最低工资相差多少。

select max(salary)-min(salary) from employees;

14.提成大于0 的人数。

select count(employee_id) from employees where commission_pct>0;

15.显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。

select max(salary),min(salary),sum(salary),round(avg(salary)) from employees;

16.整个公司有多少个领导。

select count(distinct manager_id) from employees;

17.列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期。

select distinct a.first_name ||' '|| a.last_name,a.salary,a.hire_date from employees a,employees b
where a.department_id=b.department_id and a.hire_date>b.hire_date
and a.salary>b.salary;