2. Display each employees last name, hire date, and salary review date, which
is the first
Monday after six months of service. Label the column REVIEW. Format the dates to
appear in the format similar to Monday, the Thirty-First of July, 2000.
3. Produce a list of jobs for departments 10, 50, and 20, in that order. Display
job ID and
department ID using the set operators.
SQL> SELECT distinct job_id, department_id FROM employees WHERE department_id =
10 UNION ALL SELECT DISTINCT job_id, d
5. The HR department needs a report with the following specifications:
Last name and department ID of all the employees from the EMPLOYEES table,
regardless of whether or not they belong to a department
Department ID and department name of all the depa
2. The HR department needs a list of countries that have no departments located
Display the country ID and the name of the countries. Use the set operators to
SQL> SELECT country_id,country_name FROM countries MINUS SELE
1. The HR department needs a list of department IDs for departments that do not
contain the job
ID ST_CLERK. Use the set operators to create this report.
SQL> SELECT department_id FROM departments MINUS SELECT department_id FROM
employees WHERE job_
4. 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 as
SQL> SELECT last_name, NVL(TO_CHAR(commission_pct), 'No Commission') as "COMM"
6. Rewrite the statement in the preceding exercise using the CASE syntax.
SQL> SELECT 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
as "Grade" FROM emp
5. Using the DECODE function, write a query that displays the grade of all
on the value of the JOB_ID column, using the following data: Job Grade
ST_CLERK E None
of the above 0
SQL> SELECT DECODE
3. Display the last name, hire date, and day of the week on which the employee
the column as DAY. Order the results by the day of the week, starting with
SQL> SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') as "DAY" FRO
4. Create a report that lists the employee IDs and job IDs of those employees
have a job title that is the same as their job title when they were initially
hired by the
company (that is, they changed jobs but have now gone back to doing t