Powered By Blogger

Sunday 26 February 2012

Data, Attribute,dataType,functions


Data
F  Data is a raw fact which has have meaning and  no meaning.
F  Objects & event that have meaning and importance in the user’s environment.
Structured data    stud name, address, age… etc.    
Unstructured data    stud photo, address_ map… etc.

Meta data
It is the data which describe the properties or characteristics of end user data.
1.   Data name    2. Definition   3. Size   4. Source of data   5. Ownership

Entity
Entity can be anything in Real world, which has some properties, on the basis of those properties, it can be uniquely identify. Ex.. Student, Employee, etc..
Attributes
Properties of entity called “ATTRIBUTES”.
Ex..  std_name, std_contact, std_add, etc.
Data base
DB is organized collection of data as well as information.
DBMS
  DBMS is a S/W used to manage DB.
  Collection of integrated inter-related data.
  DBMS should provide systematic method of creating DB, updating DB, storing DB, etc..
Objective of DBMS
¤  Provide greater independence between Program & data.
¤  Reduce the maintenance cost of S/W.
¤  Manage complex data type & structure.
¤  Easier & fast access of data.
RDBMS-
     i.        RDBMS is a DBMS based on the relation model. 
    ii.        RDBMS stored the data in the form of related tables.
   iii.        RDBMS are powerful because they required few assumptions about how data is related or how it will extracted from the DB.
Data definition language (DDL)
a)   Create     b) Alter    c) Rename   d) Drop   e) Truncate
Data manipulation language (DML)
A.   Insert           B. Update            C. Delete         D. Merge
Data Retrieval/Query language (DRL)
1.   Selection            2. Projection            3.  Join
Data control language (DCL)
     I.        Grant            II.  Revoke
Transaction control language (TCL)
a.    Commit         b. Rollback        c. Save point
Database Object
a.    Table      b. View      c. Synonyms      d. Column      e. Sequence
Data type
1.   Character   2.  Numeric   3.  Date time   4.  Long raw   5.  Large object        6.  Rowid
Char ()
  To store fixed length character strings.
  Maximum size== 2000 byte
   Default size == 1byte
Varchar ()
  To store variable length.
  Maximum size== 4000 byte
  Default size==no

Long ()
¯  To store very lengthy text string
¯  Long is a variable length.
¯  Max size==2 GB
¯  A single table can contain only one LONG column.
¯  “LONG “column can’t appear in WHERE clause or integrity constraint.
¯  Index can’t create on LONG column.
¯  LONG column can appear in SELECT, INSERT and UPDATE.
Raw ()
¤  To store binary data like Images, signature, thumb impression etc.
¤  Max sizes== 2000 byte.
Long Raw ()
T  To store variable length binary data type.
T  Max sizes== 2 GB.
Large objects (LOB)
Q  Lobs can store upto 4GB.
Types of LOBs
¤  CLOB (character large object)
F  CLOB uses default character set.
F  CLOB could be some large text file (txt, csv, xml).
¤  BLOB (Binary large object)
F  We can specify the character set.
F  BLOB could be an image (jpeg, gif).

INSERT    Two way of insertion.
1} Conventional method-
Oracle reuse free space in the table into which the data in being inserted & maintain referential integrity.
2} Direct path method
Oracle append the inserted data after existing data in the table, the free space is not reuse.
INSERTING NULL VALUE
1] Implicit method- Omit the column from list.
2] Explicit method- Specify the null keywords.
n  Insert, update, and delete works on table at a time.
n  Sysdate is a function.
Coping table with values
>insert into emp1 select * from emp;
>create table emp_dup
As
Select * from emp;
NOTE= in this copy, constraint will not copied to another duplicate table.
Projection
Retrieving the data from the specify columns of table is projection.
Data dictionary
Data dictionary is the main source of information for every RDBMS
>select * from dict;
>select * from user_tables where table_name=’EMP’;
>select user from all_users; ( to view all user)
ALTER (Modify, Add, Rename, Drop/Disable/Enable)
1.   To change the size of column & data size.
2.   To make NULL to NOT NULL and NOT NULL to NULL.
3.   To change data type column must be empty.
4.   Before changing Null to Not Null of a column remove Null Values.
Operator precedence
*, /, +, -
Operators of the same priority are evaluated from left to right.
NOTE
Q  If any column value in an Arithmetic expression is NULL, the overall result is also NULL.
Q  If any row has same deptno then it will sort accordingly the record had entered.
Q  During sorting process oracle will consider NULL value as biggest value.
Null will place
&  NULL first in descending order.
& NULL last in ascending order.
Operators
     I.        Arithmetic Operator (+, -, *, /).
   II.        Relational Operator (>, =, <, >=, <=, IN, BETWEEN, LIKE, IS NULL).
 III.        Logical Operator (AND, OR, NOT).
 IV.        Set Operator (UNION, UNION ALL, INSERSECT, MINUS).
   V.        Boolean Operator (EXIST, NOT EXIST).
Difference between CHECK & WHERE
Þ   Check is used to check the range during insertion where clause is used to check the range while referring the data.
Function
  Function is used for calculate the calculation of data
  Modify the individual data.
  Format dates & number for display.
NOTE
ì  If a SQL function is called with a NULL argument, then a NULL is returned.
                                                    Type of function




 Single Row function                                                                 multiple row function
(This function return single result                          (this function manipulate group for every row of a table)                                                             of row and return one result per group of row

Ø  Numeric function=       (Accept numeric input return numeric value)
Ø  Character function=     (Accept character input return number & char both)
Ø  Date function=              (Accept date input return date & number)
Ø  Conversion function=   (convert a value one data type to another data type)
CEIL (n)-  It returns the smallest integer greater than given number.
>select  CEIL(5.5)  from dual; [6]
>select  CEIL(-5.5)   from dual; [-5]
>select  CEIL(5)  from dual; [5]
FLOOR (n)-  It return the greatest integer smaller than given number.
>select  FLOOR(5.5)  from  dual; [5]
>select  FLOOR(-5.5)  from  dual; [-6]
>select  FLOOR(5)  from dual; [5]
ROUND (m,n)-  It will round the given value for specify places.
>select round (2.315) from dual; [2]
>select round (2.767) from dual; [3]
> Select round (5678.12356, 3) from dual; [5678.124]
>select round (5678.1235, -1) from dual; [5680]
>select round (5678.1235, -2) from dual; [5700]
>select round (5678.1235, -3) from dual; [6000]
>select round(5678.1235, -4) from dual; [0000]
TRUNC (m, n)- It will truncate the given number upto specify precision.
>select trunc (2.315) from dual; [2]
>select trunc (2.767) from dual; [2]
> Select trunc (5678.12356, 3) from dual; [5678.123]
>select trunc (5678.1235, -1) from dual; [5670]
>select trunc (5678.1235, -2) from dual; [5600]
>select trunc (5678.1235, -3) from dual; [5000]
>select trunc (5678.1235, -4) from dual; [0000]
LTRIM (String, set) & RTRIM (String, set)
>select LTRIM (‘abcxyz’,  ‘abc’) from dual; [xyz]
>select RTRIM(‘abcxyz’,  ‘xyz’) from dual;  [abc]
LPAD & RPAD (String, number, Padding character)
>select LPAD(‘oracle’ ,8, ‘#’) from dual; [##oracle]
>select RPAD(‘oracle’ ,8, ‘#’) from dual; [ oracle##]
TRANSLATE (string, from char, to char)
>select TRANSLATE (‘ALLEN’, ‘AEI’, ‘#@$’) from dual; [#LL@N]
REPLACE (string, searching substring, replacing string)
>select REPLACE (‘light’ , ‘l’, ‘r’) from dual; [right]
SUBSTR (string, starting position, number of char)
Used to extract a substring from main string
>select SUBSTR (‘oracle’, 2) from dual; [racle]
>select SUBSTR (‘oracle’, 1, 3) from dual; [ora]
>select SUBSTR (‘oracle’, -1, 1) from dual; [e]
>select SUBSTR (‘oracle’, -2, 2) from dual; [le]
INSTR (string, char, starting position, occurrence)
It returns the numeric position of character in the given string.
>select INSTR(‘ADAMS’, ‘A’,1, 1) first from dual; [1]
>select INSTR(‘ADAMS’, ‘A’,1, 2) second  from dual; [3]
>select INSTR(‘ADAMS’, ‘A’,1, 3) third  from dual; [0]


RENAME
n  We can’t rename two tables or two columns simultaneously.
n  We can’t drop two tables simultaneously.
n  We can update more than one column at a time.

Difference between DROP, DELETE and TRUNCATE
          Drop
            Delete
           Truncate
1. Whole structure and data will drop.

2.can’t  Rollback

3.can’t use where clause

4.All data and structure
drop simultaneously
1. Structure will not drop, data will drop.

2.can Rollback

3.can use WHERE clause

4. Row by Row data Delete
1. Structure will not drop data will drop.

2.can’t Rollback

3.can’t use WHERE clause

4. Only all data will drop, truncate operation drop and recreate the table.

Difference between JOIN and UNION
                      Join
                    Union
1.   Join the columns.

2.   Duplicate are allowed

3.   Combine the column based on condition.
1.Merge the row

2.Duplicates are not allowed

3. Combine the result of two select statements.


WHERE Vs HAVING
                     Where
                    Having
1.Without group by clause

2. Where clause select Row before grouping.

3. Where clause can’t contain aggregate function.
1.With group by clause

2.Having clause select after grouping


3.Having clause contain aggregate
Function.

CURSOR

ü  Cursor is handle or pointer to the context area.
ü  The oracle engine used a work area for its internal processing and execute to an SQL statement.
ü  To execute SQL statements oracle needs to create an area of memory known as context area.
ü  Context area contains the information needed to process the statements. It includes also the number of row Processed by the statements.

Use of cursor
                     I.        Using the cursor, the PL/SQL program can control the context area.
                   II.        Cursor allow to FETCH  and  PROCESS  rows return by a select statement.

Cursor Type
Implicit Cursor- This is automatically declare by oracle every time an SQL statements is executed. The programmer can’t control or Process the information in an implicit cursor.
a)   This is automatically associated with every DML operation.
b)   All updates and delete statements have cursor that identifies the set of rows that will be affected by the operation.
c)   An insert statement needs a place to insert data in DB, which is also handle by implicit cursor.
d)   Implicit cursor automatically performs OPEN, FETCH and CLOSE the context area.

Explicit Cursor-It is  cursor that is define by the programmer within the program for query that returns more than one Row of Data.
a)   Explicit cursor area more efficient in implementation, hence easy to trap error.
b)   It is designed to work with SELECT statements that return more than one record at a time.

4 Steps for explicit cursor
1)   Cursor declaring
2)   Cursor opening
3)   Cursor fetching
4)   Cursor closing
Cursor Attributes
A) Cursor % ISOPEN
B) Cursor % FOUND
C) Cursor % NOTFOUND
D) Cursor % ROWCOUNT

Procedure/Function
®  Procedures are a named PL/SQL block, take parameters and perform an action.
®  Procedure or function is logically grouped set of PL/SQL and statements that perform a specify task.

Procedure Vs Function
                    Procedure
                   Function
1. Procedure doesn’t contain return clause in header & executable part.

2. Procedure may be return or may not be return a value.

3. Procedure can’t be called into select statements.

4. Procedure are used to implement business logic.
1. Function contain return clause in header & executable part.

2. Function always returns a value.


3. Function can be called into select statement.

4. Function are used for numeric calculation.




Package-
£  Package is PL/SQL construct that allow to store related objects together.
£  Package is a method is to handle all required function and procedure together.
£  Package contains variable, function, procedure, cursor & exception.
Package has two part
Package specification- contain
1)   Name of package
2)   Name of data type of any arguments.
3)   This declare is local to DB but global to package.
Package Body-
1)   It contain code of program that specified in package specification.
2)   Package body successfully compiled after the successfully compilation of package header.
Types of package.

Package “private” for object that are declare in side the package body.

Package “Public” for object that are declare within the package specification.

Subprogram Overloading
More than one procedure or function can be created with same name  & different parameters within the package.
Trigger
¯  Oracle define trigger as “procedure that are store in the DB and implicitly run or fired, when something happens.
¯  Procedure are implicitly executed when an INSERT, UPDATE or DELETE against the table, these procedure are called DB trigger.





¯  Trigger Vs Procedure
                  Trigger
                   Procedure
1.   Trigger does not accept parameters.

2.   A trigger is executed implicitly by the oracle engine.
1. Procedure accepts the parameters.


2.Procedure is execute explicitly, called by a user

On one particular table any number of triggers can be defined, but all these must belongs to 12 types of triggers.
Types of trigger-
Row trigger-
A Row trigger is fired each time, a Row is affected by the triggering statements (or trigger event).
Statement Trigger-
A statement trigger is fired once for the entire triggering statement. Number of row is affected or not affected but statement trigger fires at least once.
Before trigger
These trigger fires BEFORE any transaction is implemented.
After trigger
These trigger fires AFTER any transaction is implemented.
Use of Trigger
1)   Statistical information on table access.
2)   Preventing invalid transaction.
3)   Enforce complex business rule that can’t be define by in integrity constraint.





No comments: