DATABASE MANAGEMENT SYSTEMS LAB

LABORATORY MANUAL

MCA, Semester -I

Subject Code: E1PA104B

Session: 2024-25, Odd Semester

GALGOTIAS UNIVERSITY
SCHOOL OF COMPUTER APPLICATIONS &
TECHNOLOGY
 Table of Contents
1. Vision and Mission of the University
2. Vision and Mission of the School
3. Programme Educational Objectives (PEOs)
4. Programme Outcomes (POs)
5. Programme Specific Outcomes (PSOs)
6. University Syllabus
7. Course Outcomes (COs)
8. Course Overview
9. List of Experiments
10. Lab assessment criteria
11. Guidelines for students for report preparation
 Vision and Mission of the Institute
Vision

To be recognized globally as a premier School of Computer Applications and Technology

imparting quality and value based education engaged in multi-disciplinary and collaborative
research.

Mission
The mission of the department is to :
M1: Developing a strong foundation in fundamentals of computer applications with
responsiveness towards emerging technologies.

M2: Establishing state-of-the-art facilities and adopt education 4.0 practices to analyze,
develop, test and deploy sustainable ethical IT solutions by involving multiple stakeholders.

M3: Establishing Centers of Excellence for multidisciplinary collaborative research in

association with industry and academia.

PROGRAM EDUCATIONAL OBJECTIVES

The Post Graduates of Computer Application shall:

PEO1: be able to successfully pursue research in Computer Applications and allied disciplines
at institutions of transnational reputation.

PEO2: serve in technical or managerial roles at Government firms, Corporates and

contributing to the society as successful entrepreneurs through startup.

PEO3: be engaged with leading Global Software services companies, Consultancy

organizations and academic Institutions.

PROGRAMME OUTCOME (POs):

PO1: An ability to independently carry out research /investigation and development work to
solve practical problems.

PO2: An ability to write and present a substantial technical report/document.

PO3: Students should be able to demonstrate a degree of mastery over the area as per the
specialization of the program. The mastery should be at a level higher than the requirements in
the appropriate bachelor program.
PROGRAM SPECIFIC OUTCOMES (PSOs)

The students of Computer Application will be able to:

PSO1: Have the ability to work with contemporary technologies in computing requisite to Industry
4.0 developing and implementing solutions to real life problems.

PSO2: Demonstrate application development skills learned through technical training and projects to
solve real world problems.

University Syllabus
Unit-1 Introduction to Database Management System
An overview of database management system, Database System Vs. File System, Database
system concepts and architecture, data models, schema and instances, data independence and
data base language and interfaces, Data definitions language, DML, Overall Database
Structure.
Unit-2 Entity Relationship Model
Conceptual Modelling of a database, The Entity-Relationship (ER) Model, Entity Types,
Entity Sets, Attributes, and Keys, Relationship Types, Relationship Sets, Weak Entity Types
Generalization, Specialization and Aggregation, Extended Entity Relationship (EER) Model.
Unit-3 Relational Model and SQL Query
Relational data model concepts, integrity constraints, entity integrity, referential integrity,
Keys constraints, and Domain constraints. Introduction to SQL: Characteristics of SQL,
advantage of SQL. SQL data type and literals. Types of SQL commands. SQL operators and
their procedure. Tables, views and indexes. Queries and sub queries. Aggregate functions.
Insert, update and delete operations, Joins, Unions, Intersection, Minus, Introduction to
PLSQL: Cursors, Triggers, and Procedures
Unit-4 Normalization
Functional dependencies, normal forms, first, second, third normal forms, BCNF, inclusion
dependence, loss less join decompositions, normalization using FD, MVD, and JDs
Unit-5 Overview of Transaction Management and Concurrency Control
Overview of Transaction Management: ACID Properties, Transactions and Schedules,
Concurrent Execution of transaction, Lock Based Concurrency Control, Performance
Locking Concurrency Control: Serializability, and recoverability, Introduction to Lock
Management, Lock Conversions, Dealing with Dead Locks, Specialized Locking
Techniques, Concurrency without Locking.
COURSE OBJECTIVE

 To familiarize students with basic concepts of databases and database management systems
with emphasize on relational databases.
 To gain a solid understanding of design the entity relationship diagram the database and the
concept of normalization also.
 To explore and implement all the schemas on the SQL and PL/SQL platform which will give
the practical exposure to students in that they will learn how to create tables, manipulate table.
 To understand the concepts of transactions and their processing in the real life scenario.

COURSE OUTCOMES(COs):

After the completion of this course, students will be able to:

E1PA104B.1.1 Apply database design principles and structured query language (SQL) to
develop and manage databases.
E1PA104B.1.1 Analyze database models, including ER diagrams, to identify
normalization requirements and redundancy issues.
E1PA104B.1.1 Evaluate database transaction issues and propose effective solutions for
maintaining consistency and integrity in database management systems
E1PA104B.1.1 Apply SQL queries to create, manipulate, and manage data in a relational
database system.

COURSE ASSESSMENT
The course assessment patterns are the assessment tools used both in formative and summative
examinations.
Type of Final Marks
CIE Total Marks CIE*0.5+SEE*
Course (B)
0.5
LAB
LAB
Work@ + MTE CIE SEE
EXAM*
Record
INTEGRATED
25 50 25 100 100 100

@Lab Work-15 marks + Lab Record-10 marks

*Passing Criteria-30% of marks to be secured in the lab Exam conducted by two examiners
(one internal and done external)
Assessment Criteria for Oracle Database Programming Lab

The assessment for each experiment will be based on the following criteria:

1. Problem Understanding (10%): Clarity in understanding the problem statement and

expected output.
2. Algorithm Design (20%): Logical flow and structure of SQL/PLSQL statements.
3. Coding and Implementation (30%): Correctness of SQL queries, PL/SQL blocks,
procedures, and triggers.
4. Test Cases and Output (15%): The ability to handle multiple scenarios and ensure
correct results.
5. Optimization (10%): Efficient use of database resources, proper indexing, and query
optimization.
6. Documentation and Comments (5%): Proper commenting and clear explanation of
the code.
7. Error Handling (5%): Proper exception handling in PL/SQL code.
8. Viva and Concept Explanation (5%): Ability to explain the implemented logic and
SQL/PLSQL concepts.
 INDEX

S.N. Assignment Date Signature

1 Introduction to date types in oracle.

2 Creating Entity-Relationship Diagram using case tools

3 To implement Create, insert and Basic SQL SELECT

statements.

4 To Manipulate data in the existing tables

5 To Create and manage tables with constraints on it.

6 To Alter structure of table.

7 To Aggregate data using group function.

8 To implement join concepts.

9 To implement concept of sub-queries.

10 To Create cursor in oracle.

11 To Create procedure and functions in oracle.

12 To Create triggers in oracle.

 Objective: Introduction to date types in oracle.

ASSIGNMENT- 1

Write various data types in SQL along with their description and syntax

Data Syntax Description

Type

Char CHAR(SIZE) • Used to store character string values of fixed length.

• Size is the number of characters the cell can hold.
• Maximum size=255
• Data stored is right padded.
Varchar VARCHAR(SIZ • Used to store variable length alphanumeric data.
E) • More flexible than Char data type. Maximum size=4000
• No padding. Slower than Char data type.
• Not compatible with all versions of Oracle.
Varchar2 VARCHAR2(SI • Used to store variable length alphanumeric data.
ZE) • More flexible than Char data type. Maximum size=4000
• No padding. Slower than Char data type.
• Compatible with all versions of Oracle.
Date DATE • Used to represent date and time.
• Standard format of date: DD-MON-YY
• Standard format of time:24 hours format
• Default Date: First day of current month.
• Default Time: 12:00:00 a.m., if no time zone is specified.
• Valid date range: January 14712 B.C. to December 314712 A.D.
Number NUMBER(PREC • Used to store numbers(fixed point or floating point)
ISION,SCALE) • Any magnitude may be stored up to 38 digits of precision.
• Values range: 1.0 E-130 to 9.9 E 125 including 0, +ve, -ve no.
• Precision: Maximum length of data
• Scale: Number of places to the right of the decimal point.
Long LONG • Used to store variable length character strings.
• Max size: 2 GB. Used to store arrays of binary data in ASCII format.
• At most one LONG variable per table.
• Table containing LONG variable can’t be clustered.
• LONG values can’t be used in Sub queries, Functions, and ,
Expressions.
Raw/Lon RAW/LONG • Used to store binary data, digitalized picture and image.
g Raw RAW • Data loaded into columns of these data types are stored without any
further conversion.
• Maximum length of RAW data type: 255 bytes.
• Maximum length of LONG RAW data type: 2 GB.
Objective: Creating Entity-Relationship Diagram using case tools.

Entity-Relationship Diagram:
Concept Draw gives the ability to describe a database using the Entity-Relationship model. Entity-
Relationship Diagram solution includes icons advocated by Chen's and Crow’s Foot notation that can be
used when describing a database. Concept Draw has examples and templates for Data Modeling with
Entity Relationship Diagram.

The ERD solution from ConceptDraw Solution Park extends ConceptDraw PRO vector graphics and
diagramming software with the ability to describe a database using the Entity-Relationship model. Use
ConceptDraw PRO enhanced with ERD solution to draw you own entity relationship diagrams using
Chen's or crow's foot notations.
 ASSIGNMENT- 2

Creating Entity-Relationship Diagram for one of the following:

• Library information System

• Payroll processing system

• Student Information System

Objective: To implement Create, insert and select queries.

THEORY & CONCEPTS

CREATE TABLE statement is used to create a table in a database. Tables are organized into rows and
columns; and each table must have a name.

SQL CREATE TABLE Syntax

CREATE TABLE table_name (

column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size),
....);

The column_name parameters specify the names of the columns of the table. The data_type parameter
specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.). The size
parameter specifies the maximum length of the column of the table.

Example: Now we want to create a table called "Persons" that contains five columns: PersonID,
LastName, FirstName, Address, and City.
CREATE TABLE Persons
(
PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255)
);

INSERT INTO statement is used to insert new records in a table. It is possible to write the INSERT
INTO statement in two forms.

a) The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name VALUES (value1,value2,value3,...);

b) The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

Example: Assume we wish to insert a new row in the "Customers" table:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

Insert Data Only in Specified Columns:

INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');
SELECT command to retrieve one or more rows, or partial rows, of data from an existing table or view,
and to perform grouping functions on the data.
Syntax:
a) To view all row and all column columns:
Select * from table_name;
b) To view all row and selected column columns;
Select column_name1,column_name2 from table_name;
c) To view selected row and all column columns;
Select * from table_name where condition;
d) To view selected row and all column columns;
Select column_name1,column_name2 from table_name where condition;
Examples: To select all rows of the alerts.status table where the Severity is equal to 4, enter:
select * from alerts.status where Severity = 4;
 ASSIGNMENT- 3

Create these two tables with following specifications and insert data in the table:

Table Name: Client master

Attribute Data Type Size

Client_no number 10
Client_Name Varchar2 20
City Varchar2 15
State Varchar2 15
Pin Number 6
Balance_due Number 10,2

Data for Client master:

CLIENT_NO Client_NAME CITY PIN_CODE STATE BAL_DUE

0001 Ivan Bombay 400057 Maharastra 15000

0002 Vandura Madras 980001 Tamilnadu 0

0003 Pramod Bombay 400057 Maharastra 5000

0004 Basu Bombay 400056 Maharastra 0

0005 Ravi Delhi 100001 Null 2000

0006 Rukmini Bombay 900050 Maharastra 0

PRODUCT_MASTER

COLUMN DATA TYPE Size

PRODUCT_NO VARCHAR2 6
DESCRIPTION VARCHAR2 20
PROFIT% NUMBER 10
QTY_ON_HAND NUMBER 10
ORDER_LEVEL NUMBER 10
SELL_PRICE NUMBER 10
COST_PRICE NUMBER 10
Data for Product_Master Table
Product_ Description Profit Qty_on hand Reorde Sell price Cost price
No % r_level
P00001 1.44 floppies 5 100 20 525 500
P03453 Monitors 6 10 3 12000 11200
P06734 Mouse 5 20 5 1050 500
P07865 1.22 floppies 5 100 20 525 500
P07868 Keyboards 2 10 3 3150 3050
P07885 CD drive 2.5 10 3 5250 5100
P07965 540 HDD 4 10 3 8400 8000
P07975 1.44 Drive 5 10 3 1050 1000
P08865 1.22 Drive 5 2 3 1050 1000

Now, perform following queries on the above data:

(a) Find out the name of all the clients.

(b) Retrieve the list of names and cities of all the clients.
(c) List all the clients who are located in Bombay.
(d) Display the information for client no 0001 and 0002.
(e) Find the list of all clients who stay in city ‘Bombay’ or ‘Delhi’ or ‘Madras’.
(f) List the name, city, and state of clients not in state of ‘Maharashtra’
Objective: To Manipulate data in the existing tables

THEORY & CONCEPTS

Modifying Table Data: The UPDATE Command

Use the UPDATE command to update one or more columns in an existing row of data in a table.
Syntax
UPDATE table_name SET column1=value1,column2=value2,...
WHERE some_column=some_value;
Removing Table Data: The DELETE Command
Use the DELETE command to delete one or more rows of data from an existing table.
Syntax
DELETE FROM [database_name.]object_name [ VIA ('value_of_primary_key_column',...) ]
[ WHERE condition ];
If you are deleting a single row and you know the value of the primary key for the row you wish to delete,
specify the value using the optional VIAclause to improve the performance of the delete command. If
there is more than one primary key column, the values must be specified in order, separated by commas
and in parentheses. If the column has a string value, it must be enclosed in quotes.
If you include a WHERE clause, only rows meeting the criteria specified in the condition are deleted.
Conditions are described in Conditions. If noWHERE clause is specified, all rows are deleted.
Example
To remove all the rows of the alerts.status table where the value of the Node field is equal to Fred, enter:
delete from alerts.status where Node = 'Fred' ;
 ASSIGNMENT- 4

Using the table client master and product master answer the following queries:

(a) Delete the record of Client no. 0001 from the Client master table.
(b) Change the city of Client no. 0005 to ‘Bombay’.
(c) Change the balance due of Client no. 0002 to 1000.
(d) Find out the clients who stay in a city or state where second letter is a.
(e) Calculate the average balance due of all the clients.
(f) Change the selling price of 1.44 floppy drive to Rs. 1150.00.
(g) Count the number of products having price greater than or equal to 1500.
Objective: To Create and manage tables with constraints on it.

THEORY & CONCEPTS

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).

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,
....
);

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 value
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or
combination of two or more columns) have an 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 when specified none for this column
 ASSIGNMENT- 5

CREATE THE TABLES WITH FOLLOWING SPECIFICATIONS AND CONSTRAINTS:

TABLE NAME: SALES MASTER

ATTRIBUTE DATA TYPE SIZE CONSTRAINT

SALESMAN_NO VARCHAR2 6 PRIMARY KEY, FIRST LETTER IS ‘S’
SALES_NAME VARCHAR2 20 NOT NULL
ADDRESS VARCHAR2 20 NOT NULL
CITY VARCHAR2 20 ---
STATE VARCHAR2 20 ---
PINCODE NUMBER 6 ---
SAL_AMT NUMBER 8,2 NOT NULL, CAN’T BE ZERO
Tgt_to_get NUMBER 6,2 NOT NULL, CAN’T BE ZERO
Ytd_sales NUMBER 6,2 NOT NULL, CAN’T BE ZERO
Remark VARCHAR2 30

TABLE NAME: SALES ORDER

ATTRIBUTE DATA SIZE CONSTRAINT

TYPE
S_ORDER_No VARCHAR2 6 PRIMARY KEY,FIRST LETTER IS ‘O’
S_ORDER_DATE DATE --- ---
CLIENT_NO NUMBER 10 FOREIGN KEY FROM CLIENT MASTER
SALESMAN_NO VARCHAR2 26 FOREIGN KEY FROM SALES MASTER
DELIVERY_TYPE CHAR 1 P FOR PARTIAL AND F FOR FULL, DEFAULT IS F
BILLED_YN CHAR 1 ‘Y’ FOR YES AND ‘N’ FOR NO
DELIVERY_DATE DATE --- CAN’T BE LESS THAN S_ORDER_DATE
ORDER_STATUS VARCHAR2 10 IN(IN-PROCESS,FULFILLED,BACK
ORDER,CANCELLED)

TABLE NAME: Sales_order_detail

Column Dadatype Size Attributes

S_order_no Varchar2 6 PK/FK references
s_order_no of
sales_order
Product_no Varchar2 6 PK/FK references
product_no of
product_master
Qty_order Number 8
Qty_disp Number 8
Product_rate Number 10,2
DATA OF SALES_MASTER:
Sales Tgt_to_ Ytd_
No. Sales_Name Address City Pincode State Salamt get sales Remark
A/14
S00001 Kiran worli Bombay 400002 MAH 3000 100 50 Good
65,
S00002 Manish Nariman Bombay 400001 MAH 3000 200 100 Good
P-7,
S00003 Ravi Bandra Bombay 400032 MAH 3000 200 100 Good
S00004 Ashish A/5 Juhu Bombay 400044 MAH 3500 200 150 Good

DATA OF SALES_ORDER

Dely Salesma Delay Orderstatu

S_order_no S_order_date Client no type Bill yn n no date s
20-Jan-
O19001 12-Jan-96 1 F N 50001 96 IP
27-Jan-
O19002 25-Jan-96 2 P N 50002 96 C
20-Feb-
O16865 18-Feb-96 3 F Y 500003 96 F
07-Apr-
O19003 03-Apr-96 1 F Y 500001 96 F
22-
O46866 20-May-96 4 P N 500002 May-96 C
26-
O10008 24-May-96 5 F N 500004 May-96 IP

Data for sale_order_detail

S_order_no Product_no Qty_order Qty_disp Product_rate

O19001 P00001 4 4 525
O19001 P07965 2 1 8400
O19001 P07885 2 1 5250
O19002 P00001 10 0 525
O46865 P07868 3 3 3150
O46865 P07885 10 10 5250
O19003 P00001 4 4 1050
O19003 P03453 2 2 1050
O46866 P06734 1 1 12000
O46866 P07965 1 0 8400
O10008 P07975 1 0 1050
O10008 P00001 10 5 525
Objective: To Alter structure of table.

THEORY & CONCEPTS

The SQL ALTER TABLE command is used to modify the definition (structure) of a table by
modifying the definition of its columns. The ALTER command is used to perform the following
functions.
1) Add, drop, modify table columns
2) Add and drop constraints
3) Enable and Disable constraints
Syntax to add a column

ALTER TABLE table_name ADD column_name datatype;

For Example: To add a column "experience" to the employee table, the query would be like
ALTER TABLE employee ADD experience number(3);

Syntax to drop a column

ALTER TABLE table_name DROP column_name;

For Example: To drop the column "location" from the employee table, the query would be like
ALTER TABLE employee DROP location;

Syntax to modify a column

ALTER TABLE table_name MODIFY column_name datatype;

For Example: To modify the column salary in the employee table, the query would be like
ALTER TABLE employee MODIFY salary number(15,2);
SQL RENAME Command

The SQL RENAME command is used to change the name of the table or a database object.
If you change the object's name any reference to the old name will be affected. You have to manually
change the old name to the new name in every reference.
Syntax to rename a table

RENAME old_table_name To new_table_name;

For Example: To change the name of the table employee to my_employee, the query would be like
RENAME employee TO my_emloyee;
 ASSIGNMENT- 6

1) Make client_no primary key in client_master.

2) Add new column phone_number in client_master table.

3) Add not null constraint in product master with columns : description, profit_percent, sellprice,
costprice

4) Change size of client_no field in client_master.

5) Add check constraint to product_master such that sellprice is always greater than costprice.

6) Select produc_no,description where profit percent is between 20 and 30 both inclusive.

Objective: To Aggregate data using group function.

THEORY & CONCEPTS

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by
one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE

column_name operator value GROUP BY column_name;

Example
SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID=Shippers.ShipperID GROUP BY ShipperName;

GROUP BY More Than One Column

We can also use the GROUP BY statement on more than one column, like this:

Example
SELECT Shippers.ShipperName, Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM ((Orders INNER JOIN Shippers ON Orders.ShipperID=Shippers.ShipperID) INNER JOIN
Employees
ON Orders.EmployeeID=Employees.EmployeeID) GROUP BY ShipperName,LastName;
 ASSIGNMENT- 7

Perform following queries based on all 5 tables:

1. Print the description and total quantity sold for each product.
2. Find the value of each product sold.
3. Find out the products which have been sold to ‘Ivan’.
4. Find the names of clients who have ‘CD Drive’.
5. Find the products and their quantities for the orders placed by ‘Vandana’ and ‘Ivan’
6. Select product_no, total_qty_ordered for each product.
7. Display the order number and day on which clients placed their order.
8. Display the month and date when the order must be delivered.
Objective: To implement join concepts

THEORY & CONCEPTS

SQL JOIN clause is used to combine rows from two or more tables, based 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 met.

Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN
Customers
ON Orders.CustomerID=Customers.CustomerID;

Different SQL JOINs

Before we continue with examples, we will list the types the different SQL JOINs you can use:

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in ONE of the tables
 ASSIGNMENT- 8

1. Find out the product which has been sold to ‘Ivan’.

2. Find out the product and their quantities that will have to be delivered.
3. Find out the names of clients who have purchased ‘CD DRIVE’
4. List the product_no and s_order_no of customers having qty ordered less than 5 from the order
details table for the product ‘1.44 floppies’.
5. Find the product and their quantities for the orders placed by ‘Vandan’ and ‘Ivan’.
6. Find the products and their quantities for the orders placed by client_no ‘C00001”
7. Find the order_no, Client_no ans salesman_no where a client has been received by more than one
salesman.
 Objective: To implement concept of sub-queries.

THEORY & CONCEPTS

A Sub query or Inner query or Nested query is a query within another SQL query and embedded within
the WHERE clause. A sub query is used to return data that will be used in the main query as a condition to
further restrict the data to be retrieved. Sub queries can be used with the SELECT, INSERT, UPDATE,
and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

There are a few rules that sub queries must follow:

• Sub-queries must be enclosed within parentheses.

• A sub-query can have only one column in the SELECT clause, unless multiple columns are in the
main query for the sub-query to compare its selected columns.

• An ORDER BY cannot be used in a sub-query, although the main query can use an ORDER BY.
The GROUP BY can be used to perform the same function as the ORDER BY in a sub-query.

• Sub-queries that return more than one row can only be used with multiple value operators, such as
the IN operator.

• The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY,
CLOB, or NCLOB.

• A sub-query cannot be immediately enclosed in a set function.

• The BETWEEN operator cannot be used with a sub-query; however, the BETWEEN operator can
be used within the sub-query.

Sub-queries with the SELECT Statement: Sub-queries are most frequently used with the SELECT
statement. The basic syntax is as follows:
SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name
OPERATOR
(SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])
 ASSIGNEMENT-9
1. Find the product_no and description of non moving products.

2. Find the customer name, address, city and pincode for the client who has placed order no "019001".

3. Find the client name who have placed order before the month of may 2006.

4. Find out if product "1.44 Drive" is ordered by only client and print the client_no, name to whom it was
soled.

5. Find the name of client who have placed orders worth Rs. 10000 or more.

6. Select the orders placed by "Rahul Desai".

7. Select the name of person who are in Mr.Pradeep's department and who have also worked on inventory
control system.

8. Select all the clients and the slaesman in the city of Bombay.

9. Select slaesman name in Bombay who has atleast one client located at Bombay.

10. Select the product_no, description, qty_on-hand, cost_price of non_moving items in the
product_master table.
Objective: To implement concept of cursor

THEORY & CONCEPTS

Oracle Engine uses a work area for its internal processing in order to execute an SQL statement. This
work area is private to SQL’s operations and is called a Cursor. The data that is stored in the cursor is
called the Active Data Set. The size of cursor in memory is the size required to hold the number of rows in
the Active Data Set which is determined by memory management capabilities of oracle and the amount of
RAM available. Oracle has a pre-defined area in main memory set aside, within which cursors are opened.
The values retrieved from a table are held in a cursor opened in memory by the oracle engine. This data is
then transferred to the client machine via the network. In order to hold this data, a cursor is opened. When
a cursor is loaded with multiple rows via a query , the Oracle engine opens and maintains a row pointer.
Depending on user’s requests to view data, the row pointer will be relocated within the cursor’s active
data set.
Types of Cursors: Cursors are classified into two categories depending on its circumstances under which
they are opened:
✓ Implicit Cursor
✓ Explicit Cursor

Implicit Cursor: If Oracle Engine opens cursor for its internal processing, it is known as Implicit Cursor.
When the SELECT…INTO or any DML statement is used (with respect to single row), the Oracle
internally opens the cursor, called as implicit cursor. Processing an implicit cursor is taken care of
automatically by PL/SQL.
Explicit Cursor: A cursor can be opened for processing data through a PL/SQL block, on demand. Such
a user defined cursor is known as Explicit Cursor. If a query returns more than one row then cursor has to
be defined explicitly. It is called as explicit cursor.

CURSOR Explicit Attributes:

• Cursor attributes provides the current information(status) of the cursor.
• There are four attributes available in the PL/SQL that can be applied to cursor. These cursor
attributes, when used in PL/SQL block, return a value that can be used in expressions.
• The attributes are %FOUND, %NOTFOUND, %ISOPEN AND %ROWCOUNT.
• These are referred as Cursor_name%attribute
• %OPEN returns TRUE if cursor is currently open, otherwise it returns FALSE. It is generally used
for situations where programmer is not sure whether cursor is open or not.
• %FOUND is BOOLEAN attribute. It is TRUE, if the recent fetch statement executes successfully,
otherwise if fetch statement does not return a row then it gives FALSE. Before first fetch
operation, it returns NULL. If %FOUND is checked before the opening of the cursor, it gives
ORA-1001 (Invalid cursor) error.
• %NOTFOUND is a complement of %FOUND. %NOTFOUND return FALSE, if recent fetch
statement executes successfully. It is often used as the exit condition for a fetch loop. Like
%FOUND, %NOTFOUND returns NULL before first fetch statement.
• %ROWCOUNT is a numeric attribute returns the number of rows fetched from the active set so
far. Initially, when cursor is just opened, %ROWCOUNT is zero. Like other attributes,
%ROWCOUNT if referred before opening the cursor or after closing the cursor, it gives ORA-
1001 invalid_cursor error.
Explicit Cursor
Explicit Cursor: Processing of explicit cursor involves four steps, which are as follows:
• Declare the cursor.
• Open the cursor.
• Fetch the records from the cursor area into PL/SQL variables.
• Close the cursor.

Attributes On Implicit Cursors:

• Like explicit cursors, here also we can use the same cursor attributes and can be used to access the
information about the most recently executed SQL statement.
• These attributes can be referred by using SQL prefix to the attributes as follows:
SQL%FOUND evaluates to TRUE, if an Insert, Update or Delete operation affected a row, or a
single-row Select statement returned one row. Otherwise, it evaluates to FALSE.
• SQL%NOTFOUND is the logical opposite of SQL%FOUND. It evaluates to TRUE, if an Insert,
Update or Delete affected no rows, or a single-row Select returns no rows. Otherwise, it returns too
FALSE.
• SQL%ROWCOUNT returns the number of rows affected by an Insert, Update or Delete or
Select…into…statement.

• SQL%ISOPEN cannot be referenced outside of its SQL statement, because Oracle engine
automatically opens and closes the SQL cursor after executing its associated select, insert, update
or delete statement has been processed. As a result, SQL%ISOPEN always returns to FALSE.

Example Implicit Cursor:

BEGIN
UPDATE EMP SET Sal = Sal+0.20 WHERE Empno= &employee_number;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE (‘Record modified successfully’);
ELSE
DBMS_OUTPUT.PUT_LINE (‘Employee does not exist’);
END IF;
END
 ASSIGNMENT-10

Create a explicit cursor which updates the salary of an employee such that,
1. If salary > 10000, then increase the salary by 15%
2. If 5000<salary <10000, then increase the salary by 12%
3. Otherwise, increase the salary by 25%

Solution:
Explicit Cursor:
DECLARE
CURSOR c1 IS SELECT empno, sal FROM emp WHERE deptno=10;
eno EMP.Empno%type;
salary EMP.Sal%type;
BEGIN
OPEN c1;
IF c1%ISOPEN THEN
LOOP
FETCH c1 into eno, salary;
Exit WHEN c1%NOTFOUND
IF salary > 10000 THEN
UPDATE EMP SET Sal = Sal*1.5 WHERE Empno=eno;
ELSIF salary BETWEEN 5000 and 10000 THEN
UPDATE EMP SET Sal = Sal*1.2 WHERE Empno=eno;
ELSE
UPDATE EMP SET Sal = Sal *2.5 WHERE Empno=eno;
END IF;
END LOOP;
CLOSE c1;
ELSE
DBMS_OUTPUT.PUT_LINE(‘Unable to Open the cursor’);
END IF;
END;
Objective: Creating procedure and functions in oracle.

THEORY & CONCEPTS

Procedures:
Procedure is a subprogram that performs specific action. Like an unnamed or anonymous blocks, sub
programs have a declarative part, an executable part and an optional exception handling part.
SYNTAX
CREATE [OR REPLACE] PROCEDURE <procedure_name>
[(argument 1 [{IN | OUT | INOUT}] type,…….., argument2 [{IN | OUT | INOUT}] type)]
{IS | AS}
[local_variable_declarations;]
BEGIN
PL/SQL executable statements
[ EXCEPTION
Exception Handler;]
END <procedure_name>

• procedure_name is the name of the procedure to be created;

• argument is the name of the parameter and type is the data type of associated parameters. Here we
can’t specify the width along with the data type. Also, we can not specify any constraint on the
data type.
• { IN | OUT | INOUT } is the mode of the parameter, which is optional. By default the mode is IN.
• The body of the procedure starts with the { IS | AS } keyword.
• Like any other PL/SQL block, body contains declarative section, executable section and an
exception handling section. Here the user does not need to write the DECLARE keyword.

FUNCTIONS:

• Functions are similar to stored procedures except they are always required to return a value.
• Both are having the same structure like declarative, executable and exception handling section.
• Like procedures, functions are also stored in the database. Generally we use functions to compute
a value.
• The syntax for creating function is very similar to the syntax for a procedure. It is as:

CREATE [ OR REPLACE] FUNCTION <function_name>

{( argument [{ IN | OUT | INOUT} ] type, ………,
Argument [{IN | OUT | INOUT}] type )] RETURN return_type
{IS | AS}
[ local_variable_declarations;}
BEGIN
PL/SQL executable statements
[EXCEPTIOIN
Exception Handler;}
END <function_name>
 ASSIGNMENT-11

Ques 1. Create Procedure for modifying the salaries of employees from department number
10, such that:
1. If salary > 3000, then increase salary by 10%
2. If 2000<salary<3000, then increase the salary by 5%
3. Otherwise increase salary by 3%
Ques 2. Create a function for adding two numbers.

Solution1: CREATE OR REPLACE PROCEDURE update_sal (eno IN NUMBER) IS

x EMP. Empno%TYPE;
y EMP. Sal%TYPE;
BEGIN
SELECT Empno, Sal INTO x, y FROM EMP WHERE Empno = eno;
IF y > 3000 THEN
UPDATE EMP SET Sal = Sal* 1.1 WHERE Empno = eno;
ELSIF y BETWEEN 2000 AND 3000 THEN
UPDATE EMP SET Sal = Sal * 1.05 WHERE Empno = eno;
ELSE
UPDATE EMP SET Sal = Sal * 1.03 WHERE Empno= eno;
ENDIF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No such user exit’);
END UPDATE_SAL;

Calling Procedure
• When called, this procedure accepts an employee number, which it uses to find the basic salary of
that employee.
• If current employee is not found then it raises exception with a message otherwise salary will be
modified.
• Now the PL/SQL program to call this procedure may be as follow:
DECLARE
x EMP.Empno%type
CURSOR c1 is SELECT Empno FROM EMP WHERE Deptno=10;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO x;
Exit WHEN c1%notfound;
update_sal(x); //Call to procedure
END LOOP;
CLOSE c1;
END;

Solution2. Create a function for adding two numbers.

CREATE OR REPLACE FUNCTION add_numbers_func
(a IN NUMBER, b IN NUMBER)
RETURN NUMBER IS
Result NUMBER;
BEGIN
 Result:= a + b;
Return result;
END add_numbers_func;

Calling the function:

The PL/SQL program to call this function is as follows:
DECLARE
answer NUMBER;
BEGIN
answer: = add_number_func (20, 25);
DBMS_OUTPUT.PUT_LINE(answer);
END;
Objective: To Create triggers in oracle.

THEORY & CONCEPTS

Triggers:

A trigger can include SQL and PL/SQL statements to execute as a unit and can invoke other stored
procedures. However, stored procedures and triggers differ in the following way.

(a) Triggers are implicitly executed by Oracle, when a table is modified – regardless of user or
applications acting on the table.
(b) A trigger can only be executed or fired by issuing any one of the INSERT, UPDATE or DELETE
action on a database table.
(c) Triggers do not accept arguments.

A general syntax for creating a trigger is:

CREATE [ OR REPLACE] TRIGGER trigger_name

[BEFORE | AFTER] triggering_event ON table_reference
[FOR EACH ROW [WHEN trigger_condition]]
DECLARE
Variable/Constant/Cursor/Exception etc declaration section
BEGIN
Executable section
EXCEPTION
Error handling section
END;

Trigger_name: Trigger_name is the name of the trigger

Triggering_event: Triggering_event typically consists of an INSERT, UPDATE or DELETE or a

combination of these operations.

Table_reference: Triggering_reference is the name of the table for which the trigger is defined.

Triggering_condition: Triggering_condition specifies a Boolean expression that must be true for the
trigger to fire. The trigger action is not executed if the trigger_condition evaluates
to false. Trigger_condition is specified using a WHEN clause, which is optional.

Restrictions on creating a Trigger

(a) COMMIT, ROLLBACK and SAVEPOINT cannot be used in Triggers.

(b) DDL statements (like CREATE, ALTER, DROP) are not allowed in the body of the trigger.
(c) The trigger body cannot declare any LONG variables.

Types of Triggers:

a) BEFORE trigger can be used in deciding whether we should permit a statement to complete.
 b) AFTER triggers, on the other hand, validate the UPDATE, INSERT and DELETE operations after
they have taken place and allow rollback on exceptions raised.
c) FOR EACH ROW option determines whether the trigger is a row or statement trigger.
If we specify FOR EACH ROW, the trigger fires once for each row affected by the trigger.
Without this specification, the statement trigger fires once regardless of the number of rows affected.

Packages:

Packages are used to group logically related PL/SQL types, items and subprograms encapsulated into
single entity. This encapsulation creates a true business object with many interfaces, each one handling a
specific business task. When an object is called within a package, the entire package is loaded into
memory. This load is completed in one operation for each defined object in the package specification. As
a result, further reference to package requires no disk input/output, which speeds up the application
execution.

Package Specification

The package specification contains the information about the contents of the package. Here, the user can
declare data type, variables, constants, exceptions, and stored procedures (procedures and functions).
However, it does not contain any code for anything declared. The specification defines the package’s
interface to the calling applications and the users. The syntax for creating the package specification is

CREATE [ OR REPLACE] PACKAGE package_name

[IS | AS]
Procecdure_specification |
Function_specification |
Variable_specification |
…….
Cursor_declaration
END [package_name];

• PL/SQL objects declared in the package_specification are global, they can be called or used by
external users who have the specific privilege.
• The elements with the package specification (procedure, function etc) are the same as they would be in
the declarative section of a PL/SQL anonymous block. In specification, it is not necessary to have all
type of elements.
• A package can contain only procedures and functions without having any variables and other type of
elements.

Package body

• A package body cannot be compiled without its respective package specification.

• The package body can be debugged, enhanced or replaced without recompiling the package
specification object.
• The complete example of package is given below.
 ASSIGNMENT: 12

1.Write PL/SQL statements for creating trigger which restricts any transaction on Sundays on transaction
table.
2.Create a package consisting of a procedure for updating the salary.

Solution1. CREATE OR REPLACE TRIGGER Invalid_day_check BEFORE INSERT ON transaction

FOR EACH ROW
DECLARE
x VARCHAR2(20);
BEGIN
SELECT RTRIM(TO_CHAR(SYSDATE, ‘DAY’), ‘ ‘) INTO x from DUAL;
IF x = ‘SUNDAY’ THEN
RAISE_APPLICATION_ERROR (-20001, ‘Transaction is not allowed’);
END IF;
END;

Solution2.This package has one subprogram; procedure, update_sal.

CREATE OR REPLACE PACKAGE emp_pack

IS
PROCEDURE update_sal (eno IN NUMBER);

END emp_pack;
CREATE OR REPLACE BODY emp_pack
IS
PROCEDURE update_sal (eno IN NUMBER)
IS
x EMP. Empno % type
y EMP. Sal % type
BEGIN
SELECT empno, sal INTO x, y FROM emp WHERE empno=eno;
IF y> 3000 THEN
UPDATE EMP SET Sal= sal*1.1 WHERE Empno =eno;
ELSIF y between 2000 AND 3000 THEN
UPDATE EMP SET Sal= Sal*1.05 WHERE Empno =eno;
ELSE
UPDATE EMP SET Sal= Sal*1.03 WHERE Empno =eno;
END IF;
Calling a Package

Calling a package means actually referencing one of its elements. Following is the method for calling an
element from a package.
DECLARE
x VARCHAR2(20);
BEGIN
emp_pack.update_sal (7633);
END;
 Student Report Format

Galgotias University
Plot No.2, Sector-17A, Yamuna Expresway, Greater
Noida, Gautam Buddh Nagar, U.P., India

Course
Name:_________________________________________________
Course Code:
_________________________________________________
School:_______________ Year:__________
Semester:________________
Program:___________________________
Session:___________________

Submitted By: Submitted To:

Name:______________________________
____________________
Admission No:__________________
______________________________
__________________
__________
 Name Semester Section Admission No.

Index
Sr. Name of Experiment Page Date of Signature of
No. No. Experiment Faculty

1. -------------------------------------------------------

2. -------------------------------------------------------

3. -------------------------------------------------------

4. -------------------------------------------------------

5. -------------------------------------------------------

6. -------------------------------------------------------

7. -------------------------------------------------------

8. -------------------------------------------------------

9.
-------------------------------------------------------
10.
-------------------------------------------------------
 Experiment No. 1
Objective/Aim: (Write the objective here)

Program: (Write Your program here)

Sample Run:

Show the sample output here