--> 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:
Post a Comment