Oracle SQL PL/SQL Interview Questions (Part-2)

26) Display the total number of employee working in the company.

SQL>select count(*) from emp;

27) Display the total salary beiging paid to all employees.

SQL>select sum(sal) from emp;

28) Display the maximum salary from emp table.

SQL>select max(sal) from emp;

29) Display the minimum salary from emp table.

SQL>select min(sal) from emp;

 30) Display the average salary from emp table.

SQL>select avg(sal) from emp;

31) Display the maximum salary being paid to CLERK.

SQL>select max(sal) from emp where job='CLERK';

32) Display the maximum salary being paid to depart number 20.

SQL>select max(sal) from emp where deptno=20;

33) Display the minimum salary being paid to any SALESMAN.

SQL>select min(sal) from emp where job='SALESMAN';

34) Display the average salary drawn by MANAGERS.

SQL>select avg(sal) from emp where job='MANAGER';

35) Display the total salary drawn by ANALYST working in depart number 40.

SQL>select sum(sal) from emp where job='ANALYST' and deptno=40;

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

SQL>select ename from emp order by sal;

37) Display the names of the employee in descending order of salary.

a)select ename from emp order by sal desc;

38) Display the names of the employee in order of employee name.

a)select ename from emp order by ename;

39) Display empno,ename,deptno,sal sort the output first base on name and within name by deptno and with in deptno by sal.

SQL>select empno,ename,deptno,sal from emp order by

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

SQL>select ename,sal*12 from emp order by sal desc;

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

SQL>select ename,sal,sal/100*15 as hra,sal/100*5 as pf,sal/100*10 as

da, sal+sal/100*15+sal/100*10-sal/100*5 as total from emp;

42) Display depart numbers and total number of employees working in each department.

SQL>select deptno,count(deptno)from emp group by deptno;

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

SQL>select job,count(job)from emp group by job;

44) Display the depart numbers and total salary for each department.

SQL>select deptno,sum(sal) from emp group by deptno;

45) Display the depart numbers and max salary for each department.

SQL>select deptno,max(sal) from emp group by deptno;

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

SQL>select job,sum(sal) from emp group by job;

47) Display the various jobs and total salary for each job

SQL>select job,min(sal) from emp group by job;

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

SQL>select deptno,count(deptno) from emp group by deptno having

count(*)>3;

49) Display the various jobs along with total salary for each of the jobs

where total salary is greater than 40000.

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

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

SQL>select job,count(empno) from emp group by job having count(job)>3