SQL练习(三)

--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,to_char(salary,'L99,999.99'),to_char(salary+salary*0.08,'L99,999.99') from employees;
SELECT FIRST_NAME,SALARY,ROUND(SALARY * 1.08) FROM EMPLOYEES WHERE ROWNUM <=5; 
--3. 找出谁是最高领导，将名字按大写形式显示。
select upper(first_name||' '||last_name) from employees where manager_id is null;
--4. 找出First_Name 为David，Last_Name为Austin 的直接领导名字。
select (select first_name from employees e2 where e1.manager_id=e2.employee_id) from employees e1 where first_name='Kevin' and last_name='Mourgos';
SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME FROM EMPLOYEES  
WHERE EMPLOYEE_ID IN(  SELECT MANAGER_ID FROM EMPLOYEES  WHERE FIRST_NAME = 'Steven' AND LAST_NAME = 'King');  
SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME FROM EMPLOYEES  
WHERE EMPLOYEE_ID IN(  SELECT MANAGER_ID FROM EMPLOYEES  WHERE FIRST_NAME = 'Kevin' or LAST_NAME = 'Mourgos'); 
--5. First_Name 为Alexander，Last_Name为Hunold领导谁。（谁向David 报告）。
select first_name from employees where employee_id in(select manager_id from employees where first_name ='Alexander' and last_name='Hunold');
select first_name from employees where manager_id in(select employee_id from employees where first_name='Steven');
--6. 哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。
select e2.first_name,e2.salary,e1.first_name,e1.salary from employees e1,employees e2 where e2.manager_id=e1.employee_id and e2.salary>e1.salary;
--7. 哪些员工和Chen(LAST_NAME)同部门。
select first_name from employees e where e.job_id = (select job_id from employees where last_name='King'); 
select last_name from employees;
SELECT FIRST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'King') AND LAST_NAME <> 'King'; 
SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2 WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID AND E2.LAST_NAME = 'King' AND E1.LAST_NAME <> 'King';   
--8. 哪些员工跟De Haan(LAST_NAME)做一样职位。
select (first_name||' '||last_name) from employees e where e.job_id=(select job_id from employees where last_name ='De Haan') and e.last_name!='De Haan';
select e1.first_name from employees e1,employees e2 where e1.job_id=e2.job_id and e2.last_name='De Haan' and e1.last_name<>'De Haan';
--9. 哪些员工跟Hall(LAST_NAME)不在同一个部门。
select * from employees where department_id not in (select department_id from employees where last_name='Rajs');
--10. 哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。
select * from employees e where e.job_id not in ((select job_id from employees where first_name='William'),(select job_id from employees where last_name='Ernst')) and e.first_name!='William'and e.last_name<>'Ernst';
SELECT FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES WHERE JOB_ID <> (SELECT DISTINCT JOB_ID FROM EMPLOYEES WHERE FIRST_NAME = 'William' AND LAST_NAME = 'Ernst'); 
--11. 显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。
select * from employees;
select * from employees e where e.commission_pct is not null or e.commission_pct > 0;
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME,E.COMMISSION_PCT,D.DEPARTMENT_NAME,L.CITY  FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L  
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID  AND D.LOCATION_ID = L.LOCATION_ID  AND E.COMMISSION_PCT IS NOT NULL;  
--12. 显示Executive部门有哪些职位。
select * from departments;
select distinct job_id from employees where employees.department_id =(select department_id from departments where departments.department_name='Executive'); 
--13. 整个公司中，最高工资和最低工资相差多少。
select max(salary+salary*nvl(employees.commission_pct,0))-min(salary+salary*nvl(employees.commission_pct,0)) "最高工资和最低工资差" from employees;
SELECT MAX(SALARY) - MIN(SALARY) FROM EMPLOYEES;
--14. 提成大于0 的人数。
select count(e.commission_pct) from employees e where nvl(e.commission_pct,0) > 0;
--15. 显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。
select max(salary+salary*nvl(employees.commission_pct,0)) as "最高工资",min(salary+salary*nvl(employees.commission_pct,0)) as "最低工资",
sum(salary+salary*nvl(employees.commission_pct,0)) as"工资总和",round(avg(salary+salary*nvl(employees.commission_pct,0))) as"平均工资" from employees;
SELECT MAX(NVL(SALARY,0)) AS 最高工资,MIN(NVL(SALARY,0)) AS 最低工资,SUM(NVL(SALARY,0)) AS 工资总和,ROUND(AVG(NVL(SALARY,0))) AS 平均工资 FROM EMPLOYEES;  
--16. 整个公司有多少个领导。
select count(distinct e.manager_id) from employees e where e.manager_id is not null;
SELECT COUNT(DISTINCT(MANAGER_ID))  FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL;
--17. 列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期。
select distinct e1.first_name,e1.salary,e1.hire_date,e2.hire_date from employees e1,employees e2 where e1.hire_date>e2.hire_date and e2.salary <e1.salary;