Friday, 16 March 2012

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.

No comments:

Post a Comment

OBIEE 11.1.1.9 Released and available for download

Hi All, OBIEE 11.1.1.9 is released and available for download. Here is the link you can download the software. http://www.oracle.co...