DataBase Notes

Unique key : 1) duplicate values not allowed
2)NULL values allowed
3)automatically index is created (unique index)


primary key
1) only one primary key allowed in one table
2) automatically NOTNULL, Unique, Index
Note:primary can be created on the combination of columns known as composite primary key
Max= 16 columns allowed
Similarly we can create Composite Unique key having max of 16 columns

Check :it is used to validate the values of pre_defined list
Ex: City IN (‘Pune’,’hyd’)
Sal>=(3000)


Reference Key(Foreignkey); Used for parent or child relationship
More than one reference table in allowed for one table
Reference key possible if its own own primary key … known as self key

Note:
We can define the reference key with 2 key keywords
ON DELETE CASCADE
ONE DELTE SET NULL
ON DELETE CASCADE :when master data deleted , automatically child data deleted
ON DELTE SET NULL:when master data deleted child row not deleted and reference value becomes NULl
Data Integrity:
1) it allows only to enter valid date to DB
2) it can implemented through constraints

Index:
Is used for retrieval of rows using row id
Note:
It is a data base object
It is used by oracle to search data tablews
Index is created on columns
More than one index is possible in one table
Composite Index:
When we create index on combination of columns then it is known as composite index

Purpose if View:
Security
Simplify the complex logic



Joins:
Joins are used to retrieve the from more than one table in a single query
Types of joins

1) equi joins
2) Non _ equi joins
3) Self Joins
4) Outer join

Equi Join: it is used to display only matching rows from joining table
“=” operator is used
tables have a common columns with
1) same date type
2) same value

Syntax:select Col1,col2 … from from , ….. where .= .;

Non- Equijoins
It is used to join table when one column value of table falls in the range of two column value of other table
Operator not Used
Generally we use between operator
Non –equi join is also called as Between Join
Ex:selecte.ename,e.sal,g.sal,g.dradefrom emp e, salgrade g
Where e.sal between g.losal AND g.hisal;

Outer Join: is used to display
matching rows
non-matchingrows from one table
full outer join is possible in oracle 9i and not possible in oracle 8i

left, right, full introduced in oracle 9i
operator of outer join is (+)
(+) Operator can be used with only one table and only once in join condition
Example: select e.empno,e.ename,d.deptno from emp e, dept d
Where e.e.deptno(+) =d.deptno
Note:
OR/In Operators are not allowed
Sub query not allowed


Self Join: joining on same table
table must have atleast two columns with common
1.same data type
2.same values
Query;
Dispay manager name for each employee
Select A.empno,A.ename,b.enamefrom emp A , emp B where A.manager =B.empno


Self Outer Join:
Select A.empno,A.ename ,B.ename from emp A , emp B
Where A.mangaer = B.empno(+);

Self Non- equi Join;
Select D1.Dname,D2.Dname from DeptD1, Dept D2 where D1.deptnoNotNull:
Null Values not allowed
Duplicate values Allowed
Cursor: a cursor is a handle ora pointer to the context area through the cursor, pl/saql program can context area and what happens to it as the the statement processed

Trigger:
Trigger is an event when an event is occurs it automatically fires