1

PRACTICAL –1

AIM:- Introduction to SQL and installation of SQL Server / Oracle

Relational Database Management System

A relational database management system (RDBMS) is a database management system
(DBMS) that is based on the relational model as introduced by E. F. Codd, of IBM's San
Jose Research Laboratory. Many popular databases currently in use are based on the
relational database model.
 Relational databases are powerful because they require few assumptions about
how data is related or how it will be extracted from the database. As a result, the
same database can be viewed in many different ways.

 An important feature of relational systems is that a single database can be

spread across several tables. This differs from flat-file databases, in which each
database is self-contained in a single table.

 Almost all full-scale database systems are RDBMS's. Small database systems,
however, use other designs that provide less flexibility in posing queries.

Oracle
In June 1970, Dr. E.F Codd, presented a Relational Model sponsored by IBM. The
language developed by IBM to manipulate data stored within Codd’s Model was
originally called as Structured English Query Language. Later it was called SQL.

 Oracle Corporation is a company that produces most widely used server based
multiuser RDBMS named Oracle

 The Oracle Database (commonly referred to as Oracle RDBMS or simply as

Oracle) is an object-relational database management systemproduced and
marketed by Oracle Corporation.

Oracle Application Express

Oracle Application Express (Oracle APEX, previously named Oracle HTML DB) is a
software development environment based on the Oracle database.
 It allows a fast development cycle to be achieved to create web based
applications.

 It can be used for departmental-style applications with a dozen users, but can
also scale up to handle thousands of users.

COMPUTER SCIENCE ENGG. DEPT.

 2

 Opening oracle

 Oracle – opening an existing/new database

COMPUTER SCIENCE ENGG. DEPT.

 3

 Oracle – Login

Basic SQL Commands

SQL Statements

DDL DML DCL

DDL – Data Definition Language

The data definition language concept and name was first introduced in relation to the
Codasyl database model, where the schema of the database was written in a language
syntax describing the records, fields, and sets of the user data model. The term DDL is
also used in a generic sense to refer to any formal language for describing data or
information structures.
1. Create 2. Alter 3. Rename 4. Truncate5. Drop

DML - Data Manipulation Language

A data manipulation language (DML) is a family of syntax elements similar to a
computer programming language used for inserting, deleting and updating data in a
database. Performing read-only queries of data is sometimes also considered a
component of DML.
Manipulation of persistent database objects, e.g., tables or stored procedures, via the
SQL schema statements, rather than the data stored within them, is considered to be
part of a separate data definition language. In SQL these two categories are similar in
their detailed syntax, data types, expressions etc., but distinct in their overall function.
1. INSERT 2. UPDATE 3. DELETE 4. MERGE

DCL - Data Control Language

A data control language (DCL) is a syntax similar to a computer programming language

used to control access to data stored in a database. In particular, it is a component of
Structured Query Language (SQL).GRANT2. REVOKE

COMPUTER SCIENCE ENGG. DEPT.

 4

PRACTICAL – 2

AIM: Data Types, Creating Tables, Retrieval of Rows using Select

Statement, Conditional Retrieval of Rows, Alter and Drop Statements.

Ans: SQL Database Data Types

DATA TYPES represents the type of data an object is holding. Data
Types are defined for columns of a table, local/global variables,
input/output arguments of procedures etc..
Each database system (MS SQL Server, MYSQL, DB2, Oracle etc.)
have its own long list of data types but several data types are
common in most of them. This article will list down common data
types across various database systems.

Numeric Data Type

Few numeric data type has syntax of data_type(x). Here x is
meant for precision value.

Date Time Data Type

datetime: This data type is used to store complete date and time
information. The date to be stored has range from 01/01/1753 to
12/31/9999. This data type takes 8 bit for storage purpose. This
data type is also termed as timestamp in few database systems.
date: This data type is used to store only date information.
time: This data type is used to store only time specific
information.

COMPUTER SCIENCE ENGG. DEPT.

 5

Few numeric data type has syntax of data_type(x). Here x is

meant for precision value.

String Data Type

char(x): This data type is space padded to fill the number of
characters specified. Here x is the number of characters to store.
varchar(x): This type got its name from Varying Characters. This
data type doesn’t pad unnecessary space. Here x is the number of
characters to store
text: This type is used to store long textual information.
Few numeric data type has syntax of data_type(x). Here x is
meant for precision value.

Other Data Type

blob: Binary Large Object. This type is used to store large amount
of binary date such as images or other type of files.
money: In few databases also termed as currency. The type is
used to storage money/currency information
binary: The data type is used to store information in binary string
format.

1. CREATE

Syntax
CREATE TABLE Tablename( { Fieldname Datatype [ (Size) ] Fieldtype [...] } [,...] ) ;

Description:
CREATE TABLE creates a new empty table in the selected database from the structure
description in the command.

COMPUTER SCIENCE ENGG. DEPT.

 6

2. ALTER

Syntax
ALTER TABLE table_name ADD ( column_1 column-definition);
ALTER TABLE table_name MODIFY (column_1 column_type);

Description
The ALTER TABLE statement allows you to rename an existing table. It can also be
used to add, modify, or drop a column from an existing table.
Before using alter command

After using alter command

COMPUTER SCIENCE ENGG. DEPT.

 7

3. DROP

Syntax
DROP TABLE table_name;

Description
DROP TABLE removes one or more tables. You must have the DROP privilege for each
table. All table data and the table definition are removed.

4. RENAME

Syntax
RENAME TABLE table_name TO new_table_name

Description
The rename operation is done atomically, which means that no other session can
access any of the tables while the rename is running. For example, if you have an
existing table old_table, you can create another table new_table that has the same
structure but is empty, and then replace the existing table with the empty one.

COMPUTER SCIENCE ENGG. DEPT.

 8

5. TRUNCATE
Syntax

TRUNCATE TABLE table_name;

Description
TRUNCATE TABLE empties a table completely. Logically, TRUNCATE TABLE is equivalent
to a DELETE statement that deletes all rows, but there are practical differences under
some circumstances.

Implementation of DML Statements

1. The INSERT Statement

The INSERT statement is used to add a new row of data to a table. The basic format of
an INSERT statement is as follows:

Syntax
INSERT
INTO <table>
(<column listings>)
VALUES (<column values>);

COMPUTER SCIENCE ENGG. DEPT.

 9

COMPUTER SCIENCE ENGG. DEPT.

 10

PRACTICAL – 3

AIM: Working with Null Values, Matching a Pattern from a Table,

Ordering the Result of a Query, Aggregate Functions, Grouping the
Result of a Query, Update and Delete Statements.

Ans: SQL NULL

SQL NULL value represents a blank value in a table. NULL value is used for
identifying any missing entries in a table.
One can use NOT NULL value to display table entries which are not NULL.

Syntax of NULL Value

SELECT column_name(s)

FROM table_name

Where column_name IS NULL;

SELECT column_name(s)

FROM table_name

Where column_name IS NOT NULL;

SQL NULL STATEMENT Example:

Database table “Employee”
Employee ID Employee Name Age Gender Location Salary

1001 Henry 54 Male New York 100000

1002 Tina 36 Female Moscow 80000

1003 John 24 Male   40000

1004 Mile 31 Male London 70000

1005 Tara 26 Female   50000

COMPUTER SCIENCE ENGG. DEPT.

 11

1006 Sohpie 29 Female London 60000

Note: "Location" column in the "Employee" table above is optional. Therefore, if

one enters a data with no value for the "Location" column, the "Location" column
will be saved with a NULL value.
Now if one wants to display the entries whose location is left blank, then here is a
statement example.

SELECT * FROM Employee

WHERE Location IS NULL;

SQL NULL Statement Output:

The NULL statement will display the following results
Employee ID Employee Name Age Gender Location Salary

1003 John 24 Male   40000

1005 Tara 26 Female   50000

SQL NOT NULL Statement

Now one wants to display the field entries whose location is not left blank, then
here is a statement example.

SELECT * FROM Employee

WHERE Location IS NOT NULL;

SQL NOT NULL Statement Output:

The NOT NULL statement will display the following results
Employee ID Employee Name Age Gender Location Salary

1001 Henry 54 Male New York 100000

1002 Tina 36 Female Moscow 80000

1003 John 24 Male London 40000

1006 Sophie 29 Female London 60000

COMPUTER SCIENCE ENGG. DEPT.

 12

Joins
Equi Cross Outer
Left
Right

SQL LIKE Operator

The LIKE operator is used to list all rows in a table whose column
values match a specified pattern. It is useful when you want to
search rows to match a specific pattern, or when you do not know
the entire value. For this purpose we use a wildcard character '%'.
For example: To select all the students whose name begins with
'S'
SELECT first_name, last_name

FROM student_details

WHERE first_name LIKE 'S%';

The output would be similar to:

first_name last_name

------------- -------------

Stephen Fleming

Shekar Gowda

The above select statement searches for all the rows where the
first letter of the column first_name is 'S' and rest of the letters in
the name can be any character.
There is another wildcard character you can use with LIKE
operator. It is the underscore character, ' _ ' . In a search string,
the underscore signifies a single character.
For example: to display all the names with 'a' second character,

COMPUTER SCIENCE ENGG. DEPT.

 13

SELECT first_name, last_name

FROM student_details

WHERE first_name LIKE '_a%';

The output would be similar to:

first_name last_name

------------- -------------

Rahul Sharma

NOTE:Each underscore act as a placeholder for only one

character. So you can use more than one underscore. Eg: ' __i%
'-this has two underscores towards the left, 'S__j%' - this has two
underscores between character 'S' and 'i'.

SQL BETWEEN ... AND Operator

The operator BETWEEN and AND, are used to compare data for a
range of values.
For Example: to find the names of the students between age 10
to 15 years, the query would be like,
SELECT first_name, last_name, age

FROM student_details

WHERE age BETWEEN 10 AND 15;

The output would be similar to:

first_name last_name age

------------- ------------- ------

Rahul Sharma 10

Anajali Bhagwat 12

Shekar Gowda 15

COMPUTER SCIENCE ENGG. DEPT.

 14

SQL IN Operator:
The IN operator is used when you want to compare a column with
more than one value. It is similar to an OR condition.
For example: If you want to find the names of students who are
studying either Maths or Science, the query would be like,
SELECT first_name, last_name, subject

FROM student_details

WHERE subject IN ('Maths', 'Science');

The output would be similar to:

first_name last_name subject

------------- ------------- ----------

Anajali Bhagwat Maths

Shekar Gowda Maths

Rahul Sharma Science

Stephen Fleming Science

You can include more subjects in the list like

('maths','science','history')
NOTE:The data used to compare is case sensitive.

SQL IS NULL Operator

A column value is NULL if it does not exist. The IS NULL operator
is used to display all the rows for columns that do not have a
value.
For Example: If you want to find the names of students who do
not participate in any games, the query would be as given below

COMPUTER SCIENCE ENGG. DEPT.

 15

SELECT first_name, last_name

FROM student_details

WHERE games IS NULL

There would be no output as we have every student participate in

a game in the table student_details, else the names of the
students who do not participate in any games would be displayed.
2. The DELETE Statement
The DELETE statement is used to remove one or more rows from a table. The basic
format of a DELETE statement is as follows:

Syntax
DELETE
FROM <table>
WHERE <one or more data conditions>;

Before deleting record of Student with ID=103

After deleting record of student with ID=103

COMPUTER SCIENCE ENGG. DEPT.

 16

3. The UPDATE Statement

The UPDATE statement is used to update existing records in a table.

Syntax
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

4.The MERGE Statement

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;

Employee table

COMPUTER SCIENCE ENGG. DEPT.

 17

Bonuses Table

Merge operation

COMPUTER SCIENCE ENGG. DEPT.

 18

PRACTICAL – 4

AIM: Set Operators, Nested Queries, Joins, and Sequences.

Set Operations

Union +
Intersection Minus
Union All

The set operators in oracle are UNION, UNION ALL, INTERSECT, MINUS. These set
operators allow us to combine more than one select statements and only one result set
will be returned.

UNION: The UNION operator is used to combine the result-set of two or more
SELECT statements Tables of both the select statement must have the same
number of columns with similar data types. It eliminates duplicates.

Syntax:
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

UNION ALL
UNION ALL selects all rows from all the select statements
UNION ALL output is not sorted.
Distinct keyword cannot be used in select statements.

COMPUTER SCIENCE ENGG. DEPT.

 19

Syntax:
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

INTERSECT allows combining results of two or more select queries. If a record

exists in one query and not in the other, it will be omitted from the INTERSECT
results.

Syntax:
select field1, field2, . field_n from tables INTERSECT select field1, field2, . field_n from
tables;

MINUS returns all rows in the first query that are not returned in the second
query. Each statement must have the same number of fields in the result sets
with similar data types.

Syntax:
Select field1, field2, . field_n from tables
MINUS
select field1, field2, . field_n from tables;

EXAMPLES
Table 1: Stu_acc Table 2: Stu_info

UNION UNION ALL

COMPUTER SCIENCE ENGG. DEPT.

 20

INTERSECTION

MINUS

COMPUTER SCIENCE ENGG. DEPT.

 21

SQL Subquery Example:

1) Usually, a subquery should return only one record, but
sometimes it can also return multiple records when used with
operators LIKE IN, NOT IN in the where clause. The query syntax
would be like,
SELECT first_name, last_name, subject

FROM student_details

WHERE games NOT IN ('Cricket', 'Football');

Subquery output would be similar to:
first_name last_name subject

------------- ------------- ----------

Shekar Gowda Badminton

Priya Chandra Chess

2) Lets consider the student_details table which we have used

earlier. If you know the name of the students who are studying
science subject, you can get their id's by using this query below,

COMPUTER SCIENCE ENGG. DEPT.

 22

SELECT id, first_name

FROM student_details

WHERE first_name IN ('Rahul', 'Stephen');

but, if you do not know their names, then to get their id's you
need to write the query in this manner,

SELECT id, first_name

FROM student_details

WHERE first_name IN (SELECT first_name

FROM student_details

WHERE subject= 'Science');

Subquery Output:
id first_name

-------- -------------

100 Rahul

102 Stephen

In the above sql statement, first the inner query is processed first
and then the outer query is processed.

SQL Subquery; INSERT Statement

3) Subquery can be used with INSERT statement to add rows of
data from one or more tables to another table. Lets try to group
all the students who study Maths in a table 'maths_group'.

COMPUTER SCIENCE ENGG. DEPT.

 23

INSERT INTO maths_group(id, name)

SELECT id, first_name || ' ' || last_name

FROM student_details WHERE subject= 'Maths'

SQL Subquery; SELECT Statement
4) A subquery can be used in the SELECT statement as follows.
Lets use the product and order_items table defined in the
sql_joins section.

select p.product_name, p.supplier_name, (select

order_id from order_items where product_id =

101) as order_id from product p where

p.product_id = 101
product_name supplier_name order_id

------------------ ------------------ ----------

Television Onida 5103

Correlated Subquery
A query is called correlated subquery when both the inner query
and the outer query are interdependent. For every row processed
by the inner query, the outer query is processed as well. The inner
query depends on the outer query before it can be processed.

SELECT p.product_name FROM product p

WHERE p.product_id = (SELECT o.product_id FROM

COMPUTER SCIENCE ENGG. DEPT.

 24

order_items o

WHERE o.product_id = p.product_id);

Subquery Notes
Nested Subquery
1) You can nest as many queries you want but it is recommended
not to nest more than 16 subqueries in oracle

Non-Corelated Subquery
2) If a subquery is not dependent on the outer query it is called a
non-correlated subquery

Subquery Errors
3) Minimize subquery errors: Use drag and drop, copy and paste
to avoid running subqueries with spelling and database typos.
Watch your multiple field SELECT comma use, extra or to few
getting SQL error message "Incorrect syntax".

SQL Subquery Comments

Adding SQL Subquery comments are good habit (/* your
command comment */) which can save you time, clarify your
previous work .. results in less SQL headaches

JOIN
The JOIN keyword is used in an SQL statement to query data from two or more tables,
based on a relationship between certain columns in these tables. Tables in a database
are often related to each other with keys. A primary key is a column (or a combination
of columns) with a unique value for each row. Each primary key value must be unique
within the table. The purpose is to bind data together, across tables, without repeating
all of the data in every table.

 EQUI JOIN

Syntax
Select column_list
FROM table1, table2....

COMPUTER SCIENCE ENGG. DEPT.

 25

WHERE table1.column_name = table2.column_name;

Description
SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the
associated tables. An equal sign (=) is used as comparison operator in the where clause
to refer equality. You may also perform EQUI JOIN by using JOIN keyword followed by
ON keyword and then specifying names of the columns along with their associated
tables to check equality.

 CROSS JOIN

Syntax
Select *
From table1
Cross join table2

Description
In cross joins, each row from first table joins with all the rows of another table. If 1st
table contains x rows and y rows in 2nd one, the result set will be x*y rows.

 OUTER JOIN
 LEFT JOIN

Syntax
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Description

COMPUTER SCIENCE ENGG. DEPT.

 26

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if
there are no matches in the right table (table_name2).

 RIGHT JOIN

Syntax
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Description
The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even
if there are no matches in the left table (table_name1).

EXAMPLES

Table 1: Stu_acc Table 2: Stu_info

1. Equi Join

COMPUTER SCIENCE ENGG. DEPT.

 27

2. Cross Join

3. Outer Join
1. Left Outer Join 2. Right Outer Join

COMPUTER SCIENCE ENGG. DEPT.

 28

SQL SERVER: ORACLE: Sequence

Oracle uses different approach for generating unique identifiers.
An auto incremented filed with SEQUENCE object is created first
and this value is assigned to table’s column

CREATE SEQUENCE sequence_name

START WITH start_value

INCREMENT BY increment_value

INSERT INTO table_name(autoincrement_column,

column1, column2, ..)

VALUES(sequence_name.nextval, value1,

COMPUTER SCIENCE ENGG. DEPT.

 29

value2, ..)

sequence_name - is the sequence to be created

start_value – is the start value of identifier

increment_value – is the increment value to

which previous value should be incremented

table_name – is the table name on which auto

increment value is needed

autoincrement_column– is the column whose value

to be auto generated

SQL SERVER: ORACLE Example

CREATE SEQUENCE sequence_employee

START WITH 1

INCREMENT BY 1

INSERT INTO Employee(EmployeeID, Name, ..)

VALUES(sequence_employee.nextval, “Test”, ..)

MS ACCESS: AUTO INCREMENT

column_name data_type constraint AUTOINCREMENT;

COMPUTER SCIENCE ENGG. DEPT.

 30

Example: MS AUTO INCREMENT

CREATE TABLE Employee

EmployeeID INT PRIMARY KEY AUTOINCREMENT,

Name VARCHAR(100) NOT NULL,

The default starting value of AUTOINCREMENT is 1 and will

increment by 1 for each record. To modify it alter the value as in
example below.

PRACTICAL – 5

AIM: Views, Indexes, Database Security and Privileges: Grant and

Revoke Commands, Commit and Rollback Commands.

Ans: SQL Views

A VIEW is a virtual table, through which a selective portion of the data from one or
more tables can be seen. Views do not contain data of their own. They are used to
restrict access to the database or to hide data complexity. A view is stored as a
SELECT statement in the database. DML operations on a view like INSERT,
UPDATE, DELETE affects the data in the original table upon which the view is
based.
The Syntax to create a sql view is

COMPUTER SCIENCE ENGG. DEPT.

 31

CREATE VIEW view_name

AS

SELECT column_list

FROM table_name [WHERE condition];

 view_name is the name of the VIEW.
 The SELECT statement is used to define the columns and rows that you want to
display in the view.
For Example: to create a view on the product table the sql query would be like
CREATE VIEW view_product

AS

SELECT product_id, product_name

FROM product;

SQL Index
Index in sql is created on existing tables to retrieve the rows
quickly.
When there are thousands of records in a table, retrieving
information will take a long time. Therefore indexes are created on
columns which are accessed frequently, so that the information
can be retrieved quickly. Indexes can be created on a single
column or a group of columns. When a index is created, it first
sorts the data and then it assigns a ROWID for each row.
Syntax to create Index:
CREATE INDEX index_name

ON table_name (column_name1,column_name2...);
Syntax to create SQL unique Index:
CREATE UNIQUE INDEX index_name

ON table_name (column_name1,column_name2...);

COMPUTER SCIENCE ENGG. DEPT.

 32

 index_name is the name of the INDEX.

 table_name is the name of the table to which the indexed
column belongs.
 column_name1, column_name2.. is the list of columns
which make up the INDEX.
In Oracle there are two types of SQL index namely, implicit and
explicit.

Implicit Indexes:
They are created when a column is explicity defined with PRIMARY
KEY, UNIQUE KEY Constraint.

Explicit Indexes:
They are created using the "create index.. " syntax.

SQL GRANT REVOKE Commands

DCL commands are used to enforce database security in a multiple
user database environment. Two types of DCL commands are
GRANT and REVOKE. Only Database Administrator's or owner's of
the database object can provide/remove privileges on a database
object.

SQL GRANT Command

SQL GRANT is a command used to provide access or privileges on
the database objects to the users.
The Syntax for the GRANT command is:
GRANT privilege_name

ON object_name

TO {user_name |PUBLIC |role_name}

[WITH GRANT OPTION];

COMPUTER SCIENCE ENGG. DEPT.

 33

 privilege_name is the access right or privilege granted to

the user. Some of the access rights are ALL, EXECUTE, and
SELECT.
 object_name is the name of an database object like TABLE,
VIEW, STORED PROC and SEQUENCE.
 user_name is the name of the user to whom an access right
is being granted.
 user_name is the name of the user to whom an access right
is being granted.
 PUBLIC is used to grant access rights to all users.
 ROLES are a set of privileges grouped together.
 WITH GRANT OPTION - allows a user to grant access rights
to other users.
For Example: GRANT SELECT ON employee TO user1; This
command grants a SELECT permission on employee table to
user1.You should use the WITH GRANT option carefully because
for example if you GRANT SELECT privilege on employee table to
user1 using the WITH GRANT option, then user1 can GRANT
SELECT privilege on employee table to another user, such as
user2 etc. Later, if you REVOKE the SELECT privilege on employee
from user1, still user2 will have SELECT privilege on employee
table.

SQL REVOKE Command:

The REVOKE command removes user access rights or privileges to
the database objects.
The Syntax for the REVOKE command is:

REVOKE privilege_name

ON object_name

FROM {user_name |PUBLIC |role_name}

For Example: REVOKE SELECT ON employee FROM user1;This
command will REVOKE a SELECT privilege on employee table from
user1.When you REVOKE SELECT privilege on a table from a user,
the user will not be able to SELECT data from that table anymore.
However, if the user has received SELECT privileges on that table

COMPUTER SCIENCE ENGG. DEPT.

 34

from more than one users, he/she can SELECT from that table
until everyone who granted the permission revokes it. You cannot
REVOKE privileges if they were not initially granted by you.

Privileges and Roles:

Privileges: Privileges defines the access rights provided to a user
on a database object. There are two types of privileges.
1) System privileges - This allows the user to CREATE, ALTER,
or DROP database objects.
2) Object privileges - This allows the user to EXECUTE, SELECT,
INSERT, UPDATE, or DELETE data from database objects to which
the privileges apply.
Few CREATE system privileges are listed below:
System
Description
Privileges

allows users to create the specified object

CREATE object
in their own schema.

CREATE ANY allows users to create the specified object

object in any schema.

The above rules also apply for ALTER and DROP system
privileges.
Few of the object privileges are listed below:
Object
Description
Privileges

INSERT allows users to insert rows into a table.

allows users to select data from a database

SELECT
object.

UPDATE allows user to update data in a table.

allows user to execute a stored procedure or

EXECUTE
a function.

Roles: Roles are a collection of privileges or access rights. When

there are many users in a database it becomes difficult to grant or

COMPUTER SCIENCE ENGG. DEPT.

 35

revoke privileges to users. Therefore, if you define roles, you can

grant or revoke privileges to users, thereby automatically granting
or revoking privileges. You can either create Roles or use the
system roles pre-defined by oracle.
Some of the privileges granted to the system roles are as given
below:
System Role Privileges Granted to the Role

CREATE TABLE, CREATE VIEW, CREATE

CONNECT SYNONYM, CREATE SEQUENCE, CREATE
SESSION etc.

CREATE PROCEDURE, CREATE SEQUENCE,

CREATE TABLE, CREATE TRIGGER etc. The
RESOURCE
primary usage of the RESOURCE role is to
restrict access to database objects.

DBA ALL SYSTEM PRIVILEGES

Creating Roles:
The Syntax to create a role is:
CREATE ROLE role_name

[IDENTIFIED BY password];
For Example: To create a role called "developer" with password
as "pwd",the code will be as follows
CREATE ROLE testing

[IDENTIFIED BY pwd];

It's easier to GRANT or REVOKE privileges to the users through a

role rather than assigning a privilege directly to every user. If a
role is identified by a password, then, when you GRANT or
REVOKE privileges to the role, you definitely have to identify it
with the password.
We can GRANT or REVOKE privilege to a role as below.

COMPUTER SCIENCE ENGG. DEPT.

 36

For example: To grant CREATE TABLE privilege to a user by

creating a testing role:
First, create a testing Role

CREATE ROLE testing

Second, grant a CREATE TABLE privilege to the ROLE testing. You

can add more privileges to the ROLE.

GRANT CREATE TABLE TO testing;

Third, grant the role to a user.

GRANT testing TO user1;

To revoke a CREATE TABLE privilege from testing ROLE, you can

write:

REVOKE CREATE TABLE FROM testing;

The Syntax to drop a role from the database is as below:
DROP ROLE role_name;
For example: To drop a role called developer, you can write:
DROP ROLE testing;

Data Control Language (DCL)

These SQL commands are used for providing security to database
objects. These commands are GRANT and REVOKE.
SQL is followed by unique set of rules and guidelines called
Syntax. All the SQL statements start with any of the keywords like
SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE,
SHOW and the entire statements end with a semicolon (;).
Important point to be noted is that SQL is case insensitive, which
means SELECT and select have same meaning in SQL statements.
 

COMPUTER SCIENCE ENGG. DEPT.

 37

PRACTICAL – 6

AIM: PL/SQL Architecture, Assignments and Expressions, Writing

Introduction to PL/SQL
o PL/SQL stands for Procedural Language extension of SQL.
o PL/SQL is a combination of SQL along with the procedural features of
programming languages.
o It was developed by Oracle Corporation in the early 90’s to enhance the
capabilities of SQL.

The PL/SQL Engine:

Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can
be stored in the client system (client-side) or in the database (server-side).

A Simple PL/SQL Block:

DECLARE
Variable declaration

COMPUTER SCIENCE ENGG. DEPT.

 38

BEGIN
Program Execution
EXCEPTION
Exception handling
END;

Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE.
This section is optional and is used to declare any placeholders like variables, constants,
records and cursors, which are used to manipulate data in the execution section.

Execution Section:
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and
ends with END. This is a mandatory section and is the section where the program logic
is written to perform any task. The programmatic constructs like loops, conditional
statement and SQL statements form the part of execution section.

Exception Section:
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION.
This section is optional. Any errors in the program can be handled in this section, so
that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions
that cannot be handled, the Block terminates abruptly with errors.

Every statement in the above three sections must end with a semicolon ; . PL/SQL
blocks can be nested within other PL/SQL blocks. Comments can be used to document
code.
IF-THEN-ELSE

Syntax
IF condition THEN
{...statements...}
ELSE
{...statements...}
END IF;

EXAMPLE

Table- Acc_no

COMPUTER SCIENCE ENGG. DEPT.

 39

Case 1 : If statement executed Case 2: Else statement executed

PRACTICAL – 7

AIM: Stored Procedures and Exception Handling.

Procedure
A procedure or in simple a proc is a named PL/SQL block which performs one or more
specific task. This is similar to a procedure in other programming languages.
A procedure has a header and a body. The header consists of the name of the
procedure and the parameters or variables passed to the procedure. The body consists
or declaration section, execution section and exception section similar to a general
PL/SQL Block.

COMPUTER SCIENCE ENGG. DEPT.

 40

There are three types of parameters that can be declared:

IN - The parameter can be referenced by the procedure or function. The value of the
parameter can not be overwritten by the procedure or function.

OUT - The parameter can not be referenced by the procedure or function, but the
value of the parameter can be overwritten by the procedure or function.

IN OUT - The parameter can be referenced by the procedure or function and the value
of the parameter can be overwritten by the procedure or function.

Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name

[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];

How to execute a Procedure?

There are two ways to execute a procedure.

1) From the SQL prompt.

EXECUTE [or EXEC] procedure_name;

2) Within another procedure – simply use the procedure name.

procedure_name;

Functions
A function is a named PL/SQL Block which is similar to a procedure. The major
difference between a procedure and a function is, a function must always return a
value, but a procedure may or may not return a value.

Syntax
CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype;
IS
Declaration_section
BEGIN

COMPUTER SCIENCE ENGG. DEPT.

 41

Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;

1) Return Type: The header section defines the return type of the function. The return
datatype can be any of the oracle datatype like varchar, number etc.

2)The execution and exception section both should return a value which is of the
datatype defined in the header section.

How to execute a PL/SQL Function?

A function can be executed in the following ways.

1) Since a function returns a value we can assign it to a variable.

employee_name := employer_details_func;
If ‘employee_name’ is of datatypevarchar we can store the name of the employee by
assigning the return type of the function to it.

2) As a part of a SELECT statement

SELECT employer_details_func FROM dual;

3) In a PL/SQL Statements like,

dbms_output.put_line(employer_details_func);
This line displays the value returned by the function.

EXAMPLES

1. PROCEDURE
o Before executing Procedure

o After executing Procedure

COMPUTER SCIENCE ENGG. DEPT.

 42

2. Function
o Creating the Function

o Executing the Function

PR
Iterative
Statements

for in
for while
reverse

FOR Loop
A FOR LOOP is used to execute a set of statements for a predetermined number of
times. Iteration occurs between the start and end integer values given. The counter is

COMPUTER SCIENCE ENGG. DEPT.

 43

always incremented by 1. The loop exits when the counter reaches the value of the end
integer.

Syntax
FOR counter IN val1..val2
LOOP
statements;
END LOOP;
val1 - Start integer value.
val2 - End integer value.

FOR in REVERSE Loop

A FOR LOOP is used to execute a set of statements for a predetermined number of
times in reverse order.

Syntax
FOR counter IN REVERSE val1..val2
LOOP
statements;
END LOOP;

While Loop
A WHILE LOOP is used when a set of statements has to be executed as long as a
condition is true. The condition is evaluated at the beginning of each iteration. The
iteration continues until the condition becomes false.

Syntax
WHILE <condition>
LOOP statements;
END LOOP;
EXAMPLES

1. FOR LOOP

COMPUTER SCIENCE ENGG. DEPT.

 44

2. FOR LOOP in REVERSE

3. WHILE LOOP

COMPUTER SCIENCE ENGG. DEPT.

 45

PRACTICAL – 8

AIM: Triggers and Cursor Management in PL/SQL.

TRIGGERS

A trigger is a pl/sql block structure which is fired when a DML statements like Insert,
Delete, Update is executed on a database table. A trigger is triggered automatically
when an associated DML statement is executed.

Syntax
CREATE [OR REPLACE ] TRIGGER trigger_name

COMPUTER SCIENCE ENGG. DEPT.

 46

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;

CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with
the given name or overwrites an existing trigger with the same name.

{BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should the
trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used
to create a trigger on a view. before and after cannot be used to create a trigger on a
view.

{INSERT [OR] | UPDATE [OR] | DELETE} - This clause determines the triggering
event. More than one triggering events can be used together separated by OR keyword.
The trigger gets fired at all the specified triggering event.

[OF col_name] -This clause is used with update triggers. This clause is used when you
want to trigger an event only when a specific column is updated.

CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the
given name or overwrites an existing trigger with the same name.
[ON table_name] - This clause identifies the name of the table or view to which the
trigger is associated.

[REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old and
new values of the data being changed. By default, you reference the values
as :old.column_name or :new.column_name. The reference names can also be changed
from old (or new) to any other user-defined name. You cannot reference old values
when inserting a record, or new values when deleting a record, because they do not
exist.

[FOR EACH ROW] - This clause is used to determine whether a trigger must fire when
each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql
statement is executed(i.e.statement level Trigger).

WHEN (condition) -This clause is valid only for row level triggers. The trigger is fired
only for rows that satisfy the condition specified.

COMPUTER SCIENCE ENGG. DEPT.

 47

Types of PL/SQL Triggers

There are two types of triggers based on the which level it is triggered.
1) Row level trigger -An event is triggered for each row upated, inserted or deleted.
2) Statement level trigger- An event is triggered for each sql statement executed.

PL/SQL Trigger Execution Hierarchy

The following hierarchy is followed when a trigger is fired.
1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each affected row. This events will
alternates between BEFORE and AFTER row level triggers.
4) Finally the AFTER statement level trigger fires.

2. Trigger
o Creating Trigger

COMPUTER SCIENCE ENGG. DEPT.

 48

o Trigger is fired when Insert command is executed

Cursors
Oracle creates a memory area, known as context area, for processing an SQL
statement, which contains all information needed for processing the statement, for
example, number of rows processed etc.

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. The set of
rows the cursor holds is referred to as the active set.

You can name a cursor so that it could be referred to in a program to fetch and process
the rows returned by the SQL statement, one at a time. There are two types of cursors:

Cursors

Implicit Explicit
Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is
executed, when there is no explicit cursor for the statement. Programmers cannot
control the implicit cursors and the information in it.
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor
is associated with this statement.
Attribute Description

Returns TRUE if an INSERT, UPDATE, or DELETE statement affected

%FOUND one or more rows or a SELECT INTO statement returned one or more
rows. Otherwise, it returns FALSE.

The logical opposite of %FOUND. It returns TRUE if an INSERT,

%NOTFOUND UPDATE, or DELETE statement affected no rows, or a SELECT INTO
statement returned no rows. Otherwise, it returns FALSE.

Always returns FALSE for implicit cursors, because Oracle closes the
%ISOPEN
SQL cursor automatically after executing its associated SQL statement.

Returns the number of rows affected by an INSERT, UPDATE, or

%ROWCOUNT
DELETE statement, or returned by a SELECT INTO statement.

Explicit Cursors
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.

COMPUTER SCIENCE ENGG. DEPT.

 49

Syntax
Working with an explicit cursor involves four steps:

o Declaring the cursor for initializing in the memory

o Opening the cursor for allocating memory
o Fetching the cursor for retrieving data
o Closing the cursor to release allocated memory

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT
statement. For example:
CURSOR c_customers IS SELECT id, name, address FROM customers;

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. For example :
OPEN c_customers;

Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example:
FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will close
above opened cursor as follows:
CLOSE c_customers;

EXAMPLES

CURSOR

COMPUTER SCIENCE ENGG. DEPT.

 50

COMPUTER SCIENCE ENGG. DEPT.