------------- 41 to 45 ------------
--------------- Session 41 --------------
------------------------------------------
2) Compare 2 different column values (same datatype) in same table.
====================================================================
In all relational databases if we want to compare 2 different colmn values (same datatype) in a same table then we must use self join.
In all relational databases we can also store tree structure data into relational table.In this case the table must contain minimun 3 columns and also in these 3 columns 2 columns must belongs to same datatype and also these 2 columns having related data.
Whenever a table contains these type of heiracial data then we are comparing 2 column by using Self Join for retreiving hierarchical data
Query to display employee name and their manager names from emp table by using self join.
select e1.empno, e1.ename , e2.ename as mgr_name from employee e1 , employee e2 where e1.mgr=e2.empno;
Query to dsiplay employee having sal more than manager
select e1.name 'employee' , e1.sal , e2.sal, e2.name 'manager'
from employee e1, employee e2
where e1.mgr=e2.empno
and e1.sal>e2.sal;
--------------- Session 42 --------------
------------------------------------------
Query to display the emp who are joinin before their manager by using self join
select e1.name 'employee' , e1.hiredate , e2.hiredate, e2.name 'manager'
from employee e1, employee e2
where e1.mgr=e2.empno
and e1.hiredate<e2.hiredate;
OUTER Join
===========
This joins return all rows from one table and matching rows from another table.
Generally Equi Join return Matching rows only. If we want to return Non Matching rows also then we are using join operator (+) within joining condiiton of the equi join. This join is also called as oracle 8i outer join .
Note:
==== This join opertor can be used in only one side at a time within joining condition.
select ename , sal , d.deptno, dname, loc
from emp e , dept d
where e.deptno(+) =d.deptno; ------Right join
9i Join or ANSI Joins:
=====================
Oracle 9i version onwards oracle also supports ANSI standar join same like all other relationsal databases. These joins are also called as 9i joins or ANSI joins.
These are
1) Inner Join
2) Left Outer Join
3) Right Outer Join
4) Full Outer Join
5) Natural Join
Inner Join :
========== it is 99% same like EQUI Join
These join also returns matching rows only. Here also joingin conditional columns must belongs to same datatype. When tables having common colum then we can also use inner join.
Inner join performance is very high compare to oracle 8i equi join.
--------------- Session 43 --------------
------------------------------------------
select * from test1 join test2
on test1.a=test2.a and test1.b=test2.b;
Using Clause:
============= In oracle 9i joins we can also use Using clause in place of on clause.
Using clause always return common columns one time only.
Syntax:
select * from tablenam1 join tablename2
using (commoncolumname1 , commoncolumname2);
select * from test1 join test2
using(a,b);
--------------- Session 44 --------------
------------------------------------------
Left Outer Join:
============ this join returns all rows from left side tbale and matching rows from right side table and also returns null value in place of non matching rows in another table.
select * from test1 left outer join left2
on test1.a=test2.a and test1.b=test2.b;
Right Outer Join :
================
This join returns all rows from right side table and matching rows from left side table and also returns null vlaues in place of non matching rows in another table.
select * from test1 right outer join left2
on test1.a=test2.a and test1.b=test2.b;
Full outer join :
================
Full outer join return all rows from both the table because it is a combination of LEft and Right Outer join. Also it return Null values in place of non matching rows in another table.
select * from test1 FULL outer join left2
on test1.a=test2.a and test1.b=test2.b;
Natural Join :
============
This join also return matching rows only. In this join we are not allowed to use joining conditino explicity becasue whenever tables having common columns then only oracle server internally automatically established joinign condition based on common column.
syntax:
select * form tbale1 natural join tbale2;
Note: This join also returns common columns one time only because natural join internally uses using clause.
ex :
select * from test1 full natural join test2;
Cross Join:
===========
in ANSI standard join cross join is not a default join. If we want cross join explicitly then we are using cross join keyword.
select ename , sal, from emp cross join dept;
Note :
========= In all relationaal database we can also retrieve data from multiple tables by using joins when tables doesnot have common column also.In this case database server checks those columns datatypes only.
Join Subtraction :
===============
In 9i joins elimination matching rows in left outer, right outer , full outer joins is called as Join Substraction.
inner Join :no sunstration in inner join
syntax :
select* from tablea join tableb
on tablea.key=tableb.key;
Left Outer Join / Left Join:
===========================
syntax :
select * form tablea left join tableb
on tablea.key=tableb.key;
Left Join( Substraction ): Eliminating matching records. Data from left table only and no matching rows.
select * from tablea join tableb
on tablea.key=tableb.KEY
where tableb.key is null;
Right Outer Join :
=====================
select * from tablea right join tableb
on tablea.key=tableb.key;
Right Outer Join (Substraction):
===================================
select * from tablea right join tableb
on tablea.key=tableb.key
where tablea.key is null;
Full outer Join:
================
select * from tablea full outer join tableb
on tablea.key=tableb.key;
Full Outer Join (Substraction):
select * from tablea full join tableb
on tablea.key=tableb.KEY
where tablea.key is null or tableb.key is null;
--------------- Session 45 --------------
------------------------------------------
Joining More than 2 tables :
==========================
Joining 3 tables : 2 join condition NEEDED
8i joins
----------
select * from table1,table2, table3
where table1.key =table2.key
and table2.key =table3.key
and table1.key = table3.key;
9i joins / ANSI joins
-------------------------
select * from table1 join table2
on tabel1.key=table2.key
join table3
on table3.key=table2.key;
=============
CONSTRAINTS:
==============
Data Integrity : To maintain proper data/valid Data.
Contraints are used to prevents or stops invalid data entry into our tables. Generallu contraits are created on Table Columns.
Oracle having following types of contraints:
1) Not NULL
2) UNIQUE
3) Primary KEY , COMPOSITE Key : THere will be only one primary key in table.
4) Foreign KEY
5) CHECK
2 ways to create contrains one at column level 2nd table level.
All above contransts are created in following 2 ways
1) Column LEVEL
2) Table LEVEL
1) Column Level : in this method we are creating constrainsts on individual column i.e.. whenever we are creating column then immediately we are specifying contraints type.
Syntax :
Create table tablename
( col1 datatype constraint type,
col2 datatype constraint type
);
2) Table level : in this method we are creating contraints on group of columns i.e.. in this method first we are defiinig all column and last we must speicify contraint type along with group of column.
Syntax :
create table tablename
(col datatype,
col2 datatype...
,
contraints type (col1,col2..)
);
--------------- Session 46 --------------
------------------------------------------
1) Not Null :
================ In all relational databases Not Null constraint doesnot support Table Level.
This constraints doesnnot accepts Null values but it will accepts duplicate values.
Column Level:
============
create table tablename( col datatupe Not Nul);
Table Level:
============ Does not support at table level.
2) Unique : it can be created at column and Table level.
=========== This comtraints does not accept duplicate value but it will accepts null values.
Column Level:
============
create table tablename (col datatype unique, col2 datatype);
Table Level :
==========
create table tablename (col datatype , col2 datatype,
unique (col1, col2...); ---- it is like composite , unique would be based on both column
3) Primary Key :
================== PK uiiquely identified a record in a table. These can be only one Primary key in a table. Primary key doesnot accepts Duplicates , Null Values.
Column LeveL:
Create table tablename (col datatype Primary key, col datatype);
Table Column :
========== create table tablename (col datatype, col2 cdatatype,
Primary key(col)) ;
Composite Primary Key : It is a combination of columns as a single primary key.
create table tablename (col datatype, col2 cdatatype,
Primary key(col, col2));
4) FOREIGN KEY:
===========
In all relational databases if we want to establishes relationship between tables then we are using referential integrity constraints foreign key.
Generally one table foreign key must belongs to another table Primary Key and also these foreign key , primary key columns must blongs to same datatype.
Always Foreign key values are based on Primary Key values only.
Generally Primary key doesnot accept duplicate , Null values
whereas Foreign Key accespts duplicate , Null Values.
Column level ( Reference) : ---
============================
Syntax :Create table tablename (col1 datatype references master_tablename(Primary_key_col_name)......... );
creat table j1(a number(10) references z4(sno));
Table Level( Foreign key references) :-
===================================
create table tablename(col1 datatype, col2 datatype,
foreign key (Col, col2.) references mastertablename (primarykey column name));
No comments:
Post a Comment