Oracle interview Questions

1) HOW TO FIND DUPLICATE RECORDS?

 SELECT * FROM EMP WHERE ROWID NOT IN (SELECT MAX (ROWID) FROM EMP GROUP BY JOB);

2) HOW TO DISPLAY ODD NUMBER ROWS?

 SELECT RN,SAL FROM (SELECT ROWNUM RN,SAL FROM EMP) WHERE MOD(RN,2)=1;

3) HOW TO DISPLAY EVEN NUMBER ROWS?

  SELECT RN,SAL FROM (SELECT ROWNUM RN,SAL FROM EMP) WHERE MOD(RN,2)=0;

4) HOW TO REMOVE DUPLICATE RECORDS?

 DELETE FROM EMP WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP GROUP BY JOB);

5) HOW TO DISPLAY FIRST 4 RECORDS?

 SELECT ROWNUM,ENAME,SAL FROM EMP WHERE ROWNUM<5;

6) HOW TO DISPLAY TOP 5 SALARIES?

 SELECT ROWNUM,EMPNO,ENAME,SAL FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=5;

7) HOW TO DISPLAY THE WITHOUT DUPLICATE ROWS?

 SELECT * FROM EMP WHERE ROWED IN (SELECT MAX(ROWID) FROM EMP GROUP BY JOB);

8) FIND OUT NTH HIGHEST SALARIES FROM EMP TABLE?

 SELECT DISTINCT(A.SAL)FROM EMP A

WHERE &N=(SELECT COUNT(DISTINCT(B.SAL)FROM EMP B

WHERE A.SAL=B.SAL);

9) HOW TO DISPLAY RECORDS FROM 5 TH TO 9 TH RECORDS ONLY?

 SELECT * FROM EMP WHERE ROWNUM<=10;

 MINUS

 SELECT * FROM EMP WHERE ROWNUM<=5;

OR(DYNAMICALLY)

 SELECT RN,SAL FROM(SELECT ROWNUM RN,SAL FROM EMP) WHERE RN BETWEEN &N1 AND &N2;

10) HOW TO DISPLAY THE 2 ND HIGHEST SALARY?

 SELECT EMPNO,ENAME,SAL,R FROM(SELECT EMPNO,ENAME,SAL,DENSE_RANK() OVER(ORDER BY SAL DESC) R FROM EMP)WHERE R=2;

11) HOW TO REMOVE SPACES IN BETWEEN STRING?

 SELECT REPLACE(‘BALAJIXXXBALU’,’X’,’ ‘)FROM DUAL;

12) HOW TO DISPLAY THE HOW MANY DUPLICATE NAMES IN A TABLE?

 SELECT JOB FROM(SELECT JOB,COUNT(JOB)FROM EMP GROUP BY JOB HAVING COUNT(JOB) >1);

13) HOW TO DISPLAY THE HALF OF THE ROWS?

 SELECT SAL FROM (SELECT ROWNUM RN,SAL FROM EMP)WHERE RN 6;

14) DISPLAY DEPT NUMBERS AND TOTAL NUMBER OF EMPLOYEES WORKING IN EACH DEPARTMENT?

 SELECT DEPTNO,COUNT(DEPT)FROM EMP GROUP BY DEPTNO;

15) DISPLAY THE DEPARTMENT NUMBERS AND TOTAL SALARY FOR EACH DEPT?

 SELECT DEPTNO,SUM(SAL)FROM EMP GROUP BY DEPTNO;

16) DISPLAY THE DEPT NUMBERS WITH MORETHEN THREE EMPLOYEES IN EACH DEPT?

 SELECT DEPTNO,COUNT(DEPTNO)FROM EMP GROUP BY DEPTNO HAVING COUNT(*) >3;

17) HOW TO DISPLAY SINGLE ‘S’ LETTER STRING?

 SELECT * FROM EMP WHERE INSTR(JOB,’S’,1,1) 0 AND INSTR(JOB,’S’,1,2)=0;

18) HOW TO DISPLAY LAST 4 RECORDS?

 SELECT * FROM EMP WHERE ROWNUM<=(SELECT COUNT(*) FROM EMP)

MINUS

SELECT * FROM EMP WHERE ROWNUM<=(SELECT COUNT(*)-4 FROM EMP);

19) HOW TO USE ALL FUNCTIONS?

 SELECT SUM(SAL),DEPTNO FROM EMP

WHERE SAL =500

GROUP BY DEPTNO

HAVING DEPTNO=10

ORDER BY DEPTNO DESC;

20) HOW WILL U IDENTIFY THE DUPLICATES IN PARTICULAR COLUMN?

 SELECT JOB,COUNT(*)FROM EMP GROUP BY JOB HAVING COUNT(*)> 1;

21) WRITE A QUERY TO LIST OUT THE EMPLOYEES WITH THERE RESPECT TO MGR LEVELS?

SELECT LPAD(‘*’,LEVEL2),EMPNO,ENAME,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR START WITH EMPNO=7839;

22) WRITE A QUERY TO GENERATE SEQUENCE NUMBERS FROM 1 TO THE SPECIFIED NUMBER N?

SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=&N;

23) https://www.youtube.com/watch?v=oPBWC4_Zmj0index=2list=PL9DF6E4B45C36D411

OUTPUT: HOW TO EXTRACT ONLY YOUTUBE FROM ABOVE LINK?

Ans) select substr('https://www.youtube.com/watch?v=oPBWC4_Zmj0index=2list=PL9DF6E4B45C36D411',

instr('https://www.youtube.com/watch?v=oPBWC4_Zmj0index=2list=PL9DF6E4B45C36D411','.',1,1)+1,

instr('https://www.youtube.com/watch?v=oPBWC4_Zmj0index=2list=PL9DF6E4B45C36D411','.',1,2) -

instr('https://www.youtube.com/watch?v=oPBWC4_Zmj0index=2list=PL9DF6E4B45C36D411','.',1,1)-1)from dual;

24) BALAJI

OUTPUT: HOW TO COUNT HOW MANY TIMES 'A' IS REPEATED?

Ans) SELECT LENGTH('BALAJI')-LENGTH(REPLACE('BALAJI','A','')) from dual;

25) IN THE COLUMN ACCOUNT TYPE, “SAVINGS ACCOUNT” AND “CURRENT ACCOUNT” ARE TWO DIFFERENT Values Present. THEY GET CONVERTED TO “SB” AND “CA” IN TARGET.

WRITE QUERY TO VALIDATE THIS WITH BOTH SOURCE AND TARGET TABLE IN SAME QUERY

Ans)

SELECT T1.ACCOUNT_NO,T1.ACCOUNT_TYPE

FROM EMP T1

INNER JOIN (SELECT ACCOUNT_NO,

CASE ACCOUNT_TYPE

WHEN 'SAVINGS ACCOUNT' THEN 'SB'

WHEN 'CURRENT ACCOUNT' THEN 'CA'

ELSE ACCOUNT_TYPE

END NEW_ACCOUNT_TYPE

FROM SOURCE) S1

ON T1.ACCOUNT_TYPE=S1.NEW_ACCOUNT_TYPE

AND T1.ACCOUNT_NO= S1.ACCOUNT_NO

26) SOURCE HAS 10 RECORDS AND TARGET AND 8 RECORDS ONLY. HOW WILL YOU IDENTIFY THE MISSING RECORDS FROM THE OUTPUT?

Ans)

SELECT S1.PK_COL,T1.PK_COL FROM SOURCE S1

LEFT OUTER JOIN TARGET T1

ON S1.PK_COL= T1.PK_COL

ORDER BY S1.PK_COL

or

SELECT PK_COL FROM SOURCE

WHERE PK_COL NOT IN (SELECT PK_COL FROM TARGET)

27) DATA FROM TABLE1 WITH 3 COLUMNS IS TRANSFERRED TO TABLE2 WITH 3 COLUMNS WITH NO TRANSFORMATIONS.  WRITE QUERY/QUERIES TO VALIDATE THAT ALL THE RECORDS WERE TRANSFERRED CORRECTLY?

Ans)

SELECT COL_1,COL_2,COL_3 FROM TABLE1

MINUS

SELECT COL_1,COL_2,COL_3 FROM TABLE2

28) DURING AN ETL TRANSFORMATION FROM SOURCE TO TARGET WRITE QUERY TO SHOW ALL THE RECORDS FROM SOURCE WHICH WERE NOT TRANSFERRED TO THE TARGET WITH USING “IN”?

Ans)

SELECT PK_COL FROM SOURCE

WHERE PK_COL NOT IN (SELECT PK_COL FROM TARGET)

29) Scenario Based questions.

Set operators

Table X

Name	Value
A	1
B	2
C	3
E	6
G	7

Table Y

Name	Value
B	3
C	4
D	5
E	6
F	7

MINUS

SELECT * FROM X

MINUS

SELECT * FROM Y;

O/P:-

UNION, UNION ALL AND INTERSECT

SELECT * FROM X

UNION

SELECT * FROM Y;

SELECT * FROM X

UNION ALL

SELECT * FROM Y;

SELECT * FROM X

INTERSECT

SELECT * FROM Y;

   

             


                                   

Equi JOINS

SELECT X.NAME,X.VALUE,Y.NAME,Y.VALUE FROM X X
JOIN Y Y
ON X.VALUE=Y.VALUE;

Left outer join:

SELECT X.NAME,X.VALUE,Y.NAME,Y.VALUE FROM X X
LEFT OUTER JOIN Y Y
ON X.VALUE=Y.VALUE;












Right outer join:

SELECT X.NAME,X.VALUE,Y.NAME,Y.VALUE FROM X X
RIGHT OUTER JOIN Y Y
ON X.VALUE=Y.VALUE;

Full ourter join:

SELECT X.NAME,X.VALUE,Y.NAME,Y.VALUE FROM X X
FULL OUTER JOIN Y Y
ON X.VALUE=Y.VALUE;

Example 2: