Share
Notifications

View all notifications

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)

Login
Create free account


      Forgot password?
ConceptStructured Query Language Advantages of Using SQL

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
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

  Is there an error in this question or solution?

APPEARS IN

Video TutorialsVIEW ALL [1]

Solution 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; Concept: Structured Query Language Advantages of Using SQL.
S
View in app×