SQL练习（三）

http://www.cnblogs.com/huyong/archive/2011/06/20/2085137.html

感觉oracle sql的写法是不是和MySQL有一点点差异呢...不管了，大致相似就行了吧！

/*
第三篇
*/

1. 让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

/*--说明：对于'$99,999.99'格式符：
L：表示强制显示当地货币符号
$: 表示显示美元符号
9: 表示一个数字
0: 表示强制0显示
.: 表示一个小数点
,: 表示一个千位分隔符
--------------*/


2. 列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。
select EMPLOYEES.first_name,EMPLOYEES.SALARY AS S,ROUND(S*1.08) FROM EMPLOYEES WHERE ROWNUM <= 5

3. 找出谁是最高领导，将名字按大写形式显示。
select (UPPER(EMPLOYEES.first_name)+' '+UPPER(EMPLOYEES.last_name)) from EMPLOYEES where manager_id is NULL;

4. 找出First_Name 为David，Last_Name为Austin 的直接领导名字。
select e2.first_name from EMPLOYEES e1,EMPLOYEES e2 where e1.first_name = 'David' and e1.last_name='Austin' and e2.employee_id = e1.manager_id;

5. First_Name 为Alexander，Last_Name为Hunold领导谁。（谁向David 报告）。
select e2.first_name from EMPLOYEES e1,EMPLOYEES e2 where e1.first_name = 'Alexander' and e1.last_name='Hunold' and 
e2.manager_id = e1.employee_id

6. 哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。
select e2.first_name as staff_name,e2.salery as staff_salery,e1.first_name as manager_name,e1.salery as manager_salery from EMPLOYEES e1,EMPLOYEES e2 where e2.manager_id = e1.employee_id and e2.salery > e1.salery

7. 哪些员工和Chen(LAST_NAME)同部门。
select * from EMPLOYEES where department_id = (select department_id from EMPLOYEES where LAST_NAME='Chen') and LAST_NAME!='Chen'

8. 哪些员工跟De Haan(LAST_NAME)做一样职位。
select * from EMPLOYEES where job_id = (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 != (select department_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 LAST_NAME='De Haan' and first_name='William')

11. 显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。
select e.first_name,e.comm,d.department_name,r.loction_name from EMPLOYEES e,department d,regions r where e.department_id = d.department_id  and d.loction_id = r.loction_id and e.comm is not NULL;

12. 显示Executive部门有哪些职位。
select e.job from employee e, department d where d.department_name = 'Executive' and d.department_id = e. department_id;

13. 整个公司中，最高工资和最低工资相差多少。
select (MAX(salery)-MIN(salery)) from employee;


14. 提成大于0 的人数。
select COUNT(employee_id) from employee where comm is not NULL;

15. 显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。
select MAX(salery),MIN(salery),COUNT(salery),AVG(salery) from employee;

16. 整个公司有多少个领导。
select DISTINCT COUNT(manager_id) from employee where manager_id is not null;

count(DISTINCT(manager_id));

17. 列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期。
select e1.ename as name,e1.salery as salery,e1.hiredate as hiredate from employee e1,employee e2 where e1.department_id = e2.department_id and e1.hiredate>e2.hiredate and e1.salery>e2.salery order by salery;