SQL Interview Questions Set-1

1)      FIRST AND LAST RECORD FROM EMP TABLE?

SELECT * FROM EMP WHERE ROWNUM=1 UNION

SELECT * FROM EMP WHERE ROWID=(SELECT MAX(ROWID) FROM EMP);

2)      DISPLAY Nth RECORD FROM EMP TABLE?

SELECT * FROM (SELECT ROWNUM AS RNO,EMP.* FROM EMP) WHERE RNO=&N;

3)      DISPLAY 5^th AND 8^th  RECORD’s FROM EMP TABLE;

SELECT * FROM (SELECT ROWNUM AS RNO,EMP.* FROM EMP) WHERE RNO IN(5,9) ;

4)      DISPLAY ALTERNATIVE ROW’s FROM EMP TABLE?(ODD NUMBER ROWS)

SELECT * FROM (SELECT ROWNUM AS RNO,EMP.* FROM EMP) WHERE MOD(RNO,2)=1;

5)      DISPLAY ALTERNATIVE ROW’s FROM EMP TABLE?(EVEN NUMBER ROWS)

SELECT * FROM (SELECT ROWNUM AS RNO, EMP.* FROM EMP) WHERE MOD (RNO, 2) =1;

6)      DISPLAY LAST RECORD FROM EMP TABLE?

SELECT * FROM EMP WHERE ROWID=(SELECT MAX(ROWID) FROM EMP);

7)      DISPLAY LAST 3 RECORD’s FROM EMP TABLE?

SELECT * FROM (SELECT * FROM EMP ORDER BY ROWID DESC) WHERE ROWNUM<=3;

8)      DISPLAY FIRST 3 RECORD’s AND LAST 3 RECORD’s  FROM EMP TABLE?

SELECT * FROM EMP WHERE ROWNUM<=3 UNION

SELECT * FROM (SELECT * FROM EMP ORDER BY ROWID DESC) WHERE ROWNUM<=3;

9)      DISPLAY 5^th MAXIMUM SALARY OF EMP TABLE?

SELECT * FROM (SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS RNK,

EMP.* FROM EMP) WHERE RNK=5;

10)  DISPLAY MAXIMUM SALARY OF EMP TABLE USING JOIN’s?

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

WHERE A.SAL<=B.SAL);
11)  DISPLAY SALES DEPT AVG(SAL) FROM EMP TABLE?

SELECT AVG(SAL) FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM  DEPT WHERE DNAME='SALES');

12)  DEPT WISE MAXIMUM SALARY OF EMP TABLE?

SELECT * FROM EMP WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO);

                                    (OR)

SELECT * FROM EMP  E WHERE SAL=(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO);

13)  DISPLAY  AVG(SAL) FROM EMP TABLE?
SELECT EMPNO,ENAME,SAL,(SELECT AVG(SAL) FROM EMP) AVGSAL FROM EMP;
14)  DISPLAY TOP 3 SALARIES FROM EMP TABLE?
SELECT * FROM EMP WHERE 3> (SELECT COUNT(DISTINCT SAL) FROM EMP E WHERE EMP.SAL<E.SAL);
15)  DISPLAY TOP 3 SALARY FROM EMP TABLE?
SELECT * FROM EMP E WHERE 2=(SELECT COUNT(DISTINCT SAL) FROM EMP WHERE SAL>E.SAL);
16)  DISPLAY DUPLICATE  RECORD’s FROM EMPTABLE?
SELECT * FROM EMP  WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP GROUP BY ENAME);
17)  DISPLAY DISTINCT RECORD’s FROM EMP TABLE?
SELECT * FROM EMP  WHERE ROWID IN (SELECT MAX(ROWID) FROM EMP GROUP BY ENAME);
18)  DISPLAY EMPLOYEE WITH CORRESPONDING MANAGER’s?
SELECT A.ENAME EMPLOYEE ,B.ENAME MANAGER FROM EMP A INNER JOIN EMP B ON(A.EMPNO=B.MGR);
19)  DISPLAY BOTTOM   N NUMBER OF RECORD’s IN EMP TABLE?
SELECT * FROM EMP MINUS SELECT * FROM EMP WHERE ROWNUM<=(SELECT COUNT(*) -&N FROM EMP);
20)  DISPLAY TOP 10 SALARIES OF EMP TABLE?
SELECT * FROM (SELECT * FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=10;
21)  DISPLAY DEPT WISE  AVG(SAL) FROM EMP TABLE?

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

    DEPTNO   AVG(SAL)

---------- ----------

        10 24233.3333

        20 2647.22222

        30       1900
22)  ELIMINATE THE DUPLICATE RECORD’s WITH OUT USING DISTINCT?

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

23)   DISPLAY 5^th  MAXIMUM SALRY OF EMPTABLE?

SELECT  * FROM(SELECT DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RNK, EMP.* FROM EMP ) WHERE RNK=5;

24)  WRITE A QUERY TO FIND THE HIGHEST SALARY EARNED BY AN EMPLOYEE IN EACH DEPARTMENT AND ALSO THE NUMBER OF EMPLOYEES WHO EARN THE HIGHEST SALARY?

SELECT DEPTNO,

 MAX(SAL) HIGHEST_SALARY,

 COUNT(1) KEEP(DENSE_RANK LAST ORDER BY SAL) CNT_HIGH_SAL

FROM EMP

GROUP BY DEPTNO;

                                                   (OR)

SELECT DEPARTMENT_ID,

 MAX(SALARY) HIGHEST_SALARY,

 COUNT(1) KEEP(DENSE_RANK LAST ORDER BY SALARY) CNT_HIGH_SAL

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID;

25)   WRITE A QUERY TO GET THE TOP 2 EMPLOYEES WHO ARE EARNING THE HIGHEST SALARY IN EACH DEPARTMENT?

SELECT DEPTNO, EMPNO, SAL FROM

 (

SELECT DEPTNO, EMPNO, SAL,

ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC ) R

FROM EMP

) A WHERE R <= 2;

26)   WRITE A QUERY TO FIND THE EMPLOYEES WHO ARE EARNING MORE THAN THE AVERAGE SALARY IN THEIR DEPARTMENT? 

SELECT EMPNO, SAL FROM EMP E_O WHERE SAL >

(SELECT AVG(SAL) FROM EMP E_I WHERE E_I.DEPTNO = E_O.DEPTNO ); 

Thanks

Ur's Hari

If you like this post, please share it by clicking on g+1 Button.