(a) Write the outputs of the SQL queries (i) to (iv) based on the relations Teacher and Placement given below:
Table : Teacher
(i)
SELECT Department, avg(salary) FROM Teacher
GROUP BY Department;
(ii)
SELECT MAX(Date_of_Join),MIN(Date_of_Join) FROM
Teacher;
(iii)
SELECT Name, Salary, T.Department, Place FROM
Teacher T, Placement P WHERE T.Department =
P.Department AND Salary>20000;
(iv)
SELECT Name, Place FROM Teacher T, Placement P WHERE Gender =’F’ AND T.Department=P.Department;
Question 28(a)(i)
Answer:
Explanation:
The given query will group the records based on department and then display each department and the average salary of each department.
Question 28(a)(ii)
Answer:
Explanation:
The given query will display the largest and smallest value in the column Date_of_Join of tables Teacher.
Question 28(a)(iii)
Answer:
Explanation:
The given query will join the tables Teacher and Placement using an equi join on column Department and display the Name, Salary, Department and Place of Teachers with Salary greater than 20000.
Question 28(a)(iv)
Answer:
Explanation:
The given query will join the tables Teacher and Placement using an equi join on column Department and display the Name and Place of all Female teachers.