Extenal Level > Varying View of application to different users Normalization

Key --> Uniquely Identified row in table Intelligentr Key --> Keys based on data values Non Intelligent Key --> to identify Row

Relationships One to One One to Many Many to Many

Normalization Forms

First NF --> Elimination of Repeated Groups Second NF --> Eliminate Redundant Data

Third NF --> Eliminate Column Not dependent on Keys Fourth NF --> Isolate independent Multiple relationships Fifth NF --> Isolate Semantically related multiple relationships

De-normalization --> to improve performance using frequently performed calculations. SQL Literals

CHARACTER(n) --> fixed Length of String n> 0 --> Integer --> char

Char Varying (n) --> Vary Length string max length n Similarly Bit, Bit Varying

Numeric(P,Q) --> P--> Digits Q--> Digits After Decimal Points Integer --> Signed Integer Decimal / Binary

Float(p) --> Floating Point Number SQL Operators Unary --> + , - Binary --> * , / Comparison = --> equality != , < >, --> Not EQUAL Ø --> G.T Ø < --> L.T Ø >= --> G.E Ø <= --> L.E IN --> in any member of

NOT IN --> Not in any Member of Is NULL , --> allow the field as null is Not NULL --> allow the field not as nulls

Exists --> Check the execution of SubQuery is not EMPTY LIKE -->‏like‏starts‏with‏1st‏Character‏―m‖

Like E% --> 1st letter is E Like %E% --> have letter E

IN --> Compare More than 1 Check Condition Between --> Two value ranges

Also Not Between

Logical Operators --> Single result from combining 2 separate conditions And --> both condition should be true

OR --> Either of the condition

NOT --> return true if condition is false SET OPERATOR

UNION --> All distinctr rows from both queries (main query & Sub Query) UNIONALL --> Return all rows from both queries

Intersect --> Return queries match both queries Minus --> All distinct rows of first query not in 2nd . Inbuilt Functions

ABS(-146) = 146 ABS(146) = 146

CEIL (2) = 2; CEIL(1.3) = 2; CEIL(-1.3) = 2; FLOOR(2) = 2; FLOOR(1.3) = 1; MOD(100,3) = 1; MOD(22,33) = 22; POWER(3,2) = 9; POWER(4, .5) = 2; SQRT(4) = 2; SQRT(-4) = NULL EXP(5)‏=‏148.4134…. LN(3) = 1.098612 LOG(10,100) = 2 ROUND(33,33,3) = 33.33; ROUND(66.666,2) = 66.68 TRUNC(66.666,2) = 66.66; TRUNC (22.1,0) = 22;

SIGN(146) = 1; SIGN(-146)= -1; SIG(0) = 0; SIN(VALUE); COS(VALUE); TAN(VALUE); AVG(10,10,10) = 10;

LI ST

Greatest (VAL1 ,VAL2,VAL3); --> pick greatest Value Least(VAL1,VAL2,VAL3); --> Pick Least Value AGGREGATE Functions

COUNT() --> Select Count(* ) from Table-Name SUM () --> Select SUM(C1) from Table-Name AVG() --> Select AVG(C1) from Table-Name MAX() --> Select MAX(C1) from Table-Name MIN() --> Select MIN(C1) from Table-Name Data Definition Language (DDL) CREATE TABLE , VIEW , INDEX ALTER TABLE,

DROP Table, View, Index

Data Manipulation Language (DML) INSERT

UPDATE DELETE

Data Control Language(DCL) GRANT

REVOKE DELETE

DATA Query Language (DQL) SELECT

Data Administration Statements (DAS) START AUDIT

STOP AUDIT

Transaction Control Statements (TCL) COMMIT

ROLLBACK SAVEPOINT SETTRANSACTION CREATE TABLE

Create Table Table-Name‏(‏Col1‏DataType‏NOT‏NULL‏,‏…..‏PRIMARY‏KEY(COL1)); MODIFY

Alter table Table-Name‏add‏column‏……….. DELETE TABLE

Drop Table Table-Name

Index --> Index can speed execution of SQL With Condition

Create Index Index-Name on Table-Name (Column [order]) --> Asc(default) --> Desc

Composite Index --> involves more than 1 column Unique Index --> Prevent Duplication of Data

Clustered Index --> Sorted both Logically & Physically. --> Created on Primary Key

Create index I1 on Employee (c1,c2); c1,c2 --> Columns in table Employee Drop Index Index-Name;

Views -->‏named‏Table‏,won‘t‏Physically‏Separate‏Data‏define‏interim‏of‏other‏table ( base table )

Create View View-Name as Sub-Query Can be used for multiple queries Both Updatable & nonUpdatable NULL --> Values desired

Insert into Table-Name[Column List ];

Update Table set Column =Update table-Name Where Condition; Delete from Table-Name Condition;

Select Trunc(* ) from Table-Name --> Deletes the entire data leaves Table Structure alone. Cast --> Scalar value to scalar Data-Type

Cast (scalar-exp) as { Data-Type } CASE

When Cond-Exp then Scalar exp Else

scalar Exp end;

INDICATOR --> null indicator variable parameter sent to -1

SELECT --> Select ( all / Distinct) exp (Query) from Table-Name; Where Condition Group By --> Usually refers for Aggregate functions / Columns

--> Having --> include certain Groups ,Equivalent to [ where ] --> Order By --> Sort by Order --> Asc [default]

--> Desc [descending Order ]

Select Distinct (* ) from Table-Name --> Eliminate Duplicates Select * from Table-Name where year in (1993,1996,1999); --> check if it matches year values given

Select * from Table-Name where year not in (1993,1996,1999);  check if it not matches year values given.

Select * from Table-Name where C1 between [2 specific values ] Or Using Sub-Queries Correlated sub Query

--> execute Outer Query first , Sub-Query Next Usual --> Sub Query Executed first then outer query .

Parallel Sub Query --> More than 1 sub query in a query.

Join

EQUI-JOIN --> comparison operator is equality

Non EQUI-JOIN--> comparison operator is not equal sign

Natural Join --> produce result that contain 2 identical rows ,1 row being Eliminated Self Join --> A Table joined with Itself

Outer Join --> Extended Inner Join

Table are joined contain matching values both matching & non matching rows returned. LEFT Outer Join --> Use Keyword LEFT [OUTER] or (*)

RIGHT Outer Join --> use keyword Right [OUTER] FULL Outer Join --> use (+)

Grant Permission Grant (All/Privilege)

On { Table Name Column-Name VIEW NAME } To { PUBLIC /USER LIST }

With Grant option

Public --> to all users of the system ALL --> all Privileges

With Grant --> All users can access Privileges REVOKE --> Take away privileges given by Grant Revoke { Privileges List / ALL }

On Table /Coloumn /View } From { Public /User List }

I ntegrity Constraints --> restrict data values that can be inserted into database that could be inserted into database that could be deleted & Modified.

Referential Entity

Create Assertation – check (not Exists (cond)); Domain

Create Domain D-Name as Defn

Alter Domain D-Name‏Add‏………..‏/‏Drop‏………

Candidate Key --> unique identifier ( a column or combination of columns) of table

Foreign Key --> a column or combination of column in one table ,values required to match some candidate key in another table

Lock --> On Reading Data --> on Updating Data

Exclusive Lock --> during data modification on insert / delete / update Update --> no access to other users

Shared Lock --> read only access to other users.

Trigger. --> An action database should take PL / SQL Code

--> Need to be executed only once and will get invoked automatically till dropped Row Level Trigger --> once Fro each row of transaction

Statement Level Trigger --> Execute once for each transaction Before Trigger , After Trigger

Create or Replace Trigger Trigger-Name Before /After

Delete / INSERT / UPDATE On [USER] Table-Name For Each Row

[PL/SQL Block] Backup: COMMIT; ROLLBACK;

SavePoint --> A point on transaction That you can rollback without rollback entire transaction. SavePoint sp1 SQL1; …….. SavePoint sp2 SQL2; Rollback sp2; --> rollback SP2 ORACLE SQL