SQL-4 Outside Delhi (2015) 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-SEPT-2013
|
01-SEPT-1991
|
MALE
|
D01
|
1002
|
RYMA SEN
|
11-DEC-2012
|
15-DEC-1990
|
FEMALE
|
D03
|
1003
|
MOHITESH
|
03-FEB-2013
|
04-SEPT1987
|
MALE
|
D05
|
1007
|
ANILA JHA
|
17-JAN-2014
|
19-OCT-1984
|
MALE
|
D04
|
1004
|
MANILA SAHAI
|
09-DEC-2012
|
14-NOV-1986
|
FEMALE
|
D01
|
1005
|
R SAHAY
|
18-NOV-2013
|
31-MAR-1987
|
MALE
|
D02
|
1006
|
JAYA PRIYA
|
09-JUNE-2014
|
23-JUNE-1985
|
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-SEP-13','01-SEP-91','MALE','D01');
INSERT INTO EMPLOYEE1 VALUES('1002','RYMA SEN','11-DEC-12','15-DEC-90','FEMALE','D03');
INSERT INTO EMPLOYEE1 VALUES('1003','MOHITESH','03-FEB-13','04-SEP-87','MALE','D05');
INSERT INTO EMPLOYEE1 VALUES('1007','ANILA JHA','17-JAN-14','19-OCT-84','MALE','D04');
INSERT INTO EMPLOYEE1 VALUES('1004','MANILA SAHAI','09-DEC-12','14-NOV-86','FEMALE','D01');
INSERT INTO EMPLOYEE1 VALUES('1005','R SAHAY','18-NOV-13','13-MAR-87','MALE','D02');
INSERT INTO EMPLOYEE1 VALUES('1006','JAYA PRIYA','09-JUN-14','23-JUN-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-JAN-87' AND '01-DEC-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-JUN-14 19-OCT-84
No comments:
Post a Comment