Description
This week’s lab continues using the SELECT command and learning the interfaces for SQL
Developer and the introduction of multi-line functions.
Getting Started
Your submission will be a single text-based SQL file with appropriate header and commenting.
Please ensure your file runs when the entire file is executed in SQL Developer.
Create a new Worksheet in SQL Developer. Save the file as L04_ID#_LASTNAME.sql
Tasks
• Display the difference between the Average pay and Lowest pay in the company. Name
this result Real Amount. Format the output as currency with 2 decimal places.
• Display the department number and Highest, Lowest and Average pay per each
department. Name these results High, Low and Avg. Sort the output so that the
department with highest average salary is shown first. Format the output as currency
where appropriate.
• Display how many people work the same job in the same department. Name these
results Dept#, Job and How Many. Include only jobs that involve more than one person.
Sort the output so that jobs with the most people involved are shown first.
• For each job title display the job title and total amount paid each month for this type of
the job. Exclude titles AD_PRES and AD_VP and also include only jobs that require more
than $11,000. Sort the output so that top paid jobs are shown first.
• For each manager number display how many persons he / she supervises. Exclude
managers with numbers 100, 101 and 102 and also include only those managers that
supervise more than 2 persons. Sort the output so that manager numbers with the
most supervised persons are shown first.
• For each department show the latest and earliest hire date, BUT
– exclude departments 10 and 20
– exclude those departments where the last person was hired in this decade. (it is okay
to hard code dates in this question only)
– Sort the output so that the most recent, meaning latest hire dates, are shown first.
Example Submission
— ***********************
— Name: Your Name
— ID: #########
— Date: The current date
— Purpose: Lab 4 DBS301
— ***********************
— Question 1 – write a brief note about what the
question is asking
— Q1 SOLUTION —
SELECT * FROM TABLE;
— Question 2 – blah blah blah