--脚本和其它参考见part1
select * from employees;
select * from DEPARTMENTS;
select * from locations;

SELECT TO_CHAR(SALARY,'U99,999.99','NLS_DUAL_CURRENCY=RMB') FROM EMPLOYEES WHERE ROWNUM < 5;
SELECT TO_CHAR(SALARY,'$99,999.99') FROM EMPLOYEES WHERE ROWNUM < 5;
SELECT TO_CHAR(SALARY,'99,999.$99') FROM EMPLOYEES WHERE ROWNUM < 5;
--系统货币单位？ 对应参数NLS_ISO_CURRENCY
SELECT TO_CHAR(SALARY,'C99,999.99') FROM EMPLOYEES WHERE ROWNUM < 5;
--本地货币符号 对应参数NLS_CURRENCY
SELECT TO_CHAR(SALARY,'L99,999.99') FROM EMPLOYEES WHERE ROWNUM < 5;
--不足补0，9表数字，0和9数量等于为数
SELECT TO_CHAR(SALARY,'L099,999.99') FROM EMPLOYEES WHERE ROWNUM < 5;

--C ISO 国家对应货币英文缩写 CNY USD
alter session set NLS_ISO_CURRENCY='China';

--L 所件即所得，乱码的话去修改编码
alter session set NLS_CURRENCY='RMB';
alter session set NLS_CURRENCY='￥';

--查看 v$nls_parameters（客户端nls参数） 和nls_database_parameters（数据库nls参数）
select * from v$nls_parameters;
select * from nls_database_parameters;

-- 均无权限，不知啥影响
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8;
alter system set nls_language='SIMPLIFIED CHINESE' scope=spfile;

--2. 列出前五位每个员工的名字， 工资、涨薪后的的工资（ 涨幅为8% ） ， 以“ 元”为单位进行四舍五入。
---第一句快些，数据少的时候，再select 1次 比加剪乘除快
select first_name,sal,round(sal*1.08) Acsal_now from (select first_name,salary sal from employees where rownum < 6);
select first_name,salary sal,round(salary*1.08) sal_now from employees where rownum < 6;

--3. 找出谁是最高领导，将名字按大写形式显示。
select upper(first_name) from employees where manager_id is null;

--4. 找出First_Name 为David ， Last_Name 为Austin 的直接领导名字。
select first_name||' '||last_name ename 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 first_name||' '||last_name ename from employees where manager_id= (select employee_id from employees where first_name= 'Alexander' and last_name='Hunold' );

--6. 哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资
---隐性连接
select a.first_name||' '||a.last_name ename,a.salary,b.first_name||' '||b.last_name mname,b.salary from employees a,employees b where a.manager_id=b.employee_id and a.salary > b.salary;
--内连接
select a.first_name||' '||a.last_name ename,b.salary from employees a inner join employees b on a.manager_id=b.employee_id and a.salary > b.salary;

--7. 哪些员工和Chen(LAST_NAME) 同部门。
select first_name||' '||last_name ename,department_id from employees where department_id = (select department_id from employees where last_name='Chen');

--8. 哪些员工跟De Haan(LAST_NAME) 做一样职位。
select first_name||' '||last_name ename,job_id from employees where job_id = (select job_id from employees where last_name='De Haan');

--9. 哪些员工跟Hall(LAST_NAME) 不在同一个部门。
select first_name||' '||last_name ename,department_id from employees where department_id != (select department_id from employees where last_name='Hall');

--10. 哪些员工跟William （ FIRST_NAME ） 、Smith(LAST_NAME) 做不一样的职位。
select first_name||' '||last_name ename,job_id from employees where job_id != (select job_id from employees where last_name='Smith' and FIRST_NAME='William');

--11. 显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。
select first_name|| ' ' ||last_name ename,commission_pct,empp.department_id,department_name,city from employees empp left join (select d.department_name,l.city,department_id from locations l right join departments d on l.location_id= d.location_id) locdept on locdept.department_id =empp.department_id where commission_pct is not null;
--12. 显示Executive 部门有哪些职位。
select empp.job_id from employees empp right join departments d on empp.department_id=d.department_id where d.department_name ='Executive';

--13. 整个公司中，最高工资和最低工资相差多少。
select max(salary)-min(salary) from employees;

--14. 提成大于0 的人数。
select count(*) num from employees where commission_pct>0;

--15. 显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。
select round(max(salary)),round(min(salary)),round(avg(salary)),round(sum(salary) ) from employees;

--16. 整个公司有多少个领导。
select count(distinct manager_id) cmn from employees ;

--17. 列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日 期。
--蒙圈，
select first_name|| ' ' ||last_name ename,salary,hire_date from employees empp left join (select department_id,max(hire_date),salary maxsal from employees group by department_id) maxsal on empp.department_id = maxsal.department_id where empp.hire_date>=maxsal.hire_date and empp.salary>=maxsal.;
--某答案
select distinct e1.first_name||' '||e1.last_name,e1.salary,e1.hire_date from employees e1 join employees e2 on e1.department_id=e2.department_id where e1.hire_date> e2.hire_date and e1.salary>e2.salary;