Oracle经典查询练手第二篇

--1. 让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE  ROWNUM < 5 输出结果的货币单位是￥和$。select to_char(salary,'L99,999.99') from hr.employees where  rownum < 5 ;select to_char(salary,'$99,999.99') from hr.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 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;--子查询select  first_name||' '||last_name from employees where employee_id = (  select manager_id from employees where first_name='David' and last_name='Austin');  --5. First_Name 为Alexander，Last_Name为Hunold领导谁。(同上题差不多)--连接查询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;--子查询select  first_name||' '||last_name from employees where manager_id = (  select employee_id from employees where first_name='Alexander' and last_name='Hunold');--6. 哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。select b.first_name||' '||b.last_name||' '||b.salary ,a.first_name||' '||a.last_name||' '||a.salary from employees a,employees b where  b.manager_id = a.employee_id and b.salary>a.salary ;--7. 哪些员工和Chen(LAST_NAME)同部门。select * from employees where department_id in(  select department_id from employees where last_name = 'Chen');--8. 哪些员工跟De Haan(LAST_NAME)做一样职位。select * from employees where job_id in(  select job_id from employees where last_name = 'De Haan') and  last_name <> 'De Haan' ;--9. 哪些员工跟Hall(LAST_NAME)不在同一个部门。select * from employees where department_id  not in(  select department_id from employees where last_name = 'Hall') ;--10. 哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。select * from employees where job_id  not in(  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_namefrom employees e,countries c,departments d,locations l,regions rwhere e.department_id=d.department_id and d.location_id=l.location_id and l.country_id=c.country_id and c.region_id=r.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), max(salary) - min(salary) from employees;--14. 提成大于0 的人数。select count(*) 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.salary>b.salary and a.HIRE_DATE>b.HIRE_DATE;