Queries(20-30)

//queries from 20-30

21. Query to display Name, Department Name and Department No for all the employees.

SELECT e.Ename,d.Dname,e.Dno FROM employee AS e,department AS d WHERE e.Dno=d.Dno;

22. Query to display Unique Listing of all Jobs that are in Department # 30.

SELECT DISTINCT Job_type FROM employee WHERE Dno=30;

23. Query to display Name, Dept Name of all employees who have an ‘A’ in their name.

SELECT e.Ename,d.Dname FROM employee AS e,department as d WHERE e.Ename LIKE '%A%' AND e.Dno=d.Dno;

24. Query to display Name, Job, Department No. And Department Name for all the employees working at the Dallas location.

SELECT e.Ename,e.Job_type,e.Dno,d.Dname FROM employee AS e,department as d WHERE e.Dno=d.Dno AND d.Location='Dallas';

25. Query to display Name and Employee no. Along with their Manger’s Name and the Manager’s employee no; along with the Employees’ Name who do not have a Manager.

SELECT e.Ename,e.Eno,d.Ename,d.Eno FROM employee AS e LEFT OUTER JOIN employee as d ON e.Eno=d.Manager;

26. Query to display Name, Dept No. And Salary of any employee whose department No. And salary matches both the department no. And the salary of any employee who earns a commission.

SELECT Ename,Dno,Salary FROM employee WHERE (Dno,Salary) IN (SELECT Dno,Salary FROM employee WHERE Commission>0);

27. Query to display Name and Salaries represented by asterisks, where each asterisk (*) signifies $100.

SELECT Ename,REPEAT ('*',(Salary/100)) AS SALARY_IN_STAR FROM employee;

28. Query to display the Highest, Lowest, Sum and Average Salaries of all the employees

SELECT MAX(Salary),MIN(Salary),SUM(Salary),AVG(Salary) FROM employee;

29. Query to display the number of employees performing the same Job type functions.

SELECT job_type,COUNT(*) FROM employee GROUP BY Job_type;

30. Query to display the no. Of managers without listing their names.

SELECT COUNT(DISTINCT Manager) FROM employee;