SRM INSTITUTE OF SCIENCE & TECHNOLOGY

FACULTY OF ENGINEERING AND TECHNOLOGY

RAMAPURAM CAMPUS, CHENNAI.

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

LAB MANUAL
REGULATION 2018
ACADEMIC YEAR 2022-2023

DATABASE MANAGEMENT SYSTEM LABORATORY – (18CSC303J)

 SUBJECT HANDLERS

SECTION FACULTY INCHARGE

EKE Ms.S.S.Kiruthika

COURSE CORDINATOR HOD/CSE

 TABLE OF CONTENTS

EXP
DATE TITLE OF THE EXPERIMENT
NO.

1. SQL DATA DEFINITION LANGUAGE COMMANDS

2. SQL DATA MANIPULATION LANGUAGE COMMANDS

3. SQL DCL AND TCL COMMANDS

4. IN BUILT FUNCTIONS IN SQL

5. CONSTRUCTION OF ER MODEL

6. NESTED QUERIES IN SQL

7. JOIN QUERIES IN SQL

8. SET OPRATORS AND VIEWS

9. PL/SQL CONDITIONAL AND ITERATIVE STATEMENTS

10. PL/SQL PROCEDURES

11. PL/SQL FUNCTIONS

12. PL/SQL CURSOR

13. PL/SQL EXCEPTION HANDLING

14. PL/SQL TRIGGER

15. MINI PROJECT- PAYROLL PROCESSING SYSTEM

 Ex.No :1 SQL DATA DEFINITION LANGUAGE COMMANDS
Date :

Aim:
To execute DDL commands for creating and modifying the schema of the database.
DDL COMMANDS:
DDL or Data Definition Language actually consists of the SQL commands that can be
used to define the database schema. It simply deals with descriptions of the database schema and
is used to create and modify the structure of database objects in database.
The DDL commands are:
1. CREATE
2. DROP
3. ALTER
4. TRUNCATE
5. RENAME
CREATE Command:
It is used to create databases, tables, triggers and other database objects.
Syntax:
CREATE TABLE table_name
(column_Name1 data_type (size of the column ),column_Name2 data_type (size of the column),col
umn_Name3 data_type (size of the column,.….,column_NameN data_type ( size of the column ) ) ;
DROP Command:
DROP Command is used to delete / remove the entire table from the database.
Syntax:
DROP TABLE table_name;
ALTER Command:
ALTER is a DDL command which changes or modifies the existing structure of the
database, and it also changes the schema of database objects. Adding/ dropping constraints of the
table can be done using the ALTER command.
Syntax to add a new field in the table:
ALTER TABLE table_name ADD column_name column_definition;
Syntax to modify the column of the table:
ALTER TABLE table_name MODIFY ( column_name column_datatype(size ));
Syntax to remove a column from the table:
ALTER TABLE table_name DROP Col_Name_1,col_Name_2,..,col_Name_N

TRUNCATE Command:
Truncate command is used to delete or remove all the records from the table.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 1

 Syntax:
TRUNCATE TABLE table_name;

RENAME Command:
Rename is used ro change the name of the existing table name.

Syntax:

RENAME TABLE old_table_name TO new_table_name;

Queries:
1. Create a table student with the attributes(sno,sname,age,sdob,sm1,sm2,sm3)
CREATE TABLE std(regno number(4) primary key, s_name varchar(30),dob date,m1 number(3),m2
number(3), m3 number(3));

2. Insert records into the table student.

INSERT INTO stdVALUES(4001, ‘Kalai’,’26-Mar-1995’,87,91,79);
INSERT INTO stdVALUES(4002, ‘Ashok’, ’09-Jun-1987,85,93,90);

3. Display the recordsof the relation student.

SELECT * FROM std;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 2

 4. Create a table student(id,name,game) and insert a record into it and display it.
CREATE TABLE student(idnumber(5),namevarchar(10),gamevarchar(20));
Tablecreated.
INSERT INTO student VALUES(1, ‘Mercy’, ‘Cricket’);
1rowcreated.
SELECT * FROM student;

5. Add the attribute age in the student relation and insert a record and display it.
ALTER TABLE student add(age number(4));

INSERT INTO studentVALUES(2, ‘Sharmi’, ‘Tennis’,19);

SELECT * FROM student;

6. Modify the size of id as 6 and game as 25 in the relation student and display the schema of it.
ALTER TABLE student modify (idnumber(6), gamevarchar(25));
DESC student;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 3

 7. Truncate the contents of the table student.
TRUNCATE TABLE student;

8. Drop the table student.

DROP TABLE student;

Result:
Thus the DDL commands for creating and modifying the database were executed and
verified successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 4

 Ex.No :2 SQL DATA MANIPULATION LANGUAGE COMMANDS
Date :

Aim:
To create database using Data Manipulation Commands for inserting, deleting, updating and
retrieving records of the tables.
DML COMMANDS:
Data manipulation language (DML) statements access and manipulate data in existing
tables. DML Commands are:
1. INSERT
2. SELECT
3. UPDATE
4. DELETE
INSERT COMMAND:
Insert command is used to insert values into table.
Syntax:INSERT INTO table_nameVALUES (value list)
SELECT COMMAND:
It is used to retrieve information from the table.it is generally referred to as querying the
table. We can either display all columns in a table or only specify column from the table.
Syntax:
SELECT * FROM table_name; // This query selects all rows from the table.
SELECT column_name_1, …..,column_name_nFROM table1, ..tablen,;
UPDATE COMMAND:
It is used to update the values present in the table based on the given condition.
Syntax :
UPDATE table_name SET [column_name = value ,…, column_name = value] [ WHERE
condition ];
DELETE COMMAND:
It is used to delete the records in the table.
Syntax : DELETE FROM table_name WHERE conditions;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 5

 1. Insert a single record into table persons(pid,firstname,lastname,address,city)
INSERT into persons VALUES (001,'nelson','raj','no25,annai street','chennai');

2. Insert more than a record into persons table using a single insert command.
INSERTINTO persons VALUES(&pid,'&firstname','&lastname','&address','&city');

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 6

 3. Display all the records of the table persons.
SELECT * FROM persons;

4. Display pid,firstname and city of all the persons.

SELECT pid, firstname, city FROM persons;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 7

 5. List out the cities where the persons are located.
SELECT DISTINCT(city) FROM persons;

6. List out the pid and firsname of persons who are located in Chennai
SELECT pid,firstname FROM persons where city= ‘Chennai’;

7. Display the details of the persons whose name is starting with ‘As__k%’.
SELECT *FROM persons where firstnameLIKE ‘As__k%’;

8. Display the firstname of the persons whose name ends with ‘a’.
SELECT firstnameFROM persons where firstnameLIKE ‘%a’;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 8

 9. Display the pid and firstname of persons in ascending order using the firstname.
SELECT pid,firstname FROM persons ORDER BY firstname;

10. Display the details of persons who are located either in Chennai or Bangalore.
SELECT * FROM persons WHERE city IN (‘Chennai’,’Bangalore’);
OR
SELECT * FROM persons WHERE city=‘Chennai’ OR city=‘Bangalore’;

11. Display the persons firstname and lastname whose pid is between 15 to 25.
SELECT pid,firstname,lastnameFROM persons WHEREpid BETWEEN 15 AND 25;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 9

 12. Retrieve the persons details whose lastname is NULL.
SELECT * FROM persons WHERE lastname is null;

13. Update the pid of persons as 11 whose pid is 21

UPDATE persons SETpid =11 WHERE pid=21;

14. Update the city of persons who are located at Pune as Bangalore.
UPDATE persons SET city= ‘Bangalore’ WHERE city= ‘Pune’.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 10

 15. Delete the record whose pid is 11.
DELETE * FROM persons WHERE pid=11;

Result:
Thus the DML commands for inserting, retrieving, updating and deleting the records in the
database were executed and verified successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 11

 Ex.No :3 SQL DCL AND TCL COMMANDS
Date :

Aim:
To create a database using DDL and DML Commands and execute Data Control Language
and Transaction Control Language commands on the database.
DCL COMMANDS:
DCL includes commands such as GRANT and REVOKE which mainly deal with the rights,
permissions, and other controls of the database system. The DCL commands are:
GRANT: This command gives users access privileges to the database.
REVOKE: This command withdraws the user’s access privileges given by using the
GRANT command.
TCL COMMANDS:
TCL commands deal with the transaction within the database. The TCL commands are:
COMMIT: Commits a Transaction.
ROLLBACK: Rollbacks a transaction in case of any error occurs.
SAVEPOINT:Sets a savepoint within a transaction.
PROGRAM:
SQL> connect
Enter user-name: system
Enter password:
Connected.

SQL> create table class1(id number(10),name varchar(10));

Table created.

SQL> insert into class1 values(5,'rahul');

1 row created.

SQL> update class1 set name='raj' where id=5;

1 row updated.

SQL>savepoint A;
Savepoint created.

SQL> insert into class1 values(6,'ram');

1 row created.

SQL> insert into class1 values(7,'vibhav');

1 row created.
SQL>savepoint B;
Savepoint created.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 12

 SQL> insert into class1 values(8,'sai');
1 row created.

SQL>savepoint C;
Savepoint created.

SQL> select * from class1;

ID NAME

5 raj
6 ram
7 vibhav
8 sai

SQL> rollback to B;
Rollback complete.

SQL> select * from class1;

ID NAME

5 raj
6 ram
7 vibhav

SQL> rollback to A;
Rollback complete.

SQL> select * from class1;

ID NAME

5 raj

SQL> insert into class1 values(6,'ram');

1 row created.

SQL> insert into class1 values(7,'vibhav');

1 row created.

SQL> insert into class1 values(8,'sai');

1 row created.

SQL>savepoint D;
Savepoint created.

SQL> insert into class1 values(9,'siva');

1 row created.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 13

 SQL> commit;
Commit complete.

SQL> select * from class1;

ID NAME

5 raj
6 ram
7 vibhav
8 sai
9 siva

SQL> rollback to D;
rollback to D
*
ERROR at line 1:
ORA-01086: savepoint 'D' never established

SQL> insert into class1 values(10,'tom');

1 row created.

SQL>savepoint E;
Savepoint created.

SQL> insert into class1 values(11,'sam');

1 row created.

SQL>savepoint F;
Savepoint created.

SQL> rollback to E;
Rollback complete.

SQL> select * from class1;

ID NAME

5 raj
6 ram
7 vibhav
8 sai
9 siva
10 tom
6 rows selected.

SQL> commit;
Commit complete.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 14

 SQL> select * from class1;
ID NAME

5 raj
6 ram
7 vibhav
8 sai
9 siva
10 tom
6 rows selected.

SQL> rollback to F;
rollback to F
*
ERROR at line 1:
ORA-01086: savepoint 'F' never established

Result:
Thus the database was created using DDL and DML Commands and DCL and TCL
commands on the database were executed and verified successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 15

 Ex.No : 4 IN BUILT FUNCTIONS IN SQL
Date :

Aim:
To execute DML commands using in built functions.
Create the following table and execute the following queries.
Table : Students12

Column Name Data Type

Regno Number(4)
s_name Varchar2(10)
Maths Number(3)
Science Number(3)
Social Number(3)

Functions:
COUNT( ) – It returns the number of records in the table.

MIN( ) – It returns the minimum value in the column.

MAX( ) – It returns the maximum value in the column.

SUM( ) – It returns the sum of all the values in a column.

AVG( ) – It returns the average of all the values in a column.

1. Create the table student, insert the records and display the details of the tale.
CREATE TABLE students12(regno number(4), s_name varchar(20), Maths number(3), Science
number(3), Social number(3));
INSERT INTO students12 VALUES(&regno,’&s_name’,&maths,&science,&social);
SELECT * FROM students12;

2. Display the number of students details stored in the table.

SELECT COUNT(*) AS No_of_students FROM students12;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 16

 3. Display the number of students who scored greater than 90 marks in science.
SELECT COUNT(*) AS No_of_students FROM students12 WHERE science>90;

4. Display the number of students who scored less than 50 marks in science.
SELECT COUNT(*) AS No_of_students FROM students12 WHERE science<50;

5. Find the maximum and minimum mark in Maths.

SELECT MAX(maths) AS maths_maximum FROM students12;

SELECT MIN(maths) AS maths_minimum FROM students12;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 17

 6. Find the total marks of each subject in the table.
SELECT SUM(maths) AS total_maths, SUM(science) AS total_science, SUM(social) AS
total_social FROM students12;

7. Find the average marks of each subject in the table.

SELECT AVG(maths) AS avg_maths, AVG(science) AS avg_science, AVG(social) AS
avg_social FROM students12;

8. Find the student details who scored maximum mark in science.

SELECT * FROM students12 WHERE science=(SELECT MAX(science) FROM sudents12);

9. Find the student details who scored minimum mark in maths.

SELECT * FROM students12 WHERE maths=(SELECT MIN(maths) FROM sudents12);

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 18

 10. Find the highest mark and lowest in the class.
SELECT MAX(maths+science+social) AS toal_max FROM students12;
SELECT MIN(maths+science+social) AS toal_min FROM students12;

Result:
Thus the DML commands using in built functions were executed and verified successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 19

 Ex.No : 5 CONSTRUCTION OF ER MODEL
Date :

Aim:
To construct a ER Model for the application to be constructed to a Database
Steps for drawing ER Diagram:
1. First, identify the entities in your database. In this case, we have three entities.
2. The second step involves identifying the relationships between the selected entities.
3. The third step involves identifying cardinalities .
4. The fourth step is identifying entity attributes . Make sure that every attribute is
mapped to only one entity; assign modifiers for those that belong to more than one.
5. Once you have identified the entities, relationships, cardinalities, and attributes, you
can now create your ER diagram. Here’s what our sample project will look like when
designed using the crow’s foot (IE) notation.
6. Entity: Entities are represented by rectangle . All table of database are treating as
entity.
7. Attributes: Attributes are represented by ellipses. Attributes are properties of entities.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 20

 ER DIAGRAM FOR TICKET RESERVATION SYSTEM
Bus
 BusNo
 Source
 Destination
 CoachType

Ticket
 TicketNo
 DOJ
 Address
 ContactNo
 BusNo
 SeatNo
 Source
 Destination

Passenger
 PassportID
 TicketNo
 Name
 ContactNo
 Age
 Sex
 Address

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 21

 Reservation
 PNRNo
 DOJ
 No_of_seats
 Address
 ContactNo
 BusNo
 SeatNo

Cancellation
 PNRNo
 DOJ
 SeatNo
 ContactNo
 Status

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 22

 CONCETPTUAL DESIGN OF ER MODEL:

Result:
Thus the ER model for Ticket Resevation System was constructed successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 23

 Ex.No : 6 NESTED QUERIES IN SQL
Date :
Aim:
To execute the nested queries using SQL commands.
Nested Query:
 SQL provides a mechanism for the nesting of subqueries. A subquery is a select-from-
where expression that is nested within another query.
 The nesting can be done in the following SQL query
select A , A , ..., A
1 2 n
from r , r , ..., r
1 2 m
where P
 P can be replaced with an expression of the form: B <operation> (subquery)
 where B is an attribute
Create table for the follow ing tw o relations and execute the given queries.
Table Name : department(d_no number(3) primary key, d_name varchar(20), location
varchar(20));
Table Name : Employees(e_idnumer(4) primary key, e_name varchar(15), d_no

number(3), doj date, designation varchar(15), salary numeric(8,2), foreign key d_no
references department(d_no));
Display the records of department and Emloyees relation.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 24

 1. List the employees who are working at Chennai.
Select * from employees where d_no in (select d_no from department where location='Chennai');

2. Find the employee id and name who is drawing the highest salary.
SELECT e_id, e_name FROM employees where salary=(SELECT MAX(salary) FROM
employees);

3. Display the employee details who are all working for sales department.
SELECT * FROM employees where d_no in(SELECT d_no from department where d_ name=
‘sales’);

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 25

 4. List the employees who are all getting salary greater than the average salary of all
employees.
SELECT e_name FROM employee WHERE salary>(SELECT avg(salary) FROM employee);

5. Display the employee id, name and salary of all employees who are getting the lowest salary
in each department.
SELECT e_name, d_no, salary FROM employees WHERE salary in (SELECT MIN(salary) FROM
employee GROUP BY d_no);

6. List the employee details who is getting the maximum salary in each department order by
dept.
SELECT e_name, d_no, salary FROM employee WHERE salary IN (SELECT MAX(salary)
FROM employee GROUP BY d_no) ORDER BY d_no;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 26

 7. Display the employee details in decreasing order of salary, who are all getting higher than
the average salary of all employees.
SELECT * FROM employees WHERE salary>(SELECT AVG(salary) FROM employees)Order
By salary desc;

8. Display the employee details who salary is greater than the average salary of employees
working in Bangalore.
SELECT * FROM employees WHERE salary >(SELECT AVG(salary) FROM employees WHERE
d_no in (SELECT d_no FROM department WHERE location = ‘Bangalore’));

9. Update the salary of employees who is getting the lowest salary in each department by
adding 5000.
UPDATE employees SET salary=salary+5000 WHERE salary IN (SELECT MIN(salary) FROM
employee GROUP BY d_no);

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 27

 10. Update the city of employees who are all working in sales department in Coimbatore to
Chennai.
UPDATE employees SET d_no=(SELECT d_no FROM department WHERE d_name=‘sales’ and
location=‘Chennai’ ) WHERE d_no in (SELECT d_no FROM department WHERE
d_name=‘sales’ and location=‘Coimbatore’);

Result:
Thus the Nested Queries using SQL commands were executed and verified
successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 28

 Ex.No : 7 JOIN QUERIES IN SQL
Date :
Aim:
To execute Join Queries using SQL commands.
JOIN EXPRESSIONS:
 Join operations take two relations and return as a result another relation.
 A join operation is a Cartesian product which requires that tuples in the two relations
match (under some condition). It also specifies the attributes that are present in the
result of the join
 The join operations are typically used as subquery expressions in the from clause
 Three types of joins:
o Natural join
o Inner join
o Outer join
 Natural join matches tuples with the same values for all common attributes, and
retains only one copy of each common column.
 An extension of the join operation that avoids loss of information.
 Computes the join and then adds tuples form one relation that does not match tuples in
the other relation to the result of the join.
 Uses null values.
 Three forms of outer join:
o left outer join
o right outer join
o full outer join
Create tables for the following relations and execute the given queries:
Salesman(s_id, s_name, s_city, s_comission)
Customer(cus_id, cus_name, cus_city, points, s_id)
Order(ord_no, pur_amt, ord_date, cus_id, s_id)
Create table and display the records in the table.
Create table salesman(s_id number(4) primary key, s_name varchar(20), s_city varchar(20),
s_comission numeric(3,2));
Table created.
Create table customer(cus_id number(4) primary key, cus_name varchar(20), cus_city
varchar(20), points number, s_id number(4), foreign key (s_id) references salesman(s_id));
Table created.
Create table orders2(ord-no number(5), pur_amt numeric(8,2),ord_date date, cus_id
number(3),s_id number(4), foreign key(cus_id) references customer(cus_id), foreign key (s_id)
references salesman(s_id));

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 29

 Table created.

1. Write a SQL query to find the salesperson and customer who reside in the same
city. SELECT salesman.name AS "Salesman", customer.cust_name, customer.cus_city FROM
salesman, customer WHERE salesman.city = customer.city;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 30

 2. Write a SQL query to find the salesperson(s) and the customer(s) he represents. Return
Customer Name, city, Salesman, commission.
SELECT a.cust_name AS "Customer Name", a.cus_city, b.name AS "Salesman",
b.s_commission FROM customer a INNER JOIN salesman b ON a.salesman_id=b.salesman_id;

3. Write a SQL query to find those customers with a grade less than 50. Return cust_name,
customer city, points, Salesman name and city. The result should be ordered by ascending
order of customer id.
SELECT a.cus_name, a.cus_city, a.points, b.s_name AS "Salesman", b.s_city FROM customer a
LEFT OUTER JOIN salesman b ON a.s_id=b.s_id WHERE a.points<50 ORDER BY a.cus_id;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 31

 4. Write a SQL state ment to generate a list in ascending order of salespersons who work
either for one or more customers or have not yet joined any of the customers.
SELECT a.cus_id,a.cus_city, b.s_name AS "Salesman", b.s_city FROM customer a RIGHT
OUTER JOIN salesman b ON b.s_id=a.s_id ORDER BY b.s_id;

5. Write a SQL statement to generate a report with the customer name, city, order no. order
date, purchase amount for only those customers on the list who must have points and placed
one or more orders or which order(s) have been placed by the customer.
SELECT a.cus_name,a.cus_city, b.ord_no, b.ord_date,b.pur_amt AS "Order Amount" FROM
customer a FULL OUTER JOIN orders b ON a.cus_id=b.cus_id WHERE a.points>50;

Result:
Thus the Join Queries using SQL commands were executed and verified successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 32

 Ex.No : 8 SET OPRATORS AND VIEWS
Date :
Aim:
To execute SQL Queries using set operators and views.
SET OPERATORS:
There are four major types of SQL operators, namely:
 Union - Combines distinct results of two or more SELECT statements.
 Union all - Combines all results of two or more SELECT statements, including
duplicates.
 Intersect - Returns only the common records obtained from two or more SELECT
statements.
 Minus - Returns only those records which are exclusive to the first table.
VIEW:
 A view provides a mechanism to hide certain data from the view of certain users.
 Any relation that is not of the conceptual model but is made visible to a user as a
“virtual relation” is called a view.
 Syntax : create view v as < query expression >
 where <query expression> is any legal SQL expression. The view name is represented
by v.
Tables: CSE(regno , cgpa) and ECE(regno, cgpa)
Create table and display the records in thetables.
Create table ECE(regno number(5) primary key, cgpa numeric(3,2));
Table Created.
Create table CSE(regno number(5) primary key, cgpa numeric(3,2));
Table created.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 33

 1. Display the distinct cgpa scored by ECE AND CSE Students.
SELECT cgpa FROM ECE UNION SELECT cgpa FROM CSE;

2. Display the cgpa(including duplicates) scored by ECE AND CSE Students.

SELECT cgpa FROM ECE UNION ALL SELECT cgpa FROM CSE;

3. Display the distinct cgpa scored by ECE AND CSE Students separatey.
SELECT cgpa FROM ECE MINUS SELECT cgpa FROM CSE;
SELECT cgpa FROM CSE MINUS SELECT cgpa FROM ECE;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 34

 4. Display the common cgpa scored by ECE AND CSE Students.
SELECT cgpa FROM ECE INTERSECT SELECT cgpa FROM CSE;

5. Create a view from the table students (regno,s_name,maths,science,social) that contains the
information about maths mark.
CREATE VIEW maths_student AS SELECT regno, s_name, maths FROM students;

6. Display the maximum maths mark using view.

SELECT MAX(maths) FROM maths_student;

7. Update the marks of regno 4005 to 43 in view and display the records in both view as well
as students table.
UPDATE maths_student SET maths = 43 WHERE regno=4005;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 35

 8. Drop the view and try to display it.
DROP VIEW maths_student;
View dropped.
SELECT * FROM maths_student;

Result:
Thus the SQL Queries using set operators and views were executed and verified
successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 36

 Ex.No : 9 PL/SQL CONDITIONAL AND ITERATIVE STATEMENTS
Date :
Aim:
To write PL/SQL procedure using conditional and iterative statements.
PL/SQL Procedure:
 Functions and Procedures allow “business logic” to be stored in the database and
executed from SQL statements.
 These can be defined either by the procedural component of SQL or by an external
programming language such as Java, C, or C++.
 Procedures and functions can be invoked also from dynamic SQL.
 SQL allows more than one procedure of the so long as the number of arguments of the
procedures with the same name is different.
 The name, along with the number of arguments, is used to identify the procedure.
Syntax:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;

1. Write a PL/SQL procedure to perform addition of two numbers.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 37

 2. Insert the records into the table employees using PL/SQL.

3. Insert the records into the table employees using PL/SQL by getting input from the user
and display the records of employees table.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 38

 4. Write PL/SQL procedure to retrieve the information of the employee whose employee id
is 1002.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 39

 5. Write PL/SQL procedure to find the sign of a given number using if..else statement.

6. Write PL/SQL procedure to update the salary of the employee whose employee id is
1002.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 40

 7. Write PL/SQL procedure to find the grade of a student using case statement.

8. Write PL/SQL procedure to find the sum of a number using while loop.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 41

 9. Write PL/SQL procedure to print the multiplication table using for loop.

Result:
Thus the PL/SQL procedures using conditional and iterative statements were executed
and verified successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 42

 Ex.No : 10 PL/SQL PROCEDURES
Date :
Aim:
To write PL/SQL procedure for manipulating the tables/database.
PL/SQL PROCEDURE:
A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The
simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −
Syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;

Where,

 procedure-name specifies the name of the procedure.

 [OR REPLACE] option allows the modification of an existing procedure.

 The optional parameter list contains name, mode and types of the
parameters. IN represents the value that will be passed from outside and OUT represents
the parameter that will be used to return a value outside of the procedure.

 procedure-body contains the executable part.

 The AS keyword is used instead of the IS keyword for creating a standalone procedure

Employees Table:

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 43

 1. Create a PL/SQL Procedure to display the employee details with particular employee id.

2. Create a PL/SQL Procedure to update the salary of a particular employee..

Result:
Thus the PL/SQL procedure to manipulating the tables were executed and verified
successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 44

 Ex.No : 11 PL/SQL FUNCTIONS
Date :
Aim:
To write PL/SQL functions for manipulating the tables/database.
PL/SQL FUNCTION:
 A PL/SQL function is created using the CREATE FUNCTION statement. The simplified
syntax for the CREATE OR REPLACE PROCEDURE statement is as follows
 Syntax:
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
 function-name specifies the name of the function.
 [OR REPLACE] option allows the modification of an existing function.
 The optional parameter list contains name, mode and types of the parameters. IN
represents the value that will be passed from outside and OUT represents the parameter
that will be used to return a value outside of the procedure.
 The function must contain a return statement.
 The RETURN clause specifies the data type you are going to return from the function.
 function-body contains the executable part.
 The AS keyword is used instead of the IS keyword for creating a standalone function
Table : Students
Display the records of the table student.
SELECT * from students12;

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 45

 1. Create a PL/SQL function to find the total of all marks for students.

2. Calling the PL/SQL function to calculate the total of one student.

3. Display the records of students table after calling the PL/SQL function.

Result:
Thus the PL/SQL functions for manipulating the tables was written and executed
successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 46

 Ex.No : 12 PL/SQL CURSOR
Date :
Aim:
To write PL/SQL procedures using implicit and explicit cursors for manipulating the
tables/database.
PL/SQL CURSOR:
 A cursor is a pointer to the context area. PL/SQL controls the context area through a
cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of
rows the cursor holds is referred to as the active set.
 A cursor can be named so that it could be referred to in a program to fetch and process the
rows returned by the SQL statement, one at a time. There are two types of cursor:
o Implicit cursors
o Explicit cursors
IMPLICIT CURSOR:
 Implicit cursors are automatically created by Oracle whenever an SQL statement is
executed, when there is no explicit cursor for the statement. Programmers cannot control
the implicit cursors and the information in it.
 Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit
cursor is associated with this statement. For INSERT operations, the cursor holds the data
that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the
rows that would be affected.
 In PL/SQL, Implicit cursors have attributes such as %FOUND, %ISOPEN,
%NOTFOUND, and %ROWCOUNT. The SQL cursor has additional
attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with
the FORALL statement.
EXPLICIT CURSOR:
 Explicit cursors are programmer-defined cursors for gaining more control over the context
area. An explicit cursor should be defined in the declaration section of the PL/SQL Block.
It is created on a SELECT Statement which returns more than one row.
 The syntax for creating an explicit cursor is
o CURSOR cursor_name IS select_statement;
 Working with an explicit cursor includes the following steps:
o Declaring the cursor for initializing the memory
o Opening the cursor for allocating the memory
o Fetching the cursor for retrieving the data
o Closing the cursor to release the allocated memory

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 47

 Table : Employees
Display the records of the table employees.

1. Write a PL/SQL procedure to update the salary of customers using implicit cursor
attributes.

Table : Students
Display the records of the table employees.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 48

 2. Write a PL/SQL procedure to update the total marks of all students in the table using
Explicit Cursor.

Result:
Thus the PL/SQL procedures using Implicit and Explicit Cursor for manipulating the
tables/database were written and executed successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 49

 Ex.No : 13 PL/SQL EXCEPTION HANDLING
Date :
Aim:
To write PL/SQL procedures using exception handling for manipulating the tables/
database.
EXCEPTION:
 An error condition during a program execution is called an exception in PL/SQL. PL/SQL
supports programmers to catch such conditions using EXCEPTION block in the program
and an appropriate action is taken against the error condition. There are two types of
exceptions:
o System-defined exceptions
o User-defined exceptions
Syntax:
The default exception will be handled using WHEN others THEN:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
........
WHEN others THEN
exception3-handling-statements
END;
/

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 50

 1. Write a PL/SQL procedure to handle in built exceptions while manipulating the table.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 51

 2. Write a PL/SQL procedure to handle user defined exceptions while manipulating the
table.

Result:
Thus the PL/SQL procedures using exception handling for manipulating the tables/
database were written and executed successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 52

 Ex.No : 14 PL/SQL TRIGGER
Date :
Aim:
To write PL/SQL trigger for manipulating the tables/database.
TRIGGER:
A trigger is a PL/SQL block or a PL/SQL procedure that executes implicitly whenever
a particular event takes place. It can either be:
 Application trigger: Fires whenever an event occurs with a particular application.
 Database Trigger: Fires whenever a data event (such as DML) occurs on a schema or
database.
Elements in a Trigger:
 Trigger timing
o For table: BEFORE, AFTER
o For view: INSTEAD OF
 Trigger event: INSERT, UPDATE, OR DELETE
 Table name: On table, view
 Trigger Type: Row or statement
 When clause: Restricting condition
 Trigger body: PL/SQL block
“Before triggers” execute the trigger body before the triggering DML event on a table.
These are frequently used to determine whether that triggering statement should be allowed to
complete. This situation enables you to eliminate unnecessary processing of the triggering
statement and it eventual rollback in cases where an exception is raised in the triggering action.
“After triggers” are used when the triggering statement is to be completed before the
triggering action and to perform a different action on the same triggering statement if a
BEFORE trigger is already present.
“Instead of Triggers” are used to provide a way of modifying views that Cannot be
modified directly through SQL DML statements because the view is not inherently modifiable.
You can write INSERT, UPDATE, and DELETE statements against the view. The INSTEAD
OF trigger works invisibly in the background performing the action coded in the trigger body
directly on the underlying tables.
Triggering user events:
 INSERT
 UPDATE
 DELETE

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 53

 Create a trigger for the employee table for updating the salaryof the
employees.
Trigger:

PL/SQL Procedure to update the salary of employees: After executing this

procedure trigger is auromaticallly executed.

Result:
Thus the PL/SQL trigger for manipulating the tables/ database was written and
executed successfully.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 54

 Ex.No : 15 MINI PROJECT-PAYROLL ROCESSING SYSTEM
Date :
Aim:
To create a database for payroll processing system using SQL and implement it using VB.
PROCEDURE:
1. Create a database for payroll processing which request the using SQL .
2. Establish ODBC connection.
3. In the administrator tools open data source ODBC.
4. Click add button and select oracle in ORA and click finish.
5. A window will appear given the data source home as oracle and select source name and
user id.
6. ADODC CONTROL FOR SALARY FORM
7. The above procedure must be follow except the table ,A select the table as salary.
8. Write appropriate Program in form each from created in VB from each from created in
VB form project.
PROGRAM:
SQL>create table emp(eno number primary key,enamr varchar(20),age number,addr
varchar(20),DOB date,phno number(10));
Table created.
SQL>create table salary(enonumber,edesig varchar(10),basic
number,danumber,hranumber,pfnumber,mcnumber,metnumber,foreign key(eno) references
emp);
Table created.
TRIGGER to calculate DA,HRA,PF,MC
SQL> create or replace trigger employ
2 after insert on salary
3 declare
4 cursor cur is select eno,basic from salary;
5 begin
6 for cur1 in cur loop
7 update salary set
8 hra=basic*0.1,da=basic*0.07,pf=basic*0.05,mc=basic*0.03 where hra=0;
9 end loop;
10 end;
11 /
Trigger created.
PROGRAM FOR FORM 1
Private Sub emp_Click()
Form2.Show
End Sub
Private Sub exit_Click()
Unload Me
End Sub

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 55

 Private Sub salary_Click()
Form3.Show
End Sub

PROGRAM FOR FORM 2

Private Sub add_Click()
Adodc1.Recordset.AddNew MsgBox "Record added"
End Sub
Private Sub clear_Click()
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Text6.Text = ""
End Sub
Private Sub delte_Click()
Adodc1.Recordset.Delete MsgBox "Record Deleted"
If Adodc1.Recordset.EOF = True Then
Adodc1.Recordset.MovePrevious
End If
End Sub
Private Sub exit_Click()
Unload Me
End Sub
Private Sub main_Click()
Form1.Show
End Sub
Private Sub modify_Click()
Adodc1.Recordset.Update
End Sub

PROGRAM FOR FORM 3

Private Sub add_Click()
Adodc1.Recordset.AddNew MsgBox "Record added"
End Sub
Private Sub clear_Click()
Text1.Text = "" Text2.Text = "" Text3.Text = "" Text4.Text = "" Text5.Text = "" Text6.Text =
""
End Sub
Private Sub delte_Click()
Adodc1.Recordset.Delete MsgBox "Record Deleted"
If Adodc1.Recordset.EOF = True Then
Adodc1.Recordset.MovePrevious
End If
End Sub
Private Sub exit_Click()
Unload Me

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 56

 End Sub
Private Sub main_Click()
Form1.Show
End Sub
Private Sub modify_Click()
Adodc1.Recordset.Update
End Sub

OUTPUT:

Result:
Thus the database for payroll processing system using SQL was implemented using VB.

SRM IST, RAMAPURAM 18CSC303J-DATABASEMANAGEMENTSYSTEMS 57