CBSE Class 12 Informatics Practices
Question 57 of 79
Database Query using SQL — Question 15
Back to all questions 15
Question 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';+-----------+-----------+-----------+
| MinSalary | MaxSalary | AvgSalary |
+-----------+-----------+-----------+
| 2985 | 2985 | 2985 |
+-----------+-----------+-----------+
(b)
SELECT COUNT(*) AS ClerkCount
FROM EMP
WHERE Designation = 'CLERK';+------------+
| ClerkCount |
+------------+
| 1 |
+------------+
(c)
SELECT Designation, EmpName, Sal, DOJ
FROM EMP
ORDER BY Designation;+-------------+-----------+------+------------+
| 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;+---------+
| 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;+--------+----------+
| 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;+-----------+-----------+
| DeptName | MaxSalary |
+-----------+-----------+
| PERSONNEL | 2985 |
| ACCOUNTS | 1600 |
+-----------+-----------+
(h)
SELECT EMP.EMPNAME, EMP.DESIGNATION, DEPT.DEPTNAME
FROM EMP, DEPT
WHERE EMP.DEPTID = DEPT.DEPTID;+-----------+-------------+-----------+
| 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';+---------+
| NUM_EMP |
+---------+
| 2 |
+---------+