Description
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.
