COMPLEX SQL 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 not 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 from emp where rowid not in (select max(rowid) from emp group by  

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

  

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; 

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 emp where sal in (select max(sal) from (select *from emp order  

          by sal) where rownum <= 5); 

10. Which Employees are not Managers? 

SQL> select * from Emp e where e.empno not in ( select mgr from emp e1 where e.empno= e1.mgr); 

11. Display Names who earns highest Salary? 

SQL> Select ename, sal from emp where sal > ( select max(sal) from emp); 

12. Display Em- from Dept 10 & Greater than any of employee working in other Dept. 

SQL> Select ename, Dept, Sal  from emp where sal > ( select max(sal) from emp where deptno = 10) and deptno = 10 group by deptno ; 

13. Display Employees whose salary is more than their Managers 

SQL> select e.Sal, e1.sal, e.ename, e1.ename from emp e, emp e1 where e.ename=e1.mgr and e.sal>e.sal; 

14. Which Job group having total salary greater than Max Sal for Manager 

SQL> select ename, Job,sum(Sal) from emp group by ename, Job having sal> (select max(sal) from emp where job= ‘Manager’); 

15. Display common jobs in Dept 10, 20 

SQL> select Job from emp where job in ( select job from emp where deptno=20) and deptno=10; 

16. Display top 5 Employees ( Designation) 

SQL> Select level, ename, job, mgr from emp where level<=5 connect by prior empno=mgr start with mgr is null; 

17. Display the Employees who joined in same day? 

SQL> select e.ename, e1.ename from emp e,e1 where e.hiredate = e1.hiredate and e.ename != e1.ename 

18) Display Odd Records and Even Records separately.

Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5

Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6

  

1) How to Get the Difference between 2 Timestamps Into a New Column? 

  

Sol: 

TO_TIMESTAMP( your_column2, 'MM/DD/YYYY HH24:MI') -  TO_TIMESTAMP( your_column1, 'MM/DD/YYYY HH24:MI' 

  

OR 

  

( TO_DATE( your_column2, 'MM/DD/YYYY HH24:MI') -  TO_DATE( your_column1, 'MM/DD/YYYY HH24:MI') ) * 24 

  

2) I need to replace multiple commas  input:  11,0000,AB,015,1111,,,066,5555,,,901,6666,,100,,40 0,7777,,,,5,V,140609,   Output:  11,0000,AB,015,1111,,,~066,5555,,,,,~901,6666,,~10 0,,400,7777,,,~5,V,140609,  

  

SOL: 

replacestr(0,replacestr(0,replacestr(0,input_port, ',,,,',',~'),',,,',',~'),',,',',~') 

  

3) Sorting the columns using multiple columns 

select * 

from (select col1, col2, col3, row_number() over(order by col1, col2, col3) rn 

from ... 

where ... 

) 

where rn < 300 ; 

 1) i have a table like this emp id empname empse  12 ram 1000  13 rohan 1200  14 mohan 1300   i want to output like that    emp id empname empse empadd  12 ram 1000 1000  13 rohan 1200 2200  14 mohan 1300 2500     Sol: You can do it as follows:   select emp_id, empname, empse,  sum(empse)  over(order by emp_id  rows between 1 preceding and current row)  from your_table  order by emp_id  /   or, doing the same with a shorter syntax:   select emp_id, empname, empse,  sum(empse)  over(order by emp_id  rows 1 preceding )  from your_table  order by emp_id  /   You should be careful and specify exactly your required ORDER BY  inside the analytic SUM function.  

1) How to Get the Difference Between 2 Timestamps Into a New Column? 

Sol: 

TO_TIMESTAMP( your_column2, 'MM/DD/YYYY HH24:MI') -  TO_TIMESTAMP( your_column1, 'MM/DD/YYYY HH24:MI' 

OR 

( TO_DATE( your_column2, 'MM/DD/YYYY HH24:MI') -  TO_DATE( your_column1, 'MM/DD/YYYY HH24:MI') ) * 24 

2) I need to replace multiple commas  input:  11,0000,AB,015,1111,,,066,5555,,,901,6666,,100,,40 0,7777,,,,5,V,140609,   Output:  11,0000,AB,015,1111,,,~066,5555,,,,,~901,6666,,~10 0,,400,7777,,,~5,V,140609,  

  

SOL: 

replacestr(0,replacestr(0,replacestr(0,input_port, ',,,,',',~'),',,,',',~'),',,',',~') 

3) Sorting the columns using multiple columnsselect * from (select col1, col2, col3, row_number() over(order by col1, col2, col3) rn from ... where ... ) where rn < 300 ;   3)Retrieve Only Duplicate Records  

Sol:  

SELECT * FROM emp aWHERE rowid = (SELECT max(rowid) FROM emp  WHERE empno = a.empno  GROUP BY empno  HAVING count(*) >1) 

 4) How can we remove the Duplicate records in Oracle? 

  

SOL: 

  

SYNTAX 

delete from <table_name> where rowid not in                     ( select min(rowid)                       from exp group by column1..,column2,...column3..); 

  

EX: 

delete from EMP where rowid not in                     ( select min(rowid)                       from EMP group by Student_Name); 

=====================