11 create a query that displays the employees last

This preview shows page 3 - 4 out of 4 pages.

11. Create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, show “No Commission.” Label the column COMM .
12. Create a query that displays the first eight characters of the employees’ last names and indicates the amounts of their salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Label the column EMPLOYEES_AND_THEIR_SALARIES . SELECT rpad(last_name, 8)||' '|| rpad(' ', salary/1000+1, '*') EMPLOYEES_AND_THEIR_SALARIES FROM employees ORDER BY salary DESC;
Oracle Database 10 g : SQL Fundamentals I A - 11 Practice 3: Solutions (continued) 13. Using the DECODE function, write a query that displays the grade of all employees based on the value of the column JOB_ID , using the following data: Job Grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E None of the above 0 SELECT job_id, decode (job_id, 'ST_CLERK', 'E', 'SA_REP', 'D', 'IT_PROG', 'C', 'ST_MAN', 'B', 'AD_PRES', 'A', '0')GRADE FROM employees; 14. Rewrite the statement in the preceding exercise using the CASE syntax. SELECT job_id, CASE job_id WHEN 'ST_CLERK' THEN 'E' WHEN 'SA_REP' THEN 'D' WHEN 'IT_PROG' THEN 'C' WHEN 'ST_MAN' THEN 'B' WHEN 'AD_PRES' THEN 'A' ELSE '0' END GRADE FROM employees;

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture