Query List
31. Query to display the Department Name, Location Name, No. of Employees and the average salary for all employees in that department.
Query 1:
Select Dname, Location, count(*), AVG (Salary) from EMPLOYEE e,DEPARTMENT d where e.Dno=d.Dno group by d.Dname, d.Location;
OR
SELECT d.Dname, d.Location, COUNT(*),AVG(e.Salary) FROM employee e, department d WHERE d.Dno = e.Dno GROUP BY d.Dname, d.Location;
Output:
32. Query to display Name and Hire Date for all employees in the same dept. As Rupesh.
Query:
Select Ename, Hire_date from EMPLOYEE where Dno = (select Dno from EMPLOYEE where Ename='Rupesh');
Output:
33. Query to display the Employee No. And Name for all employees who earn more than the average salary.
Query:
Select Eno, Ename from EMPLOYEE where Salary>(select AVG(Salary)from EMPLOYEE);
Output:
34. Query to display Employee Number and Name for all employees who work in a department with any employee whose name contains a ‘T’.
Query:
Select Eno, Ename from EMPLOYEE where Dno IS NOT NULL AND Ename LIKE '%t%';
Output:
35. Query to display the names and salaries of all employees who report to Kunal.
Query:
Select Ename, Salary from EMPLOYEE where Manager = (select Eno from EMPLOYEE where Ename='Kunal');
Output:
