LAB COURSE FILE

B.TECH
DATABASE MANAGEMENT SYSTEM LAB
COURSE CODE:-BCSE2014
Room no. :

School of Computing Science & Engineering

1
______________________
DIVISION :

CONCERNED MEMBER NAME CONTACT DETAILS

PROGRAM CHAIR Prof. Sanjeev Pippal 9971582888

DIVISION CHAIR

FACULTY IN-CHARGE Dr.S.Vijayalakshmi 9990353845

LAB TECH

2
VISION AND MISSION OF GU

GU’s Vision

"To be known globally for education, research, and innovation at the intersection of disciplines"

GU’s Mission
● TO PLACE ALL STAKEHOLDERS in pursuit of perfection, discovery and innovation in
one’s own discipline, while being aware that all one does, emerges from and integrates into a
wider common human good.

● TO FOSTER a culture of openness and preparedness towards cutting-edge future roadmap

and emerging knowledge blocks with a constant eye on applicability and scalability.

● TO DEVELOP and instill a culture of continual multi-disciplinary and interdisciplinary

enquiry and education among all stakeholders, leading to the evolution of an education model
that focuses on experiential learning to prepare students to lead, through the challenges of a
rapidly changing world.

● TO DEVELOP a learning orientation across the entire university ecosystem that goes beyond
domain knowledge so as to ensure that learning and problem-solving continue for life.

● TO ENABLE the finest student and faculty bodies to learn together, through the most evolved
education processes in an atmosphere of rigour and discipline, which emerges from and
integrates with real life industry, science and commerce processes; so as to develop an expert
body that understands all aspects of societal challenges; and works with teams to tackle
complex problems that directly benefit society.

3
VISION AND MISSION OF SCHOOL

VISION OF SCSE

To be known globally a premier school for value-based education, multidisciplinary research and
innovation

MISSION OF SCSE

M1: Create a strong foundation on fundamentals of SCSE through OB-TLP

M2: Establish state-of-the-art facilities for Analysis, Design and Implementation to develop
sustainable ethical solutions

M3: Conduct multidisciplinary research for developing innovative solutions

M4: Involve the students in group activity including that of professional bodies to develop leadership
and communication skills

4
 PROGRAMME OUTCOMES

To
introduce
Co
upcoming
De nd Pr
En domains
sig uct oje
Th vir like
n/ inv Ind ct
En e on To artificial
de est M ivi ma Lif
gin Pro en me Co train intelligence
vel iga od du na e-
eer ble gin nt m studen , Robotics,
op tio ern Et al ge lon
ing m eer an mu ts in Augmented
Program Outcome→ me ns too hic or me g
Kn ana an d nic trendi reality,
nt of l s tea nt Le
ow lysi d sus ati ng Data
of co usa m an arn
led s soc tai on techno analytics,
sol mp ge wo d ing
ge iet na logies. Ubiquitous
uti lex rk fin
y bili Computing
on pro an
ty to develop
s ble ce
insights for
ms
problem
solving.

P P P
Course Course P P P P P P P P P
O O O PSO1 PSO2
Code Name O O O O O O O O O
1 1 1
1 2 3 4 5 6 7 8 9
0 1 2

5
 COURSE OUTCOMES

Course Outcomes (COs)

CO1 Create database objects and enforce integrity constraints on a
S5(Naturalization)
database DDL commands.
CO2 Write statements to insert and query a database using DML
S4(Articulation)
commands
CO3 Perform functions and Operators on database. S3(Precision)
CO4 Write queries including Join/grouping of data/view. S4(Articulation)
CO5 Perform Data Control and Transaction processing commands. S3(Precision)
CO6 Write PL/SQL programming including procedures, functions,
S4(Articulation)
cursors.

Mapping CO-PO
CO/PO Mapping
(S/M/W indicates strength of correlation) S-Strong, M-Medium, L-Low
COs Programme Outcomes(POs)
PO PO PO PO PO PO PO PO PO PO1 PO1 PO1 PSO PSO
1 2 3 4 5 6 7 8 9 0 1 2 1 2
CO1 S M M M S M M L L S S

CO2 S M M S M M L S S

CO3 S L S L L M S

CO4 S S M M

CO5 S L L S L M

CO6 S L L S L L

6
 EVALUATION SCHEME

Mode of Evaluation: Continuous assessment of the progress will be done week followed by an
External Exam Practical (EEP) Laboratory Exam. This evaluation scheme is without PBL.

Component of Internal/ Rubric for

Marks
evaluation external CO

Midterm lab
--- 20
examination

Internal lab test Internal R1 20

Internal viva --- 10

Lab experiment R1 20

Lab Report External R2 20

Viva by external expert --- 10

Total: 100

7
ASSESSMENT PROCESS

Rubric R1: Internal Lab Test

Maximum Marks: 20

Level of Achievement

Assessment Excellent (4) Very Good Fair (2) Poor (1) Mapped
Parameter (3) PO

Identify Demonstrates Adequate Superficial Lack of PO3

appropriate deep Knowledge Knowledge information
tests, Knowledge of of most of about most
procedures Procedure; procedures procedures; of the
and answer the answer the able to procedures;
a equipment related related answer only cannot even
design questions questions, some of the answer
with but fails to related basic
explanations elaborate basic related
and Questions questions
elaboration

Result Excellent Adequate Little No insight PO4

Analysis insight and insight but insight and and entirely
and Data well focused missed some analyzed missed the
Interpretati result and important only the point of the
on discussion; points in most basic experiment;
Data results and points; little or no
completely discussion; Interpreted attempt to
and interpreted some data interpret
b
appropriately most data correctly data or
interpreted correctly but over-
and no over- but some significant interpreted
interpretation conclusions errors, data.
may be omissions
suspect or still present
over-
interpreted

8
 Rubric R1: ETE Lab Test

Maximum Marks: 20

Level of Achievement

Assessment Excellent (4) Very Good Fair (2) Poor (1) Mapped
Parameter (3) PO

Identify Demonstrates Adequate Superficial Lack of

appropriate deep Knowledge Knowledge information
tests, Knowledge of of most of about most PO5
procedures Procedure; procedures procedures; of the
and answer the answer the able to procedures;
a equipment related related answer only cannot even
design questions questions, some of the answer
with but fails to related basic
explanations elaborate basic related
and Questions questions
elaboration

b Result Excellent Adequate Little No insight PO4

Analysis insight and insight but insight and and entirely
and Data well focused missed some analyzed missed the
Interpretati result and important only the point of the
on discussion; points in most basic experiment;
Data results and points; little or no
completely discussion; Interpreted attempt to
and interpreted some data interpret
appropriately most data correctly data or
interpreted correctly but over-
and no over- but some significant interpreted
interpretation conclusions errors,
9
 may be omissions data.
suspect or still present
over-
interpreted

Rubric R2: Lab Report

Maximum Marks: 20

Level of Achievement

Assessment Very Fair Poor (1) Mapp

Parameter Excellent Good (3) (2) ed PO
(4)

a Result Data is Tables and Data is brief No data PO10

Representati complete and graphs and missing reported
on and relevant; (wherever significant
Discussion Tables applicable) pieces of
(wherever are drawn information;
applicable) but few Tables and
are easy to readings graphs
read and missing or (wherever
units are not clearly applicable),
provided. specified are
incomplete
Graphs
(wherever
applicable
are labeled
10
 and show
trends

Organization Lab report is Lab report Report Poor PO11

of Report and well is well contains few organization
Timely organized as organized errors and and late
b
Submission directed and but not not submission
submitted on submitted submitted
time on time on time

School of Computing Science and Engineering

Summary of Rubric Based Assessment of Internal Lab Test

Session…………………………
Class………………………... Subject Code……………………

Rubric R1: Lab experiment

Maximum Marks: 20

Experiment Name……………………………

S Univ. Name of student Dat Assessment Total

Roll No. e Parameter (20)
No
. Knowledge of Result Analysis
Tools and and Data
Procedures Interpretation
(10) (10)

1.

11
 2.

3.

4.

5.

6.

7.

8.

9.

10.

Signature of Evaluator (s) 1. …………………2,……………………….3,……………………4,

…………………...5,……………………….

School of Computing Science and Engineering

Summary of Rubric Based Assessment of ETE Lab Test

Session………………………… Class………………………...
Subject Code……………………

Rubric R1: Lab experiment

Maximum Marks: 20

Experiment Name……………………………

S Univ. Name of student Dat Assessment Total

Roll No. e Parameter (20)
No
. Knowledge of Result Analysis
Tools and and Data
Procedures Interpretation
(10) (10)

1.

12
 2.

3.

4.

5.

6.

7.

8.

9.

10.

Signature of Evaluator (s) 1. …………………2,……………………….3,……………………4,

…………………...5,……………………….

School of Computing Science and Engineering

Summary of Rubric Based Assessment of Lab Report

Session………………………… Class………………………...
Subject Code……………………

Rubric R2: Rubric for Assessing Lab Report

Maximum Marks: 20

Experiment Name……………………………

S Univ. Name of Dat Assessment Total

Roll No. student e Parameter (20)
No
. Result Organization
Representation of Report and
and Discussion Timely
(10) Submission
(10)

13
1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

Signature of Evaluator (s) 1. ………………………….…… 2,………………………………..…….

3……………………………………

School of Computing Science and Engineering

Summary of Rubric Based Assessment of CO of a Laboratory Course

Session…………………. Class…………………………..
Subject Code……………………….

Rubric R1: Rubric for Assessing Lab Test

Maximum Marks: 24

Rubric R1: Rubric for Assessing Lab Report

Maximum Marks: 20

Total Marks: 40

S Univ. Name of Student Date Total

No. Roll No. Assessment
Parameter

14
 R1 R2(20
(40) )

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

Signature of Evaluator (s) 1. …………………………….…

2,……………………….…………….3,……………………………………..……

15
 LIST OF EXPERIMENTS

Sr. Title of Lab Experiments

No.
1. Implement Data Definition language Statements.

Implement Data Manipulation Statements.

Implement SELECT command with different clauses.

Implement various type of Integrity Constraints on database.

Implement SINGLE ROW functions (Character, Numeric, Date functions) and GROUP functions
(avg, count, max, min, sum).

Implement various type of SET OPERATORS (Union, Intersect, Minus) and JOINS.

Implement the concept of grouping of Data and Subqueries.

Implement the concept of Data Control Language (DCL), Transaction Control Language (TCL).

Implement Simple and Complex View.

Write a PL/SQL block to satisfy some conditions by accepting input from the user.

Write a PL/SQL block for greatest of three numbers using IF AND ELSEIF

Write a PL/SQL block for summation of odd numbers using for LOOP

Write a PL/SQL Procedure for GCD Numbers

Write a PL/SQL Procedure for cursor implementation

Write a PL/SQL block to implementation of factorial using function

Value Added Experiments

16. Create a Database for Banking Sector and implement various queries on it.

17. Create a Database for Customer Sale/purchase and implement various queries on it.

16
EXPERIMENT DETAILS

Experiment 1
Title Data Definition Language
Objectiv Study of Data Definition language commands. - Create table, Alter
e Table,Drop Table, Rename Table.
Pre- Knowledge of Basic Database
requisite
Algorith The SQL DDL allows specification of not only a set of relations but also
m information about each relation, including-

/Theory ● Schema for each relation

● The domain of values associated with each attribute.
● The integrity constraints.
● The set of indices to be maintained for each relation.
● The security and authorization information for each relation.

The physical storage structure of each relation on disk.

Syntax CREATE TABLE

CREATE TABLE TABLENAME(COLUMN_NAME1 DATA_TYPE1(SIZE1),…….

COLUMN_NAMEN DATA_TYPEN(SIZEN));

ALTER TABLE

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
MODIFY column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
RENAME COLUMN OLDcolumn_name TO NEWcolumn_name;

DROP TABLE

DROP TABLE table_name;

RENAME TABLE

RENAME OLDtable_name TO NEWtable_name;

17
 TRUNCATE

TRUNCATE TABLE table_name;

Post Lab
Assignm
ent (If
Any)
Experiment 2
Title Data Manipulation Language Statements.
Objectiv Study of Data Manipulation Statements.
e
Pre- Knowledge of
requisite
● ORACLE Queries
Algorith Data Manipulation Language (DML) statements are used for
m managing data in database. DML commands are not auto-committed.
It means changes made by DML command are not permanent to
/Theory
database, it can be rolled back.

DML statements are used for managing data within schema objects. Some
examples:

o SELECT - retrieve data from the a database

o INSERT - insert data into a table
o UPDATE - updates existing data within a table
o DELETE - deletes all records from a table, the space for the records
remain

Syntax INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);
UPDATE table_name SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE FROM table_name WHERE condition;
SELECT column1, column2, ...FROM table_name;
Post Lab
Assignm
ent (If
Any)
Experiment 3
Title SELECT Command
Objectiv Study of SELECT command with different clauses.
e

18
Pre- Knowledge of
requisite
● ORACLE
Algorith
m SQL SELECT Statement
The most commonly used SQL command is SELECT statement. SQL SELECT
/Theory
statement is used to query or retrieve data from a table in the database. A query
may retrieve information from specified columns or from all of the columns in the
table. To create a simple SQL SELECT Statement, you must specify the column(s)
name and the table name. The whole query is called SQL SELECT Statement.
Syntax
Syntax of SQL SELECT Statement:
SELECT column_list FROM table-name 
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

Post Lab
Assignm
ent (If
Any)
Experiment 4
Title Keys
Objectiv Study of various type of Integrity Constraints.
e
Pre- Knowledge of
requisite
● ORACLE COMMANDS
Algorith
m SQL Constraints

/Theory SQL constraints are used to specify rules for the data in a table.

 If there is any violation between the constraint and the data action, the
action is aborted by the constraint.

Constraints can be specified when the table is created (inside the CREATE
TABLE statement) or after the table is created (inside the ALTER TABLE
statement).

In SQL, we have the following constraints:

● NOT NULL - Indicates that a column cannot store NULL value

● UNIQUE - Ensures that each row for a column must have a unique
19
 value
● PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures
that a column (or combination of two or more columns) have a unique
identity which helps to find a particular record in a table more easily
and quickly
● FOREIGN KEY - Ensure the referential integrity of the data in one
table to match values in another table
● CHECK - Ensures that the value in a column meets a specific
condition
● DEFAULT - Specifies a default value for a column

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain UNIQUE values.

A primary key column cannot contain NULL values.

Most tables should have a primary key, and each table can have only ONE primary key.

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Syntax
SQL CREATE TABLE + CONSTRAINT Syntax
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

CREATE TABLE PersonsNotNull

(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons

(

20
 P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

CREATE TABLE Orders

(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

Post Lab
Assignm
ent (If
Any)
Experiment 5
Title SINGLE ROW functions and Group functions
Objectiv Study of SINGLE ROW functions (Character, Numeric, Date functions) and
e GROUP functions (avg, count, max, min, sum).

Pre- Knowledge of
requisite
● ORACLE
Algorith Oracle SQL supplies a rich library of in-built functions which can be
m employed for various tasks. The essential capabilities of functions
can be the case conversion of strings, in-string or substring
/Theory
operations, mathematical computations on numeric data, and date
operations on date type values. SQL Functions optionally take
arguments from the user and mandatorily return a value.
Aggregate functions perform a variety of actions such as counting all the
rows in a table, averaging a column's data, and summing numeric data.

Aggregates can also search a table to find the highest "MAX" or lowest "MIN"
values in a column. As with other types of queries, you can restrict, or filter
21
 out the rows these functions act on with the WHERE clause. For example, if a
manager needs to know how many employees work in an organization, the
aggregate function named COUNT(*) can be used to produce this
information.The COUNT(*) function shown in the below SELECT statement
counts all rows in a table.

Syntax The SELECT query below demonstrates the use of NVL function.

SELECT first_name, last_name, salary, NVL (commission_pct,0)

FROM employees
WHERE rownum < 5;

FIRST_NAME LAST_NAME SALARY

NVL(COMMISSION_PCT,0)
-------------------- ------------------------- ---------- ---------------------
Steven King 24000 0
Neena Kochhar 17000 0
Lex De Haan 17000 0
Alexander Hunold 9000 0

Some of the commonly used aggregate functions are as below -

SUM( [ALL | DISTINCT] expression )

AVG( [ALL | DISTINCT] expression )

COUNT( [ALL | DISTINCT] expression )

COUNT(*)

MAX(expression)

MIN(expression)

Post Lab
Assignm
22
ent (If
Any)
Experiment 6(a)
Title SET Operators.
Objectiv Study of various type of SET OPERATORS (Union, Intersect, Minus).
e
Pre- Knowledge of
requisite
● Concept of SET Operators.
Algorith Set Operation in SQL
m
SQL supports few Set operations to be performed on table data. These are
used to get meaningful results from data, under different special conditions.
/Theory

Syntax select * from First

UNION
select * from second

Post Lab
Assignm
ent (If
Any)
Experiment 6(b)
Title Joins
Objectiv Study of Various type of JOINS.
e
Pre- Knowledge of
requisite
● ORACLE COMMANDS
Algorith SQL JOIN
m
An SQL JOIN clause is used to combine rows from two or more tables, based
/Theory on a common field between them.

The most common type of join is:  SQL INNER JOIN (simple
join). An SQL INNER JOIN return all rows from multiple tables
where the join condition is me. SQL INNER JOIN Keyword

The INNER JOIN keyword selects all rows from both tables as long as there is
a match between the columns in both table

23
 SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching
rows in the right table (table2). The result is NULL in the right side when there is no
match.

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all rows from the right table (table2), with the
matching rows in the left table (table1). The result is NULL in the left side when there is
no match.

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from
the right table (table2).

The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

Syntax
SQL INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

SQL FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1

24
 FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

Post Lab
Assignm
ent (If
Any)
Experiment 7
Title Subqueries
Objectiv Study and implement the concept of sub queries.
e
Pre- Knowledge of
requisite
● ORACAL COMMANDS
Algorith Subqueries:- A subquery is a form of an SQL statement that appears inside
m another SQL statement. It also termed as nested query. The statement
containing a subquery called a parent statement. The rows returned bu the
/Theory subquery are use by the following statement.

It can be used by the following commands:

1.To insert records in the target table.

2.To create tables and insert records in this table.
3.To update records in the target table.
4.To create view.
5.To provide values for the condition in the WHERE , HAVING IN ,
SELECT,UPDATE, and DELETE statements.
Exam:-

Creating clientmaster table from oldclient_master, table

Create table client_master

AS SELECT * FROM oldclient_master;

Syntax
Union Clause:
25
The user can put together multiple queries and combine their output using
the union clause . The union clause merges the output of two or more
queries into a single set of rows and column. The final output of union clause
will be

Output: = Records only in query one + records only in query two + A single
set of records with is common in the both queries.

Syntax:

SELECT columnname, columname

FROM tablename 1

UNION

SELECT columnname, columnname

From tablename2;

Intersect Clause: The use can put together multiple queries and their
output using the interest clause. The final output of the interest clause will
be :

Output =A single set of records which are common in both queries

Syntax:

SELECT columnname, columnname

FROM tablename 1

INTERSECT

SELECT columnname, columnname

FROM tablename 2;

MINUS CLAUSE:- The user can put together multiple queries and combine
their output = records only in query one

26
 Syntax:

SELECT columnname, columnname

FROM tablename ;

MINUS

SELECT columnname, columnname

FROM tablename ;

Post Lab
Assignm
ent (If
Any)
Experiment 8
Title Control languages
Objectiv Study and implement the concept of Data Control Language (DCL),
e Transaction Control Language (TCL).
Pre- Knowledge of
requisite
● ORACAL COMMANDS
Algorith TCL command
m
Transaction Control Language(TCL) commands are used to manage transactions in
/Theory database.These are used to manage the changes made by DML statements. It also allows
statements to be grouped together into logical transactions.
Commit command
Commit command is used to permanently save any transaaction into database.
Following is Commit command's syntax,

Rollback command
This command restores the database to last commited state. It is also use with savepoint
command to jump to a savepoint in a transaction.
Following is Rollback command's syntax,
Savepoint command
savepoint command is used to temporarily save a transaction so that you
27
 can rollback to that point whenever necessary.
Following is savepoint command's syntax,

Syntax commit;
rollback to savepoint-name;
savepoint savepoint-name;

Post Lab
Assignm
ent (If
Any)
Experiment 9
Title Views
Objectiv Study of Simple and Complex View.
e
Pre- Knowledge of
requisite
● ORACLE COMMANDS
Algorith
m CREATE VIEW Statement

/Theory In SQL, a view is a virtual table based on the result-set of an SQL statement.

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.

Note: A view always shows up-to-date data! The database engine recreates
the data, using the view's SQL statement, every time a user queries a view.

Syntax
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Renaming the columns of a view:-

28
 Syntax:-

CREATE VIEW viewname AS

SELECT newcolumnname….

FROM tablename

WHERE columnname=expression_list;

Selecting a data set from a view-

Syntax:-

SELECT columnname, columnname

FROM viewname

WHERE search condition;

Destroying a view-

Syntax:-

DROP VIEW viewname;

Post Lab
Assignm
ent (If
Any)
Experiment 10
Title PL/SQL Program for Addition of Two numbers

Objectiv PL/SQL Control Structure provides conditional tests, loops, flow control
and branches that let to produce well-structured programs.
29
e
Pre- Knowledge of SQL
requisite
Algorith STEP 1: Start
m STEP 2: Initialize the necessary variables.
STEP 3: Develop the set of statements with the essential operational
/Theory parameters.
STEP 4: Specify the Individual operation to be carried out.
STEP 5: Execute the statements.
STEP 6: Stop.
Syntax SQL>set serveroutput on
SQL>declare
1 a number;
2 b number;
3 c number;
4 begin
5 a: =&a;
6 b: =&b;
7 c: =a+b;
8 dbms_output.put_line ('sum of'||a||'and'||b||'is'||c);
9 end;
10 /
INPUT
Enter value for a: 23
old 6: a:=&a;
new 6: a:=23;
Enter value for b: 12
old 7: b:=&b;
new 7: b:=12;
OUTPUT sum of23and12is35
PL/SQL procedure successfully completed.
Post Lab
Assignm
ent (If
Any)
Experiment 11
Title PL/SQL block for greatest of three numbers using IF AND ELSEIF

Objectiv PL/SQL Control Structure provides conditional tests

e
Pre- Knowledge of SQL
requisite
Algorith STEP 1: Start
m STEP 2: Initialize the necessary variables.
STEP 3: invoke the if else if condition.
/Theory STEP 4: Execute the statements.

30
 STEP 5: Stop

Syntax SQL>set server output on

SQL> declare
2 a number;
3 b number;
4 c number;
5 begin
6 a:=&a;
7 b:=&b;
8 c:=&c;
9 if(a>b)and(a>c) then
10 dbms_output.put_line('A is maximum');
11 else if(b>a)and(b>c)then
12 dbms_output.put_line('B is maximum');
13 else
14 dbms_output.put_line('C is maximum');
15 end if;
16 end;
17 / INPUT Enter value for a: 21
old 7: a:=&a;
new 7: a:=21;
Enter value for b: 12
old 8: b:=&b;
new 8: b:=12;
Enter value for b: 45
old 9: c:=&b;
new 9: c:=45;
OUTPUT C is maximum PL/SQL procedure successfully completed.
Post Lab
Assignm
ent (If
Any)
Experiment 12
Title PL/SQL block for summation of odd numbers using for LOOP

Objectiv PL/SQL Control Structure provides conditional tests, loops, flow control
e
and branches that let to produce well-structured programs.

Pre- Knowledge of SQL

requisite
Algorith STEP 1: Start
m STEP 2: Initialize the necessary variables.
STEP 3: invoke the for loop condition.
/Theory STEP 4: Execute the statements.
STEP 5: Stop.
Syntax SQL>set server output on

31
 SQL> declare
2 n number;
3 sum1 number default 0;
4 end value number;
5 begin
6 end value:=&end value;
7 n:=1;
8 for n in 1..endvalue
9 loop
10 if mod(n,2)=1
11 then
12 sum1:=sum1+n;
13 end if;
14 end loop;
15 dbms_output.put_line('sum ='||sum1);
16 end;
17 /
INPUT
Enter value for end value: 4
old 6: end value:=&end value;
new 6: end value:=4;
OUTPUT sum =4
PL/SQL procedure successfully completed.
Post Lab
Assignm
ent (If
Any)
Experiment 13
Title PL/SQL Procedure for GCD Numbers

Objectiv PL/SQL Control Structure provides conditional tests.

e
Pre- Knowledge of SQL
requisite
Algorith Create or replace procedure <procedure_name> (argument {in, out, in
m out} data type)
{is, as} Variable declaration
/Theory Begin
Pl/SQL Subprogram body.
Exception
Exception PL/SQL Block.
End;
Syntax create or replace procedure pro is
a number(3);
b number(3);
c number(3);
d number(3);
32
 begin a:=&a;
b:=&b;
if(a>b) then c:=mod(a,b);
if(c=0) then
dbms_output.put_line('GCD is');
dbms_output.put_line(b);
else
dbms_output.put_line('GCD is');
dbms_output.put_line(c);
end if;
else d:=mod(b,a);
if(d=0) then
dbms_output.put_line('GCD is');
dbms_output.put_line(a);
else
dbms_output.put_line('GCD is');
dbms_output.put_line(d);
end if;
end if;
end;
/
Enter value for a: 8
old 8: a:=&a;
new 8: a:=8;
Enter value for b: 16
old 9: b:=&b;
new 9: b:=16;
Procedure created.
SQL> set serveroutput on;
SQL> execute pro;
GCD is 8
PL/SQL procedure successfully completed
Post Lab
Assignm
ent (If
Any)
Experiment 14
Title PL/SQL Procedure for cursor implementation.

Objectiv To understand the concept of cursor.

e
Pre- Knowledge of SQL.
requisite
Algorith create table st13(regno number(4),name varchar2(20),mark1
m number(3),mark2 number(3),mark3 numbe r(3),mark4 number(3),mark5
number(3));
/Theory insert into st13 values(101,'raji',100,90,97,89,91);
33
 insert into a13 values(102,'kali');
Syntax insert into a13 values(103,'jaya');
select * from st13;

SQL>set server output on

declare
ave number(5,2);
tot number(3);
cursor c_mark is select * from st13 where mark1>=40 and mark2>=40
and mark3>=40 and mark4>=40 and mark5>=40;
begin
dbms_output.put_line('regno name mark1 mark2 mark3 mark4 mark5
total average');
dbms_output.put_line('------------------------------------------------------');
for student in c_mark
loop
tot:=st13.mark1+st13.mark2+st13.mark3+st13.mark4+st13.mark5;
ave:=tot/5; dbms_output.put_line(st13.regno||rpad(st13.name,15)||
rpad(st13.mark1,6)||rpad(st13.mark2,6)||rpad(st13.mark3,6)||
rpad(st13.mark4,6)||rpad(st13.mark5,6)||rpad(tot,8)||rpad(ave,5)); end
loop;
end;
/

Post Lab
Assignm
ent (If
Any)
Experiment 15
Title FUNCTION TO FIND FACTORIAL
Objectiv To find factorial using function
e
Pre- Knowledge of SQL
requisite
Algorith Input An integer.

34
m Output Factorial of given number.
Factorial(num)
/Theory 1 if (num=0 or num=1) then.
2 fact = 1;
3 else.
4 for i 1 to n.
Syntax SQL> create or replace function fact(n number)
2 return number is
3 i number(10);
4 f number:=1;
5 begin
6 for i in 1..N loop
7 f:=f*i;
8 end loop;
9 return f;
10 end;
11 /
Function created.
SQL> select fact(2) from dual;
FACT(2)
---------
2
Post Lab
Assignm
ent (If
Any)

35