Oracle Queries

By PenchalaRaju.Yanamala

001. Display all the information of the emp table.

SQL> Select * from emp ;

002. Display unique jobs from EMP table.

SQL> SELECT DISTINCT Job FROM EMP GROUP BY Job ;

003. List the details of the emps in asc order of their salaries.
SQL> SELECT * FROM EMP
ORDER BY Sal ASC ;

004. List the details of the emps in asc order of the Deptnos and desc of
Jobs.
SQL> SELECT * FROM EMP
ORDER BY Deptno ASC, Job DESC ;

005. Display all the unique job groups in the descending order
SQL> select unique job from emp order by job desc ;

006. Display all the details of all ‘Mgrs’

SQL> select * from emp where job = 'MANAGER' ;

007. List the emps who joined before 1981.

SQL> select * from emp where hiredate < '01-Jan-1981' ;

008. List the Empno, Ename, Sal, Daily Sal of all Employees in the ASC
order of AnnSal.
SQL> SELECT Empno, Ename, sal, Sal/30 DailySal
FROM Emp ORDER BY Sal*12 ;

009. Display the empno , ename, job, hiredate, exp of all Mgrs
SQL> select empno, ename, sal,
months_between(sysdate,hiredate)/12 Exp
from emp where job = 'MANAGER' ;

010. List the empno, ename, sal, exp of all emps working for Mgr 7839.
SQL> select empno, ename, sal,
months_between(sysdate,hiredate)/12 Exp
from emp B where Mgr = 7839 ;

011. Display the details of the emps whose Comm. Is more than their sal.
SQL> select * from emp where comm > sal ;

012. List the emps in the asc order of Designations

SQL> select * from emp order by job ;

013. List the emps along with their exp and daily sal is more than Rs.100
SQL> Select emp.*, months_between(sysdate,hiredate)/12 Exp
from emp where sal/30 > 100 ;

014. List the emps who are either ‘CLERK’ or ‘ANALYST’ in the desc order
SQL> Select * from emp where job in (‘CLERK’,‘ANALYST’) order by job
desc ;

015. List the emps who joined on 1May81,31Dec81, 17Dec81, 19Jan80 in

asc order of seniority.
SQL>
Select ename,hiredate,months_between(sysdate,hiredate)/12 EXP
from emp
where hiredate like '01-MAY-81' or hiredate like '31-DEC-81'
or hiredate like '17-DEC-81' or hiredate like '19-JAN-80'
order by hiredate desc ;
016. List the emps who are working for the deptno 10 or 20
SQL> Select * from emp where deptno in (10,20) ;

017. List the emps who are joined in the year 1981
SQL> Select * from emp where hiredate like ‘%81’ ;

018. List the emps who are joined in the month of Aug 1980
SQL> Select * from emp where hiredate like %AUG-80’ ;

019. List the emps whose annul sal ranging from 22000 and 45000
SQL> Select * from emp where sal*12 between 22000 and 45000 ;

020. List the emps those are having five characters in their names.
SQL> Select * from emp where ename like ‘_____’ ;

021. List the enames those are starting with ‘s’ and with fire characters
SQL> Select * from emp where ename like ‘S____’ ;

022. List the emps those are having four chars and third char must be ‘r’
SQL> Select * from emp where ename like ‘__R_’ ;

023. List the 5 character names starting with ‘s’ and ending with ‘h’
SQL> Select * from emp where ename like ‘S___H’ ;

024. List the emps who joined in January

SQL> Select * from emp where hiredate like ‘%JAN%’ ;

025. List the emps who joined in the month of which second character is ‘a’
SQL> Select * from emp where hiredate like ‘__-_A%’ ;

026. List the emps whose sal is 4 digit number ending with zero
SQL> Select * from emp where sal like ‘___0’ ;

027. List the emps whose names having a character set ‘ll’ together
SQL> Select * from emp where ename like ‘%LL%’ ;
028. List the emps those who joined in 80’s
SQL> Select * from emp where hiredate like ‘%80’ ;

029. List the emps who does not belong to deptno 20

SQL> Select * from emp where deptno <> 20 ;

030. List all the emps except ‘president’ & ‘Mgr’ in asc order of salaries
SQL> Select * from emp where job not in (‘PRESIDENT’,’MANAGER’)
order by sal ;

031. List the emps who joined in before or after 1981

SQL> Select * from emp where hiredate not like '%81' ;

032. List the emps whose empno not starting with digit 78
SQL> Select * from emp where empno not like ‘78%’ ;

033. List the emps who are working under ‘Mgr’

SQL> Select * from emp where mgr in
(select empno from emp where job = ‘MANAGER’) ;

034. List the emps who joined in any year but not belongs to the month of
March
SQL> Select * from emp where hiredate not like ‘%MAR%’ ;

035. List all the clerks of deptno 20.

SQL> Select * from emp where deptno=20 and job =’CLERK’ ;

036. List the emps of deptno 30 or10 joined in the year 1981
SQL> Select * from emp where deptno in(10,30) and hiredate like ’%81’ ;

037. Display the details of ‘Smith’

SQL> Select * from emp where ename = ‘SMITH’ ;

038. Display the location of ‘Smith’

SQL> Select loc from emp where ename = ‘SMITH’ ;

039. List the total information of emp table along with dname and loc of all
the emps working under ‘Accounting’ & ‘Research’ in the asc deptno
SQL> SELECT EMP.*,DNAME,LOC FROM Emp, Dept
WHERE Dname IN ('ACCOUNTING','RESEARCH')
AND EMP.DEPTNO = DEPT.DEPTNO
ORDER BY EMP.DEPTNO

040. List the empno, ename, sal, dname of all the ‘Mgrs’ and ‘Analyst’
working in NEWYORK, DALLAS with an exp more than 7 years without
receiving the Comma Asc order of Loc.
SQL> SELECT EMPNO, ENAME,SAL, dname FROM EMP, DEPT
WHERE LOC IN ('NEW YORK','DALLAS') AND JOB IN
('MANAGER','ANALYST')
AND MONTHS_BETWEEN(SYSDATE,HIREDATE)/12 > 7 AND
COMM IS NULL AND EMP.DEPTNO = DEPT.DEPTNO
 ORDER BY LOC ;

041. List the empno, ename, sal, dname, loc, deptno, job of all emps
working at CHICAGO or working for ACCOUNTING dept wit ann sal >
28000, but the sal should not be = 3000 or 2800 who doesn’t belongs
to the Mgr and whose no is having a digit ‘7’ or ‘8’ in 3rd position in the
asc order of deptno and desc order of job.
SQL> SELECT EMPNO, ENAME,JOB,SAL,EMP.DEPTNO, dname, loc
FROM EMP, DEPT
WHERE (LOC = 'CHICAGO' OR DNAME = 'ACCOUNTING')
AND SAL*12 > 28000 AND SAL NOT IN(3000,2800)
AND MGR IS NULL AND EMPNO LIKE '__7_' OR EMPNO LIKE
'__8_'
AND EMP.DEPTNO = DEPT.DEPTNO
ORDER BY EMP.DEPTNO ASC, JOB DESC ;

042. Display the total information of the emps along with grades in the asc
order
SQL> Select emp.*, grade from emp,salgrade where sal between losal and
hisal ;

043. List all the grade2 and grade 3 emps

SQL> Select emp.*, grade from emp,salgrade where grade in(2,3) and
sal between losal and hisal

044. Display all grade 4,5 Analyst and Mgr

SQL> Select emp.*,grade from emp,salgrade where grade in(4,5) and
Job in ('ANALYST','MANAGER') and sal between losal and hisal ;

045. List the empno, ename, sal, dname, grade, exp, ann sal of emps
working for dept 20 or 10.
SQL> Select empno, ename, sal, sal*12 AnnSal, dname, grade,
months_between(sysdate,hiredate)/12 Exp from emp, dept,
salgrade
where emp.deptno in(10,20) and emp.deptno=dept.deptno and sal
between losal and hisal

046. List all the information of emps with loc and the grade of all the emps
belong to the grade ranges from 2 to 4 working at the dept those are
not starting with char set ‘OP’ and not ending with ‘S’ with the design
having a char ‘a’ any where joined in the year 81 but not in the month
of Mar or Sep and sal not end with ‘00’ in the asc order of grades.
SQL> Select emp.*, loc, grade from emp, dept, salgrade where
Grade between 2 and 4 and dname not like ‘OP%’ and
dname not like ‘%S’ and Job like ‘%A%’ and
(hiredate like ‘%81’ and hiredate not like ‘%MAR%’ and hiredate not
like ‘%SEP%’) and
sal not like %00 and emp.deptno=dept.deptno
and sal between losal and hisal ;

047. List the details of the depts along with empno, ename or without the
emps
SQL> Select empno, ename, dept.* from emp,dept where
emp.deptno=dept.deptno ;

048. List the details of the emps whose salaries more than the employee
BLAKE
SQL> Using self-join:
select A.* from emp A, emp B where
A.sal > B.sal and B.ename = 'BLAKE'

Using sub-query:
select * from emp where
sal > (select sal from emp where ename = 'BLAKE')

049. List the details of the emps whose job is same as ALLEN.
SQL> select * from emp where job = (select job from emp where ename =
'ALLEN')

050. List the emps who are senior to King

SQL> select * from emp where hiredate < (select hiredate from emp where
ename = 'KING')

051. List the emps who are senior to their own MGRs
SQL> select * from emp A where hiredate <
(select hiredate from emp B where b.Empno = A.Mgr)

052. List the emps of deptno 20 whose jobs are same as deptno 10
SQL> Select * from emp where deptno=20 and job in (select job from emp
where deptno=10) ;

053. List the emps whose Sal is same as FORD or SMITH in desc order of
Sal.
SQL> Select distinct * from Emp where sal in
(select sal from emp where ename in ('FORD','SMITH'))
and ename not in ('FORD','SMITH')
Order By sal Desc ;

054. List the emps whose Jobs are same as MILLER or Sal is more than
ALLEN.
SQL> Select * from Emp where job = (select job from emp where
ename=’MILLER’) OR Sal > (select sal from emp where ename
='ALLEN') ;

055. List the emps whose Sal is > the total remuneration of the
SALESMAN.
SQL> Select * from Emp where sal > (select SUM(sal) from emp where
JOB=’SALESMAN');
Note : no rows will be selected. If you choose job=’CLERK’, one record
will be fetched.

056. List the emps who are Senior to ‘BLAKE’ working at CHICAGO &
BOSTON.
SQL> Select * from Emp,dept where Hiredate <
 (select hiredate from emp where ename='BLAKE')
AND loc in ('CHICAGO','BOSTON') and emp.deptno = dept.deptno ;

057. List the emps of Grade 3,4 belongs to the dept ACCOUNTING and
RESEARCH whose sal is more than ALLEN and exp more than
SMITH in the asc order of Exp.
SQL> Select * from Emp,dept,salgrade where grade in(3,4)
AND dname in ('ACCOUNTING','RESEARCH') and
Sal > (select sal from emp where ename='ALLEN')
and Hiredate < (select hiredate from emp where ename='SMITH')
and emp.deptno = dept.deptno and sal between losal and hisal;
Note : no rows will be selected. Because, ’SMITH’ is the senior most in
the emp table. If you choose ename=’TURNER’, Two record will be
fetched.

058. List the emps whose jobs same as ALLEN Or SMITH.

SQL> Select * from Emp where job in
(select distinct job from emp where ename in
('ALLEN','SMITH')) and ename not in ('ALLEN','SMITH').

059. Write a Query to display the details of emps whose Sal is same as of
a) Employee Sal of Emp1 table.
b) ¾ Sal of any Mgr of Emp2 table.
c) The Sal of any sales person with exp of 5 yrs belongs to the
sales dept of emp3 table.
d) Any Grade 2 employees of Emp4 table.
e) Any Grade 2&3 employee working for SALES dept or
OPERATIONS dept joined in 89.
SQL> Select * from emp where sal in (select sal from emp1) OR
sal in (select sal*3 from Emp2 where job='MANAGER') OR
sal in (select sal from Emp3 where
months_between(sysdate,hiredate)/12 > 5) OR
sal in (select sal from Emp4,salgrade where grade=2 and sal
between losal and hisal) OR
sal in (select sal from Emp,salgrade,dept where grade in(2,3) and
dname in ('SALES','OPERATIONS') and hiredate like '%89' and
sal between losal and hisal and emp.deptno=dept.deptno)

060. List the jobs of Deptno 10 those are not found in dept 20.
SQL> Select * from emp where deptno = 10 and job not in (select job from
emp where deptno=20)

061. List the emps of Emp1 who are not found in deptno 20
SQL> Select * from emp where ename not in (select ename from emp where
deptno=20)

062. Find the highest Sal of the Emp table.

SQL> Select Max(sal) from emp ;

063. Find the details of highest paid employee.

SQL> Select * from emp where sal = (select Max(sal) from emp) ;
064. Find the highest paid employee of sales dept.
SQL> Select * from emp where sal = (select Max(sal) from emp,dept where
dname=’SALES’ and emp.deptno = dept.deptno ) ;

065. List the most recently hired emp of grade 3 belongs to the Loc
CHICAGO
SQL> Select * from emp,dept where loc=’CHICAGO’ and
hiredate = (Select max(hiredate) from emp);

066. List the emps who are senior to most recently hired emp who is
working under Mgr KING
SQL> Select * from emp where hiredate < (Select max(hiredate) from emp)
and
mgr=(select empno from emp where ename=’KING’) ;

067. List the details of emp belongs to New York with the Grade 3 to 5
except ‘PRESIDENT’ whose sal > the highest paid emp of CHICAGO
in Group where there is ‘MANAGER’ & ‘ANALYST’ not working for Mgr
KING
SQL> Select * from emp,dept,salgrade where loc='NEW YORK' and
Grade in (3,4,5) and job <> 'PRESIDENT' and Sal >
(select Max(sal) from emp,dept where loc='CHICAGO' and
Job not in ('MANAGER','ANALYST') and emp.deptno = dept.deptno)
and Mgr =
(select empno from emp where ename='KING') And
emp.deptno = dept.deptno and sal between losal and hisal ;

068. Display the details of most senior employee belongs to 1981.

SQL> select * from emp where hiredate = (select min(hiredate)
from emp where hiredate like '%81') ;

069. List the emps who joined in 81 with job same as the most senior
person of year 81.
SQL> select * from emp where hiredate like '%81' and job =
(select job from emp where hiredate= (select min(hiredate) from emp
where hiredate like '%81')) ;

070. List the most senior emp working under KING and Grade is more than
3.
SQL> select emp.* from emp,salgrade where hiredate =
(select max(hiredate) from emp where mgr=
(select empno from emp where ename = 'KING')) and
grade > 3 and sal between losal and hisal

071. Find the total sal given to the ‘MGR’

SQL> select sum(sal) from emp where job = 'MANAGER'

072. Find the total annual sal to distribute job wise in the year 81
SQL> select Job,sum(sal*12) from emp GROUP BY JOB ;

073. Display the total sal of emps belong to Grade 3.

SQL> select grade, sum(sal) from emp,salgrade where
 sal between losal and hisal group by grade ;

074. Display the avg salaries of all CLERKS

SQL> select avg(sal) from emp where job = 'CLERK' ;

075. List the emps in dept 20 whose sal is > the avg sal of deptno 10 emps.
SQL> select * from emp where sal > (select avg(sal) from emp
where deptno=10) and deptno=20 ;

076. Display the number of emps for each job group deptno wise
SQL> select dname,job,count(empno) from emp,dept
where dept.deptno = emp.deptno
group by dname,job ;

077. List the Mgr no & no. of emps working for those Mgrs in the asc Mgrno.
SQL> select mgr,count(*) from emp group by mgr order by mgr asc ;

078. List the dept details where at least two emps are working.
SQL> select dname, count(empno) from emp,dept
where dept.deptno = emp.deptno
group by dname,job
having count(empno) >= 2 ;

079. Display the grade, number of emps, max sal of each grade.
SQL> select grade, count(empno),max(sal) from emp,dept,salgrade
where dept.deptno = emp.deptno and
sal between losal and hisal
group by grade ;

080. Display dname, grade, no of emps where atleast two emps are
‘CLERKS’
SQL> select dname,grade, count(empno) from emp,dept,salgrade
where dept.deptno = emp.deptno and
sal between losal and hisal
group by dname,grade
having count('CLERK') >= 2 ;

081. List the details of the dept where the max no of emps are working
SQL> select dname, count(empno) from emp,dept
where dept.deptno = emp.deptno group by dname
having count(empno) = (select max(count(empno)) from emp group
by deptno) ;

082. Display the emps whose Mgr name is Jones

SQL> Select * from emp where mgr = (select empno from emp where ename
= ‘JONES’) ;

083. List the emps whose salary is more than 3000 after giving 20%
increment
SQL> Select EMP.*,SAL+(SAL*.2) incrsal from emp where SAL+(SAL*.2) >
3000 ;
084. List the emps with their dept names.
SQL> Select emp.*, dname from emp, dept where
emp.deptno = dept.deptno ;

085. List the emps who are not working in sales dept
SQL> Select emp.*, dname from emp, dept where
Dname <> ‘SALES’ and
emp.deptno = dept.deptno ;

086. List the emps name, dept, sal & comm. For those whose salary is
between 2000 and 5000 while loc is CHICAGO
SQL> Select ename, dname, sal, comm from emp, dept where
sal between 2000 and 5000 and loc = 'CHICAGO' and
emp.deptno = dept.deptno ;

087. List the emps whose salary is > his Manager’s salary.
SQL> select A.* from emp A,emp B where
A.mgr=B.empno and A.sal > B.sal ;

088. List the grade, employee name for the deptno 10 or deptno 30 but
salgrade is not 4 while they joined the company before ’31-DEC-82’
SQL> select grade, ename, hiredate from emp,salgrade where
deptno in (10,30) and grade <> 4 and hiredate
in (select hiredate from emp where hiredate
< '31-DEC-82') and sal between losal and hisal

089. List the name, job, dname, loc for those who are working as a ‘MGRs’
SQL> select empno,ename, job, dname, loc from emp,dept where
job = 'MANAGER' and emp.deptno = dept.deptno ;

090. List the emps whose mgr name is ‘JONES’ and also list their manager
name
SQL> select A.*, B.ename from emp A, emp B where
A.mgr = B.empno and b.ename='JONES' ;

091. List the name and salary of FORD if his salary is equal to hisal of his
Grade
SQL> select ename, sal from emp where ename = 'FORD'
and sal = (select hisal from emp, salgrade where ename = 'FORD'
and sal between losal and hisal)

092. List the name, job, dname, Manager, salary, Grade dept wise.
SQL> Select ename, job, Mgr, sal, dname, grade from
emp, dept, salgrade where emp.deptno=dept.deptno
and sal between losal and hisal and emp.deptno in
(select distinct deptno from emp
group by emp.deptno) ;

093. List the emps name, job, salary, grade and dname except ‘CLERK’s
and sort on the basis of highest salary.
SQL> select ename, job, sal, grade, dname from emp, dept, salgrade
 where job <> 'CLERK' and dept.deptno = emp.deptno and
sal between losal and hisal order by sal desc ;

094. List the emps name, job and Manager who are without Manager.
SQL> select ename, job, mgr from emp where mgr is NULL ;

095. List the names of emps who are getting the highest salary dept wise.
SQL> Select ename, job, sal, dname from emp, dept
where emp.deptno=dept.deptno and emp.deptno in
(select distinct deptno from emp group by emp.deptno)
and sal in (select max(sal) from emp group by deptno) ;

096. List the emps whose salary is equal to average of maximum and
minimum.
SQL> TO RETRIEVE SAL WHICH IS = TO AVG. SAL
Select ename, job, sal from emp where
sal = (select avg(sal) from emp) ;

TO RETRIEVE SAL WHICH IS = TO AVG. OF MAX SAL AND MIN

SAL
select * from emp where sal = (select (max(sal)+min(sal))/2 from
emp) ;

097. List the no of emps in each dept where the number is more than 3.
SQL> Select deptno, count(deptno) from emp
group by deptno having count(deptno) > 3 ;

098. List the names of depts. Where at least 3 emps are working in each
dept.
SQL> Select dname, count(emp.deptno) from emp,dept
where emp.deptno = dept.deptno
group by dname having count(emp.deptno) > 3 ;

099. List the managers whose salary is more than his emps avg sal.
SQL> Select distinct A.* from emp A, emp B
where A.empno = B.mgr and
A.sal > (select avg(B.sal) from emp group by B.Mgr) ;

100. List the name, sal, comm. For those emps whose net pay is > or = any
other employee salary of the company.
SQL> Select distinct A.ename, A.sal, A.comm from emp A, emp B where
((A.sal + A.sal*1.2) + (A.sal*1.2) -(A.sal*1.6 + 80)) >= (select avg(sal)
from emp) ;

101. List the emps whose sal < his Manager but more than other Manager
SQL> Select distinct A.* from emp A, emp B, emp C
where A.sal < B.sal and A.mgr = B.empno and
A.sal > C.sal and A.mgr not in (select empno
from emp where empno <> B.Empno and job = 'MANAGER') ;
???
102. List the employee names and his annual salary dept wise.
SQL> Select ename, dname, sal*12 from emp, dept where
 emp.deptno in (select deptno from emp group by deptno)
and emp.deptno = dept.deptno ;
OR
Select deptno, ename, sal*12 from emp group by deptno,ename,sal ;

103. Find out least 5 earners of the company.

SQL> select * from emp where rownum < 6 order by sal asc

104. Find out the no of emps whose salary is > their Manager salary.
SQL> select count(A.empno) from emp A, emp B where
A.sal > B.sal and A.Mgr = B.empno ;

105. List the Mgrs who are not working under ‘PRESIDENT’ but are working
under other Manager.
SQL> select A.ENAME, A.JOB from emp A, emp B where
A.Mgr = B.empno and B.ename <> 'PRESIDENT' AND A.job =
'MANAGER' ;

106. List the records from emp whose deptno is not in dept
SQL> HOW CAN IT BE POSSIBLE???
[A REFERENCE KEY VALUE WITHOUT A PRIMARY KEY VALUE.]

select * from emp where deptno not in (select deptno from dept)

107. List the name, sal, comm. And net pay is more than any other
employee.
SQL> select empno, sal, comm, sal+sal*1.2+800 Net from emp where
sal = (select max(sal) from emp) ;

108. List the enames who are retiring after ’31-DEC-89’ the max job period
is 20Y.
SQL> Select empno, ename, hiredate, sysdate,
months_between(sysdate,hiredate)/12 Exp from emp
where months_between(sysdate,hiredate)/12 > 20
order by hiredate ;

ABOVE QUERY IS WORKING BUT BELOW QUERY IS NOT. IN

BELOW QUERY I GAVE DATE() IN PLACE OF SYSDATE.

select emp.*, months_between('31-DEC-89',hiredate)/12

Retirement_Date
from emp where months_between('31-DEC-89',hiredate) / 12 > 20 ;
months_between(…)/12 giving o/p greater than 100.

109. List those emps whose sal is odd value.

SQL> Select * from emp where mod(sal,2) <> 0 ;

110. List the emps whose sal contain 3 digits

SQL> Select * from emp where length(sal) = 3 ;

111. List the emps who joined in the month of ‘DEC’

SQL> Select * from emp where hiredate like ‘%DEC%’ ;
112. List the emps whose names contains ‘A’
SQL> select ename from emp where ename like '%A%' ;

113. List the emps whose deptno is available in his salary.

SQL> select ename, sal, deptno from emp
where substr(sal,length(sal)-1,2) = deptno
or substr(sal,length(sal)-2,2) = deptno
or substr(sal,length(sal)-3,2) = deptno ;

114. List the emps whose first 2 chars from hiredate = last 2 chars of
salary.
SQL> select ename, sal, hiredate from emp
where substr(hiredate,1,2) = substr(sal,length(sal)-1,2) ;

115. List the emps whose 10% of sal is equal to year of Joining
SQL> select ename,sal , sal * .1, hiredate from emp
where sal * .1 like substr(hiredate,length(hiredate)-1,2) ;

116. List first 50% of chars of ename in lower case and remaining are
upper case.
SQL> select ename, lower(substr(ename,1,length(ename)/2)),
upper(substr(ename,(length(ename)/2)+1,length(ename))) from emp ;

117. List the dname whose no of emps is = to no of chars in the

dname.
SQL> select dname, length(dname) Len from dept where deptno =
(select emp.deptno from emp, dept where
emp.deptno = dept.deptno having Count(empno) in
(select length(dname) from dept group by dname)
group by emp.deptno) ;

THIS QUERY WILL WORK AFTER MODIFING EMP TABLE. AT

PRESET THERE ARE NO SUCH DNAME WHERE NO. OF EMPS =
NO OF CHARS IN THE DNAME. TO GET THE OUTPUT, I HAD TO
CHANGE ONE SALES DEPT(DEPTNO 30) EMP TO SOME OTHER
DEPT – SO THAT THE NO OF CHARS IN SALES DEPT(5) IS = NO
OF EMPS OF THAT DEPT. [AT PRESENT THERE ARE 6 EMPS]

IF U DON’T CHANGE THE ABOVE, U GET THE WRONG O/P FOR

THE SAME.

select dname, length(dname) Len from dept where deptno =

(select emp.deptno from emp, dept where
emp.deptno = dept.deptno having Count(empno) in
(select length(dname) from dept group by dname)
group by emp.deptno) ;
O/P : Reserch , 8 Which is a wrong O/P.

select dname, count(empno) from emp, dept

where emp.deptno = dept.deptno
 group by dname ;
O/P : Dname, count of emps
118. List the emps those who joined in company before 15th of the
month.
SQL>

select * from emp where hiredate in

(select hiredate from emp where hiredate > (select
hiredate from emp where hiredate like '14-%')) ;

NO ROWS SELECTED

119. List the dname, no of chars of which is no of emp’s in any other dept
SQL> ???

select dname, length(dname) Len from dept where deptno IN

(select emp.deptno from emp, dept where emp.deptno = dept.deptno
having Count(empno) in (select count(empno) from
emp group by deptno) group by emp.deptno) ;

select dname, length(dname) from dept ;

120. List the emps who are working as managers

SQL> select * from emp where empno in (select mgr from emp) ;

121. List the name of dept where highest no of emps are working
SQL> select dname, emp.deptno, count(empno) from emp, dept
where emp.deptno = dept.deptno having count(empno) =
(select max(count(empno)) from emp group by deptno)
group by emp.deptno, dname ;

122. Count the no of emps who are working as ‘Managers’ (using set
option)
SQL> Select * from emp where empno in (select empno from emp)
Union (select Mgr from emp)
GIVING ERROR : ‘Query block has incorrect number of result
columns’

123. List the emps who joined in the company on the same date
SQL> select * from emp where hiredate in (select hiredate from emp
having count(hiredate) > 1 group by hiredate) ;

124. List the details of the emps whose grade is equal to one tenth of sales
dept.
SQL> select * from emp, salgrade where grade = deptno/10
and deptno = (select deptno from dept where dname='SALES')
and sal between losal and hisal ;
 OR

select * from emp, salgrade where grade = deptno/10

and sal between losal and hisal ;

125. List the name of the dept where more than avg. no of emps are
working
SQL> select dname, dept.deptno, count(empno) from emp2, dept
where emp2.deptno in (select deptno from emp group by emp.deptno)
and emp2.deptno = dept.deptno group by dept.deptno, dname
having count(empno) >= (select count(deptno) from dept) ;

126. List the managers name who is having max no of emps working under
him
SQL> select ename from emp where empno =
(select mgr from emp having count(empno) =
(select max(count(empno)) from emp group by mgr) group by mgr) ;

127. List the ename and sal is increased by 15% and expressed as no of
dollars.
SQL>

select ename, (sal + sal * 0.15), decode(sal, sal + sal * 0.15,

(sal + sal * 0.15)/34) NewSal from emp ;

128. Produce the output of EMP table ‘EMP_AND_JOB’ for ename and job
SQL> select ename || '_AND_' || job from emp ;

129. Produce the following output from EMP

EMPLOYEE
----------------
SMITH(clerk)
ALLEN(salesman)
SQL> select ename || '(' || lower(job) ||')' EMPLOYEE from emp ;

130. List the emps with hiredate in format June 4,1988.

SQL> select ename, to_char(hiredate,'Month DD,YYYY.') from emp ;

131. Print a list of emp’s listing ‘just salary’ if salary is more than 1500, ‘On
target’ if salary is 1500 and ‘Below 1500’ if salary is less than 1500.
SQL>

select ename, sal, decode(sal,(sal>1500), 'just salary',

1500,'On target',(sal<1500),'Below 1500',sal) from emp ;

MISSING RIGHT PARENTHESIS.

132. Write a query to return the day of the week for any date entered in
 format ‘DD-MM-YY’
SQL> select to_char(hiredate, 'day') from emp ;
OR
select to_char(sysdate, 'day') from dual

133. Wrote a query to calculate the length of service of any employee with
the company, use DEFINE to avoid repetitive typing of functions
SQL> DEFINE ????

134. Give a string of format ‘NN/NN’, verify that the first and last two
characters are numbers and that the middle character is ‘/’. Print the
expression ‘YES’ if valid, ‘NO’ if not valid. Use the following values to
test your solution.
‘12/34’, ‘01/la’, ‘99/98’
SQL>

135. Emps hired on or before 15th of any month are paid on the last Friday
of that month. Those hired after 15th are paid on the first Friday of the
following month. Print a list of emps, their hiredate and the first pay
date. Sort on hiredate.
SQL>

136. Count the no of characters without considering spaces for each name.
SQL> Select ename, length(ename) from emp ;

137. Find out the emps who are getting decimal value in their sal without
using like operator.
SQL>

Select ename, sal, to_char(substr(to_char(sal),3,1)) from emp ;

Select ename, sal from emp where

to_char(substr(to_char(sal),-3,1)) ='.'

138. List those emps whose salary contains first four digit of their
deptno. ???
SQL> ?????????? salary contains first four digit of their deptno ?????

139. List those managers who are getting less than his emps salary.
SQL> select distinct B.* from emp A, emp B where
A.Mgr=B.empno and A.sal > B.sal ;

140. Print the details of all the emps who are sub-ordinates to BLAKE.
SQL> select * from emp where mgr =
(select empno from emp where ename='BLAKE') ;

141. List the emps who are working as managers using co-related sub-
query
SQL> Using Sub Query :
Select * from emp where empno in(select mgr from emp) ;
 Using Co-Releated Query :
Select distinct A.* from emp A, emp B where A.empno =B.Mgr ;

Using Co-Releated Sub Query :

Select distinct A.* from emp A, emp B
where A.empno in(select B.mgr from emp)

142. List the emps whose Mgr name is ‘JONES’ and also with his Manager
Name.
SQL> select distinct A.*, B.ename from emp A, emp B where A.mgr =
(select empno from emp where ename='JONES') and
B.ename='JONES' ;

143. Define a variable representing the expression used to calculate on

emps total annual remuneration. Use the variable in a statement which
finds all emps who can earn 30000 a year or more.
SQL> select emp.*, sal*12 Ann_Sal from emp where sal*12 >= 30000 ;

144. Find out how many managers are there in the company.
SQL> Select count(*) from emp where empno in(select mgr from emp) ;

145. Find avg sal and avg total remuneration for each job type. Remember
salesman earn commission.
SQL> Select job, count(empno),avg(sal), sum(sal) from emp group by job ;

OR

Select job, avg(sal), sum(decode(comm,null,0)+sal) from emp group

by job ;

OR
Select job, avg(decode(comm,null,0)+sal),
sum(decode(comm,null,0)+sal) from emp group by job ;

146. Check whether all the emps numbers are indeed unique.
SQL> ???

147. List the emps who are drawing less than 1000. sort the output by
salary.
SQL> Select * from emp where sal < 1000 order by sal ;

148. List the employee name, job, annual sal, deptno, dept name, and
grade who earn 36000 a year or who are not CLERKS.
SQL> Select ename, job, sal*12 AnnSal, emp.deptno, dname, grade
from emp, dept, salgrade where sal*12 > 36000 and
job <> 'CLERK' and sal between losal and hisal and
dept.deptno = emp.deptno ;

149. Find out the job that was filled in the first half of 1983 and same job
that was filled during the same period of 1984.
SQL>
 select job from emp where hiredate in (select hiredate from
emp where hiredate > '01-JUL-82') and hiredate in (select hiredate
from emp where hiredate > '01-JUL-84')

NO ROWS SELECTED

150. Find out the emps who joined in the company before their managers.
SQL> select A.empno, A.hiredate, A.mgr from emp A, emp B
where A.mgr = B.empno and A.hiredate < B.hiredate ;

151. List all the emps by name and number along with their manager’s
name and number also list KING who has no ‘Manager’
SQL>

select distinct A.empno, A.ename, A.mgr, B.empno, B.ename

from emp A, emp B where A.mgr = B.empno ;

HOW TO RETRIEVE KING’S INFO

select * from emp where empno in

(select distinct A.empno from
emp A, emp B where A.mgr = B.empno)
union
(select B.empno from emp B where
ename='KING') ;
ERRRO : Query Block has incorrect number of result columns.
152. Find all the emps who earn minimum sal for each job wise in asc order
of sal
SQL> select * from emp where sal in (select min(sal) from emp
group by job) order by sal ;

153. Find out all the emps who earn highest sal in each job type. Sort in des
order of sal
SQL> select * from emp where sal in (select max(sal) from emp
group by job) order by sal desc ;

154. Find out the most recently hired emps in each dept order by hiredate
SQL> select * from emp where hiredate in (select max(hiredate) from emp
group by job) order by hiredate desc ;

155. List the emp name, sal and deptno for each emp who earns a sal
greater than the avg for their dept order by deptno
SQL>

select ename, sal, deptno from emp where sal in

(select sal from emp where sal > (select avg(sal) from emp
 group by deptno) group by deptno) order by deptno

NOT A GROUP BY EXPRESSION

156. List the deptno where there are no emps

SQL> select deptno, dname from dept where deptno not in
(select deptno from emp) ;

157. List the no of emps and avg salary within each dept for each job.
SQL> select deptno, job, count(empno), avg(sal) from emp group by deptno,
job ;

158 Find the max avg salary drawn for each job except for ‘PRESIDENT’
SQL> select job, max(sal), avg(sal) from emp
where job <> 'PRESIDENT' group by job ;

159. Find the name and job of the emps who earn Max salary and
Commission.
SQL> select ename, job, sal, comm from emp
where sal=(select max(sal) from emp) or
comm=(select max(comm) from emp) ;

160. List the name, job and salary of the emps who are not belonging to the
dept 10 but who have the same job and salary as the emps of dept 10.
SQL> select ename, job, sal from emp where deptno <> 10
and job in (select job from emp where deptno=10)
and sal in (select sal from emp where deptno=10) ;

NO SUCH RECORDS, SO GIVE :

select ename, job, sal from emp where deptno <> 10
and job in (select job from emp where deptno=10)
or sal in (select sal from emp where deptno=10) ;

161. List the deptno, name, job, salary and sal+comm. Of the emps who
earn the second highest earnings (sal+comm)
SQL> select ename, job, sal, sal+decode(comm,null,0), deptno from
emp where empno = (select empno from emp where
sal+decode(comm,null,0) =
(select max(sal+decode(comm,null,0)) from emp where
sal+decode(comm,null,0) in (select sal+decode(comm,null,0)
from emp where sal+decode(comm,null,0) <
(select max(sal+decode(comm,null,0)) from emp)))) ;

Select ename, job, sal, sal+decode(comm,null,0), deptno from emp

where
sal = (select max(sal) from emp where sal in
(select sal from emp where sal < (select max(sal) from emp))) ;

Select ename, job, sal, sal+comm, deptno from emp where

sal+comm = (select max(sal+decode(comm,null,0)) from emp
where sal+decode(comm,null,0) in (select sal+decode(comm,null,0)
 from emp where sal+decode(comm,null,0) < (select
max(sal+decode(comm,null,0)) from emp)))

NO ROWS SELECTED
162. List the deptno, name, job, salary and sal+comm. Of the salesman
SQL> select ename, job, sal, comm, deptno from emp where job =
'SALESMAN'

163. List the deptno and their avg salaries for dept with the avg salary less
than the avg for all departments.
SQL> select deptno, avg(sal) from emp having avg(sal) =
(select min(avg(sal)) from emp having avg(sal) in
(select avg(sal) from emp group by deptno) group by deptno)
group by deptno ;

164. List out the names and salaries of the emps along with their manager
names and salaries for those emps who earn more salary than their
manager.
SQL> select A.ename, A.sal, B.ename, B.sal from emp A, emp B
where A.mgr = B.empno and A.sal > B.sal ;

165. List out the name, job, sal of the emps in the department with the
highest avg sal.
SQL> select ename, job, sal from emp where sal >=
(select max(avg(sal)) from emp group by deptno) ;

166. List the empno, sal and comm. of emps

SQL> Select empno, sal, comm From emp ;

167. List the details of the emps in the asc order of salary.
SQL> Select * from emp order by sal ;

168. List the dept in asc order of job and the desc order of emps print
empno, ename
SQL> select empno, ename, job, sal, deptno from emp order by job, empno
desc ;

169. Display the unique dept of emps.

SQL> select * from emp where deptno = (select deptno from emp
having count(deptno) = 1 group by deptno);

To get the answer enter a new record with deptno as 40.

170. Display the unique dept with jobs.

SQL> select dname,job from emp, dept where emp.deptno = (select deptno
from emp
having count(deptno) = 1 group by deptno) ;

171. Display the details of BLAKE.

SQL> Select * from emp where ename = ‘BLAKE’ ;
172. List all CLERKs
SQL> Select * from emp where job = ‘CLERK’ ;

173. List all emps joined on 1 May 81.

SQL> Select * from emp where hiredate = ’01-MAY-81’ ;

174. List empno, ename,sal, deptno of dept 10 emps in the asc order of sal.
SQL> Select empno, ename,sal, deptno from emp where deptno = 10 order
by sal ;

175. List the emps whose salaries are less than 3500.
SQL> Select * from emp where sal < 3500 ;

176. List the emps Empno, Ename,sal for all emp joined before 1 apr 81
SQL>

Select Empno, Ename,sal,hiredate from emp where hiredate in (

select hiredate from emp where hiredate > '01-APR-81') ;

Select Empno, Ename,sal,hiredate from emp where hiredate < '01-

APR-81'

Giving wrong output.

177. List the emps whose ann sal is < 25000 in the asc order of salaries
SQL> Select * from emp where sal*12 < 25000 order by sal ;

178. List the empno, ename, ann sal, daily sal of all salesman in the asc
ann sal.
SQL> Select empno, ename, sal*12 AnnSal, sal/30 DailySal
from emp where job = ‘SALESMAN’ order by sal ;

179. List the empno, ename, hiredate, current date & exp in the asc order of
exp.
SQL> Select empno, ename, hiredate, sysdate,
months_between(sysdate,hiredate)/12 Exp from emp order by hiredate
;

180. List the emps whose exp is more than 10 years.

SQL> select * from emp where
months_between(sysdate,hiredate)/12 > 10 ;

181. List the empno, ename,sal TA 30%, DA 40%, HRA 50%, gross, LIC,
PF, net deductions, net allowances and net sal in the asc order of net
sal.
SQL> select empno, ename,sal,sal*.3 TA,sal*.4 DA,sal*.5 HRA, 80 IT,
sal*.04 LIC, sal*.12 PF, (sal*.16)-80 NetDeductions, sal*1.2
NetAllowances,
sal+sal*1.2 Gross, (sal+sal*1.2 - (sal*.16)-80) NetSal from emp order
by sal ;
182. List the emps who are working as Mgrs.
SQL> Select * from emp where job = ‘MANAGER’ ;

183. List the emps who are either CLERKs or MANAGERs.

SQL> Select * from emp where job = ‘MANAGER’ or job = ‘CLERK’;

184. List the emps who joined in the following dates : 1 may 81, 17 nov 81,
30 dec 81.
SQL> select * from emp where hiredate in
('01-MAY-81', '17-NOV-81', '30_DEC-81') ;

NO ROWS WILL BE SELECTED. THERE IS NO SUCH HIREDATES

185. List the emps who joined in year 81.

SQL> select * from emp where hiredate like '%81' ;

186. List the emps whose ann sal ranging from 23000 to 40000.
SQL> Select * from emp where sal*12 between 23000 and 40000 ;

187. List the emps working under the Mgrs 7369, 7890, 7654, 7900.
SQL> Select * from emp where Mgr in (7369, 7890, 7654, 7900) ;

NO ROWS WILL BE SELECTED. THERE IS NO SUCH EMPNOs

188. List the emps who joined in the second half of 82.
SQL>

Select * from emp where hiredate in (select hiredate

from emp where hiredate > '30-JUN-82') ;

Select * from emp where hiredate > '30-JUN-82';

NO ROWS SELECTED
189. List all the 4 chars emps.
SQL> Select ename from emp having
length(ename) = 4 group by ename ;

190. List the emps names starting with ‘M’ with 5 chars
SQL> Select ename from emp where ename like ‘M____’ ;

NO ROWS WILL BE SELECTED. THERE IS NO SUCH EMPs. GIVE

ONE MORE ‘_’ IN ENAME.

191. List the emps names end with ‘H’ all together 5 chars.
SQL> Select ename from emp where ename like '____H' ;

192. List the names start with ‘M’

SQL> Select ename from emp where ename like 'M%'

193. List the emps who joined in the year 81

SQL> Select * from emp where hiredate like ‘%81’ ;

194. List the emps whose sal is ending with 00.

SQL> Select * from emp where sal like ‘%00’ ;

195. List the emps who joined in the month of jan

SQL> Select * from emp where hiredate like ‘%JAN%’ ;

196. List the emps who joined in the month having a char ‘a’
SQL> Select * from emp where hiredate like ‘%A% ;’

197. List the emps who joined in the month having a second char is ‘A’
SQL> Select * from emp where hiredate like ‘%-_A%’ ;

198. List the emps whose salary is 4 digit no.

SQL> Select * from emp where sal like ‘____’ ;

199. List the emps who joined in 80’s.

SQL> Select * from emp where hiredate like ‘%8_’ ;

200. List the emps who are either clerks with an exp more than 8y
SQL> Select * from emp where job = ‘CLERK’ or
Months_between(sysdate,hiredate)/12 > 8 ;

201. List the Mgrs of dept 10 or 20.

SQL> select * from emp where deptno in (10,20) and job = 'MANAGER' ;

202. List the emps joined in Jan with salary ranging from 1500 to 4000.
SQL> Select * from emp where hiredate like ‘%JAN%’ and
Sal between 1500 and 4000 ;

203. List the unique jobs of depts. 20 or 30 in des order

SQL> select distinct job from emp where deptno in (10,20) order by job desc ;

204. List the emps along with the exp of those working under the mgr
whose no is starting with 7 but should not have a digit 9 joined before
1983.
SQL>

select emp.*, months_between(sysdate,hiredate)/12 Exp

from emp where mgr like '7%' and mgr not like '%9%'
and hiredate < (select '01-Jan-83' from dual) ;

CHECK OUTPUT. DATE IS GIVING WRONG RESULT.

205. List the emps who are working as either mgr or analyst with sal ranging
from 2000 and 5000 and without comm..
SQL> select * from emp where JOB IN ('MANAGER','ANALYST') and
sal between 2000 and 5000 and comm is null ;

206. List the empno, ename, sal, job of emps with the ann sal < 34000 but
receiving some comm., Which should not be > sal and designation
 should be salesman working for dept 30.
SQL> select empno, ename, sal, job from emp where
sal*12 < 34000 and comm is not null and
comm < sal and job = 'SALESMAN' and deptno=30 ;

207. List the emps who are working for dept 10 or 20 with desigs as CLERK
or analyst with a sal is either 3 or 4 digits with an exp > 8y, but not
belongs to months of MAR, APR & SEP and working for mgrs & no is
not ending with 88 or 56.
SQL> select * from emp where
deptno in (10,20) and job IN ('CLERK','ANALYST') and
length(sal) in (3,4) and months_between(sysdate,hiredate)/12 > 8
and hiredate not in (select hiredate from emp where hiredate like
'%MAR%' or
hiredate like '%APR%' or hiredate like '%SEP%') and mgr is not null
and
empno not in (select empno from emp where empno like '%88' or
empno like '%56') ;

208. List the empno, ename, sal, job, deptno & exp of all emps belongs to
dept 10 or 20 with an exp 6 to 10 yrs working under some mgr without
comm., with a job not ending with ‘MAN’ but sal > 850 or the emps
working for dept 30 with a job having a char ‘A’ irrespective of the
position with comm. > 200 with exp >= 7y and sal < 2500 but not
belongs to the month SEP or NOV working under the Mgr whose no is
not having digits either 9 or 0 in the asc dept & desc dept. [???]
SQL>

select empno, ename, sal, job, deptno,

months_between(sysdate,hiredate)/12 Exp from emp where
(deptno in (10,20) and (months_between(sysdate,hiredate)/12
between 6 and 10)
and mgr is not null and comm is null and job not like '%MAN' and sal >
850)
or
(deptno = 30 and job like '%A%' and comm > 200 and
months_between(sysdate,hiredate)/12 >= 7 and
sal < 2500 and hiredate not in (select hiredate from emp where
hiredate like '%SEP%' or hiredate like '%NOV%') and
mgr in (select empno from emp where empno not like '%9%' or empno
like '%0%'))
order by deptno desc

NO ROWS SELECTED
209. List the details of emps working at CHICAGO.
SQL> Select * from emp,dept where loc = ‘CHICAGO’ and
Dept.deptno = emp.deptno ;

210. List the empno, ename,deptno, loc of all emps

SQL> Select empno,ename,dept.deptno,loc from emp,dept where
Dept.deptno = emp.deptno ;
211. List the empno, ename, loc, dname of all depts. 10 or 20.
SQL> Select empno,ename,dname,loc from emp,dept where
Deptno in (10,20) and Dept.deptno = emp.deptno ;

212. List the empno, sal, loc of all emps working at CHICAGO, DALLAS
with an exp> 6yrs.
SQL> Select empno, sal, loc from emp, dept
where Loc in ('CHICAGO','DALLAS') and
Months_between(sysdate,hiredate)/12 > 6
and Emp.deptno = dept.deptno ;

213. List the emps along with loc of those who belongs to Dallas, new york
with sal ranging from 2000 to 5000 joined in 81.
SQL> Select ename, loc from emp, dept
where Loc in ('NEW YORK','DALLAS') and
sal between 2000 and 5000 and
hiredate like '%81'
and Emp.deptno = dept.deptno ;

214. List the empno, ename, sal, grade of all emps.

SQL> Select empno, ename, sal, grade from emp, salgrade
Where sal between losal and hisal ;

215. List the grade 2&3 emp of Chicago

SQL> Select emp.* from emp,salgrade where
Job = ‘CHICAGO’ and grade in (2,3) and sal between losal and
hisal ;

216. List the emps with loc & grade of accounting dept or the locs Dallas or
Chicago with the grades 3 to 5 & exp > 6y.
SQL> Select ename, loc, grade from emp, dept, salgrade
where job = 'ACCOUNTING' or Loc in ('chicago','DALLAS') and
grade in (3,4,5) and months_between(sysdate,hiredate)/12 > 6
and Emp.deptno = dept.deptno and sal between losal and hisal ;

217. List the grade 3 emps of research & operations depts. Joined after
1987 and whose names should not be either miller or allen.
SQL> Select ename, loc, grade, dname, job from emp, dept, salgrade
where grade = 3 and dname in ('RESEARCH','OPERATIONS')
and hiredate in (select hiredate from emp where hiredate >
'31-DEC-87') and ename not in ('MILLER','ALLEN') and
Emp.deptno = dept.deptno and sal between losal and hisal ;

NO SUCH RECORDS, SO GIVE :

Select ename, loc, grade, dname, job from
emp, dept, salgrade where grade = 3 and
dname in ('RESEARCH','OPERATIONS','SALES') and
hiredate in (select hiredate from emp where hiredate
< '31-DEC-87') and ename not in ('MILLER','ALLEN') and
Emp.deptno = dept.deptno and sal between losal and hisal ;
218. List the emps whose job is same as smith.
SQL> Select * from emp where job = (select job from emp where
ename=’SMITH’) ;

219. List the emps who are senior to miller.

SQL> Select * from emp where hiredate <
(select hiredate from emp where ename = 'MILLER') ;

220. List the emps whose job is same as either allen or sal > allen
SQL> Select * from emp where ename <> 'ALLEN' AND
(job = (select job from emp where ename = 'ALLEN') or
sal > (select sal from emp where ename = 'ALLEN')) ;

221. List the emps who are senior to their own mgr.
SQL> Select A.* from emp A, emp B where
A.mgr = B.empno and A.hiredate <
(select B.hiredate from emp where
empno = B.empno) order by A.hiredate ;

222. List the emps whose sal > blake’s

SQL> Select A.* from emp A, emp B where A.sal >
(select sal from emp where B.ename = 'BLAKE'
and emp.empno=B.empno) ;

223. List the emps who belongs to dept 10 and sal > allen’s sal
SQL> Select A.* from emp A, emp B where
A.deptno = 10 and A.sal >
(select sal from emp where B.ename =
'ALLEN' and emp.empno=B.empno) ;

OR

select * from emp where deptno= 10 and

sal > (select sal from emp where ename='ALLEN') ;

224. List the Mgrs who are senior to king & who are junior to smith.
SQL> Select * from emp where job = 'MANAGER' and
hiredate < (select hiredate from emp where ename = 'KING') and
hiredate > (select hiredate from emp where ename = 'SMITH') ;

225. List the empno, ename, loc, sal, dname,loc of all the emps belong to
king’s dept
SQL> Select empno, ename, sal, dname, loc from emp, dept, salgrade
Where emp.deptno = (select deptno from emp where ename='KING')
and
emp.deptno = dept.deptno and sal between losal and hisal

226. List the emps whose grade are > the grade of miller.
SQL> Select emp.*, grade from emp, salgrade Where grade >
(select grade from emp, salgrade Where ename = 'MILLER'
and sal between losal and hisal) and sal between losal and hisal
227. List the emps who are belongs to dallas or Chicago with the grade
same as adams or exp more than smith.
SQL> Select * from emp, dept, salgrade Where loc in
('DALLAS','CHICAGO') and (grade = (select grade
from emp,salgrade where ename='ADAMS' and
sal between losal and hisal) or
months_between(sysdate,hiredate)/12 >
(select months_between(sysdate,hiredate)/12 from
emp where ename = 'SMITH')) and ename not in ('ADAMS','SMITH')
and emp.deptno = dept.deptno and sal between losal and hisal ;

228. List the emps whose sal is same as ford or blake.

SQL> Select * from emp where sal in (select sal from emp where ename
in(‘FORD’,’BLAKE’)) ;

Select * from emp where sal in

(select sal from emp where ename = 'FORD')
UNION
(select sal from emp where ename = 'BLAKE') ;

GIVING ERROR : ‘Query block has incorrect no. of result columns.‘

229. List the emps whose sal is same as any one of the following:
a) Sal of any clerk of emp1 table.
b) Any emp of emp2 joined before 83.
c) The total remuneration (Sal+comm.) of all sales person of sales
dept belongs to emp2 table.
d) Any grade 4 emps sal of emp4 table
e) Any emp sal of emp5 table
SQL> Select * from emp Where
sal in (select sal from emp1 where job = 'CLERK') or
sal in (select sal from emp2 where hiredate > '01-JAN-83' ) or
sal in (select sal+decode(comm,null,0) from
emp2,dept where dname='SALES' and emp2.deptno=dept.deptno) or
sal in (select sal from emp4,salgrade where grade=4) or
sal in (select sal from emp5) ;
Select * from emp Where sal in (select sal from emp1
where job = 'CLERK')
union (select sal from emp2 where hiredate > '01-JAN-83' )
union (select sal+decode(comm,null,0) from emp2,dept where
dname='SALES' and emp2.deptno=dept.deptno)
union (select sal from emp4,salgrade where grade=4)
union (select sal from emp5) ;

GIVING ERROR : ‘Query block has incorrect no. of result columns.‘

230. List the highest paid emp
SQL> Select * from emp where sal = (select max(sal) from emp) ;

231. List the details of most recently hired emp of dept 30.
SQL> Select * from emp where hiredate = (select max(hiredate) from emp
where deptno = 30) and deptno = 30;
232. List the highest paid emp of Chicago joined before the most recently
hired emp of grade 2.
SQL> select emp.*,loc from emp,dept,salgrade where hiredate <
(select max(hiredate) from emp where hiredate in
(select hiredate from emp where hiredate <
(select max(hiredate) from emp,salgrade,dept where
loc = 'CHICAGO' and grade=2 and sal between losal and hisal
and
emp.deptno = dept.deptno))) and loc = 'CHICAGO' and grade=2
and
emp.deptno = dept.deptno and sal between losal and hisal

OR
select emp.*,loc from emp,dept where hiredate <
(select max(hiredate) from emp where hiredate in
(select hiredate from emp where hiredate <
(select max(hiredate) from emp,salgrade,dept where
loc = 'CHICAGO' and grade=2 and sal between losal and hisal and
emp.deptno = dept.deptno))) and loc = 'CHICAGO' and
emp.deptno = dept.deptno
SPENT 40 MINUTES ON THE ABOVE QUERY

233. List the highest paid emp working under king.

SQL> Select * from emp where sal = (select max(sal) from emp where
mgr=(select empno from emp where ename='KING')) ;

END OF BOOK QUERIES

234. List the second highest paid emp
SQL> Select * from emp where sal = (select max(sal) from emp where sal in
(select sal from emp where sal < (select max(sal) from emp))) ;

235. Find the oldest date between current date and ‘01_JAN-83’.
SQL> select greatest(sysdate,'01-JAN-83') from dual ;