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

Question

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

Table: EMPLOYEE

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. 

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

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

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

Answer 1

(a) SELECT AVG(SALARY)

FROM EMPLOYEE 

GROUP BY DEPTID;

(b) SELECT NAME, DEPTNAME 

FROM EMPLOYEE, DEPARTMENT 

WHERE

EMPLOYEE.DEPTID= 

    DEPARTMENT.DEPTID 

 AND SALARY>50000;

(c) SELECT NAME FROM EMPLOYEE 

WHERE SALARY IS NULL 

ORDER BY NAME;

(d) SELECT DISTINCT DEPTID 

FROM EMPLOYEE;