SQL-3 TWO TABLE PROBLEMS

  Consider the following Dept and Employee tables.  Write SQL queries for (1) to (4) and find outputs for SQL queries (5) to (8).

Table: Dept

DCODE	DEPARTMENT	LOCATION
D01	INFRASTRUCTURE	DELHI
DO2	MARKETTING	DELHI
D03	MEDIA	MUMBAI
D05	FINANCE	KOLKATA
D04	HUMAN RESOURCE	MUMBAI

Table: Employee

ENO	NAME	DOJ	DOB	GENDER	DCODE
1001	GEORORGE K	01/09/2013	01/09/1991	MALE	D01
1002	RYMA SEN	11/12/2012	15/12/1990	FEMALE	D03
1003	MOHITESH	03/02/2013	04/09/987	MALE	D05
1007	ANILA JHA	17/01/2014	19/10/984	MALE	D04
1004	MANILA SAHAI	09/12/2012	14/11/1986	FEMALE	D01
1005	R SAHAY	18/11/2013	31/03/1987	MALE	D02
1006	JAYA PRIYA	09/06/2014	23/06/985	FEMALE	D05

CREATE TABLE DEPT1
( 

DCODE CHAR(3),
DEPARTMENT CHAR(15),
LOCATION CHAR(8)

);

INSERT INTO DEPT1 VALUES('D01','INFRASTRUCTURE','DELHI');
INSERT INTO DEPT1 VALUES('D02','MARKETING','DELHI');
INSERT INTO DEPT1 VALUES('D03','MEDIA','MUMBAI');
INSERT INTO DEPT1 VALUES('D05','FINANACE','KOLKATA');
INSERT INTO DEPT1 VALUES('D04','HUMAN RESOURCE','MUMBAI');

CREATE TABLE EMPLOYEE1
(

ENO CHAR(4),
ENAME CHAR(15),
DOJ DATE,
DOB DATE,
GENDER CHAR(6),
DCODE CHAR(3)

);

INSERT INTO EMPLOYEE1 VALUES('1001','GEORORGE','01/09/13','01/09/91','MALE','D01');

INSERT INTO EMPLOYEE1 VALUES('1002','RYMA SEN','11/12/12','15/12/90','FEMALE','D03');

INSERT INTO EMPLOYEE1 VALUES('1003','MOHITESH','03/02/13','04/09/87','MALE','D05');

INSERT INTO EMPLOYEE1 VALUES('1007','ANILA JHA','17/01/14','19/10/84','MALE','D04');

INSERT INTO EMPLOYEE1 VALUES('1004','MANILA SAHAI' ,'09/12/12', '14/11/86', 'FEMALE', 'D01');

INSERT INTO EMPLOYEE1 VALUES('1005','R SAHAY','18/11/13','13/03/87','MALE','D02');

INSERT INTO EMPLOYEE1 VALUES('1006','JAYA PRIYA','09/06/14','23/06/85', 'FEMALE','D05');

Q01 - To display Eno, Name, Gender from the table EMPLOYEE in ascending of Eno.
Ans: SELECT ENO,ENAME,GENDER FROM EMPLOYEE1 ORDER BY ENO;

Q02 - To display the Name of all the MALE emloyees from the table EMPLOYEE.
Ans: SELECT NAME FROM EMPLOYEE WHERE GENDER='MALE';

Q03 - To display the Eno and Name of those employees from the table EMPLOYEE   who are born between  '01-JAN-87' and '01-DEC-91'.
Ans: SELECT ENO,ENAME FROM EMPLOYEE1 WHERE DOB BETWEEN '01/01/87' AND '01/12/91';

Q04 - To  count and display FEMALE employees who have joined after '01-JAN-86'.
Ans: SELECT COUNT(*) FROM EMPLOYEE1 WHERE GENDER='FEMALE' AND DOJ>'01-JAN-86';


Q05 - SELECT COUNT(*), DCODE FROM EMPLOYEE GROUP BY DCODE HAVING COUNT(*)>1;
Ans: COUNT      DCODE
           2                  D01
           2                  D05

Q06 - SELECT DISTINCT DEPARTMENT FROM DEPT;
Ans: Department
         INFRASTRUCTURE
         MARKETTING
         MEDIA
         FINANCE
        HUMAN RESOURCE

Q07 - SELECT NAME, DEPARTMENT FROM EMPLOYEE E  , DEPT D WHERE E.DCODE=D AND ENO<1003;
Ans:  NAME             DEPARTMENT
          George K         INFRASTRUCTURE
          Ryma  Sen        MEDIA

Q08 - SELECT MAX(DOJ), MIN(DOB) FROM EMPLOYEE;
Ans: MAX(DOJ)              MIN(DOB)
        09/01/14                19/10/84

Outside Delhi (2015)