SQL Common Interview Questions

1 ) Write a Query To Delete The Repeted Rows from emp table;

SQL>Delete from emp where rowid not in(select min(rowid)from emp group

by ename)

               

2) TO DISPLAY 5 TO 7 ROWS FROM A TABLE

SQL>select ename from emp

         where rowid in(select rowid from emp where rownum<=7

         minus

  select rowid from empi where rownum<5)

3)  DISPLAY  TOP N ROWS FROM TABLE?

SQL>SELECT * FROM

                (SELECT *  FROM EMP ORDER BY ENAME DESC)

                 WHERE ROWNUM <10;

4) DISPLAY   TOP 3 SALARIES FROM EMP;

SQL>SELECT SAL FROM ( SELECT  * FROM EMP ORDER  BY SAL DESC )

                   WHERE ROWNUM <4

5) DISPLAY  9th FROM THE EMP TABLE?

SQL>SELECT ENAME FROM EMP

                WHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10

                MINUS

                SELECT ROWID FROM EMP WHERE ROWNUM <10)

                select second max salary from emp;

                select max(sal) fromemp where sal<(select  max(sal) from emp);

SQL Important  QUERIES 

1)   To find the nth row of a table

SQL> Select *from emp where rowid = (select max(rowid) from emp where rownum <= 4);

Or

   SQL> Select *from emp where rownum <= 4 minus select *from emp where rownum <= 3;

2)   To find duplicate rows

SQL> Select *from emp where rowid in (select max(rowid) from emp group by empno,          

         ename, mgr, job, hiredate, comm, deptno, sal);

Or

 SQL> Select empno,ename,sal,job,hiredate,comm , count(*) from emp group by

         empno,ename,sal,job,hiredate,comm  having count(*) >=1;

3)   To delete duplicate rows

      SQL> Delete emp where rowid in (select max(rowid) from emp group by

              empno,ename,mgr,job,hiredate,sal,comm,deptno having count(*)>1);

4)   To find the count of duplicate rows

      SQL> Select ename, count(*) from emp group by ename having count(*) >= 1;

5)   How to display alternative rows in a table?

          SQL> select *from emp where (rowid,0) in (select rowid,mod(rownum,2) from emp);

6)   Getting employee details of each department who is drawing maximum sal?

       SQL> select *from emp where (deptno,sal) in

               ( select deptno,max(sal)  from emp group by deptno);

7)   How to get number of employees in each department  , in which department is having more than 2500 employees?

       SQL> Select deptno,count(*) from emp group by  deptno having count(*) >2500;

           9) To reset the time to the beginning of the day

                  SQL> Select to_char(trunc(sysdate),’dd-mon-yyyy hh:mi:ss am’) from dual;

10) To find nth maximum sal

  SQL> Select *from emp where sal in (select max(sal) from (select *from emp order by sal)

          where rownum <= 5);