Starting from:
$25

$19

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.



More products