205 - RDBMS

Unit-1 Introduction of Relational Model

1.1 Codd’s Rules

Dr. E.F. CODD’S RULES:

Dr [Link], also known to the world as the ‘Father of Database Management Systems’
had produce 12 rules which are in-fact 13 in number. The rules are numbered from zero to
twelve. According to him, a DBMS is fully relational if it accepts by all his twelve rules.

Rule 0: The Foundation Rule

The DB must be structured in a relational manner so that the system’s relational
capabilities can manage the DB.

Rule 1: The Information Rule

A DB comprises a variety of data, which must be recorded in the form of columns and rows
in each and every cell of a table.

Rule 2: The Guaranteed Access Rule

A relational DB’s primary key value, column name, and table name can be used to
conceptually retrieve any single or precise data (the atomic value).

Rule 3: The Systematic Treatment of Null Values

The treatment of Null values in DB records is defined by this rule. No value in a cell,
missing data, unsuitable information, unknown data, the primary key that should not be
null, etc., are all examples of null values in DBs.

Rule 4: The Dynamic/Active Online Catalog on the basis of the Relational Model
A DB dictionary is a logical representation of the whole logical structure of a descriptive DB
that needs to be stored online. It grants user’s access to the DB and uses a query language
that is comparable to that of the DB.

Rule 5: The Comprehensive Data Sub Language Rule

The relational DB supports a variety of languages, and in order to access the DB, the
language has to be linear, explicit, or a well-defined syntax, character strings. It must
support the following operations: view definition, integrity constraints, data manipulation,
data definition, as well as limit transaction management.

Rule 6: The View Updating Rule

A view table can theoretically be updated, and DB systems must update them in practice.
 205 - RDBMS

Rule 7: The Relational Level Operation (or High-Level Insert, Delete, and Update) Rule
In each level or single row, a DB system should adhere to high-level relational operations
(for example, update, insert, and delete). The DB system also includes operations like
intersection, union, and minus.

Rule 8: The Physical Data Independence Rule

To access a DB or an application, all stored data must be independent physically. Each
piece of data should not be loyal on another piece of data or an application.

Rule 9: The Logical Data Independence Rule

It’s similar to the independence of physical data. It indicates that any modifications made
at the logical level (or the table structures) should not have an impact on the user’s
experience (application). For example, if a table is split into two separate tables or into
two table joins in order to produce a single table, the application at the user view should
not be affected.

Rule 10: The Integrity Independence Rule

When we are using SQL to put data into table cells, a DB must guarantee integrity
independence. All the entered values must not be changed, and the integrity of the data
should not be loyal on any external component or application. It’s also useful for making
each front-end app DB-independent.

Rule 11: The Distribution Independence Rule

This rule denotes that a DB must function properly even if it’s stored in multiple locations
and used by various end-users.

Rule 12: The Non-Subversion Rule

RDBMS is defined by this rule as a SQL language for storing and manipulating data in a DB.

1.2 Relational operations Algebra (select, project, union, intersection, rename)

Relational algebra refers to a procedural query language that takes relation instances as
input and returns relation instances as output. It performs queries with the help of
operators. A binary or unary operator can be used.

Unary Relational Operations

 SELECT (symbol: σ)
 PROJECT (symbol: π)
 RENAME (symbol: ρ)
 205 - RDBMS

Relational Algebra Operations from Set Theory

 UNION (υ)
 INTERSECTION ( ),
 DIFFERENCE (-)
 CARTESIAN PRODUCT ( x )

Binary Relational Operations

 JOIN
 DIVISION

Let's study them in detail with solutions:

SELECT (σ)
The SELECT operation is used for selecting a subset of the tuples according to a given
selection condition. Sigma (σ) Symbol denotes it. It is used as an expression to choose
tuples which meet the selection condition. Select operator selects tuples that satisfy a
given predicate.
σp(r)
σ is the predicate
r stands for relation which is the name of the table
p is prepositional logic

Example 1
σ topic = "Database" (Tutorials)
Output - Selects tuples from Tutorials where topic = 'Database'.

Example 2
σ topic = "Database" and author = "guru99"( Tutorials)
Output - Selects tuples from Tutorials where the topic is 'Database' and 'author' is
guru99.

Example 3
σ sales > 50000 (Customers)
Output - Selects tuples from Customers where sales is greater than 50000

Projection (π)
The projection eliminates all attributes of the input relation but those mentioned in the
projection list. The projection method defines a relation that contains a vertical subset
of Relation.
 205 - RDBMS

This helps to extract the values of specified attributes to eliminate duplicate values. (pi)
symbol is used to choose attributes from a relation. This operator helps you to keep
specific columns from a relation and discards the other columns.

Example of Projection:
Consider the following table

CustomerID CustomerName Status

1 Google Active
2 Amazon Active
3 Apple Inactive
4 Alibaba Active

Here, the projection of CustomerName and status will give

Π CustomerName, Status (Customers)

CustomerName Status
Google Active
Amazon Active
Alibaba Active
Apple Inactive

Rename (ρ)
Rename is a unary operation used for renaming attributes of a relation. ρ (a/b)R will
rename the attribute 'b' of relation by 'a'.

Union operation (υ)

UNION is symbolized by 𝖴 symbol. It includes all tuples that are in tables A or in B. It also
eliminates duplicate tuples. So, set A UNION set B would be expressed as:
The result <- A 𝖴 B
For a union operation to be valid, the following conditions must hold -
 R and S must be the same number of attributes.
 Attribute domains need to be compatible.
 Duplicate tuples should be automatically removed.

Example
Consider the following tables.
 205 - RDBMS

Table A Table B
column 1 column 2 column 1 column 2
1 1 1 1
1 2 1 3

A 𝖴 B gives
column 1 column 2
1 1
1 2
1 3

Set Difference (-)

- Symbol denotes it. The result of A - B, is a relation which includes all tuples that are in A
but not in B.

 The attribute name of A has to match with the attribute name in B.

 The two-operand relations A and B should be either compatible or Union
compatible.
 It should be defined relation consisting of the tuples that are in relation A, but not in
B.

Example Table A - B
column 1 column 2
1 2

Intersection
An intersection is defined by the symbol ∩ A ∩ B defines a relation consisting of a set of all
tuple that are in both A and B. However, A and B must be union-compatible.

Visual Definition of Intersection

Example: Table A ∩ B
column 1 column 2
1 1
 205 - RDBMS

Cartesian product(X) in DBMS

Cartesian Product in DBMS is an operation used to merge columns from two relations.
Generally, a Cartesian product is never a meaningful operation when it performs alone.
However, it becomes meaningful when it is followed by other operations. It is also called
Cross Product or Cross Join.
Example – Cartesian product
(A X B)
Output – The above example shows all rows from relation A and B
AXB
column 1 column 2 column 1 column 2
1 1 1 1
1 1 1 3
1 2 1 1
1 2 1 3

1.3 Transaction control language: commit, save point, rollback

Transactional Control Commands

Transactional control commands are only used with the DML Commands such as -
INSERT, UPDATE and DELETE only. They cannot be used while creating tables or
dropping them because these operations are automatically committed in the database.

The COMMIT Command

The COMMIT command is the transactional command used to save changes invoked by
a transaction to the database. The COMMIT command saves all the transactions to the
database since the last COMMIT or ROLLBACK command.
The syntax for the COMMIT command is as follows.

COMMIT;

The ROLLBACK Command

The ROLLBACK command is the transactional command used to undo transactions that
have not already been saved to the database. This command can only be used to undo
transactions since the last COMMIT or ROLLBACK command was issued.
The syntax for a ROLLBACK command is as follows −

ROLLBACK;

The SAVEPOINT Command

 205 - RDBMS

A SAVEPOINT is a point in a transaction when you can roll the transaction back to a
certain point without rolling back the entire transaction.
SAVEPOINT command is used to temporarily save a transaction so that you can rollback
to that point whenever required.
The syntax for a SAVEPOINT command is as shown below.
SAVEPOINT SAVEPOINT_NAME;
This command serves only in the creation of a SAVEPOINT among all the transactional
statements. The ROLLBACK command is used to undo a group of transactions.
The syntax for rolling back to a SAVEPOINT is as shown below.

ROLLBACK TO SAVEPOINT_NAME;

1.4 Data Control language: Grant, Revoke

DCL (Data Control Language) includes commands like GRANT and REVOKE, which are
useful to give "rights & permissions." Other permission controls parameters of the
database system.
Examples of DCL commands:
Commands that come under DCL:
 Grant
 Revoke

Grant:
This command is use to give user access privileges to a database.
Syntax:
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
For example:
GRANT SELECT ON Users TO 'Tom'@'localhost;

Revoke:
It is useful to back permissions from the user.
Syntax:
REVOKE privilege_nameON object_nameFROM {user_name |PUBLIC |role_name}
For example:
REVOKE SELECT, UPDATE ON student FROM BCA, MCA;
 205 - RDBMS

Unit-2 Advanced SQL

2.1 Data types (NUMBER, CHAR, VARCHAR, VARCHAR2, CLOB, NCLOB, LONG,
DATE, RAW, LONGROW)

ORACLE DATA TYPE:

1. Char
The char datatype consist character as alpha, numeric and alpha-numeric.
Capacity :
255 bytes Default and minimum size is 1 byte.
Syntax :
Fieldname Char(size)

2. Varchar
The varchar datatype consist character as alpha,numeric and alpha-numeric.
Capacity :
2000 bytes
Syntax :
Fieldname Varchar(size)

3. Varchar2
The varchar2 datatype consist character as alpha, numeric and alpha-numeric.
Capacity :
4000 bytes
Syntax :
Fieldname Varchar2(size)

4. Number
The number datatype consists only numeric.
Capacity :
38 digit
Syntax :
Fieldname number(p) p – precision
Fieldname number(p,s) p – precision, s – scale
Example:
Fieldname Number(7,2) 50000.20 7– precision and 2 – scale

5. Date
The date datatype consist only date in proper oracle format.
Format : dd-mon-yyyy
Syntax: Date
 205 - RDBMS

6. Blob
BLOB data type same as BFILE data type to store unstructured binary object into Operating
System file. BLOB type fully supported transactions are recoverable and replicated
Capacity.
Capacity :
the blob capacity is system dependent
Syntax :
Fieldname blob(size)

7. Raw & Long Raw

The RAW and LONG RAW data types are for storing binary data or byte strings e.g., the
content of documents, sound files, and video files.
The RAW data type can store up to 2000 bytes while the LONG RAW data type can store
up to 2GB.

8. CLOB
CLOB stands for character large object. You use CLOB to store single-byte or multibyte
characters with the maximum size is 4 gigabytes
Note that CLOB supports both fixed-with and variable-with character sets.

9. NCLOB
NCLOB is similar to CLOB except that it can store the Unicode characters.

10. LONG
This data type is used to store large text data up to the maximum size of 2GB. These are
mainly used in the data dictionary.
LONG data type is used to store character set data.

2.2 ROWID pseudo column & DUAL table

ROWID Pseudocolumn
For each row in the database, the ROWID pseudocolumn returns the address of the row.
Oracle Database rowid values contain information necessary to locate a row:
• The data object number of the object
• The data block in the data file in which the row resides
• The position of the row in the data block (first row is 0)
• The data file in which the row resides (first file is 1). The file number is relative to the
table space.

Usually, a rowid value uniquely identifies a row in the database. However, rows in
different tables that are stored together in the same cluster can have the same rowid.
 205 - RDBMS

Rowid values have several important uses:

• They are the fastest way to access a single row.
• They can show you how the rows in a table are stored.
• They are unique identifiers for rows in a table.

You should not use ROWID as the primary key of a table. If you delete and reinsert a row
with the Import and Export utilities, for example, then its rowid may change. If you delete
a row, then Oracle may reassign its rowid to a new row inserted later.
Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a
query. You cannot insert, update, or delete a value of the ROWID pseudocolumn.
Example This statement selects the address of all rows that contain data for employees in
department 20:

SELECT ROWID, last_name FROM employees

WHERE department_id = 20;

Selecting from the DUAL Table

DUAL is a table automatically created by Oracle Database along with the data dictionary. It
is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be
VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is
useful for computing a constant expression with the SELECT statement. Because DUAL has
only one row, the constant is returned only once.
An example of using the DUAL table would be:

SELECT SYSDATE FROM DUAL;

This would return the system’s current date to your screen.

SYSDATE
03/JUL/16

2.3 DATE Functions (SYSDATE, SYSTIMESTAMP, TO_CHAR, TRUNC, ROUND,

NEXT_DAY, LAST_DAY, MONTHS_BETWEEN, ADD_MONTHS)

Date Scalar Functions

Function Description
Add_Months (d,n) Adds n months to date d.
Example:
Add_Months (`5/13/99',4) = 9/13/99
Last_Day (d) Returns date of the last day of the month
 205 - RDBMS

containing date d.
Example:
Last_Day (`12/6/99') = 12/31/99
Months_Between (d1,d2) Returns the number of months between
dates d1 and d2 as a real number
(fractional value).
Example:
Months_Between (`12/5/99','5/6/99') =
6.9677
Next_Day (d,s) Returns the date of the first weekday s
after date d. If s is omitted, add one day to
d.
Example:
NextDay (`12/16/99',"Monday") =
12/22/99
Sysdate Returns the current system date and time
for each record in item c.
Example:
Sysdate = 2/11/96 19:54:36
To_Date (s) Returns date type in place of date-string s.
This function does not change the data, but
rather the item data type. The results can
be computed mathematically.
Example:
ToDate ("10/12/96") = 10/12/96
Note: See Functions for Returning the Day
of the Week for information on how to
return the day of the week on which a
given date falls.
Systimestamp The SYSTIMESTAMP function is used to get
the system date, including fractional
seconds and time zone, of the system on
which the database resides.
Round (n,m) Returns
Returnsnumber
numbernnrounded
rounded to to m decimal
decimal
places.
places.
TheThe
default
defaultvalue
value
forfor
mmis is
0. 0.
Example:
Example:Round
Round (5.6178,2)
(5.6178,2)= 5.62
= 5.62
Trunc (n,m) Returns number n truncated to number m
decimal places. The default value for m is
0. Example:
Trunc (56.0379,2) = 56.03
TO_CHAR Example:
 205 - RDBMS

Convert Data type To String Converts DATE and TIMESTAMP to

VARCHAR2 with the specified format
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY
HH:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'MM
07/07/2015 03:15:32
Convert NUMBER to CHARACTER
SELECT TO_CHAR(123) FROM DUAL;
TO_
--- 123

2.4 Concepts of Index (Create, drop)

Indexing is a way to optimize the performance of a database by minimizing the number of

disk accesses required when a query is processed. It is a data structure technique which is
used to quickly locate and access the data in a database.
Just like we have index present in the textbooks to help us find the particular topic in the
book, Oracle index behaves the same way.
Indexes are used to search the rows in the table quickly. If the index is not present the
select query has to read the whole table and returns the rows. With Index, the rows can be
retrieved quickly
We should create Indexes when selecting a small percentage of rows from a table (less
than 2-4%). If the % of rows returned is high then index scan will be slow.
Indexes are optional structures associated with tables. You can create indexes on one or
more columns of a table to speed SQL statement execution on that table.
The query decides at the beginning whether to use index or not
The presence of many indexes on a table decreases the performance of updates, deletes,
and inserts because Oracle must also update the indexes associated with the table.

Type of Indexes

Unique or Non Unique Index can be Unique or non Unique.

Oracle create unique index for Primary
key and unique key constraints
Composite The index can be comprised of single of
multiple columns. Composite indexes
can speed retrieval of data for SELECT
statement in which the WHERE clause
references all or the leading portion of
the columns in the composite index.
Function Based indexes The indexed column’s data is based on a
 205 - RDBMS

calculation(Function)

How to Create an Index

Syntax
Create index <index_name> on <table_name> ( <column1>, <column2>, … );
Example.
Create index idx_customer_custname on Customer(CustName);

Composite
Create index idx_customer_Custname_CustNo_City on Customer
(CustName,Custno,City);
Function Based indexes
Create index idx_customer_custnameLower on Customer(LOWER(CustName));

How to Drop (Delete) index

Drop index <Index Name>
Ex:
Drop index idx_customer_custnameLower

2.5 Join Queries

Description
Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed
whenever two or more tables are joined in a SQL statement.
There are 4 different types of Oracle joins:
• Oracle INNER JOIN (or sometimes called simple join)
• Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
• Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)

INNER JOIN (simple join)

It is the most common type of join. Oracle INNER JOINS return all rows from multiple
tables where the join condition is met.
Syntax
The syntax for the INNER JOIN in Oracle/PLSQL is:

SELECT columns
FROM table1
INNER JOIN table2
ON [Link] = [Link];
 205 - RDBMS

In this visual diagram, the Oracle INNER JOIN returns the shaded area:

The Oracle INNER JOIN would return the records where table1 and table2 intersect.

Example
Here is an example of an Oracle INNER JOIN:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This Oracle INNER JOIN example would return all rows from the suppliers and orders
tables where there is a matching supplier_id value in both the suppliers and orders tables.
Let's look at some data to explain how the INNER JOINS work:
We have a table called suppliers with two fields (supplier_id and supplier_name). It
contains the following data:
supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA

We have another table called orders with three fields (order_id, supplier_id, and
order_date). It contains the following data:
order_id supplier_id order_date
500125 10000 2003/05/12
500126 10001 2003/05/13
500127 10004 2003/05/14
If we run the Oracle SELECT statement (that contains an INNER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
 205 - RDBMS

Our result set would look like this:

supplier_id name order_date
10000 IBM 2003/05/12
10001 Hewlett Packard 2003/05/13
Old Syntax
As a final note, it is worth mentioning that the Oracle INNER JOIN example above could be
rewritten using the older implicit syntax as follows (but we still recommend using the
INNER JOIN keyword syntax):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

LEFT OUTER JOIN

Another type of join is called an Oracle LEFT OUTER JOIN. This type of join returns all rows
from the LEFT-hand table specified in the ON condition and only those rows from the
other table where the joined fields are equal (join condition is met).
Syntax
The syntax for the Oracle LEFT OUTER JOIN is:
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON [Link] = [Link];

In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

In this visual diagram, the Oracle LEFT OUTER JOIN returns the shaded area:

The Oracle LEFT OUTER JOIN would return the all records from table1 and only those
records from table2 that intersect with table1.

Example
Here is an example of an Oracle LEFT OUTER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers
 205 - RDBMS

LEFT OUTER JOIN orders

ON suppliers.supplier_id = orders.supplier_id;

This LEFT OUTER JOIN example would return all rows from the suppliers table and only
those rows from the orders table where the joined fields are equal.
Let's look at some data to explain how LEFT OUTER JOINS work:
If we run the SELECT statement (that contains a LEFT OUTER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id supplier_name order_date
10000 IBM 2003/05/12
10001 Hewlett Packard 2003/05/13
10002 Microsoft <null>
10003 NVIDIA <null>

Old Syntax
As a final note, it is worth mentioning that the LEFT OUTER JOIN example above could be
rewritten using the older implicit syntax that utilizes the outer join operator (+) as follows
(but we still recommend using the LEFT OUTER JOIN keyword syntax):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id(+);

RIGHT OUTER JOIN

Another type of join is called an Oracle RIGHT OUTER JOIN. This type of join returns all
rows from the RIGHT-hand table specified in the ON condition and only those rows from
the other table where the joined fields are equal (join condition is met).
Syntax
The syntax for the Oracle RIGHT OUTER JOIN is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON [Link] = [Link];
 205 - RDBMS

In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

In this visual diagram, the Oracle RIGHT OUTER JOIN returns the shaded area:

The Oracle RIGHT OUTER JOIN would return the all records from table2 and only those
records from table1 that intersect with table2.
Example
Here is an example of an Oracle RIGHT OUTER JOIN:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This RIGHT OUTER JOIN example would return all rows from the orders table and only
those rows from the suppliers table where the joined fields are equal.
Let's look at some data to explain how RIGHT OUTER JOINS work:
If we run the SELECT statement (that contains a RIGHT OUTER JOIN) below:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name

FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

order_id order_date supplier_name

500125 2013/08/12 Apple
500126 2013/08/13 Google
500127 2013/08/14 <null>
Old Syntax
As a final note, it is worth mentioning that the RIGHT OUTER JOIN example above could be
rewritten using the older implicit syntax that utilizes the outer join operator (+) as follows
(but we still recommend using the RIGHT OUTER JOIN keyword syntax):

SELECT orders.order_id, orders.order_date, suppliers.supplier_name

FROM suppliers, orders
 205 - RDBMS

WHERE suppliers.supplier_id(+) = orders.supplier_id;

FULL OUTER JOIN

Another type of join is called an Oracle FULL OUTER JOIN. This type of join returns all rows
from the LEFT-hand table and RIGHT-hand table with nulls in place where the join
condition is not met.
Syntax
The syntax for the Oracle FULL OUTER JOIN is:

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON [Link] = [Link];

In some databases, the FULL OUTER JOIN keywords are replaced with FULL JOIN.

In this visual diagram, the Oracle FULL OUTER JOIN returns the shaded area:

The Oracle FULL OUTER JOIN would return the all records from both table1 and table2.
Example
Here is an example of an Oracle FULL OUTER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This FULL OUTER JOIN example would return all rows from the suppliers table and all rows
from the orders table and whenever the join condition is not met, <nulls> would be
extended to those fields in the result set.
Let's look at some data to explain how FULL OUTER JOINS work:
If we run the SELECT statement (that contains a FULL OUTER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers
FULL OUTER JOIN orders
 205 - RDBMS

ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id supplier_name order_date
10000 IBM 2013/08/12
10001 Hewlett Packard 2013/08/13
10002 Microsoft <null>
10003 NVIDIA <null>
<null> <null> 2013/08/14

However, you will notice that the order_date field for those records contains a <null>
value.
The row for supplier_id 10004 would be also included because a FULL OUTER JOIN was
used. However, you will notice that the supplier_id and supplier_name field for those
records contain a <null> value.
Old Syntax
As a final note, it is worth mentioning that the FULL OUTER JOIN example above could not
have been written in the old syntax without using a UNION query.

2.6 Sub Queries with (Insert, update and Delete)

Introduction to the Oracle subquery

A subquery is a SELECT statement nested inside another statement such as SELECT,
INSERT, UPDATE, or DELETE.
Example :
SELECT
product_id, product_name, list_price
FROM
products
WHERE
list_price = (
SELECT
MAX( list_price )
FROM
products );
In this example, the query that retrieves the max price is called the subquery and the
query that selects the detailed product data is called the outer query. We say that the
subquery is nested within the outer query. Note that a subquery must appear within
parentheses ().
Oracle evaluates the whole query above in two steps:
• First, execute the subquery.
 205 - RDBMS

• Second, use the result of the subquery in the outer query.

A subquery which is nested within the FROM clause of the SELECT statement is called an
inline view. Note that other RDBMS such as MySQL and PostgreSQL use the term derived
table instead of the inline view.
A subquery nested in the WHERE clause of the SELECT statement is called a nested
subquery.
A subquery can contain another subquery. Oracle allows you to have an unlimited number
of subquery levels in the FROM clause of the top-level query and up to 255 subquery levels
in the WHERE clause.
Advantages of Oracle subqueries
These are the main advantages of subqueries:
• Provide an alternative way to query data that would require complex joins and
unions.
• Make the complex queries more readable.
• Allow a complex query to be structured in a way that it is possible to isolate each
part.

Subqueries with INSERT statement

INSERT statement can be used with subqueries. Here are the syntax and an example of
subqueries using INSERT statement.

Syntax:
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [*|column1 [, column2]
FROM table1 [, table2 ]
[WHERE VALUE OPERATOR ];

Example:
INSERT INTO neworder
SELECT * FROM orders
WHERE advance_amount in(2000,5000);

Subqueries with UPDATE statement

In a UPDATE statement, you can set new column value equal to the result returned by a
single row subquery. Here are the syntax and an example of subqueries using UPDATE
statement.

Syntax:
UPDATE table SET column_name = new_value
[WHERE OPERATOR [VALUE]
 205 - RDBMS

(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]

Example:
UPDATE neworder
SET ord_date='15-JAN-10'
WHERE ord_amount-advance_amount<
(SELECT MIN(ord_amount) FROM orders);

Subqueries with DELETE statement

DELETE statement can be used with subqueries. Here are the syntax and an example of
subqueries using DELETE statement.

Syntax:
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]

Example:
DELETE FROM neworder
WHERE advance_amount<
(SELECT MAX(advance_amount) FROM orders);
 205 - RDBMS

Unit-3 PL/SQL and Conditional Statements

3.1 Introduction to PL/SQL (Definition & Block Structure)

PL /SQL:
PL/SQL is a combination of SQL along with the procedural features of programming
languages. It was developed by Oracle Corporation in the early 90's.
PL/SQL is one of three key programming languages embedded in the Oracle Database,
along with SQL itself and Java.
Following are notable facts about PL/SQL :
PL/SQL is a completely portable, high-performance transaction-processing language.

PL/SQL provides a built-in interpreted and OS independent programming environment.

PL/SQL can also directly be called from the command-line SQL*Plus interface.
Basic Syntax of PL/SQL:
DECLARE
<declaration section>
BEGIN
<executablecommads> EXCEPTION
<exception handling>
END ;

1. Declaration :
This section starts with the keyword DECLARE. It is section that define all variables,
cursors, subprograms, and other elements to be used in the program.

2. Executable Command : (program code)

This section is enclosed between the keywords BEGIN and END and it is a mandatory
section. It consists of the executable PL/SQL statements of the program. It should have at
least one executable line of code.

3. Exception Handling :
This section starts with the keyword EXCEPTION. This section is again optional and
contains exception(s) that handle errors in the program.
Note : Every PL/SQL statement ends with a semicolon (;) .
PL/SQL blocks can be nested within other PL/SQL blocks using BEGIN and END.

PL/SQL Block Structure:

In PL/SQL, the code is not executed in single line format, but it is always executed by
grouping the code into a single element called Blocks.
 205 - RDBMS

Blocks contain both PL/SQL as well as SQL instruction. All these instruction will be
executed as a whole rather than executing a single instruction at a time.
PL/SQL blocks have a pre-defined structure in which the code is to be grouped. Below are
different sections of PL/SQL blocks.
1. Declaration section
2. Execution section
3. Exception-Handling section

Block Structure
PL/SQL blocks have a pre-defined structure in which the code is to be grouped. Below are
different sections of PL/SQL blocks.

1. Declaration section
2. Execution section
3. Exception-Handling section

Declaration Section
This is the first section of the PL/SQL blocks. This section is an optional part. This is the
section in which the declaration of variables, cursors, exceptions, subprograms, pragma
instructions and collections that are needed in the block will be declared. Below are few
more characteristics of this part.
 This particular section is optional and can be skipped if no declarations are needed.
 This should be the first section in a PL/SQL block, if present.
 This section starts with the keyword 'DECLARE' for triggers and anonymous block.
For other subprograms, this keyword will not be present. Instead, the part after the
subprogram name definition marks the declaration section.
 This section should always be followed by execution section.

Execution Section
Execution part is the main and mandatory part which actually executes the code that is
written inside it. Since the PL/SQL expects the executable statements from this block this
cannot be an empty block, i.e., it should have at least one valid executable code line in it.
Below are few more characteristics of this part.
 This can contain both PL/SQL code and SQL code.
 This can contain one or many blocks inside it as a nested block.
 This section starts with the keyword 'BEGIN'.
 This section should be followed either by 'END' or Exception-Handling section (if
present)
 205 - RDBMS

Exception-Handling Section:
The exception is unavoidable in the program which occurs at run-time and to handle this
Oracle has provided an Exception-handling section in blocks. This section can also contain
PL/SQL statements. This is an optional section of the PL/SQL blocks.

This is the section where the exception raised in the execution block is handled.
 This section is the last part of the PL/SQL block.
 Control from this section can never return to the execution block.
 This section starts with the keyword 'EXCEPTION'.
 This section should always be followed by the keyword 'END'.
The Keyword 'END' marks the end of PL/SQL block.
PL/SQL Block Syntax
Below is the syntax of the PL/SQL block structure.

Types of PL/SQL block

PL/SQL blocks are of mainly two types.
1. Anonymous blocks (name is not assign)
2. Named Blocks

Anonymous blocks:
Anonymous blocks are PL/SQL blocks which do not have any names assigned to them.
They need to be created and used in the same session because they will not be stored in
the server as database objects.
Since they need not store in the database, they need no compilation steps. They are
written and executed directly, and compilation and execution happen in a single process.
Below are few more characteristics of Anonymous blocks.
 These blocks don't have any reference name specified for them.
 These blocks start with the keyword 'DECLARE' or 'BEGIN'.
 Since these blocks do not have any reference name, these cannot be stored for later
purpose. They shall be created and executed in the same session.
 They can call the other named blocks, but call to anonymous block is not possible as
it is not having any reference.
 It can have nested block in it which can be named or anonymous. It can also be
nested in any blocks.
 These blocks can have all three sections of the block, in which execution section is
mandatory; the other two sections are optional.
 205 - RDBMS

Named blocks:
Named blocks have a specific and unique name for them. They are stored as the database
objects in the server. Since they are available as database objects, they can be referred to
or used as long as it is present on the server. The compilation process for named blocks
happens separately while creating them as a database objects.
Below are few more characteristics of Named blocks.
 These blocks can be called from other blocks.
 The block structure is same as an anonymous block, except it will never start with
the keyword 'DECLARE'. Instead, it will start with the keyword 'CREATE' which
instruct the compiler to create it as a database object.
 These blocks can be nested within other blocks. It can also contain nested blocks.
Named blocks are basically of two types:
1. Procedure 2. Function

3.2 Variables, Constants and Data Type

PL/SQL Variable :
PL/SQL variables must be declared in the declaration section or in a package as a global
variable.
Syntax
Variable_name [ CONSTANT ] datatype [ NOT NULL ] [ := DEFAULT initial_value ]
Example
no number := 0 ;

Variable Scope In PL/SQL :

There are two types of variable scope:

Local Variable - variables declared in an inner block and not accessible to outer blocks.

Global Variable - variables declared in the outermost block or a package.

Assign SQL Query Result to PL/SQL Variable :
You can use the SELECT INTO statement of SQL to assign values to PL/SQL variables.
Example
SELECT salary INTO sal FROM emp ;
Declare Constant Variable :
A constant is declared using the CONSTANT keyword. It requires an initial value and does
not allow that value to be changed.

Example:
PI CONSTANT NUMBER: = 3.14;
 205 - RDBMS

3.4 User Defined Record

User-Defined Records
PL/SQL provides a user-defined record type that allows you to define the different record
structures. These records consist of different fields. Suppose you want to keep track of
your books in a library. You might want to track the following attributes about each book −
• Title
• Author
• Subject
• Book ID

Defining a Record
The record type is defined as −
TYPE
type_name IS RECORD
( field_name1 datatype1 [NOT NULL] [:= DEFAULT EXPRESSION],
field_name2 datatype2 [NOT NULL] [:= DEFAULT EXPRESSION],
...
field_nameN datatypeN [NOT NULL] [:= DEFAULT EXPRESSION);
record-name type_name;
The Book record is declared in the following way −
DECLARE
TYPE books IS RECORD
(title varchar(50),
author varchar(50),
subject varchar(100),
book_id number);
book1 books;
book2 books;
Accessing Fields
To access any field of a record, we use the dot (.) operator. The member access operator is
coded as a period between the record variable name and the field that we wish to access.
Following is an example to explain the usage of record −
DECLARE
type books is record
(title varchar(50),
author varchar(50),
subject varchar(100),
book_id number);
book1 books;
 205 - RDBMS

book2 books;
BEGIN
-- Book 1 specification
[Link] := 'C Programming';
[Link] := 'Nuha Ali ';
[Link] := 'C Programming Tutorial';
book1.book_id := 6495407;
-- Book 2 specification
[Link] := 'Telecom Billing';
[Link] := 'Zara Ali';
[Link] := 'Telecom Billing Tutorial';
book2.book_id := 6495700;
-- Print book 1 record
dbms_output.put_line('Book 1 title : '|| [Link]);
dbms_output.put_line('Book 1 author : '|| [Link]);
dbms_output.put_line('Book 1 subject : '|| [Link]);
dbms_output.put_line('Book 1 book_id : ' || book1.book_id);
-- Print book 2 record
dbms_output.put_line('Book 2 title : '|| [Link]);
dbms_output.put_line('Book 2 author : '|| [Link]);
dbms_output.put_line('Book 2 subject : '|| [Link]);
dbms_output.put_line('Book 2 book_id : '|| book2.book_id);
END;

3.5 Conditional Statements

Condition in PL/SQL :
Decision-making structures require that the programmer specify one or more conditions
to be evaluated or tested by the program, along with a statement or statements to be
executed if the condition is determined to be true, and optionally, other statements to be
executed if the condition is determined to be false.
IF-THEN statement
If the condition is TRUE, the statements get executed, and if the condition is FALSE or
NULL, then the IF statement does nothing.
Syntax
IF condition THEN
Statement ;
END IF ;
IF-THEN-ELSE statement
A sequence of IF-THEN statements can be followed by an optional sequence of ELSE
statements, which execute when the condition is FALSE.
 205 - RDBMS

Syntax
IF condition THEN
Statement ;
ELSE
Statement ;
END IF ;

IF-THEN-ELSIF statement
The IF-THEN-ELSIF statement allows you to choose between several alternatives. An IF-
THEN statement can be followed by an optional ELSIF...ELSE statement. The ELSIF clause
lets you add additional conditions.
When using IF-THEN-ELSIF statements there are few points to keep in mind.

 It's ELSIF, not ELSEIF

Syntax :
IF condition THEN
Statement ;
ELSIF condition THEN
Statement ;
ELSIF condition THEN
Statement ;
ELSE
Statement ;
END IF ;

Case statement
Like the IF statement, the CASE statement selects one sequence of statements to execute.
Syntax :
CASE selector
WHEN ‘ value1 ’ THEN Statements ;
WHEN ‘ value2 ’ THEN Statements ;
…
ELSE
Statement ;
END
CASE ;
Nested IF-THEN-ELSE statement
It is always legal in PL/SQL programming to nest IF-ELSE statements, which means you can
use one IF or ELSE IF statement inside another IF or ELSE IF statement(s).
 205 - RDBMS

Syntax :
IF condition THEN
IF condition THEN
Statement ;
END IF ;
ELSE
Statement ;
END IF ;

Unit-4 Iterative Statements

4.1 Iterative statements
4.1.1 Loop..End Loop
There may be a situation when you need to execute a block of code several number of
times. A loop statement allows us to execute a statement or group of statements multiple
times .
PL/SQL provides the following types of loop to handle the looping requirements.
Basic Loop
Basic loop structure encloses sequence of statements in between the LOOP and END LOOP
statements.

Syntax
LOOP
No. of Statement ;
END LOOP ;
NOTE : An EXIT statement or an EXIT WHEN statement is required to break the loop.

4.1.2 For.. Loop

A FOR LOOP is a repetition control structure that allows you to efficiently write a loop that
needs to execute a specific number of times.
Syntax
FOR counter IN initial_value .. final_value
LOOP
No. of Statement ;
END LOOP

4.1.3 While Loop

A WHILE LOOP statement in PL/SQL programming language repeatedly executes a target
statement as long as a given condition is true.
Syntax
 205 - RDBMS

WHILE condition
LOOP
No. of Statement ;
END LOOP ;

4.1.4 EXIT Loop

The EXIT statement in PL/SQL programming language has following two usages:
When the EXIT statement is encountered inside a loop, the loop is immediately terminated
and program control resumes at the next statement following the loop.

If you are using nested loops (i.e. one loop inside another loop), the EXIT statement will
stop the execution of the innermost loop and start executing the next line of code after
the block.

4.1.5 Continue

The CONTINUE statement causes the loop to skip the remainder of its body and
immediately retest its condition prior to reiterating. In other words, it forces the next
iteration of the loop to take place, skipping any code in between.

Unit-5: Cursors and Exception Handling

5.1 Concepts of Cursors

A cursor is a pointer to this context area. PL/SQL controls the context area through a
cursor. A cursor holds the rows (one or more) returned by a SQL statement.

5.1.1 Types of cursors (Implicit & Explicit)

The set of rows the cursor holds is referred to as the active set. There are two

Types of cursors:

Implicit Cursor [In-built Cursor ]

Implicit cursors are automatically created by Oracle whenever an SQL statement is
executed, Programmers cannot control the implicit cursors and the information in it.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor.
 205 - RDBMS

Attribute Attribute Description

SQL%FOUND Returns TRUE if an INSERT, UPDATE, or
DELETE statement affected
one or more rows or a SELECT INTO
statement returned one or more rows.
Otherwise, it returns
FALSE.
SQL%NOTFOUND The logical opposite of %FOUND. It
returns TRUE if an INSERT, UPDATE, or
DELETE statement affected no rows, or
a SELECT INTO
statement returned no rows.
Otherwise, it returns FALSE.
SQL%ISOPEN Always returns FALSE for implicit
cursors, because Oracle closes the
SQL cursor automatically after
executing its associated SQL statement.
SQL%ROWCOUNT Returns the number of rows affected
by an INSERT,
UPDATE, or
DELETE statement, or returned by a
SELECT INTO statement.

Explicit Cursor [User Define Cursor]

Explicit cursors are programmer defined cursors for gaining more control over the
context area. An explicit cursor should be defined in the declaration section of the PL/SQL
Block. It is created on a SELECT Statement which returns more than one row.
Syntax
CURSOR cursor_name IS SELECT
statement ;

5.1.2 Declare, open, fetch and close cursors.

Working with an explicit cursor involves four steps:

1. Declaring the cursor for initializing in the memory

2. Opening the cursor for allocating memory
3. Fetching the cursor for retrieving data
4. Closing the cursor to release allocated memory
 205 - RDBMS

1. Declaring the cursor :

Declaring the cursor defines the cursor with a name and the associated SELECT statement.
Syntax
CURSOR cursor_name IS SELECT statement ;
2. Opening the cursor :
Opening the cursor allocates memory for the cursor and makes it ready for fetching the
rows returned by the SQL statement into it.
Syntax
OPEN cursor_name ;
3. Fetching the cursor :
Fetching the cursor involves accessing one row at a time.
Syntax
FETCH cursor_name INTO variable1, variable2, ..
4. [Link] the cursor :
Closing the cursor means releasing the allocated memory.
Syntax
CLOSE cursor_name ;

Example
Select * from customers;
++++++
| ID | NAME | AGE | ADDRESS | SALARY |
++++++
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
++++++
QUE:
The following program will update the table and increase the salary of each customer by
500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected –
IN PL /SQL BLOCK
DECLARE
total_rows number(2);

BEGIN
UPDATE customers
SET salary = salary + 500;
 205 - RDBMS

IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected '); END IF;
END;
/

1) What is Exception Handling?

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known
as exception Handling. Using Exception Handling we can test the code and avoid it from
exiting abruptly.
When an exception occurs a message, which explains its cause is received. PL/SQL
Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A message

By Handling the exceptions, we can ensure a PL/SQL block does not exit abruptly.
2) Structure of Exception Handling.
General Syntax for coding the exception section
DECLARE
Declaration section BEGIN
Exception section EXCEPTION
WHEN ex_name1 THEN
-Error handling statements WHEN ex_name2 THEN
-Error handling statements WHEN Others THEN
-Error handling statements END;

General PL/SQL statements can be used in the Exception Block.

When an exception is raised, Oracle searches for an appropriate exception handler in the
exception section. For example, in the above example, if the error raised is 'ex_name1 ',
then the error is handled according to the statements under it. Since, it is not possible to
determine all the possible runtime errors during testing of the code, the 'WHEN Others'
exception is used to manage the exceptions that are not explicitly handled. Only one
exception can be raised in a Block and the control does not return to the Execution Section
after the error is handled.

5.3.1 Types of Exceptions:

a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions
 205 - RDBMS

[Link] Named System Exceptions

System exceptions are automatically raised by Oracle, when a program violates a RDBMS
rule. There are some system exceptions which are raised frequently, so they are pre-
defined and given a name in Oracle which are known as Named System Exceptions.
For example: NO_DATA_FOUND and ZERO_DIVIDE is called Named System exceptions.
Named system exceptions are:

1) Not Declared explicitly,

2) Raised implicitly when a predefined Oracle error occurs,
3) caught by referencing the standard name within an exception-handling routine.

Exception Name Reason Error

Number
CURSOR_ALREADY_OPEN When you open a cursor ORA-06511
that is already
open.
INVALID_CURSOR When you perform an ORA-01001
invalid operation on a
cursor like closing a cursor,
fetch data from a cursor that
is not opened.
NO_DATA_FOUND When a SELECT...INTO ORA-01403
clause does not
return any row from a table.
TOO_MANY_ROWS When you SELECT or fetch ORA-01422
more than one
row into a record or
variable.
ZERO_DIVIDE When you attempt to divide ORA-01476
a number by
zero.

For Example: Suppose a NO_DATA_FOUND exception is raised in a proc, we can write a

code to handle the exception as given below.
BEGIN
Execution section EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line ('A SELECT...INTO did not return any row.'); END;
 205 - RDBMS

[Link] Unnamed System Exceptions

Those system exception for which oracle does not provide a name is known as unnamed
system exception. These exceptions do not occur frequently. These Exceptions have a
code and an associated message.
There are two ways to handle unnamed system exceptions:
1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.

We can assign a name to unnamed system exceptions using a Pragma called

EXCEPTION_INIT.
EXCEPTION_INIT will associate a predefined Oracle error number to a
programmer_defined exception name.
Steps to be followed to use unnamed system exceptions are
• They are raised implicitly.
• If they are not handled in WHEN Others they must be handled explicitly.
• To handle the exception explicitly, they must be declared using Pragma EXCEPTION_INIT
as given above and handled referencing the user-defined exception name in the exception
section.

The general syntax to declare unnamed system exception using EXCEPTION_INIT is:
DECLARE
exception_name EXCEPTION; PRAGMA
EXCEPTION_INIT (exception_name, Err_code); BEGIN
Execution section EXCEPTION
WHEN exception_name THEN handle the exception
END;
For Example: Let’s consider the product table and order_items table from sql joins. Here
product_id is a primary key in product table and a foreign key in order_items table.

If we try to delete a product_id from the product table when it has child records in
order_id table an exception will be thrown with oracle code number -2292.
We can provide a name to this exception and handle it in the exception section as given
below.
DECLARE
Child_rec_exception EXCEPTION; PRAGMA
EXCEPTION_INIT (Child_rec_exception, -2292); BEGIN
Delete FROM product where product_id= 104; EXCEPTION
WHEN Child_rec_exception
THEN Dbms_output.put_line('Child records are present for this product_id.'); END;
/
 205 - RDBMS

[Link] User-defined Exceptions

Apart from system exceptions we can explicitly define exceptions based on business rules.
These are known as user-defined exceptions.
Steps to be followed to use user-defined exceptions:
• They should be explicitly declared in the declaration section.
• They should be explicitly raised in the Execution Section.
• They should be handled by referencing the user-defined exception name in the
exception section.

RAISE_APPLICATION_ERROR ( )
RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the
user-defined error messages along with the error number whose range is in between -
20000 and -20999.
Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous
transactions which are not committed within the PL/SQL Block are rolled back
automatically (i.e., change due to INSERT, UPDATE, or DELETE statements).
RAISE_APPLICATION_ERROR raises an exception but does not handle it.
RAISE_APPLICATION_ERROR is used for the following reasons,
a) to create a unique id for a user-defined exception.
b) to make the user-defined exception look like an Oracle error. The General Syntax to use
this procedure is:

RAISE_APPLICATION_ERROR (error_number, error_message);

• The Error number must be between -20000 and -20999
• The Error_message is the message you want to display when the error occurs.

Steps to be followed to use RAISE_APPLICATION_ERROR procedure:

1. Declare a user-defined exception in the declaration section.
2. Raise the user-defined exception based on a specific business rule in the execution
section.
3. Finally, catch the exception and link the exception to a user-defined error number in
RAISE_APPLICATION_ERROR.

Using the above example, we can display an error message using

RAISE_APPLICATION_ERROR.