FastFoodCoding

Login


Connect with one of the following accounts





Login

Please wait while we redirect...

Views: 135   



Below queries are asked in the interviews frequently

1) To find the nth row of a table

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

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

2) To find duplicate rows

SQL> Select *from employee where rowid in (select max(rowid) from employee group by 
         empno, ename, mgr, job, hiredate, comm, deptno, sal);
                              Or
 SQL> Select empno,ename,sal,job,hiredate,comm , count(*) from employee group by 
         empno,ename,sal,job,hiredate,comm  having count(*) >=1;

3) To delete duplicate rows

  SQL> Delete from employee where rowid in (select max(rowid) from employee group by 
          empno,ename,mgr,job,hiredate,sal,comm,deptno);

4) To find the count of duplicate rows

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

5) How to display alternative rows in a table?

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

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

   SQL> select *from employee where (deptno,sal) in
      ( select deptno,max(sal)  from employee 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 employee group by  deptno having count(*) >2500;

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

9) To find nth maximum sal

  SQL> Select *from employee where sal in (select max(sal) from (select *from emp order 
          by sal) where rownum <= 5);



On By


Top Tutorials

Top Questions

Top Articles

Top Blogs

Top News