$|$SQL Opportunity No$.4$

This opportunity will give you a chance to work with the EMP and DEPT tables that we have begun talking about in class. These tables can be created by downloading the file opportunity$4$tables.sq$1$ attached to this opportunity. As was the case with SQL Opportunity No$.1,$ before making a copy of this file, please make sure it is copied to a folder with a name that contains no embedded blanks. Then copy contents of this file to that folder. Once you have executed this file using the start command given below, the tables necessary to complete this exercise will exist in your database permanently $($i$.$e$.,$ for as long as Oracle $19$c is installed on your machine$)$ and can begin working on SQL Opportunity No$.4.$

Once in SQL$*$Plus$,$ at the SQL$>$ prompt, enter the command

SQL$>$start opportunity$4$tables.sq$1$

When you issue the above start command, please remember to precede the file name with name of the folder or folders in which it is stored.

$1.$ Display the name, job and salary of all employees who work for a department located in New York in ascending order by salary. Note that EMP table does not know that department number $10$ is located in New York.

ENAME

$2.$ For each department, including department number $40,$ display the number of employees working in the department along with their average salary. Include the department number, number of employees, and average salary for each department.

DEPTNO DNAME Number of Employees Average Salary

$3.$ Write a query that displays for each department in the DEPT table the number of employees, the average salary of the employes, the average commission of employees and the average of the difference between each employees salary and commission.

DEPTNO DNAME No Employees Average Sal Average Comm Average Diff

$10$ Accounting

$20$ Research

$30$ Sales

$40$ Operations

$4$ rows selected.