PS7

1. Create a SQL query that will use the count function to return the number of employees in each department in the emp table. Include the deptno column in your SQL query. In this example you will be grouping by deptno.

Copy and paste your code into your assignment and include a screenshot of your result set in your assignment.

2. Modify the previous SQL query to use the single-row function DECODE() to change the deptno into the name of the department. You will have to look at the dept table to get the name of each department given the deptno.

Copy and paste your code into your assignment and include a screenshot of your result set in your assignment.

3. Modify the previous query to show the departments that have only 4 or more employees. So now you will be using the count(*) function in both the ordered list of columns and conditional clause.

Copy and paste your code into your assignment and include a screenshot of your result set in your assignment.

4. Create a SQL query that will give the total amount of salary and commission paid out for each job for one year, GROUP BY job. Assume that the sal column means salary paid every two weeks, so you will have to multiply sal by 26 and add the commission, but the commission is paid for 1 month. Remember many employees have NULL in their commission field so you will have to use the NVL() single row function or the SUM() function will not work correctly.

Copy and paste your code into your assignment and include a screenshot of your result set in your assignment.

5. Modify the previous SQL query to show only those jobs that paid out more than $150,000 in salary and commission last year. In the having clause you can use the SQL code that was used to find the total sum of salary and commission.

Copy and paste your code into your assignment and include a screenshot of your result set in your assignment.