Group SELECT Command Questions

1.    Display the maximum, minimum and average salary and commission earned.

SELECT max(sal), min(sal), avg(sal), max(comm), min(comm), avg(comm) FROM emp;

2.    Display the department number, total salary payout and total commission payout for each
department.

SELECT deptno, sum(sal), sum(comm) FROM emp GROUP BY deptno;

3.    Display the department number, total salary payout and total commission payout for each
Department that pays at least one employee commission.

SELECT deptno,sum(sal),sum(comm) FROM emp GROUP BY deptno HAVING sum(comm)> 0;

4.    Display the department number and number of clerks in each department.

SELECT deptno, count(job) FROM emp WHERE job = 'CLERK' GROUP BY deptno;

5.    Display the department number and total salary of employees in each department that employs four or more people.

SELECT deptno, sum(sal) FROM emp GROUP BY deptno HAVING count(empno) >= 4;

6.    Display the employee number of each employee who manages other employees with the number of people he or she manages.

SELECT mgr, count(mgr) FROM emp WHERE mgr IS NOT NULL GROUP BY mgr;


I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: