Categories
Uncategorized

Query(4th cia)

1. Using group functions, retrieve suitable results by using HAVING, GROUP BY and ORDER BY clauses

a. Find the age of highest paid employee who is at least 30 years old for each department with at least two such employees.

b. Find those departments for which the average age of employee is the minimum over all departments.

c. Find the sum of salary of all the employees in a each department having department number greater than 10.

solution,

a. select age from employee where salary in( select max(salary) from employee where employee.age>30 group by employee.deptno having count(*)>2);

b.select deptno from employee group by employee.deptno having avg(age)<(select avg(age) from employee);

c. Select sum(sal) as Sumsal from Employee where deptno>30 group by deptno;