Oracle- SQL interview Question Answer Part -3 - ETL- iNFORMATICA DEVELOPER

Sunday, June 30, 2019

Oracle- SQL interview Question Answer Part -3

                                             
ktnewslive

                                                   SQL QUERIES -Part 3


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

51) Display the name of the employee who earns highest salary.

SQL>select ename from EMP where sal= (select max (sal) from EMP);

52) Display the employee number and name for employee working as clerk and
      earning highest salary among clerks.

SQL>select empno,ename from emp where job='CLERK'
           and sal=(select max(sal) from emp  where job='CLERK');

53) Display the names of salesman who earns a salary more than the highest
        salary of any clerk.

SQL>select ename,sal from emp where job='SALESMAN' and sal>(select
          max(sal) from emp where job='CLERK');



54) Display the names of clerks who earn a salary more than the lowest
salary of any salesman.

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

54)Display the names of employees who earn a salary more than that of
        Jones or that of salary grether than   that of scott.

SQL>    select ename,sal from emp where sal>
             (select sal from emp where ename='JONES')and sal>
               (select sal from emp where ename='SCOTT');

55) Display the names of the employees who earn highest salary in their
       respective departments.

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

56) Display the names of the employees who earn highest salaries in their
      respective job groups.

SQL>select ename,sal,job from emp where sal in(select max(sal) from emp
        group by job)

57) Display the employee names who are working in accounting department.

SQL>select ename from emp where deptno=(select deptno from dept where
        dname='ACCOUNTING')

58) Display the employee names who are working in Chicago.

SQL>select ename from emp where deptno=(select deptno from dept where
         LOC='CHICAGO')

59) Display the Job groups having total salary greater than the maximum
          salary for managers.

SQL>SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>(SELECT
          MAX(SAL) FROM EMP WHERE JOB='MANAGER');

60) Display the names of employees from department number 10 with salary
        grether than that of any employee working in other department.

SQL>select ename from emp where deptno=10 and sal>any(select sal from
       emp where deptno not in 10).




No comments:

Post a Comment