CBSE Class 12 Computer Science
Question 66 of 91
Relational Database and SQL — Question 27
Back to all questions 27
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 having salary > 2000.
(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.DeptName, AVG(E.Sal) AS AvgSalary
FROM EMP E, DEPT D
WHERE E.DeptID = D.DeptID AND E.Sal > 2000
GROUP BY D.DeptName;+-----------+-----------+
| DeptName | AvgSalary |
+-----------+-----------+
| PERSONNEL | 2985 |
+-----------+-----------+
(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 |
+---------+