sql study material

8/16/2019 SQL Study Material

1/62

1

www.askhareesh.blogspot.com

www.facebook.com/askhareesh

Study Material


2/62

2



SQL STUDY MATERIAL

INDEX

S.NO NAME OF THE CONTENTS PAGE NO.

1 INTRODUCTION OF SQL STATEMENTS 3-18

2 PSUDO COLUMNS 18-19

3 SQL OPERATORS 19-21

4 SQL FUNCTIONS 21-375 SET OPERATORS 37-40

6 CONSTRAINTS 40-42

7 JOINS 42-46

8 SUB QUERIES 47-50

9 VIEWS 50-54

10 SYNONYMS 54-56

11 INDEXES 56-64

12 DICTIONARY TABLES 64


3/62

3



Introduction of SQL:

Structured English Query Language (“SEQUEL”) was developed by IBM CorporationInc., to use Codd‟s model.SEQUEL later becomes SQL (still pronounced as

“sequel”). SQL (Structured Query Language) is an ANSI and ISO standard computer

language for accessing, manipulating and controlling data‟s in databasesystems.SQL works with database programs like Oracle, MS SQL Server, MySQL,Teradata, MS Access, DB2, Informix, Sybase, etc.

SQL is a Standard - BUT....

Unfortunately, there are many different versions of the SQL language,but tobe in compliance with the ANSI standard.

They must support the same major keywords in a similar manner(Such as

SELECT, UPDATE, DELETE, INSERT, WHERE, and others).

Note:Most of the SQL database programs also have their own proprietary extensions inAddition to the SQL standard!

SQL Queries:With SQL, We can query a database and have a result set returned.SQL Advantages:SQL has the following advantages:

Efficient

Easy to Learn & Use(SQL is simple English-like commands make it easy todesign and manage the information)

Functionally complete(You can define, retrieve, and manipulate data in thetables)

SQL in Oracle:

The Oracle Server supports ANSI standard SQL and contains extensions. Oracle SQL statements complies with industry-accepted standards.

Five Sub-Languages in SQL:SQL provides 5 Sub-Languages for a variety of database tasks such as:

DDL – Data Definition Language DML – Data Manipulation Language

DRL/DQL – Data Retrieval/Query Language TCL – Transaction Control Language DCL – Data Control Language


4/62

4



DDL:DDL statements are used to provide Structure and Structure-Oriented changes tothe objects like Tables, Views etc,Statements are:

Create - Used to create objects like Table, View, Index and Synonyms

Alter - Used to alter definition of an object.

Drop - Used to drop the definition of an object along with its data.

Truncate - Used to remove all the rows from the object & its structureremains

Rename - Used to rename the objects

Rules for creating a table:

Table names & column names must begin with a letter and be 1–30characters.

Table names must begin with a character A–Z or a–z. Names may contain characters, numerals and special characters like A–Z, a–

z,0–9, _ (underscore), $, and # (legal characters, but their use isdiscouraged).

The name must not be same as the name of any other object in yourschema.

Names must not be an Oracle Server reserved word.

Table names are not case sensitive. If you create a table with upper, lower ora mixed case the table‟s names are stored only the upper case in the datadictionary.

If you want to create a table with the name in lower case characters, thenyoumust enclose the table name within double quotes while creating the table.

The no of columns in a table can be up to a maximum of 1000.

The column names in the table must be unique. However, column names canbeduplicated across tables.

Syntax:

CREATE TABLE [schema.](

[DEFAULT ] [,…..] );

E.g: CREATE TABLE dept(


5/62

5



deptno NUMBER(2),dname VARCHAR2(14),loc VARCHAR2(13));

The DEFAULT Option:

This Default clause lets you specify a value to be assigned to the column iftheuser omits the value for the column.

This option prevents null values from entering the columns if a row isinsertedwithout a value for the column.

The default value can be a literal value, an expression, or a SQL function,

such asSYSDATE and USER.

But the value cannot be the name of another column or a pseudo columnsuch asLEVEL, ROWNUM, PRIOR, NEXTVAL or CURRVAL.

The default expression must match the data type of the column.

E.g: CREATE TABLE emp(empno NUMBER(4),sal NUMBER(7,2) DEFAULT 2500,comm NUMBER(7,2) DEFAULT 0);Creating a Table with Rows from another table:E.g: CREATE TABLE empdup AS SELECT * FROM SCOTT.emp;E.g: CREATE TABLE empcopy (empno, ename, Annualsal, deptno)AS SELECT empno, ename, sal*12, deptno FROM empWHERE deptno = 10;

To copy the structure of table from other schema or from same schemaE.g: CREATE TABLE empstru AS SELECT * FROM emp WHERE 1 = 2;

The ALTER Command:To change the definition of a table ALTER TABLE Command is used.

Use the ALTER TABLE statement to: Add a new column

Modify an existing column Drop a column Rename a column


6/62

6



The Add clause:ADD clause with ALTER Table is used to add new columns to the table.

Syntax: ALTER TABLE ADD (column datatype [DEFAULT expr][, column datatype]...);

E.g: ALTER TABLE emp ADD grade CHAR(1);

E.g: ALTER TABLE emp ADD (pf NUMBER(7,2),esi NUMBER(7,2) );

The Modify clause:Column modification can include changes to a column‟s data type, size, and default value.

Syntax: ALTER TABLE MODIFY (column datatype [DEFAULT expr][, column datatype]...);

Note: You can change any columns data type provided if all rows of that column

contain NULL.

You can change the size of a column‟s data type. Note that you can increasethe size. Whether the columns contain NULL value or not.

You can decrease the width of a column only if the column contains only nullvalues or if the table has no rows.

9i You can decrease the size as long as the change does not affect therequire data to be modified.

You can modify a DATA column to TIMESTAMP.

You can convert a CHAR column to the VARCHAR2 data type or convert a

VARCHAR2 column to the CHAR data type only if the column contains nullvalues or if you do not change the size.

A change to the default value of a column affects only subsequent insertionsto the table.

E.g: ALTER TABLE emp MODIFY grade VARCHAR2(10);E.g: ALTER TABLE emp MODIFY sal NUMBER(10,2);E.g: ALTER TABLE emp MODIFY (job VARCHAR2(10),shopno number(3) );


7/62

7



The Drop clause:The Drop clause removes a column from a table.Syntax: ALTER TABLE DROP [COLUMN] (column_name [,column_name]…);

E.g: ALTER TABLE emp DROP COLUMN comm;E.g: ALTER TABLE emp DROP (comm);E.g: ALTER TABLE emp DROP (hiredate, comm);

Note:o The column may or may not contain data.o

The table must have at least one column remaining in it after it isaltered.

o Once a column is dropped, it cannot be recovered.

The Rename clause:It is used to change the existing column name of a table.Syntax: ALTER TABLE RENAME COLUMN TO ;

E.g: ALTER TABLE emp RENAME COLUMN sal TO salary;

Note:

It invalidates all objects that depend on the renamed column.

You must be the owner of the object that you rename.

It works Oracle 9i ver 9.2.0.1.0 and prior.

The DROP command:The DROP TABLE statement removes the definition of a table from thedatabase.When you drop a table, the database loses all the data in the table and allthe indexes associated with it. Dropping table invalidates the table‟s dependentobjects like views, synonyms etc The DROP TABLE statement, once executed, isirreversible. To drop a table, the table should be in your own schema or you shouldhave DROP ANY TABLE system privilege. The Oracle Server does not question the

action when you issue the DROP TABLE statement.

Syntax: DROP TABLE ;E.g: DROP TABLE emp;


8/62

8



The RENAME command:The RENAME statement is used to rename a table, view, sequence or a synonym.Syntax: RENAME TO ;

E.g: RENAME dept TO department;

Note:

It transfers the integrity constraints, indexes and grants on the old object tonew object.

It invalidates all objects that depend on the renamed object.

You must be the owner of the object that you rename.

The TRUNCATE command: The TRUNCATE TABLE statement is used to remove all rows from a table and

to release the storage space used by that table. When you TRUNCATE a table, the table structure is not removed unlike DROP

TABLE command.

The TRUNCATE also removes all data from all indexes also. When using the TRUNCATE TABLE statement, you cannot rollback.

You must be the owner of the table or have DELETE TABLE system privilegesto truncate a table.

The DELETE statement can also remove all rows from a table, but it does notrelease storage space.

The TRUNCATE command is more efficient and faster than the DROP TABLE.If you drop a table, then you need to recreate the table‟s indexes, integrityconstraints and triggers.

When you truncate a table, only the data is removed and it has none of theseeffects.

If the table is the parent of a referential integrity constraint, you cannottruncate the table. Disable the constraint before issuing the TRUNCATEstatement.

Syntax: TRUNCATE TABLE ;E.g: TRUNCATE TABLE emp;

Removing rows with the TRUNCATE statement is faster than removing themwith the DELETE statement for the following reasons:

The TRUNCATE statement is a data definition language (DDL) statement and

generates no rollback information. Truncating a table does not fire the delete triggers of the table.


9/62

9



DML:DML statements enable the manipulation of data in the existing object or used toprovide Record and Record-Oriented changes.Statements are:

Insert - Used to add rows to a table

Update - Used to modify the existing values in a table

Delete - Used to remove one or more rows from a table

Merge (9i) - Used to select rows & those rows are update/insert into anothertable

DML statements enable manipulation of data in the existing oracle schemaobjects such as tables and views.

You can do the Insert, Update, Delete, or Merge data in the database.

A collection of DML statements that form a logical unit of work is called atransaction.

Insert Command:Insert statement is used for adding new row in an existing table.Syntax: INSERT INTO [schema.]{|}[(column [, column…])] [VALUES (value [, expr…|DEFAULT])] | [sub_query];

E.g: INSERT INTO dept VALUES (50, 'HRD', 'Chennai');

Note:

This statement with the VALUES clause adds only one row at a time to atable.

Character data should be enclosed within single quotes.

Date and Time stamp data‟s can be provided with single quotes or you canuse the conversion functions.

Number values should not be enclosed in single quotes; because implicitconversion may take place for numeric values assigned to NUMBER data typecolumns if single quotes are included.

If you want to insert a new row that contains values for each column, thecolumn list is not required in the INSERT clause.

However, if you do not use the column list, the values must be listed

according to the default order of the columns in the table, and a value mustbe provided for each column.


10/62

10



Inserting data’s into specific columns: If you want to insert a new row that contains values for specific column, the

column list is required in the INSERT clause.

The column list should contain the column names of the table.

E.g: INSERT INTO emp (empno, ename, job) VALUES (7550, 'DOLLY', 'HR');

Inserting Default Values (9i): The DEFAULT keyword can be used in INSERT statements to identify a

defaultcolumn value.

If no default value exists, a null value is used.

When creating a table, you can specify a default value for a column.

Implicit method: Omit the column from the column list.

E.g: INSERT INTO dept (deptno, dname) VALUES (30, 'Purchasing');Explicit method: Specify the DEFAULT keyword in the VALUES clause.E.g: INSERT INTO dept (deptno, dname) VALUES (30, 'Purchasing', DEFAULT);

Inserting Null Values:The NULL keyword can be used in INSERT statements to insert NULL value.Implicit method: Omit the column from the column list.

E.g: INSERT INTO dept (deptno, dname) VALUES (30, 'Purchasing');Explicit method: Specify the NULL keyword in the VALUES clause.E.g: INSERT INTO dept VALUES (100, 'Finance', NULL);

Inserting Special Values:You can use functions to enter special values in your table like SYSDATE etc.,E.g: INSERT INTO emp (empno, ename, hiredate, mgr, deptno)VALUES (UID, USER, SYSDATE, NULL, DEFAULT);

Inserting Specific Date Format: The default date format is DD-MON-YY is used to insert a date value.

If a date must be entered in a format, other than the default format by usingthe TO_DATE function.

E.g: INSERT INTO emp (empno, ename, hiredate)VALUES (114, 'Den', TO_DATE('FEB 3, 1999', 'MON DD, YYYY'));


11/62

11



Inserting rows using substitution variables:

SQL *Plus substitution variable „&‟ allow the user to add values interactivelyin an Insert statement.

The SQL *Plus prompts for the value of the variable, accepts it andsubstitutes into the variable.

Any number of rows can be inserted one after another by using the „/‟. Whichexecutes the last executed statement. But supply a different set of valueseach time you run it.

E.g: INSERT INTO dept (deptno, dname, loc) VALUES (&deptno, '&dname', '&loc');

Copying Rows from Another Table: You can use the INSERT statement to add rows to a table where the values

arederived from existing tables. In place of the VALUES clause, use a sub-query.

The number of columns and their data types in the column list of the INSERTclause must match the number of values and their data types in the sub-query.

Syntax: INSERT INTO table [column (, column…)] subquery;

E.g: INSERT INTO emp2 SELECT * FROM emp;INSERT INTO emp2 (empno, ename, job, sal)SELECT empno, ename, job, sal FROM scott.emp;

Update Command: Update statement is used to change or modify the existing values in a table.

Values of a single column or a group of columns can be updated.

Updates can be carried out for all the rows in a table or selected rows.

Syntax: UPDATE [schema.]{ | }SET column = [, column = ,………] [WHERE ];

Note: Condition identifies the rows to be updated and is composed of column

names expressions, constants, sub-queries, and comparison operators

In general, use the primary key to identify a single row. Using other columnscan unexpectedly cause several rows to be updated.


12/62

12



E.g: UPDATE emp SET comm = 1500;If you omit the WHERE clause, all the rows in the table are modified.

WHERE Clause with UPDATE Statement:The UPDATE statement modifies specific rows if the WHERE clause is specifiedor WHERE clause allows the update for selected rows.E.g: UPDATE emp SET comm = 500 WHERE empno = 7900;E.g: UPDATE emp SET sal = sal + 5000 WHERE sal > 3500;E.g: UPDATE emp SET sal = sal * 1.4 WHERE job = „MANAGER‟;

Updating more than one column values:E.g: UPDATE emp SET sal = sal * 2, comm = sal * 0.2 WHERE empno = 7900;

Updating with Default, Null and Special Values:

E.g: UPDATE emp SET comm = NULL WHERE empno = 7654;E.g: UPDATE emp SET comm = DEFAULT WHERE comm IS NULL;E.g: UPDATE emp SET hiredate = sysdate WHERE comm = 0;

Updating rows using substitution variables:E.g: UPDATE emp SET ename = „&Name‟ WHERE empno = &Empno; E.g: UPDATE emp SET comm = sal * 0.6 WHERE sal = &Salary;E.g: UPDATE emp SET &Expression WHERE &condition;Sub-queries in Update statement:

Delete Command:

Delete statement removes one or more existing rows from a table.

WHERE clause allows a set of rows to be deleted from a table by specifyingthe conditions.

If you omit the WHERE clause all the rows in the table will be deleted.

Syntax: DELETE [FROM] [schema.]{ | }[WHERE ];

E.g: DELETE emp;E.g: DELETE FROM emp;E.g: DELETE FROM emp WHERE job= 'CLERK';E.g: DELETE FROM emp

WHERE hiredate > '01-JAN-1980' AND hiredate < '31-DEC-1999';.


13/62

13



Types of Multi-table INSERT Statements:Oracle 9i introduces the following types of multi-table INSERT statements:

Unconditional INSERT

Conditional ALL INSERT

Conditional FIRST INSERT

Pivoting INSERT

Unconditional INSERT: ALL into_clause Specify ALL followed by multiple insert_into_clauses to perform an

unconditional multi-table insert.

The Oracle Server executes each insert_into_clause once for each rowreturnedby the sub-query.

This INSERT statement is referred to as an unconditional INSERT, as no

furtherrestriction is applied to the rows that are retrieved by the SELECT statement.

All the rows retrieved by the SELECT statement are inserted into the tables.

The VALUES clause in the INSERT statements specifies the columns from theSELECT statement that have to be inserted into each of the tables.

Syntax: INSERT [ALL] [insert_into_clause values_clause] (sub-query);

E.g: INSERT ALLINTO em1 (empno, hiredate, sal) VALUES (empno, hiredate, sal)INTO em2 (empno, mgr, sal) VALUES (empno, mgr, sal)SELECT empno, hiredate, sal, mgr FROM empWHERE empno > 7840;E.g: INSERT ALLINTO dept1 (deptno, dname, loc)INTO dept2 (deptno, dname, loc)SELECT * FROM dept;E.g: INSERT ALLINTO dept1INTO dept2SELECT * FROM dept;E.g: INSERT ALLINTO dept1 (deptno, dname, loc)

VALUES (10, 'SALES', 'CHENNAI')INTO dept2 (deptno, dname, loc)VALUES (20, 'HRD', 'DELHI')SELECT * FROM dual;


14/62

14



Conditional INSERT: conditional_insert_clause Specify the conditional_insert_clause to perform a conditional multi-table

insert.The Oracle server filters each insert_into_clause through the corresponding

WHEN condition, which determines whether that insert_into_clause isexecuted.A single multi-table insert statement can contain up to 127 WHEN clauses.

conditional_insert_clause Syntax:[ALL] [FIRST][WHEN condition THEN] [insert_into_clause values_clause][ELSE] [insert_into_clause values_clause]- 103 -Conditional INSERT: ALL

If you specify ALL, the Oracle server evaluates each WHEN clause regardlessofthe results of the evaluation of any other WHEN clause.

For each WHEN clause whose condition evaluates to true, the Oracle serverexecutes the corresponding INTO clause list.

This INSERT statement is referred to as a conditional ALL INSERT, as afurtherrestriction is applied to the rows that are retrieved by the SELECT statement.

Syntax: INSERT [ALL] [WHEN condition THEN][insert_into_clause values_clause] (sub-query);E.g: INSERT ALLWHEN SAL > 1800 THENINTO em1 (empno, hiredate, sal) VALUES (empno, hiredate, sal)WHEN MGR > 7600 THENINTO em2 (empno, mgr, sal) VALUES (empno, mgr, sal)SELECT empno, hiredate, sal, mgr FROM empWHERE empno > 7654;E.g: INSERT ALLWHEN deptno < 20 THENINTO dept1 (deptno, dname, loc)WHEN deptno >= 30 THENINTO dept2 (deptno, dname, loc)

SELECT * FROM dept;E.g: INSERT ALLWHEN deptno


15/62

15



INTO dept2 (deptno, dname, loc)SELECT * FROM dept;- 104 -Conditional FIRST: INSERT

If you specify FIRST, the Oracle Server evaluates each WHEN clause in theorder in which it appears in the statement.

If the first WHEN clause evaluates to true, the Oracle Server executes thecorresponding INTO clause and skips subsequent WHEN clauses for givenrow.

Syntax: INSERT [FIRST] [WHEN condition THEN][insert_into_clause values_clause] (subquery)

E.g: INSERT FIRST

WHEN salary > 10000 THENINTO emssal VALUES (deptid, salary)WHEN hdate like ('%87%') THENINTO emhd87 VALUES (deptid, hdate)WHEN hdate like ('%81%') THENINTO emhd81 VALUES (deptid, hdate)SELECT deptno deptid, SUM(sal) salary, MAX(hiredate) hdateFROM emp GROUP BY deptno;

DRL/DQL:DRL/DQL statements enable to retrieve the data‟s from the database. Statements is: Select - Used to extracts data from a database table

TCL:TCL statements enable to manage the changes made by DML statements, which iscalled as Transactions. Transactions can change a set data from one state toanother.A transaction helps to store the data permanently in the database.Statements is:

Commit - Used to make the changes permanent to the database

Rollback - Used to undo or cancel the transactions

Save point - Used to divide a transaction into smaller sub-units

When Does a Transaction Start?A transaction begins when the first DML statement is encountered or first DMLstatement issued after the previous transactions.


16/62

16



When Does a Transaction End?A transaction ends when one of the following occurs:

A COMMIT or ROLLBACK statement is issued

A DDL statement, such as CREATE, etc., is issued

A DCL statement is issued

The user exits SQL*Plus

A user process terminates abnormally.

A machine fails or the system crashesAfter one transaction ends, the next executable SQL statement automatically startsthe next transaction.

Commit:Committing a transaction means making permanent the changes performed by theSQL statements within the transaction.

Syntax: COMMIT;

Rollback:Rolling back means undoing any changes to data that have been performed bySQL statements within an uncommitted transaction.Syntax: ROLLBACK;

Save Point:You can create an intermediate marker in the current transaction by using theSAVEPOINT statement, which divides the transaction into smaller sections withinthe current transaction.You can then discard pending changes up to that marker by using the ROLLBACKTO SAVEPOINT statement.Savepoints are similarly useful in application programs. If a procedure containsseveral functions, you can create a savepoint before each function begins, then youcan easily rollback to that particular savepoint if the function fails.

Syntax: SAVEPOINT ;E.g: SAVEPOINT rainforest;Note:

Savepoint names must be unique within a given transaction.

If you create another savepoint with the same name as an earlier one, theearlier savepoint is erased.

SAVEPOINT is not ANSI standard SQL.


17/62

17



Rollback to Savepoint: ROLLBACK TO SAVEPOINT allows to Roll back the current transaction to the

specified savepoint, thereby discarding any changes and or savepointscreated after the savepoint to which you are rolling back.

If you omit the TO SAVEPOINT clause, the ROLLBACK statement rolls backthe entire transaction.

As savepoints are logical, there is no way to list the savepoints you havecreated.

Syntax: ROLLBACK TO ;E.g: ROLLBACK TO rainforest;Implicit Transaction ProcessingAutomatic commit:

The action that will force a commit to occur, even without issuing the COMMIT is: Any DDL or DCL statements issued.

SQL*Plus exited normally. Connecting to other user.

Disconnecting from Oracle.

Automatic rollback:

The action that will force a rollback to occur, even without issuing theROLLBACK is:

Abnormal termination of SQL*Plus

A machine fails or the system crashes

System failure

Network failure

DCL:DCL statements enable to control the access to the database and its objects.Statements are:Grant - Used to give access of the database object from one user to anotherRevoke - Used to remove the object privileges granted to other user

Creating a User:

The DBA creates the user by executing the CREATE USER statement.

The user does not have any privileges at this point. The DBA can then grant privileges to that user.

These privileges determine what the user can do at the database level.

Syntax: CREATE USER user IDENTIFIED BY password;


18/62

18



E.g: CREATE USER scott IDENTIFIED BY tiger;

Changing Your Password:The DBA creates an account and initializes a password for every user.You can change your password by using the ALTER USER statement.

In Oracle 7.x:Syntax: ALTER USER IDENTIFIED BY ;

E.g: ALTER USER scott IDENTIFIED BY lion;Although this statement can be used to change your password, there are manyotheroptions. You must have the ALTER USER privilege to change any other option.In Oracle 8 and prior:

Syntax: PASSW [ORD] ;It will prompt the user for old & new password.E.g: PASSWORD scott;Changing the password for scottOld password: tigerNew password: lionRetype new password: lionTypical User Privileges:Once a user is created by DBA, he grants specific system privileges to a user.

Pseudo Column:A pseudo column behaves like a table column, but is not actually stored in thetable. You can select from pseudo columns, but you cannot insert, update, or deletetheir values.

ROWID Maintains Unique Rowid for each and every record. It‟s Permanent.

ROWNUM Displays Sequence number of the records. It‟s Temporary. LEVEL and SYSDATE

CURRVAL and NEXTVAL

E.g: SELECT rownum, rowid FROM emp;

To display the Structure of a tables:

Syntax: DESC [RIBE] ;E.g: DESC emp;DESCRIBE dept;


19/62

19



Restrict the Rows using WHERE clause (Selection) You can restrict the rows returned from the query by using the WHERE

clause.

A WHERE clause contains a condition that must be met, and it directly followsthe FROM clause.

If the condition is true, the row meeting the condition is returned.

The WHERE clause can compare values in columns, literal values, arithmeticexpressions or functions.

The character strings and date values should be enclosed within singlequotes.

The character strings when specified as a value are always case sensitive.

The date values when specified as a value are always format sensitive.

Comparison Operators:

= Equal to> Greater than< Less than> = Greater than or equal to< = Less than or equal to< > or ! = or ^ = Not equal to

E.g: SELECT * FROM emp WHERE job = 'MANAGER';SELECT * FROM emp WHERE sal >= 2600;SELECT * FROM emp WHERE comm < sal;

Special Comparison Operators:BETWEEN…..AND Between two values (inclusive) IN Match any of a list of valuesLIKE Match a character patternIS NULL Is a null valueNOT Keyword can be used before any of this operator to negate its meaning.

BETWEEN…AND: E.g: SELECT ename, sal FROM emp WHERE sal BETWEEN 1600 AND 3500;IN:E.g: SELECT ename, sal FROM emp WHERE ename IN ('KING', 'WARD');LIKE:

Like is a pattern matching comparison operator. Like operator matching is case sensitive.

„%‟ And „_‟ (underscore) are wild card characters used to specify a pattern „%‟ Represents a sequence of zero or more characters. „_‟ Represents any single character


20/62

20



You can use wild card characters multiple times either before & after thepattern.

E.g: SELECT ename, sal FROM emp WHERE ename LIKE('S%');E.g: SELECT ename, sal FROM emp WHERE ename LIKE('S_%');E.g: SELECT ename, sal FROM emp WHERE ename LIKE('_I%');E.g: SELECT ename, sal FROM emp WHERE ename LIKE('%R');

Escape Option: You can include the actual characters "%" or "_" in the pattern by using the

ESCAPE clause, which identifies the escape character.

If the escape character appears in the pattern before the character "%" or"_" then Oracle interprets this character literally in the pattern, rather thanas a special pattern matching character.

The ESCAPE clause identifies the backslash (\) as the escape character. To search for employees with the pattern 'A_B' in their name:

E.g: SELECT ename FROM emp WHERE ename LIKE '%A\_B%' ESCAPE '\';E.g: SELECT ename FROM emp WHERE ename LIKE 'S%\_%' ESCAPE '\';E.g: SELECT ename FROM emp WHERE ename LIKE '\%S%' ESCAPE '\';

IS NULL:E.g: SELECT ename, sal FROM emp WHERE comm IS NULL;

Logical Operators:

AND Returns TRUE if both component conditions are true

OR Returns TRUE if either component condition is true

NOT Returns TRUE if the following condition is false

AND:E.g: SELECT ename, sal, job FROM emp WHERE sal > 1600 AND deptno = 30;OR:E.g: SELECT ename, sal, job FROM emp WHERE sal >1600 AND deptno = 30;NOT:E.g: SELECT ename, sal, comm FROM emp WHERE comm IS NOT NULL;

Rules of Precedence:1st Arithmetic operators

2nd Concatenation operator3rd Comparison conditions4th IS [NOT] NULL, LIKE, [NOT] IN, EXIXTS5th [NOT] BETWEEN6th NOT logical condition


21/62

21



7th AND logical condition8th OR logical conditionOverride rules of precedence by using parentheses ( ).

Sorting Rows using ORDER BY clause

The ORDER BY clause can be used to sort the rows either in Ascending orDescending.

If you use the ORDER BY clause, it must be the last clause of the SQLstatement.

You can specify an expression, an alias, or column position as the sortcondition.

By default, it takes Ascending. For Descending you have to specify DESC.

You can also order by columns that are not included in the SELECT clause.

In ascending Null‟s are last. In descending Null‟s are first.

Ascending Order:E.g: SELECT empno, ename, sal FROM emp ORDER BY sal;SELECT empno, ename, sal * 12 FROM emp ORDER BY sal * 12 ASC;Descending Order:E.g: SELECT empno, ename, sal FROM emp ORDER BY sal DESC;

Sorting by Column Alias:E.g: SELECT ename, sal * 12 annualsal FROM emp ORDER BY annualsal;Sorting by Multiple Columns:E.g: SELECT ename, sal, deptno FROM emp ORDER BY deptno, sal DESC;Sorting by Column Position No:E.g: SELECT empno, ename, sal FROM emp ORDER BY 3;Sorting the column that are not included in the Select:E.g: SELECT empno, ename, sal FROM emp ORDER BY job;

SQL FunctionsFunctions are a very powerful built-in feature of SQL.

Advantages of functions: Used to perform calculations on data.

Modify individual data items.

Easy manipulate output for groups of rows.

Used for alter date and number formats for display.

Types of Functions: Single Row Function Group Function (Aggregate Function)


22/62

22



Single Row Functions:Single-row functions are used to manipulate data items. They accept one or morearguments and return one value for each row returned by the query.An argument can be one of the following:

User-supplied constant

Variable value

Column name

ExpressionFeatures of single-row functions include:

Acting on each row returned in the query

Returning one result per row

Possibly returning a data value of a different type than that referenced

Single row functions can be nested.

Can be used in SELECT, WHERE, and ORDER BY clauses; can be nested

Types of Single Row Functions:1. General Functions2. Character Functions3. Number Functions4. Date Functions5. Conversion Functions

General Functions:NVL DECODENVL2 LEASTNULLIF (9i) GREATESTCOALSECE (9i) USER & UID

NVL:Syntax: NVL (expr1, expr2)E.g: SELECT ename, (sal * 12) + NVL(comm, 0) FROM emp;NVL2:Syntax: NVL2 (expr1, expr2, expr3)E.g: SELECT ename, comm, NVL2(comm, comm * 1.5, 1000) FROM emp;NULLIF (9i):Syntax: NULLIF (expr1, expr2)E.g: SELECT ename, NULLIF(sal, 3000) FROM emp;COALESCE (9i):

Syntax: COALESCE (expr1, expr2,....expr n)E.g: SELECT ename, comm, sal, COALESCE(comm, sal, 7000) FROM emp;Before Executing the above statement, just execute this statement, then only youcan understand the coalesce functionUPDATE emp SET sal = NULL WHERE sal >= 3000;


23/62

23



DECODE:Syntax: DECODE(expr | column, search1, result1 [,search 2, result2,..] [, default])E.g: SELECT empno, ename, sal, job, DECODE( job, 'MANAGER', 'Admin','CLERK', 'Accounts','SALESMAN', 'Marketing') TradeFROM emp;E.g: SELECT empno, ename, sal, job, DECODE( job, 'MANAGER', 'Admin','CLERK', 'Accounts', 'Others') TradeFROM emp;E.g: SELECT empno, ename, sal, job, DECODE( job, 'MANAGER', 'Admin','CLERK', 'Accounts', job) TradeFROM emp;

LEAST:Syntax: LEAST(exp1, exp2,…..)

E.g: SELECT LEAST('HARRY', 'HARRIOT', 'HAROLD') FROM DUAL;SELECT LEAST(25, 14, 84, 12, 54 ,7) "LEAST" FROM DUAL;GREATEST:Syntax: GREATEST (exp1, exp2,…..) E.g: SELECT GREATEST('HARRY', 'HARRIOT', 'HAROLD') FROM DUAL;SELECT GREATEST(25, 14, 84, 12, 54 ,7) FROM DUAL;

UID: This function returns the User ID of the session user, generated by system.E.g: SELECT UID FROM DUAL;USER: This function returns the name of the session user.E.g: SHOW USER;SELECT USER FROM DUAL;

Character Functions:Case - Manipulation Functions:LOWERUPPERINITCAPCharacter - Manipulation Functions:CHR TRIMCONCAT LTRIMSUBSTR RTRIMLPAD REPLACERPAD TRANSLATE

REVERSECharacter Functions Returning Numerical Values:ASCIILENGTHINSTR


24/62

24



LOWER:Syntax: LOWER (char)E.g: SELECT LOWER(ename) FROM emp WHERE LOWER(job) = 'manager';

UPPER:Syntax: UPPER (char)E.g: SELECT UPPER('oracle') FROM DUAL;

INITCAP:Syntax: INITCAP (char)E.g: SELECT empno, INITCAP(ename) FROM emp;SELECT INITCAP('ORACLE SERVER') FROM DUAL;

CHR:

Syntax: CHR(n)E.g: SELECT CHR(67) || CHR(65) || CHR(84) "Dog" FROM DUAL;

CONCAT:Syntax: CONCAT (char1, char2)E.g: SELECT CONCAT(empno, ename) FROM emp;

SUBSTR:Syntax: SUBSTR(string, position[, substring_length])E.g: SELECT SUBSTR('ABCDEFG', 3, 4) "Substring" FROM DUAL;SELECT SUBSTR('ABCDEFG', -4, 3) "Substring" FROM DUAL;SELECT SUBSTR('ABCDEFG', 5) "Substring" FROM DUAL;

LPAD:Syntax: LPAD(char1, n[, char2])E.g: SELECT LPAD(job, 12, '*') FROM emp;

RPAD:Syntax: RPAD(char1, n[, char2])E.g: SELECT RPAD(job, 12, '*') FROM emp;

REVERSE:Syntax: REVERSE(string)

E.g: SELECT REVERSE(ename) FROM emp;

TRIM:Syntax: TRIM([LEADING | TRAILING] FROM )E.g: SELECT TRIM(LEADING 'S' FROM ename)FROM emp;


25/62

25



SELECT TRIM(TRAILING 'S' FROM ename)FROM emp;SELECT TRIM('S' FROM ename)FROM emp;

LTRIM:Syntax: LTRIM(string [, char(s)])E.g: SELECT ename, LTRIM(ename, 'SM')FROM emp;

RTRIM:Syntax: RTRIM(string [, char(s)])E.g: SELECT ename, RTRIM(ename, 'ES')FROM emp;

REPLACE:

Syntax: REPLACE(char, search_string[, replacement_string]) --replacement byentire string

E.g: SELECT REPLACE(ename, 'A') FROM emp; --replacement by entire string SELECT REPLACE(ename, 'A', 'x') FROM emp;

SELECT REPLACE(ename, 'AM', '***') FROM emp; --replacement by entire string

TRANSLATE:

Syntax: TRANSLATE(string, from_string, to_string) - ---replacement character bycharacter

E.g: SELECT ename, TRANSLATE(ename, 'ALT', 'alt') FROM emp; ---replacementcharacter by character

ASCII:

Syntax: ASCII(char)E.g: SELECT ASCII('A') FROM DUAL;

LENGTH:Syntax: LENGTH(char)E.g: SELECT ename, LENGTH(ename) FROM empWHERE LENGTH(ename) = 5;

INSTR:Syntax: INSTR(string, substring[, position[, occurrence]])E.g: SELECT INSTR('CORPORATE FLOOR', 'OR') "Instring" FROM DUAL;

SELECT INSTR('CORPORATE FLOOR', 'OR', 3)"Instring" FROM DUAL;SELECT INSTR('CORPORATE FLOOR', 'OR', 3, 2)"Instring" FROM dual;SELECT INSTR('CORPORATE FLOOR', 'OR', -3, 2)"Reversed Instring" FROM DUAL;


26/62

26



Number Functions:ABSCEILFLOORMODPOWERSQRTSIGNTRUNCROUND

ABS:Syntax: ABS (n)E.g: SELECT ABS(-15) "Absolute" FROM DUAL; o/p: 15

CEIL:Syntax: CEIL(n)E.g: SELECT CEIL(15.7) "Ceiling" FROM DUAL; o/p: 16

FLOOR:Syntax: FLOOR(n)E.g: SELECT FLOOR(15.7) "Floor" FROM DUAL; o/p: 15

MOD:Syntax: MOD(m, n)E.g: SELECT sal, MOD(sal, 3) "Modulus" FROM emp;

POWER:Syntax: POWER(m, n)E.g: SELECT sal, POWER(sal, 2) "Salary Raised" FROM emp;

SQRT:Syntax: SQRT(n)E.g: SELECT SQRT(24) "Square root" FROM DUAL; o/p: 4.89897949

SIGN:Syntax: SIGN(n)

E.g: SELECT SIGN(-15) "Sign" FROM DUAL; o/p: -1SELECT SIGN(0) "Sign" FROM DUAL; o/p: 0SELECT SIGN(15) "Sign" FROM DUAL; o/p: 1


27/62

27



TRUNC:Syntax: TRUNC(n[, m])E.g: SELECT sal, sal / 3, TRUNC((sal / 3), 1) "Truncate" FROM emp;

ROUND:Syntax: ROUND(n[, m])E.g: SELECT sal, sal / 3, ROUND((sal / 3),1) "Round" FROM emp;

Matrix Query using General Function DECODE:E.g: SELECT job,SUM(DECODE(deptno, 10, sal)) dept10,SUM(DECODE(deptno, 20, sal)) dept20,SUM(DECODE(deptno, 30, sal)) dept30,SUM(DECODE(deptno, 40, sal)) dept40

FROM empGROUP BY job;

Date Functions:Arithmetic with Dates

Operation Result Descriptiondate + number Date Adds a no. of days to a datedate - number Date Subtracts a no. of days from a datedate - date No. of days Subtracts one date from anotherdate + number/24 Date Adds a no. of hours to a dateE.g: SELECT hiredate, hiredate + 5 FROM emp;SELECT hiredate, hiredate - 5 FROM emp;SELECT hiredate, (SYSDATE - hiredate) FROM emp;SELECT SYSDATE, SYSDATE + 12 / 24 FROM DUAL;

Type of Date Functions:MONTHS_BETWEEN Number of months between two datesADD_MONTHS Add calendar months to dateNEXT_DAY Next day of the date specifiedLAST_DAY Last day of the monthROUND Round dateTRUNC Truncate date

MONTHS_BETWEEN:Syntax: MONTHS_BETWEEN(date1, date2)E.g: SELECT MONTHS_BETWEEN(SYSDATE, hiredate)FROM emp;


28/62

28



ADD_MONTHS:Syntax: ADD_MONTHS(date, n)E.g: SELECT hiredate, ADD_MONTHS(hiredate, 2)FROM emp;

NEXT_DAY:Syntax: NEXT_DAY(date, 'char')E.g: SELECT hiredate, NEXT_DAY(hiredate, 'WEDNESDAY')FROM emp;

LAST_DAY:Syntax: LAST_DAY(date)E.g: SELECT hiredate, LAST_DAY(hiredate) FROM emp;

ROUND:Syntax: ROUND(date[,'fmt'])

E.g: SELECT hiredate, ROUND(hiredate, 'YEAR') FROM emp;SELECT hiredate, ROUND(hiredate, 'MONTH') FROM emp;SELECT ROUND(SYSDATE) FROM DUAL;

TRUNC:Syntax: TRUNC(date[,'fmt'])E.g: SELECT hiredate, TRUNC(hiredate, 'YEAR') FROM emp;SELECT hiredate, TRUNC(hiredate, 'MONTH') FROM emp;

Conversion Functions:

Implicit data-type conversion

From ToVARCHAR2 or CHAR NUMBER or DATENUMBER or DATE VARCHAR2Although implicit data-type conversion is available, it is recommended that youdo explicit data type conversion to ensure the reliability of your SQL statements.

Explicit data-type conversionFunctions:TO_CHARTO_NUMBER

TO_DATE

TO_CHAR:Syntax: TO_CHAR(date | number [, fmt[, 'nlsparam']])


29/62

29



TO_CHAR Date Type Conversions:Sample Format Elements of Valid Date FormatsE.g: SELECT ename, TO_CHAR(hiredate, 'DDspth/MMth/YYsp')FROM emp;SELECT ename, TO_CHAR(hiredate, 'fmDD Month YYYY') FROM emp;SELECT ename, TO_CHAR(hiredate,'DD Month YYYY HH24:MI:SS AM')FROM emp;

TO_CHAR Number Type Conversions:TO_NUMBER:Syntax: TO_NUMBER(char[, fmt[, 'nlsparam']])E.g: SELECT TO_NUMBER('-AusDollars100', 'L9G999D99','NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''AusDollars''')"Amount" FROM DUAL;TO_DATE:

Syntax: TO_DATE(char[, fmt[, 'nlsparam']])E.g: SELECT TO_DATE('January 15, 1989, 11:00 A.M.','Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')FROM DUAL;Display the salary in words:Ans: SELECT ename, sal, INITCAP(TO_CHAR(TO_DATE(sal,'jsp'),'jsp'))|| ' Only' "Sal in Words" FROM emp;Display the Last day of a required date in YYYY/DD/MM format:Ans: SELECT TO_CHAR(TO_DATE(LAST_DAY('01-JAN-2007')),'YYYY/DD/MM') FROM DUAL;

Conditional Expressions:Give you the use of IF-THEN-ELSE logic within a SQL statement

CASE expression (9i):Syntax: CASE expr WHEN comparison_expr1 THEN return_expr1[WHEN comparison_expr2 THEN return_expr2WHEN comparison_exprn THEN return_exprnELSE else_expr]ENDE.g: SELECT ename, job, sal, comm,CASE job WHEN 'PRESIDENT' THEN 1.8 * salWHEN 'ANALYST' THEN 1.5 * sal

WHEN 'MANAGER' THEN 1.3 * salWHEN 'SALESMAN' THEN 1.2 * salELSE salEND "REVISED_SALARY"FROM emp order by 5;


30/62

30



Connecting to SQL*Plus:CONN [ECT] logs on to another user account without exiting SQL*PlusSyntax: CONN / @ ;E.g: CONN scott/tiger@oracle;To connect with oracle without exiting SQL*PlusSQL>connEnter user-name: SCOTTEnter password: *****SQL> conn scottEnter password: ****

Aggregating Data using Group FunctionsUnlike single-row functions, group functions operate on sets of rows to give oneresult per group. These sets may be the whole table or the table split into groups.

Types of Group Functions: AVG Average value of n, ignoring null values

COUNT Count No. of Rows MAX Maximum value of expr, ignoring null values

MIN Minimum value of expr, ignoring null values

STDDEV Standard deviation of n, ignoring null values

SUM Sum values of n, ignoring null values

VARIANCE Variance of n, ignoring null values

AVG:Syntax: AVG([DISTINCT | ALL] n)E.g: SELECT AVG(sal) "Average" FROM emp;SELECT AVG(comm) "Average" FROM emp;SELECT AVG(NVL(comm, 0)) "Average" FROM emp;SELECT AVG(DISTINCT sal) "D.AVG" FROM emp;

COUNT:Syntax: COUNT(* |{ [DISTINCT | ALL] expr})E.g: SELECT COUNT(*) "Total" FROM emp;SELECT COUNT(DISTINCT deptno) "DIST.COUNT" FROM emp;SELECT COUNT(*) FROM emp WHERE comm IS NULL;

MAX:Syntax: MAX([DISTINCT | ALL] expr)E.g: SELECT MAX(sal) "Maximum" FROM emp;


31/62

31



MIN:Syntax: MIN([DISTINCT | ALL] expr)E.g: SELECT MIN(sal) "Minimum" FROM emp;

STDDEV:Syntax: STDDEV([DISTINCT | ALL] x)E.g: SELECT STDDEV(sal) "Deviation" FROM emp;

SUM:Syntax: SUM([DISTINCT | ALL] n)E.g: SELECT SUM(sal) "Total" FROM emp;SELECT SUM(DISTINCT sal) "D.SUM" FROM emp;

VARIANCE:

Syntax: VARIANCE([DISTINCT | ALL] expr)E.g: SELECT VARIANCE(sal) "Variance" FROM emp;

Guidelines for Using Group Functions: DISTINCT makes the function consider only non-duplicate values; ALL makes

it consider every value including duplicates. The default is ALL and thereforedoes not need to be specified.

The data types for the functions with an expr argument may beCHAR,VARCHAR2, NUMBER, or DATE.

All group functions (expect COUNT function) ignore null values. To substitutea value for null values, use the NVL, NVL2, or COALESCE functions.

COUNT (*) counts all selected rows including duplicates and rows with nulls.

The default „group‟ is the whole set of records in the table. Thus anyaggregate functions will apply to the whole table if no GROUP BY clause isspecified.

Creating Groups of Data by Using GROUP BY ClauseAll group functions have treated the table as one large group of information.At times, you need to split rows in a table into smaller groups or subsets.

Syntax: SELECT FROM [WHERE ]

[GROUP BY ][ORDER BY ];


32/62

32



Guidelines: If you include a group function in a SELECT clause, you cannot select

individual

results as well, unless the individual column appears in the GROUP BY clause.

You receive an error message if you fail to include the individual column listinthe GROUP BY clause.

Using a WHERE clause, you can exclude rows before dividing them intogroups.

The GROUP BY clause must be specified after WHERE clause. You must include the individual columns in the GROUP BY clause.

You cannot use a column alias in the GROUP BY clause.

By default, rows are sorted by ascending order of the columns included in theGROUP BY list. You can override this by using the ORDER BY clause.

You cannot use the WHERE clause to restrict groups.

You cannot use group functions in the WHERE clause. You use the HAVING clause to restrict groups.

E.g: SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

Using the group function in the ORDER BY clause:SELECT job, SUM(sal) FROM emp GROUP BY job ORDER BY SUM(sal);

Using the Group By Clause on Multiple Columns:SELECT deptno, job, SUM(sal) FROM emp GROUP BY deptno, job;

Restrict Groups Using HAVING ClauseYou use the HAVING clause to specify which groups are to be displayed,and thus,you further restrict the groups on the basis of aggregate information.

Note: WHERE clause conditions can‟t be used in Having clause & vice versa. The Oracle performs the following steps when you use the HAVING clause:1. Rows are grouped.2. The group function is applied to the grouped rows.3. The groups that match the criteria in the HAVING clause are displayed.

Syntax: SELECT column, group_function FROM table_name[WHERE condition]

[GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];E.g: SELECT deptno, SUM(sal) FROM empGROUP BY deptno HAVING SUM(sal) > 1500;


33/62

33



E.g: SELECT deptno, job, SUM(sal) FROM emp WHERE deptno < > 10GROUP BY deptno, job HAVING SUM(sal) > 2000 ORDER BY 3;

To Find nth row in a table using Group By Clause:E.g: SELECT * FROM empGROUP BY empno, ename, job, mgr, hiredate, sal, comm, deptno, rownumHAVING rownum = &N;Nesting Group Functions:E.g: SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;

Why should group functions are not allowed in WHERE clause?SELECT Statement Execution Flow:A) Because while executing where clause there is no group function results.

Advance Group By FunctionThe ROLLUP Operator:

The ROLLUP operator delivers aggregates and super-aggregates forexpressions within a GROUP BY statement.

The ROLLUP operator can be used by report writers to extract statistics andsummary information from results sets.

ROLLUP grouping produces a result set containing the regular grouped rowsand subtotal rows.

E.g: SELECT deptno, job, SUM(sal) FROM empWHERE deptno > 10GROUP BY ROLLUP(deptno, job);

The CUBE Operator: The CUBE operator can be applied to all aggregate functions, including

AVG,SUM, MAX, MIN, and COUNT.

It is used to produce results sets that are typically used for cross-tabularreports.

Columns included in the GROUP BY clause are cross-referenced to produce asuperset of groups.

The aggregate function specified in the select list is applied to these groupsto produce summary values for the additional super-aggregate rows.

The CUBE groups the selected rows based on the values of all possible

combinations of expressions in the specification and returns a single row of summary information for each group.

E.g: SELECT deptno, job, SUM(sal) FROM empWHERE deptno > 10


34/62

34



GROUP BY CUBE(deptno, job);

The GROUPING Function:

The GROUPING function can be used with either the CUBE or ROLLUPoperator to help you understand how a summary value has been obtained.

The GROUPING function uses a single column as its argument.

The expr in the GROUPING function must match one of the expressions in theGROUP BY clause.

The function returns a value of 0 or 1.o A value of 0 returned by the GROUPING function, If the expression

has been used to calculate the aggregate value.o

A value of 1 returned by the GROUPING function, If the expressionhas not been used to calculate the aggregate value.

E.g: SELECT deptno, job, SUM(sal), GROUPING(deptno), GROUPING(job)FROM emp WHERE deptno < 30GROUP BY ROLLUP(deptno, job);

Grouping Sets:GROUPING SETS are a further extension of the GROUP BY clause that let youspecify multiple groupings of data. Doing so facilitates efficient aggregation andhence facilitates analysis of data across multiple dimensions.A single SELECTstatement can now be written using GROUPING SETS to specify various groupings(that can also include ROLLUP or CUBE operators), rather than multiple SELECTstatements combined by UNION ALL operators.

E.g: SELECT deptno, job, mgr, AVG(sal)FROM empGROUP BY GROUPING SETS ((deptno, job), (job, mgr));

Composite Columns: A composite column is a collection of columns that are treated as a unit

during the computation of groupings. In general, composite columns are useful in ROLLUP, CUBE, and GROUPING

SETS.You specify the columns in parentheses.

E.g ROLLUP (a, (b, c), d) (b, c) form a composite column & treated as a unit.

E.g: SELECT deptno, job, mgr, SUM(sal)FROM empGROUP BY ROLLUP( deptno, (job, mgr));


35/62

35



Oracle 9i Date Time FunctionOracle 9i, you can include the time zone in your date and time data, and providesupport for fractional seconds.

Date-Time functions:

SYSDATE

SYSTIMESTAMP

CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP

DBTIMEZONE

SESSIONTIMEZONE

EXTRACT

Altering the Time Zone and Date Format:ALTER SESSION SET TIME_ZONE = '-5:0';ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SYSDATE:• SYSDATE returns the current date and time. The function requires no arguments.

• The datatype of the returned value is DATE. It won‟t return the time value.• Using TO_CHAR function you can display the time and date values.• In distributed SQL statements, this function returns the date and time on yourlocal database.E.g: SELECT SYSDATE, TO_CHAR( SYSDATE, 'MM-DD-YYYY HH24:MI:SS' )FROM DUAL;

SYSTIMESTAMP:SYSTIMESTAMP returns the system date, including fractional seconds and timezone of the system on which the database resides.The return type is TIMESTAMP WITH TIME ZONE.E.g: SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP Explicitly specify fractional seconds:E.g: SELECT TO_CHAR(SYSTIMESTAMP, 'SSSS.FF') FROM DUAL;

Output:TO_CHAR(SYSTIM2525.000001


36/62

36



CURRENT_DATE:• The CURRENT_DATE returns the current date in the session‟s time zone. • CURRENT_DATE is sensitive to the session time zone.• The TIME_ZONE parameter specifies the default local time zone displacementfor the current SQL session.The TIME_ZONE parameter is set as follows:TIME_ZONE = '[+ | -] hh:mm'E.g: SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;Output:SESSIONTIMEZONE CURRENT_D+05:30 11-NOV-06

CURRENT_TIMESTAMP:The CURRENT_TIMESTAMP function returns the current date and time in the

session time zone, as a value of the data type TIMESTAMP WITH TIME ZONE.The syntax of the CURRENT_TIMESTAMP function is:CURRENT_TIMESTAMP (precision)Where, precision is an optional argument that specifies the fractional secondprecision of the time value returned. If you omit precision, the default is 6.Otherwise it Accepts values are 0 to 9.E.g: SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;Output:SESSIONTIMEZONE CURRENT_TIMESTAMP+05:30 11-NOV-06 02.04.30.000000 AM +05:30

LOCALTIMESTAMP:The LOCALTIMESTAMP function returns the current date and time in thesession time zone in a value of data type TIMESTAMP.The TIMESTAMP WITH TIME ZONE data type has the following format:TIMESTAMP [ (fractional_seconds_precision) ] WITH TIME ZONEThe syntax of the LOCAL_TIMESTAMP function is:LOCAL_TIMESTAMP (TIMESTAMP_precision)E.g: SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;Output:CURRENT_TIMESTAMP LOCALTIMESTAMP11-NOV-06 02.06.20.000000 AM +05:30 11-NOV-06 02.06.20.000000 AM

Difference between LOCALTIMESTAMP and CURRENT_TIMESTAMP isLOCALTIMESTAMP returns a TIMESTAMP value,CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.


37/62

37



DBTIMEZONE:• The DBTIMEZONE function returns the value of the database time zone.• The default database time zone is the same as the operating system's time zone.• You can set the database's default time zone by specifying the SET TIME_ZONEclause of the CREATE DATABASE statement.E.g: SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;Output: see the output in session time zone.SESSIONTIMEZONE:The SESSIONTIMEZONE returns the value of the current session‟s time zone. E.g: SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;

DBTIME SESSIONTIMEZONE-05:00 +05:30

EXTRACT:

The EXTRACT expression extracts and returns the value of a specified datetimefield from a datetime or interval value expression.Syntax:EXTRACT ( [YEAR] [MONTH] [DAY] [HOUR] [MINUTE] [SECOND][TIMEZONE_HOUR] [TIMEZONE_MINUTE] [TIMEZONE_REGION][TIMEZONE_ABBR]FROM [datetime_value_expression] [interval_value_expression] )E.g: SELECT EXTRACT (YEAR FROM SYSDATE),EXTRACT (MONTH FROM SYSDATE),EXTRACT (DAY FROM SYSDATE) FROM DUAL;Output:(YEARFROMSYSDATE) (MONTHFROMSYSDATE) (DAYFROMSYSDATE)

The SET Operators

The SET operators combine the results of two or more component queries into oneresult. Queries containing SET operators are called compound queries.

Note: In the slide, the light color (gray) in the diagram represents the query result.Operator ReturnsUNION All distinct rows selected by either queryUNION ALL All rows selected by either query, including all duplicates

INTERSECT All distinct rows selected by both queriesMINUS All distinct rows that are selected by the first SELECT statement and thatare not selected in the second SELECT statement


38/62

38



Guidelines:

• All SET operators have equal precedence.• If multiple SET operators used, the Oracle evaluates them from left (top) to right(bottom)

• You should use parentheses to specify the order of evaluation explicitly.

The UNION SET Operator:The UNION operator returns all rows selected by either query. Use the UNIONoperator to return all rows from multiple tables and eliminate any duplicate rows.

Guidelines:

The number of columns and the data types of the columns being selected

must be identical in all the SELECT statements used in the query. The namesof the columns need not be identical.

NULL values are not ignored during duplicate checking.

The IN operator has a higher precedence than the UNION operator. By default, the output is sorted in ascending order of the first column of the

SELECT clause.

E.g: SELECT job FROM emp WHERE deptno = 20UNIONSELECT job FROM emp WHERE deptno = 30;The UNION ALL Operator:Use the UNION ALL operator to return all rows from multiple queries.

Guidelines:

Unlike UNION, duplicate rows are not eliminated and the output is not sortedby default.

The DISTINCT keyword cannot be used.

Note: With the exception of the above, the guidelines for UNION and UNION ALLarethe same.

E.g: SELECT job FROM emp WHERE deptno = 20UNION ALLSELECT job FROM emp WHERE deptno = 30;


39/62

39



The INTERSECT Operator:

Use the INTERSECT operator to return all rows common to multiple queries.

Guidelines:

The number of columns and the data types of the columns being selected bytheSELECT statements in the queries must be identical in all theSELECTstatements used in the query. The names of the columns need not beidentical.

Reversing the order of the intersected tables does not alter the result.

INTERSECT does not ignore NULL values.

E.g: SELECT job FROM emp WHERE deptno = 20INTERSECT

SELECT job FROM emp WHERE deptno = 30;

The MINUS Operator:

Use the MINUS operator to return rows returned by the first query that are notpresent in the second query (the 1st SELECT query MINUS the 2nd SELECT query).

Guidelines:

The number of columns and the data types of the columns being selected bythe SELECT statements in the queries must be identical in all the SELECT

statements used in the query. The names of the columns need not beidentical.

All of the columns in the WHERE clause must be in the SELECT clause for theMINUS operator to work.

E.g: SELECT job FROM emp WHERE deptno = 20MINUSSELECT job FROM emp WHERE deptno = 30;

The Oracle Server and SET Operators:

Duplicate rows are automatically eliminated except in UNION ALL. Column names from the first query appear in the result.

The output is sorted in ascending order by default except in UNION ALL.


40/62

40



Restrictions on set operators:

The set operators are not valid on columns of type BLOB, CLOB,BFILE,VARRAY, or Nested Table.

The UNION, INTERSECT, and MINUS operators are not valid on LONGcolumns.

To reference a column, you must use an alias to name the column.

You cannot specify the order by clause in the sub-query of these operators. You cannot use these operators in SELECT statements containing TABLE

collection expressions.

Integrity Constraints

Constraints are enforced on data being stored in a table, are calledConstraints.

Constraints super control the data being entered into a table for permanentstorage.

Constraints are preferred Rules applied on table columns while creating orafter creation

These are automatically activated whenever DML statement is performed ona table

Provides high security

Integrity Constraints are three types1.Entity Integraty: - check for uniqueness of data

ex- primary key, Unique2. Domain Constraint : - Check for conditional rules

ex:- Check, Not null3. Referential Constraint : Used bind relation between tables

NOT NULL: Prevent a column from accepting NULL valuesUNIQUE : Ensures uniqueness of the values in a columnPRIMARY KEY : Same as UNIQUE, but only one column per table is allowedCHECK: Controls the value of a column(s) being insertedDEFAULT: Assigns a default value for the column(s), at the time of insertion when

no value is given for that columnREFERENCES: Assigns a Foreign Key constraint to maintain “Referential Integrity”ON DELETE CASCADE: Using this option whenever a parent row is deleted then allthe corresponding child rows are deleted from the details Table. This option isalways used with foreing key.


41/62

41



ExampleCreate a table using Primary Key and Not null constraints

create table employee(empno number(4),ename varchar2(30) not null,Job varchar2(30) unique ,deptno number(2) not null,constraint Emp_pk_id primary key (empno)) ;

- For foreign key , create the master table first and after that child table.

create table department

(deptno number(2) primary key ,dname varchar2(30) not null,location varchar2(30)) ;

- Creating a table using foreign key, primary key, unique , check and defaultconstraint

create table employee(empno number(4) primary key,ename varchar2(30) not null,Job varchar2(30) unique ,sal number(5,2) check (sal > 0) ,DOJ date default sysdate,deptno number(2) ,constraint EmpNO_fk foreign key (deptno) references department(deptno) ondelete cascade) ;

Note: If a user is not giving the constraint name, Oracle automatically create aconstraint named as “SYS_C0 (some number)”

Dropping the Primary key constraint

SQL> alter table employee drop primary key ;oralter table employee drop constraint constraintname;alter table employee add constraint Dept_pk primary key (deptno) ;


42/62

42



To see details about the infomration of constraints

SQL> desc user_constraintsSQL> select constraint_name,constraint_type from user_constraints wheretable_Name=‟TABLENAME‟ ;

Displaying Data from Multiple Tables (Joins)• Sometimes we have to select data from two tables to make our result complete.We have to perform a join.

• The relationships between multiple tables and draws information from them interms of these relationships all using a single statement.

• We specify the Join condition in WHERE Clause.Cartesian Products:

When a join condition is invalid or omitted completely, the result is a Cartesianproduct, in which all combinations of rows are displayed. All rows in the first tableare

joined to all rows in the second table.E.g: SELECT ename, job, sal, dnameFROM emp, deptTo avoid a Cartesian product, always include a valid join condition in a WHEREclause.

Defining Joins• When data from more than one table in the database is required, a join conditionis used.• Rows in one table can be joined to rows in another table according to commonvalues existing in corresponding columns, that is, usually primary and foreign keycolumns.• If a row does not satisfy a join condition, the row will not appear in the queryresult.• If two tables have a column name in common, you must qualify all references tothese columns throughout the query with its table names to avoid ambiguity.

Joins :

INNER JOIN

NATURAL JOIN

CROSS JOIN LEFT OUTER JOIN

RIGHT OUTER JOIN FULL OUTER JOIN SELF JOIN


43/62

43



inner join :When we join two tables or datasets together on an equality (i.e. column or set ofcolumns) we are performing an inner join. The ANSI method for joining EMP andDEPT is as follows.

Example1 :SELECT d.dname

,d.loc,e.ename,e.job

FROM dept d,emp e

WHERE d.deptno = e.deptno;Example2 :

SELECT d.dname,d.loc,e.ename,e.job

FROM dept d INNER JOIN emp e USING(deptno);

SELECT d.dname,d.loc,e.ename,e.job

FROM dept d INNER JOIN emp e ON(e.deptno=d.deptno)

Example3 : SELECT COUNT(deptno) FROM dept d INNER JOIN emp e USING(deptno);

Example3 : SELECT d.dname


FROM dept d INNER JOIN emp e ON(d.deptno = e.deptno);

Example4 : SELECT d.dname


FROM dept d INNER JOIN emp e ON(d.deptno = e.deptno) WHERE d.loc = 'DALLAS';


44/62

44



natural joinA natural join will join two datasets on all matching column names, regardless ofwhether the columns are actually related in anything other than name. Forexample, the EMP and DEPT tables share one common column name and a natural

join between the two tables would be correct in this scenario.The following example converts our INNER JOIN from previous examples to aNATURAL JOIN.Example1 : SELECT d.dname


FROM dept d NATURAL JOIN emp e;

Cross join:A join without having any condition is known as cross join, in cross join every rowin first table is joins with every row in second table. Cross join is nothing butcortizion product.Example1:select *from emp,dept

Outer join:Outer join produces the results, which contains matched rows and unmatched rows.Outer join is further classified as three types.They are

left outer join

right outer join

full outer join.

Left outer join:Left outer join produces the results, which contains all the rows from left table andmatched rows from right table.Example1 :SELECT d.deptno

,d.dname,d.loc,e.ename

,e.job FROM dept d

,emp e WHERE d.deptno = e.deptno(+);

Example2 :


45/62

45



SELECT deptno,d.dname,d.loc,e.ename,e.job

FROM dept d LEFT OUTER JOIN emp e USING(deptno);In traditional Oracle syntax, outer joins are indicated by (+) and this cansometimes cause issues when attempting to outer join multiple tables or includegexpressions in join predicates. Oracle outer joins have no concept of direction,whereas ANSI-compliant outer joins do. In the following example, we will outer joinDEPT to EMP using the ANSI LEFT OUTER JOIN. The way to interpret this is to readthe tables in the FROM clause from left to right. The left-hand table is the supersetand the table on the right is the potentially deficient set.

Right outer join:Right outer join produces the resultset, which contains all the rows from right tableand matched rows from left table.Example1 :SELECT d.deptno

,d.dname,d.loc,e.ename,e.job

FROM dept d,emp e

WHERE d.deptno(+) = e.deptnoExample2 :SELECT deptno

,d.dname,d.loc

,e.ename,e.job

FROM emp e RIGHT OUTER JOIN dept d USING(deptno); As its name suggests, a right outer join is an outer join where datasets are drivenfrom right to left (i.e. related to the ordering in the query). There are a fewoccasions where we might want to use RIGHT OUTER JOIN, but generally, there is

nothing to be gained from switching the ordering of the tables in this way.Everything described in the left outer join section of this article applies equally toright outer joins so we will not spend too much time on these. The followingexample simply shows how to use this type of join. Again, the OUTER keyword isoptional.


46/62

46



Full outer join:Full outer join produces the result set, which contains all the rows from left tableand all the rows from right table.Example1:SELECT deptno

,d.dname,d.loc,e.ename,e.job

FROM emp e FULL OUTER JOIN dept d USING(deptno); Self join:A self join is a join in which a table is joined with itself. For example, when yourequire details about an employee and his manager (also an employee).

Example1: SELECT e1.ename || ' works for ' || e2.ename "Employees and their Managers" FROM emp e1

,emp e2WHERE e1.mgr = e2.empno;

Example2: SELECT e1.ename || ' works for ' || e2.ename "Employees and their Managers" FROM emp e1 JOIN emp e2 ON(e1.mgr = e2.empno);

Outer Join Restrictions:Oracle Corporation recommends that you use the ANSI OUTER JOIN syntaxrather than the Oracle join operator.Outer join queries that use the Oracle joinoperator (+) are subject to the following rules and restrictions, which do not applyto the ANSI syntax:

You cannot specify the (+) operator in a query block that also contains ANSI JOIN syntax.

If A and B are joined by multiple join conditions, you must use the (+)operator in all of these conditions. If you do not, Oracle will return only therows resulting from a simple join, but without a warning or error to adviseyou that you do not have the results of an outer join.

A condition containing the (+) operator cannot be combined with another

condition using the OR logical operator. A condition cannot use the IN comparison condition to compare a column

marked with the (+) operator with an expression. A condition cannot compare any column marked with the (+) operator with a

sub-query.


47/62

47



Subqueries:

Sub queries can be used to answer queries such as "who has a salary morethan Tom's". For such query, two queries have to be executed: the first query findsTom's salary and the second finds those whose salary is greater than Tom's. Subquery is an approach provides the capability of embedding the first query into theother: Oracle executes the sub query first, making the result of the sub queryavailable to the main query and then executing the main query.The syntax of sub query isSELECT FROM WHERE expression operator

( SELECT FROM

WHERE )

For example, the following statement answers the described query above.

Note that: a subquery must be enclosed in the parenthesis.

a subquery must be put in the right hand of the comparison operator, and a subquery cannot contain a ORDER-BY clause. a query can contain more than one sub-queries.

three types subqueries: single-row subquery, where the subquery returns only one row.

multiple-row subquery, where the subquery returns multiple rows,.and multiple column subquery, where the subquery returns multiple columns.

Single-Row Subquery:Single-row subqueries can only be used with single-row comparison operators, andmultiple-row subqueries can be used only with multiple-row operators. They are tobe described separately in the following.The operators that can be used with single-row subqueires are =, >, >=,


48/62

48



SELECT deptno,MIN(sal)

FROM empGROUP BY deptnoHAVING MIN(sal) > (SELECT MIN(sal)

FROM empWHERE deptno = 5);

SELECT empno,ename,job ,sal,deptno

FROM empWHERE deptno IN(SELECT deptno

FROM deptWHERE loc = 'CHICAGO');

Multiple-Row Subquery:Note the following statement is illegal, because the operator = cannot be used withsubquery returns multiple rows.Some operators that can be used with multipe-row subqueries are:

1. IN, equal to any member in the list,2. ANY, compare values to each value returned by the subquery.3. ALL, compare values to each value returned by the subquery.

Example:SELECT ename,salFROM empWHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno) Operators:

ALL


49/62

49



SELECT * FROM emp WHERE sal >ANY (SELECT MIN(sal) FROM emp GROUP BY deptno) >ALL : Maximum Value of Subquery(1300)SELECT * FROM emp WHERE sal >ALL (SELECT MIN(sal) FROM emp GROUP BY deptno)

Multiple-Column Subquery:In multiple-column subqueries, rows in the subquery results are evaluated in themain query in pair-wise comparison. That is, column-to-column comparison androw-to-row comparison.Note that you can put a subquery in the FROM clause in the main query.For example, the following statement finds all employees in each department wheretheir salary is above the average.

Example :

SELECT a.ename,a.sal,a.deptno,b.sal_avg

FROM emp a ,(SELECT deptno

,AVG(sal) sal_avgFROM emp

GROUP BY deptno) bWHERE a.deptno = b.deptnoAND a.sal > b.sal_avg;

Correlated Oracle subquery:A correlated Oracle subquery is evaluated once FOR EACH ROW as opposed to anormal subquery which is evaluated only once for each table.

You can reference the outer query inside the correlated subquery using an aliaswhich makes it so handy to use.

Let's select all employees whose salary is less than the average of all theemployees' salaries in the same department.

SELECT ename,sal,deptno

FROM emp a WHERE a.sal


50/62

50



FROM emp bWHERE a.deptno = b.deptno);

Using a correlated subquery in an update:

UPDATE emp a SET sal = (SELECT AVG(sal)

FROM emp bWHERE a.deptno = b.deptno)

WHERE sal


51/62

51



Types of Views: Simple View

Complex View

Inline view

Materialized View

Simple View:

Simple view derives data from only one table and simple view contain no functionsor group of data simple view always allow DML operation through the view.Example1:CREATE VIEW myviewAS

SELECT empno employee_no

,ename NAME ,sal salary,job job

FROM empWHERE deptno = 20;

Example2:CREATE VIEW myview1(emloyee_no

,NAME ,salary,job )

AS SELECT empno

,ename,sal,job

FROM empWHERE deptno = 20;

Complex View:Complex view derives data from many table and complex view contain function orgroup of data and Complex view does not always allow.Complex views can be constructed on more than one base table. In particular,

complex views can contain: join conditions

group by clause order by clause


52/62

52



One cannot perform DML operations against complex views directly. To enable DMLoperations on complex views one needs to write INSTEAD OF triggers to tell Oraclehow the changes relate to the base table(s).Example1:CREATE VIEW v3AS

SELECT e.*FROM emp e

,dept dWHERE e.deptno = d.deptno

Inline View:An inline view is a SELECT statement in the FROM-clause of another SELECTstatement. In-line views are commonly used simplify complex queries by removing

join operations and perform several separate queries into a single query.Example1 :SELECT *FROM (SELECT deptno

,COUNT(*) emp_countFROM emp

GROUP BY deptno) emp,dept

WHERE dept.deptno = emp.deptno;Example2:SELECT a.ename

,a.sal,a.deptno,b.maxsal

FROM emp a ,(SELECT deptno

,MAX(sal) maxsalFROM emp

GROUP BY deptno) bWHERE a.deptno = b.deptnoAND a.sal = b.maxsal;

Materialized View:

A materialized view is a database object that contains the results of a query. TheFROM clause of the query can name tables, views, and other materializedviews.There are three steps involved in creating a materialized view

Grant the necessary privileges Create the materialized view log


53/62

53



Create the materialized view itself

Privilege

grant create materialized view to scott;

grant query rewrite to scott;Note: you must have privileges on the underlying tables

Create table (if one does notexists)

create table dept (dept_id number primary key,description varchar2(50));

Create Materialized view log create materialized view log on scott.dept;

Create Materialized view

create materialized view m_deptbuild immediate

refresh fast on commitenable query rewriteas

select * from dept;build immediate - populate the materialized view rightawayrefresh fast on commit - use the fast refresh method usingthe logs create above

enable query rewrite - Oracle CBO will rewrite queries touse the new materialized view.

Refresh a materialized view

exec dbms_mview.refresh('m_dept','F');F = Fast refreshC = Complete refresh? = Force refresh

A = Always refresh

Determine Materialized viewsize

exec dbms_mview.refresh.estimate_mview_size (stmt_id IN VARCHAR2,select_clause IN VARCHAR2,num_rows OUT NUMBER,

num_bytes OUT NUMBER);

Removing Materialized Views drop materialized view test_mview;

Using the WITH CHECK OPTION clause:

CREATE OR REPLACE VIEW myviewAS SELECT *FROM emp

WHERE deptno = 20


54/62

54



WITH CHECK OPTION CONSTRAINT myview_ck;

CREATE VIEW d20AS

SELECT ename,sal,deptno

FROM empWHERE deptno = 20

WITH CHECK OPTION;

UPDATE d20 SET deptno = 10

Read-only views:

CREATE VIEW clerk(id_number,person,department,POSITION)

AS SELECT empno

,ename,deptno,job

FROM empWHERE job = 'CLERK'

WITH READ ONLY ;

Rules for performing DML Operations on a view:

You can perform DML operations on Simple views.

You cannot remove a row if the view contains the following:o Group functions such as SUM, MIN, MAX, AVG, … o A GROUP BY clause.o The DISTINCT keyword.

You cannot modify data in a view if it contains:o

Group functions such as SUM, MIN, MAX, AVG, … o A GROUP BY clause.o

The DISTINCT keyword.o Columns defined by expressions (i.e, SAL * 12).o The ROWNUM pseudo column.

You cannot add data if:o The view contains any of the conditions mentioned above.


55/62

55



o There are NOT NULL columns in the base tables that are not selectedby the view.

Synonyms:

Synonyms are aliases for objects within the database, they are used to make lifeeasier for users, they hide the objects identity and can beeither public or private. public are accessible to all users, and private synonyms arepart on an individual users schema, so the user has to grant privilege right to otherusers. Synonyms can be created for

tables views (including materialized views)

stored code (packages and procedures)They are used to allow users access to objects that lie outside their own schema.There are two major uses of synonyms

Object transparency - keep original names from the users Location transparency - hide the location of the object as it could be on

another database not the local one.

Creating publiccreate public synonym employees for test.employees;Note: any user can use the synonym above

Creating private create synonym addresses for hr.locations;

Removing drop synonym addresses;

Useful Views

DBA_SYNONYMS describes all synonyms in the database

DBA_CATALOG lists all indexes, tables, views, clusters, synonyms, andsequences in the database

Sequences Oracle has a automatic sequence generator that can produce a unique set ofnumbers, normally the numbers are used for primary keys. It is possible to cachethe sequence numbers making the numbers ready and available in memory whichimproves performance, however if the system was to crash those sequencesnumbers are lost forever so be careful if the application requires no loss of

sequence numbers.There are a number of options that can be used when creating a sequence

Creatingcreate sequence employee_id_seqstart with 1000


56/62

56



increment by 1nomaxvalue

nocycle;start with - you can choose any number you like to start withincrement by - you can increment by any numbernomaxvalue - just keep on goingnocycle - you can recycle the list

Removing drop sequence employee_id_seq;

caching(default 20)

alter sequence employee_id_seq cache 100;Note: remember you will lose the cache values during a systemcrash.

Using

select employee_id_seq.nextval from dual;select employee_id_seq.currval from dual;

Note: If using "currval" in you get error message “is not yetdefined in this session” must use nextval first.

Oracle Indexes:Oracle indexes provides faster access to table rows by storing sorted values

in specific columns and using those sorted values to easily lookup the associatedtable rows. This means that you can lookup data without having to look at morethan a small fraction of the total rows within the table, they are completelyoptional.The trade off is that you get faster retrieval but inserting data is slower as the indexneeds to be updated, this slower inserting is bad news for OLTP type of databases

but in a data warehouse type of database where inserting is at a minimal indexescan be used heavily.Index Types

Unique/non-Unique

Based on unique column, something like nationalinsurance number. It is better to use uniqueconstraints on a tables columns which means oraclewill create a unique index on those columns.

Primary/Secondary

Primary indexes are unique indexes that must alwayshave a value, they cannot be NULL. Secondary indexesare other indexes in the same table that may not beunique.

Composite(concatenated)

Indexes that contain two or more columns from thesame table, they are useful for enforcing uniqueness ina tables column where there's no single column thatcan uniquely identify a row.


57/62

57



Index Guidelines: Here are some guidelines from creating efficient indexes:

Index if you need to access no more than 10-15% of the data in the table. Afull table scan (read all the table) is better if you intend to retrieve a highpercentage of the table data, this is because a index read requires two reads

Do not use indexes on small tables, a full table scan would be fine. Create primary keys for all tables as a index will be created by default. Index the columns that are involved in multi-table join operations Index columns that are used frequently in where clauses. Index columns that are involved in order by, group by,

union and distinct operations. Columns that have long character strings are bad for indexing

Columns that are frequently update are bad for indexing Choose tables where few rows have similar values

Keep the number of indexes small, to many will cause performance problemson inserting data.

Index size estimation: Just like oracle tables you can estimate the size of an index using the DBMS_SPACEpackage

estimating

set serveroutput ondeclarel_index_ddl varchar2(1000);l_used_bytes number;l_allocated_bytes number;begin

dbms_space.create_index_cost (ddl=>'create index persons_idx on test01.emp(emp_id)',used_bytes=>l_used_bytes,alloc_bytes=>l_allocated_bytes);dbms_output.put_line ('used = ' || l_used_bytes || 'bytes'|| ' allocated = ' || l_allocated_bytes || 'bytes');end;

/

Note:used - shows the number of bytes that the index data actually representsallocated - show the number of bytes the index will take up in thetablespace when you actual create it


58/62

58



Oracle Index schemes: B*Tree Indexes - These are the common indexes in Oracle. They are similar

construct to a binary tree, they provide fast access by key, to an individualrow or range of rows, normally requiring very few reads to find the correctrow. The B*Tree index has several subtypes

o Index Organized Tables - A table stored in a B*Tree structure(see Index organized Tables)

o B*Tree Cluster Indexes - They are used to index the cluster keys(see Cluster Indexes)

o Reverse Key Indexes - The bytes in the key are reversed. This is usedto stop sequence keys being on the same block like 999001, 999002,999003 would be reversed to 100999, 200999, 300999 thus thesewould be located on different blocks.

o Descending Indexes - They allow data to be sorted from big to small

(descending) instead of small to big (ascending). Bitmap Indexes - With a bitmap index , a single index entry uses a bitmap to

point to many rows simultaneously, they are used with low data that ismostly read-only. They should not be use

sql study material

Documents