REF DEREF VALUE
REF
Ø
The
ref function allows referencing of existing row objects.
Ø
Each
of the row objects has an object id value assigned to it.
Ø
The
object id assigned can be seen by using ref function.
DEREF
Ø
The
deref function per\ opposite action.
Ø
It
takes a reference value of object id and returns the value of the row objects.
VALUE
Ø
Even
though the primary table is object table, still it displays the rows in general
format.
Ø
To
display the entire structure of the object, this will be used.
Ex:
1) create
vendot_adt type
SQL> Create
type vendor_adt as object (vendor_code number(2), vendor_name
varchar(2), vendor_address varchar(10));/
2) create
object tables vendors and vendors1
SQL> Create
table vendors of vendor_adt;
SQL> Create table vendors1 of vendor_adt;
3) insert the data into object tables
SQL> insert
into vendors values(1, ‘a’, ‘hyd’);
SQL> insert into vendors values(2, ‘b’,
‘bang’);
SQL> insert into vendors1 values(3, ‘c’,
‘delhi’);
SQL> insert into vendors1 values(4, ‘d’,
‘chennai’);
4) create another table orders which holds
the vendor_adt type also.
SQL> Create
table orders (order_no number(2), vendor_info ref vendor_adt);
Or
SQL> Create table orders (order_no number(2), vendor_info ref
vendor_adt with
rowid);
5) insert the data into orders table
The vendor_info column in the following syntaxes will store
object id of any table
which is referenced by vendor_adt
object ( both vendors and vendors1).
SQL> insert
into orders values(11,(select ref(v) from vendors v where vendor_code
= 1));
SQL> insert
into orders values(12,(select ref(v) from vendors v where vendor_code
= 2));
SQL> insert
into orders values(13,(select ref(v1) from vendors1 v1 where
vendor_code = 1));
SQL> insert
into orders values(14,(select ref(v1) from vendors1 v1 where
vendor_code = 1));
6) To see the object ids of vendor table
SQL> Select
ref(V) from vendors v;
7) If you see the vendor_info of orders it will
show only the object ids not the values,
to see the values
SQL> Select deref(o.vendor_info) from orders o;
8) Even though the vendors table is object table
it will not show the adt along with
data, to see the data along with the
adt
SQL>Select
* from vendors;
This will give the data without adt.
SQL>Select
value(v) from vendors v;
This will give the columns data along
wih the type.
REF
CONSTRAINTS
Ø
Ref
can also acts as constraint.
Ø
Even
though vendors1 also holding vendor_adt, the orders table will store the object
ids of vendors only because it is constrained to that table only.
Ø
The
vendor_info column in the following syntaxes will store object ids of vendors
only.
SQL> Create table orders (order_no number(2),
vendor_info ref vendor_adt scope is
vendors);
Or
SQL> Create table orders (order_no number(2),
vendor_info ref vendor_adt constraint fk
references vendors);
OBJECT VIEWS WITH REFERENCES
To implement the objects and the ref constraints to the existing
tables, what we can do? Simply drop the both tables and recreate with objects
and ref constrains.
But you can achieve this with out dropping the tables and
without losing the data by creating object views with references.
Ex:
a) Create the following
tables
SQL> Create
table student1(no number(2) primary key,name varchar(2),marks
number(3));
SQL> Create table student2(no number(2) primary key,hno number(3),city
varchar(10),id number(2),foreign Key(id) references student1(no));
b) Insert the records
into both tables
SQL> insert into student1(1,’a’,100);
SQL> insert into student1(2,’b’,200);
SQL> insert into student2(11,111,’hyd’,1);
SQL> insert into student2(12,222,’bang’,2);
SQL> insert into student2(13,333,’bombay’,1);
c) Create the type
SQL> create
or replace type stud as object(no number(2),name varchar(2),marks
number(3));/
d) Generating OIDs
SQL> Create or replace view student1_ov of stud with object
identifier(or id) (no) as
Select *
from Student1;
e) Generating references
SQL> Create or replace view student2_ov as select no,hno,city,
make_ref(student1_ov,id) id from Student2;
d) Query the following
SQL> select *from student1_ov;
SQL> select ref(s) from student1_ov s;
SQL> select values(s) from student1_ov;
SQ> select *from student2_ov;
SQL> select deref(s.id) from student2_ov s;
PARTITIONS
A single logical table can be split into a number of physically
separate pieces based on ranges of key values. Each of the parts of the table
is called a partition.
A non-partitioned table can not be partitioned later.
TYPES
Ø Range partitions
Ø List partitions
Ø Hash partitions
Ø Sub partitions
ADVANTAGES
Ø Reducing downtime for scheduled
maintenance, which allows maintenance operations to be carried out on selected
partitions while other partitions are available to users.
Ø Reducing downtime due to data failure,
failure of a particular partition will no way affect other partitions.
Ø Partition independence allows for
concurrent use of the various partitions for various purposes.
ADVANTAGES OF PARTITIONS BY STORING THEM IN DIFFERENT TABLESPACES
Ø Reduces the possibility of data
corruption in multiple partitions.
Ø Back up and recovery of each partition
can be done independently.
DISADVANTAGES
Ø Partitioned tables cannot contain any
columns with long or long raw datatypes, LOB types or object types.
RANGE PARTITIONS
a) Creating range partitioned table
SQL> Create table student(no number(2),name varchar(2)) partition by
range(no)
(partition p1
values less than(10), partition p2 values less than(20), partition p3
values less
than(30),partition p4 values less than(maxvalue));
** if you are using
maxvalue for the last partition, you can not add a partition.
b) Inserting records into range partitioned table
SQL> Insert into student values(1,’a’); --
this will go to p1
SQL> Insert into student values(11,’b’); -- this will go to p2
SQL> Insert into student values(21,’c’); -- this will go to p3
SQL> Insert into student values(31,’d’); -- this will go to p4
c) Retrieving records from range partitioned table
SQL> Select *from student;
SQL> Select *from student partition(p1);
d) Possible operations with range partitions
v Add
v Drop
v Truncate
v Rename
v Split
v Move
v Exchange
e) Adding a partition
SQL> Alter table student add partition p5 values less than(40);
f) Dropping a partition
SQL> Alter table student drop partition p4;
g) Renaming a partition
SQL> Alter table student rename partition p3 to p6;
h) Truncate a partition
SQL> Alter table student truncate partition p6;
i) Splitting a partition
SQL> Alter table student split partition p2 at(15) into (partition
p21,partition p22);
j) Exchanging a partition
SQL> Alter table student exchange partition p1 with table student2;
k) Moving a partition
SQL> Alter table student move partition p21 tablespace saketh_ts;
LIST PARTITIONS
a) Creating list partitioned table
SQL> Create table student(no number(2),name varchar(2)) partition by
list(no)
(partition p1
values(1,2,3,4,5), partition p2 values(6,7,8,9,10),partition p3
values(11,12,13,14,15), partition p4 values(16,17,18,19,20));
b) Inserting records into list partitioned table
SQL> Insert into student values(1,’a’); --
this will go to p1
SQL> Insert into student values(6,’b’); --
this will go to p2
SQL> Insert into student values(11,’c’); -- this will go to p3
SQL> Insert into student values(16,’d’); -- this will go to p4
c) Retrieving records from list partitioned table
SQL> Select *from student;
SQL> Select *from student partition(p1);
d) Possible operations with list partitions
v Add
v Drop
v Truncate
v Rename
v Move
v Exchange
e) Adding a partition
SQL> Alter table student add partition p5 values(21,22,23,24,25);
f) Dropping a partition
SQL> Alter table student drop partition p4;
g) Renaming a partition
SQL> Alter table student rename partition p3 to p6;
h) Truncate a partition
SQL> Alter table student truncate partition p6;
i) Exchanging a partition
SQL> Alter table student exchange partition p1 with table student2;
j) Moving a partition
SQL> Alter table student move partition p2 tablespace saketh_ts;
HASH PARTITIONS
a) Creating hash partitioned table
SQL> Create table student(no number(2),name varchar(2)) partition by
hash(no)
partitions 5;
Here oracle
automatically gives partition names like
SYS_P1
SYS_P2
SYS_P3
SYS_P4
SYS_P5
b) Inserting records into hash partitioned table
it will insert the
records based on hash function calculated by taking the partition key
SQL> Insert into student values(1,’a’);
SQL> Insert into student values(6,’b’);
SQL> Insert into student values(11,’c’);
SQL> Insert into student values(16,’d’);
c) Retrieving records from hash partitioned table
SQL> Select *from student;
SQL> Select *from student partition(sys_p1);
d) Possible operations with hash partitions
v Add
v Truncate
v Rename
v Move
v Exchange
e) Adding a partition
SQL> Alter table student add partition p6 ;
f) Renaming a partition
SQL> Alter table student rename partition p6 to p7;
g) Truncate a partition
SQL> Alter table student truncate partition p7;
h) Exchanging a partition
SQL> Alter
table student exchange partition sys_p1 with table student2;
i) Moving a partition
SQL> Alter table student move partition sys_p2 tablespace saketh_ts;
SUB-PARTITIONS
WITH RANGE AND HASH
Subpartitions clause is used by hash only. We can not create
subpartitions with list and hash partitions.
a) Creating subpartitioned table
SQL> Create table student(no number(2),name varchar(2),marks number(3))
Partition by
range(no) subpartition by hash(name) subpartitions 3
(Partition p1 values less
than(10),partition p2 values less than(20));
This will create two partitions p1 and p2 with three
subpartitions for each partition
P1
– SYS_SUBP1
SYS_SUBP2
SYS_SUBP3
P2
– SYS_SUBP4
SYS_SUBP5
SYS_SUBP6
** if you are using
maxvalue for the last partition, you can not add a partition.
b) Inserting records into subpartitioned table
SQL> Insert into student values(1,’a’); --
this will go to p1
SQL> Insert into student values(11,’b’); -- this will go to p2
c) Retrieving records from subpartitioned table
SQL> Select *from student;
SQL> Select *from student partition(p1);
SQL> Select *from student subpartition(sys_subp1);
d) Possible operations with subpartitions
v Add
v Drop
v Truncate
v Rename
v Split
e) Adding a partition
SQL> Alter table student add partition p3 values less than(30);
f) Dropping a partition
SQL> Alter table student drop partition p3;
g) Renaming a partition
SQL> Alter table student rename partition p2 to p3;
h) Truncate a partition
SQL> Alter table student truncate partition p1;
i) Splitting a partition
SQL> Alter table student split partition p3 at(15) into (partition
p31,partition p32);
DATA
MODEL
Ø
ALL_IND_PARTITIONS
Ø
ALL_IND_SUBPARTITIONS
Ø
ALL_TAB_PARTITIONS
Ø
ALL_TAB_SUBPARTITIONS
Ø
DBA_IND_PARTITIONS
Ø
DBA_IND_SUBPARTITIONS
Ø
DBA_TAB_PARTITIONS
Ø
DBA_TAB_SUBPARTITIONS
Ø
USER_IND_PARTITIONS
Ø
USER_IND_SUBPARTITIONS
Ø
USER_TAB_PARTITIONS
Ø
USER_TAB_SUBPARTITIONS
GROUP BY
AND HAVING
GROUP BY
Using group by, we can create groups of related information.
Columns used in select must be used with group by, otherwise it
was not a group by expression.
Ex:
SQL> select
deptno, sum(sal) from emp group by deptno;
DEPTNO
SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
SQL> select
deptno,job,sum(sal) from emp group by deptno,job;
DEPTNO
JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
HAVING
This will work as where clause which can be used only with group by because of absence
of where clause in group by.
Ex:
SQL> select deptno,job,sum(sal) tsal from emp group by deptno,job
having sum(sal) >
3000;
DEPTNO
JOB TSAL
---------- --------- ----------
10 PRESIDENT 5000
20 ANALYST 6000
30 SALESMAN 5600
SQL> select
deptno,job,sum(sal) tsal from emp group by deptno,job having sum(sal) >
3000 order by job;
DEPTNO
JOB TSAL
----------
--------- ----------
20 ANALYST 6000
10
PRESIDENT 5000
30 SALESMAN 5600
ORDER OF
EXECUTION
Ø Group the rows together based on group by
clause.
Ø Calculate the group functions for each
group.
Ø Choose and eliminate the groups based on
the having clause.
Ø Order the groups based on the specified
column.
ROLLUP GROUPING CUBE
These are the enhancements to the group by feature.
USING ROLLUP
This will give the salaries in each department in each job
category along wih the total salary for individual departments and the total
salary of all the departments.
SQL> Select
deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB
SUM(SAL)
---------- ---------
----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
29025
USING GROUPING
In the above query it
will give the total salary of the individual departments but with a
blank in the job column
and gives the total salary of all the departments with blanks in
deptno and job columns.
To replace these blanks with your desired
string grouping will be used
SQL> select decode(grouping(deptno),1,'All
Depts',deptno),decode(grouping(job),1,'All
jobs',job),sum(sal) from emp group by rollup(deptno,job);
DECODE(GROUPING(DEPTNO),1,'ALLDEPTS',DEP
DECODE(GR SUM(SAL)
----------------------------------- ---------------------------------- --------------
10 CLERK 1300
10 MANAGER
2450
10 PRESIDENT 5000
10 All jobs 8750
20
ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 All jobs 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 All jobs 9400
All Depts All jobs 29025
Grouping will return 1 if the column
which is specified in the grouping function has been
used in rollup.
Grouping will be used
in association with decode.
USING CUBE
This will give the salaries in each department in each job
category, the total salary for individual departments, the total salary of all
the departments and the salaries in each job category.
SQL> select
decode(grouping(deptno),1,’All Depts’,deptno),decode(grouping(job),1,’All
Jobs’,job),sum(sal)
from emp group by cube(deptno,job);
DECODE(GROUPING(DEPTNO),1,'ALLDEPTS',DEP
DECODE(GR SUM(SAL)
----------------------------------- ------------------------------------ ------------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 All Jobs 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 All Jobs 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 All Jobs 9400
All Depts ANALYST 6000
All Depts CLERK 4150
All Depts MANAGER 8275
All Depts PRESIDENT 5000
All Depts SALESMAN 5600
All Depts All Jobs 29025
SET OPERATORS
TYPES
Ø
Union
Ø
Union
all
Ø
Intersect
Ø
Minus
UNION
This
will combine the records of multiple tables having the same structure.
Ex:
SQL> select
* from student1 union select * from student2;
UNION ALL
This
will combine the records of multiple tables having the same structure but
including duplicates.
Ex:
SQL> select
* from student1 union all select * from student2;
INTERSECT
This
will give the common records of multiple tables having the same structure.
Ex:
SQL> select
* from student1 intersect select * from student2;
MINUS
This
will give the records of a table whose records are not in other tables having
the same structure.
Ex:
SQL> select
* from student1 minus select * from student2;
VIEWS
A view is a database object that is a logical representation of
a table. It is delivered from a table but has no storage of its own and often
may be used in the same manner as a table.
A view takes the output of the query and treats it as a table,
therefore a view can be thought of as a stored query or a virtual table.
TYPES
Ø
Simple
view
Ø
Complex
view
Simple view can be created from one table where as complex view
can be created from multiple tables.
WHY
VIEWS?
Ø Provides additional level of security by
restricting access to a predetermined set of rows and/or columns of a table.
Ø Hide the data complexity.
Ø Simplify commands for the user.
VIEWS WITHOUT DML
Ø Read only view
Ø View with group by
Ø View with aggregate functions
Ø View with rownum
Ø Partition view
Ø View with distinct
Ex:
SQL> Create view dept_v as select *from dept with read only;
SQL> Create view dept_v as select deptno,
sum(sal) t_sal from emp group by deptno;
SQL> Create view stud as select rownum no, name,
marks from student;
SQL> Create view student as select *from
student1 union select *from student2;
SQL> Create view stud as select distinct no,name
from student;
VIEWS WITH DML
Ø View with not null column --
insert with out not null column not possible
-- update not null column to null is not
possible
-- delete possible
Ø View with out not null column which was
in base table -- insert not possible
--
update, delete possible
Ø View with expression -- insert , update
not possible
-- delete possible
Ø View with
functions (except aggregate) -- insert, update not possible
-- delete possible
Ø View was created but the underlying table
was dropped then we will get the message like “ view has errors ”.
Ø View was created but the base table has
been altered but still the view was with the initial definition, we have to
replace the view to affect the changes.
Ø Complex view (view with more than one
table) -- insert not possible
-- update, delete possible
(not always)
CREATING VIEW WITHOUT HAVING THE BASE TABLE
SQL> Create
force view stud as select *From student;
-- Once the base
table was created then the view is validated.
VIEW WITH CHECK OPTION CONSTRAINT
SQL> Create
view stud as select *from student where marks = 500 with check option
constraint Ck;
- Insert possible
with marks value as 500
- Update possible
excluding marks column
- Delete possible
DROPPING VIEWS
SQL> drop view dept_v;
DATA MODEL
ALL_VIEW
DBA_VIEW
USER_VIEWS
SYNONYM AND SEQUENCE
SYNONYM
A synonym is a database object, which is used as an alias for a
table, view or sequence.
TYPES
Ø
Private
Ø
Public
Private synonym is available to the particular user who creates.
Public synonym is created by DBA which is available to all the
users.
ADVANTAGES
Ø Hide the name and owner of the object.
Ø Provides location transparency for remote
objects of a distributed database.
CREATE AND DROP
SQL>
create synonym s1 for emp;
SQL> create
public synonym s2 for emp;
SQL> drop
synonym s1;
SEQUENCE
A sequence is a database object, which can generate unique,
sequential integer values.
It can be used to automatically generate primary key or unique
key values.
A sequence can be either in an ascending or descending order.
Syntax:
Create sequence <seq_name> [increment bty n] [start
with n] [maxvalue n]
[minvalue n] [cycle/nocycle] [cache/nocache];
By defalult the sequence starts with 1, increments by 1 with
minvalue of 1 and with nocycle, nocache.
Cache option pre-alloocates a set of sequence numbers and
retains them in memory for faster access.
Ex:
SQL> create sequence s;
SQL> create sequence s increment by 10 start with 100 minvalue 5 maxvalue
200 cycle
cache 20;
USING SEQUENCE
SQL> create
table student(no number(2),name varchar(10));
SQL> insert
into student values(s.nextval, ‘saketh’);
Ø Initially currval is not defined and
nextval is starting value.
Ø After that nextval and currval are always
equal.
CREATING ALPHA-NUMERIC SEQUENCE
SQL> create
sequence s start with 111234;
SQL> Insert into student values (s.nextval ||
translate
(s.nextval,’1234567890’,’abcdefghij’));
ALTERING SEQUENCE
We can alter the sequence to perform the
following.
Ø Set or eliminate minvalue or maxvalue.
Ø Change the increment value.
Ø Change the number of cached sequence
numbers.
Ex:
SQL> alter sequence s minvalue 5;
SQL> alter sequence s increment by 2;
SQL> alter sequence s cache 10;
DROPPING SEQUENCE
SQL> drop
sequence s;
JOINS
Ø The purpose of a join is to combine the
data across tables.
Ø A join is actually performed by the where
clause which combines the specified rows of tables.
Ø If a join involves in more than two tables
then oracle joins first two tables based on the joins condition and then
compares the result with the next table and so on.
TYPES
Equi
join
Non-equi
join
Self
join
Natural
join
Cross
join
Outer
join
Ø Left outer
Ø Right outer
Ø Full outer
Inner
join
Using
clause
On
clause
Assume that we have the following tables.
SQL> select
* from dept;
DEPTNO DNAME LOC
------ ----------
----------
10 mkt hyd
20 fin bang
30 hr bombay
SQL> select
* from emp;
EMPNO ENAME
JOB MGR DEPTNO
----------
---------- ---------- ---------- ----------
111 saketh analyst 444 10
222 sudha clerk 333 20
333 jagan manager 111 10
444 madhu engineer 222 40
EQUI JOIN
A join which contains an ‘=’ operator in the joins condition.
Ex:
SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno;
EMPNO ENAME JOB
DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh analyst
mkt hyd
333 jagan manager
mkt hyd
222 sudha clerk fin bang
USING CLAUSE
SQL> select
empno,ename,job ,dname,loc from emp e join dept d using(deptno);
EMPNO ENAME JOB
DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh analyst
mkt hyd
333 jagan manager
mkt hyd
222 sudha clerk fin bang
ON CLAUSE
SQL> select empno,ename,job,dname,loc from emp e
join dept d on(e.deptno=d.deptno);
EMPNO ENAME JOB
DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh analyst
mkt hyd
333 jagan manager
mkt hyd
222 sudha
clerk fin bang
NON-EQUI JOIN
A join which contains an operator other than ‘=’ in the joins
condition.
Ex:
SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno
>
d.deptno;
EMPNO ENAME
JOB DNAME LOC
---------- ---------- ---------- ---------- ----------
222
sudha clerk mkt hyd
444
madhu engineer mkt
hyd
444
madhu engineer fin
bang
444
madhu engineer hr
bombay
SELF JOIN
Joining the table itself is called self join.
Ex:
SQL> select
e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where
e1.empno=e2.mgr;
EMPNO ENAME JOB
DEPTNO
---------- ---------- ---------- ----------
111 jagan analyst 10
222 madhu clerk 40
333 sudha manager 20
444 saketh engineer 10
NATURAL JOIN
Natural join compares all the common columns.
Ex:
SQL> select empno,ename,job,dname,loc from emp natural join dept;
EMPNO ENAME JOB
DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh analyst
mkt hyd
333 jagan manager
mkt hyd
222 sudha clerk fin bang
CROSS JOIN
This will gives the cross product.
Ex:
SQL> select empno,ename,job,dname,loc from emp cross join dept;
EMPNO
ENAME JOB DNAME
LOC
----------
---------- ---------- ---------- ----------
111
saketh analyst mkt
hyd
222
sudha clerk mkt hyd
333
jagan manager mkt
hyd
444
madhu engineer mkt
hyd
111
saketh analyst fin bang
222
sudha clerk fin bang
333
jagan manager fin
bang
444
madhu engineer fin
bang
111
saketh analyst hr bombay
222
sudha clerk hr bombay
333
jagan manager hr
bombay
444
madhu engineer hr
bombay
OUTER JOIN
Outer join gives the non-matching
records along with matching records.
LEFT OUTER JOIN
This will display the all matching
records and the records which are in left hand side table those that are not in
right hand side table.
Ex:
SQL> select
empno,ename,job,dname,loc from emp e left outer join dept d
on(e.deptno=d.deptno);
Or
SQL> select empno,ename,job,dname,loc from emp e,dept d where
e.deptno=d.deptno(+);
EMPNO
ENAME JOB DNAME LOC
---------- ---------- ----------
---------- ----------
111 saketh analyst mkt hyd
333 jagan manager
mkt hyd
222 sudha clerk fin bang
444 madhu engineer
RIGHT OUTER JOIN
This will display the all matching
records and the records which are in right hand side table those that are not
in left hand side table.
Ex:
SQL> select
empno,ename,job,dname,loc from emp e right outer join dept d
on(e.deptno=d.deptno);
Or
SQL> select empno,ename,job,dname,loc from emp e,dept d where
e.deptno(+) =
d.deptno;
EMPNO
ENAME JOB DNAME
LOC
---------- ---------- ----------
---------- ----------
111 saketh analyst mkt
hyd
333 jagan manager
mkt hyd
222 sudha clerk fin bang
hr bombay
FULL OUTER JOIN
This will display the all matching
records and the non-matching records from both tables.
Ex:
SQL> select
empno,ename,job,dname,loc from emp e full outer join dept d
on(e.deptno=d.deptno);
EMPNO
ENAME JOB DNAME LOC
---------- ---------- ----------
---------- ----------
333 jagan
manager mkt hyd
111 saketh analyst
mkt hyd
222 sudha clerk fin bang
444 madhu engineer
hr bombay
INNER JOIN
This will display all the records
that have matched.
Ex:
SQL> select empno,ename,job,dname,loc from emp inner join dept
using(deptno);
EMPNO ENAME JOB
DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh analyst mkt
hyd
333 jagan manager
mkt hyd
222 sudha clerk fin bang
No comments:
Post a Comment