oracle自带表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

L：表示强制显示当地货币符号
$: 表示显示美元符号
9: 表示一个数字
0: 表示强制0显示

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

2. 找出谁是最高领导，将名字按大写形式显示。
   SELECT UPPER(FIRST_NAME||’ ‘||LAST_NAME)
   FROM EMPLOYEES
   WHERE MANAGER_ID IS NULL

3. 找出First_Name 为David，Last_Name为Austin 的直接领导名字。
   SELECT FIRST_NAME||’ ‘||LAST_NAME
   FROM EMPLOYEES
   WHERE EMPLOYEE_ID IN (SELECT MANAGER_ID FROM EMPLOYEES WHERE FIRST_NAME=’David’ AND LAST_NAME=’Austin’)

4. First_Name 为Alexander，Last_Name为Hunold领导谁。（谁向David 报告）。
   SELECT FIRST_NAME||’ ‘||LAST_NAME AS NAME
   FROM EMPLOYEES
   WHERE MANAGER_ID IN(SELECT EMPLOYEE__ID FROM EMPLOYEES
   WHERE FIRST_NAME=’Alexander’ AND LAST_NAME=’Hunold’)

5. 哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。
   SELECT E.FIRST_NAME, E.SALARY
   FROM EMPLOYEES E WHERE E.SALARY>
   (SELECT M.SALARY FROM EMPLOYEES M
   WHERE E.MANAGER_ID=M.EMPLOYEE_ID)

6. 哪些员工和Chen(LAST_NAME)同部门。
   SELECT FIRST_NAME FROM EMPLOYEE
   WHERE DEPARTMENT_ID IN
   (SELECT DEPARTMENT_ID FROM EMPLOYEES
   WHERE LAST_NAME=’Chen’) AND LAST_NAME<>’Chen’

7. 哪些员工跟De Haan(LAST_NAME)做一样职位。
   SELECT FIRST_NAME||’ ‘||LAST_NAME
   FROM EMPLOYEES
   WHERE JOB_ID IN (SELECT JOB_ID
   FROM EMPLOYEE
   WHERE LAST_NAME=’Haan’) AND LAST_NAME<>’De Haan’

8. 哪些员工跟Hall(LAST_NAME)不在同一个部门。
   SELECT FIRST_NAME||’ ‘||LAST_NAME
   FROM EMPLOYEES
   WHERE DEPARTMENT_ID NOT IN(SELECT DEPARTMENT_ID
   FROM EMPLOYEES WHERE LAST_NAME=’Hall’)

9. 哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。
   SELECT FIRST_NAME||’ ‘||LAST_NAME
   FROM EMPLOYEES WHERE JOB_ID<>(SELECT DISTINCT JOB_ID
   FROM EMPLOYEES
   WHERE FIRST_NAME=’William’ AND LAST_NAME=’Smith’)

10. 显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。
    SELECT E.FIRST_NAME||’ ‘||E.LAST_NAME AS NAME,
    E.COMMISSION_PCT, D.DEPARTMENT_NAME, L.CITY
    FROM EMPLOYEES E, DEPARTMENT D, LOCATIONS L
    WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
    AND D.LOCATION_ID=L.LOCATION_ID
    AND E.COMMISSION_PCT IS NOT NULL

11. 显示Executive部门有哪些职位。
    SELECT DISTINCT E.JOB_ID FROM EMPLOYEES E, DEPARTMENTS D
    WHERE D.DEPARTMENT_ID=E.DEPARTMENT_ID
    AND D.DEPARTMENT_NAME=’Executive’

12. 整个公司中，最高工资和最低工资相差多少。
    SELECT MAX(SALARY)-MIN(SALARY) AS DIFFERENCE
    FROM EMPLOYEES

13. 提成大于0 的人数。
    SELECT COUNT(*) AS TAR FROM EMPLOYEES
    WHERE COMMISSION_PCT>0

14. 显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。
    SELECT MAX(NVL(SALARY, 0)) AS MAX_SAL,
    MIN(NVL(SALARY, 0)) AS MIN_SAL,
    SUM(NVL(SALARY, 0)) AS SUM_SAL,
    ROUND(AVG(NVL(SALARY,0))) AS AVG_SAL
    FROM EMPLOYEES

15. 整个公司有多少个领导。
    SELECT COUNT(DISTINCT(MANAGER_ID)) FROM EMPLOYEES
    WHERE MANAGER_ID IS NOT NULL

16. 列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期。
    SELECT DISTINCT E1.FIRST_NAME || ’ ’ || E1.LAST_NAME AS NAME, E1.SALARY, E1.HIRE_DATE
    FROM EMPLOYEES E1, EMPLOYEES E2
    WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
    AND E1.HIRE_DATE>E2.HIRE_DATE
    AND E1.SALARY>E2.SALARY
    ORDER BY E1.SALARY DESC