Queries(30-40)

//queries from 30-40

31. Query to display the Department Name, Location Name, No. Of Employees and the average salary for all employees in that department.

SELECT d.Dname,d.Location,AVG(e.Salary),COUNT(*) 

FROM employee AS e,department AS d WHERE d.Dno=e.Dno GROUP BY d.Dname;

32. Query to display Name and Hire Date for all employees in the same dept. As Blake.

SELECT Ename,Hire_date 

FROM employee WHERE Dno=(SELECT Dno FROM employee WHERE Ename='Blake'); 

  

33. Query to display the Employee No. And Name for all employees who earn more than the average salary.

SELECT Eno,Ename 

FROM employee WHERE Salary > (Select AVG(Salary) FROM employee);

34. Query to display Employee Number and Name for all employees who work in a department with any employee whose name contains a ‘T’.

SELECT e.Eno,e.Ename 

FROM employee AS e ,employee as d WHERE e.Manager=d.Eno AND d.Ename LIKE '%T%';

35. Query to display the names and salaries of all employees who report to King.

SELECT Ename,Salary 

FROM employee WHERE Manager=(SELECT Eno FROM employee WHERE Ename='King');

36. Query to display the department no, name and job for all employees in the Sales department.

SELECT e.Dno,e.Ename,e.Job_type 

FROM employee AS e,department as d WHERE d.Dno=e.Dno AND d.Dname='Sales';