Consider the following EMP and DEPT tables:

Table: EMP

EmpNo	EmpName	City	Designation	DOJ	Sal	Comm	DeptID
8369	SMITH	Mumbai	CLERK	1990-12-18	800.00	NULL	20
8499	ANYA	Varanasi	SALESMAN	1991-02-20	1600.00	300.00	30
8521	SETH	Jaipur	SALESMAN	1991-02-22	1250.00	500.00	30
8566	MAHADEVAN	Delhi	MANAGER	1991-04-02	2985.00	NULL	20

Table: DEPT

DeptID	DeptName	MgrID	Location
10	SALES	8566	Mumbai
20	PERSONNEL	9698	Delhi
30	ACCOUNTS	4578	Delhi
40	RESEARCH	8839	Bengaluru

Write the SQL command to get the following:

(a) Show the minimum, maximum and average salary of managers.

(b) Count the number of clerks in the organization.

(c) Display the designation-wise list of employees with name, salary and date of joining.

(d) Count the number of employees who are not getting commission.

(e) Show the average salary for all departments with more than 5 working people.

(f) List the count of employees grouped by DeptID.

(g) Display the maximum salary of employees in each department.

(h) Display the name of employees along with their designation and department name.

(i) Count the number of employees working in ACCOUNTS department.

(a)

SELECT MIN(Sal) AS MinSalary, MAX(Sal) AS MaxSalary, AVG(Sal) AS AvgSalary
FROM EMP
WHERE Designation = 'MANAGER';

Output

+-----------+-----------+-----------+
| MinSalary | MaxSalary | AvgSalary |
+-----------+-----------+-----------+
|      2985 |      2985 |      2985 |
+-----------+-----------+-----------+

(b)

SELECT COUNT(*) AS ClerkCount
FROM EMP
WHERE Designation = 'CLERK';

Output

+------------+
| ClerkCount |
+------------+
|          1 |
+------------+

(c)

SELECT Designation, EmpName, Sal, DOJ
FROM EMP
ORDER BY Designation;

Output

+-------------+-----------+------+------------+
| Designation | EmpName   | Sal  | DOJ        |
+-------------+-----------+------+------------+
| CLERK       | SMITH     |  800 | 1990-12-18 |
| MANAGER     | MAHADEVAN | 2985 | 1991-04-02 |
| SALESMAN    | ANYA      | 1600 | 1991-02-20 |
| SALESMAN    | SETH      | 1250 | 1991-02-22 |
+-------------+-----------+------+------------+

(d)

SELECT COUNT(*) AS No_comm
FROM EMP
WHERE comm is NULL;

Output

+---------+
| No_comm |
+---------+
|       2 |
+---------+

(e)

SELECT d.DeptID, d.DeptName, AVG(e.Sal) AS Avg_Salary
FROM DEPT d, EMP e
WHERE e.DeptID = d.DeptID AND d.DeptID IN (
  SELECT DeptID
  FROM EMP
  GROUP BY DeptID
  HAVING COUNT(*) > 5
)
GROUP BY d.DeptID, d.DeptName;

(f)

SELECT DeptID, COUNT(*) AS EmpCount
FROM EMP
GROUP BY DeptID;

Output

+--------+----------+
| DeptID | EmpCount |
+--------+----------+
|     20 |        2 |
|     30 |        2 |
+--------+----------+

(g)

SELECT D.DeptName, MAX(E.Sal) AS MaxSalary
FROM EMP E, DEPT D
WHERE E.DeptID = D.DeptID
GROUP BY D.DeptName;

Output

+-----------+-----------+
| DeptName  | MaxSalary |
+-----------+-----------+
| PERSONNEL |      2985 |
| ACCOUNTS  |      1600 |
+-----------+-----------+

(h)

SELECT EMP.EMPNAME, EMP.DESIGNATION, DEPT.DEPTNAME
FROM EMP, DEPT
WHERE EMP.DEPTID = DEPT.DEPTID;

Output

+-----------+-------------+-----------+
| EMPNAME   | DESIGNATION | DEPTNAME  |
+-----------+-------------+-----------+
| SMITH     | CLERK       | PERSONNEL |
| ANYA      | SALESMAN    | ACCOUNTS  |
| SETH      | SALESMAN    | ACCOUNTS  |
| MAHADEVAN | MANAGER     | PERSONNEL |
+-----------+-------------+-----------+

(i)

SELECT COUNT(*) AS NUM_EMP
FROM EMP, DEPT
WHERE EMP.DEPTID = DEPT.DEPTID
AND DEPTNAME = 'ACCOUNTS';

Output

+---------+
| NUM_EMP |
+---------+
|       2 |
+---------+