2
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 | Mukhtar | 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 |
(a) To display ENo, EName, Salary and corresponding DName of all the employees whose age is between 25 and 35 (both values inclusive).
(b) To display DName and corresponding EName from the tables DEPARTMENT and EMPLOYEE (Hint: HOD of DEPARTMENT table should be matched with ENo of EMPLOYEE table for getting the desired result).
(c) 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.
(a)
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 |
+-----+--------+--------+-----------+
(b)
SELECT D.DNAME, E.ENAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.ENO = D.HOD;+-----------+---------+
| DNAME | ENAME |
+-----------+---------+
| COMPUTERS | MONA |
| ECONOMICS | MUKHTAR |
| ENGLISH | SURYA |
+-----------+---------+
(c)
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 |
+---------+--------+-------+------------+