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

Sunday, July 7, 2019

Oracle- SQL interview Question Answer Part -6

                 
www.ktbluehost.com
                

                                           SQL QUERIES -Part 6                      


101) Display name and salary of ford if his salary is equal to hisal of his
        Grade

a)select ename,sal,grade from emp,salgrade where sal between losal and
      hisal and ename ='FORD' AND HISAL=SAL;

102) Display employee name,job,depart name ,manager name,his grade and make
      out an under department wise?

SQL>SELECT E.ENAME,E.JOB,DNAME,EMP.ENAME,GRADE FROM EMP,EMP
E,SALGRADE,DEPT
WHERE EMP.SAL BETWEEN LOSAL AND HISAL AND EMP.EMPNO=E.MGR
 AND EMP.DEPTNO=DEPT.DEPTNO ORDER BY DNAME

103) List out all employees name,job,salary,grade and depart name for every
one in the company  except 'CLERK'.Sort on salary display the highest salary?

SQL>SELECT ENAME,JOB,DNAME,SAL,GRADE FROM EMP,SALGRADE,DEPT WHERE
       SAL BETWEEN LOSAL AND HISAL AND EMP.DEPTNO=DEPT.DEPTNO AND JOB
       NOT IN('CLERK')ORDER BY SAL ASC;

104) Display the employee name,job and his manager.Display also employee who
      are without manager?

SQL>select e.ename,e.job,eMP.ename AS Manager from emp,emp e where
         emp.empno(+)=e.mgr

105) Find out the top 5 earners of company?

SQL>SELECT DISTINCT SAL FROM EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL)
          FROM EMP A WHERE A.SAL>=E.SAL)ORDER BY SAL DESC;


106) Display name of those employee who are getting the highest salary?

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

107) Display those employee whose salary is equal to average of maximum and
          minimum?

SQL>select ename from emp where sal=(select max(sal)+min(sal)/2 from emp);



108) Select count of employee in each department  where count greater than 3?

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

109) Display dname where at least 3 are working and display only department
        name?

SQL>select distinct d.dname from dept d,emp e where d.deptno=e.deptno
         and 3>any(select count(deptno) from emp group by deptno)

110) Display name of those managers name whose salary is more than average
salary of his company?

SQL>SELECT E.ENAME,EMP.ENAME FROM EMP,EMP E
       WHERE EMP.EMPNO=E.MGR AND E.SAL>(SELECT AVG(SAL) FROM EMP);

111)Display those managers name whose salary is more than average salary of
        his employee?

SQL>SELECT DISTINCT EMP.ENAME FROM EMP,EMP E WHERE
            E.SAL <(SELECT AVG(EMP.SAL) FROM EMP
            WHERE EMP.EMPNO=E.MGR GROUP BY EMP.ENAME) AND
            EMP.EMPNO=E.MGR;

112) Display employee name,sal,comm and net pay for those employee
whose net pay is greter than or equal to any other employee salary of
the company?

SQL>select ename,sal,comm,sal+nvl(comm,0) as NetPay from emp
          where sal+nvl(comm,0) >any (select sal from emp)


113) Display all employees names with total sal of company with each
        employee name?

SQL>SELECT ENAME,(SELECT SUM(SAL)  FROM EMP) FROM EMP;

114) Find out last 5(least)earners of the company.?

SQL>SELECT DISTINCT SAL FROM EMP E WHERE 5>=
      (SELECT COUNT(DISTINCT SAL) FROM EMP A WHERE A.SAL<=E.SAL)
        ORDER BY SAL DESC;

115) Find out the number of employees whose salary is greater than their
    manager salary?

SQL>SELECT E.ENAME FROM EMP ,EMP E WHERE EMP.EMPNO=E.MGR
        AND EMP.SAL<E.SAL;


116) Display those department where no employee working?

SQL>select dname from emp,dept where emp.deptno not in(emp.deptno)

117) Display those employee whose salary is ODD value?

SQL>select * from emp where sal<0;

118) Display those employee whose salary contains alleast 3 digits?

SQL>select * from emp where length(sal)>=3;

119) Display those employee who joined in the company in the month of Dec?

SQL>select ename from emp where to_char(hiredate,'MON')='DEC';

120) Display those employees whose name contains "A"?

SQL>select ename from emp where instr(ename,'A')>0;
                                or
SQL>select ename from emp where ename like('%A%');

1 comment:

  1. The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. machine learning projects for final year In case you will succeed, you have to begin building machine learning projects in the near future.

    Projects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.


    Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.


    The Nodejs Training Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

    ReplyDelete