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