CBSE Class 12 Informatics Practices
Question 72 of 91
JOINS and SET Operations — Question 8
Back to all questions 8
Question In a Database BANK there are two tables with a sample data given below :
Table : EMPLOYEE
| ENO | ENAME | SALARY | ZONE | AGE | GRADE | DEPT |
|---|---|---|---|---|---|---|
| 1 | Mona | 70000 | East | 40 | A | 10 |
| 2 | Muktar | 71000 | West | 45 | B | 20 |
| 3 | Nalini | 60000 | East | 26 | A | 10 |
| 4 | Sanaj | 65000 | South | 36 | A | 20 |
| 5 | Surya | 58000 | North | 30 | B | 30 |
Table : DEPARTMENT
| DEPT | DNAME | HOD |
|---|---|---|
| 10 | Computers | 1 |
| 20 | Economics | 2 |
| 30 | English | 5 |
Note.
- ENAME refers to Employee Name
- DNAME refers to Department Name
- DEPT refers to Department Code
- HOD refers to Employee number (ENO) of the Head of the Dept.
Write SQL queries for the following :
(i) To display ENO, ENAME, SALARY and corresponding DNAME of all the employees whose age is between 25 and 35 (both values inclusive).
(ii) To display DNAME and corresponding ENAME from the tables DEPARTMENT and EMPLOYEE.
Hint. HOD of the DEPARTMENT table should be matched with ENO of the EMPLOYEE table for getting the desired result.
(iii) To display ENAME, SALARY, ZONE and INCOME TAX (Note. Income Tax to be calculated as 30% of salary) of all the employees with appropriate column headings.
(i)
SELECT E.ENO, E.ENAME, E.SALARY, D.DNAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT = D.DEPT AND (E.AGE BETWEEN 25 AND 35);+-----+--------+--------+-----------+
| ENO | ENAME | SALARY | DNAME |
+-----+--------+--------+-----------+
| 3 | NALINI | 60000 | COMPUTERS |
| 5 | SURYA | 58000 | ENGLISH |
+-----+--------+--------+-----------+
(ii)
SELECT D.DNAME, E.ENAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.ENO = D.HOD;+-----------+---------+
| DNAME | ENAME |
+-----------+---------+
| COMPUTERS | MONA |
| ECONOMICS | MUKHTAR |
| ENGLISH | SURYA |
+-----------+---------+
(iii)
SELECT ENAME, SALARY, ZONE, (SALARY * 30/100) AS INCOME_TAX
FROM EMPLOYEE;+---------+--------+-------+------------+
| ENAME | SALARY | ZONE | INCOME_TAX |
+---------+--------+-------+------------+
| MONA | 70000 | EAST | 21000.0000 |
| MUKHTAR | 71000 | WEST | 21300.0000 |
| NALINI | 60000 | EAST | 18000.0000 |
| SANAJ | 65000 | SOUTH | 19500.0000 |
| SURYA | 58000 | NORTH | 17400.0000 |
+---------+--------+-------+------------+