Question

Consider the following tables EMPLOYEE and DEPARTMENT and answer (a) and (b) parts of this question.

1. Write SQL commands for the following statements:

(i) To display all DepName along with the DepCde in descending order of DepCde.

(ii) To display the average age of Employees in DepCde as 103.

(iii) To display the name of DepHead of the Employee named “Sanjeev P”

(iv) To display the details of all employees who has joined before 2007 from EMPLOYEE table.

2. Give the output of the following SQL queries:

(i) SELECT COUNT (DISTINCT DepCde) FROM EMPLOYEE;

(ii) SELECT MAX(JoinDate), MIN (JointDate) FROM EMPLOYEE;

(iii) SELECT TName, DepHead FROM EMPLOYEE E, DEPARTMENT D 

WHERE E.DepCde = D.DepCde;

(iv) SELECT COUNT (*) FROM EMPLOYEE WHERE Salary > 60000 AND Age > 30;

Answer 1

1. (i)  SELECT DEPNAME, DEPARTMENT. DepCde FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE. DepCDE= DEPARTMENT. DepCde Order by DepCde DESC;

(ii). Select AVG (Age) from EMPLOYEE WHERE DepCde=”103″;

(iii). SELECT DeptHead FROM DEPART MENT WHERE Employee. TName= “Sanjeev P” AND EMPLOYEE. DepCde = DEPARTMENT. DepCde;

(iv). SELECT * from EMPLOYEE WHERE joinDate<’01-JAN-2007′;

2.

(iv) 5