Saturday, March 23, 2013

Oracle SQL Queries



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

--> Q:To delete the even number of records
A:delete  from emp 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 distinct sal from emp e where &n=
         (select count(distinct sal) from emp d where e.sal<=d.sal)

A2:select * from(select rownum r,e.* from
          (select  distinct sal from emp order by sal desc)e) where r=&n

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

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

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

--> Q:To select nth min sal from each department.
A:  select distinct sal from emp e where &n=
       (select count(distinct sal) from emp 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 by deptno order by sal desc) top_sal from emp)
                    where top_sal <=&n order by deptno,sal desc

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

--> Q:To create the duplicate table without data
A: create table emp_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 from emp where rowid=(select rowid from emp group by                     
rowid,rownum having rownum=&n)

A2:delete from emp 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' from emp d where d.deptno=e.deptno))

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

--> Q:To delete duplicate recoreds
A:delete from tabel 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  from emp

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

--> Q:To count the number of sundays,mondays...saturdays..tot_days in the current month
A:create table temp
      (
          num number(2)
       )
 insert into temp values(&num)
 insert the values into temp from 1 to 31. and execute the below 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
 from temp where num<=(select to_char(last_day(sysdate),’dd’) from dual)
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 from emp group by rowid,rownum having rownum          
      in(1,(select count(*) from emp)))

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

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

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

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

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

--> Q:To select n to nth record
A:select * from(select rownum r,e.* from emp 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:with maxsal as (select max(sal)  max_sal from emp),dept_max as
   (select deptno,max(sal) dept_maxsal from emp group by deptno)

select empno,ename,sal,(select max_sal from maxsal) maxsal,(select dept_maxsal from dept_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 the whole dept  and minimum salary  of the whole department and dept name of the employee

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

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

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

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

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

--> 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 FROM EMP_D WHERE EMPNO IN
     (SELECT EMPNO FROM EMP_D WHERE EMPNO IN
         (SELECT DISTINCT MGR FROM EMP_D WHERE MGR<>
                       (SELECT EMPNO FROM EMP_D WHERE
              ENAME='KING')) AND MGR=(SELECT EMPNO FROM EMP_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 FROM EMP44 E WHERE MGR=(SELECT EMPNO FROM EMP44 D WHERE ENAME='KING' AND HIREDATE>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 FROM EMP44 WHERE EMPNO  IN(SELECT  EMPNO FROM EMP44 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 FROM EMP44 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 FROM EMP44 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(SELECT HIREDATE FROM EMP44 WHERE HIREDATE LIKE'%80'))

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

A2.delete from emp e where sal =(select min(sal) from emp d where d.deptno=e.deptno)

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

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

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

--> Q: Delete the employes whose experience greater than four.
A: delete from emp where hiredate in(select hiredate 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 where sal<(select sal from emp where empno=e.mgr)
And  sal> any(select sal from emp
where empno in(select distinct mgr from emp where mgr<>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 ) WHERE MGR_NAME='BLAKE'

--> Q:To find the employes who are seniour to all the other managers.
A:SELECT * FROM EMP E  WHERE HIREDATE < ALL(SELECT HIREDate from emp where empno 
 IN(SELECT DISTINCT MGR FROM EMP WHERE MGR <>
(SELECT EMPNO FROM EMP WHERE EMPNO=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 where hiredate <(select hiredate from emp where empno=e.mgr) and HIREDATE  > any(SELECT HIREDate from emp where empno   IN(SELECT DISTINCT MGR FROM EMP
 WHERE MGR <>(SELECT EMPNO FROM EMP 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      FROM EMP
Q:To find the employes who have joined on the same hiredate.
A: select*from emp where hiredate in(select hiredate from(select hiredate, count(hiredate) from emp group by hiredate
        having count(*)>1))

--> Q:To delete nth record.
A: delete from emp44 where rowid=(select rowid from emp44 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) from emp order  by sal)e)where r=&n

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

--> Q:To delete the duplicate records existing.
A: delete from emp_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 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  FROM EMP

--> Q:To select the duplicate records.
A: select e.*  from emp_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.* from emp  e connect by prior empno=mgr start
     with mgr  is null

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

--> Q: To incrementally update the table where mgr is null
A: update emp set empno=rownum where rowid in
(select rowid from emp  where comm is null)

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

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

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

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect