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'));
               

1 comment:

  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