Oracle faqs

1)display 1st highest sal
 select * from(select * from emp order by sal desc) where rownum<=1;
----------------------------
2)display 2nd highest sal
 select max(sal) from emp where sal < (select max(sal) from emp);
--------------------------
3)display nth sal
select * from emp e where &n=
(select count(distinct sal) from emp where e.sal<=sal)
-------------------------
4)display nth row
 select * from emp e where &n=
 (select count(rowid) from emp e1 where e.rowid>=e1.rowid);
--------------------------------
5)dispalay first n rows
select * from ( select * from emp order by empno ) where rownum<=5
---------------------------
6)display last n rows
 select * from emp a where 5>=
 (select count(empno) from emp b where a.rowid<=b.rowid)
---------------------------
7)display duplicates in  a table
select * from emp where rowid not in (select min(rowid) from emp
group by sal);
---------------------------
8)eliminate duplicates
delete from emp1 where rowid not in ( select min(rowid) from emp1
group by sal);
---------------------------
9)find out the employees where there are more than 3 employees in a dept
select * from emp where deptno in
( select deptno from emp group by deptno having count(deptno)>3)
------------------------------
10)display 95-98 records
select * from emp where rownum<=98
minus
select * from emp where rownum<95;
---------------------------------------
11) display last nth number
 select * from emp a where 4=
 (select count(empno) from emp b where a.rowid<=b.rowid);
-------------------------
11)
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;
12)DISPLAY DOMAIN
SELECT SUBSTR(ID,INSTR(ID,'@')+1) FROM EMAIL;
display name in email
select substr(id, 1, instr(id, '@')-1) from email;
13) Salary of highest 2,4,5,7,9,10
 SELECT ID,NAME,SAL,R FROM(SELECT EMPNO,ENAME,SAL,DENSE_RANK() OVER(ORDER BY SAL DESC) R FROM EMP)WHERE R in (2,4,5,7,9,10);`
14)chop string into fname,mname,lname
select substr(N,1,(instr(N,' ')-1)) as Firstname,
substr(N,instr(N,' ',1,1)+1,instr(N,' ',1,2) - instr(N,' ',1,1)-1) as middlename,
substr(N,instr(N,' ',1,2)) as lastname from name;

15)columns to Rows
SELECT id,
       CASE pivot WHEN 1 THEN sub1
                  WHEN 2 THEN sub2
                  WHEN 3 THEN sub3
                  ELSE NULL
       END sub
FROM   teachers,
       (SELECT rownum pivot from dual CONNECT BY LEVEL <=3)
16)Rows to columns
SELECT product_id,
       MAX(DECODE(pivot,1,product_name,NULL))
                  product_name_1,
       MAX(DECODE(pivot,2,product_name,NULL))
                  product_name_2,
       MAX(DECODE(pivot,3,product_name,NULL))
                  product_name_3
FROM
(
SELECT product_id,
       product_name,
    row_number() over (partition by product_id order by product_name) pivot
FROM   products
) a
GROUP BY product_id;
17)Sum of positive numbers and Negative Numbers
select sum(case when x>0 then x else 0 end) pos, sum(case when x<0 then x else 0 end) neg from D;