Description
Objective: Create SELECT statements using complex conditions and expressions
Problem 1:
Create a query displaying the last name, first name and hire date from the employees table. Show only those employees
that were hired on 6/7/94, 8/20/97, and 3/23/98. Sort the resulting set by hire_date in descending order and last_name
in ascending order.
Problem 2:
Display all stock managers (Last and first name and phone number) (job_id = ‘ST_MAN’) in the phone area code of 650
from the employees table. Order by last_name.
Problem 3:
In addition to the result set of the query in problem 2, the HR manager needs to see also all employees that earn more
than $10,000. Display the salary column in addition to last, first, and phone number. (Instructions: Take the query from
problem 2 and add the additional filter and column from problem 2.).
Problem 4:
Create a query showing the last and first names of employees. Order it by last name. Translate the telephone area codes
into the following values and alias this expression with Area:
➢ 011 -> International
➢ 650 -> SF Peninsula
➢ 515 -> Central Iowa
➢ all other -> Unknown
Problem 5:
Create a query showing the employee_id, last_name, and phone number. Show only those records where the last_name
starts with the letters A,D, F, H, K. Implement the following custom sort order on the area code of the phone number
(first 3 numbers): 011, 650, 590, 603, 515 (meaning 011 first, then 650, etc.). In addition to this custom sort, also sort by
last name.
Problem 6:
Create a query showing employee_id, department_id, start and end date from the job history table together with
employee_id, department_id, hire date and the fixed string ‘Current’ from the employees table. Order it by
employee_id, department_id.