SQL QUERIES

1) Display the details of all employees

SQL>Select * from emp;
2) Display the depart information from department table
SQL>select * from dept;
3) Display the name and job for all the employees
SQL>select ename,job from emp;
) Display the name and salary for all the employees
SQL>select ename,sal from emp;
!) Display the employee no and totalsalary for all the employees
SQL>select empno,ename,sal,comm, sal"n#l$comm,%) as&total salary& from
emp
') Display the employee name and ann(al salary for all employees)
SQL>select ename, 12*$sal"n#l$comm,%)) as &ann(al Sal& from emp
*) Display the names of all the employees +ho are +or,in- in depart n(mber 1%)
SQL>select emame from emp +here deptno.1%;
/) Display the names of all the employees +ho are +or,in- as cler,s and
dra+in- a salary more than 3%%%)
SQL>select ename from emp +here job.01L2340 and sal>3%%%;
5) Display the employee n(mber and name +ho are earnin- comm)
SQL>select empno,ename from emp +here comm is not n(ll;
1%) Display the employee n(mber and name +ho do not earn any comm)
SQL>select empno,ename from emp +here comm is n(ll;
11) Display the names of employees +ho are +or,in- as cler,s,salesman or
analyst and dra+in- a salary more than 3%%%)
SQL>select ename from emp +here job.01L2340 63 768.0S9L2S:9;0
63 768.09;9L<S=0 9;D S9L>3%%%;
12) Display the names of the employees +ho are +or,in- in the company for
the past ! years;
SQL>select ename from emp +here to>char$sysdate,0<<<<0)?to>char$hiredate,0<<<<0)>.!;
13) Display the list of employees +ho ha#e joined the company before
3%?7@;?5% or after 31?D21?5%)
a)select ename from emp +here hiredate A 03%?7@;?155%0 or hiredate >
031?D21?5%0;
1) Display c(rrent Date)
SQL>select sysdate from d(al;
1!) Display the list of all (sers in yo(r database$(se catalo- table))
SQL>select (sername from all>(sers;
1') Display the names of all tables from c(rrent (ser;
SQL>select tname from tab;
1*) Display the name of the c(rrent (ser)
SQL>sho+ (ser
1/) Display the names of employees +or,in- in depart n(mber 1% or 2% or %
or employees +or,in- as
1L234S,S9L2S:9; or 9;9L<S=)
SQL>select ename from emp +here deptno in$1%,2%,%) or job
in$01L234S0,0S9L2S:9;0,09;9L<S=0);
15) Display the names of employees +hose name starts +ith alaphabet S)
SQL>select ename from emp +here ename li,e 0SB0;
2%) Display the 2mployee names for employees +hose name ends +ith alaphabet S)
SQL>select ename from emp +here ename li,e 0BS0;
21) Display the names of employees +hose names ha#e second alphabet 9 in
their names)
SQL>select ename from emp +here ename li,e 0>9B0;
22) select the names of the employee +hose names is eCactly fi#e characters
in len-th)
SQL>select ename from emp +here len-th$ename).!;
23) Display the names of the employee +ho are not +or,in- as :9;9D23S)
SQL>select ename from emp +here job not in$0:9;9D230);
2) Display the names of the employee +ho are not +or,in- as S9L2S:9; 63
1L234 63 9;9L<S=)
SQL>select ename from emp +here job not
in$0S9L2S:9;0,01L2340,09;9L<S=0);
2!) Display all ro+s from emp table)=he system sho(ld +ait after e#ery
screen f(ll of informaction)
SQL>set pa(se on
2') Display the total n(mber of employee +or,in- in the company)
SQL>select co(nt$*) from emp;
2*) Display the total salary bei-in- paid to all employees)
SQL>select s(m$sal) from emp;
2/) Display the maCim(m salary from emp table)
SQL>select maC$sal) from emp;
25) Display the minim(m salary from emp table)
SQL>select min$sal) from emp;
3%) Display the a#era-e salary from emp table)
SQL>select a#-$sal) from emp;
31) Display the maCim(m salary bein- paid to 1L234)
SQL>select maC$sal) from emp +here job.01L2340;
32) Display the maCim(m salary bein- paid to depart n(mber 2%)
SQL>select maC$sal) from emp +here deptno.2%;
33) Display the minim(m salary bein- paid to any S9L2S:9;)
SQL>select min$sal) from emp +here job.0S9L2S:9;0;
3) Display the a#era-e salary dra+n by :9;9D23S)
SQL>select a#-$sal) from emp +here job.0:9;9D230;
3!) Display the total salary dra+n by 9;9L<S= +or,in- in depart n(mber %)
SQL>select s(m$sal) from emp +here job.09;9L<S=0 and deptno.%;
3') Display the names of the employee in order of salary i)e the name of
the employee earnin- lo+est salary sho(ld salary appear first)
SQL>select ename from emp order by sal;
3*) Display the names of the employee in descendin- order of salary)
a)select ename from emp order by sal desc;
3/) Display the names of the employee in order of employee name)
a)select ename from emp order by ename;
35) Display empno,ename,deptno,sal sort the o(tp(t first base on name and
+ithin name by deptno and +ith in deptno by sal)
SQL>select empno,ename,deptno,sal from emp order by
%) Display the name of the employee alon- +ith their ann(al salary$sal*12))=he name of
the employee earnin- hi-hest ann(al salary sho(ld apper first)
SQL>select ename,sal*12 from emp order by sal desc;
1) Display name,salary,hra,pf,da,total salary for each employee) =he
o(tp(t sho(ld be in the order of total salary,hra 1!B of salary,da 1%B of salary,pf !B
salary,total salary +ill be$salary"hra"da)?pf)
SQL>select ename,sal,salE1%%*1! as hra,salE1%%*! as pf,salE1%%*1% as
da, sal"salE1%%*1!"salE1%%*1%?salE1%%*! as total from emp;
2) Display depart n(mbers and total n(mber of employees +or,in- in each
department)
SQL>select deptno,co(nt$deptno)from emp -ro(p by deptno;
3) Display the #ario(s jobs and total n(mber of employees +ithin each job
-ro(p)
SQL>select job,co(nt$job)from emp -ro(p by job;
) Display the depart n(mbers and total salary for each department)
SQL>select deptno,s(m$sal) from emp -ro(p by deptno;
!) Display the depart n(mbers and maC salary for each department)
SQL>select deptno,maC$sal) from emp -ro(p by deptno;
') Display the #ario(s jobs and total salary for each job
SQL>select job,s(m$sal) from emp -ro(p by job;
*) Display the #ario(s jobs and total salary for each job
SQL>select job,min$sal) from emp -ro(p by job;
/) Display the depart n(mbers +ith more than three employees in each dept)
SQL>select deptno,co(nt$deptno) from emp -ro(p by deptno ha#in-
co(nt$*)>3;
5) Display the #ario(s jobs alon- +ith total salary for each of the jobs
+here total salary is -reater than %%%%)
SQL>select job,s(m$sal) from emp -ro(p by job ha#in- s(m$sal)>%%%%;
!%) Display the #ario(s jobs alon- +ith total n(mber of employees in each
job)=he o(tp(t sho(ld contain only those jobs +ith more than three employees)
SQL>select job,co(nt$empno) from emp -ro(p by job ha#in- co(nt$job)>3
!1) Display the name of the empployee +ho earns hi-hest salary)
SQL>select ename from emp +here sal.$select maC$sal) from emp);
!2) Display the employee n(mber and name for employee +or,in- as cler, and
earnin- hi-hest salary amon- cler,s)
SQL>select empno,ename from emp +here +here job.01L2340
and sal.$select maC$sal) from emp +here job.01L2340);
!3) Display the names of salesman +ho earns a salary more than the hi-hest
salary of any cler,)
SQL>select ename,sal from emp +here job.0S9L2S:9;0 and sal>$select
maC$sal) from emp
+here job.01L2340);
!) Display the names of cler,s +ho earn a salary more than the lo+est
salary of any salesman)
SQL>select ename from emp +here job.01L2340 and sal>$select min$sal)
from emp
+here job.0S9L2S:9;0);
Display the names of employees +ho earn a salary more than that of
7ones or that of salary -rether than that of scott)
SQL>select ename,sal from emp +here sal>
$select sal from emp +here ename.076;2S0)and sal> $select sal from emp
+here ename.0S16==0);
!!) Display the names of the employees +ho earn hi-hest salary in their
respecti#e departments)
SQL>select ename,sal,deptno from emp +here sal in$select maC$sal) from
emp -ro(p by deptno);
!') Display the names of the employees +ho earn hi-hest salaries in their
respecti#e job -ro(ps)
SQL>select ename,sal,job from emp +here sal in$select maC$sal) from emp
-ro(p by job)
!*) Display the employee names +ho are +or,in- in acco(ntin- department)
SQL>select ename from emp +here deptno.$select deptno from dept +here
dname.09116@;=F;D0)
!/) Display the employee names +ho are +or,in- in 1hica-o)
SQL>select ename from emp +here deptno.$select deptno from dept +here
L61.01GF19D60)
!5) Display the 7ob -ro(ps ha#in- total salary -reater than the maCim(m
salary for mana-ers)
SQL>S2L21= 768,S@:$S9L) H36: 2:I D36@I 8< 768 G9JF;D S@:$S9L)>$S2L21=
:9K$S9L) H36: 2:I LG232 768.0:9;9D230);
'%) Display the names of employees from department n(mber 1% +ith salary
-rether than that of any employee +or,in- in other department)
SQL>select ename from emp +here deptno.1% and sal>any$select sal from
emp +here deptno not in 1%))
'1) Display the names of the employees from department n(mber 1% +ith
salary -reater than that of all employee +or,in- in other departments)
SQL>select ename from emp +here deptno.1% and sal>all$select sal from
emp +here deptno not in 1%))
'2) Display the names of the employees in @ppercase)
SQL>select (pper$ename)from emp
'3) Display the names of the employees in Lo+ecase)
SQL>select lo+er$ename)from emp
') Display the names of the employees in Iropercase)
SQL>select initcap$ename)from emp;
'!) Display the len-th of <o(r name (sin- appropriate f(nction)
SQL>select len-th$0name0) from d(al
'') Display the len-th of all the employee names)
SQL>select len-th$ename) from emp;
'*) select name of the employee concatenate +ith employee n(mber)
SQL>select enameMMempno from emp;
'/) @ser appropriate f(nction and eCtract 3 characters startin- from 2
characters from the follo+in- strin- 06racle0) i)e the o(t p(t sho(ld be 0ac0)
SQL>select s(bstr$0oracle0,3,2) from d(al
'5) Hind the Hirst occ(rance of character 0a0 from the follo+in- strin- i)e
01omp(ter :aintenance 1orporation0)
SQL>S2L21= F;S=3$01omp(ter :aintenance 1orporation0,0a0,1) H36: D@9L
*%) 3eplace e#ery occ(rance of alphabhet 9 +ith 8 in the strin- 9llens$(se
translate f(nction)
SQL>select translate$09llens0,090,080) from d(al
*1) Display the informaction from emp table)Lhere job mana-er is fo(nd it
sho(ld be displayed as boos$@se replace f(nction))
SQL>select replace$768,0:9;9D230,086SS0) H36: 2:I;
*2) Display empno,ename,deptno from emp table)Fnstead of display department
n(mbers display the related department name$@se decode f(nction))
SQL>select
empno,ename,decode$deptno,1%,09116@;=F;D0,2%,032S2931G0,3%,0S9L2S0,%,06I39=F6;S0
) from emp;
*3) Display yo(r a-e in days)
SQL>select to>date$sysdate)?to>date$01%?sep?**0)from d(al
*) Display yo(r a-e in months)
SQL>select months>bet+een$sysdate,01%?sep?**0) from d(al
*!) Display the c(rrent date as 1!th 9(-est Hriday ;ineteen ;inety Sa#en)
SQL>select to>char$sysdate,0ddth :onth day year0) from d(al
*') Display the follo+in- o(tp(t for each ro+ from emp table)
scott has joined the company on +ednesday 13th 9(-(st ninten nintey)
SQL>select 2;9:2MM0 G9S 76F;2D =G2 16:I9;< 6; 0MMto>char$GF32D9=2,0day
ddth :onth year0) from 2:I;
**) Hind the date for nearest sat(rday after c(rrent date)
SQL>S2L21= ;2K=>D9<$S<SD9=2,0S9=@3D9<0)H36: D@9L;
*/) Display c(rrent time)
SQL>select to>char$sysdate,0hhN::Nss0) from d(al)
*5) Display the date three months 8efore the c(rrent date)
SQL>select add>months$sysdate,3) from d(al;
/%) Display the common jobs from department n(mber 1% and 2%)
SQL>select job from emp +here deptno.1% and job in$select job from emp
+here deptno.2%);
/1) Display the jobs fo(nd in department 1% and 2% 2liminate d(plicate jobs)
SQL>select distinct$job) from emp +here deptno.1% or deptno.2%
$or)
SQL>select distinct$job) from emp +here deptno in$1%,2%);
/2) Display the jobs +hich are (niO(e to department 1%)
SQL>select distinct$job) from emp +here deptno.1%
/3) Display the details of those +ho do not ha#e any person +or,in- (nder them)
SQL>select e)ename from emp,emp e +here emp)m-r.e)empno -ro(p by
e)ename ha#in- co(nt$*).1;
/) Display the details of those employees +ho are in sales department and
-rade is 3)
SQL>select * from emp +here deptno.$select deptno from dept +here
dname.0S9L2S0)and sal bet+een$select losal from sal-rade +here -rade.3)and
$select hisal from sal-rade +here -rade.3);
/!) Display those +ho are not mana-ers and +ho are mana-ers any one)
i)display the mana-ers names
SQL>select distinct$m)ename) from emp e,emp m +here m)empno.e)m-r;
ii)display the +ho are not mana-ers
SQL>select ename from emp +here ename not in$select distinct$m)ename)
from emp e,emp m +here m)empno.e)m-r);
/') Display those employee +hose name contains not less than characters)
SQL>select ename from emp +here len-th$ename)>;
/*) Display those department +hose name start +ith &S& +hile the location
name ends +ith &4&)
SQL>select dname from dept +here dname li,e 0SB0 and loc li,e 0B40;
//) Display those employees +hose mana-er name is 76;2S)
SQL>select p)ename from emp e,emp p +here e)empno.p)m-r and
e)ename.076;2S0;
/5) Display those employees +hose salary is more than 3%%% after -i#in- 2%B
increment)
SQL>select ename,sal from emp +here $sal"sal*)2)>3%%%;
5%) Display all employees +hile their dept names;
SQL>select ename,dname from emp,dept +here emp)deptno.dept)deptno
51) Display ename +ho are +or,in- in sales dept)
SQL>select ename from emp +here deptno.$select deptno from dept +here
dname.0S9L2S0);
52) Display employee name,deptname,salary and comm for those sal in bet+een
2%%% to !%%% +hile location is chica-o)
SQL>select ename,dname,sal,comm from emp,dept +here sal bet+een 2%%%
and !%%%
and loc.01GF19D60 and emp)deptno.dept)deptno;
53)Display those employees +hose salary -reter than his mana-er salary)
SQL>select p)ename from emp e,emp p +here e)empno.p)m-r and p)sal>e)sal
5) Display those employees +ho are +or,in- in the same dept +here his
mana-er is +or,)
SQL>select p)ename from emp e,emp p +here e)empno.p)m-r and
p)deptno.e)deptno;
5!) Display those employees +ho are not +or,in- (nder any mana-er)
SQL>select ename from emp +here m-r is n(ll
5') Display -rade and employees name for the dept no 1% or 3% b(t -rade is
not +hile joined the company before 31?dec?/2)
SQL>select ename,-rade from emp,sal-rade +here sal bet+een losal and
hisal and deptno in$1%,3%) and -radeA> and hiredateA031?D21?/20;
5*) @pdate the salary of each employee by 1%B increment +ho are not
eli-ibl+ for commission)
SQL>(pdate emp set sal.sal"sal*1%E1%% +here comm is n(ll;
5/) S2L21= those employee +ho joined the company before 31?dec?/2 +hile
their dept location is ne+yor, or 1hica-o)
SQL>S2L21= 2:I;6,2;9:2,GF32D9=2,D;9:2,L61 H36: 2:I,D2I=
LG232 $2:I)D2I=;6.D2I=)D2I=;6)9;D
GF32D9=2 A031?D21?/20 9;D D2I=)L61 F;$01GF19D60,0;2L <6340);

55) DFSIL9< 2:IL6<22 ;9:2,768,D2I93=:2;=,L619=F6; H63 9LL LG6 932 L634F;D
9S :9;9D23P
SQL>select ename,768,D;9:2,L619=F6; from emp,D2I= +here m-r is not
n(ll;
1%%) DFSIL9< =G6S2 2:IL6<22S LG6S2 :9;9D23 ;9:2 FS 76;2SP ??
Q9;D 9LS6 DFSIL9< =G2F3 :9;9D23 ;9:2RP
SQL> S2L21= I)2;9:2 H36: 2:I 2, 2:I I LG232 2)2:I;6.I):D3 9;D
2)2;9:2.076;2S0;
1%1) Display name and salary of ford if his salary is eO(al to hisal of his
-rade
a)select ename,sal,-rade from emp,sal-rade +here sal bet+een losal and
hisal
and ename .0H63D0 9;D GFS9L.S9L;
1%2) Display employee name,job,depart name ,mana-er name,his -rade and ma,e
o(t an (nder department +iseP
SQL>S2L21= 2)2;9:2,2)768,D;9:2,2:I)2;9:2,D39D2 H36: 2:I,2:I
2,S9LD39D2,D2I=
LG232 2:I)S9L 82=L22; L6S9L 9;D GFS9L 9;D 2:I)2:I;6.2):D3
9;D 2:I)D2I=;6.D2I=)D2I=;6 63D23 8< D;9:2
1%3) List o(t all employees name,job,salary,-rade and depart name for e#ery
one in the company eCcept 01L2340)Sort on salary display the hi-hest salaryP
SQL>S2L21= 2;9:2,768,D;9:2,S9L,D39D2 H36: 2:I,S9LD39D2,D2I= LG232
S9L 82=L22; L6S9L 9;D GFS9L 9;D 2:I)D2I=;6.D2I=)D2I=;6 9;D 768
;6= F;$01L2340)63D23 8< S9L 9S1;
1%) Display the employee name,job and his mana-er)Display also employee +ho
are +itho(t mana-erP
SQL>select e)ename,e)job,e:I)ename 9S :ana-er from emp,emp e +here
emp)empno$").e)m-r
1%!) Hind o(t the top ! earners of companyP
SQL>S2L21= DFS=F;1= S9L H36: 2:I 2 LG232 !>.$S2L21= 16@;=$DFS=F;1= S9L)
H36:
2:I 9 LG232 9)S9L>.2)S9L)63D23 8< S9L D2S1;
1%') Display name of those employee +ho are -ettin- the hi-hest salaryP
SQL>select ename from emp +here sal.$select maC$sal) from emp);
1%*) Display those employee +hose salary is eO(al to a#era-e of maCim(m and
minim(mP
SQL>select ename from emp +here sal.$select maC$sal)"min$sal)E2 from
emp);
1%/) Select co(nt of employee in each department +here co(nt -reater than 3P
SQL>select co(nt$*) from emp -ro(p by deptno ha#in- co(nt$deptno)>3
1%5) Display dname +here at least 3 are +or,in- and display only department
nameP
SQL>select distinct d)dname from dept d,emp e +here d)deptno.e)deptno
and 3>any
$select co(nt$deptno) from emp -ro(p by deptno)
11%) Display name of those mana-ers name +hose salary is more than a#era-e
salary of his companyP
SQL>S2L21= 2)2;9:2,2:I)2;9:2 H36: 2:I,2:I 2
LG232 2:I)2:I;6.2):D3 9;D 2)S9L>$S2L21= 9JD$S9L) H36: 2:I);
111)Display those mana-ers name +hose salary is more than a#era-e salary of
his employeeP
SQL>S2L21= DFS=F;1= 2:I)2;9:2 H36: 2:I,2:I 2 LG232
2)S9L A$S2L21= 9JD$2:I)S9L) H36: 2:I
LG232 2:I)2:I;6.2):D3 D36@I 8< 2:I)2;9:2) 9;D
2:I)2:I;6.2):D3;
112) Display employee name,sal,comm and net pay for those employee
+hose net pay is -reter than or eO(al to any other employee salary of
the companyP
SQL>select ename,sal,comm,sal"n#l$comm,%) as ;etIay from emp
+here sal"n#l$comm,%) >any $select sal from emp)
113) Display all employees names +ith total sal of company +ith each
employee nameP
SQL>S2L21= 2;9:2,$S2L21= S@:$S9L) H36: 2:I) H36: 2:I;
11) Hind o(t last !$least)earners of the company)P
SQL>S2L21= DFS=F;1= S9L H36: 2:I 2 LG232
!>.$S2L21= 16@;=$DFS=F;1= S9L) H36: 2:I 9 LG232
9)S9LA.2)S9L)
63D23 8< S9L D2S1;
11!) Hind o(t the n(mber of employees +hose salary is -reater than their
mana-er salaryP
SQL>S2L21= 2)2;9:2 H36: 2:I ,2:I 2 LG232 2:I)2:I;6.2):D3
9;D 2:I)S9LA2)S9L;
11') Display those department +here no employee +or,in-P
SQL>select dname from emp,dept +here emp)deptno not in$emp)deptno)
11*) Display those employee +hose salary is 6DD #al(eP
SQL>select * from emp +here salA%;
11/) Display those employee +hose salary contains alleast 3 di-itsP
SQL>select * from emp +here len-th$sal)>.3;
115) Display those employee +ho joined in the company in the month of DecP
SQL>select ename from emp +here to>char$hiredate,0:6;0).0D210;
12%) Display those employees +hose name contains &9&P
SQL>select ename from emp +here instr$ename,090)>%;
or
SQL>select ename from emp +here ename li,e$0B9B0);
121) Display those employee +hose deptno is a#ailable in salaryP
SQL>select emp)ename from emp, emp e +here emp)sal.e)deptno;
122) Display those employee +hose first 2 characters from hiredate ?last 2
characters of salaryP
SQL>select ename,S@8S=3$hiredate,1,2)MM2;9:2MMs(bstr$sal,?2,2) from emp
123) Display those employee +hose 1%B of salary is eO(al to the year of
joinin-P
SQL>select ename from emp +here to>char$hiredate,0<<0).sal*%)1;
12) Display those employee +ho are +or,in- in sales or researchP
SQL>S2L21= 2;9:2 H36: 2:I LG232 D2I=;6 F;$S2L21= D2I=;6 H36: D2I= LG232
D;9:2 F;$0S9L2S0,032S2931G0));
12!) Display the -rade of jonesP
SQL>S2L21= 2;9:2,D39D2 H36: 2:I,S9LD39D2
LG232 S9L 82=L22; L6S9L 9;D GFS9L 9;D 2name.076;2S0;
12') Display those employees +ho joined the company before 1! of the monthP
a)select ename from emp +here to>char$hiredate,0DD0)A1!;
12*) Display those employee +ho has joined before 1!th of the month)
a)select ename from emp +here to>char$hiredate,0DD0)A1!;
12/) Delete those records +here no of employees in a partic(lar department
is less than 3)
SQL>delete from emp +here deptno.$select deptno from emp
-ro(p by deptno ha#in- co(nt$deptno)A3);
125) Display the name of the department +here no employee +or,in-)
SQL> S2L21= 2)2;9:2,2)768,:)2;9:2,:)768 H36: 2:I 2,2:I :
LG232 2):D3.:)2:I;6
13%) Display those employees +ho are +or,in- as mana-er)
SQL>S2L21= :)2;9:2 :9;9D23 H36: 2:I : ,2:I 2
LG232 2):D3.:)2:I;6 D36@I 8< :)2;9:2
131) Display those employees +hose -rade is eO(al to any n(mber of sal b(t
not eO(al to first n(mber of salP
SQL> S2L21= 2;9:2,D39D2 H36: 2:I,S9LD39D2
LG232 D39D2 ;6= F;$S2L21= S@8S=3$S9L,%,1)H36: 2:I)
132) Irint the details of all the employees +ho are S(b?ordinate to 8L942P
SQL>select emp)ename from emp, emp e +here emp)m-r.e)empno and
e)ename.08L9420;
133) Display employee name and his salary +hose salary is -reater than
hi-hest a#era-e of department n(mberP
SQL>S2L21= S9L H36: 2:I LG232 S9L>$S2L21= :9K$9JD$S9L)) H36: 2:I
D36@I 8< D2I=;6);
13) Display the 1%th record of emp table$+itho(t (sin- ro+id)
SQL>S2L21= * H36: 2:I LG232 36L;@:A11
:F;@S
S2L21= * H36: 2:I LG232 36L;@:A1%
13!) Display the half of the ename0s in (pper case and remainin- lo+ercaseP
SQL>S2L21=
S@8S=3$L6L23$2;9:2),1,3)MMS@8S=3$@II23$2;9:2),3,L2;D=G$2;9:2))
H36: 2:I;
13') Display the 1%th record of emp table +itho(t (sin- -ro(p by and ro+idP
SQL>S2L21= * H36: 2:I LG232 36L;@:A11
:F;@S
S2L21= * H36: 2:I LG232 36L;@:A1%
Delete the 1%th record of emp table)
SQL>D2L2=2 H36: 2:I LG232 2:I;6.$S2L21= 2:I;6 H36: 2:I LG232 36L;@:A11
:F;@S
S2L21= 2:I;6 H36: 2:I LG232 36L;@:A1%)
13*) 1reate a copy of emp table;
SQL>create table ne+>table as select * from emp +here 1.2;

13/) Select ename if ename eCists more than once)
SQL>select ename from emp e -ro(p by ename ha#in- co(nt$*)>1;
135) Display all enames in re#erse orderP$S:F=GNG=F:S))
SQL>S2L21= 32J23S2$2;9:2)H36: 2:I;
1%) Display those employee +hose joinin- of month and -rade is eO(al)
SQL>S2L21= 2;9:2 H36: 2:I LG232 S9L 82=L22;
$S2L21= L6S9L H36: S9LD39D2 LG232
D39D2.=6>1G93$GF32D9=2,0::0)) 9;D
$S2L21= GFS9L H36: S9LD39D2 LG232
D39D2.=6>1G93$GF32D9=2,0::0));
11) Display those employee +hose joinin- D9=2 is a#ailable in deptno)
SQL>S2L21= 2;9:2 H36: 2:I LG232 =6>1G93$GF32D9=2,0DD0).D2I=;6
12) Display those employees name as follo+s
9 9LL2;
8 8L942
SQL> S2L21= S@8S=3$2;9:2,1,1),2;9:2 H36: 2:I;
13) List o(t the employees ename,sal,IH$2%B 6H S9L) from emp;
SQL>S2L21= 2;9:2,S9L,S9L*)2 9S IH H36: 2:I;
1) 1reate table emp +ith only one col(mn empno;
SQL>1reate table emp as select empno from emp +here 1.2;
1!) 9dd this col(mn to emp table ename #rachar2$2%))
SQL>alter table emp add$ename #archar2$2%));
1') 6ops F for-ot -i#e the primary ,ey constraint) 9dd in no+)
SQL>alter table emp add primary ,ey$empno);
1*) ;o+ increase the len-th of ename col(mn to 3% characters)
SQL>alter table emp modify$ename #archar2$3%));
1/) 9dd salary col(mn to emp table)
SQL>alter table emp add$sal n(mber$1%));
15) F +ant to -i#e a #alidation sayin- that salary cannot be -reater 1%,%%%
$note -i#e a name to this constraint)
SQL>alter table emp add constraint ch,>%%1 chec,$salA.1%%%%)
1!%) Hor the time bein- F ha#e decided that F +ill not impose this #alidation):y boss has
a-reed to pay more than 1%,%%%)
SQL>a-ain alter the table or drop constraint +ith alter table emp drop constraint ch,>%%1
$or)Disable the constraint by (sin- alter table emp modify constraint ch,>%%1 disable;
1!1) :y boss has chan-ed his mind) ;o+ he doesn0t +ant to pay more than
1%,%%%)so re#o,e that salary constraint)
SQL>alter table emp modify constraint ch,>%%1 enable;
1!2) 9dd col(mn called as m-r to yo(r emp table;
SQL>alter table emp add$m-r n(mber$!));
1!3) 6hS =his col(mn sho(ld be related to empno) Di#e a command to add this
constraint)
SQL>9L=23 =98L2 2:I 9DD 16;S=39F;= :D3>D2I= H632FD; 42<$:D3) 32H232;12S
2:I$2:I;6)

1!) 9dd deptno col(mn to yo(r emp table;
SQL>alter table emp add$deptno n(mber$!));
1!!) =his deptno col(mn sho(ld be related to deptno col(mn of dept table;
SQL>alter table emp add constraint dept>%%1 forei-n ,ey$deptno)
reference dept$deptno)
Qdeptno sho(ld be primary ,eyR
1!') Di#e the command to add the constraint)
SQL>alter table Atable>name) add constraint Aconstraint>name>
Aconstraint type>
1!*) 1reate table called as ne+emp) @sin- sin-le command create this table
as +ell as -et data into this table$(se create table as);
SQL>create table ne+emp as select * from emp;
SQL>1reate table called as ne+emp) =his table sho(ld contain only
empno,ename,dname)
SQL>create table ne+emp as select empno,ename,dname from emp,dept +here
1.2;
1!/) Delete the ro+s of employees +ho are +or,in- in the company for more
than 2 years)
SQL>delete from emp +here $sysdate?hiredate)E3'!>2;
1!5) Iro#ide a commission$1%B 1omm 6f Sal) to employees +ho are not earnin-
any commission)
SQL>select sal*%)1 from emp +here comm is n(ll
1'%) Ff any employee has commission his commission sho(ld be incremented by
1%B of his salary)
SQL>(pdate emp set comm.sal*)1 +here comm is not n(ll;
1'1) Display employee name and department name for each employee)
SQL>select empno,dname from emp,dept +here emp)deptno.dept)deptno
1'2)Display employee n(mber,name and location of the department in +hich he
is +or,in-)
SQL>select empno,ename,loc,dname from emp,dept +here
emp)deptno.dept)deptno;
1'3) Display ename,dname e#en if there are no employees +or,in- in a
partic(lar department$(se o(ter join))
SQL>select ename,dname from emp,dept +here emp)deptno.dept)deptno$")
1') Display employee name and his mana-er name)
SQL>select p)ename,e)ename from emp e,emp p +here e)empno.p)m-r;
1'!) Display the department name and total n(mber of employees in each
department)
SQL>select dname,co(nt$ename) from emp,dept +here
emp)deptno.dept)deptno -ro(p by dname;
1'')Display the department name alon- +ith total salary in each department)
SQL>select dname,s(m$sal) from emp,dept +here emp)deptno.dept)deptno
-ro(p by dname;
1'*) Display itemname and total sales amo(nt for each item)
SQL>select itemname,s(m$amo(nt) from item -ro(p by itemname;
1'/) Lrite a Q(ery =o Delete =he 3epeted 3o+s from emp table;
SQL>Delete from emp +here ro+id not in$select min$ro+id)from emp -ro(p
by ename)
1'5) =6 DFSIL9< ! =6 * 36LS H36: 9 =98L2
SQL>select ename from emp
+here ro+id in$select ro+id from emp +here ro+n(mA.*
min(s
select ro+id from empi +here ro+n(mA!)
1*%) DFSIL9< =6I ; 36LS H36: =98L2P
SQL>S2L21= * H36:
$S2L21= * H36: 2:I 63D23 8< 2;9:2 D2S1)
LG232 36L;@: A1%;
1*1) DFSIL9< =6I 3 S9L93F2S H36: 2:I;
SQL>S2L21= S9L H36: $ S2L21= * H36: 2:I 63D23 8< S9L D2S1 )
LG232 36L;@: A
1*2) DFSIL9< 5th H36: =G2 2:I =98L2P
SQL>S2L21= 2;9:2 H36: 2:I
LG232 36LFD.$S2L21= 36LFD H36: 2:I LG232 36L;@:A.1%
:F;@S
S2L21= 36LFD H36: 2:I LG232 36L;@: A1%)
select second maC salary from emp;
select maC$sal) fromemp +here salA$select maC$sal) from emp);
??????******??????