1--DISPLAY types of jobs in emp ?

select distinct(job) from emp

2--DISPALY EMPLOYEE SAL > TWO TIMES OF HIS COMM ?

select * from emp where sal>2*comm

3--display where sal in comm in emp no--?

select * from emp where length (sal)!= length(replace(sal,deptno))

4--display even emp no ?

select * from emp where mod(empno,2)=0

5--76 hours in (3 days ,4 hours) ?

select trunc(76/24)||'days'||mod(76,24)||'hours' from dual

6--last date of hiredate ?

select * from emp where last_day(to_char(hiredate,'mm')= to_char(hiredate,'d')

--or--
select * from emp where hiredate = last_day(hiredate)

7--update comm if comm is ther then sal*20/100,if comm is null then 5000 ?

update emp set comm = nvl2(comm,sal*20/100,5000)

--OR--
update emp set comm=nvl(comm*comm*20/100,2000)

8--display yesterday joined emp details ?

select * from emp where trunc(hiredate) = trunc(sysdate-1)

9--which emp joined week ,month or year begining ?

select * from EMP WHERE TO_CHAR(HIREDATE,'D')=1 OR TO_CHAR(HIREDATE,'DD')=1 OR

TO_CHAR(HIREDATE,'DDDD')=1

10--FIND LAST SAT OF THIS MONTH ?

SELECT (NEXT_DAY(LAST_DAY(SYSDATE),'SAT')-7) FROM DUAL

--OR--
select (to_char(last_day(sysdate),'d')) from dual

11--find how many days for current year ?

select add_months(trunc(sysdate,'rrrr'),12)- trunc(sysdate,'rrrr') from dual

12--wich emp r completinp 50years of exp in year of 2035 feb 10th 10:10min ?

select (months_between(hiredate,to_date('10-feb-2035','dd-mm-yyyy'))/12) exp from

emp where months_between(hiredate,to_date('10-feb-2035','dd-mm-yyyy'))/12 >50

13--if deptno :-10-1000,20-2000,30-3000 ?

select comm,nvl2(comm,sal*20/100,case when deptno =10 then 1000 when deptno=20

then 2000 when deptno=30 then 3000 end ) from emp
--or--
select comm,nvl2(comm,sal*20/100,decode(deptno,10,1000,20,2000,30,3000) )from emp

14--display year wise how many empes joined ?

select count(*),to_char(hiredate,'yyyy') from emp group by

to_char(hiredate,'yyyy')

15-- most emp joined year ?

select count(*),to_char(hiredate,'yyyy') from emp group by

to_char(hiredate,'yyyy')having count(*) =(select max(count(*)) from emp group by
to_char(hiredate,'rrrr'))

16--display depart details whish have more empl ?

select *from dept where deptno in(select deptno from emp group by deptno having
count(*) in (select max(count(*))from emp group by deptno))

17--display manager name , which manager have more employes ?

select m.ename,count(*) from emp w,emp m where w.mgr=m.empno group by m.ename

having count(*)= (select max(cu) from (select count(*) cu from emp group by
mgr)a)
--or--
select * from emp where empno in(select mgr from emp group by mgr having
count(*)in(select max(count(*)) from emp group by mgr))

18-- name of duplicate sal emp name ?

select * from emp where sal in(select sal from emp group by sal having count(*)
in (select max(count(*)) from emp group by sal))

19--display comm is null dept wide ?

select * from emp where comm is null

20--display only char from the string 'bh@as#kar$123' ?

select regexp_replace(regexp_replace('bh@as#kar$123','\W'),'\d') from dual

21--display emp name which have 3L's ?

select * from emp where (length(replace(ename,'L'))-length(ename))=-2

22--'gnana it solutions', find no of I's in given string ?

select length(replace('gnana it solutions','i'))-
length('gnana it solutions') from dual

23--to display first and last char in between *** should be print ?

select substr(ename,1,1)||lpad('*',length(ename)-2,'*')||substr(ename,-1) from

emp

24--update emp comm ,if emp getting comm give same value, if not give 1000 ?

update emp set comm=nvl(comm,1000)

25--display emp details which emp having %char ?

select * from emp where ename like '%/%%'escape'/';

26--display emp details emp sal should be in 1000's ?

select ename,sal from emp where sal like '%000'

27--give the level for each row like rownum ?

select level from dual connect by level<11

28--display all even number below 100 ?

select level from dual where mod(level,2)=0 connect by level<100

--or---
select * from (select case when mod(level,2)=0
then level end even from dual connect by level<=100)where even is not null

29--print all dates of current year ?

select trunc(sysdate,'yyyy')+(level-1) from dual

connect BY level<=(add_MONTHS(TRUNC(SYSDATE,'YYYY'),12)-TRUNC(SYSDATE,'RRRR'));

30--print all sundays and sat day of year ?

select trunc(sysdate,'yyyy')+(level-1) from dual where

to_char(trunc(sysdate,'yyyy')+(level-1),'d') in (1,7)
connect by level<=(add_MONTHS(TRUNC(SYSDATE,'YYYY'),12)-TRUNC(SYSDATE,'RRRR'));
---or----
select * from (select case when to_char(trunc(sysdate,'yyyy')+
(level-1),'d') in (1,7) then trunc(sysdate,'yyyy')+(level-1) end
day from dual connect by level<=(add_MONTHS(TRUNC(SYSDATE,'YYYY'),12)
-TRUNC(SYSDATE,'RRRR'))) where day is not null

31--to display month 1st in one col and month last in one col ?

select add_months(trunc(sysdate,'rrrr'),(level-1))mon_beg,
last_day(add_months(trunc(sysdate,'yyyy'),(level-1)))mon_end from dual
connect by level<=12

32--display string('WATERMELON')in horizontal to vertical and vertical to

horizontal ?
--vertical--
SELECT SUBSTR('WATERMELON',LEVEL,1) FROM DUAL CONNECT BY LEVEL
<=LENGTH('WATERMELON');
--horizontal--
SELECT REPLACE (wm_concat(vs.wm),',')horizontal_string FROM (
SELECT SUBSTR('WATERMELON',LEVEL,1) wm FROM DUAL CONNECT BY LEVEL
<=LENGTH('WATERMELON'))vs;

33--display 10 depart employs, if there salary is more

--than any emp salay in 20 department ?

select * from emp where deptno=10 and sal>(select min(sal) from emp where
deptno=20)
--OR--
select * from emp where deptno=10 and sal>some(select sal from emp where
deptno=20)

34--display higest sal and least salary emp list ?

select * from emp where sal in

(select max(sal) from emp union select min(sal) from emp)
--OR--
select * from emp where sal in ((select max(sal) from emp),
(select min(sal) from emp))

35--write a query to display india first and remaining counts in alpa order ?

create table temp(x varchar2(30))

select * from temp for update

select * from temp where x='india' union all

(select * from (select * from temp where x<>'india' order by x))

36-- display emp details which emp is manager of any other employ ?

select * from emp where empno in (select mgr from emp)

37--display emp details , which should not be mgr for any other employs
-- in not in case if sub query return null value , outer query will not work ?

select * from emp where empno not in (select nvl(mgr,0) from emp)

select * from emp where empno not in (select mgr from emp where mgr is not null)

38--display dept details which dept sum(sal) more than other deptn ?

select * from dept where deptno in(select deptno from emp group by deptno having
sum(sal)in
(select max(sum(sal)) from emp group by deptno))
39-- display emp which are working as james in manager departnent ?

SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE EMPNO=
(SELECT MGR FROM EMP WHERE ENAME='JAMES'))

40--DISPLAY NO OF 0 IN SALARY COLUMN ?

select sum(length(replace (sal,0)))-sum(length(sal)) from emp

50-- DISPLAY WHICH EMP SAL IS TOP BY DEPARTMENT WISE ?

SELECT * FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO)

51--display aLLen emp manager'S ,MANAGER name ?

SELECT * FROM EMP WHERE EMPNO=(SELECT MGR FROM EMP WHERE EMPNO=(select mgr from
emp where ename='ALLEN'))

52--10% SAL IS PF , DISPLAY WHICH EMP GETTING 300 PF ?

SELECT X.* FROM(SELECT ENAME,EMPNO ,SAL*10/100 PF FROM EMP)X

WHERE PF=300

53-- DISPLAY EMP WHICH EMP JOINED BEFORE MANAGER JOINED ?

SELECT * FROM EMP E,EMP M WHERE E.MGR=M.EMPNO AND E.HIREDATE<M.HIREDATE

54--DISPLAY WHICH EMP GETTING MORE THAN MANAGER SAL ?

SELECT * FROM EMP E,EMP M WHERE E.MGR=M.EMPNO AND E.SAL>M.SAL

55--DISPLAY DEPARTWISE HIEGEST WISE ?

select * from emp e where e.sal in

(select max(sal) from emp d where e.deptno=d.deptno group by deptno)

56--display emp getting more than manager sal ?

select * from emp e where (e.ename,e.empno) in (select e.ename,e.empno from emp e

, emp m where e.mgr=m.empno and e.sal>m.sal)

57--depart wise max sal ?

select * from emp where sal in (select max(sal) from emp group by deptno)

58--depart wise third hiesr sal emp details ?

select * from (select e.*,(dense_rank() over(partition by deptno order by sal

desc ))dsk from emp e)x where dsk=3

59--dispaly table name, column name,created date , coloum position, data type ,
-- precision,scale ?
select c.TABLE_NAME,u.CREATED,c.COLUMN_NAME,c.DATA_TYPE,
nvl(c.DATA_PRECISION, c.DATA_LENGTH) PRECISION,
nvl(c.DATA_PRECISION, c.DATA_LENGTH),nvl(c.DATA_SCALE, 0) scale
from cols c, user_objects u where c.TABLE_NAME = u.OBJECT_NAME

60--DISPLAY WHICH OBJECTS ARE MODIFIED YESTERDAY ?

select * from USER_OBJECTS U WHERE TRUNC(U.LAST_DDL_TIME)=TRUNC(SYSDATE-1)

60--DISPLAY YESTERDAY CREATED OBJECTS ?

select * from USER_OBJECTS U WHERE TRUNC(U.CREATED)=TRUNC(SYSDATE-1)

61--display which objects on depended on tables ?

select * from all_dependencies a where A.REFERENCED_NAME='BHASKAR'

select * from user_tables and select * from USER_OBJECTS

62-- TO DISPLAY INVALID OBJECTS ?

select * from user_objects where STATUS = 'invalid'

63-- TO COMPAIL INVALID OBJECTS ?

begin
DBMS_UTILITY.compile_schema('bhaskar');
END;

64-- check cursor or bulk collect ?

select * from all_source A WHERE A.TEXT LIKE '%bulk collect%' AND

A.OWNER = 'BHASKAR'

65-- TO GET CONSTRAINS LIST ?

select * from USER_CONSTRAINTS WHERE CONSTRAINT_TYPE IN ('R','P','F','U')

66--DISPLAY ALL FOREIGN KEYS LIST DEPENDING ON PRIMEARY KEY ?

select * from USER_CONSTRAINTS C WHERE C.R_CONSTRAINT_NAME='PK_B_ID'

67-- dispaly last column from each table in schema ?

select c.column_id,c.table_name from cols c where (c.column_id,c.table_name) in

(select max(column_id),table_name from cols group by table_name)

68--table name , coloum name which does have constrains ?

select * from cols c where (c.table_name,c.column_name) not in

( select table_name,column_name from user_cons_columns)

69-- which emp joined in same month ?

select * from emp where trunc(hiredate,'mm')=trunc(sysdate,'mm')

70--difference between dense_rank , rank,row_num ?

select dense_rank()over(order by sal),rank()over(order by sal),

row_number()over(order by sal) from emp e

select ename,sal,dense_rank()over(partition by deptno order by sal) from emp

71-- DISPLAY 6:00 PM TO 6:00 AM JONED EMP ?

SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'HH24')>=6 OR

TO_CHAR(HIREDATE,'HH24')<6;

72--COUNT ON EMP TABLE ?

SELECT COUNT(1),COUNT(*),COUNT(ROWID) FROM EMP;

SELECT COUNT(1),COUNT(*),COUNT(ROWID) FROM TEMP5;

73-- SUM OF ROWS ON TABLE ?

SELECT SUM(10) FROM EMP;

74-- DISPLAY MAX SAL AND MIN SAL EMP DETAILS BY ROW WISE ?

select * from EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP

UNION select MIN(SAL) from EMP)

75--display the name of emp who earn high sal ?

select * from emp where sal in (select max(sal) from emp)

76--display empee number and name for emp working as clerk and
--earning high sal among the clerks ?

select ename,empno,JOB from emp where job='CLERK' and sal in(select max(sal)
from emp where job='CLERK')

77--display name of salesmen who earns salary more than the highest salary
of the clerk ?

select ename,job from emp where job='SALESMAN' and sal >

(select max(sal) from emp where job='CLERK')

78-display name of CLERK who earns salary more than the lowest salary of the
SALESMEN?

select ename from emp where job='CLERK' and sal>(select min(sal) from emp where
job='SALESMEN')

79--display the names of emp who earns a salary more than of jones or greater
than that of scott?
 select ENAME from emp where sal >
(select MAX(SAL) from emp where ename IN ('JONES', 'SCOTT'))

80--DISPLAY TOP SAL IN respective department ?

select * from emp where sal in (select max(sal) from emp group by deptno)

81--DISPLAY TOP SAL IN respective job groups ?

select * from emp where sal in (select max(sal) from emp group by job)

82--display emp who working in account dept ?

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and

d.dname= 'ACCOUNTING'

83--display emp who working in CHICAGO ?

select e.ename,d.LOC from emp e,dept d where e.deptno=d.deptno AND

d.loc='CHICAGO'

84--DISPLAY JOB GROUPS HAVING TOTAL SAL GREATER THAN THE MAXIMUM SALARY FOR
MANAGER?

(select JOB from emp e group by e.job having sum(e.sal) >

(select max(m.sal) from emp m , emp e where e.mgr=m.empno))
--OR---
select JOB from emp e group by job having sum(e.sal)>
(select mAx(sal) from emp where job='MANAGER')

85--display names of employee from department no 10 with sal greather than

--that of any employee working in other deptment?

select ename from emp where deptno=10 and sal> any

(select sal from emp where deptno not in 10)
--OR--
select e1.ename from emp e1 where e1.deptno=10 and e1.sal> any
(select e2.sal from emp e2 where e1.deptno <> e2.deptno )

86--display the names of emp from depment no 10 with sal greather than of all
employee working in other deptmoent ?

select ename from emp where deptno=10 and sal> all

(select sal from emp where deptno not in 10)
--OR--
select e1.ename from emp e1 where e1.deptno=10 and e1.sal> all
(select e2.sal from emp e2 where e1.deptno <> e2.deptno )

87--display names of employeee in upper case ?

select upper(ename) from emp

88--display names of employeee in lower case ?

select lower(ename) from emp

89--display name in proper case ?

select initcap(ename) from emp

90--display length of name ?

select length(ename) from emp

91--display name of emp comcate with empno ?

select ename||empno from emp

92--use appropirate function and extract 3 char starting from 2 char

-- for the following string'oracle' ?

select substr('oracle',3,2) from dual

93--find first char 'a' from the srting 'bhaskar' ?

select instr('bhaskar','a',1,1) from dual

94-- replace a with b in 'bhaskar' ?

select replace('bhaskar','a','b') from dual

95-- manager as boss in emp table ?

select replace (job,'MANAGER','BOSS') FROM EMP

96--DISPALY EMPNO,ENAME,DEPTN FROM EMP TABLE INSTED OF DISPALY DEPARTMENT NUMBERS

DISPALY ?

- FOR RELATED DEPARTMENT NAME(USE DECODE FUNCTION)

SELECT EMPNO,ENAME,DECODE(DEPTNO,10,'AA',20,'BB',30,'CC') FROM EMP

97--DISPLAY YOUR AGE IN DAYS ?

SELECT (TO_DATE('08/01/1997','DD/MM/YYYY'))-SYSDATE FROM DUAL

98--DISPLAY YOUR AGE IN MONTHS ?

SELECT MONTHS_BETWEEN(TO_DATE('08/01/1997','DD/MM/YYYY'),SYSDATE) FROM DUAL

99--DISPLAY CURRENT DATE AS 15TH AUGUST FRIDAY NINETEEN NINETY SEVEN ?

select to_char(sysdate,'ddth month day year') from dual

100--get last day of current month ?

select last_day(sysdate) from dual

101--display all the records of emp as following format

--"scott has joined the vcompany on 13th august nunteen ninety" ?

select ename|| ' has joined the company on' ||

to_char(hiredate,'ddth month day year') from emp

102--next sat after current date ?

select next_day(sysdate,'sat') from dual

103--dispaly current time ?

select to_char(sysdate,'hh:mi:ss') from dual

104-- dispaly three months before current date ?

select add_months(sysdate,-3) from dual

105--dispaly common jobs from dept no 10 and 20 ?

select job from emp where deptno=10 intersect

select job from emp where deptno=20

106--eliminate dublicate jobs that are common in deptno 10 and 20 ?

select job from emp where deptno=10 union select job from emp where deptno=20
minus (select job from emp where deptno=10 intersect
select job from emp where deptno=20)

107--display jobs who are unique in depart = 10 ?

select job,deptno, count(job) from emp group by job,deptno having count(job)=1

and deptno=10
--OR--
select job from emp where deptno=10 minus select job from emp where deptno!=10

108-- display emp who do not have any person working under him ?

select * from emp where empno not in (select mgr from emp where mgr is not null)

109--dispaly emp who are in sales department and grade is 3?

select s.grade,e.sal,e.ename from emp e , salgrade s where job = 'SALESMEN' and

s.grade=3

110--DISPLAY THOSE WHO ARE NOT MANAGERS ?

SELECT * FROM EMP WHERE JOB<>'MANAGER'

111--DISPLAY EMP NAME WHO NAME CONTAINS NOT LESS THAN 4 CHAR ?
SELECT * FROM EMP WHERE LENGTH(ENAME)>= 4

112--NAME OF EMP WHOSE NAME START WITH S AND LOC ENDS WITH K ?

SELECT E.ENAME,D.LOC FROM EMP E , DEPT_INFO D WHERE E.ENAME LIKE 'S%'

AND D.LOC LIKE '%K'

113--DISPLAY EMP WHOSE MGR NAME IS JONES ?

SELECT * FROM EMP E , EMP M WHERE E.MGR=M.EMPNO AND M.ENAME='JONES'

114-- DISPALY EMP WHOSE SAL = 3000 AFTER GIVING 20% INCREMENT ?

SELECT * FROM EMP WHERE (SAL*20/100)<3000

115--DISPLAY ALL EMP WITH THEIR DEPART NAMES ?

SELECT E.ENAME,D.DNAME FROM EMP E , DEPT_INFO D WHERE E.DEPTNO=D.DEPTNO

116-- DISPLAY EMP WHO ARE WORKING IN SALES DEPARTMENT ?

SELECT E.ENAME,D.DNAME FROM EMP E , DEPT_INFO D WHERE E.DEPTNO=D.DEPTNO AND

D.DNAME='SALES'

117--DISPAY EMP ,EMPNO,DEPtNO,COMM WHOSE SAL BETWEEN 2000-5000 AND LOC IN CHICAGO
?

SELECT E.ENAME,E.EMPNO,E.DEPTNO,E.COMM FROM EMP E,DEPT_INFO D WHERE

E.DEPTNO=D.DEPTNO AND D.LOC='CHICAGO' AND E.SAL BETWEEN 2000 AND 5000

118--DISPLAY WHOSE EMP SAL > MGR SAL ?

select * from EMP E, EMP M WHERE E.MGR=M.EMPNO AND E.SAL>M.SAL

119--DISPLAY EMP WHO WORKS SAME DEPT WHERE HIS MANAGER IS WORKS ?

SELECT * FROM EMP E , EMP M WHERE E.MGR=M.EMPNO AND E.DEPTNO = M.DEPTNO

120--DISPALY MGR WHO R NOT WORKING UNDER ANY MANAGER ?

SELECT * FROM EMP WHERE MGR IS NULL

121--DISPLAY THE GRADE AND EMPEE NAME FOR DEPTNO 10 OR 30 BUT GRADE IS NOT 4
WHILE
--JOINED THE COMPANY BEFORE 21-DEC-82?

SELECT S.GRADE,E.ENAME FROM EMP E, SALGRADE S WHERE E.SAL>=S.LOSAL AND

E.SAL<=S.HISAL AND E.DEPTNO IN(10,30) AND S.GRADE<>4 AND HIREDATE <'31/DEC/82'
--OR--
SELECT E.ENAME,E.SAL,E.DEPTNO FROM EMP E ,SALGRADE S WHERE
E.SAL>=S.LOSAL AND E.SAL<=S.HISAL AND
E.DEPTNO IN(10,30) AND S.GRADE<>4 AND E.HIREDATE<'31-DEC-1982'
122--UPDATE SAL 10 INCREMENT WHERE COM IS NULL ?

UPDATE EMP SET SAL=SAL*10/100 WHERE COMM IS NULL

123--DELETE EMP WHO JOINED BEFOR 31-DEC-82 WHILE DEPT LOC NEW YORK AND CHICAGO ?

DELETE FROM EMP WHERE HIREDATE>'31-DEC-82' AND DEPTNO IN (SELECT D.DEPTNO FROM
DEPT_INFO D WHERE D.LOC IN ('CHICAGO','NEW YORK')

124--display job , dept name, ename, loc, for all who are working as manager ?

select e.ename,e.job,d.dname,d.loc from emp e , dept_info d

where e.deptno=d.deptno and E.JOB = 'MANAGER'
--OR--
select e.ename,e.job,d.dname,d.loc from emp e , dept_info d
where e.deptno=d.deptno AND E.EMPNO IN (SELECT MGR FROM EMP WHERE MGR IS NOT
NULL)

125--display name and salary of employee whose salary is equal to hisal of

--grade of ford ?

select e.ename,e.sal,s.grade from emp e , salgrade s where e.sal

between s.losal and s.hisal and e.ename='FORD' AND
e.sal in (select s.hisal from salgrade s )
--OR--
select e1.ename from emp e1 where e1.sal=(select s.hisal from salgrade s ,
emp e where e.ename='FORD' and e.sal>=s.losal and e.sal<=s.hisal)

126--display employee name, job, dept name , his manager name and his grade
--.display department wise?

select e.ename,e.job,d.dname,m.ename,s.grade from emp e,emp m,dept_info

d,salgrade s
where e.mgr=m.empno
and e.deptno=d.deptno and e.sal between s.losal and s.hisal order by dname

127--list out all the employee name,job ,sal ,salary,grade and dept name for
--every one in a company expect 'clerk' .sort on salary display the highest
salary?

select e.ename,e.sal,s.grade,d.dname from emp e , salgrade s,dept_info d

where e.deptno=d.deptno and e.job <> 'CLERK' AND e.sal between
s.losal and s.hisal order by e.sal desc

128--display employee name,job and his manager .display also employees who are
--without manager?

select e.ename,e.job,nvl2(e.mgr,m.ename,'no manager') from emp e,emp m

where m.empno(+)=e.mgr
129--display top 5 earners ?

select x.* from (select ename,empno,rank()over(order by sal desc)rank from emp)x

where rank<=5

130--display names of emp who are getting high salary ?

select * from emp where sal =(select max(sal) from emp )

131--display those employee whose salary is equal to average of max and min sal ?

select ename,empno from emp where sal=(select (max(sal)+min(sal))/2 from emp)

132--select count of emp in each department where count>3 ?

select deptno,count(*) from emp group by deptno having count(*)>3

133--display dname where atlest three are working and display only deptno ?

select dname from dept_info where deptno in

(select deptno from emp group by deptno having count(deptno)>=3)

134--dispalay names of those manager whose salary is more than average

-- salary of company ?

select m.ename,m.sal from emp m where m.job='MANAGER' AND

m.sal>(select avg(sal) from emp )

135--DISPLAY NAMES OF MANAGER WHOSE SAL IS MORE THAN AVERAGE OF HIS EMPLOYEES?

SELECT E.ENAME FROM EMP E WHERE E.SAL > (SELECT AVG(E1.SAL) FROM EMP E1
WHERE E1.MGR=E.EMPNO)

136--DISPALY EMP NAME,SAL,COMM,NETPLAY FOR THOSE EMPLOYEE WHOSE NETPLAY IS

--GREATHER THAN OR EQUAL TO ANY OTHER EMPLOYEE SALARY OF THE COMPANY ?

SELECT X.* FROM

(SELECT ENAME ,SAL ,COMM,(SAL+NVL(COMM,0)*12) NETPAY FROM EMP ) X
WHERE NETPAY >= SAL

137-- DISPLAY THOSE EMP WHOSE SALARY IS LESS THAN HIS MANAGER BUT MORE THAN
-- SALARY OF OTHER MANAGERS ?

SELECT E.ENAME,E.SAL,M.ENAME,M.SAL FROM EMP E , EMP M WHERE E.MGR=M.EMPNO

AND E.SAL<e.Mgr and e.sal> any e.mgr
--OR--
select * from emp e where e.sal<(select m.sal FROM EMP M
WHERE E.MGR=M.EMPNO) and e.sal > any (select m.sal from emp m where
m.empno in (select e.mgr from emp e))
138--display all the emp name with total sal of company with each emp name ?

select ename,(select sum(sal) from emp ) from emp

139--find least 5 emp of company on salary base ?

select x.* from(select ename,rank()over(order by sal )rank from emp)x where

rank<=5

140-- find emp whose sal is greater than manager ?

select * FROM EMP E , EMP M WHERE E.MGR=M.EMPNO and e.sal>m.sal

141--display manager who are not working under president but they are working
under
--any other manager?

SELECT E.ENAME,E.EMPNO,M.ENAME FROM EMP E , EMP M WHERE E.MGR=M.EMPNO AND

M.ENAME<> (SELECT ENAME FROM EMP WHERE JOB='PRESIDENT')

142--DELETE THOSE DEPTNO WHERE NO EMPLOYEE WORKING ?

DELETE FROM DEPT_INFO WHERE DEPTNO NOT IN (select DEPTNO from EMP )

143--DELETE THOSE RECORDS FROM EMP TABLE WHOSE DEPTNO NOT AVALIABLE IN DEPT TABLE
?

DELETE FROM EMP WHERE DEPTNO NOT IN (SELECT DEPTNO FROM DEPT_INFO)

144--DISPLAY THOSE NAMES WHOSE SALARY IS OUT OF GRADE AVALIABLE IN SALGRADE TABLE
?

SELECT ENAME FROM EMP WHERE SAL < (SELECT MIN(LOSAL) FROM SALGRADE)
OR SAL>(SELECT MAX(HISAL) FROM SALGRADE)

145--DISPALY EMPL NAME,SAL,COMM AND WHOLE NETPLAY IS GREATER THAN

--ANY OTHER EMPEE IN THE COMPANY ?

SELECT X.* FROM(SELECT ENAME,SAL,COMM,((SAL+NVL(COMM,0))*12) NETPAY FROM EMP )X

WHERE NETPAY >ANY SAL
--or--
SELECT ENAME,SAL,COMM FROM EMP WHERE (SAL+NVL(COMM,0)*12)>ANY(SAL+NVL(COMM,0)*12)

146--diaplay name of those empee who are going to retire by

--31-dec-99 if maxmum job period is 30 years?

select ename from emp where add_months(hiredate,12*30)<='31-dec-99'

147--display thosee employee whhose empno is odd value ?

select * from emp where mod(empno,2)<>0

148--display those employeee whose sal contains atlest 3 digits ?

select * from emp where length(sal)=3

149--dispaly empee ewho joinedd in company in the month dec ?

select * from emp where to_char(hiredate,'mon')='dec'

150--display those employee whose name contain A?

select * from emp where ename like '%A%'

151--display whose empee sal avliable in deptno ?

select * from emp where length (sal)!= length(replace(sal,deptno))

152--dispaly emp whose sal is less than (first 2 characters from hiredate
--combined with last 2 characters of sal) ?

select * from emp where substr(sal,1,2)<to_char(hiredate,'dd')||substr(sal,-2)

--OR--
select * from emp where sal<(substr(hiredate,1,2)||substr(sal,-2,2))

153--dispaly those emploo whose 10% of salay is equal to the year joining ?

select * from emp where sal*10/100=to_char(hiredate,'yy')

154--display the empeee who working in sales or research ?

select * from emp e WHERE DEPTNO IN (select D.DEPTNO from dept_info d where
e.deptno=d.deptno and d.dname in ('SALES','RESEARCH') )

155--DISPLAY GRADE OF JONES ?

select E.ENAME,S.GRADE from EMP E ,SALGRADE S WHERE E.ENAME = 'JONES' AND E.SAL
BETWEEN S.LOSAL AND S.HISAL

156--DISPLAY EMPEE WHO JOINED THE COMPANY BEFORE 15TH OF THE MONTH ?

SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'DD')<= 15

157--DISPALY EMPEE WHO JOINED BETWEEN 15TH AND 20TH OF THE MONTH?

SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'DD') BETWEEN 15 AND 20

158--DELETE ETHOSE RECORDS WHERE NO OF EMP IN PARTICULAR DEPARTMENT IS

-- LESS THAN 3 ?

DELETE FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT_INFO GROUP BY
DEPTNO HAVING COUNT(DEPTNO)<3)

159--DELETE FROM EMP WHO JOINED THE COMPANY 10 YEARS BACK FROM TODAY ?

DELETE from emp where hiredate = add_months(sysdate,-12*10)

160--display dept.name where no employee is working ?

select e.ename,d.dname from emp e,dept_info d where e.deptno=d.deptno

and e.empno is null

161--display emp who are working as manager?

select * from emp where job='MANAGER'

162--DISPLAY THE DEPT NAME THE NUMBER OF CHARACTER OF WHICH IS EQUAL TO

--NO OF EMPLOYEE IN ANY OTHER DEPARTMENT ?

select * from DEPT_INFO WHERE LENGTH(DNAME) IN ( SELECT COUNT(DEPTNO) FROM

EMP GROUP BY DEPTNO)

163--DISPLAY NAMES OF DEPT THOSE EMPEE WHO JOINED THE COMPANY ON THE SAME DATA ?

SELECT DNAME FROM DEPT_INFO D WHERE D.DEPTNO IN(SELECT TO_CHAR(HIREDATE,'DD')

FROM EMP)
--OR--
SELECT DNAME FROM DEPT_INFO D WHERE D.DEPTNO IN(SELECT E.DEPTNO FROM EMP E, EMP
E1
WHERE E.EMPNO!=E1.EMPNO AND E.HIREDATE=E1.HIREDATE)

164--DISPLAY THOSE EMPLOYEE WHOSE GRADE IS EQUAL TO ANY NUMBER OF SAL BUT NOT
--EQUAL TO FIRST NUMBER OF SAL ?

SELECT E.ENAME,S.GRADE,E.SAL,S.GRADE FROM EMP E , SALGRADE S WHERE E.SAL BETWEEN

S.LOSAL AND S.HISAL AND LENGTH(SAL)<>LENGTH(REPLACE(E.SAL,S.GRADE))
AND S.GRADE<> SUBSTR(SAL,1,1)
--OR--
SELECT E.ENAME,S.GRADE,E.SAL,S.GRADE FROM EMP E , SALGRADE S WHERE E.SAL BETWEEN
S.LOSAL AND S.HISAL AND S.GRADE<> SUBSTR(SAL,1,1) AND INSTR(E.SAL,S.GRADE,1,1)<>0

165--COUNT NO OF EMP ARE WORKING AS MANAGER (USING SET OPERATOR) ?

SELECT COUNT(JOB) FROM EMP GROUP BY JOB HAVING JOB='MANAGER'

166-- DISPLAY NAMES OF EMPEE WHO JOINED ON SAME DATE ?

SELECT E.ENAME,HIREDATE FROM EMP E WHERE E.HIREDATE IN

(SELECT E1.HIREDATE FROM EMP E1 WHERE E.EMPNO<>E1.EMPNO )

167-- DISPALY THE MANAGER WHO HAVING MAX NO OF EMPLOYEE WORKING UNDER HIM ?

SELECT MGR FROM EMP GROUP BY MGR HAVING

COUNT(*)=(SELECT MAX(COUNT(MGR)) FROM EMP GROUP BY MGR)

168--LIST OUT THE EMPEE NAMR AND SAL INCRESED BY 15% AND
--EXPRESS AS WHOLE NUMBER OF DOLLERS ?

SELECT ENAME,'$'||(SAL+SAL*15/100) FROM EMP

169--PRODUCE THE OUTPUT OF EMP TABLE "EMPLOYEE_AND_JOB" FOR ENAME AND JOB ?

SELECT ENAME||' AND '||JOB EMPLOYEE_JOB FROM EMP

170--LIST EMP WHO HIREDATE FORMATE OF 'JUNE 4 1998' ?

SELECT TO_CHAR(HIREDATE,'MON-DD-YYYY') FROM EMP

171--PRINT LIST OF EMPEE DISPLAY 'JUST SALARY ' IF SAL MPRE THAN 1500,
--IF EXACTLY 1500 DISPLAY 'ON TARGET', IF LESS THAN 1500 DISPALY BELOW 1500?

SELECT ENAME,(CASE WHEN SAL > 1500 THEN 'JUST SALARY' WHEN SAL=1500 THEN
'ON TARGET' WHEN SAL<1500 THEN 'BELOW 1500' END) FROM EMP

172--GIVEN A STRING OF FORMAT 'NN/NN' ,VERFY THAT THE FIRST AND LAST 2 CHARACTER
--ARE NUMBERS . AND THAT THE MIDDLE CHARACTER IS '/' PRINT THE EXPRESSION 'YES'
--IF VALID 'NO' OF NOT VALID . USE THE FOLLOWING VALUES TO TEST YOYR SOLUTION
--'12/54' , 01/1A , '99/98' ?

SELECT A,((CASE WHEN SUBSTR(A,1,2) BETWEEN 0 AND 9 THEN 'OK' WHEN

SUBSTR(A,3,1) = '/' THEN 'OK' WHEN SUBSTR(A,-2) BETWEEN 0 AND 9 THEN 'OK') ELSE
'NOT OK' END) FROM
(SELECT '1A/54' A FROM DUAL)X;