Consider the following DEPT and EMPLOYEE tables. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii).
Table: DEPT
| DCODE | DEPARTMENT | LOCATION |
| D01 | INFRASTRUCTURE | DELHI |
| D02 | MARKETING | DELHI |
| D03 | MEDIA | MUMBAI |
| D04 | FINANCE | KOLKATA |
| D05 | HUMAN RESOURCE | MUMBAI |
Table: EMPLOYEE
| ENO | NAME | DOJ | DOB | GENDER | DCODE |
| 1001 | George K | 2013-09-02 | 1991-09-01 | MALE | D01 |
| 1002 | Ryma Sen | 2012-12-11 | 1990-12-15 | FEMALE | D03 |
| 1003 | Mohitesh | 2013-02-03 | 1987-09--04 | MALE | D05 |
| 1007 | Anil Jha | 2014-01-17 | 1984-10-19 | MALE | D04 |
| 1004 | Manila Sahai | 2012-12-09 | 1986-11-14 | FEMALE | D01 |
| 1005 | R SAHAY | 2013-11-18 | 1987-03-31 | MALE | D02 |
| 1006 | Jaya Priya | 2014-06-09 | 1985-06-23 | FEMALE | D05 |
Note: DOJ refers to the date of joining ·and DOB refers to the date of Birth of employees
1) To display Eno, Name, Gender from the table EMPLOYEE in ascending order of Eno.
2) To display the Name of all the MALE Employees from the table EMPLOYEE.
3) To display the Eno and Name of those employees from the table EMPLOYEE who are born between '1987-01-01' and '1991-12-01'.
4) To count and display FEMALE employees who have Joined after '1986-01-01'.
5) SELECT COUNT(*), DCODE FROM EMPLOYEE GROUP BY DCODE HAVING COUNT(*)>1;
6) SELECT DISTINCT DEPARTMENT FROM DEPT;
7) SELECT NAME, DEPARTMENT FROM EMPLOYEE E, DEPT D WHERE E.DCODE=D.DCODE AND EN0<1003;
8) SELECT MAX(DOJ), MIN(DOB) FROM EMPLOYEE;
Solution
1) SELECT Eno, Name GENDER from table EMPLOYEE
2) SELECT NAME from EMPLOYEE where GENDER = ‘MALE’
3) SELECT Eno, NAME from EMPLOYEE where DOB BETWEEN ‘1987 - 01 - 01’ and ‘1991 - 12 - 01’
4) SELECT FEMALE from EMPLOYEE where DOJ > ‘1986 - 01 - 01’
5)
2, D01
2, D05
6)
| Department |
|
INFRASTRUCTURE MARKETING MEDIA FINANCE HUMAN RESOURCE |
7)
| NAME | DEPARTMENT |
|
George K Ryma Sen |
INFRASTRUCTURE MEDIA |
8) MAX (DOJ) = 2014 - 11 - 18
MIN (DOB) = 1984 - 10 - 19
