ORACLE_OCP之1Z0-071 SQL WORKSHOP 练习

ORACLE_OCP之1Z0-071 SQL WORKSHOP 练习

• 一下所有数据均来自HR用户的EMPLOYEES表和DEPARTMENTS表中数据！
• 没有填的希望广大读者们，在评论区附上题号和完美答案，期待您的参与哦！

一、显示1997年后聘用职员的所有数据

答：
SELECT * FROM EMPLOYEES
WHERE HIRE_DATE > ‘31-12月-1997’;

二、按工资升序排列数据，并且不计算没有提成COMMISSION_PCT那部分员工的数据

答：
SELECT LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY SALARY;

三、显示没有提成的员工，并且将这些员工的工资增加百分之十

答：
SELECT LAST_NAME, ROUND(SALARY*1.1) “NEW SALARY”
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NULL;

四、显示所有员工的姓氏以及他们被聘用的年数和完整的月份数

答：SELECT LAST_NAME, SYSDATE, HIRE_DATE, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)/12) YEARS, TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, HIRE_DATE), 12)) MONTHS
FROM EMPLOYEES;

五、显示姓名以J K L M开头的员工

答：SELECT LAST_NAME
FROM EMPLOYEES
WHERE SUBSTR(LAST_NAME, 1, 1) IN(‘J’, ‘K’, ‘L’, ‘M’);

六、显示所有员工，并且用YES或者NO来指明他们是否有提成

答：
SELECT LAST_NAME, SALARY, DECODE(COMMISSION_PCT, NULL, ‘No’, ‘Yes’) COMMISSION
FROM EMPLOYEES;

七、显示在地点1800号工作的员工的部门名称、地点、姓名、职称和佣金（用到了DEPARTMENTS表）

答：
SELECT D.DEPARTMENT_NAME, D.LOCATION_ID, E.LAST_NAME, E.JOB_ID, E.SALARY
FROM EMPLOYEES E, DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID AND D.LOCATION_ID = 1800;

八、显示员工姓名以n结尾的人数

答：
SELECT COUNT(*)
FROM EMPLOYEES
WHERE LAST_NAME LIKE ‘%n’;
SELECT COUNT(*)
FROM EMPLOYEES
WHERE SUBSTR(LAST_NAME, -1) = ‘n’;

九、显示所有部门的名称和地点，并显示每个部门中的工作人数，确保将没有员工的部门也包含其中（使用到了DEPARTMENTS表）

答：
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID, COUNT(E.EMPLOYEE_ID)
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID;
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID, COUNT(E.EMPLOYEE_ID)
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID;

十、10和20号部门都有哪些职务JOB_ID

答：
SELECT DISTINCT JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20);

十一、在管理部门和行政部门中都有哪些职务，有多少员工担任这些职务，显示频率最高的职务，单词注意：（频率：FREQUENCY，管理：Administration，行政：Executive）

答：
SELECT E.JOB_ID, COUNT(E.JOB_ID) FREQUENCY
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.DEPARTMENT_NAME IN (‘Administration’, ‘Executive’)
GROUP BY E.JOB_ID
ORDER BY FREQUENCY DESC;

十二、显示在前半月（本月的16号前）入职的所有员工

答：
SELECT LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, ‘DD’) < 16;

十三、显示员工的姓名、薪水以及每位员工所挣的美元数（工资以千进位，比如说24000显示24 thousands）

答：
SELECT LAST_NAME, SALARY, TRUNC(SALARY, -3)/1000 Thousands
FROM EMPLOYEES;

十四、显示平均工资最高的部门的部门编号和最低薪金（请使用子查询完成）

答：
SELECT DEPARTMENT_ID, MIN(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY) = (SELECT MAX(AVG(SALARY)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);

十五、显示没有销售代表的部门的部门编号、名称和地点（使用到了DEPARTMENTS表）

答：
SELECT *
FROM DEPARTMENTS
WHERE DEPARTMENT_ID
NOT IN
(SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE JOB_ID = ‘SA_REP’ AND DEPARTMENT_ID IS NOT NULL);

十六、显示分别符合以下情况的每个部门的部门编号、部门名称和员工人数（使用到了DEPARTMENTS表）：a.员工人数少于3人，b.员工人数最多，c.员工人数最少

答：
//a
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, COUNT()
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID, D.DEPARTMENT_NAME
HAVING COUNT() < 3;
//b
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, COUNT(*)
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID, D.DEPARTMENT_NAME
HAVING COUNT(*) = (SELECT MAX(COUNT()) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);
//c
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, COUNT(*)
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID, D.DEPARTMENT_NAME
HAVING COUNT(*) = (SELECT MIN(COUNT()) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);

十七、显示所有员工的员工编号、姓名、薪水、部门编号和所在部门的平均工资（使用到了DEPARTMENTS表）

答：
SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.DEPARTMENT_ID, AVG(S.SALARY)
FROM EMPLOYEES E, EMPLOYEES S
WHERE E.DEPARTMENT_ID = S.DEPARTMENT_ID
GROUP BY E.EMPLOYEE_ID, E.LAST_NAME, E.DEPARTMENT_ID;

十八、显示聘用人数最多的是一周的哪几天（使用星期法，栗子：星期一、星期二，这个方式实现），并显示当天聘用的所有员工的姓名（PS：结果只需要显示姓名和入职那天的星期即可）

答：
SELECT LAST_NAME, TO_CHAR(HIRE_DATE, ‘DAY’) DAY
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, ‘Day’) IN (
SELECT TO_CHAR(HIRE_DATE, ‘Day’)
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE, ‘Day’)
HAVING COUNT(*) = (
SELECT MAX(COUNT(*))
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE, ‘Day’)));

十九、根据员工的入职时间为他们编写一个入职周年纪念日，并按照月份升序周年的纪念日日期格式化类型请使用：Month DD这种模式

答：
SELECT LAST_NAME, TO_CHAR(HIRE_DATE, ‘Month DD’) BIRTHDAY
FROM EMPLOYEES
ORDER BY TO_CHAR(HIRE_DATE, ‘DDD’);

二十、列出EMPLOYEES表的表结构

答：
DESC EMPLOYEES