UG - B.Sc. - Computer Science - 130 54 - Relational Database Management Systems - Binder - 8455

ALAGAPPA UNIVERSITY
[Accredited with ‘A+’ Grade by NAAC (CGPA:3.64) in the Third Cycle

and Graded as Category–I University by MHRD-UGC]
(A State University Established by the Government of Tamil Nadu)
KARAIKUDI – 630 003
B.Sc. [Computer Science]

V - Semester
130 54
LAB: RELATIONAL DATABASE

MANAGEMENT SYSTEMS
Author:
Dr. Kavita Saini, Associate Professor, Galgotias University, Greater Noida
"The copyright shall be vested with Alagappa University"
All rights reserved. No part of this publication which is material protected by this copyright notice
may be reproduced or transmitted or utilized or stored in any form or by any means now known or
hereinafter invented, electronic, digital or mechanical, including photocopying, scanning, recording
or by any information storage or retrieval system, without prior written permission from the Alagappa
University, Karaikudi, Tamil Nadu.
Information contained in this book has been published by VIKAS® Publishing House Pvt. Ltd. and has
been obtained by its Authors from sources believed to be reliable and are correct to the best of their
knowledge. However, the Alagappa University, Publisher and its Authors shall in no event be liable for
any errors, omissions or damages arising out of use of this information and specifically disclaim any
implied warranties or merchantability or fitness for any particular use.
Vikas® is the registered trademark of Vikas® Publishing House Pvt. Ltd.

VIKAS® PUBLISHING HOUSE PVT. LTD.
E-28, Sector-8, Noida - 201301 (UP)
Phone: 0120-4078900  Fax: 0120-4078999
Regd. Office: A-27, 2nd Floor, Mohan Co-operative Industrial Estate, New Delhi 1100 44
 Website: www.vikaspublishing.com  Email: helpline@vikaspublishing.com
Work Order No.AU/DDE/DE12-27/Preparation and Printing of Course Materials/2020 Dated 12.08.2020 Copies…….
LAB: RELATIONAL DATABASE
MANAGEMENT SYSTEMS
BLOCK 1: TABLE MANIPULATION

Table: Creation, Renaming a Table, Copying Another Table, Dropping a Table
Table Description: Describing Table Definitions, Modifying Tables
BLOCK 2: SQL QUERIES AND SUB QUERIES

SQL Queries: Queries, Sub Queries, and Aggregate Functions
DDL: Experiments Using Database DDL SQL Statements
DML: Experiment Using Database DML SQL Statements
DCL: Experiment Using Database DCL SQL Statements
BLOCK 3: INDEX AND VIEW

Index: Experiment Using Database Index Creation, Renaming an Index, Copying Another Index, Dropping an Index
Views: Create Views, Partition and Locks
BLOCK 4: EXCEPTION HANDLING AND PL/SQL

Exception Handling: PL/SQL Procedure for Application Using Exception Handling
Cursor: PL/SQL Procedure for Application Using Cursors
Trigger: PL/SQL Procedure for Application Using Triggers
Package: PL/SQL Procedure for Application Using Package
Reports: DBMS Programs to Prepare Report Using Functions
BLOCK 5: APPLICATION DEVELOPMENT

Design and Develop Application: Library Information System, Students Mark Sheet Processing, Telephone Directory
Maintenance, Gas Booking and Delivering, Electricity Bill Processing, Bank Transaction, Pay Roll Processing. Personal
Information System, Question Database and Conducting Quiz and Personal Diary
INTRODUCTION
Rapid globalization coupled with the growth of the Internet and information technology has led to a complete
transformation in the way organizations function today. Organizations require those information systems
that would provide them a ‘Competitive Strength’ by handling online operations, controlling operational
and transactional applications, and implementing the management control tools. All this demands the Relational
DataBase Management System or RDBMS which can serve both the decision support and the transaction
processing requirements. Technically, the present RDBMS handles the distributed heterogeneous data
sources, software environments and hardware platforms. Precisely, RDBMS is a DataBase Management
System or DBMS that is based on the relational model introduced by Edgar F. Codd, who was an English
computer scientist. Edgar F. Codd, while working for IBM, invented the relational model for database
management, the theoretical basis for relational databases and relational database management systems.
He made other valuable contributions to computer science, but the relational model, a very influential
general theory of data management, remains his most mentioned, analysed and celebrated achievement.
The most widely used commercial and open source databases are based on the relational model.
Characteristically, a RDBMS is a DBMS in which data is stored in tables and the relationships among the
data are also stored in tables. This stored data can be accessed or reassembled in many different ways
without having to change the table forms. RDBMS program lets you create, update and manage a relational
database. In spite of repeated challenges by competing technologies, as well as the claim by some experts
that no current RDBMS has fully implemented relational principles, the majority of new corporate databases
are still being created and managed with an RDBMS. So, understanding RDBMS through ‘Laboratory
Manuals’ has become extremely important.
This laboratory manual, RELATIONAL DATABASE MANAGEMENT SYSTEMS, is intended for
the students of undergraduate courses in the subject of RDBMS. This laboratory manual typically contains
‘Practical/ Laboratory Sessions’ related to RDBMS, covering various significant topics on the subject to
enhance the understanding. This laboratory manual will help the students to understand the concepts, such
as data normalization, link between tables by means of foreign keys and other relevant database concepts,
menu-driven query processing and reports, the SQL commands, the cursor, triggers, and packages. In
addition, the students will be able to write SQL queries, PL/SQL statements and the database applications
using SQL. Students are advised to thoroughly go through this laboratory manual rather than only topics
mentioned in the syllabus as practical aspects are the key to understand the conceptual visualization of
theoretical aspects covered in the textbooks.
Relational Database
RELATIONAL DATABASE MANAGEMENT SYSTEMS Management Systems
A Relational Database Management System (RDBMS) is a collection of

database and stored procedures. A RDBMS enables you to store, extract and NOTES
manage important information from a database. It is a software that is used to
maintain data security and data integrity in a structured database.
RDBMS helps in maintaining and retrieving data in different forms. There
are various tools available for RDBMS, such as Oracle, Ingres, Sybase, Microsoft
SQL Server, MS-Access, IBM-Db-2, MySQL.
Introduction to Oracle
Oracle is a secure portable and powerful database management system of Oracle
Corporation. Oracle database is also termed as Oracle Database. It is compatible
and connectable with almost all operating systems and machines.
Oracle database is based on relational data model and a non-procedural
language called Structure Query Language (SQL). Oracle database is a tool that
supports storing managing and organization the data.
Structured Query Language (SQL)
SQL is query language used for all database relation management systems. It is a
standard language for all RDBMS’s. It could be classified into various types where
every sub-variety plays its own role and different purpose SQL commands which
can further be classified as:-
 Data Definition Language (DDL)
 Data Manipulation Language (DML)
 Data Control Language (DCL)
 Transaction Control Language (TCL)
Data Types in Oracle
When you define any table, it is required to specify the data type of fields. The
main categories of data types are:-
Data Type Size
Char (size) Maximum size of 2000 bytes
Varchar2 (size) Maximum size of 4000 bytes
Long Maximum size of 2GB
Raw (size) Maximum size of 2000 bytes
long raw (size) Maximum size of 2GB
Number(p, s) Precision can range from 1 to 38.
Scale can range from -84 to 127. Self-Instructional
Material 1
Relational Database Data Type Size
Management Systems
Date A date between Jan 1, 4712 BC and
Dec 31, 9999 AD.
NOTES
BLOCK 1: TABLE MANIPULATION
This block will cover the following topics:

1. Table Creation, Renaming a Table, Copying Another Table, Dropping
a Table.
2. Table Description: Describing Table Definitions, Modifying Tables.
Table
A table is represented in two dimensional structure containing rows and columns.
It contains interrelated data, for example, an employee table contains data about
employees only, i.e., Emp_ID, name, designation, etc. A table is also termed as a
relation. A table is depicted in following table:
Emp_ID Name Designation Salary

5001 Tom Sr. Programmer 38,000
5002 Merlisa Proj. Leader 60,000
5003 George Programmer 26,000
Table - Employee
Getting Started with SQL:
To work with SQL, *Plus Oracle should be installed on computer system. The
following steps are required to follow for invoking SQL Plus:
1. Click on Start Button.
2. Point on All Programs.
3. Point on Oracle Database 10g Express Edition.
4. Click on Go to Database Home Page.
Self-Instructional
2 Material
Relational Database
Management Systems
NOTES
Click on Go to Database Home Page

The following Screen will appear:
Self-Instructional
Material 3
Relational Database Note:
Management Systems
Oracle user name and password may be different and need to be verified in lab. In
this manual, User name is Demo and Password is Demo.
NOTES 1. Enter the User Name Demo, Password Demo (Consult to your Lab
Instructor for user name and password)
2. Click on “Login” button.
Enter the User name and Password as created during installation. The
following screen will appear. In this screen click on SQL.
After clicking on SQL following screen will appear. Click on SQL Command
to go to SQL command window.
Self-Instructional
4 Material
After clicking on SQL Command following command screen will appear, Relational Database
Management Systems
where we can type and run all SQL commands:
NOTES
Creating a Table
This is a Data Definition Language (DDL) command that is used to define the
structure of any table. In a table structure you can define various fields, their data
types and constraints as per the requirements.
Syntax:
Create table <table_name >
(column_name data type(size), column_name data type(size),
…);
Example:
1. Create a table Course.
Column Name Data Type Size
C_Code varchar2 15
C_Name varchar2 15
Duration number 8
Fee number 10, 2
The SQL command to create table is as follows:

Create Table Course
(
C_Code varchar (15), C_Name varchar (15),
Duration number (8), Fee number (10, 2)
);
Self-Instructional
Material 5
Relational Database The above command will create table Course and Oracle will prompt a
Management Systems
message as shown below:
NOTES
Example-2
Create a table Student
Roll_No Varchar 10
Name Varchar 10
Address Varchar 35
C_Code Varchar 8
The above command will create a table structure to store student’s

information. Where Roll_No, Name, Address and C_Code are the field names
and varchar is a data type.
Rename Tables
To rename table, you can use Rename command.
The Syntax for Alter command:
Rename old_table_name to new_table_name;
Self-Instructional
6 Material
Example: Relational Database
Management Systems
NOTES
Dropping a Table
When a SQL table is no more required, you can get rid of the table by using
DROP command. Drop table is a Data Definition Language (DDL). Drop command
is used to drop any object, such as table, index, view, package and function.
The Syntax to drop a table:
Drop table <table_name >
Example:
The above command will remove the course tables.

TRUNCATE:
This command will remove the data permanently but the structure will not be
removed.
Syntax: Truncate Table <Table name>;
Example: Truncate Table Student;
Self-Instructional
Material 7
Relational Database Difference between Truncate and Delete:-
Management Systems
 By using Truncate command, data will be removed permanently and will
not get back whereas by using Delete command, data will be removed
temporarily and get back by using Roll Back command.
NOTES
 By using Delete command, data will be removed based on the condition
‘where as’ or by using Truncate command if there is no condition.
 Truncate is a DDL command and Delete is a DML command.
Try yourself
1. Write SQL Queries to create following tables:
Table: Student:
SID Name Login Age gpa
Table: Enrolled:
SID CID grade
2. Write SQL Queries to add address column in student tables.

3. Write SQL Queries to drop address column in student tables.
4. Write SQL Queries to delete student and enrolled tables.
5. Delete the student table.
Describing a Table:
To see the table structure Oracle provides command Describe (or Desc).
The Syntax to describe a table:
Describe <table_name>
Or
Self-Instructional
8 Material Desc <table_name>
Example Relational Database
Management Systems
Describe Course
Or Desc Course
Command to Modifying Table: NOTES
Table modification may be required in future if requirements gets change after
creating table. Modification includes:
 Add a New Column
 Change Data Type of an Existing Column
 Modify the Length of on Existing Column
 Delete any Column
There are certain points to remember while modifying table:
 If table column contains the values, then the length of column could be
increase.
 To change the data type, column should be empty.
 To decrease the size of data type, column should be empty.
Add a New Column:
The Syntax for alter command:
Alter table <table_name >
ADD (column_name data type (length), column_name data
type(length), …);
Example:
Alter table STUDENT add (MOBILE Number (10));
Self-Instructional
Material 9
Relational Database The above command will add a new column, MOBILE in STUDENT table.
Management Systems
You could see the new structure of STUDENT table:
desc STUDENT;
NOTES
To change data type of an existing column

Alter table <table_name> modify (column data type (length),
column data type (length),…);
Example:
Alter table course modify c_code char (15);
Self-Instructional
10 Material
The above command will change the data type of c_code field from varchar Relational Database
Management Systems
to char.
To modify the length of an existing column:
The Syntax for alter command: NOTES
Example:
Alter table student modify (name varchar (20), address
varchar (40));
The above command will change the length of name column from 15 to 20
and address from 35 to 40.
After altering student table structure will look like:
Self-Instructional
Material 11
Relational Database
Management Systems
NOTES
The above command will describe the structure of COURSE table as shown
below:
To Delete any Column

Alter table <table name> drop column column_name;
Self-Instructional
12 Material
Example Relational Database
Management Systems
Alter table student drop column mobile;
NOTES
The above command will delete the column mobile form students table.
Example:
Self-Instructional
Material 13
Relational Database
Management Systems BLOCK 2: SQL QUERIES AND SUB QUERIES
This block will discuss about the following topics:

NOTES  SQL Queries: Queries, Sub Queries, and aggregate functions
 DDL: Experiments using database DDL SQL statements
 DML: Experiment using database DML SQL statements
 DCL: Experiment using database DCL SQL statements
SQL Queries:
DDL: EXPERIMENTS USING DDL SQL STATEMENTS
Data Definition Language (DDL)
Data definition language commands are used for creating, modifying and removing
database objects wherein the object could be a table cursor, view trigger or a
sequence.
Data definition language commands are:
 CREATE
 ALTER
 DROP
Data Constraints
It is very important that whatever you store into your tables is as per the need of
your organization. No false or incorrect data is stored by the user either intentionally
or accidentally. Constraints are the restriction that you could put on your data to
maintain data integrity. For example, employer’s salary should not be negative
value, two students should not have the same enrollment number, etc.
The constraints help in maintaining data integrity which is one of the rules
defined by E.F. Codd.
Constraints could be specified when a table is created or even after the
table is created with the ALTER TABLE command.
Oracle provides various types of constraints as listed below:-
 PRIMARY KEY
 FOREIGN KEY OR REFERENCE KEY
 NOT NULL
 UNIQUE
 CHECK
 DEFAULT
Self-Instructional
14 Material
Constraint could be defined at column level or at the table level. The only Relational Database
Management Systems
difference between these two is the syntax of these two.
Note: Drop all table created previously in this manual.
NOT NULL Constraint NOTES
In database, NULL is a special value that is different from zero, space or blank. It
represents an unknown value for the column.
The NOT NULL constraint ensures that the value in column is not missing
(NULL). This constraint enforce user to enter data into a specified column. A
column with this constraint could have duplicate values but could not be null or
empty.
You must have created your E-Mail ID. When you create an E-Mail ID, it
is mandatory to fill certain entries (the field with *), those fields are the fields with
the NOT NULL constraint.
The following example creates a table book with the NOT NULL constraint
with the structure as shown below:
Column Name Data Type Size Constraint
B_Code varchar2 15
Title varchar2 40 NOT NULL
Author varchar2 15 NOT NULL
Price number 7,2
The SQL command to create table with NOT NULL constraint is as follows:
Self-Instructional
Material 15
Relational Database Book Table Structure;
Management Systems
NOTES
The above SQL command will create a table Book where Title and Author
have NOT NULL constraints. These constraints would make it sure that both the
columns have some values during inserting and updating of data to these columns.
NOT NULL constraints could be set at column level only.
UNIQUE Constraint:
Sometimes, it is required that column must have unique values only. The unique
constraint ensures that data to the specified column data is not duplicate but it
could contain the NULL values. Let us take an example of contact number and E-
Mail ID; it is not necessary that every student has a contact number and an E-Mail
ID, if they have that will be unique only.
The following example creates a table student with the UNIQUE constraint
Roll_No Varchar 10
Name Varchar 10
Address Varchar 35
E-Mail Varchar 20 Unique
Mobile Number 10 Unique
Self-Instructional
16 Material
The SQL command to create table with UNIQUE constraint is as follows: Relational Database
Management Systems
NOTES
In the above example UNIQUE constraints are set at table level.

PRIMARY KEY Constraint:
A PRIMARY KEY constraint is used to uniquely identify each and every record in
a table. A Primary Key has properties of UNIQUE and NOT NULL constraints.
A PRIMARY KEY constraint has the following properties:
 A Primary Key column allows unique values only.
 It does not allow NULL value in column.
 A Primary Key column could be used for a reference in another table
(Child Table).
Example-1
The following example creates a table Course with the PRIMARY KEY constraint
C_code varchar2 15 Primary Key
C_name varchar2 15
Duration number 8
Fee number 10,2
Self-Instructional
Material 17
Relational Database The SQL command to create table with PRIMARY KEY constraint is as
Management Systems
follows:
NOTES
The above command will create table course which contains a Primary
Key field course code. Here, PRIMARY KEY constraint will enforce the end
user to enter Unique and Not Null values only.
Example-2
The following example creates a table Book with the PRIMARY KEY constraint
B_code varchar2 15 Primary Key
Title varchar2 40
Author varchar2 15
Price number 7,2
The SQL command to create table with PRIMARY KEY constraint is as

follows:
Self-Instructional
18 Material
The above command will create table Book which contains a Primary Key Relational Database
Management Systems
field Book Code. This constraint is required to have unique and not null book
code in a library. * A table can have only and only one Primary Key.
FOREIGN KEY Constraint or REFERENCE KEY Constraint:
NOTES
A Foreign Key column in a table derived values from a Primary Key of another
table that helps in establishing relationship between tables.
A table having Primary Key column is called a Master Table or a Parent
Table and a table with the Reference Key is known as a Transaction Table or a
Child Table.
A Course and Book tables created in the PRIMARY KEY constraint section
have the Primary Key columns C_code and B_code respectively. These columns
could be used to as a Reference Key in another table.
Important Points to Remember
 Reference Key column in a table must have the same data type be as specified
in Primary Key column in another table.
 Size of data type must be the same or more as defined in a Primary Key
column.
 Name of Reference Key column could be same or different as defined in
Primary Key column.
 A table may contain more than one Reference Keys.
 Reference Key’s column values could be duplicate or not null.
 Reference Key’s column can have the same values as stored in Primary
Key column.
Let us suppose that students in any University could be enrolled in the course
which are offered by that university. Course table contains the detail of all the
courses offered by the university, so C_code column in Student table must have
reference of C_code column of Course table.
The following example creates a table student with the REFERENCE KEY
constraint with the structure as shown below:
Roll_No Varchar 10
Name Varchar 10
Address Varchar 35
C_code Varchar 15 Reference Key
Self-Instructional
Material 19
Relational Database The SQL command to create table with REFERENCE KEY constraint is
Management Systems
as follows:
Note: drop student table
NOTES
Now create Student table again with Reference Key as shown below:
The above command will create Table Student which contains a Reference
Key column Course Code. This column will table reference of Course Code of
Course table when record in Student table will be inserted or updated by the user.
* A table can have more than one reference keys.
CHECK Constraint:
A CHECK constraint enforce user to enter data as specified condition. For example,
Marks in any subject should be between the ranges 0 to 100, Fee should not be
negative, Book Code must start with ‘B’, and Book Price should be between the
ranges 1 to 15000 and Employee HRA could not be more than 40% of basic
salary and so on.
Self-Instructional
20 Material
The following example creates a table Book with the CHECK constraint Relational Database
Management Systems
B_Code varchar2 15 Check NOTES
Title varchar2 40
Author varchar2 15
Price number 7,2 Check
Note: drop table book created earlier.

The SQL command to create table with CHECK constraint is as follows:
The above command will create table Book which contains a check
constraints with the field Book Code and Price.
DEFAULT Constraint:
Sometimes, the value of any column for every new record is same. To maintain the
status of book in a library is either available to issue or not you must keep the
status of book as ‘T’ (Available) or ‘F’ (Issued). Every new book purchased for
library the status of book is required to be ‘T’. Default value concept is suitable
for many these types of situations.
The following example creates a table book with the DEFAULT constraint
B_Code varchar2 15
Title varchar2 40
Author varchar2 15
Price number 7,2
Status Char 1 Default
Self-Instructional
Material 21
Relational Database The SQL command to create table with DEFAULT constraint is as follows:
Management Systems
NOTES
The above command will create table Book which contains a default
constraints with the field status.
Multiple constraints could be set together in a table as shown below,
The following example creates a table student with the multiple constraints
Roll_No Varchar 10 Primary Key
Name Varchar 10 Not Null
Address Varchar 35
C_code Varchar 15 Reference Key
Mobile Number 10 Unique
Note: drop Student table then create Student

table again.
The SQL command to create the above mentioned table as follows:
Self-Instructional
22 Material
DML: EXPERIMENTS USING DML SQL STATEMENTS Relational Database
Management Systems
Data Manipulation Language (DML)
Once a table or other object is created using data definition language, Data
Manipulation Language (DML) commands are used to insert, manipulate and NOTES
access data. DML commands helps in inserting, updating, deleting and searching
of data.
The data manipulation language statements are INSERT, DELETE, and
UPDATE.
Insert Records in Table
Once structure of a table is created the next action is to insert records on table.
Insert is a Data Manipulation Language (DML) command.
The Syntax for insert command,
Insert into <table name> values (value1, value2, …);
Few Examples to insert records in table
Example-1
To insert records data into course table the command is as follows:
Output:
Self-Instructional
Material 23
Relational Database After executing the above command system will prompt a message 1 row
Management Systems
created.
Note: All char, varchar and date values should be enclosed in single quotes
(‘), for example ‘MCA’, ’07-Sept-09’, ‘A-08-02’,…
NOTES
Try Yourself:
1. Add five records in course table

2. Create a new table Book with the following fields and data types
Field Name Data Type Size
B_Code varchar 15
Title varchar 30
Author varchar 15
Price Number 6, 2
3. View the structure of Book table
4. Add five records in Book table
INSERT FEW MORE RECORDS:

 Insert into course values (‘PG003’, ‘M Sc-IT’, 3, 32000.00)
 Insert into course values (‘PG002’, ‘MBA’, 2, 40000.00)
 Insert into course values (‘UG002’, ‘B SC-IT’, 3, 25000.00)
Insert Data into Specific Fields
With the above syntax of insert command it is necessary to insert data in all the
fields in the same sequence as defined in the table. But sometimes few fields are
required to update later on for example student’s subjects marks are inserted in
the table and total, percentage or grade is required to calculate later on. To deal
with such a situation you could use the following syntax:
The Syntax to insert data into selected fields only
Insert into <table name> (column1, column2, …)
values (value1, value2, …);
Self-Instructional
24 Material
Examples Relational Database
Management Systems
NOTES
Insert Data with User Interaction

If hundreds or thousands of records are to be inserted in a table it will be very
tedious job to do it with the constant values. The other ways to insert records into
table is take input from the user and repeat the command.
Example:
Self-Instructional
Material 25
Relational Database To insert more record the same command could be repeated by putting /
Management Systems
and pressing enter key at SQL prompt.
You could also insert records interactively into specific fields as shown below.
NOTES Example:
Note: The ‘&’ symbol would prompt user to input data to the various variable.
The variable name that is written after ‘&’ is not required to the same as field
names.
Check Your Progress

1. Add the following data into C_code, C_name and duration fields of Course
table
C_code C_name Duration
UG001 BCA 3
UG002 B Sc-IT 3
PG003 M Sc-IT 2
2. Add three 10 records into Student table with the user interaction.
3. Add data into B_code, Title, and Author fields of Book table with the user
interaction.
Display Table Records

After inserting records into table, data could be displayed with the command
select. All the fields and records could be displayed or only selective records and
fields could be retrieved.
Self-Instructional
26 Material
To View All the Columns Relational Database
Management Systems
To retrieve all the columns use “*” as shown below:
The Syntax for select command:
Select * from <table name>; NOTES
The Example for select command:
To View Selective Columns

To view only selective fields enter column names separated by comma (,) as shown
below:
The Syntax to select required fields:
Select field1, field2, .from <table_names>;
Self-Instructional
Material 27
Relational Database The Example for select:
Management Systems
NOTES
Update Table Records

You may sometimes need to update the records that you have in your table. The
Contact No. or an Address of any person has been changed or Course Fee is
changed by the university. In such cases, the Data manipulation language update
command is used.
The Syntax to Update:
Update <table name>
Set <column_name1 = <new value>,
<column_name2=<new value,
…
[Where <condition>];
Self-Instructional
28 Material
The Example for Update command: Relational Database
Management Systems
NOTES
The above command will update the fee of course UG001 from 29,000
to 32,000.
Where clause is used to specify the condition for which this fee should be
changed. Without any condition all the records will be updated with the new fee
32,000.
More than one columns could also be updated by specifying multiple columns
and there new values after set key words.
Self-Instructional
Material 29
Relational Database The Example to update multiple columns:
Management Systems
NOTES
Try Yourself:
1. Display Name and C_code of students.
2. Change the address from Madras to Delhi of student whose Roll
number is A-08-20.
3. Change the Fee from 32000 to 38000 of course where C_code
is PG001
Delete Records
If records are no more needed you could delete records form the table. For this
purpose you may use Data Manipulation Language (DML) command, i.e., delete.
One, more than one or all the records could be deleted from the table depending
upon the ‘Where’ condition.
Self-Instructional
30 Material
The Syntax for delete command: Relational Database
Management Systems
Delete <table_name> [where <condition>];
Or
Delete from <table_name> where <condition>]; NOTES
The Example for delete command:
The above command will delete one record from Course table where Course
Code is PG002.
To delete all the records from a table you could write the delete command
without ‘Where’ clause as given below:
Delete from course;
Or
Delete course;
Self-Instructional
Material 31
Relational Database
Management Systems
NOTES
The above command will delete all the five records from the course table.
View the Existing Tables
To view all the existing tables in database you could use Tab. Tab is a view which
displays the name and type of object, such as table, view, or synonym.
The Example to view all tables:
Self-Instructional
32 Material
TNAME is a column which displays the object name as table, view, index, Relational Database
Management Systems
or synonym.
TABTYPE is a column which displays the type of object. The type of
object may be any table, view, index, or synonym.
NOTES
Before discussing about the ‘Where’ condition in SQL commands, it is
important to know about the operators in Oracle.
Operators in Oracle
Operators are the special characters that manipulate data items to produce some
result. These data items are called operands. Operators are classified into two
categories:
1. Unary Operators
2. Binary Operators
1. Unary Operators
A unary operator operates only one operand. A unary operator is used as shown
below:
Syntax:
Operator operand
2. Binary Operators
A binary operator operates two operands. A binary operator is used as shown
below:
Syntax:
Operand1 operator operand2
There are various types of operators to cater different purpose which includes:
 Arithmetic Operators
 Comparison Operators
 Logical Operators
 Set Operators
 Concatenation Operator
Arithmetic Operators: Arithmetic operators manipulate two operands and
produce one result. These operators are Addition, Subtraction, Division,
Multiplication and Modulus. These operators work on numeric data type for any
calculation, such as addition, subtraction, and division operators also works on
date data type.
Self-Instructional
Material 33
Relational Database The examples of arithmetic operators are as given below:
Management Systems
Operator Description Example Result
/ Division Select 345 / 4 from dual; 86.25
NOTES * Multiplication Select 345 * 4 from dual; 1380
+ Addition Select 345 + 4 from dual; 349
- Subtraction Select 345 - 4 from dual; 341
Mod Modulus (returns Select 345 mod 4 from 1
the reminder of m dual;
divided by n)
Dual Table
Dual is a dummy table in Oracle that could be used to perform temporary
calculations and to check the result of any Oracle function on data which is not
stored in any table. A dual table is consisting of only one row and a column.
Comparison Operators: Comparison operators are used to compare one
expression with another. The result of a comparison could be TRUE, FALSE. It is
mainly used with WHERE clause of select, update and delete commands.
Operator Description Example
= Equal to Select roll_no, name from student where c_code =
‘PG001’;
!= or <> Not equal to Select roll_no, name from student where c_code <>
‘UG003’;
< Less than Select c_name, duration from course where fee < 50000
> Greater than Select c_name, duration from course where fee >50000;
<= Less than or equal to Select c_name, duration from course where fee
<=45000;
>= Greater than or equal to Select c_name, duration from course where
fee >=56000;
In / Not In Compare if a value lies Select * from student where name IN (‘Smith’, ‘John’);
within a specified list of Select * from student where name IN NOT (‘Smith’,
values ‘John’);
Between/ Compare if a value lies Select c_name, duration from course where fee between
Not within a specified range 45000 and 56000;
Between of values
Like / Not Pattern Matching Select * from student where name LIKE ‘d%’;
Like
Is Null/ Is Compare if a value is Select * from student where contact_no IS NULL;

Not Null null
Note: In a pattern matching operator LIKE, the underscore character ( _ )

represents any one character and the percent character (%) represents a group of
characters.
Self-Instructional
34 Material
Logical Operators: Logical operators test for the truth of some condition. Relational Database
Management Systems
Logical operators, like comparison operators, return a Boolean data type with a
value of TRUE, FALSE, or UNKNOWN.
Operator Description Example NOTES
AND Returns TRUE if both conditions Select c_name, duration from course where
are TRUE. Returns FALSE if either fee >=45000 AND fee<=56000;
is FALSE
OR Returns TRUE if either condition is Select roll_no, name from student where
TRUE. Returns FALSE if both are c_code = ‘PG001’ OR c_code = ‘PG002’
FALSE.
NOT Returns TRUE if the condition is Select * from course where fee not >78000;
FALSE. Returns FALSE if it is
TRUE.
Set Operators: Set operators combine the results of two queries into a
single result.
UNION Returns all distinct rows Select roll_no,b_code from issue
selected by either query. UNION
Select roll_no,b_code from return;
INTERSECT Returns all distinct rows Select roll_no,b_code from issue
selected by both queries. INTERSECT
Select roll_no,b_code from return;
MINUS Returns all distinct rows Select roll_no,b_code from issue

selected by the first query but MINUS
not the second. Select roll_no,b_code from return;
Concatenation Operator: Concatenation operator is used to concatenate

two strings.
|| Concatenates character strings Select ‘Student Name' || name from
student;
Filtering Records Using Where Conditions

A university could have thousand of records but all those records are not required
to view every time. Many users might need to view different records from the
same table at different time.
To filter various records of table ‘Where’ clause could be used with
conditional, logical and other operators could be used. Following are the examples
of various operators in ‘Where’ clause of select query:
The Syntax for select command with ‘Where’ clause:
Select * from <table name> [where <condition>];
Self-Instructional
Material 35
Relational Database The following is the Course table contains 8 records. Let us filter records
Management Systems
from this table with different conditions.
C_CODE C_NAME DURATION FEE
NOTES PG001 MCA 3 55000
PG007 M Sc-CS 2 50000
UG001 BCA 3 32000
UG002 B Sc-IT 3 25000
PG003 M Sc-IT 2 48000
PG002 B Tech-CS 4 60000
PG004 B Tech-EC 4 64000
PG005 B Tech-IT 4 58000
Conditional Operators in SQL:

a) Equal to (=): To see the detail of course where course code equal to
PG003 then the query will be Select * from course where
C_code = ‘PG003’
Output of the above query is shown below:
b) Not Equal to (<>, ! =): To see the detail of course where course duration
is not 4 years then the query will be
Select * from course where duration <> 4;
Self-Instructional
36 Material
Output of the above query is shown below: Relational Database
Management Systems
NOTES
c) Greater Than (>): To see the detail of course where course fee is Greater
than 50000 then the query will be
Select * from course where fee >50000;
Self-Instructional
Material 37
Relational Database As equal to, not equal to and greater than operators are used to filer records
Management Systems
other operators as less than, less than equal to, greater than equal to could be
used.
Other Operators in SQL:
NOTES
(a) BETWEEN: The BETWEEN operator filters the records between a given
range. Suppose you want to filter the courses where fee in between 45000
to 58000. The query to retrieve such records is given below:
Select * from course where fee between 45000 and 58000
The between operators can filter the numbers, text, or date values.
(b) NOT BETWEEN: The NOT BETWEEN operator filters the records
where the data in not between a given range.
Select * from course where fee not between 45000 and 58000
Self-Instructional
38 Material
Output of the above query is shown below: Relational Database
Management Systems
NOTES
Oracle Functions
Oracle provided various built-in functions for different purposes, such as calculation,
comparison and conversion of data. Functions may or may not have the arguments
(input) and have the capability to return a value.
Basically there are two types of function:
 Aggregate Functions
 Scalar Functions
Aggregate Functions: Aggregate functions work on a group of values (a
column values) and returns a single value.
Few aggregate functions are listed below:
 SUM()
 MAX()
 MIN()
 AVG()
 COUNT()
Scalar Functions: SQL scalar functions return a single value, based on the
input value.
Self-Instructional
Material 39
Relational Database Few scalar functions are listed below:
Management Systems
 MID()
 LEN()
NOTES  Upper()
 Lower()
Let suppose we have a table course with the following records:-
Select * from course;
C_CODE C_NAME DURATION FEE

PG002 MBA 2 40000
PG006 MBA 2 50000
PG007 M Sc-CS 3 32000
UG001 BCA 3 32000
UG002 B SC-IT 3 25000
PG003 M Sc-IT 3 32000
PG001 MCA 3 32000
Table - Book
(i) Sum (): To see the sum of price of the item_ID I003 SQL query is as
follows:
Self-Instructional
40 Material
(ii) Min (): To see the order detail where item price is minimum SQL Relational Database
Management Systems
query is as follows:
NOTES
(iii) Max (): To see the order detail where item price is maximum SQL
Select max (fee) from course where C_NAME= ‘MBA’;
(iv) Count (): To see the number of orders for item_ID I001 SQL
Select count (item_id) from order where item_ID = ‘I001’;
Self-Instructional
Material 41
Relational Database
Management Systems
NOTES
(v) Count (*): To see the number of records in a table SQL query is as
follows:
Self-Instructional
42 Material
Select count (c_code) from course; Relational Database
Management Systems
NOTES
(vi) Upper (): To converts the text to upper case SQL query is as
follows:
Self-Instructional
Material 43
Relational Database (vii) Lower (): To converts the text to upper case SQL query is as
Management Systems
follows:
NOTES
(viii) Round (n): To round of any number SQL query is as follows:
Self-Instructional
44 Material
(ix) Sqrt (n): It calculates square root value of number SQL query is Relational Database
Management Systems
as follows:
NOTES
Join Commands
To collect or see data from two or more tables Oracle provides Join command.
The Join command in SQL helps in fetching rows and columns from multiple tables
and also we can apply some condition while fetching records from multiple tables.
Oracle vs. ANSI Syntax
There are two syntaxes for join commands Oracle and ANSI. To join table in
Oracle syntax where clause is used. Where in ANSI format there is a separate
join clause which makes the query more clear and easy to read and understand.
There are mainly four types of joins that you need to understand. They are:
 CROSS JOINS
 INNER JOIN
 FULL JOIN
 LEFT JOIN
 RIGHT JOIN
(a) Cross Joins (Cartesian Product)
A cross join returns every row from the first table matched to every row in the
second. This will always return the Cartesian product of the two table’s rows i.e.,
the number of rows in the first table times the number in the second. Student and
project both store three rows. So cross joining those returns 3 * 3 = 9 rows.
Self-Instructional
Material 45
Relational Database To cross join tables using Oracle syntax, simply list the tables in the
Management Systems
‘from’ clause:
select *
from student, project;
NOTES
Using ANSI style, type cross join between the tables you want to
combine:
select *
from student
cross join project;
(b) INNER JOINS (Join)
An inner join is used to compares values in one or more columns from each. It
only returns rows which match the join conditions in both tables. The simplest join
checks if the values in a column from one table equal the values in a column from
the other. This join is also known as Equi join.
For example:
Create following tables Student, Marks and Project:
RNO NAME COURSE FEE
101 NAMAN B.tech 59000
102 AMAN B.tech 59000
103 SITA BCA 49000
105 GITA MCA 59000
Table: Student
RNO NAME COURSE FEE
101 NAMAN B.tech 59000
103 SITA BCA 49000
105 GITA MCA 59000
Table: Marks
Table: Project
Oracle Format
select student.rno, name, sub1, sub2, sub3, total
from student, marks
where student.rno=marks.rno;
Self-Instructional
46 Material
ANSI Format Relational Database
Management Systems
from student
inner join marks
NOTES
on student.rno= marks.rno;
(c) Outer Join:
An outer join returns all the rows from one table along with the matching rows
from the other. Rows without a matching entry in the outer table return null for the
outer table’s columns.
An outer join can either be left or right. The direction defines which side of
the join the database preserves the rows for.
There are three types of outer join:
 Left Outer Join
 Right Outer Join
 Full Outer Join
(i) Left Outer Join: The LEFT JOIN or the LEFT OUTER JOIN returns all the
records from the left table and also those records which satisfy a condition from
the right table. Also, for the records having no matching values in the right table,
the output or the result-set will contain the NULL values.
Oracle Format
from student,marks
where student.rno=marks.rno(+);
ANSI Format
select student.rno,name,sub1,sub2,sub3,total from student
left outer join marks on student.rno=marks.rno;
Output:
RNO NAME SUB1 SUB2 SUB3 TOTAL
101 NAMAN 50 40 40 130
103 SITA 60 40 40 140
105 GITA 50 40 50 140
102 AMAN - - - -
(ii) Right Outer Join: The RIGHT JOIN or the RIGHT OUTER
JOIN returns all the records from the right table and also those records which
satisfy a condition from the left table. Also, for the records having no matching
values in the left table, the output or the result-set will contain the NULL values.
Self-Instructional
Material 47
Relational Database Oracle Format
Management Systems
select student.rno, project.rno, name, pname
from student , project
where student.rno(+)=project.rno;
NOTES
ANSI Format
select student.rno, project.rno, name, pname from student
right outer join project on student.rno=project.rno;
RNO RNO NAME PNAME

102 102 AMAN Railway
- 106 - AI
(iii) Full Outer Join: Sometimes you may want to join two tables to find the
matching rows. But also include any unmatched rows from both tables i.e., a
“double outer” join. This is known as a full (outer) join.
select student.rno, project.rno, name, pname
from student full outer join project on
student.rno=project.rno;
RNO RNO NAME PNAME

102 102 AMAN Railway
103 - SITA -
105 - GITA -
101 - NAMAN -
- 106 - AI
Data Control Language (DCL)

Data Control Language are the commands that allow authorized database users
to share the data with other users. The shared data could be accessed or
manipulated by other users as per the permission granted to those users.
The data manipulation language statements are GRANT and REVOKE
 GRANT- Gives user’s access privileges to database.
 REVOKE- Withdraw user’s access privileges given by using the
GRANT command.
TCL: EXPERIMENTS USING TCL SQL STATEMENTS
Transaction Control Language (TCL)
Various transactions are being done by different users these transactions then could
be saved permanently or could be can called by the user. TCL commands manage
changes made by DML statements. The transaction control statements are
COMMIT, SAVEPOINT and ROLLBACK.
Self-Instructional
48 Material
Examples of TCL Commands: Relational Database
Management Systems
 COMMIT– Commits a Transaction.
 ROLLBACK– Rollbacks a transaction in case of any error occurs.
 SAVEPOINT– Sets a save-point within a transaction. NOTES
 SET TRANSACTION– Specify characteristics for the transaction.
Oracle Transactions:
All the changes that you make through DML command are known as transaction.
A transaction is a logical group of work. Transactions that you do on a database
temporarily stores data on the client machine that is either it could be made
permanent or could be cancelled by the user.
Oracle provides few commands to control the transactions as given below:
 Commit
 Save-Point
 Rollback
Commit: The commit command is used to make the transaction permanent
to the database the commit command ends the current transactions.
SQL > Commit to work;
The keyword work is optional which is used to increased readability only,
you could also write
SQL > Commit;
Rollback: The rollback command is used to terminate the current transaction
all the change made to the rollback database can be undone by rollback. It is
generally used when a session disconnects from the database without completing
the current transaction.
Example:
SQL > Rollback work;
The keyword work is optional which is used to increased readability only,
you could also write
SQL > rollback;
When commit command is executed Oracle prompts a message as shown
below:
Rollback complete.
* Rollback undone the whole transaction made after the last committed
transaction.
Self-Instructional
Material 49
Relational Database
Management Systems BLOCK 3: INDEX AND VIEW
This block will discuss about the following topics:

NOTES  Index : Experiment Using Database Index Creation, Renaming an
Index, Copying Another Index, Dropping an Index
 Views: Create Views, Partition and Locks
Index and View:
(a) Index: An index is a performance-tuning method of allowing faster retrieval
of records. An index creates an entry for each value that appears in the
indexed columns. By default, Oracle creates B-tree indexes.
(i) Creating an Index
The syntax for creating an index in Oracle/PL/SQL is:
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, column_n)
[COMPUTE STATISTICS];
In above syntax, UNIQUE refers that the combination of values in the
indexed columns must be unique, index_name is the name of index, table_name
is the name of table on which we are creating the index, column1, column2, ...
column_n refers to the columns to use in the index and COMPUTE
STATISTICS tells Oracle to collect statistics during the creation of the index.
The statistics are then used by the optimizer to choose a “Plan of Execution” when
SQL statements are executed.
Example: Let us look at an example of how to create an index in Oracle/PL/
SQL. For example:
CREATE INDEX employee_idx
ON employee (name);
In this example, we have created an index on the employee table called
employee_idx. We could also create an index with more than one field as in the
example below:
Self-Instructional
50 Material
Relational Database
Management Systems
NOTES
We could also choose to collect statistics upon creation of the index as

follows:
Self-Instructional
Material 51
Relational Database (ii) Rename an Index
Management Systems
The syntax for renaming an index in Oracle/PL/SQL is:
ALTER INDEX index_name
NOTES RENAME TO new_index_name;
Here, index_name is the name of the index that you wish to rename
and new_index_name is the new name to assign to the index.
Example:
Let us look at an example of how to rename an index in Oracle/PL/SQL.
For example:
(iii) Drop an Index

The syntax for dropping an index in Oracle/PL/SQL is:
DROP INDEX index_name;
Where index_name is the name of the index to drop.
Self-Instructional
52 Material
Example: Relational Database
Management Systems
Let us look at an example of how to drop an index in Oracle/PL/SQL. For example:
NOTES
View
A view is a virtual table, which consists of a set of columns from one or more
tables. It is similar to a table but it does not store in the database. View is a query
stored as an object. In SQL, a view is a virtual table based on the result-set of an
SQL statement. A “View Table” or just “View” makes the Select a long-lived
virtual table: no data storage in its own right, just window on data it selects from.
A view contains rows and columns, just like a real table. The fields in a view
are fields from one or more real tables in the database. You can add SQL functions,
WHERE, and JOIN statements to a view and present the data as if the data were
coming from one single table.
Syntax:
CREATE VIEW view_name AS SELECT set of fields FROM
relation_name WHERE (Condition)
Self-Instructional
Material 53
Relational Database CREATE VIEW student_view AS SELECT ROLL_NO, NAME, mobile
Management Systems from student;
NOTES
Display Records from View:

select * from student_view;
Self-Instructional
54 Material
DROP VIEW: This query is used to delete a view, which has been already Relational Database
Management Systems
created.
Syntax: DROP VIEW View_name;
Drop view student_view; NOTES
A view can a simple view or a complex view.

Differences between Simple VIEW and Complex VIEW:
Simple VIEW Complex VIEW
It contains only one table. It contains one or more number of tables
It does not contain aggregate function like Sum (), It can contain aggregate function.
Max () or any other aggregate function.
Insert and update commands can be performed Insert and update commands cannot be performed
through a Simple view. through a Simple view.
INSERT, DELETE and UPDATE are directly We cannot apply INSERT, DELETE and UPDATE
possible on simple view. on complex view directly.
It does not include NOT NULL columns from base NOT NULL columns that are not selected by
table. simple view can be included in complex view.
Self-Instructional
Material 55
Relational Database Complex View
Management Systems
Select * from student1
RNO NAME COURSE FEE

NOTES 101 NAMAN B.tech 59000
103 SITA BCA 49000
105 GITA MCA 59000
Select * from marks1
RNO SUB1 SUB2 SUB3 TOTAL

101 50 40 40 130
103 60 40 40 140
105 50 40 50 140
Example-1
Create view students_marks1 as
Select student1.rno, name, sub1, sub2, sub3, total from
student1, marks1
Where student1.rno=marks1.rno;
Select * from students_marks1;

101 NAMAN 50 40 40 130
103 SITA 60 40 40 140
105 GITA 50 40 50 140
Example-2
Create view students_marks2 as
Select student1.rno, name, sub1, sub2, sub3, total from
student1 left outer join marks1 on
student1.rno=marks1.rno;
Select * from students_marks2;

101 NAMAN 50 40 40 130
103 SITA 60 40 40 140
105 GITA 50 40 50 140
102 AMAN - - - -
Self-Instructional
56 Material
Example-3 Relational Database
Management Systems
Create view students_view as
Select student1.rno, project.rno, name, pname from student1
right outer join project on student1.rno=project.rno;
NOTES
Select * from students_view;
ORDER BY Clause (Sorting Records)
The ORDER BY clause is used to arrange data in ascending or descending
order. One or more fields can be used to arrange records.
Example-4
Select Rno, name
From student
ORDER BY name;
The above command will arrange name in ascending order. Asc and Desc
can be used along with order by clause to arrange records n ascending order,
which is default or Desc to arrange records in descending order.
Select Rno, name, Fee
From student
ORDER BY name Asc, fee Desc
BLOCK 4: EXCEPTION HANDLING AND PL/SQL
This block will cover the following topics:

 Exception Handling: PL/SQL Procedure for Application Using Exception
Handling
 Cursor: PL/SQL Procedure for Application Using Cursors
 Trigger: PL/SQL Procedure for Application Using Triggers
 Package: PL/SQL Procedure for Application Using Package
 Reports: DBMS Programs to Prepare Report Using Functions
PL/ SQL
Introduction:
PL/ SQL is also known as an embedded SQL and is a superset of SQL. PL/ SQL
is an acronym of Procedural Language/Structure Query Language. It supports
procedural features and SQL commands.
Self-Instructional
Material 57
Relational Database Structure of PL/ SQL Program:
Management Systems
PL/ SQL program block is divided in three sections, such as
1. Declaration Section
NOTES 2. Execution Section
3. Exception Handling Section
[DECLARE
--Declarations]
BEGIN
--Statements
[EXCEPTION
--Exception Handler]
END;
Description of the Blocks

(a) Declaration Section: In Declaration section variables, constants, user
defined exceptions, cursor and other objects are declared. This is an optional
section. This section begins with the key word DECLARE.
(b) Execution Section: All the executable statements, such as SQL statements,
control statements, loops are written under this section. This is a mandatory
section. This section begins with the key word BEGIN and ends with the
key word END.
(c) The Exception Handling Section: During program execution many
abnormal situations may occur. To handle those situations statements are
written in this block. These situations are known as errors which occur due
to the logical error, syntax error or system error. This is an optional section.
The PL/ SQL Syntax is as follows:
DECLARE
Declaration_Statements
…
BEGIN
Self-Instructional
58 Material
Executable_Statements Relational Database
Management Systems
…
EXCEPTION
Exception_Handling_Statements
NOTES
…
END ;
..
The PL/ SQL Engine:
Oracle uses a PL/ SQL engine to processes the PL/ SQL statements. Either the
PL/ SQL program is stored on the client side or on the server side PL/ SQL
engine is used by Oracle to execute the program statements.
Data Types in PL/ SQL:
A program has many inputs and outputs in the form of variable and constant.
These variable and constant specifies the storage format, type of value and a
range of the values that could be stored. PL/ SQL provides a various data types
which are system defined and also gives the flexibility to the programmer to create
their own data types which fit to the business needs. Classification of data types:
 Scalar Data Types
 Composite Data Types
Self-Instructional
Material 59
Relational Database Comments in PL/ SQL:
Management Systems
In Oracle, comments may be introduced either for single line or for multiple lines.
Types of Comments:
NOTES 1. /*...*/ is used for multiple line comments.
2. — is used for single line comments.
The Example for single line comment is given below:
— This is a PL/ SQL program to calculate employee
salary
Declare
…
Variables in PL/ SQL:
Variables are the identifiers of data type. These variables could be the identifiers
of either system defined (Scalar) data types or the identifiers of user defined
(Composite) data type i.e., record, table or Varray.
Variable declaration can be of any data type:
Example:
Name char (30);
Salary Number (8, 2);
Date_of_join Date;
Constants can be of any data type:
Example:
Pi constant number (3, 2) : = 3.5;
Status Booleans: = TRUE;
Pi and Status are assigned valued during declaration makes them constant.
Example of PL/ SQL Program:
Step-1: Write PL/SQL Program in SQL Commands as shown below:
Self-Instructional
60 Material
Example: Write a PL/SQL program to display ‘First PL/SQL Program’. Relational Database
Management Systems
DECLARE
BEGIN
DBMS_OUTPUT.PUT_LINE (‘First PL/SQL Program’);
NOTES
END;
Click on Run button to run program.

Output:
Example: Write a PL/SQL program to display sum of two numbers given at run
time.
Self-Instructional
Material 61
Relational Database After running this program it will show input screen as shown below:
Management Systems
NOTES
Enter values in text boxes and click on Submit button.

Output:
Example: Write a PL/SQL Program to print Prime Number.
Self-Instructional
62 Material
Input: Relational Database
Management Systems
NOTES
Output:
Example: Write a PL/SQL Program to Find Factorial of a Number given number.
Self-Instructional
Material 63
Relational Database Input:
Management Systems
NOTES
Output:
Description of above program is given below:

In Declaration Section three variables are declared named number_1, number_2
and res of number data type.
In Executable Section value in number_1 and number_2 variables are
taken by the user interactively. Here & symbol prompts the user to enter the value
and:= ( assignment operator ) is used to assign value to variables.
Value for res variable is calculated to produce the sum of number_1 and
number_2.
DBMS_OUTPUT.PUT_LINE is used to display output of a program.
Compile Procedure:
To execute any stored procedure, it is necessary to compile it. To compile a
procedure the following command is used:
Syntax: SQL> @ procedure_name ;
Self-Instructional
64 Material
Example: SQL> @ search_book ; Relational Database
Management Systems
If the procedure does not contain any error then the system would prompt
a message as follows: Procedure created.
NOTES
Try Yourself:
1. Write PL/SQL program to display demonstrate all sections of
PL/SQL program.
2. Write PL/SQL program to display HELLO.
Exception Handling:
In PL/ SQL error is called exception. Error may occur due to various reasons,
such as coding mistakes, hardware failure, system resources problems and many
other reasons. Due to these errors program terminates abnormally.
Type of Exception:
1. I nter nal Exception
2. User -Defined Exceptions
The following is the list of internal exceptions:
Exception Explanation
ZERO_DIVIDE This exception raised when PL/SQL program attempts to divide
a number by zero.
NO_DATA_FOUND This exception raised when SELECT INTO statement returns no
rows while expected to return.
CURSOR_ALREADY_OPEN This exception raised when you try to open a cursor which is
already.
INVALID_NUMBER This exception raised when, the conversion of a string into a
number fails because the string does not represent a valid
number.
LOGIN_DENIED This exception raised when PL/SQL program attempts to log on
to Oracle with an invalid username and/or password.
NOT_LOGGED_ON This exception raised when PL/SQL program issues a database
call without being connected to Oracle.
STORAGE_ERROR This exception raised when PL/SQL runs out of memory.
TOO_MANY_ROWS A SELECT INTO statement returns more than one row while
expected only one.
VALUE_ERROR This exception raised when data type or data size is invalid.
PROGRAM_ERROR This exception raised when PL/SQL has an internal problem.
OTHERS This exception raised when error is unknown or not explicitly
defined.
Exception handling program is based on book table as shown below:

B_CODE TITLE AUTHOR PRICE STATUS
B004 DBMS Korth 440 T
B006 DCN forouzan 500 T
B001 DBMS NAVATHE 400 T
B003 programming in c Kavita 340 T
Self-Instructional
Material 65
Relational Database Example: Write a program to demonstrate exception handling.
Management Systems
NOTES
Query returns more than one records then TOO_MANY_ROWS exception:
In the above program select query is used to select book title into variable
B_title. Two internal exceptions are handled named NO_DATA_FOUND and
TOO_MANY_ROWS. If query returns more than one records then
TOO_MANY_ROWS exception would be raised by the system, if no record
matches then NO_DATA_FOUND exception would be raised.
Example:
Write a program to demonstrate User-named Exception
handlers.
You could assign a name to unnamed system exceptions using a Pragma
called Exception_Init as shown below:
Pragma Exception_Init (Exception Name, Oracle Error
Number);
In the above example, exception name is the user defined name of the
exception that will be associated with Oracle error number.
Syntax:
DECLARE
Exception_Name EXCEPTION ;
PRAGMA EXCEPTION_INIT (Exception_Name , Err_Code
) ;
Self-Instructional
66 Material
BEGIN Relational Database
Management Systems
Executable Statement;
. . .
EXCEPTION
NOTES
WHEN exception_name THEN
Handle the Exception
END;
Example: Let us consider the student table and course tables.
The c_code is a primary key in course table and c_code is a foreign key in
student table.
If you try to delete a c_code from course table and it has a corresponding
child records in student table an exception will be thrown with oracle code number
-2292. You could assign a user defined name to this exception that could be handled
in the exception block as given below:
Example:
As user could assign name to the Oracle exceptions in the above example
child_record_exception is a user defined name of exception.
RAISE_APPLICATION_ERROR ( )
A user could assign an error message by using
Raise_application_error ( ) to make the error message more
descriptive for the end-user. Raise_application_error ( ) is a build-in procedure.
Self-Instructional
Material 67
Relational Database Example: Write a PL/SQL program to demonstrate User-defined
Management Systems
Exceptions.
Other than the pre-defined exceptions you could define your own exception
to validate data against business requirements. For example, if user wants to update
NOTES
total marks of student but subject marks are NULL an error must be raised the
system to alert the user.
A user defined exceptions must be declared within declaration section by
the keyword EXCEPTION and must be raised explicitly by RAISE statement
within the executable section.
Create Table Marks:
Create table marks (roll_no number(3), sub1 number(3),
sub2 number(3), sub3 number(3), total number(3) )
Insert values in roll_no, sub1, sub2, sub3 fields only:
Insert into marks (roll_no, sub1, sub2, sub3) values (101, 34, 54, 43)
Select * from marks;
R OLL_NO S UB1 SUB2 SUB3 TOTAL

101 34 54 43 -
102 54 54 50 -
104 65 44 40 -
Example:
Self-Instructional
68 Material
In the above example null_marks is a user defined exception which must Relational Database
Management Systems
be raised explicitly using RAISE statement. This exception would be raised when
marks in any subject would be NULL.
After executing above program check students marks:
NOTES
Select *from marks;
R OLL_NO SUB 1 SUB 2 SUB 3 T OT AL

101 34 54 43 -
102 54 54 50 158
104 65 44 40 -
Try Yourself
 Write a PL/SQL code block that will accept an account number from the
user and debit an amount of 2000 from the account if the account has
a minimum balance of 500 after the amount is debited. The Process is
too fired on the Accounts table.
 Write a PL/SQL code block to calculate the area of the circle for a value
of radius varying from 3 to 7. Store the radius and the corresponding
values of calculated area in a table Areas. (Usage While loop)
Areas – radius, area.
 Write a PL/SQL block of code for inverting a number 5639 or 9365.
(Usage For loop)
 Usage of for and goto Statement: Write a PL/SQL block of code
to achieve the following: if the price of Product ‘p00001’ is less than
4000, then change the price to 4000. The Price change s to be recorded
in the old_price_table along with Product_no and the date on which the
price was last changed. Tables involved: product_master- product_no,
sell_price.
Old_price_table- product_no,date_change, Old_price
Cursor
Oracle allocates a memory known as the context area for the processing of the
SQL statements. A cursor is a pointer or handle to the context area. Through the
cursor, a PL/SQL program can control the context area and what happens to it as
the statement is processed.
The three types of the cursors are:
 Static Cursors
 Dynamic Cursors
 REF Cursors
Self-Instructional
Material 69
Relational Database Static cursors are the ones whose select statements are known at the compile
Management Systems
time. These are further classified into
 Explicit Cursors
NOTES  Implicit Cursors
Use marks table to practice cursor:
Select * from marks;
ROLL_NO SUB1 SUB2 SUB3 TOTAL
101 34 54 43 131
102 54 54 50 158
104 65 44 40 149
Cursors:
Q. Create a cursor to show roll number and total marks of students from
marks table using cursor.
Trigger:
A trigger is a PL/ SQL code block that triggered (runs) automatically an event. An
event in PL/ SQL is the Data Definition Language such as INSERT, UPDATE or
DELETE done on a table.
Use of a Trigger
A database trigger helps in maintaining the organization’s database in such a manner
Self-Instructional that without executing the PL/ SQL code explicitly it update and validate the data.
70 Material
Triggers have the capabilities to provide a customized management system of your Relational Database
Management Systems
database.
Database trigger can be used to cater the following purposes:
 To enforce integrity constraints (for example, check the referenced data NOTES
to maintain referential integrity) across the clients in a distributed database
 To prevent generate invalid transactions in database.
 To update data automatically to one or more tables or views without
user interaction
 Automatically generate derived column values
 To customize complex security authorizations.
 To permit insert, update or delete operations to an associated table only
during predetermined a date and time.
 Provide auditing
 Provide transparent event logging
 Helps in prompting information about various events taken on database,
events of users, and SQL statements to subscribe applications.
 Helps in maintaining replication of synchronous table
 Helps in gathering statistics on various table accesses.
Structure of PL/SQL Trigger
Like a PL/SQL code block procedure and function also divided into different
sections.
The Syntax for creating a trigger
CREATE [OR REPLACE]
TRIGGER <trigger_name>
BEFORE (or AFTER)
INSERT OR UPDATE [OF COLUMNS] OR DELETE
ON table_name
[FOR EACH ROW [WHEN (condition)]]
DECLARE
Declaration Statements
…
BEGIN
Executable Statements
...
EXCEPTION
Exception Handling Statement
…
END;
A database trigger could also have declarative and exception handling parts. Self-Instructional
Material 71
Relational Database How to Apply Trigger:
Management Systems
A database trigger has three sections namely a trigger statement, a trigger body
and a trigger restriction. Three of Parts of Trigger:
NOTES 1. A Trigger Statement
2. A Trigger Body Action
3. A Trigger Restriction
The above mentioned parts are described below:
Create a Trigger
A company XYZ has the employee detail in employee table. Company wants to
have the history of all the employees how have left the organization. To store the
employee history a new table emp_history is create with the same structure as
employee table.
The structure of employee table is shown below:
EMP_CODE NUMBER 10
E_NAME Varchar2 15
DESIGNATION Varchar2 35
SALARY NUMBER 10,2
DEPTNO NUMBER 2
The employee table contains the following records:

EMP_CODE E_NAME DESIGNATION SALARY DEPTNO
7369 SMITH CLERK 15000 20
7499 ALLEN SALESMAN 35000 30
7521 WARD SALESMAN 32000 30
7566 JONES MANAGER 55000 20
7654 MARTIN SALESMAN 30000 30
7698 BLAKE MANAGER 60000 30
7782 CLARK MANAGER 64000 10
7788 SCOTT ANALYST 58000 20
7839 KING PRESIDENT 70040 10
7844 TURNER SALESMAN 30430 30
7876 ADAMS CLERK 23000 20
Table- Employee
Create a Duplicate Table:
To maintain the employee history a table emp_history could be created with the
SQL command as written below:
SQL> Create table emp_history as select * from employee
where emp_code is null;
The above command would create a new table emp_history which would
Self-Instructional contaion all the fields of employee table (as * represents all the fields of a table).
72 Material
The where condition “emp_code is null” is used to create the duplicate table empty. Relational Database
Management Systems
* Without where clause duplicate table would contain all the records of
employee table.
After writing the above command system would prompt a message Table NOTES
created.
You could see the structure of new table emp_history by giving command
as written below:
SQL> Desc emp_history;
EMP_CODE NUMBER 10
E_NAME Varchar2 15
DESIGNATION Varchar2 35
SALARY NUMBER 10,2
DEPTNO NUMBER 2
Table- Emp_History
When any employee leaves the organization his or her detail would be deleted
from the employee table and the same record should be inserted into emp_history
table. A trigger could be associated on table employee on the event delete. The
code for trigger is given below:
Example-1: Before Delete Trigger:
Self-Instructional
Material 73
Relational Database In the above example, emp_history is a trigger which is associated with
Management Systems
the employee table. This is a trigger would fire on delete command on employee
table and would store the deleted record in emp_history table.
Now to test it:
NOTES
To test whether the trigger is fired and insert the deleted record in emp_history
table delete few records from employee table as shown below:
SQL> delete from employee where emp_code = 7782;
The above command would delete a record from employee table where
emo_code is 7782. Now check whether this record has been inserted in to
emp_history table or not write the following command on SQL prompt:
Select *from emp_history;
The above command would prompt the record as shown below:
EMP_CODE E_NAME DESIGNATIO SALARY DEPTNO
N
7782 CLARK MANAGER 64000 10
7876 ADAMS CLERK 23000 20
7844 TURNER SALESMAN 30430 30
Table- Emp_History
Example-2: Before Insert Trigger
In the below example, a trigger is associated with the employee table. This trigger
would fire before inserting a new record in the table.
Self-Instructional
74 Material
In the above example, insert_emp is a trigger which is associated with the Relational Database
Management Systems
employee table. This is a trigger would fire on insert command on employee table
and would prompt new employee code and employee name before inserting it in
to employee table.
NOTES
Now to test it:
To test whether the trigger is fired and display message on screen, insert new
record in to employee table as shown below:
SQL> Insert into employee (emp_code, e_name) values
(321,’Scott’);
When new record is inserted in to employee table system prompts the
message as shown below:
New employee Code inserted is: 321
New employee Name inserted is: Scott
The trigger would execute even if you insert data in all the fields of employee
table.
IF Statement in Trigger
As to control the PL/ SQL code execution if statement is used, a database trigger
also use if statement. If statements in database triggers is used to determine what
statement caused the execution of the trigger, such as inserting, updating or deleting
a data from the associated table.
The general form of if statements in trigger are:
 If Inserting Then
 If Deleting Then
 If Updating Then
An Example of is statement in trigger is given below:
Self-Instructional
Material 75
Relational Database In the above example, emp_trigger is a database trigger which is associated
Management Systems
with the employee table. This is a trigger has three if conditions where if conditions
are used to determine what statement is invoked, and what prompts an appropriate
message in various cases.
NOTES
Different Conditions of Trigger Execution
1. Insert record in to employee table:
Syntax: SQL> insert into employee (emp_code, e_name,
designation) values (1001,’xyz’, ‘manager’);
When inserting a record in to employee table the first condition is true and
the system would prompt a message as shown below:
Inserting Employee xyz
New employee Number inserted is: 1001
New employee Name inserted is: xyz
1 row created.
Deleting Employee KING
2. Delete record from employee table:
Syntax: SQL> d e l e t e from employee where
emp_code=7839;
When deleting a record from employee table, the second condition is true
and the system would prompt a message as shown below:
Deleting Employee KING
1 row deleted.
3. Update record from employee table:
Syntax: SQL> update employee set e_name= ‘Spark’
where emp_code=7934;
When updating a record from employee table, the third condition is true
and the system would prompt a message as shown below:
Updating Employee MILLER to Spark
1 row updated.
Viewing Triggers
To view all the triggers created by the user, a data dictionary named
USER_TRIGGERS could be used. To see all the triggers, use select statement
on USER_TRIGGERS as shown below:
Select trigger_name from user_triggers;
Self-Instructional
76 Material
Relational Database
Management Systems
NOTES
For more description, you could also write the following command:
SQL> Select * from user_triggers;
Deleting a Trigger
If trigger is more required, you can get side of it by using a Data Definition Language
command Drop.
Syntax:
SQL > Drop trigger < trigger name >
Example:
Self-Instructional
Material 77
Relational Database PL/ SQL Package:
Management Systems
A package is a database object. It is a collection of various database objects as
procedures, functions, cursors, variables and constants.
NOTES There are two types of packages:
1. Built-in Packages
2. User-Defined Packages
Built-in Packages: Built-in packages, such as DBMS_OUTPUT,
DBMS_SQL, DBMS_DDL, DBMS_TRANSACTION, etc., caters pre-defined
functionality.
User-Defined Packages: User defined package serve the user as per the
changed business needs. A package consists of two parts:
 Package Specification
 Package Body
(a) Package Specification: In package specification one could declare variables,
constants, exceptions, cursors, sub-procedures and other database objects as
mentioned earlier. To package specification could be created using the CREATE
PACKAGE statement. The Syntax to create package specification is as follows:
CREATE [ or Replace ] Package < package_name > IS <
declarations >
Begin
( Executable statements )
END <package_name > ;
The sub-procedures declared in package specification must be declared in
package body.
(b) Package Body: The actual implementation of declared sub-procedures and
cursors is done in package body . The sub-procedures declared in package
specification must be declared in package body. The Syntax for the CREATE
BODY statement is as follows :
CREATE [or Replace] package < package_name > IS <
declarations >
Procedure < procedure_name > (variable data type);
Function < function_name > (variable data type) return
data type ;
END < body_name> ;
Self-Instructional
78 Material
A Package Function: The example given below declares a function Relational Database
Management Systems
getGrade which would accept an argument of varchar data type and would return
a value of varchar data type.
Example:
NOTES
Step-1
The above code would create a package with the name pkg_marksheet.
This package contains a function named getGrade. This function will accept an
argument of varchar type and will return a value of varchar type.
Package created.
Step-2: The function pkg_marksheet is declared in package body as shown below:
create or replace package body pkg_marksheet as
function getgrade (rno varchar ) return varchar IS
s1 number (3) ;
s2 number (3) ;
s3 number (3) ;
s4 number (3) ;
total number (3) ;
per number (3) ;
begin
select sub1, sub2, sub3, sub4 into s1, s2, s3 , s4
from marks where roll_no = rno ;
total := s1 + s2 + s3 + s4 ;
per := total / 4 ;
Self-Instructional
Material 79
Relational Database if per >= 90 then
Management Systems
return ‘A+’ ;
elsif per >= 80 then
return ‘A’ ;
NOTES
return ‘A-’ ;
return ‘B+’ ;
return ‘B’ ;
return ‘B-’ ;
return ‘C’ ;
else
return ‘F’ ;
end if ;
end getgrade ;
end pkg_marksheet ;
/
The output of the above PL/ SQL code when compiled is given below:
Package body created.
Calling Package Function: To call the function declared in package
specification the reference of package name need to give as given below. The
Syntax to call a package function is as follows :
Self-Instructional
80 Material
The Example to call a package function is as follows: Relational Database
Management Systems
pkg_marksheet.getGrade (‘A-08-12’);
Where pkg_marksheet is a package name in which a function getGrade is
declared which takes a varchar argument A-08-12. NOTES
A Package Procedure: The example given below declares a procedure
show_book_price which would accept an argument of varchar data type.
Example :
Step-1
The above code would create a package with the name book_price. This
package contains a procedure named show_book_price. This procedure will
accept an argument of varchar type.
* Procedure cannot return any value.
The output of the above PL/ SQL code when compiled is given below :
Self-Instructional
Material 81
Relational Database Package created.
Management Systems
Step-2
NOTES
Save the above program with the any name (let us suppose show_price)
and then run it by using :
The output of the above PL/ SQL code when compiled is given below :
Package body created.
Calling Package Procedure: To call the procedure declared in package
specification the reference of package name need to give as given below :
The Syntax to call a package procedure is as follows:
Package_name.procedure_name;
The Example to call a package procedure is as follows:
book_price. show_book_price ( ‘B003’ );
Where book_price is a package name in which a procedure
show_book_price is declared which takes a varchar argument B003.
Reports Using Functions:
A stored function always returns a result and can be called inside an SQL statement
just like ordinary SQL function. A function parameter is the equivalent of the IN
procedure parameter, as functionals use the RETURN keyword to determine
Self-Instructional
82 Material
what is passed back. User-defined functions or stored functions are the stored Relational Database
Management Systems
procedures which have the features of all procedures. They can accept parameters,
perform calculations based on data retrieved and return the result to the calling
SQL statement, procedure, and function or PL/SQL program. A function returns
a value. NOTES
Create a Function
The syntax to create a function is as follows:
CREATE OR REPLACE FUNCTION function_name ( function_params
)
RETURN return_type IS
Declaration statements
BEGIN
Executable statements
RETURN something_of_return_type ;
EXCEPTION
Exception section
END;
Description of the Syntax
CREATE Function: This is used to create a function, if no other function with the
given name exists.
OR REPLACE Function: OR REPLACE is used to re-create the function
if the given function name already exists. If no function exists with the given name,
it creates the new function. You can also use OR REPLACE clause to change the
definition of an existing function without dropping, re-creating and regranting
privileges previously granted on the function to other users. If you redefine a function,
then Oracle Database recompiles it.
IS: It is similar to DECLARE in PL/SQL Blocks. Variables could be declared
between IS and BEGIN.
RETURN: Clause Function returns a value. The RETURN clause is used
to specify the data type of the return value of the function. Since every function
must return a value, this clause is mandatory to use. The return value can have any
data type supported by PL/SQL.
Example: Functions can be very useful in many situations. For example,
functions can be useful when you need to calculate the total monthly sale in different
areas and of different items. Or you want to calculate the expenses of an
organization. In such instances functions are useful. Consider Table, which contains
the detailed of accounts of account holders of bank.
Self-Instructional
Material 83
Relational Database Table: Account_Holder
Management Systems
NOTES
A stored function is given to return the balance of an account holder. The

account number is passed as a parameter in this function.
Function: get_balance ()
/* This is a stored function which returns the total
balance of all saving accounts*/
CREATE or replace FUNCTION get_balance ( no IN NUMBER)
RETURN NUMBER
IS acc_bal NUMBER ( 11 , 2 ) ;
BEGIN
SELECT sum ( ac_balance ) INTO acc_bal from account_holder
WHERE acc_no = no ;
RETURN ( acc_bal ) ;
END;
/
The given function, get_balance () has a parameter of number type to accept
the account holder’s account number. The acc_bal is a variable in which the balance
of the given account holder is stored and returned to the caller program.
Save File: Save the above file with the name account_balance.SQL
Compile Function: To execute any stored procedure it is necessary to
compile it. To compile a procedure the following command is used:
The syntax is as follows:
SQL> @ function_name ;
For example,
SQL> @ account_balance ;
Self-Instructional
84 Material
Relational Database
BLOCK 5: APPLICATION DEVELOPMENT Management Systems
This block will deal with Design and Development of various Applications including,
Library information system, Students mark sheet processing, Telephone directory NOTES
maintenance, Gas booking and delivering, Electricity bill processing, Bank
Transaction, Pay roll processing. Personal information system, Question database
and conducting Quiz and Personal diary.
Library Information System:
Tables
Book_Details
Binding_Details
Category_Details
Borrower_Details
Student_Details
Staff_Details
Student_Details
Shelf_Details
Library Management System (SQL Commands)
Creating Table “Book_Details”:
CREATE TABLE Book_Details
(
ISBN_Codeint PRIMARY KEY,
Book_Titlevarchar(100),
Language varchar(10),
Binding_Idint,
No_Copies_Actualint,
No_Copies_Currentint,
Category_idint,
Publication_yearint
)
Inserting Some Data in “Book_Details”:
1. INSERT INTO Book_details
2. VALUES(‘0006’,’Programming Concept’,’English’,2,20,15,2,2006);
Creating Table “Binding_Details”:
1. CREATE TABLE Binding_details
2. (
3. Binding_idint PRIMARY KEY,
Self-Instructional
Material 85
Relational Database 4. Binding_Namevarchar(50)
Management Systems
5. )
Describe Binding Table:
NOTES Describe binding_details
Inserting Some Data in Binding Table:
1. I N S E R T I N T O B i n d i n g _ D e t a i l s V A L U E S
(1,’McGraw Hill);
2. I N S E R T I N T O B i n d i n g _ D e t a i l s V A L U E S
(2,’BPB Publication’);
All Data of Binding Table:
1. select *from binding_Details
Creating Relationship between Book and Binding Table:

1. ALTER TABLE Book_details
2. ADD CONSTR AINT Binding _ID_FK FOREIGN KEY
(Binding_Id)REFERENCESBinding_Details
(Binding_Id);
Checking Relationship:
1. selectb.Book_Title, e.binding_name
2. fromBook_Detailsb, Binding_Details e
3. whereb.binding_id = e.binding_id;
Creating Category Table:

1. CREATE TABLE Category_Details
2. (
3. Category_Idint PRIMARY KEY,
4. Category_Namevarchar(50)
5. )
Self-Instructional
86 Material
Inserting Some Data in Category Table: Relational Database
Management Systems
1. I N S E R T I N T O C A T E G O R Y _ D E T A I L S V A L U E S
(1,’Database’);
2. I N S E R T I N T O C A T E G O R Y _ D E T A I L S V A L U E S NOTES
(2,’Programming Language’);
Building Relationship between Book & Category Table:
1. ALTER TABLE Book_details
2. ADD CONSTRAINT Category_Id_FK FOREIGN KEY
(Category_ Id) REFERENCES Category_Details
(Category_Id);
Checking Relationship:
1. selectb.Book_Title,e.Category_Name
2. fromBook_Detailsb,Category_Details e
3. whereb.binding_id = e.Category_id;
Creating Borrower Table:

1. CREATE TABLE Borrower_Details
2. (
3. Borrower_Idint PRIMARY KEY,
4. Book_Idint,
5. Borrowed_From date,
6. Borrowed_TO date,
7. Actual_Return_Date date,
8. Issued_byint
9. )
Inserting Some Data in Category Table:
1. I n s e r t i n t o B O R R O W E R _ D E T A I L S V A L U E S
(1,0004,’01-Aug-2014',’7-Aug-2014',’7-Aug
2014',1)
2. Insert into BORROWER_DETAILS VALUES(2,6,’02-
Aug-2014',’8-Aug-2014',NULL,1)
Building Relation between Book & Borrower Table:
1. ALTER TABLE Borrower_details ADD CONSTRAINT
Book_Id_FK FOREIGN KEY(Book_Id) REFERENCES
Book_Details(ISBN_Code); Self-Instructional
Material 87
Relational Database Checking Relationship:
Management Systems
1. s e l e c t B o r r o w e r _ D e t a i l s . B o r r o w e r _ i d ,
Book_Details.Book_title
NOTES 2. fromBorrower_Details,Book_Details
3. w h e r e B o r r o w e r _ D e t a i l s . b o o k _ i d =
Book_Details.ISBN_Code
1. ALTER TABLE Borrower_Details

2. A D D C O N S T R A I N T I s s u e d _ b y _ F K F O R E I G N K E Y
(Issued_by) REFERENCES Staff_Details(Staff_Id);
Creating Staff Table:
1. CREATE TABLE Staff_Details
2. (
3. Staff_Idint PRIMARY KEY,
4. Staff_Namevarchar(50),
5. Password varchar(16),
6. Is_Adminbinary_float,
7. Designation varchar(20)
8. )
Inserting Some Data in Staff Table:
1. I n s e r t i n t o S T A F F _ D E T A I L S v a l u e s
(1,’Tarek Hossain’, ’1234asd’,0,’Lib_mgr’);
2. I n s e r t i n t o S T A F F _ D E T A I L S v a l u e s
(2,’Md.Kishor Morol’, ’iloveyou’,0,’Lib_clr’);
All Data of Staff Table:
1. select * from staff_details
Creating Student Table:

1. Create TABLE Student_Details
2. (
Self-Instructional 3. Student_Idvarchar(10) PRIMARY KEY,
88 Material
4. Student_Namevarchar(50), Relational Database
Management Systems
5. Sex Varchar(20),
6. Date_Of_Birth date,
7. Borrower_Idint, NOTES
8. Department varchar(10),
9. contact_Numbervarchar(11)
10. )
Inserting Some Data in Student Table:
1. Insert into STUDENT_DETAILS values (’13-23059-
1',’ Ahmed,Ali’,’Male’,’05-Oct-1995',1,’CSSE’,
’01681849871');
2. Insert into STUDENT_DETAILS values (’13-23301-
1', ’MOrol MD.Kishor’,’Male’,’03-Jan-1994',2,
’CSE’,’01723476554');
All Data of Student Table:
1. select *from student_details
Building Relationship between Student and Borrower Table:

1. ALTER TABLE student_details
2. ADD CONSTRAINT borrower_id_FK FOREIGN KEY
(Borrower_Id) REFERENCES Borrower_Details(Borrower_Id);
Checking Full Relationship:
1. s e l e c t s t u d e n t . s t u d e n t _ i d , s t u d e n t .
student_name,book.Book_ Title, staff.
staff_name, b.Borrowed_To
2. f r o m s t u d e n t _ D e t a i l s s t u d e n t , S t a f f _
Detailsstaff, Borrower_Detailsb,book_
details book
3. w h e r e s t u d e n t . B o r r o w e r _ i d = b . B o r r o w e r
_id an d b o o k . I S B N _ C o d e = b . b o o k _ i d a nd
b.Issued_by = staff.Staff_id;
Self-Instructional
Material 89
Relational Database Adding Shelf Table:
Management Systems
1. Create Table Shelf_Details
2. (
NOTES 3. Shelf_idint PRIMARY KEY,
4. Shelf_Noint,
5. Floor_Noint
6. );
Inserting some Data from Shelf Table:
1. Insert into Shelf_DetailsValues (1, 1, 1);
All Data in Shelf Table:
1. select*from Shelf_Details;
Adding Relationship between Shelf and Book Table:

1. ALTER TABLE Book_Details
2. ADD(Shelf_Idint);
3.
4. UPDATE Book_Details set Shelf_Id = 1
5. where ISBN_CODE = 4;
6.
7. UPDATE Book_Details set Shelf_Id = 2
8. where ISBN_CODE = 6;
9.
10. ALTER TABLE Book_Details
11. A D D C O N S T R A I N T S h e l f _ I d _ F K F O R E I G N K E Y
(Shelf_Id) REFERENCES Shelf_Details
(Shelf_Id);
Combined All Relationship:
1. s e l e c t s t u d e n t . s t u d e n t _ i d , s t u d e n t .
student_name, book.Book_Title, staff.staff
Self-Instructional _name, b.Borrowed_To, shelf.shelf_No
90 Material
2. f r o m s t u d e n t _ D e t a i l s s t u d e n t , S t a f f _ Relational Database
Management Systems
Det ail s s t a f f , B o r r o w e r _ D e t a i l s b , bo ok_
detailsbook, Shelf_Details shelf
3. w h e r e s t u d e n t . B o r r o w e r _ i d = b . B o r r o w e r _
NOTES
id and b o o k . I S B N _ C o d e = b . b o o k _ i d a nd
b.Issued_by = staff.Staff_id and book.
Shelf_Id = shelf.Shelf_Id;
Student marks sheet processing

Telephone directory
Gas booking and delivering
Electricity bill processing
Bank transaction
Payroll processing
Personal information system
Question database and conducting quiz and personal diary
Implement the Hospital Database and execute the given queries:
Self-Instructional
Material 91
Relational Database Relations (Include All the Necessary Integrity Constraints):
Management Systems
BILLED
BILL NO NUMBER(5) - PRI KEY
PATIENT_NO NUMBER(9)
NOTES ITEM_CODE NUMBER(5)
CHARGE NUMBER(7,2)
TREATS
PHY_ID NUMBER(4) - PRI KEY
PATIENT_NO NUMBER(4) - PRI KEY
PROCEDURE_NO NUMBER(4) - PRI KEY
DATE_TREATED DATE - PRI KEY
TREAT_RESULT VARCHAR2(50)
ITEM
ITEM_CODE NUMBER(4) - PRI KEY
DESCRIPTION VARCHAR2(50)
NORMAL_CHARGE NUMBER(7,2)
PHYSICIANS
PHY_ID NUMBER(4) - PRI KEY
PHY_PHONE CHAR(8)
PHY_NAME VARCHAR2(50)
PATIENT
PATIENT_NO NUMBER(4) - PRI KEY
DATE_ADMITTED DATE
DATE_DISCHARAGED DATE
PAT_NAME VARCHAR2(50)
ROOM_LOCATION CHAR(4)
ROOM
ROOM_LOCATION CHAR(4) - PRI KEY
ROOM_ACCOMODATION CHAR(2)
ROOM_EXTENSION NUMBER(4)
PROCEDURES
PROCEDURE_NO NUMBER(4) - PRI KEY
PROC_DESCRIPTION VARCHAR2(50)
Self-Instructional
92 Material

UG - B.Sc. - Computer Science - 130 54 - Relational Database Management Systems - Binder - 8455

Uploaded by

Copyright:

Available Formats

UG - B.Sc. - Computer Science - 130 54 - Relational Database Management Systems - Binder - 8455

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

UG - B.Sc. - Computer Science - 130 54 - Relational Database Management Systems - Binder - 8455

Uploaded by

Copyright:

Available Formats

ALAGAPPA UNIVERSITY

[Accredited with ‘A+’ Grade by NAAC (CGPA:3.64) in the Third Cycle

B.Sc. [Computer Science]

LAB: RELATIONAL DATABASE

Vikas® is the registered trademark of Vikas® Publishing House Pvt. Ltd.

BLOCK 1: TABLE MANIPULATION

BLOCK 2: SQL QUERIES AND SUB QUERIES

BLOCK 3: INDEX AND VIEW

BLOCK 4: EXCEPTION HANDLING AND PL/SQL

BLOCK 5: APPLICATION DEVELOPMENT

A Relational Database Management System (RDBMS) is a collection of

BLOCK 1: TABLE MANIPULATION

This block will cover the following topics:

Emp_ID Name Designation Salary

Click on Go to Database Home Page

The SQL command to create table is as follows:

The above command will create a table structure to store student’s

The above command will remove the course tables.

2. Write SQL Queries to add address column in student tables.

To change data type of an existing column

To Delete any Column

This block will discuss about the following topics:

In the above example UNIQUE constraints are set at table level.

The SQL command to create table with PRIMARY KEY constraint is as

Note: drop table book created earlier.

Note: drop Student table then create Student

1. Add five records in course table

INSERT FEW MORE RECORDS:

Insert Data with User Interaction

Check Your Progress

Display Table Records

To View Selective Columns

Update Table Records

The Example for delete command:

Is Null/ Is Compare if a value is Select * from student where contact_no IS NULL;

Note: In a pattern matching operator LIKE, the underscore character ( _ )

MINUS Returns all distinct rows Select roll_no,b_code from issue

Concatenation Operator: Concatenation operator is used to concatenate

Filtering Records Using Where Conditions

Conditional Operators in SQL:

C_CODE C_NAME DURATION FEE

(viii) Round (n): To round of any number SQL query is as follows:

RNO RNO NAME PNAME

RNO RNO NAME PNAME

Data Control Language (DCL)

This block will discuss about the following topics:

We could also choose to collect statistics upon creation of the index as

(iii) Drop an Index

Display Records from View:

A view can a simple view or a complex view.

It contains only one table. It contains one or more number of tables

RNO NAME COURSE FEE

RNO SUB1 SUB2 SUB3 TOTAL

RNO NAME SUB1 SUB2 SUB3 TOTAL

RNO NAME SUB1 SUB2 SUB3 TOTAL

BLOCK 4: EXCEPTION HANDLING AND PL/SQL

This block will cover the following topics:

Description of the Blocks

Click on Run button to run program.