QuesAns Print Page – Computer Sir Ki Class

Login


Lost your password?

Don't have an account ?
Register (It's FREE) ×
  


Shop
siteicon
Q&A CBSE12A-2015-C05B #5984

Question:

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
D05 FINANCE KOLKATA
D04 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 date of joining and DOB refers to date of Birth of employees.

(i) To display Eno, Name, Gender from the table EMPLOYEE in ascending order of Eno.

(ii) To display the Name of all the MALE employees from the table EMPLOYEE.

(iii) To display the Eno and Name of those employees from the table EMPLOYEE w ho are born between ‘1987-01-01’ and ‘1991-12-01’.

(iv) To count and display FEMALE employees who have joined after ‘1986-01-01’.

(v) SELECT COUNT(*),DCODE FROM EMPLOYEE GROUP BY DCODE HAVING COUNT(*)>1;

(vi) SELECT DISTINCT DEPARTMENT FROM DEPT;

(vii) SELECT NAME, DEPARTMENT FROM EMPLOYEE E, DEPT D WHERE E.DCODE=D.DCODE AND EN0<1003;

(viii) SELECT MAX(DOJ), MIN(DOB) FROM EMPLOYEE;

Answer:

(i) SELECT Eno,Name,Gender FROM Employee ORDER BY Eno;

(ii) SELECT Name FROM Employee WHERE Gender=’MALE’;

(iii) SELECT Eno,Name FROM Employee
WHERE DOB BETWEEN ‘1987-01-01’ AND ‘1991-12-01′ ;
OR
SELECT Eno,Name FROM Employee
WHERE DOB >=’1987-01-01′ AND DOB <=’1991-12-01′ ; OR SELECT Eno,Name FROM Employee WHERE DOB >’1987-01-01’ AND DOB <‘1991-12-01′ ; (iv) SELECT count(*) FROM Employee WHERE GENDER=’FEMALE’ AND DOJ > ‘1986-01-01′ ;
OR
SELECT * FROM Employee
WHERE GENDER=’FEMALE’ AND DOJ > ‘1986-01-01’ ;

(v)
COUNT   DCODE
2               D01
2               D05

(vi)
Department
INFRASTRUCTURE
MARKETING
MEDIA
FINANCE

(vii)
NAME           DEPARTMENT
George K       INFRASTRUCTURE
Ryma Sen     MEDIA
HUMAN        RESOURCE

(viii)
MAX(DOJ)           MIN(DOB)
2014-06-09           1984-10-19