TNSNAMES.

ORA is the file where configuration is

needed.
Single row functions
1.
a.
select employee_id,concat(last_name,first_name) as
fullname,length(last_name),lpad(round(salary),10,'*'),instr(lower(last_name),'k')as instr from
employees where substr(lower(last_name),1,1)'k'
b. select employee_id,last_name, length(last_name),substr(last_name,1,!) from employees
(takes only first ! letters of last name)
c. select employee_id,last_name, length(last_name),substr(last_name,"!) from employees (last !
letters)
d. select last_name,#ob_id,salary from employees where #ob_id like (upper('$clerk$')) or #ob_id
like '$%&'$' and salary ()* +((,-00,!-00,.000)
e. select last_name as employee,salary as /monthly salary/,commission_pct from employees
where (commission_pct*100),0
f. select replace(last_name,'an',11) from employees
,.select round(0!.,!-),trunc(0!.,!-),mod(-00,000) from dual
!.select round(0!.,!-,,),trunc(0!.,!-,,),mod(-00,000) from dual
0.select sysdate from dual
-.
a.
select (sysdate1,) from dual
b.
select last_name,hire_date,salary,(round((sysdate"hire_date)2!3-)) as
*enure_4ears,to_char(ne5t_day(add_months(hire_date,3),'6onday'),'fmddspth month 4444') as
%e7iew from employees
c.
select last_name,hire_date,to_char(hire_date,'day') from employees
d.
select last_name,n7l,(commission_pct,to_char((commission_pct)*salary),'no commission') from
employees order by commission_pct asc
3. finding the employees serving > years will get !enifits
a.select last_name,hire_8ate,round((sysdate"hire_date)2.)as weeks,round((sysdate"
hire_date)2!3-) as year from employees
b select employee_id,salary,salary*10 as gratuaty,round((sysdate"hire_date)2!3-) as year from
employees
where round((sysdate"hire_date)2!3-)9-
c. select last_name,employee_id,salary,salary*-*n7l(commission_pct,1) as
gratuaty,round((sysdate"hire_date)2!3-,0) as years from employees where ((sysdate"
hire_date)2!3-)9-
..
a.
select last_name,salary,hire_date,ne5t_day(sysdate,'friday') as last_day,round(months_between
(sysdate,hire_date)21,) as tenure,add_months (hire_date,30) as gratuity_year from employees
b.
select last_name,salary from employees where salary not between -000 and 1,000
c.
select last_name,salary from employees where salary not between -000 and 1,000 and salary
not between ,0000 and ,:000
d.
select last_name,#ob_id,hire_date from employees where hire_date between ',";eb"1::<' and
'01"6ay"1::<'
<.select last_name,salary,hire_date,to"char(hire_date,'dd month yy') as hire_8ate from
employees
:.
a.
select last_name,to"char(salary,'=::,:::.00') as salary from employees where
lower(last_name)'ernst'
b.
select employee_id as &6'>,last_name as employee,#ob_id as ?ob,hire_date as /@ire 8A*&/
from employees order by employee_id
10. #hanging date format
select last_name,hire_date from employees where hire_date to"date('6ay ,0,1:::','f5month
dd,yyyy')
11. Replacing any Null value in a column with a given num!er or char
select last_name,nvl$to"char$manager"id%&'no manager'% as manager from employees where
manager_id is null ( to make the null 7alue as character)
1,. $since the manager"id is defined as num!er so the comand can !e written directly if
manager"id would have !een defined as varchar then the command would !e li(e No ))%
select last_name,n7l(manager_id,0) from employees
1!. N*+ command using with if then else function. if not !lan( then sal,commi else sal
only
select last_name,employee_id,salary,commission_pct,salary*nvl$commission"pct&)% as
bonus,n7l,$commission"pct&'sal,commi'&'sal only'% as income from employees

10.
a. select last_name,employee_id,salary,commission_pct,salary*n7l(commission_pct,1) as
bonus,nvl-(commission_pct,'sal1commi','sal only') as income from employees
b.select last_name,salary,n7l(to_char(commission_pct),'null') as comm,round((sysdate"
hire_date)2!3-,0) as ser7ing_years,salary*10*commission_pct as gratuity from employees where
round((sysdate"hire_date)2!3-,0)910
).
a.select first"name&length$first"name% as .No).&last"name&length$last"name% as
/(o,/,nullif(Bength(first_name),length(last_name)) from employees where
lower(last_name)'ernst'
b.
select last_name,#ob_id from employees where manager_id is (CBB
c.
select last_name,salary,commission_pct from employees where commission_pct is not (CBB
13.
a.
selectlast_name,manager_id,salary,commission_pct,coalesce(commission_pct,manager_id,salar
y,10) as comm from employees
c.
select last_name,salary from employees where (salary 9-000 A(8 salary D10000) or
(salary91-000 and salary D1:000) or (salary 9,:000 and salary D!,000)order by salary asc
1.. #ase study using when &then.
a.
select last_name,employee_id,salary,
case
when salary9!1:00 then 1.1*salary
when salary9,0000 and salaryD!1:00 then 1.,*salary
when salary910000 and salaryD,0000 then 1.!*salary
else salary
end /bonus/
from employees
b.
select e.&6'B)4&&_+8,
case when #ob_id 'E@_FB&%G' and salary,300 then 'A'
when #ob_id 'A8_AEE*' and salary0000 then 'H'
else 'F' end as #ob_catg,
e.EABA%4
from
employees e
c.
select e.&6'B)4&&_+8,e.salary,
case
when #ob_id '+*_'%)I' and salary between 1,000 and !0000 and employee_id between 10!
and 10- then 'A'
when #ob_id 'A8_AEE*' and salary0000 then 'H'
else 'F' end as #ob_catg
from
employees e
1<.
a.
select last_name,employee_id,salary,
decode
(salary,,0000 ,1.1*salary,
salary)
bonus
from employees
b.
select last_name,#ob_id,
decode
(#ob_id, 'A8_'%&E','A',
'E*_6A(','H',
'+*_'%)I','F',
'EA_%&'','8',
'E*_FB&%G','&',
0)
as I from employees
order by I
8&EF
1:.select last_name,employee_id,salary,
case salary
when !1:00 then 1.1*salary
else salary
end /bonus/
from employees
,0.
select last_name,employee_id,salary,
decode( trunc(salary21000,0),
0,1.1*salary,
1,1.,*salary,
!,1.,*salary,
0,1.,*salary,
-,1.,*salary,
3,1.!*salary,
.,1.!*salary,
<,1.!*salary,
:,1.!*salary,
salary
)
re7ised_salary
from employees
/ata from multiple ta!les
,1.E0ui1oins
select e.last_name,e.employee_id,d.department_id,d.manager_id from employees e,departments d where
e.department_idd.department_id order by d.department_id asc
,,. E0ui1oins
select e.last_name,e.employee_id,d.department_id,e.manager_id,d.department_name from employees
e,departments d
where e.department_idd.department_id and lower(d.department_name)lower('shipping')
order by e.last_name asc
,!. E0ui1oins with AN/ operators
select e.last_name,e.employee_id,d.department_id,e.manager_id,d.department_name from employees
e,departments d
where e.department_idd.department_id and substr(lower(d.department_name),1,1)'s'
order by e.last_name asc
,0. E0ui1oins with AN/ operators and from more than - ta!les
select e.last_name,e.employee_id,d.department_id,e.manager_id,d.department_name,l.location_id from
employees e,departments d,locations l
where e.department_idd.department_id and d.location_idl.location_id and
substr(lower(d.department_name),1,1)'s'
order by e.last_name asc
,-. Non2e0ui1oins
select e.first_name,e.last_name,e.salary,#.#ob_title from employees e ,#obs # where e.salary between
#.min_salary and #.ma5_salary order by salary asc
,3. Outer21oins
select e.last_name,e.department_id,d.department_name,e.manager_id,e.salary
from employees e,departments d
where e.department_idd.department_id(1) order by last_name asc ( + sign means the data will give the data
which is in employees but not in departments)
,3. Self 1oin
select worker.last_nameJJ' 'JJ'works for'JJmanager.last_name
from employees worker ,employees manager
where worker.manager_idmanager.employee_id
,.. #ross 1oins
select last_name,department_name,salary
from employees
cross #oin departments order by department_name asc (each cell multiplies with each column)
,<. Natural 3oins
select last_name,department_name,employees.department_id,departments.location_id,city
from departments,locations,employees
where departments.location_idlocations.location_id
and employees.department_iddepartments.department_id
order by last_name asc
a. 1Kst it matches department_id in employees with department_id in departments
b. then it matches department_id in departments with department_id in locations
c. then it puts its locations against all department_id in employees
,:. Natural 3oins with 4N
a.
select last_name,department_id from employees where department_id +((,0,-0) order by last_name
b.
Eelect
employees.last_name,departments.department_name,employees.department_id,departments.location_id,city
from departments,locations,employees
where departments.location_idlocations.location_id
and employees.department_iddepartments.department_id
and departments.department_id +((100)
order by last_name asc
!0. Natural 3oins with using&4N
select e.employee_id,d.location_id,e.salary
from employees e #oin departments d
using (department_id)
where department_id in(-0,30) (department_id can not be selected)
!1. Natural 1oins with ON and 5 way 1oins
select e.employee_id,d.location_id,e.salary,d.department_id,l.city
from employees e
#oin departments d
on (e.department_idd.department_id)
#oin locations l
on d.location_idl.location_id
!,. Natural 1oins with ON&4N and 5 way 1oins
select e.employee_id,d.location_id,e.salary,d.department_id,l.city
from employees e
#oin departments d
on (e.department_idd.department_id)
#oin locations l
on d.location_idl.location_id
and d.department_id in (-0,30)
!!a. +eft outer 1oin retrieving data from 5 ta!les employees&departments&locations. 6ere all rows from
employees will !e selected even last"name not having department"id
select e.last_name,e.department_id,d.department_name,d.location_id,l.city
from employees e
left outer #oin departments d
on( e.department_idd.department_id)
left outer #oin locations l
on (d.location_idl.location_id)
!!b. +eft outer 1oin retrieving data from 5 ta!les employees&departments&locations. 6ere all rows from
departments will !e selected .
select e.last_name,e.department_id,d.department_name,d.location_id,l.city
from departments d
left outer #oin employees e
on( e.department_idd.department_id)
left outer #oin locations l
on (d.location_idl.location_id)
!0. right outer 1oin retrieving all data from 5 ta!les employees&departments&locations.6ere all the rows
will !e selected from locations
select e.employee_id,e.last_name,e.department_id,d.department_name,d.location_id,l.city
from employees e
right outer #oin departments d
on( e.department_idd.department_id)
right outer #oin locations l
on (d.location_idl.location_id)
order by last_name asc
!-. 7ull outer 1oin retrieving all data from 5 ta!les employees&departments&locations.6ere all the rows
will !e selected from locations+employees+departments
select e.employee_id,e.last_name,e.department_id,d.department_name,d.location_id,l.city
from employees e
full outer #oin departments d
on( e.department_idd.department_id)
full outer #oin locations l
on (d.location_idl.location_id)
order by last_name asc
Aggregating data using group functions
!3.
a.
select trunc(a7g(salary),,) as a7erage_salary,ma5(salary),
min(salary),sum(salary)
from employees
where lower(#ob_id) like '$rep$'
b.
select last_name from employees where last_name like '$a$' and last_name like '$e$'
!.. using lower function
select trunc(a7g(salary),,) as a7erage_salary,ma5(salary),
min(salary),sum(salary),Fount(last_name),count(commission_pct)
from employees
where lower(#ob_id) like '$rep$'
!<. counting department"id
a.select count(distinct department_id)
from employees
b. select count(salary) from employees where salary9-000
00.
a.n7l puts 0 for all null 7alues,
select trunc(a7g(n7l(commission_pct,0)),,) from employees .0.<!01<<3.:,0-,<!01<<3.:,0-,<!01<<3.:,-
01.
select sum(commission_pct)2count(employee_id) from employees .0.<!01<<3.:,0-,<!01<<3.:,0-,<!01<<3.:,-
0,.
select a7g(n7l(commission_pct,0))*sum(salary) from employees
0!. sum of salary department"id wise group !y
a.
select department_id,#ob_id,sum(salary)
from employees
group by department_id,#ob_id
order by department_id asc
b. E&B&F* data1.Fell, data1.(eighbor,sum(data1.L;ailed @)M) as failed_ho,sum( data1.LEuccessful @)M) as
succ_ho, sum(data1.L@) AttemptsM) as ho_att,round((sum( data1.LEuccessful @)M)*1002sum(data1.L@)
AttemptsM)),0) as sr_ho
;%)6 data1 group by data1.cell,data1.neighborN
00. 7inding sum of salary&department"name from employees ta!le 8 departments !y matching
department"id from !oth ta!les
select d.department_id,sum(e.salary)
from employees e ,departments d
where e.department_idd.department_id
group by d.department_id
order by d.department_id asc
0-. 7inding sum of salary&department"name from employees ta!le 8 departments !y matching
department"id from !oth ta!les
select d.department_id,sum(e.salary),d.department_name
from employees e ,departments d
where e.department_idd.department_id
group by d.department_id,d.department_name
order by d.department_id asc
03. 6aving clause. 9here can not !e used !efore avg$e.salary%
select d.department_id,trunc(a7g(e.salary),0),d.department_name
from employees e ,departments d
where e.department_idd.department_id
group by d.department_id,d.department_name
ha7ing a7g(e.salary)9<000
order by d.department_id asc
0.. using +i(e :
a.
select e.#ob_id,d.department_id,trunc(a7g(e.salary),0),d.department_name
from employees e ,departments d
where e.department_idd.department_id
and lower(e.#ob_id) like 's$'
group by d.department_id,d.department_name,e.#ob_id
ha7ing a7g(e.salary)9<000
order by d.department_id asc
b.
select last_name,salary from employees where last_name like 'G$' )% last_name like '8$'
Su!0ueries
0<. Single row su!0ueries where su!20uery return only one row.
select e.last_name,e.department_id,d.department_name,e.salary
from employees e,departments d
where e.department_idd.department_id
and salary9 (select salary
from employees
where lower(last_name)'abel')
order by e.last_name
asc
0:.
select e.last_name,e.department_id,d.department_name,e.salary,e.#ob_id
from employees e,departments d
where e.department_idd.department_id
and #ob_id(select #ob_id
from employees
where employee_id101)
and salary9 (select salary
from employees
where employee_id10!)
order by e.last_name
asc
-0. ;sing group function in su!0ueries&the e<ample displays the employee last"name&1o!"id and salary
of all employees whose salary is e0uals to minimum salary.
select last_name,#ob_id,salary
from employees
where salary (select min(salary) from employees)
-1. =roup !y function finds the min salary of all department"id
select department_id,min(salary)
from employees
group by department_id
-,. =roup !y function finds the min salary of all department"id > the min salary of any particular
department"id
select department_id,min(salary)
from employees
group by department_id
ha7ing min(salary)9(select min(salary)
from employees
where department_id-0)
-!. Multiple row su!0ueries where su!20uery returns only one row. 7inds the employees who earns the
same salary as minimum salary in each department.
select employee_id,last_name,salary,department_id,#ob_id
from employees
where salary +((select 6in(salary)
from employees
group by department_id)
-0. a.This command will return all employee> salary whose salary is less than ma< salary in 4T
department
select employee_id,last_name,#ob_id,salary
from employees
where salary ? any
(select salary
from employees
where lower(#ob_id)'it_prog')
and lower(#ob_id)D9'it_prog'
order by salary asc
b. This command will return all employee> salary whose salary is more than min salary in 4T
department
select employee_id,last_name,#ob_id,salary
from employees
where salary > any
(select salary
from employees
where lower(#ob_id)'it_prog')
and lower(#ob_id)D9'it_prog'
order by salary asc
c. This command will return all employee> salary whose salary is E@;A+S TO any salary in 4T
department
select employee_id,last_name,#ob_id,salary
from employees
where salary any
(select salary
from employees
where lower(#ob_id)'it_prog')
and lower(#ob_id)D9'it_prog'
order by salary
asc
d. This command will return all employee> salary in any department whose$all employees in that
department% salary is less than the min salary in 4T department
select employee_id,last_name,#ob_id,salary
from employees
where salary D all
(select salary
from employees
where lower(#ob_id)'it_prog')
and lower(#ob_id)D9'it_prog'
order by salary
asc
--.
a. To find the employees who have the su!ordinates
select emp.last_name
from employees emp
where emp.employee_id +(
(select mgr.manager_id
from employees mgr)
b. To find the employees who don>t have the su!ordinates
select emp.last_name
from employees emp
where emp.employee_id ()* +(
(select mgr.manager_id
from employees mgr) ( *his command does not wor( cause one of the value in su! 0uery returns N;++
value.)
select last_name from employees
where employee_id ()* +(
(select manager_id
from employees
where manager_id +E ()* (CBB)
A+BS@+
--.
select employee_id,last_name,salary,department_id
from employees
where lower(#ob_id)O#ob_title
2
-3. To find varia!le select statement&condition
select employee_id,last_name,salary,department_id,
lower$8column"name%
from employees
where 8condition
order by 8order"column
2
-..
;irst write the statement
select employee_id,last_name,salary,department_id
from employees
where employee_idOemployee_num
second define in the
EPB9 define employee_num,00N
!Krd only put
EQl92
-<. /M+ statements 4nsert rows to a ta!le inserting values
4mplicit method
insert into departments(department_id,department_name)
7alues (!00,''ublic')
-:. /M+ statements 4nsert rows to a ta!le inserting values
e<plicit method
insert into departments(department_id,department_name,manager_id,location_id)
7alues (!-0,''ublic',(ull,(ull)
30. /M+ statements 4nsert rows to a ta!le inserting values
in varia!le method
insert into departments
(department_id,department_name,location_id)
7alues(Odepartment_id,'Odepartment_name',Olocation)
2
31. #reating ta!le form another ta!le and ta(ing some specified columns
create table sales_reps
as select
employee_id,last_name,salary,commission_pct from employees where department_id:0
3,. #oying rows from another ta!le. Adding some specific columns to the created ta!le sales"reps. 4t
also inserts the same row information even though it e<ist in the ta!le !efore inserting
insert into sales_reps (employee_id,last_name,salary,commission_pct)
select employee_id,last_name,salary,commission_pct
from employees
where #ob_id like '$%&'$' {column 80 in emplyees table}
EMA+OCEE"4 / +AST"NAME SA+ARC #OMM4 SS4 ON"A#T
100 Ging !1:00 .-
100 Ging !1:00 .-
3!. ;pdating row value in a particular ta!le
update sales_reps
set commission_pct0..
where employee_id100
30.
a. first finding the current status
select last_name,employee_id,#ob_id,salary,commission_pct from employees
where employee_id in (,0-,110)
+AST"NAME EMA+OCEE"4 / 3OD"4 / SA+ARC #OMM4 SS4 ON"A#T
%aphaely 110 'C_6A( 1,100 "
@iggins ,0- AF_6I% 1!,00 "
b. updating , columns with a subQuery matching with other employee.
update employees
set 1o!"id(select #ob_id from employees
where employee_id,0-),
salary(select salary from employees
where employee_id,0-)
where employee_id110
+AST"NAME EMA+OCEE"4 / 3OD"4 / SA+ARC #OMM4 SS4 ON"A#T
%aphaely 110 AF_6I% 1!,00 "
@iggins ,0- AF_6I% 1!,00 "
3-. E&B&F* data!.date,data!.cell,right(data!.cell,0) as nei,mid(data!.cell,,1,.) as
cell_name,data!.fail,data!.succ,(data!.fail1data!.succ) as att
;%)6 data!N
33.
E&B&F* data1.Fell, data1.(eighbor,sum(data1.L;ailed @)M) as failed_ho,sum( data1.LEuccessful @)M) as
succ_ho, sum(data1.L@) AttemptsM) as ho_att,round((sum( data1.LEuccessful @)M)*1002sum(data1.L@)
AttemptsM)),0) as sr_ho
;%)6 data1 group by data1.cell,data1.neighborN
3..
E&B&F*
data!.date, data!.cell,
%ight(data!.cell,0) AE nei,
6id(data!.cell,,1,.) AE cell_name,
data!.;ail, data!.Eucc,
(data!.fail1data!.succ) AE att,
round((data!.succ)*1002 (data!.fail1data!.succ),0) AE succ_rate,
data0.cell as neighbor
;%)6 data!,data0 where %ight(data!.cell,0)data0.he5_ciN