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

Tuesday, July 9, 2019

Oracle- SQL interview Question Answer Part -7

                       
www.ktbluehost.com
                              

                    SQL QUERIES -Part 7                                


121) Display those employee whose deptno is available in salary?

SQL>select emp.ename from emp, emp e where emp.sal=e.deptno;

122) Display those employee whose first 2 characters from hiredate -last 2
        characters of salary?

SQL>select ename,SUBSTR(hiredate,1,2)||ENAME||substr(sal,-2,2) from emp

123) Display those employee whose 10% of salary is equal to the year of
      joining?

SQL>select ename from emp where to_char(hiredate,'YY')=sal*0.1;

124) Display those employee who are working in sales or research?

SQL>SELECT ENAME FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE
           DNAME IN('SALES','RESEARCH'));

125) Display the grade of jones?

SQL>SELECT ENAME,GRADE FROM EMP,SALGRADE
            WHERE SAL BETWEEN LOSAL AND HISAL AND Ename='JONES';




126) Display those employees who joined the company before 15 of the month?

a)select ename from emp where to_char(hiredate,'DD')<15;

127) Display those employee who has joined before 15th of the month.

a)select ename from emp where to_char(hiredate,'DD')<15;

128) Delete those records where no of employees in a particular department
         is less than 3.

SQL>delete from emp where deptno=(select deptno from emp
           group by deptno having count(deptno)<3);


129) Display the name of the department where no employee working.

SQL> SELECT E.ENAME,E.JOB,M.ENAME,M.JOB FROM EMP E,EMP M
         WHERE E.MGR=M.EMPNO

130) Display those employees who are working as manager.

SQL>SELECT M.ENAME MANAGER FROM EMP M ,EMP E
          WHERE E.MGR=M.EMPNO GROUP BY M.ENAME

131) Display those employees whose grade is equal to any number of sal but
         not equal to first number of sal?

SQL> SELECT ENAME,GRADE FROM EMP,SALGRADE
            WHERE GRADE NOT IN(SELECT SUBSTR(SAL,0,1)FROM EMP)

132) Print the details of all the employees who are Sub-ordinate to BLAKE?

SQL>select emp.ename from emp, emp e where emp.mgr=e.empno and
         e.ename='BLAKE';

133) Display employee name and his salary whose salary is greater than
       highest average of department number?

SQL>SELECT SAL FROM EMP WHERE SAL>(SELECT MAX(AVG(SAL)) FROM EMP
         GROUP BY DEPTNO);

134) Display the 10th record of emp table(without using rowid)

SQL>SELECT * FROM EMP WHERE ROWNUM<11
                MINUS
     SELECT * FROM EMP WHERE ROWNUM<10




135) Display the half of the ename's in upper case and remaining lowercase?

SQL> SELECT
         SUBSTR(LOWER(ENAME),1,3)||SUBSTR(UPPER(ENAME),3,LENGTH(ENAME))
           FROM EMP;

136) Display the 10th record of emp table without using group by and rowid?

SQL>SELECT * FROM EMP WHERE ROWNUM<11
                MINUS
                SELECT * FROM EMP WHERE ROWNUM<10

                Delete the 10th record of emp table.

SQL>DELETE FROM EMP WHERE EMPNO=(SELECT EMPNO FROM EMP WHERE ROWNUM<11
                MINUS
                SELECT EMPNO FROM EMP WHERE ROWNUM<10)

137) Create a copy of emp table;

SQL>create table new_table as select * from emp where 1=2;
 
138) Select ename if ename exists more than once.

SQL>select ename  from emp e group by ename having count(*)>1;

139) Display all enames in reverse order?(SMITH:HTIMS).

SQL>SELECT REVERSE(ENAME)FROM EMP;

140) Display those employee whose joining of month and grade is equal.

SQL>SELECT ENAME FROM EMP WHERE SAL BETWEEN
           (SELECT LOSAL FROM SALGRADE WHERE
             GRADE=TO_CHAR(HIREDATE,'MM')) AND
            (SELECT HISAL FROM SALGRADE WHERE
           GRADE=TO_CHAR(HIREDATE,'MM'));
               

2 comments:

  1. Very informative post ! There is a lot of information here that can help any business get started with a successful social networking campaign !data science course in malaysia
    best tableau training
    data analytics course malaysia
    360DigiTMG

    ReplyDelete
  2. Great Article android based projects

    Java Training in Chennai Project Center in Chennai Java Training in Chennai projects for cse The 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 Project Centers in Chennai

    ReplyDelete