SQL Query and Answers - Part2

26.Display the total number of employees working in the company.

  Select count(*) from emp;

27. Display the total salary being paid to all employees.

  Select sum(sal)+sum(nvl(comm.,0)) form emp;

28. Display the maximum salary from emp table.

Select max(sal ) from emp;

29. Display the minimum salary from emp table.

Select min(sal) from emp;

30. Display the average salary from emp table.

Select avg(sal) from emp;

31. Display the maximum salary being paid to CLERK.

Select max(sal ) from emp  where job=’clerk’;

32. Display the maximum salary being paid in dept no 20.

Select max(sal ) from emp  where deptno=20;

33. Display the min salary being paid to any SALESMAN

Select max(sal ) from emp  where job=’ SALESMAN’;

34. Display the average salary drawn by managers.

Select avg(sal) from emp where job=’MANAGER’;

35. Display the total salary drawn by analyst working in dept no 40.

Select sum(sal)+sum(nvl(comm,0)) from emp where deptno=40;

36. Display the names of employees in order of salary i.e. the name of the employee earning  lowest salary should appear first.

Select ename from emp order by sal;

37.  Display the names of employees in descending order of salary

Select ename from emp order by sal desc;

38. Display the details from emp table in order of emp name.

Select ename from emp order by ename;

39. Display empno,ename,deptno and sal .Sort the output first based on name and within name by deptno  and withindeptno by sal;

 Select * from emp order byename,deptno,sal;

40. Display the name of the employee along with their annual salary (sal*12).The name of the employee earning highest annual salary should appear first.

  Select ename,12*(sal+nvl(comm,0)) Annual from emp order by  12*(sal+nvl(comm,0)) desc;

41. Display name ,Sal,hra,pf,da,total sal for each employee.The Output should be in the order of total sal, hra 15% of sal , da 10% of sal , pf 5% of sal total salary will be (sal*hra*da)-pf.

Select ename,sal,sal*15/100 HRA, SAL*5/100 pf,SAL*10/100 da,sal+sal*15/100-sal*5/100 Total_SALARY from emp

42. Display dept numbers and total number of employees within each group.

Select deptno,count(*) from emp group by deptno;

43. Display the various jobs and total number of employees with  each job group.

Select  job,count(*) from emp group by job;

44. Display department numbers and total salary for each department.

Select deptno, sum(sal)  from emp group by deptno;

45. Display department numbers and maximum salary for each department.

Select deptno, max(sal)  from emp group by deptno;

46. Display the various jobs and total salary for each job.

Select  job, sum(sal)  from emp group by job;

47. Display each jobs along  with minimum sal being paid in each job group.

Select  job,min(sal)  from emp group by job;

48.Display the department numbers with more than three employees  in each dept.

Select deptno,count(*) from emp group by deptno having count(*)>3;

49. Display the various jobs along with total sal for each of the jobs where total sal is greater than 40000

Select  job, sum(sal)  from emp group by job having sum(sal)>40000;

50. Display the various jobs along with total number of employee in each job.the output should contain only those jobs with more than three employees.

  Select job,count(*) from emp group by job having count(*)>3.