(Multi-Line Functions)
This lab continues using the SELECT command and learning the interfaces for SQL Developer and the introduction of multi-line functions.
Getting Started
Create a new Worksheet in SQL Developer. Save the file as L04_ID#_LASTNAME.sql
Tasks
1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
