add

About Me

My photo
Oracle Apps - Techno Functional consultant

Saturday, March 23

Oracle SQL Queries



--> Q:To find the uniquness of the given record.
A1:select * from dual where 1=
         (select max(count(empno)) fromemp group byempno)
 A2: select 1yes_unique from emp
             having  count(distinct  sal)=count(sal)

--> Q:To delete the even number of records
A:delete  fromemp where rowid in(select rowid from
   emp group by rownum,rowid having mod(rownum,2)<>0)

--> Q:To find the nth max sal
A1: select distinctsal from emp e where&n=
         (select count(distinctsal) fromemp d where e.sal<=d.sal)

A2:select * from(select rownum r,e.* from
          (select  distinctsal from emp order bysal desc)e) where r=&n

--> Q:To find the nth min sal
A:  select distinctsal from emp e where&n=
      (select count(distinctsal) fromemp d where e.sal>=d.sal)

--> Q:To select nth max sal from each department.
A1: select * from(select rownum r,e.* from
        (select  distinctsal from emp order bysal)e) where  r=&n

A2:select distinctsal from emp e where&n=
       (select count(distinctsal) fromemp d where e.sal<=d.sal
           and e.deptno=d.deptno)

--> Q:To select nth min sal from each department.
A:  select distinctsal from emp e where&n=
       (select count(distinctsal) fromemp d where
              e.sal>=d.sal and e.deptno=d.deptno)

--> Q:TO select top n salarys from each department.
A: select * from(select empno,ename,sal,deptno,rank() over
             (partition bydeptno order bysal desc) top_sal fromemp)
                    wheretop_sal <=&n order bydeptno,sal desc

--> Q:To select bottom n salarys from each department
A:select * from(select empno,ename,sal,deptno,rank() over
(partition bydeptno order bysal ) bottom_sal fromemp)
              wherebottom_sal <=&n order bydeptno,sal

--> Q:To create the duplicate table without data
A: create tableemp_dup as select * from emp where 1=2

--> Q:what will be the output
    select * from emp where null is null
    select * from emp where null=null

--> Q:To delete nth record
A:Delete fromemp where rowid=(select rowid fromemp group by                     
rowid,rownum having rownum=&n)

A2:delete fromemp where rowid=(select max(r) from
          (select rowid r from emp where rownum<=&n))

--> Q:To delete a record where there are no employes working
A:delete from(select * from dept e where not exists
    (select 'x' fromemp d where d.deptno=e.deptno))

--> Q:To display the duplicate records
A:selectcol1,col2,col# ,count(*) from table group bycol1,col2,col# having       
count(*)>1

--> Q:To delete duplicate recoreds
A:delete fromtabel a where rowid in
     (select rowid from table b minus select max(rowid)
         from table c group by c.col1,c.col2,c.col#)

--> Q:To count the number of nulls and notnulls in Comm. column of emp table
A:select count(decode(nvl2(comm,1,2),1,1)) nulls,
   count(decode(nvl2(comm,1,2),2,1))not_nulls  fromemp

--> Q:To select 1st and last record of the employee table
A: select * from(select rownum r,e.* fromemp e)
     where r=(select count(*) fromemp) or r=1

--> Q:To count the number of sundays,mondays...saturdays..tot_days in the current month
A:create tabletemp
      (
          num number(2)
       )
 insert intotemp values(&num)
 insert the values intotemp from 1 to 31. and execute thebelow query.
 select 
count(decode(to_char(last_day(add_months(sysdate,-1))+num,'d'),1,1))sun,
count(decode(to_char(last_day(add_months(sysdate,-1))+num,'d'),2,1))mon,
count(decode(to_char(last_day(add_months(sysdate,-1))+num,'d'),3,1) )tue,
count(decode(to_char(last_day(add_months(sysdate,-1))+num,'d'),4,1) )wed,
count(decode(to_char(last_day(add_months(sysdate,-1))+num,'d'),5,1))thu,
count(decode(to_char(last_day(add_months(sysdate,-1))+num,'d'),6,1))fri,
count(decode(to_char(last_day(add_months(sysdate,-1))+num,'d'),7,1))sat,
to_char(last_day(sysdate),’dd’)tot_days
 fromtemp where num<=(select to_char(last_day(sysdate),’dd’) fromdual)
Output:
SUN      MON        TUE        WED        THU        FRI        SAT        TOTAL
---- ---------- ---------- ---------- ---------- ---------- ----------------------------------

   4              4               5               5               5           4              4             31

--> Q:To select first and last record of employee table
 A2: select * from emp where rowid in
      (select rowid fromemp group by rowid,rownum having rownum          
      in(1,(select count(*) fromemp)))

A3:select * from emp where rowid in
      (select min(rowid) fromemp union select max(rowid) fromemp)

--> Q:To display the second max sal
A:select max(sal) fromemp where sal<(select max(sal) fromemp)

--> Q:To display top n salarys
A:select * from(select rownum r,e.* from(select  distinctsal from emp order     
bysal desc) e) where r<=&n

--> Q:To select bottom n salarys
A:select * from(select rownum r,e.* from
(select  distinctsal from emp order bysal ) e) where r<=&n

--> Q:To select first and last salary of the table
A:select * from(select rownum r,e.* from
     (select  distinctsal from emp order bysal desc ) e)
         where r in(1,(select count(*) fromemp))

--> Q:To select n to nth record
A:select * from(select rownum r,e.* fromemp e)
        where r between 4 and 7

--> Q:Display empno,ename,sal,max(sal) of their own dept ,max(SAL) of  the emp table of all the employes using with clause.
A:withmaxsal as (select max(sal)  max_sal fromemp),dept_max as
   (selectdeptno,max(sal)dept_maxsal from emp group bydeptno)

selectempno,ename,sal,(selectmax_sal from maxsal)maxsal,(select dept_maxsal fromdept_max where
       dept_maxsal.deptno=e.deptno)dept_max from emp e

--> Q:To find the empno,ename,deptno,maximum sal of the whole table,
maximum salary of thewhole dept  and minimumsalary  of thewhole department and dept name of theemployee

A:selectempno,ename,sal,deptno,(select max(sal) fromemp)  max_sal_table,
     (select max(sal) fromemp where deptno=e.deptno)dept_max,
        (select min(sal) fromemp where deptno=e.deptno)min_sal,
        (selectdname from dept d where d.deptno=e.deptno)dname  fromemp e

--> Q:Display the employes whose salary is greater than his own manager
A:select*fromemp e wheresal>(select sal fromemp where empno=e.mgr)
 
--> Q:Disply the employes whose sal is greater than the other managers
A:  select * from emp e wheresal> any(selectsal from emp
whereempno in(select distinctmgr from emp wheremgr<>e.mgr))

--> Q:To  find the employes who are seniour to the employes who are joined in the year 1981.
A:SELECTHIREDATE FROM EMP WHEREHIREDATE<ALL
      (SELECTHIREDATE FROM EMP WHEREHIREDATE LIKE'%81')

--> Q:To fined the employes who have joined on the same hiredate
A:  SELECT * FROM EMP WHEREHIREDATE IN
     (SELECTHIREDATE FROM EMP GROUP BYHIREDATE HAVING  COUNT(HIREDATE)>1)

--> Q:To find the uniquness of the given column for the given table.
A: SELECT 1YES_UNIQUE FROM DUAL
      WHERE 1=(SELECT MAX(COUNT(EMPNO)) FROMEMP
     GROUP  BYEMPNO)

--> Q:To delete the managers who  are working under king with salary ranging from 2000 to 3000 and joined in first half of 1981

A:DELETE FROMEMP_D WHERE EMPNO IN
     (SELECTEMPNO FROM EMP_D WHEREEMPNO IN
         (SELECT DISTINCTMGR FROM EMP_D WHEREMGR<>
                       (SELECTEMPNO FROM EMP_D WHERE
              ENAME='KING')) ANDMGR=(SELECT EMPNO FROMEMP_D WHERE ENAME='KING'))
AND (SAL BETWEEN 2000 AND 3000) AND (HIREDATE BETWEEN '1-JAN-81' AND '31-MAY-81')

--> Q:To delete all the employes who have joined most recently under king.
A: DELETE FROMEMP44 E WHEREMGR=(SELECT EMPNO FROMEMP44 D WHERE ENAME='KING' ANDHIREDATE>E.HIREDATE)

--> Q:To delete all the grade 1 and grade 2 employes and woring in chicago and joined in the first half of 81.
A: SELECT E.EMPNO,E.ENAME,S.GRADE,D.LOC,E.HIREDATE FROM SALGRADE S,DEPT D, EMP E WHERE E.DEPTNO=D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL  AND S.GRADE IN(1,2) AND D.LOC='CHICAGO'
AND E.HIREDATE BETWEEN '01-JAN-81' AND '30-JUN-81'

--> Q:To delete all the employes with experience <4  and whose job is ending with 'man'
A: DELETE FROMEMP44 WHERE EMPNO  IN(SELECT  EMPNO FROMEMP44 WHERE
FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)>4
AND JOB LIKE '%MAN')

--> Q:Delete all the employes of sales department whose salary ranging from 1500 to 3000
A: DELETE FROMEMP44 WHERE EMPNO IN(select e.empno from emp44 e,dept d where e.deptno=d.deptno
     AND d.dname='SALES' AND E.SAL BETWEEN 1500 AND 3000)

--> Q:To delete all the grade 2 and 3 employes of sales dept and working at chicago and joined after the all the employes whose hiredate is in the year1980.
A:DELETE FROMEMP44 WHERE EMPNO IN(SELECT E.EMPNO FROM EMP44 E,DEPT D,SALGRADE S
WHERE E.DEPTNO=D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL
AND S.GRADE IN(2,3)
AND D.DNAME ='SALES'
AND D.LOC='CHICAGO'
AND
HIREDATE>ALL(SELECTHIREDATE FROM EMP44 WHEREHIREDATE LIKE'%80'))

--> Q: Delete the emps whose salary is lowest salary of their own deptartment
A: DELETE FROMEMP44 WHERE EMPNO IN
      (SELECTEMPNO FROM EMP44  WHERESAL IN
      (SELECT MIN(SAL)
FROMEMP44 GROUP BYDEPTNO))

A2.delete fromemp e wheresal =(select min(sal) fromemp d where d.deptno=e.deptno)

--> Q: To find the employes who are seniour to their own managers.
A:  SELECT * FROM EMP  E WHEREHIREDATE<
      (SELECT  HIREDATE FROMEMP WHERE EMPNO=E.MGR)

--> Q:To find the employes who are seniour to their own managers using corelated sub query.
A: DELETE FROMEMP44 WHERE EMPNO IN
      (SELECT E.EMPNO FROM EMP44  E WHERE
     HIREDATE< (SELECTHIREDATE FROM EMP WHEREEMPNO=E.MGR))

--> Q:To find the employes who are seniour to their own manager using self join.
A: SELECT E.* FROMEMP E,EMP D WHERE E.MGR=D.EMPNO AND E.HIREDATE<D.HIREDATE

--> Q: Delete the employes whose experience greater than four.
A: delete fromemp where hiredate in(selecthiredate from emp where (months_between(sysdate,hiredate)/12)>4)

--> Q: To list the employes whose salary is less than his own manages and   greater than any of the other managers.
A: select * from emp e wheresal<(select sal fromemp where empno=e.mgr)
And  sal> any(selectsal from emp
whereempno in(select distinctmgr from emp wheremgr<>e.mgr))

--> Q:To display the employee name and this manager name for each record selected.
A: SELECT E.EMPNO,E.ENAME,D.ENAME MGR_NAME FROM EMP E,EMP D WHERE D.EMPNO=E.MGR

--> Q:To display all the employes who are working under blake and display the manager name along with the select list.
A: SELECT * FROM(SELECT E.*,D.ENAME MGR_NAME FROM EMP E,EMP D WHERE E.MGR=D.EMPNO ) WHEREMGR_NAME='BLAKE'

--> Q:To find the employes who are seniour to all the other managers.
A:SELECT * FROM EMP E  WHEREHIREDATE < ALL(SELECTHIREDate from emp whereempno 
 IN(SELECT DISTINCTMGR FROM EMP WHEREMGR <>
(SELECTEMPNO FROM EMP WHEREEMPNO=E.MGR)))

--> Q:To count the m's and f's from the column and display in the same row like
A:SELECT COUNT(DECODE(GENDER1,'M',1)) M, COUNT(DECODE(GENDER1,'F',1)) F FROM GENDER

--> Q:To find the emps who are senior to his own manager and junior to any one of the other managers
A: select * from emp e wherehiredate <(select hiredate fromemp where empno=e.mgr) andHIREDATE  > any(SELECTHIREDate from emp whereempno   IN(SELECT DISTINCTMGR FROM EMP
 WHEREMGR <>(SELECT EMPNO FROMEMP WHERE   EMPNO=E.MGR)))

--> Q:To convert the given number into string format.
A:SELECT TO_CHAR(TO_DATE(SAL,'J'),'JSP')SAL_STRING,SAL      FROMEMP
Q:Tofind the employes who have joined on thesame hiredate.
A: select*fromemp where hiredate in(selecthiredate from(select hiredate, count(hiredate) fromemp group byhiredate
        having count(*)>1))

--> Q:To delete nth record.
A: delete fromemp44 where rowid=(select rowid fromemp44 group by rowid,rownum having rownum=&n)

--> Q:To find the uniqueness of any given cloumn in a given table.
A: SELECT CASE
WHEN COUNT( DISTINCT&COLUMN)=COUNT(*)
 THEN
'YES_UNIQUE'
ELSE
'NOT_UNIQUE'
END CASE
FROM&TABLE

--> Q:To find nth minimum salary.
A:select * from(select rownum r,e.* from
(select distinct(sal) fromemp order  bysal)e)where r=&n

--> Q:To find nth minimum salary.
A: select * from(select rownum r,e.* from
    (select distinct(sal) fromemp order bysal)e)where r=&n

--> Q:To delete the duplicate records existing.
A: delete fromemp_dups e where rowid<(select max(rowid) fromemp_dups d where
e.empno=d.empno and e.ename=d.ename and e.sal=d.sal and e.hiredate=d.hiredate
and e.comm=d.comm and e.mgr=d.mgr and e.deptno=d.deptno)

--> Q:To count the number of nulls and notnulls.
A:SELECT COUNT(*)-COUNT(DECODE(NVL(COMM,1),1,1))NOT_NULLS,
COUNT(DECODE(NVL(COMM,1),1,1)) NULLS  FROMEMP

--> Q:To select the duplicate records.
A: select e.*  fromemp_dups e where rowid<(select max(rowid) from   emp_dups d where
e.empno=d.empno and e.ename=d.ename and e.sal=d.sal and e.hiredate=d.hiredate
and e.comm=d.comm and e.mgr=d.mgr and e.deptno=d.deptno)

--> Q:To find the hirearchey of the employee table
A; select level, e.* fromemp  e connect by priorempno=mgr start
     withmgr  is null

--> Q:  To incrimentally update the employee table from nth row
A: updateemp set empno=rownum+999 where rowid >
    (select max(rowid) fromemp where
     rownum<=&n)

--> Q: To incrementally update the table where mgr is null
A: updateemp set empno=rownum where rowid in
(select rowid fromemp  wherecomm is null)

--> Q:To select from nth record to nth record.
A: select * from(select rownum r,e.* fromemp  e)
    where r between &n and&m

--> Q:To select the given records
A:select * from(select rownum r,e.* fromemp  e)
                        where r in(3,5,6)

--> Q:TO delete the nth record
A:delete fromemp where rowid=
(select rowid fromemp group by rownum,rowid
                   having rownum=&n )

--> Q:TO delete the given set of records
A:delete fromemp where rowid in
            (select rowid fromemp group by rownum,rowid
                  having rownum  in (4,5,6) )

No comments: