CIS 411w spring 2017 problem set 7 Due Date 322017 1 Create

CIS 411w spring 2017 problem set 7                                                                              Due Date: 3/2/2017

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 per week, so you will have to multiply that by 52 and add the commission. 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.

Solution

1. SELECT deptno\"Department Code\",

COUNT(*) \"No of Employees\"

2. select decode

(

\"deptno\",\"deptname\"

)

from department;

Then again repeat the query given in ans 1 i.e

SELECT deptname\"Department Name\",

COUNT(*) \"No of Employees\"

3.  

4. select sal,comm,(sal+nvl(comm,0)) as \"Total_sal\" from emp group by job;