Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 63 of 79

Database Query using SQL — Question 21

Back to all questions
21
Question

Question 21

Consider the following tables WORKER and PAYLEVEL and answer the questions:

Table: WORKER

ECODENAMEDESIGPLEVELDOJDOB
11Sachin PatelSupervisorP0012004-09-131985-08-23
12Chander NathOperatorP0032010-02-221987-07-12
13FizzaOperatorP0032009-06-141983-10-14
15Ameen AhmedMechanicP0022006-08-211984-03-13
18SanyaClerkP0022005-12-191983-06-09

Table: PAYLEVEL

PLEVELPAYALLOWANCE
P0012600012000
P0022200010000
P003120006000

(a) To display details of all workers in descending order of DOB.

(b) To display the PLEVEL and number of workers in that PLEVEL.

(c) To display the PLEVEL and number of workers in that PLEVEL whose pay is greater than 15000.

(d) To display NAME and DESIG of those workers, whose PLEVEL is either P001 or P002.

(e) Give the output of the following SQL queries:

  1. SELECT COUNT(PLEVEL), PLEVEL FROM WORKER GROUP BY PLEVEL;
  2. SELECT MAX(DOB), MIN(DOJ) FROM WORKER;
Answer

(a)

SELECT * 
FROM WORKER 
ORDER BY DOB DESC;
Output
+-------+--------------+------------+--------+------------+------------+
| ECODE | NAME         | DESIG      | PLEVEL | DOJ        | DOB        |
+-------+--------------+------------+--------+------------+------------+
|    12 | CHANDER NATH | OPERATOR   | P003   | 2010-02-22 | 1987-07-12 |
|    11 | SACHIN PATEL | SUPERVISOR | P001   | 2004-09-13 | 1985-08-23 |
|    15 | AMEEN AHMED  | MECHANIC   | P002   | 2006-08-21 | 1984-03-13 |
|    13 | FIZZA        | OPERATOR   | P003   | 2009-06-14 | 1983-10-14 |
|    18 | SANYA        | CLERK      | P002   | 2005-12-19 | 1983-06-09 |
+-------+--------------+------------+--------+------------+------------+

(b)

SELECT PLEVEL, COUNT(*) 
FROM WORKER 
GROUP BY PLEVEL;
Output
+--------+----------+
| PLEVEL | COUNT(*) |
+--------+----------+
| P001   |        1 |
| P003   |        2 |
| P002   |        2 |
+--------+----------+

(c)

SELECT PL.PLEVEL, COUNT(*)   
FROM WORKER W, PAYLEVEL PL  
WHERE W.PLEVEL = PL.PLEVEL AND PL.PAY > 15000   
GROUP BY PL.PLEVEL;
Output
+--------+----------+
| PLEVEL | COUNT(*) |
+--------+----------+
| P001   |        1 |
| P002   |        2 |
+--------+----------+

(d)

SELECT NAME, DESIG 
FROM WORKER 
WHERE PLEVEL IN ('P001', 'P002');
Output
+--------------+------------+
| NAME         | DESIG      |
+--------------+------------+
| SACHIN PATEL | SUPERVISOR |
| AMEEN AHMED  | MECHANIC   |
| SANYA        | CLERK      |
+--------------+------------+

(e)

1. SELECT COUNT(PLEVEL), PLEVEL FROM WORKER GROUP BY PLEVEL;

Output
+---------------+--------+
| COUNT(PLEVEL) | PLEVEL |
+---------------+--------+
|             1 | P001   |
|             2 | P003   |
|             2 | P002   |
+---------------+--------+

2. SELECT MAX(DOB), MIN(DOJ) FROM WORKER;

Output
+------------+------------+
| MAX(DOB)   | MIN(DOJ)   |
+------------+------------+
| 1987-07-12 | 2004-09-13 |
+------------+------------+