Description

Lab 2 – Week 2 (SELECT, RANGE, ORDER)
This week’s lab continues using the SELECT command and learning the interfaces for both SQL Developer.
Getting Started
Your submission will be a single text-based SQL file with the solutions provided in addition to HTML OR PDF files for questions 7-9.
Create a new Worksheet in SQL Developer. Save the file as L02_ID_LASTNAME.sql
Your submission needs to be commented and include the question, the solutions, and the results. An example is provided!
Notes:
• For this lab, all fields that output monetary values, should be shown as money (use: to_char(<fieldname>, ‘$999,999.99’) function to accomplish this, also note this changes the field from numeric or decimal to a string and therefore reacts differently to sorting requests)
1. Display the employee_id, last name and salary of employees earning in the range of $8,000 to $1 ,000. Sort the output by top salaries first and then by last name.

2. Modify previous query (#1) so that additional condition is to display only if they work as Programmers or Sales Representatives. Use same sorting as before.

3. The Human Resources department wants to find high salary and low salary employees. Modify previous query (#2) so that it displays the same job titles but for people who earn outside the given salary range from question 1. Use same sorting as before.

5. Modify previous query (#4) so that it displays only employees earning more than $11,000. List the output by job title alphabetically and then by highest paid employees.

DBS301 – Database Design II and SQL using Oracle

6. Display the job titles and full names of employees whose first name contains an ‘e’ or ‘E’ anywhere. The output should look like: (BONUS MARK FOR NOT using the OR keyword in the solution but obtaining the same results)

Job Title Full name ————————————– AD_VP Neena Kochhar
… more rows

7. Create a query to display the address of the various locations where offices are located. Add a parameter to the query such that the user can enter all, or part of, the city name and all locations from the resultant cities will be shown.

Example Submission
— ***********************
— Name: Your Name
— ID: #########
— Purpose: Lab 2 DBS301
— ***********************

— Question 1 – write a brief note about what the question is asking

SELECT * FROM TABLE;

— Question – blah blah blah