Check sibling questions

Write queries (a) to (d) based on the tables EMPLOYEE and DEPARTMENT given below:

Table: EMPLOYEE

EMPID 

NAME  DOB  DEPTID  DESIG  SALARY 
120 Alisha  23 - Jan- - 1978  D001  Manager  75000
123 Nitin  10-Oct-77 D002  AO  59000
129 Navjot  12-Jul-71 D003  Supervisor  40000
130 Jimmy  30-Dec-80 D004  Sales Rep   
131 Faiz  06-Apr-84 D001  Dep Manager  65000

Table: DEPARTMENT

DEPTID 

DEPTNAME  FLOORNO 
D001  Personal  4
D002  Admin  10
D003  Production  1
D004  Sales  3

 

(a) To display the average salary of all employees, department wise.

Answer

(a) SELECT AVG(SALARY)

FROM EMPLOYEE 

GROUP BY DEPTID; 

 

(b) To display name and respective department name of each employee whose salary is more than 50000. 

Answer

SELECT NAME, DEPTNAME

FROM EMPLOYEE, DEPARTMENT

WHERE

EMPLOYEE.DEPTID=

DEPARTMENT.DEPTID

AND SALARY>50000;

 

(c) To display the names of employees whose salary is not known, in alphabetical order. 

Answer

SELECT NAME FROM EMPLOYEE

WHERE SALARY IS NULL

ORDER BY NAME;

(d) To display DEPTID from the table EMPLOYEE without repetition.

Answer

SELECT DISTINCT DEPTID 

FROM EMPLOYEE;

983 students joined Teachoo Black. What are you waiting for?

Davneet Singh's photo - Teacher, Engineer, Marketer

Made by

Davneet Singh

Davneet Singh is a graduate from Indian Institute of Technology, Kanpur. He has been teaching from the past 12 years. He provides courses for Maths and Science at Teachoo.