This are the names that you will be using. DEPTNO, DNAME, LOC, EMPNO,ENAME,JOB,M

Question

This are the names that you will be using. DEPTNO, DNAME, LOC, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL

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.

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.

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

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.

5. Modify the previous SQL query to show only those jobs that paid out more than $150,000 in total 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.

Explanation / Answer

1) SELECT deptno., count(empid) FROM emp GROUPBY deptno.

selecting department no. and count of employee in that department when we group them on the basis of department no. as above.

2)SELECT DECODE(deptno., deptname), count(empid) FROM emp GROUPBY deptno.

same as above only difference is we are using DECODE function to decode deptno. to its department name in the output

3)SELECT deptno., count(empid) FROM emp GROUPBY deptno. HAVING count(*) >= 3

same as above with one more clause having employee count more than or equal to 3 in each department,

4)SELECT sum(sal)*26 + sum(NVL(commissionpaid, 0)) FROM emp GROUPBY job HAVING date >='01/01/2016' AND date <='31/12/2016'

in above we are computing total sum as  sum(sal)*26 + sum(NVL(commissionpaid, 0)) this will first sum salary and multipy it to 26 as per requirement then we are adding this sum to commision paid as it can be null so we are using NVL function to make NULL value to 0 so that it will not fail.

5)SELECT sum(sal)*26 + sum(NVL(commissionpaid, 0)) FROM emp GROUPBY job HAVING date >='01/01/2016' AND date <='31/12/2016' AND (sum(sal)*26 + sum(NVL(commissionpaid, 0))) > 150000

same as above just one more clause in having to check the total sum greater than 150000

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.