Advertisement Remove all ads

Consider the Following Dept and Employee Tables. Write Sql Queries for (I) to (Iv) and Find Outputs for Sql Queries (V) to (Viii). to Display Eno, Name, Gender from the Table Employee in Ascending Order of Eno. Select Count(*), Dcode from Employee Group by Dcode Having Count(*)>1; Select Max(Doj), Min(Dob) from Employee; - Computer Science (Python)

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;

Advertisement Remove all ads

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

Concept: Structured Query Language Advantages of Using SQL
  Is there an error in this question or solution?
Advertisement Remove all ads
Advertisement Remove all ads

Video TutorialsVIEW ALL [1]

Advertisement Remove all ads
Share
Notifications

View all notifications


      Forgot password?
View in app×