ORACLE INTRODUCTIN

ORACLE INTRODUCTION

Before database was invented, File system is used to store data in computers. Because of the disadvantages like data redundancy, atomicity, consistency and security in file system, Database Management System (DBMS) was introduced. Collection of related data is called as Database. Collection of inter-related data and a set of programs to access those data is called as Database Management Sysytem (DBMS). Oracle is a Database Management System Software.

After DBMS came in to practice, several models are proposed in DBMS with different types of storage methods for the data within the database. Among them Relational Database Model is the widely used data model. Relational Database model was proposed by E.F.Codd. In Relational Database Model, Data is stored in the form of relations or tables. A table is comprised of rows and columns. A row is also called as a Tuple and a column is called as an Attribute. Relational Database Model was developed based on the concepts of Relational Algebra and Relational Calculus.

Another development in DBMS is introducing Object Oriented Programming (OOP) concepts in DBMS. By this concept, new database models called Object Oriented Database Models were introduced. A new model called Object Relational Database Model was developed by incorporating OOPS concepts in relational database model.

Upto Version 7.3, Oracle is Relational Database Management System Software. But from version 8.0 onwards, Oracle is Object Relational Database Management System Software (ORDBMS). After version 8.0, Oracle version 8i was released. In this version, Oracle has provided the features by using which we can interact with the Internet. After Version 8i, another version 9i was released. Most of the new features in 9i are towards the Architecture and DBA related and some new features are added towards application development side.

Oracle Provides several tools to access the database. Among them the tools that are covered in this book are SQL, PL/SQL, FORMS and REPORTS. Forms and Reports are combinedly called as Developer/2000 or simply D2k. New version of D2k is 6i and is released along with database release 8i.

SQL Introduction and Data Types

SQL ( Structured Query Language )

SQL is a common language to interact with the database in any RDBMS software.
IBM Corporation first developed SQL. They have developed SQL as a part of
System-R project. After recognizing the ease and flexibility of this part, they have developed it as a separate language and named it as SEQUEL (Structured English Query Language).
Oracle Corporation first commercially released SEQUEL by changing its name to SQL.

Features Of SQL

SQL is a 4th generation (4 GL) or Non procedural language.
Every statement in SQL must end with a semicolon.
SQL is not a case sensitive language.
In SQL at a time a single SQL statement is stored in buffer. To re-execute the statement stored in buffer we have to use ‘/’.
In SQL a single SQL statement can be broken into more than one line without using any continuation character.

Datatypes In SQL

To represent data in database we have to create tables. During the creation of a table we have to specify the columns to be included in the table and the type of values that are going to be stored in those columns. The data types available in SQL are as follows.

Number : This data type is used to store numeric values in a column. For number datatype maximum allowed digits is specified in parantheses. For example, if we specify the datatype as number(5) then we can store –99,999 to +99,999 in that column.

Char : This data type is used to store fixed length character data. Max. Allowed size for Char is 1000 bytes.

Varchar2 : This data type is used to store variable length character data. Max. Allowed Size for varchar2 is 2000 bytes. Difference between char and varchar2 is if we specify the datattype as char(10) and stored the string “Oracle” then even the given string contains 6 characters it occupies 10 bytes in memory by placing blank spaces towards the end. if we specify the datatype as varchar2(10) then it occupies only 6 bytes even max. size is 10.
Long : This data type is used to store variable length character data up to 2 GB.

Raw : This data type is used to store binary data up to 1000 bytes. Binary data means pictures and sounds.

Long Raw : This data type is used to store binary data up to 2 GB.

Date :
This data type is used to store dates in a column. Default Date format in Oracle is DD – MON – YY (Ex: 12-AUG-02).

CONSTRAINTS

Constraints In O R A C L E


Constraints are nothing but conditions given on a column to maintain data integrity within the database. A constraint clause can constrain a single column or group of columns in a table. The point of these constraints is to get oracle to do most of the work in maintaining the integrity of your database. The more constraints you add to a table definition, the less work you have to do in applications to maintain the data. On the other hand, the more constraints there are in a table, the longer it takes to update the data.
There are two ways to specify constraints. As part of the column definition (a column constraint) or at the end of the create table statement (a table constraint). Clauses that constrain several columns must be table constraints. Constraints in Oracle and their purpose are as follows.

1. Not Null : This constraint is used to restrict the user from inserting null values into a column.

2. Unique : This constraint is used to restrict the user from inserting duplicate values to a column.

3. Primary Key : This constraint is used to restrict the user from inserting duplicate values or null values into a column. Another purpose of Primary Key is a column to be referenced by a Foreign Key must be a Primary Key. Oracle allows only one primary key for the table.

4. Foreign Key : This constraint is used to create relationship between two tables. We create relationship between two tables by giving reference to a primary key column of one table to a column in second table, which is called foreign key column. Foreign Key constraint allows either null values or the values present in a Primary Key column to which it refers.
When a relationship is created between two tables then the table that contains Primary Key is called as Master or parent Table and the table that contains Foreign Key is called as Detailed or child Table and the Primary Key and Foreign Key relationship is called as Master Detail Relationship.
A Foreign Key constraint is also called as Referential Integrity constraint. If a Foreign Key refers to a Primary Key present in the same table then that foreign key is called as Self Referential Key.
The clause on delete cascade added to the references clause tells oracle to delete the dependent row when you delete the corresponding row in the parent table. This action automatically maintaince referential integrity.

5. Check : Many columns must have values that are within a certain range or that satisfy certain conditions. A column level check constraint can’t refer to values in other rows. It can’t use the pseudo columns sysdate, uid, user, userenv, currval, nextval, level or rownum. You can use the table constraint form to refer to multiple columns in a check constraint.

CREATING TABLE, TABLE LEVEL CONSTRAINT

Creating A Table


To create your own table in oracle, “Create Table” command is used. Create Table command has the following syntax.



Create Table TabName
(col1 datatype [constraint constname] [constspec],
col2 datatype [constraint constname] [constspec],
.
.
.
coln datatype [constraint constname] [constspec]
);

Here Col1, Col2, … , Coln are the column names to be included in the table. Datatype is any valid datatype of SQL. Constraint is keyword. Constname is the name for the constraint specifying on that column. Constspec is the constraint to be given on that column. Specifying constraint name is optional. When you want to give a name to the constraint, then only you have to use the key word constraint. If we don’t specify a name to the constraint, then oracle will automatically give a name to that constraint. You can get the constraint names from data dictionary view user_constraints. Most of oracle’s generated constraint names are of the form SYS_C######. For example, SYS_C000145. Even specifying a constraint on a column is also optional.
These are the basic elements of this command.
The words create table
The name of the table
An opening paranthesis
Column definitions
A closing paranthesis
A SQL terminator

The individual column definitions are separated by commas. There is no comma after the last column definition. The table and column names must start with a letter of the alphabet, but may include letters, numbers, and underscores. Names may be 1 to 30 characters in length, must be unique within the table, and can not be an oracle reserved word.
Example :

Create Table STUDENT
(Sno Number(5) constraint SNO_PK Primary Key,
Sname Varchar2(15) NOT NULL,
Address Varchar2(20) NOT NULL,
Course Varchar2(1) NOT NULL,
Fee Number(7,2)
);

Example :

Create table Marks
(Sno Number(5) constraint SNO_FK references Student(Sno),
M1 Number(3) constraint M1_CHK Check(M1<=100),
M2 Number(3) constraint M2_CHK Check(M2<=100),
M3 Number(3) constraint Mn_CHK Check(M3<=100),
Total Number(5),
Aveg Number(7,2)
);


Table Level Constraints

Constraints can be classified into column level constraints and table level constraints. If constraint was specified immediately after column definition then that constraint is called as column level constraint and if a constraint was specified at the end of the create table statement, then that constraint is called as table level constraint. Table level constraints are generally used when constraint has to be given on more than one column. An example of table level constraint is as follows. In this example, primary key constraint is given on two columns invno and itemno.

Create table Invoice
(Invno number(5) ,
Cname varchar2(20) not null,
Itemno number(5),
Qty number(3) not null,
Uprice number(7,2) not null,
Constraint invitem_pk primary key(invno,itemno)
);

Creating a Table From Another Table

To create a new table from an existing table, the following syntax is used.

Syntax : create table tablename as select statement on the source table;
Example : the following example creates emp1 by selecting the structure and rows of emp table.

Create table Emp1 as select * from emp;

Displaying Structure of The Table

To display the structure of a table i.e. the column names and their data types, describe command is used.

Syntax : DESC TableName

INSERTING DATA INTO TABLE

Using Merge Command
As of oracle9i, you can use the merge command to perform inserts and updates in to a single table in a single command. Based on the conditions you specify, oracle will take the source data i.e. a table, view, or a query and update existing values if the conditions are met. If the conditions are not met, the row will be inserted.

For example, create a duplicate table of emp with name emp1 and make some changes to emp1. now we can merge the changes made to emp1 in to emp by using merge command as follows.


Merge Into Emp EUsing
(Select Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno From Emp1) E2On(E.Empno=E2.Empno)
When Matched Then
Update Set E.Sal=E2.Sal,E.Comm=E2.CommWhen Not Matched Then Insert values(E2.Empno,E2.Ename,E2.Job,E2.Mgr,E2.Hiredate,E2.Sal,E2.Comm,E2.Deptno)


Inserting Data Into A Table


After creating the table we can insert data into that table by using insert statement. Insert statement has following syntaxes.

Syntax 1: Insert into tabname values( val1, val2, val3, . . . , val n );
Example: Insert into Student values( 1, ‘SAI’, ‘Apet’, ‘ORACLE’, 2000);

Syntax 2: Insert into tabname values( &col1, &col2, &col3, . . . , &co n );
Example : Insert into Student values( &Sno, ‘&Sname’, ‘&Address’, &Fee );

Second syntax will be easier for user when he/she has to insert serveral rows in to the table. By using above two syntaxes we must provide values for all columns of the table even columns don’t have ‘Not Null’ constraint. To insert data into a table by excluding some columns, the following syntaxes are used.

Syntax 3: Insert into tablename(Col1, Col2, Col3,. . .,Col n) values ( Val1, Val2, Val3, . . . ,Val n);
Example: Insert into Marks ( Sno, M1, M2, M3 ) values ( 1, 55, 67, 82 );


Syntax 4: Insert into tablename (Col1, Col2, Col3, ..., Col n) values (‘&Col1’, ‘&Col2’, ‘&Col3’, ... , ‘&Col n’);
Example: Insert into Marks (Sno, M1, M2, M3) values (&sno,&m1,&m2,&m3);

Insert With Select

You also can insert information that has been selected from a table.

Syntax : Insert into Tablename select col1, col2, col3, … , col n from tablename;





Using Append Hint

Oracle uses an optimizer to determine the most efficient way to perform each SQL command. For insert statements, oracle tries to insert each new record into an existing block of data already allocated to the table. This execution plan optimizes the use of space required to store the data. However, it may not provide adequate performance for an insert with a select command that inserts multiple rows.you can override the execution plan by using the append hint to improve the performance of large inserts.the append hint will tell the database to find the last block into which the table’s data has ever been inserted. The new records will be inserted starting in the next block following the last previously used block. Since the data is being written into new blocks of the table, there is much less space management work for the database to do during the insert. Therefore, the insert may complete faster when the append hint is used.

You specify the append hint within the insert command. A hint looks like a comment. It starts with /* and ends with */. The only difference is that the starting set of characters includes a + before the name of the hint.

Syntax : insert /* + APPEND */ in to tablename select statement.

Since the new records will not attempt to reuse available space that the table has already used, the space requirements for the table may increase. In general you should use the append hint only when inserting large volumes of data in to tables with little reusable space. The point at which appended records will be inserted is called the table’s high-water mark.

Multitable Inserts

As of oracle 9i, you can perform multiple inserts in a single command. You can perform all of the inserts unconditionally or you can spcify conditions using a when clause to tell oracle how to manage the multiple inserts. If you specify all, then all of the when clauses will be evaluated. Specifying first tells oracle o skip subsequent when clauses after it finds one that is true for the row being evaluated. You can also use an else clause to tell oracle what to do if none of the when clauses evaluates to true.


Syntax : Insert [ALL] / [FIRST]
[When condition]
Into tablename values(val1, val2, …, val n)
[When condition]
Into tablename values(val1, val2, …, val n)
[When condition]
Into tablename values(val1, val2, …, val n)
.
.
.
else
Into tablename values(val1, val2, …, val n)
Select statement;

UPDATE COMMAND

Updating A Table


To make modifications to the data present in a table, update statement is used. Update statement has the following syntax.

Syntax:

Update tablename set Col1=Val1, Col2=Val2, ... ,Col n=Val n [ Where Condition ];

Example 1:
The following update statement changes the name of student with id 3 to ‘NAVEEN’.
Update Student set Sname=’Naveen’ where Sno=3;

Example 2: The following example updates the Marks table by calculating total and average.
Update Marks set Total= (M1 + M2 + M3 ), Aveg = ( M1 + M2 + M3 ) / 3;

If you specify the where clause, then the rows that satisfy the given codition in where clause will only be updated. If you omit the where clause then all rows of the table will be updated.

RENAME, DELETE and TRUNCATE

Renaming a Table

Name of a table can be chaged by using Rename command. Rename command has the following syntax.

Syntax: Rename Oldname to Newname;
Example: The following example changes the name of student table to stu.
Rename Student to Stu;


Deleting a Table

A table can be deleted by using Drop Table command. Drop Table command has the following syntax.

Syntax: Drop Table Tablename;
Example: The following example deletes marks table.
Drop Table Marks;


TRUNCATE

In oracle, the truncate command lets you remove all the rows in the table and reclaim the space for other uses without removing the table definition from the database.

Syntax: Truncate Table Tablename;

Example: The following example deletes all rows in marks table, keeping marks table definition within the database.

Truncate Table Marks;

ALTER COMMAND

Altering A Table Structure


After creating the table we can change the data type of a column or change the size of a column or add a new constraint or delete a constraint or add a new column or delete a column by using Alter Table command. Syntaxes of Alter table command are as follows.

To change the datatype of a column or to change the size of a column or to add not null constraint to a column, the following syntax is used.

Syntax:

Alter table tablename modify
(Col1 Datatype Not Null,
Col2 Datatype Not Null,
………..
Col n Datatype Not Null
);

Example : The following example increases the size of Sname column in Student table to 20 and adds not null constraint to fee column.
Alter table Student modify (Sname varchar2(20), Fee number(7,2) Not Null );

Restrictions:

Changing the datatype of a column is not possible if that column contains data.
Adding not null constraint is not possible if that column contains NULL values.
Decreasing the Size of a column is not possible if that column contains data.


To add a new column to the table, the following syntax is used.

Syntax:
Alter table tabname ADD
(Col1 Datatype Constraint Consname Consspec,
Col2 Datatype Constraint Consname Consspec,
.
.
.
Col n Datatype Constraint Consname Consspec);

Example : The following example adds a new column with name Fname (Father’s name) to Student table.

Alter table Student ADD (Fname Varchar2(10));

Restrictions :

a) Adding a column with primary key or Not Null constraint is not possible if the table contains data.


To add a constraint to a table, the following syntax is used.


Syntax:
Alter table tabname ADD Constraint consname consspec( Colname );

Example 1: The following example adds Primary Key constraint to Sno Column of Student table.
Alter table Student ADD constraint Sno_PK Primary Key (Sno);

Example 2: The following example adds Foreign Key constraint to Sno Column of Marks table.
Alter table Marks ADD constraint Sno_FK Foreign Key (Sno) references Student(Sno);

Restrictions:

Adding any constraint to a column is not possible if that column contains data that violates that constraint.
Adding Not Null constraint is not possible by using this syntax.


To disable and enable constraints, the following syntax is used.
Syntax:
Alter table tabname Disable/Enable constraint consname;

Example: The following example disables the foreign key constraint on Sno column of Marks table.
Alter table Marks Disable constraint Sno_FK;

Restrictions:

Disabling a primary key is not possible if that Primary key is refered by a foreign key. To delete primary key by deleting the foreign key constraint automatically, we have to use cascade constraints clause at the end of the alter table command.
Enabling a constraint is not possible if that column contains data that violates the constraint.

To delete a constraint or a column, the following syntax is used.

Syntax:
Alter table tabname Drop Column/Constraint Col.Name/ConsName;

Example 1: The following example deletes Foreign Key constraint on Sno column of Marks table.
Alter table Marks Drop constraint Sno_FK;

Example 2: The following example deletes Fee column from Student table.
Alter table Student Drop Column Fee;

Example 2: The following example deletes Fee, Fname columns from Student table in a sinle alter table statement.
Alter table Student Drop (Fee, Fname);


Restrictions:

Deleting a Primary key constraint or primary key column is not possible if that Primary key is refered by a Foreign Key. To delete primary key column by deleting the foreign key constraint automatically, we have to use cascade constraints clause at the end of the alter table command.

We can make a column as unused. Making a column unused does not release the space previously used by the column until you drop the unused columns.

Syntax:
Alter table tabname set unused column ColumnName;

Example : The following example sets total column of marks table unused.
Alter table Marks set unused column total;

To delete all unused columns of a table, drop unused columns clause is used as follows.

Example : Alter table Marks drop unused columns;

OPERATORS

O p e r a t o r s


The following operators can be used in the where clause to simplify the conditions.

Between … And
In
Like
Is Null

1) Between … And:

This operator is used to test whether the given value is present in given range of values.

Examples:
Find all employees whose Salary is more than 2000 and less than 4000.
Select * from EMP where Sal between 2000 and 4000;
Find all employees joined after 12 th june 1990, and before 31st December 1990.
Select * from EMP where Hiredate between ‘12-Jun-90’ and ‘31-Dec-90’;
Find all employees who are not joined after 12th June 1990, and before 31st December 1990.
Select * from EMP where Hiredate NOT Between ‘12-Jun-90’ and ‘31-Dec-90’;


2) In :

This operator is used to test whether the given value is present in a given list of values.

Examples:
Find all employees whose salary is either 1200 or 1500 or 1750.
Select * from EMP where Sal IN (1200, 1500, 1750);
Find all employees who are working either as a Manager or a Clerk.
Select * from EMP where Job IN (‘MANAGER’, ‘CLERK’);


3) Like:

One of the most powerful features of SQL is a marvelous pattern-matching operator called LIKE, which is able to search through the rows of a database column for values that look like a pattern you describe. It uses two special characters to denote which kind of matching to do : a percent sign, called wildcard, and an underscore, called position marker.

Examples:
Find all employees whose Name starts with ‘S’.
Select * from EMP where Ename LIKE ‘S%’;
Find all employees whose name ends with ‘E’.
Select * from EMP where Ename LIKE ‘%E’;
Find all employees whose name contains the letter ‘A’.
Select * from EMP where Ename LIKE ‘%A%’ ;
Find all employees whose name contains ‘L’ as second letter.
Select * from EMP where Ename LIKE ‘_L%’ ;
Find all employees whose name doesn’t contain the letter ‘A’.
Select * from EMP where Ename NOT LIKE ‘%A%’;
Find all employees whose name contains five letters.
Select * from EMP where Ename like ‘_ _ _ _ _’; (Underscores without space between them)


4) Is Null:

This operator is used to test for a null value in the given column.


Examples:
Find all employees who are not drawing any commission.
Select * from EMP where Comm IS NULL;
Find all employees who are drawing Commission.
Select * from EMP where Comm IS NOT NULL;

BUILT IN FUNCTIONS IN ORACLE

Built In Functions

As in other programming languages, SQL also provides built in functions. Built in functions in SQL are classified into following categories.

Single row functions.
List Functions.
Group Functions.

1) Single Row Functions: The Built in functions of ORACLE that work on each row of the table separately are called as Single Row Functions. Depending on the data type of the values on which Single Row Functions work, they are classified into following five categories.

Character Functions.
Numeric Functions.
Conversion Functions
Date Functions.
Special Functions.


Character Functions :

Built in Single Row Functions of SQL that work on character type of data are called as Character functions. Character functions in SQL are as follows.


UPPER(String or ColName) : This function converts the given string or values of given column in any case to upper case.

LOWER(String or ColName) : This function converts the given string or values of given column in any case to lower case.

INITCAP(String or ColName) : This function converts the given string or values of given column in any case to title case i.e. converts first character in every word to upper case and remaining characters in the word to lower case.

In general above three functions are used to perform case-insensitive queries against database. For example the following query retrieves all managers from emp table with case insensitivity.

Example: Select * from EMP where UPPER(JOB) = ‘MANAGER’;

LPAD(String or Colname, Len, Char ) : This function pads the given char with in the blank spaces to the left of given string or values of given column by assuming the maximum length of string or column as Len.

Following example pads “*” to the left of employee name. In this example, colname is ename, len is given as 10 and char as “*”. Now if ename is “Scott” then ename is five charcaters and we have given the len as 10. Hence it pads five “*” to the left of ename “scott” and result is “*****Scott”.

Example: Select Lpad(Ename,10,”*”) from Emp;

RPAD(String, Len, Char ) : This function pads the given char with in the blank spaces to the Right of given string or values of given column by assuming the maximum length of string or column as Len.

Following example pads “*” to the right of employee name. In this example, colname is ename, len is given as 10 and char as “*”. Now if ename is “Scott” then ename has five charcaters and we have given the len as 10. Hence it pads five “*” to the right of ename “scott” and result is “Scott*****”.

Example: Select Lpad(Ename,10,”*”) from Emp;

LTRIM(String or ColName,[Char]) : Removes any leading blank spaces i.e. blank spaces on left of given string or values of given column if char is omitted. Otherwise removes the given char from left of given string or values of given column.

RTRIM(String or ColName,[Char]) : Removes any trailing blank spaces i.e. blank spaces on right of given string or values of given column if char is omitted. Otherwise removes the given char from right of given string or values of given column.

LENGTH(String or ColName) : Returns the number of characters in the given string or values of given column seperately.

SUBSTR(String or ColName, M, [N] ) : This function extracts ‘ N ’ number of characters from the given string or values of given column starting from ‘ M ’. If ‘ N ‘ is omitted then all charcters starting from ‘ M ‘ will be retrieved.

Example: Select Substr(‘ORACLE CORPORATION’, 8, 4 ) from Dual;

This example returns “CORP” by extracting 4 characters from the given string “ORACLE CORPORATION” starting from the 8th character. If same query is written without giving value 4, then result will be “CORPORATION”.

INSTR(String or ColName, Substring, M, N ) : Finds Nth occurrence of the given substring with in the given string or values of given column starting from ‘M’.

Example: Select INSTR( ‘ORACLE CORPORATION’, ‘OR’, 1, 2 ) from Dual;

This example returns value 9. Because given substring “OR” is started second time at 9th character starting from first(1) character. If the same query is written as follows





Example: Select INSTR( ‘ORACLE CORPORATION’, ‘OR’, 9, 2 ) from Dual;

Then return value is 12. Because given substring “OR” is started second time at 12th character starting from 9th character. If the given substring is not found in the specified ocuurrece, then this function will return the value 0.

REPLACE(String or ColName, Substring, [RepString] ) : Replaces every occurrence of given substring with in the given string or values of given column with Repstring.

Example: Select REPLACE( ‘ORACLE CORPORATION’, ‘ORA’, ‘X’ ) from Dual;
This example will replace every occurrence of given substring “ORA” in the given string “ORACLE CORPORATION” with given replace string “X” and the result will be “XCLE CORPXTION”. If Replace string is omitted, then the given substring will be removed from the given string or values of given column. If replace string “X” is not given in the above example, then result will be “CLE CORPTION”.

12.) TRANSLATE(String or ColName, substring, RepString ) : Replaces every occurrence of individual characters in the given substring within given string or values of given column with corresponding character in the replace string.

Example: Select Translate( ‘ORACLE CORPORATION’, ‘ORA’, ‘XYZ’ ) from Dual;

This example will replace every occurrence of given characters in the substring “ORA” in the given string “ORACLE CORPORATION” with given charcters in replace string “XYZ”. I.e. Character “O” is replaced with “X”, “R” is replced with “Y” and “A” is replaced with “Z” and the result will be “XYZCLE CXYPXYZTIXN”. If corrosponding charcter in Replace string for a charcater in substring is omitted, then the given charcter in substring will be removed from the given string or values of given column. If replace string “XYZ” is given as “XY” in the above example, then result will be “XYCLE CXYPXYTION”.

CONCAT(String1 or Col1, String2 or Col2) : Concatenates String2 or Col2 at the end of String1 or Col1. Instead of concat function, we can use the Concatenation operator “”(Double Pipe Symbol”).

SOUNDEX(String or ColName) : Returns how the given string or values of given column are pronounced. The following example retrives the employee records, whose name pronounces same as “SKOTT”.

Example: Select * from EMP where SOUNDEX(Ename) = SOUNDEX(‘SKOTT’);

ASCII(Char) : Returns ASCII value of given character.

Example: Select Ascii(‘A’) from Dual; ( Returns 65 )

Chr(N) : Returns ascii character for given ‘N’.

Example: Select Chr(65) from Dual; ( Returns “A” )


ii.) NUMERIC FUNCTIONS :

Built in single row functions of SQL that work on numeric type of data are called as numeric functions. Numeric functions in SQL are as follows.

Sin(n) : Returns Sine value of given ‘ n ‘ in Radians.
ASin(n) : Returns Arc Sine value of given ‘ n ‘ in Radians.
3. SinH(n) : Returns Hyperbolic Sine value of given ‘ n ‘ in Radians.
4. Cos(n) : Returns Co Sine value of given ‘ n ‘ in Radians.
5. ACos(n) : Returns Arc Co Sine value of given ‘ n ‘ in Radians.
6. CosH(n) : Returns Hyperbolic Co Sine value of given ‘ n ‘ in Radians.
7. Tan(n) : Returns Tangent value of given ‘ n ‘ in Radians.
8. Atan(n) : Returns Arc Tangent value of given ‘ n ‘ in Radians.
9. TanH(n) : Returns Hyperbolic Tangent value of ‘ n ‘ in Radians.
10. ABS(n) : Returns absolute value of given ‘ n ’. i.e., without any sign.
Following example returns 8 by removing “-“ sign.

Example : Select Abs(-8) from Dual;

11.Exp(n) : Returns ‘ e ’ value raised to the power of ‘ n ’. ‘ e ‘ is exponentiation and its value is 2.718.
Following example returns (2.718)3

Example : Select Exp(3) from Dual;

12.Ceil(n) : Returns the smallest integer among all the values that are greater than given ‘n‘
In the following example, ‘ n ‘ is given as 4.721. Here integers greater than 4.721 will be 5,6,7, and so on. Hence it returns 5, the smallest integer among all values that are greater than 4.721.

Example: Select CEIL(4.721) from Dual;
13. Floor(n) : Returns the largest integer among all the numbers that are less than ‘ n ‘.
In the following example, ‘ n ‘ is given as 4.721. Here integers less than 4.721 will be 1,2,3, and 4. Hence it returns 4, the largest integer among all values that are less than 4.721.

Example: Select Floor(4.721) from Dual;

14. LN(n) : It returns Natural Logarithmic value of ‘ n ‘.
15. LOG(M,N) : It returns base M logarithmic value of given N.
Following example returns Log2(8) i.e. base 2 logorithmic value of 8.

Example : Select Log(2,8) from Dual;

16. MOD(M,N) : Returns the remainder after integer division of M with N.
Following example returns 2. Integer division of 7 with 5 will result 1 and remainder will be 2.
Example : Select Mod(7,5) from Dual;

17. SQRT(N) : Returns Square root value of given ‘ N ’.
Following example will return 5 as square root of 25 is 5.

Example : Select Sqrt(25) from Dual;

18. POWER(M,N) : Returns the value of ‘ M ’ raised to the power of ‘ N ’ i.e. MN
Following example returns 125 i.e. 53.
Example : Select Power(5,3) from Dual;
19. ROUND(N,P) : Rounds the given ‘ N ‘ value to ‘ P ‘ decimal places.
Following example returns 7.64 by rounding 7.6351 to 2 decimal places. Round function increments the value 7.63 to 7.64. Because immediate next digit of 3 is greater than or equal to 5.

Example : Select ROUND(7.6351, 2 ) from Dual;

20. TRUNC(N,P) : Truncates the given ‘ N ’ value to ‘ P ‘ decimal places.
Following example returns 7.63 by truncating 7.6351 to 2 decimal places. Truncate function Does not increment the value 7.63 to 7.64 even immediate next digit of 3 is greater than or equal to 5.

Example : Select Truncate(7.6351, 2 ) from Dual;

21. SIGN(N) : Returns –1 if the ‘ N’ value is negative, 0 ( Zero ) if the ‘ N ‘ value is Zero and +1 if ‘ N ‘ is positive.



iii) CONVERSION FUNCTIONS :

The Built in single row functions of SQL that are used to convert one type of data to another are called as conversion function. Conversion functions in SQL are as follows.


TO_CHAR(n) : Converts the given numeric value to character format.
Following example converts the given numeric value 14 to character format i.e. “14”

Example : Select To_Char(14) from Dual;

2. TO_NUMBER(Char) : Coverts the numeric value in character format to Numeric format.
Following example converts the given numeric value 14 in charcater format i.e. “14” to numeric format i.e. 14

Example : Select To_Number(‘14’) from Dual;

TO_CHAR(Date, Format) : Converts given Date to Character format by using the specified format.
Following example converts the system date to DD-MONTH-YYYY format. If we assume the system date as ‘5-jan-02’ then result will be ‘05-JANUARY-2002’.
Example : Select To_Char(Sysdate, ‘DD-MONTH-YYYY’) from Dual;
Strings that can be used to specify the format and their purpose are as follows
1. D : Returns Day of week.
2. DD : Returns Day of Month.
3. DDD : Returns Day of Year.
4. DY : Abbrivative form of week.
5. DAY : Complete spelling of week. ( Ex: Sunday, Monday, …)
6. SP : Complete spelling of dates (Fourteen).
7. TH : Places ‘Th’ or ‘St’ or ‘Rd’ at the end of day.(3rd, 2nd , 4th etc)
8. MM : Two digital months. ( 01, 02, 02,…12 )
9. MON : Abbrivative form of Month.
10. RM : Roman numerical month. ( i, ii, iii, iv, v,…, XII )
11. MONTH : Complete Spelling of month. ( January, …, December )
12. Y : Last digit of the year.
13. YY : Last two digits of year.
14. YYY : Last three digits of year.
15. YYYY : 4 Digital year.
16. YEAR : Year spelled out.
17. HH / HH12 : Hour of day in 12-hour format.
18. HH24 : Hour of day in 24-hour format.
19. MI : Minute of year.
20. SS : Second of minute.
21. AM / PM : Represents AM or PM.
22. WW : Returns week of YEAR.

TO_DATE(CharDate, Format) : Converts the given date in character format to Date. This function has two arguments. First argument is the date in character format and second one is the format of date, which we have given, in first argument.
Following example converts the given date in character format ‘Fourteen-August-2002’ to date format i.e. 14-Aug-02.

Example : Select TO_DATE(‘Fourteen-August-2002’, ‘DDSP-MONTH-YYYY’) from Dual;

iv) DATE FUNCTIONS:

Built in single row functions of SQL that work on Date type of data are called as Date functions. Date functions in SQL are as follows.

SYSDATE : Returns current system date.
Example : Select Sysdate From Dual;

ADD_MONTHS(Date, n) : Returns a date after adding ‘n’ number of months to the given date. If ‘n’ value is negative then ‘n’ months are subtracted from the given date.
Following example adds two months to the current system date and returns a date. If the current system date is 13-JAN-2003 then this example will return 13-MAR-2003.

Example : Select Add_Months( Sysdate, 2 ) from Dual;

MONTHS_BETWEEN( Date1, Date2 ) : Return the difference between given two dates in number of months.
Following example returns the difference between sysdate and ‘01-MAR-2003’. If the system date is ‘13-JAN-2003’, then result will be 1.51(Estimated). Actually the difference is 1 month 16 days. Value after decimal point is obtained by using “(16/30)*100” Calculation.

Example : Select MONTHS_BETWEEN(Sysdate, TO_DATE(‘01-MAR-03’)) from Dual;

NEXT_DAY(Date, day) : Returns the date of given day after given date.
Following Example returns the date of ‘Sunday’ after sysdate. If system date is ‘13-JAN-2003’ then result will be ‘19-JAN-2003’.

Example : Select NEXT_DAY(Sysdate, ‘Sunday’) from Dual;

LAST_DAY(Date) : Returns the date of last day of the month in given date.
Following example returns the last day of the month in system date. If system date is ‘13-JAN-2003’, then result will be ‘31-JAN-2003’ i.e. the last day of month in sysdate i.e. January.

Example : Select Last_Day(Sysdate) from Dual;

ROUND(Date, format ) : Rounds the given date to given format.
Following example Rounds the system date to 1 month. When we are rounding to a month, then Depending on the day of month i.e. if day is less than or equal to 15, then first day of current month will be the result. If day is greater than 15, then first day of next month will be returned. If sysdate is ‘13-JAN-2003’, then result will be ‘01-JAN-2003’. Because day is ‘01’. If sysdate is ‘17-JAN-2003’, then result will be ‘01-FEB-2003’. Because day is ‘17’.

Example 1 : Select ROUND(Sysdate, ‘MM’) from Dual;

Following example Rounds the system date to 1 Year. When we are rounding to a year, then Depending on month of the year i.e. if month is less than or equal to 6, then first day of current year will be the result. If month is greater than 6, then first day of next year will be returned. If sysdate is ‘13-MAR-2003’, then result will be ‘01-JAN-2003’. Because month is ‘03’. If sysdate is ‘13-JUL-2003’, then result will be ‘01-JAN-2004’. Because month is ‘7’.

Example 2: Select ROUND(Sysdate, ‘YY’) from Dual;

Following example Rounds the system date to 1 Day. When we are rounding to a Day, then Depending on the time of Day i.e. if Time is less than or equal to 12PM, then time part is rounded to “00:00:00AM” of the same date. If Time is greater than 12PM, then, time part is rounded to “00:00:00AM” of the next day of current date. If sysdate is ‘13-JAN-2003 10:23:22AM’, then result will be ‘13-JAN-2003 00:00:00AM’. Because Time is ’10:23:22AM’. If sysdate is ‘13-JAN-2003 01:24:12PM’, then result will be ‘14-JAN-2003 00:00:00AM’. Because Time is ’01:24:12PM’.

Example 3 : Select To_char(ROUND(Sysdate, ‘DD’),’dd-mon-yy hh12:mi:ssAM’) from Dual;

TRUNC(Date, Format) : Truncates the given date to given format.
Following example truncates the system date to 1 month. Unlike Round function, truncate returns the first day of month in given date even date is either less than or equal to 15 or greater than 15. If system date is ‘13-JAN-2003’, then result will be ‘01-JAN-2003’.

Example 1: Select TRUNC(Sysdate, ‘MM’) from Dual;

Following example truncates the system date to 1 Year. Unlike Round function, truncate returns the first day of year in given date even month is either less than or equal to 6 or greater than 6. If system date is ‘13-JUL-2003’, then result will be ‘01-JAN-2003’.

Example 2: Select TRUNC(Sysdate, ‘YY’) from Dual;

Following example truncates the system date to 1 Day. Unlike Round function, truncate truncates the time part to “00:00:00AM” of the given date even the time is either less than or equl to 12PM or greater than 12PM. If the sysdate is ‘13-JAN-2003 01:24:12” then result will be ‘13-JAN-2003 00:00:00AM’.

Example 3: Select TRUNC(Sysdate, ‘DD’) from Dual;

DATE ARITHMETIC

Date + n : Returns a date after adding ‘n’ no. of days to the given date.
Following example adds 3 days to system date. If system date is ‘14-JAN-2003’, then result wil be ‘17-JAN-2003’.

Example : Select Sysdate+3 from dual;

Date – n : Returns a date after subtracting ‘n’ no. of Days from given date.
Following example subtracts 3 days from system date. If system date is ‘14-JAN-2003’, then result wil be ‘11-JAN-2003’.

Example : Select Sysdate - 3 from dual;

3. DATE1 –DATE2 : Returns the difference between given two dates in no. of days.
Following example calculates the difference between system date and ‘20-JAN-2003’. If system date is ‘14-JAN-2003’, then result will be 6. Result may come in float value i.e. 5.45, when the difference is not exactly the multiple of 24 hours. This function calculates the difference from “00:00:00AM” of ‘14-JAN-2003’ to “00:00:00AM” of ‘21-JAN-2003’(we have given the date as ‘20-JAN-2003’ and this day will be completed at “00:00:00AM” of ‘21-JAN-2003’). When the difference is not exactly the multiple of 24, then this function calculates the decimal value as current hour/24*100.

Example : Select sysdate – to_date(‘20-JAN-2003’) from Dual;







SPECIAL FUNCTIONS

DECODE : Built in single row functions of SQL those works on any type of data are called as Special functions. Special functions in SQL are as follows.

Syntax : DECODE(Expression, Result1, ReturnVal1, Result2, ReturnVal2,… else ReturnVal);
This function is equivalent to if statement in programming languages. If the expression returns a value equivalent to Result1, then function returns ReturnVal1, if the expression returns a value equivalent to Result2, then function returns ReturnVal2 and so on.
Following example displays Employee name and Salary. Value of salary column is displayed as ‘On Target’ if employee’s salary is 1500, ‘Below 1500’ if salary is less than 1500 and actual salary i.e. 2000,3000 and so on if salary is greater than 1500.

Sign function returns –1 if the value passed is negative, 1 if the value assed is positive, and 0 if the value passed is zero. If the result of the expression Sign(sal-1500) is –1, then Decode Function will return ‘Below 1500’. If the result is zero, then decode function will return ‘on target’. If the expression sign(sal-1500) does not return either –1 or zero, the decode function will return the salary of employee.

Example : Select Ename, DECODE(Sign(Sal-1500), -1, ‘Below 1500’, 0, ‘On Target’, Sal) Salary from EMP;

Using Case : As of Oracle 9i, you can use CASE function in place of DECODE. The CASE function uses the keywords when, then, else, and end to indicate the logic path followed, which may take the resulting code easier to follow than an equivalent DECODE.

Following example displays Employee name and Salary. Value of salary column is displayed as ‘On Target’ if employee’s salary is 1500, ‘Below 1500’ if salary is less than 1500 and actual salary i.e. 2000,3000 and so on if salary is greater than 1500.

Example : Select Ename, case sign(sal-1500)
when 0 then 'On Target'
when -1 then 'Below target'
else to_char(sal)
end "salary" from emp

NVL(Col, Val ) : Replaces Null value in the given column with given val.
Following example displays Employee name, Salary and Total Remuneration for all employees. Total Remuneration is calculated by the formula ( (Sal * 12 ) + Comm ). Any arithmetic operation with null value result null value. As only Sales employees are having commission, For all remaining employees the total remuneration will be Null. To solve this problem we will write the formula to calculate total remuneration as ((Sal * 12) + nvl(Comm,0)). This replaces the null value of comm column with zero. Now arithmetic operation is not performed against null value. Hence the result will be correct.

Example : Select Ename, Sal, (Sal*12+NVL(Comm, 0)) TotalRem from EMP;


LIST FUNCTIONS

The built in functions of SQL that work on a given list of values are called as List Funtions. The list functions in SQL are as follows.

1. LEAST(Val1, Val2, …, Valn) : Returns the smallest value in given list of values.

Example 1 : Select Least(10, 15, 120, 380, 0 ) from Dual; ( Result is 0 )
Example 2 : Select Least( ‘ORACLE’, ‘MICROSOFT’, ‘SUN’, ‘ADOBE’ ) from Dual; (Result is ADOBE)

Example 3 : Select Least(To_Date(‘01-JAN-03’), To_Date(‘12-DEC-02’),To_char(‘31-DEC-03’)) from Dual; (Result is ‘12-DEC-02’)

2.GREATEST( Val1, Val2,…,Valn) : Returns the largest value in given list of values.

Example 1 : Select Greatest(10, 15, 120, 380, 0 ) from Dual; ( Result is 380 )

Example 2 : Select Greatest( ‘ORACLE’, ‘MICROSOFT’, ‘SUN’, ‘ADOBE’ ) from Dual; (Result is SUN)

Example 3 : Select Greatest(To_Date(‘01-JAN-03’), To_Date(‘12-DEC-02’),To_char(‘31-DEC-03’)) from Dual; (Result is ‘31-DEC-03’)

GROUP FUNCTIONS

Built in functions of SQL those works on a group of values are called as Group functions. Group functions in SQL are as follows.

1. SUM(Col) : Returns the sum of all values in the given column.
Following example finds the total salary drawn by all employees.

Example : Select SUM(Sal) from EMP;

AVG(Col) : Returns average of all values in the given column.
Following example finds the average salary drawn by all employees.

Example : Select AVG(Sal) from EMP;

3. MAX(Col) : Returns maximum value of the values in the given column.
Following example finds the maximum salary among the salaries of all employees.

Example : Select MAX(Sal) from EMP;

MIN(Col) : Returns the minimum value of the values in the given column.
Following example finds the minimum salary among the salaries of all employees.

Example : Select MIN(Sal) from EMP;

5. COUNT(Col) : Returns the count of Non Null values in the given column.
Following eample finds the number of employees drawing Commission.

Example : Select COUNT(COMM) from EMP;

6. COUNT(*) : Returns the total number of rows in the given table.
Following eample finds the total number of employees in Emp Table.

Example : Select COUNT(*) from EMP;


7. STDDEV(Col) : Returns the standard deviation for all values of the given column.
Following eample calculates the Standard deviation of salaries of all employees.

Example : Select STDDEV(SAL) from EMP;

VARIANCE(Col) : Returns the variance for all values of the given column.
Following eample calculates the Variance of salaries of all employees.

Example : Select VARIANCE(SAL) from EMP;

GROUP BY : Group by clause is used to divide the rows of a table into groups and apply group functions on each group separately.
Following example finds the maximum salary in each deartment.

Example 1 : Select Deptno, Max(Sal) from EMP Group By Deptno;

Following example finds the average Salary for each job type.

Example 2 : Select Job, AVG(Sal) from EMP Group By Job;

Rule : When we are using group by clause in a select statement, then all column names in the selection list except group functions must be included in group by clause.
Following example finds the total salary drawn by each job type in each department. In this example Deptno and job, two columns are listed in selection list along with the group function sum. As per the rule, we must include these two columns in, group by clause.

Example 3 : Select Deptno, Job, Sum(Sal) from emp group by Deptno, Job;

HAVING : When we have to check a condition that includes a group function, then instead of where clause we must use Having clause.
Following example finds sum of salaries of all employees of the departments that have more than 3 employees.

Example 1: Select Deptno, Sum(sal) from EMP Group By Deptno Having COUNT(*) > 3;

Following example finds the number of employees in departments whose average salary is more than 3000.

Example 2: Select Deptno, Count (*) from EMP Group By Deptno Having AVG(Sal) > 3000;

Rule : If we are using having clause in a select statement, then a corresponding Group By clause is MUST in that select statement.

SET OPERATORS

S E T O P E R A T O R S

Here the data retrieved by a select statement is treated as a set. Set operators are used to combine Two sets retrieved by two different select statements. The two select statements may retrieve data from the same table or from different tables. Set operators in SQL are as follows.

UNION
UNION ALL
INTERSECT
MINUS

1. UNION : The set operator Union combines the data retrieved by two different select statements by eliminating duplicate values that are obtained after combining the data of two select statements.
Following Example finds all jobs that are in departments 10, 20. In this example, first select statement retrieves the list of jobs in department 10. For example, the jobs in department 10 are Manager, Clerk, and Accountant. Second select statement retrieves the list of jobs in department 20. For example, jobs in department 20 are Manager, Clerk, and Analyst. Result of combining these two results is Manager, Clerk, Accountant, Manager, Clerk, and Analyst. Here Manager and Clerk are duplicated. Union eliminates the duplicated values and final result of union is Manager, Clerk, Accountant, and Analyst.

Example : Select JOB from EMP where Deptno =10
UNION
Select JOB from EMP where Deptno = 20;

2. UNION ALL : Like UNION, UNION ALL also combines two sets that are retrieved by two different select statements except that it does not eliminate duplicate values.
If we take the same above example with union all, then the final result is Manager, Clerk, Accountant, Manager, Clerk, and Analyst.

Example : Select JOB from EMP where Deptno =10
UNION ALL
Select JOB from EMP where Deptno = 20;

3. INTERSECT : The set operator INTERSECT results the values that are common in two sets that are retrieved by two different select statements.
Following example finds the jobs that are present in both departments 10 and 20. In this example, first select statement retrieves the list of jobs in department 10. For example, the jobs in department 10 are Manager, Clerk, and Accountant. Second select statement retrieves the list of jobs in department 20. For example, jobs in department 20 are Manager, Clerk, and Analyst. Result of combining these two results is Manager, Clerk, Accountant, Manager, Clerk, and Analyst. Here Manager and Clerk are Common in both sets. Hence the final result is Manager and Clerk.

Example : Select JOB from EMP where Deptno=10
INTESRSECT
Select JOB from EMP where Deptno = 20;

4. MINUS : The set operator MINUS retrieves the values that are present in first set retrieved by first select statement and not in second set retrieved by second select statement.
Following example finds the jobs that are in department 10, but not in department 20. In this example, first select statement retrieves the list of jobs in department 10. For example, the jobs in department 10 are Manager, Clerk, and Accountant. Second select statement retrieves the list of jobs in department 20. For example, jobs in department 20 are Manager, Clerk, and Analyst. Here Accountant job is present in department 10 but not in department 20. Hence the final result is Accountant.

Example : Select JOB from EMP where Deptno=10 MINUS
Select JOB from EMP where Deptno = 20;

RULES FOR WORKING WITH SET OPERATORS :

Both Select statements must contain same number of columns in the selection list.
Corresponding columns in both the select statements must be of same type.
When using order by clause with set operators, order by clause must be written at the end of second select statement only.
In order by clause we have to specify the position of the column in selection list instead of column name.

JOINS IN ORACLE

J O I N I N G

Combining data from more than one table using a single select statement is called JOINING. Joining in SQL is of these types.

Equi Join
Natural Join
Outer Join
Inner Join
Self Join

1.EQUI JOIN :

Joining more than one table by using a condition that includes ‘ = ’ operator is called as EQUI JOIN.
Following example displays employee details along with Department name in which he is working. Employee details are present in EMP table and Department name exists in another table DEPT. Hence in this example joining is performed between EMP and DEPT. When performing join operation, then we must specify column names in selection list as tablename.columnname when any two tables among all tables we are going to join have a column with same name. Instead of using entire table name, we can use alias names for the tables as in the following example. Using alias names is otional for the columns that are not common in two tables.

Example : Select Ename, Job, Sal, E.Deptno, Dname from EMP E, DEPT D where E.Deptno=D.Deptno;


2. NATURAL JOIN :

As of Oracle 9i, you can use the natural join keyword to indicate that a join should be performed based on all columns that have the same name in the two tables being joined.

Example : Select Ename, Job, Sal, Deptno, Dname from EMP natural join DEPT;

3. OUTER JOIN :

Outer Join is used to display the rows that doesn’t satisfy the given condition, along with the rows that satisfy the given condition. To perform outer join, “right outer join” or “left outer join” or “full outer join” clauses are used. These clauses are used in from clause of the select statement.
The above example, written for the equi-join does not display the department names, in which there are no employees. Because the given condition E.Deptno=D.Deptno will be false for the departments that have no employees. Following example displays the employee details along with their department names. Also displays the department names that have no employees. For this outer-join is performed between emp and dept and “right outer join” clause is used. Because a department exists but corresponding employee does not exist. This means that EMP table does not contain rows corresponding to one or more departments in dept table and DEPT table is towards right side of the equi-join condition.

Example : Select Ename, Job, Sal, D.Deptno, Dname from EMP E right outer join DEPT D on E.Deptno = D.Deptno;

You can replace the on clause with a using clause along with the name of the column the tables have in common – do not qualify the column name with a table name or table alias.

Example : Select Ename, Job, Sal, Deptno, Dname from EMP right outer join DEPT using(deptno)

4. INNER JOIN :

Support for inner join was introduced in Oracle9i. inner joins are the default – they return the rows the two tables have in common, and are the alternative to outer joins. Note that they support on and using clauses. So you can specify your join criteria.
Following example displays employee details along with their grade. Employee details are present in EMP Table and Grade Details are present in SALGRADE Table. SALGRADE table contains LOSAL, HISAL and GRADE Columns. LOSAL and HISAL are the salary range and GRADE is grade of the employees having the salary in that salary range. In this example between…and operator is used. Here as both tables EMPand SALGRADE does not contain columns with same names, Table aliases are not used.

Example : Select Ename, Job, Sal, Deptno, Grade from EMP inner join SALGRADE on Sal BETWEEN Losal AND Hisal;

5. SELF JOIN :

Joining a table to it self is called as Self-Join. Generally self-join is performed on a table that contains self-referential key. Self-referential key is a foreign key that refers to a primary key present in the same table in which it presents.
Following example displays Employee details along with their manager name. In EMP table, EMPNO is primary key and MGR is a foreign key that refers to the primary key EMPNO. As manager is also an employee in the same table, we can get manager’s name by joining EMP table to it self. In self-join we must use alias names or tablename.columnname notation.

Example : Select E.Empno, E.Ename, E.Job, E.Sal, E.Mgr, M.Ename “MANAGER” from EMP E inner join EMP M on E.Mgr = M.Empno;

RULES TO PERFORM JOIN :

User must follow the following rules when performing join operation.

1. In joining statements you must specify the column names in the selection list even you are
Selecting all columns of the table and specifying “*” to select al columns is not possible.
2. If the joining tables contain columns with same name, then tablename.columnname notation or table alias is must except when performing natural join and when using “using” clause.
3. The number of join conditions must be one less than the number of tables you are going to join when performing inner join.

SUB QUERIES

S U B Q U E R I E S

A statement requesting data from database called as a query. As we are using select statement to request data from database, select statement is called as a query. Writing a select statement within another select statement is called as a Subquery. In a subquery the select statement that contains another select statement within it is called as outer query and the select statement that is within another select statement is called as Inner query. Inner query must be placed in parantheses. This is because inner query must be executed prior to outer query. Sub queries in SQL are of four types and are as follows.

Single Row Sub Queries
Multi Row Sub Queries
Nested Sub Queries
Correlated Sub Queries

1.SINGLE ROW SUBQUERIES :

If an inner query of a subquery returns only one value then that subquery is called as a Single Row Subquery.
Following example displays the employee details whose salary is maximum salary among all employees. In this example, inner query retrieves the maximum salary in EMP table and outer query compares salary of every employee with the salary returned by inner query. When employee salary is equal to the salary returned by inner query, then that employee row is displayed in output.

Example 1 : Select * from EMP where Sal = ( Select Max(Sal) from EMP );

Following example displays all employee details who are working in SALES deparetment. Because EMP table contains only department number and it does not contain department name, first we have to know the DEPTNO of SALES department. Inner query retrieves the DEPTNO of sales department from DEPT table and that DEPTNO is used by outer query to find employees working in SALES department.

Example 2 : Select * from EMP where Deptno=(Select Deptno from DEPT where Dname=’SALES’);

As inner query of above two subqueries returns only one value, these subqueries are called as single row subqueries.

2.MULTI ROW SUB QUERIES :

If an inner query of a subquery returns more than one value then that subquery is called as a Multi Row Subquery.
Following example finds the employees drawing maximum salary for each department. In this example, inner query returns the list of maximum salaries in all departments and outer query will compare employee salary with the list of salaries returned by inner query. If employee salary is equivalent to any one of the slary in the list of slaries returned by inner query, then that employee row is displayed in out put.

Example : Select * from EMP where Sal in ( Select Max(Sal) from EMP Group By Deptno );

But the result of this query is wrong. If maximum salaries of departments 10,20, and 30 are 1500,2800,5000 and an employee of department 30 has a monthly salary 2800, then the row of that employee is also retrieved even he is not drawing maximum salary in his department i.e. 5000. When working with multi-row subqueries, we must perform condition checking with more than one column and the above query corrected is as follows.
Example : Select * from EMP where (Deptno, Sal) in ( Select Deptno, Max(Sal) from EMP Group By Deptno);

In the above subquery, inner query returns more than one value. Hence this subquery is called as multi-row subquery. There are two special operators that can be used with Multi Row subqueries. They are Any/Some and All.

ANY / SOME : This operator can be used only with multi row subqueries. This operator returns true if the given condition is true with any one of the values retrieved by the inner query.
Following example finds all employees who are drawing more salary than any one of the employees working in Department 30. Inner query retrieves a list of slaries drawn by the employees working in department 30 and outer query will compare the salary of each employee with every value returned by inner query. If the given condition is true with any one of the values in the list of salaries returned by inner query, then that row is displayed in the output. If the given condition is not true with all values in the list returned by inner query, then that row is not displayed in the output.
Example : Select * from EMP where Sal > ANY(Select distinct Sal from EMP where Deptno=30);

ALL : This operator can also be used only with multi row subqueries. This operator returns true if the given condition is true with all values returned by inner queery.
Following example finds all employees whose salary is more than every employee working in Department 30. Inner query retrieves a list of slaries drawn by the employees working in department 30 and outer query will compare the salary of each employee with every value returned by inner query. If the given condition is true with all of the values in the list of salaries returned by inner query, then that row is displayed in the output. If the given condition is not true with any one of the value in the list returned by inner query, then that row is not displayed in the output.

Example : Select * from EMP where Sal > ALL(Select distinct sal from EMP where Deptno = 30 );

3. NESTED SUBQUERIES :

If an inner select statement of a subquery contains another select statement within it then that subquery is called as Nested subquery. Subqueries can be nested up to 255 levels.
Following example finds all employees whose salary is more than average salary of sales department. As department name does not existin EMP table, first we have to find the deptno of sales dapartment. Then we have to find the average salary of sales department by using deptno returned by inner most query. Finally this average salary is used by outer query to find the employees whose salary is more than this average salary.

Example : Select * from EMP where Sal > (Select AVG(Sal) from EMP where Deptno = (Select Deptno from DEPT where Dname = ‘SALES’ ));

4. CO-RELATED SUBQUERIES : If an inner query of a subquery contains an outer query column in the condition of where clause then that subquery is called as a co-related subquery.
Following example finds all employees whose Salary is more than average salary of the department in which they are working. Inner query finds the average salary of a department in which an employee, whose row is curently accessed by outer query and this average salary is compared to the salary of that employee and if his salary is more than average salary. Then his row is displayed in the output.

Example : Select * from EMP E where Sal > ( Select AVG(Sal) from EMP S Where E.Deptno = S.Deptno );

The difference between normal subqueries and correlated subqueries is that in normal subqueries the inner query is executed only once. But in correlated subqueries inner query is executed once per each row of the outer query.

TRANSACTION PROCESIING, CONCURRENCY AND LOCKING

TRANSACTION PROCESSING

A transaction is an operation against the database, which comprises a series of changes to one or more tables. There are two classes of transactions, DML transactions, Which can consist of any number of DML statements and which Oracle treats as a Single entity or Logical unit of work and DDL transactions, which can only consist of one DDL statement.

There can be no halfway situation during the execution of the transaction, where some changes specified within the transaction are made to the database and others are not made. For every transaction either all the changes made to the database are made permanent or none of the changes are carried out.

A transaction begins when the first executable DML or DDL command is encountered and ends when one of the following occurs.
1. Commit or Rollback
2. DDL Command is issued
3. Certain Errors
4. Log Off
5. Machine Failure.
A DDL statement is automatically committed and therefore implicitly ends a transaction.

Making Changes Permanent

In order for changes to become permanent they must be committed to the database. The “ COMMIT ” command makes database changes permanent. The change or changes made to the database between two COMMIT commands therefore makeup a transaction. Until a transaction is committed, none of its changes are visible to other users.

COMMIT
Commit makes changes in the current transaction permanent.
Commit erases all Save Points in the transaction.
Commit ends the transaction.
Commit releases the transaction locks.
You should explicitly end the transaction in application programs, using COMMIT statement. If you do not explicitly COMMIT the transaction and the program terminates abnormally, the last committed transaction will be rolled back.
Implicit commits occur in the following situations.
Before a DDL command.
After a DDL statement.
At normal disconnect from database.

Removing Unwanted Changes :

Uncommitted changes may be abandoned by typing “ROLLBACK“. Rollback will return the data to the state it was in immediately after your last Commit by discarding all changes made since the last commit.

Syntax: RollBack to Savepoint Savepointname;

The Rollback statement is used to UNDO work.
Rollback to a savepoint is optional.
If you use rollback without a “ to Savepoint ” clause, it
Ends the transaction.
Undoes all changes in current transaction.
Erases all Savepoints in that transaction.
Releases the transaction locks.

Save Point :

Syntax : Savepoint SavepointName;

Savepoint can be used to divide a transaction into smaller portions.
Savepoints allow you to arbitrarily hold your work at any point in time, with the option of later committing that work or undoing all or a portion of it.
If you create a second savepoint with the same name as an earlier Savepoint, the earlier Savepoint is deleted.
The maximum number of Savepoints per user process is defaults to 5.

System Failures :

When a transaction is interrupted by a serious error, for example, a system failure, the entire transaction is automatically rolled back. This prevents the error from causing unwanted changes to your data and returns your tables to their status at the time of the last COMMIT.
An automatic roll back is most often caused by a system failure, such as an unintentional system reset or power blackout. Errors in entering commands such as misspelling a column name or trying to perform an unauthorized operation on another user’s table do not interrupt a transaction or cause an automatic rollback.
Auto Commit :
Commit or Rollback may be issued manually or automatically by using the auto commit option of the set command. Syntax: Set AutoCommit On/Off.

CONCURRENCY AND LOCKING

Oracle locks are used to control concurrent access to data. Locks are mechanisms intended to prevent destructive interaction between users accessing the same data. Locks are used to achieve two important goals, data concurrency and read consistency. Locks in Oracle fall into two categories.

Data Locks or DML Locks : These locks protect data. Table locks lock the entire table while Row locks just locks selected rows.

Dictionary Locks : These locks protect the structure of Database objects. Dictionary locks are of two types.
Parse Locks and
DDL Locks.
In this chapter we will discuss only data locks.

Summary Of Table Locks :

Tables can be locked in Exclusive(X), Share(S), RowExclusive(RX), RowShare(RS) and ShareRowExclusive(SRX) modes. Following table shows the posiility of operations against a table when that table is locked in a particular mode. The user who locks the table can perform any operation against the table. The following table describes the posibilities for other users on a table, which is locked by a user. For example, if a table is locked in exclusive mode, then locking that table in any mode and performing DML operations is not posible for other users. In the same way if a table is locked in share mode, then locking that table in share mode and row share mode is possible for other users.


Lock Table Command :

Syntax : Lock Table Tabname1, Tabname2,…, Tabnamen In Lockmode [Nowait];

Here Tabname1, Tabname2, …. , Tabnamen are the names of tables to be locked. Lock mode is one of the modes in which a table can be locked i.e. exclusive, share, row exclusive, row share and share row exclusive. Nowait will allow the user to do other work when another user locks a table, which he accesses. If nowait is not specified, then the system will hang if another user locks the table, which he accesses, until that user releases the lock.

Implicit Locking :

Implicit locking is the locking performed by Oracle automatically without Locking by the user manually. This implicit lock is generally done when updation or deletion is performed against a table and it locks only the rows that are affected by the update or delete statement.

Select…For Update : The select for update statement will overwrite the default locking mechanism. It is used in anticipation of performing an update. The difference between select for update and Update is that select for update locks the rows earlier in a transaction.

Syntax : Select col1, col2,…, coln from tablename For Update [ NOWAIT ]

Dead Lock :

It is quite possible in a multi user environment, that two users will lock each other out of resources. It is also possible that two users will end up locking each other out of different resources. This situation is called a Dead Lock, Because each user is waiting for resources held by the other user.

When Oracle detects a dead lock, it signals an error to one of the participating transactions and rolls back the current statement of that transaction. This resolves the Dead Lock, although other users may still wait until their resource is available. The signaled user should explicitly rollback their transaction.

Dead locks can be avoided if users accessing the same tables lock those tables in the same order as each other. You should pre-define order of access for all tables in your applications and then have all applications follow the same order. If this order is followed in all applications, deadlocks will be kept to a minimum.

Duration Of Locks :

All locks acquired during a transaction are released when the transaction is committed or rolled back.

CONCURRENCY AND LOCKING

CONCURRENCY AND LOCKING

Oracle locks are used to control concurrent access to data. Locks are mechanisms intended to prevent destructive interaction between users accessing the same data. Locks are used to achieve two important goals, data concurrency and read consistency. Locks in Oracle fall into two categories.

Data Locks or DML Locks : These locks protect data. Table locks lock the entire table while Row locks just locks selected rows.

Dictionary Locks : These locks protect the structure of Database objects. Dictionary locks are of two types.
Parse Locks and
DDL Locks.
In this chapter we will discuss only data locks.

Summary Of Table Locks :

Tables can be locked in Exclusive(X), Share(S), RowExclusive(RX), RowShare(RS) and ShareRowExclusive(SRX) modes. Following table shows the posiility of operations against a table when that table is locked in a particular mode. The user who locks the table can perform any operation against the table. The following table describes the posibilities for other users on a table, which is locked by a user. For example, if a table is locked in exclusive mode, then locking that table in any mode and performing DML operations is not posible for other users. In the same way if a table is locked in share mode, then locking that table in share mode and row share mode is possible for other users.


Lock Table Command :

Syntax : Lock Table Tabname1, Tabname2,…, Tabnamen In Lockmode [Nowait];

Here Tabname1, Tabname2, …. , Tabnamen are the names of tables to be locked. Lock mode is one of the modes in which a table can be locked i.e. exclusive, share, row exclusive, row share and share row exclusive. Nowait will allow the user to do other work when another user locks a table, which he accesses. If nowait is not specified, then the system will hang if another user locks the table, which he accesses, until that user releases the lock.

Implicit Locking :

Implicit locking is the locking performed by Oracle automatically without Locking by the user manually. This implicit lock is generally done when updation or deletion is performed against a table and it locks only the rows that are affected by the update or delete statement.

Select…For Update : The select for update statement will overwrite the default locking mechanism. It is used in anticipation of performing an update. The difference between select for update and Update is that select for update locks the rows earlier in a transaction.

Syntax : Select col1, col2,…, coln from tablename For Update [ NOWAIT ]

Dead Lock :

It is quite possible in a multi user environment, that two users will lock each other out of resources. It is also possible that two users will end up locking each other out of different resources. This situation is called a Dead Lock, Because each user is waiting for resources held by the other user.

When Oracle detects a dead lock, it signals an error to one of the participating transactions and rolls back the current statement of that transaction. This resolves the Dead Lock, although other users may still wait until their resource is available. The signaled user should explicitly rollback their transaction.

Dead locks can be avoided if users accessing the same tables lock those tables in the same order as each other. You should pre-define order of access for all tables in your applications and then have all applications follow the same order. If this order is followed in all applications, deadlocks will be kept to a minimum.

Duration Of Locks :

All locks acquired during a transaction are released when the transaction is committed or rolled back.

USER, PRIVILLEGES and ROLES

USER, ROLES & PRIVILEGES

Oracle provides exclusive security features to safeguard your information from both unauthorized viewing and intentional or inadvertent damage. This security is provided by granting or revoking privileges on a person by person and privilege by privilege basis and is in addition to any security your computer system already has.

Every Oracle user has a name and password and owns any tables, views and other resources that he creates. An Oracle role is a set of privileges. You can grant or revoke specific privileges to roles and then assign roles to the appropriate user. A user can also grant privileges directly to other users.

Users :

The Oracle system comes with two users already created System and Sys. You log on to the system user to create other users, since system user has that privilege. The create user command is used to create a new user. After creating a user we can change the password of the user or the memory allocation option or tablespace by using alter user command and it has the following syntax.

Syntax : Alter user UNAME identified by PWD Quota unlimited/Nk on System;

Like create user command, alter user command must also be given only from SYSTEM user. There is another command to change the password of the user from within the same user to which the password has to be changed. That command is Password. To change the password by using this command, user must know the old password.

Syntax : Password

We can delete a user by using drop user command and has the following syntax.

Syntax : Drop user Uname [cascade];

Privileges :

To connect to the database or to create any objects within the database or to access the data owned by another user, a user must have a corresponding permission. These permissions are called PRIVILEGES.

Database system privileges let you execute specific sets of commands. The “create table“ privilege, for example, lets you create tables. The “Grant Any Privilege” privilege allows you to grant any system privilege.

Database object privileges give you the ability to perform some operation on various objects. The “Delete” privilege, for example, lets you delete rows from tables and views. The “Select” privilege allows you to Query with a Select from tables, views, sequences and snapshots.

Granting Privileges :

Grant command is used to grant any system privilege or role to another user, to another role, or to public. The grant command has the following syntax for granting system privileges or role. System privileges can be granted to any user from System User only.

Syntax : Grant SystemPrivilege or Role, SystemPrivilege or Role, …
To Username/Role, Username/Role, …
[With Admin Option];

Example : Grant Create User, Alter User, Drop User to Ramesh;

After executing the above statement, user Ramesh can create a new user or alter an existing user or can delete an existing user from the database. The “with Admin Option” clause permits grantee to grant the Privilege/Role on to other user or role. If the above grant statement is written with “with admin option” at the end, then user Ramesh can grant “Create User”, “Alter User” and “Drop User” Privileges to other users, without connecting to system user.

To grant object privileges the grant command has the following syntax.

Syntax : Grant ObjectPrivilege, ObjectPrivilege,… on TableName
To UserName/Role, Username/Role, …
[ With grant Option ];

For example, if you want to grant select, insert and update permissions on student table of your user to scott, then following statement is used.

Example : Grant Select, Insert, Update(Fee) on Student to Scott;

The above grant statement grants select, insert and updation of FEE column permissions on student table to user Scott. This means that Scott can perform selection, insertion on student table and he can update only fee column of student table.

The “with Grant Option“ clause permits grantee to grant the Privilege on to other user or role. If the above grant statement is written with “with grant option” at the end, then user scott can grant “Select”, “Insert” and “Update” Privileges on student table to other users, without connecting to your user.

Instead of granting specific privileges to specific users, we can grant all privileges on a table as follows.

Example : Grant ALL on Student to Scott;

Instead of granting specific privileges to specific users, we can grant specific privileges or all privileges on object or system privileges to all users in the database at a time by using keyword “PUBLIC“ in place of Username. Following grant statement grants all permissions on student table in your user to all users in the database.

Example : Grant ALL on Student to Public;

Revoking Privileges :

Revoke is used to revoke privileges from a user or role. Revoke command has the following syntax for revoking System privileges. System privileges can be revoked from System user or the user who grants those permissions.

Syntax : Revoke SystemPrivilege/Role, SystemPrivilege/Role,…
From UserName/Role, UserName/Role…;

Example : Revoke Create user, Alter user, Drop user from Ramesh;

After executing the above revoke statement, user Ramesh can not create a new user or alter an existing user or drop an existing user.

Revoke command has following syntax for revoking Object privileges.

Syntax : Revoke ObjectPrivilege, ObjectPrivilege, …
on TableName
From Username/Role,Username/Role,…;

Example 1 : Revoke Select, Insert, Update(Fee) on Student From Scott;
Example 2 : Revoke All on Student From Public;

Example 1 revokes select, insert and updation of fee column privileges on student table from user Scott. Example 2 revokes all permissions on student table from all users in the database.


Roles

A role is nothing but a set of privileges. Using following syntax creates a role.

Syntax : Create Role RoleName;

Example : Create Role TEST;

Granting Privileges On A Role :

Example 1 : Grant Select on Student to TEST;
Example 2 : Grant Select, Insert, on Marks to TEST;
Example 3 : Grant ALL on EMP to TEST;

Above Grant statements grant different permissions on different users to the role TEST. You can also grant system privileges to the same role.

Granting A Role To A User :

Example : Grant TEST to Scott;

Above grant statement grants all permissions of Role TEST to the user Scott.

Revoking A Role From A User :

Example : Revoke TEST from Scott;

Above grant statement revokes all permissions of Role Test from User Scott.

Revoking Privileges From A Role :

Example : Revoke Select, Insert on Marks From TEST;

This statement revokes select and insert privileges on Marks table from Role TEST.

Deleting A Role : A Role can be deleted from database by using drop role command and has following syntax.

Syntax : Drop Role RoleName;

Example : Drop Role TEST;

Standard Roles
Oracle provides standard roles for compatibility with previous versions.

The Connect Role : Occasional users, particularly those who do not create tables will usually be given only the connect role. Connect is the simple privilege to use Oracle at all. This right becomes meaningful with the addition of access to specific tables belonging to other users, and the privilege to select, insert, update and delete rows from these tables as each of these rights are granted.

The Resource Role : More sophisticated and regular users of the database may be granted the resource role. Resource role gives users the additional rights to create their own tables, sequences, procedures, triggers and clusters.

The DBA Role : The DBA role has all the system privileges including unlimited space quotas and the ability to grant all privileges to other users.

Synonym : Synonym is nothing but an alias name to a table and is generally used when we have to access a table of another user. Prior to creating a synonym on another users table you must have privilege on that table.

Syntax : Create [ Public ] Synonym Synonymname for Username.TableName;

Example : Create Synonym SS For Scott.Student;

Above example creates a synonym with name SS for student table in user scott. Now you can use SS to access student table of scott instead of scott.student. Public key word will create public synonym that can be used by all users in the database those have permissions on student table of scott.

As other objects, a synonym can also be deleted. Syntax for deleting a synonym is as follows

Syntax : Drop [ Public ] Synonym Sname;

Example : Drop Synonym SS;

If we use public key word to create a synonym then to delete that synonym we must use the key word public.

VIEWS

V I E W S

· A view is like a window through which data on tables can be viewed or changed.
· A view is derived from another table or view, which is referred to as the base table of the view.
· A view is stored as a select statement only. It is a virtual table i.e. a table that doesn’t physically exist in its own right, but appears to the user as if it exists.
· A view has no data of its own. It manipulates data in the underlying base table.

Creating A View :

Syntax : Create [ OR REPLACE ] [ FORCE ] View
Vname [( Col1, Col2, Col3, … Col n )] As
Select Statement [ WITH CHECK OPTION ]

The “OR REPLACE“ option allows a view to be created even if one exists with that name already. Thus replacing the old version of the view for its owner. This means that the view can be altered without dropping and recreating.

The “FORCE“ option creates the view even if the base table doesn’t exist or there are insufficient table privileges. However, the table must exist before the view is used.

When you create a view with “ WITH CHECK OPTION ” then the view prevents a row from being inserted into the table that violates the condition given in Select statement.

Types Of Views

Depending on the select statement written for the creation of view, views are classified into two types.

Simple View : If a view is created by using a select statement that retrieves data from a single table and doesn’t contain functions and Group By clause, then that view is called as a simple view.

Example : Create Or Replace View EMP10 As Select * from EMP where Deptno = 10;

Complex View : If a view is created by using a select statement that retrieves data from more than one table or contains functions or Group By clause then that view is called as a Complex view. Any DML operation is not possible on a Complex view.

Example : Create Or Replace View EMPDEPT as Select Eno, Ename, Sal, E.Deptno,
Dname, LOC From EMP E, DEPT D where E.Deptno = D.Deptno;

Restrictions On Views :

Using “WITH CHECK OPTION” when the view is created can impose restrictions. There are other constraints to be aware of.

Delete is prohibited if the view contains
Join condition
Group function
Group By Clause
Distinct command
Row Num.

Update is prohibited if the view contains
1. Any of the above
2. Columns defined by expressions. ( like Sal * 12 )

Insert is prohibited if the view contains
1. Any of the above
2. Any NOT NULL columns are not selected by the view.

Uses Of Views :

Restricting access to database. Selecting from a view can display a restricted portion of database.
Allowing users to make simple queries to retrieve results from complicated queries.

Deleting A View :

Syntax : DROP VIEW VIEW NAME