FRANCIS XAVIER ENGINEERING COLLEGE,

TIRUNELVELI
DEPARTMENT OF COMPUTER SCIENCE ENGINEERING

21CS4611 – DATABASE MANAGEMENT SYSTEMS

LABORATORY RECORD
I Year B.E CSE / First Semester

2022 – 2023 / EVEN SEMESTER

Register Number : ________________________________________________

Name : ________________________________________________

1
 FRANCIS XAVIER ENGINEERING COLLEGE,
TIRUNELVELI – 627003

ROLL NO ……………………….

BONAFIDE CERTIFICATE

Certified that this is a bonafide record of work done

by Selvan/Selvi……………………………………………………
with Reg. No……………………………………………………..of
IV semester in B.E –Computer Science Engineering branch of
this institution in the 21CS4611 – Database Management Systems
Laboratory during the academic year 2022 – 2023.

Staff-in charge HOD

Submitted for the University Practical Examination held on…………………

Internal Examiner External Examiner

2
 INDEX
S. No List Of CO
Experiments
1 Student should decide on a case study and formulate the problem CO
statement. 1
Conceptual Designing using ER Diagrams (Identifying entities,
2 attributes, keys and relationships between entities, cardinalities, CO
1
generalization, specialization
etc.) Note: Student is required to submit a document by drawing ER
Diagram
Converting ER Model to Relational Model (Represent entities and
3 relationships CO
in Tabular form, represent attributes as columns, identifying keys) 2
Note: Student is required to submit a document showing the tables
created from ER Model.
Creation of Tables using SQL- Overview of using SQL tool, Data
4 types in SQL, Creating Tables (along with Primary and Foreign keys), CO
2
Altering Tables and Dropping Tables, Constraints

5 Practicing DDL commands, Integrity constraints, DML commands CO

3
6 Practicing DCL, TCL commands, views and operations on views CO
3
7 Practicing Queries using ANY, ALL, IN, EXISTS, NOT EXISTS, CO
UNION, 3
INTERSECT etc.
8 Practicing Sub queries (Nested, Correlated) and Joins. CO
3
9 Practice Queries using COUNT, SUM, AVG, MAX, MIN, CO
GROUP BY, 3
HAVING, VIEWS Creation and Dropping.
10 Practicing on Triggers - creation of trigger, Insertion using trigger, CO
Deletion using 4
trigger, Updating using trigger
11 Procedures- Creation of Stored Procedures, Execution of CO
Procedure, and 4
Modification of Procedure.
12 Cursors- Declaring and Opening Cursor, Fetching the data, closing the CO
cursor. 4

3
 Ex. No: 1 Student Should Decide On A Case Study And Formulate The
Problem Statement.

AIM:

To decide on a case study and formulate the problem statement.

PROBLEM STATEMENT:

HOSPITAL MANAGEMENT SYSTEM

XYZ hospital is a multi-specialty hospital that includes a number of

departments, rooms, doctors, nurses, compounders, and other staff working in the
hospital. Patients having different kinds of ailments come to the hospital and get
checkup done from the concerned doctors. If required they are admitted in the hospital
and discharged after treatment. The aim of this case study is to design and develop a
database for the hospital to maintain the records of various departments, rooms, and
doctors in the hospital. It also maintains records of the regular patients, patients
admitted in the hospital, the checkup of patients done by the doctors, the patients that
have been operated, and patients discharged from the hospital.

DESCRIPTION:

In hospital, there are many departments like Orthopedic, Pathology,

Emergency, Dental, Gynecology, Anesthetics, I.C.U., Blood Bank, Operation Theater,
Laboratory, M.R.I., Neurology, Cardiology, Cancer Department, Corpse, etc. There is
an OPD where patients come and get a card (that is, entry card of the patient) for
check up from the concerned doctor. After making entry in the card, they go to the
concerned doctor’s room and the doctor checks up their ailments. According to the
ailments, the doctor either prescribes medicine or admits the patient in the concerned
department. The patient may choose either private or general room according to
his/her need. But before getting admission in the hospital, the patient has to fulfill
certain formalities of the hospital like room charges, etc. After the treatment is
completed, the doctor discharges the patient. Before discharging from the hospital, the
patient again has to complete certain formalities of the hospital like balance charges,
test charges, operation charges (if any), blood charges, doctors’ charges, etc. Next, we
talk about the doctors of the hospital. There are two types of the doctors in the
hospital, namely, regular doctors and call on doctors. Regular doctors are those
doctors who come to the hospital daily. Calls on doctors are those doctors who are
called by the hospital if the concerned doctor is not available.

4
TABLE DESCRIPTION:
Following are the tables along with constraints used in Hospital Management
database.
1. DEPARTMENT: This table consists of details about the various
departments in the hospital. The information stored in this table includes department
name, department location, and facilities available in that department. Constraint:
Department name will be unique for each department.
2. ALL_DOCTORS: This table stores information about all the doctors
working for the hospital and the departments they are associated with. Each doctor is
given an identity number starting with DR or DC prefixes only. Constraint: Identity
number is unique for each doctor and the corresponding department should exist in
DEPARTMENT table.
3. DOC_REG: This table stores details of regular doctors working in the
hospital. Doctors are referred to by their doctor number. This table also stores
personal details of doctors like name, qualification, address, phone number, salary,
date of joining, etc. Constraint: Doctor’s number entered should contain DR only as a
prefix and must exist in ALL_DOCTORS table.
4. DOC_ON_CALL: This table stores details of doctors called by hospital
when additional doctors are required. Doctors are referred to by their doctor number.
Other personal details like name, qualification, fees per call, payment due, address,
phone number, etc., are also stored. Constraint: Doctor’s number entered should
contain DC only as a prefix and must exist in ALL_DOCTORS table
5. PAT_ENTRY: The record in this table is created when any patient arrives
in the hospital for a checkup. When patient arrives, a patient number is generated
which acts as a primary key. Other details like name, age, sex, address, city, phone
number, entry date, name of the doctor referred to, diagnosis, and department name
are also stored. After storing the necessary details patient is sent to the doctor for
checkup. Constraint: Patient number should begin with prefix PT. Sex should be M or
F only. Doctor’s name and department referred must exist.
6. PAT_CHKUP: This table stores the details about the patients who get
treatment from the doctor referred to. Details like patient number from patient entry
table, doctor number, date of checkup, diagnosis, and treatment are stored. One more
field status is used to indicate whether patient is admitted, referred for operation or is a
regular patient to the hospital. If patient is admitted, further details are stored in
PAT_ADMIT table. If patient is referred for operation, the further details are stored in
PAT_OPR table and if patient is a regular patient to the hospital, the further details are
stored in PAT_REG table. Constraint: Patient number should exist in PAT_ENTRY
table and it should be unique.
7. PAT_ADMIT: When patient is admitted, his/her related details are stored in
this table. Information stored includes patient number, advance payment, mode of
payment, room number, department, date of admission, initial condition, diagnosis,
treatment, number of the doctor under whom treatment is done, attendant name, etc.
Constraint: Patient number should exist in PAT_ENTRY table. Department, doctor
number, room number must be valid.

5
 8. PAT_DIS: An entry is made in this table whenever a patient gets discharged
from the hospital. Each entry includes details like patient number, treatment given,
treatment advice, payment made, mode of payment, date of discharge, etc. Constraint:
Patient number should exist in PAT_ENTRY table.
9. PAT_REG: Details of regular patients are stored in this table. Information
stored includes date of visit, diagnosis, treatment, medicine recommended, status of
treatment, etc. Constraint: Patient number should exist in patient entry table. There
can be multiple entries of one patient as patient might be visiting hospital repeatedly
for checkup and there will be entry for patient’s each visit.
10. PAT_OPR: If patient is operated in the hospital, his/her details are stored
in this table. Information stored includes patient number, date of admission, date of
operation, number of the doctor who conducted the operation, number of the operation
theater in which operation was carried out, type of operation, patient’s condition
before and after operation, treatment advice, etc. Constraint: Patient number should
exist in PAT_ENTRY table. Department, doctor number should exist or should be
valid.
11. ROOM_DETAILS: It contains details of all rooms in the hospital. The
details stored in this table include room number, room type (general or private), status
(whether occupied or not), if occupied, then patient number, patient name, charges per
day, etc. Constraint: Room number should be unique. Room type can only be G or P
and status can only be Y or N.

RESULT:
Thus, the case study was analyzed successfully.

6
 Ex No:2 Conceptual Designing Using ER Diagrams(Identifying Entities,
Attributes, Keys And Relationship Between Entities, Cardinalities,
Generalization, Specialization Etc..) Note: Student Is Required To Submit A
Document By Drawing ER Diagram.

AIM:

To draw the ER Diagram using Conceptual Designing.

ER DIAGRAM:

RESULT:

Thus, the ER Diagram for Hospital Management System has been drawn
successfully.

7
 Ex No: 3 Converting ER Model To Relational Model (Represent Entities
And Relationships In Tabular Form, Represent Attributes As Columns,
Identifying Keys) Note: Student Is Required To Submit A Document
Showing The Tables Created From ER Model.

AIM:

To convert ER Model to Relational Model.

PROBLEM DEFINITION:

In this example problem, you will create a database for an organization with many
departments. Each department has employees and employees have dependents. To
create a database for the company, read the description and identify all the entities
from the description of the company.

 The company organized into departments and departments have

employees working in it.
 Attributes of Department are dno, dname. Attributes of Employee
include eno, name, dob,gender, doj, designation, basic_pay, panno,
skills. Skills are multi-valued attribute.
 The Department has a manager managing it. There are also supervisors
in Department who supervises a set of employees.
 Each Department enrolls a number of projects. Attributes of Project
are pcode, pname. A project is enrolled by a department. An employee
can work on any number of projects on a given day. The Date of
employee work in-time and out-time has to keep track.
 The Company also maintains details of the dependents of each
employee. Attributes of dependent include dname, dob,
gender and relationship with the employee.

ER DIAGRAM:

From the real-world description of the organization, we were able to identify the
following entities. These entities will become the basis for an entity-relationship
diagram or model.

8
RELATIONAL MODEL:

The next step in the database design is to convert the ER Model into the Relational
Model. In the Relational Model, we will define the schema for relations and their
relationships. The attributes from the entity-relationship diagram will become fields
for a relationship and one of them is a primary field or primary key. It is usually
underlined in the entity-relationship diagram.

An entity in a relational model is a relation. For example, the entity Dependent is a

relation in the relational model with all the attributes as fields – eno, dname, dob,
gender, and relationship.

Here is the Relational Model for above diagram of the company database. This the
result after converting ER model into relational model.

9
RESULT:

Thus, the conversion of ER Model to Relational Model has been executed

successfully.

10
 Ex. No: 4 Creation Of Tables Using SQL- Overview Of Using SQL Tool,
Data Types In SQL, Creating Tables (Along With Primary And Foreign
Keys), Altering Tables And Dropping Tables, Constraints.
AIM:
To implement Creation of Tables using SQL, Overview of using SQL tool,
Data types in SQL, Creating Tables (along with Primary and Foreign keys), Altering
Tables and Dropping Tables, Constraints in ORACLE.

CREATION OF TABLES USING SQL:

CREATE Command:
This command is used to create a table or an object.

DATA TYPES IN SQL:

char, varchar(size), varchar2(size), date, number, number(size)
CONSTRAINTS:
Constraints are specified as a part of DDL statements (Mainly Create command) in the
column definition.

Not Null Constraint: When a column is defined as not null, then the column becomes
a mandatory column.
Syntax: Column name datatype size not null;
Column name datatype Constraint <constraint-name> not null;
Check Constraint: Check constraints must be specified as the logical expression that
evaluates either TRUE or FALSE.
Syntax: Column name datatype (size) check (logical expression);
Column name datatype Constraint <con-name> check (logical
Expression);

Unique Constraint: The purpose of the unique key is to ensure that information in
the column is distinct. Null values are allowed.
Syntax: Columnname datatype (size) unique
Columnname datatype (size) unique constraint <cons-name> unique
CREATING TABLES (ALONG WITH PRIMARY AND FOREIGN
KEYS):

11
Primary Key: A Primary key is a one or more column in a table used to uniquely
identify each row in the table. Primary key cannot have null values.
Syntax: Column name datatype (size) primary key
Column name datatype (size) constraint <cons-name> primary key

Foreign Key: A Foreign key is a column in a (referencing) table that references a

(Primary key) field of one other table (Parent Table)
Syntax: Column name datatype (size) references
<referenced table(or) Parent table > (<referenced field name>)
ALTERING AND DROPPING TABLES:
ALTER Command:
This command is used to add a field or modify the definition of field or
column.
DROP Command:
This command is used to delete the entire table along with the structure.

IMPLEMENTATION:
SQL> create table equip030(id number(3) unique, name varchar(10) not null,
number(3) check(amount<1000));

Table created.
SQL> desc equip030;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER (3)
NAME NOT NULL VARCHAR2(10)
AMOUNT NUMBER (3)
SQL> insert into equip030 values(&id,'&name',&amount);
Enter value for id: 001
Enter value for name: raj
Enter value for amount: 987
old 1: insert into equip030 values(&id,'&name',&amount)
12
new 1: insert into equip030 values(001,'raj',987)

1 row created.
SQL> /
Enter value for id: 002
Enter value for name:
Enter value for amount: 450
old 1: insert into equip030 values(&id,'&name',&amount)
new 1: insert into equip030 values(002,'',450)
insert into equip030 values(002,'',450)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SECONDIT"."EQUIP030"."NAME")
SQL> /
Enter value for id: 002
Enter value for name: kumar
Enter value for amount: 478
old 1: insert into equip030 values(&id,'&name',&amount)
new 1: insert into equip030 values(002,'kumar',478)
insert into equip030 values(002,'kumar',478)
*
ERROR at line 1:
ORA-00001: unique constraint (SECONDIT.SYS_C001413) violated
SQL> /
Enter value for id: 003
Enter value for name: pavi
Enter value for amount: 3654
old 1: insert into equip030 values(&id,'&name',&amount)
new 1: insert into equip030 values(003,'pavi',3654)
insert into equip030 values(003,'pavi',3654)
*

13
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
SQL> select * from equip030;

ID NAME AMOUNT
---------- ---------- ----------
1 raj 987
2 selvi 599

SQL> create table equip01(id number(3) primary key,name varchar(10));

Table created.
SQL> insert into equip01 values(&id,'&name');
Enter value for id: 101
Enter value for name: raj
old 1: insert into equip01 values(&id,'&name')
new 1: insert into equip01 values(101,'raj')
1 row created.
SQL> /
Enter value for id: 102
Enter value for name: naveen
old 1: insert into equip01 values(&id,'&name')
new 1: insert into equip01 values(102,'naveen')
1 row created.
SQL> /
Enter value for id: 103
Enter value for name: selvi
old 1: insert into equip01 values(&id,'&name')
new 1: insert into equip01 values(103,'selvi')
1 row created.

SQL> create table equip02(id number(3) references equip01(id),sal number(5));

14
Table created.
SQL> insert into equip02 values(&id,&sal);
Enter value for id: 101
Enter value for sal: 50000
old 1: insert into equip02 values(&id,&sal)
new 1: insert into equip02 values(101,50000)

1 row created.
SQL> /
Enter value for id: 200
Enter value for sal: 30500
old 1: insert into equip02 values(&id,&sal)
new 1: insert into equip02 values(200,30500)
insert into equip02 values(200,30500)
*
ERROR at line 1:
ORA-02291: integrity constraint (SECONDIT.SYS_C001519) violated - parent key
not found
SQL> delete from equip01 where id=101;
delete from equip01 where id=101
*
ERROR at line 1:
ORA-02292: integrity constraint (SECONDIT.SYS_C001519) violated - child record
Found
SQL> create table equip03 as select * from equip01;

Table created.
SQL> create table equip04 as select * from equip02;

Table created.
SQL> drop table equip02;

15
Table dropped.
SQL> drop table equip01;

Table dropped.
SQL> create table equip1(id number(3) primary key,name varchar(10) not null);

Table created.
SQL> desc equip1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(10)
SQL>alter table equip1 modify(name varchar(25) not null);
Table altered.
SQL> insert into equip1 values(&id,'&name');
Enter value for id: 123
Enter value for name: raja
old 1: insert into equip1 values(&id,'&name')
new 1: insert into equip1 values(123,'raja')

1 row created.
SQL> insert into equip1 values(&id,'&name');
Enter value for id: 124
Enter value for name: roja
old 1: insert into equip1 values(&id,'&name')
new 1: insert into equip1 values(124,'roja')

1 row created.
SQL>select * from equip1;
ID NAME

16
----------- ------------------------
123 raja
124 roja

SQL> create table equip2(id number(3) references equip1(id) on delete cascade,exp

number(2));
Table created.
SQL> desc equip2;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(3)
EXP NUMBER(2)
SQL> insert into equip2 values(&id,&exp);
Enter value for id: 123
Enter value for exp: 9
old 1: insert into equip2 values(&id,&exp)
new 1: insert into equip2 values(123,9)
1 row created.
SQL>

SQL> delete equip1;

1 row deleted.

SQL> select * from equip1;

no rows selected
RESULT:
Thus, Creation of Tables using SQL- Overview of using SQL tool, Data
types in SQL, Creating Tables (along with Primary and Foreign keys), Altering
Tables and Dropping Tables, Constraints has been executed successfully.

17
Ex. No: 5 Practicing DDL Commands, Integrity Constraints, DML Commands.

AIM:
To implement Data Definition Language(DDL), Integrity constraints and Data
Manipulation Language(DML) commands in ORACLE.

DDL Commands:( Data Definition Language)

DDL commands are used to create an object, alter the structure of an object and
also drop the object created.

CREATE Command:
This command is used to create a table or an object.
ALTER Command:
This command is used to add a field or modify the definition of field or
column.
TRUNCATE Command:
This command is used to delete all the rows of a table but not the structure of
the table.
DROP Command:
This command is used to delete the entire table along with the structure.
Note:
i) DESC Command:
This command is used to describe the table structure.( field or column name,
datatype, null?)
ii) Data types:
char, varchar(size),varchar2(size),date,number,number(size)

iii) ORACLE implicitly commits the current transaction before and after every
Data Definition Language statement.

QUERIES:

SQL> create table Tel(name varchar2(10),telno number(7));

18
Table created.
SQL> desc tel;

Name Null? Type

----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
TELNO NUMBER(7)

SQL> alter table tel add(city varchar2(10));

Table altered.

SQL> desc tel;

Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
TELNO NUMBER(7)
CITY VARCHAR2(10)

SQL> alter table tel modify(city varchar2(15));

Table altered.

SQL> desc tel;

Name Null? Type
----------------------------------------- -------- ---------------
NAME VARCHAR2(10)
TELNO NUMBER(7)
CITY VARCHAR2(15)

SQL> insert into tel values('Aarthi',2502157,'Chennai');

1 row created.
SQL> insert into tel values('Banu',2345678,'Mumbai');
1 row created.
SQL> insert into tel values('Chitra',2502387,'Tvl');
1 row created.
SQL> insert into tel values('Sankar',2330289,'Tvl');
1 row created.
SQL> select * from tel;
NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Chitra 2502387 Tvl
Sankar 2330289 Tvl

SQL> select distinct city from tel;

19
CITY
---------------
Chennai
Mumbai
Tvl

SQL> select * from tel order by telno;

NAME TELNO CITY
---------- ---------- ---------------
Sankar 2330289 Tvl
Banu 2345678 Mumbai
Aarthi 2502157 Chennai
Chitra 2502387 Tvl

SQL> select * from tel order by name desc;

NAME TELNO CITY
---------- ---------- ---------------
Sankar 2330289 Tvl
Chitra 2502387 Tvl
Banu 2345678 Mumbai
Aarthi 2502157 Chennai

SQL> select * from tel where name='Sankar';

NAME TELNO CITY
---------- ---------- ---------------
Sankar 2330289 Tvl

SQL> select * from tel where name='Chitra';

NAME TELNO CITY
---------- ---------- ---------------
Chitra 2502387 Tvl
SQL> select * from tel where name='Banu';

NAME TELNO CITY

---------- ---------- ---------------
Banu 2345678 Mumbai

SQL> select * from tel;

NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Sankar 2330289 Tvl
Chitra 2502387 Tvl

20
SQL> truncate table tel;

Table truncated.

SQL> select * from tel;

no rows selected

SQL> desc tel;

Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
TELNO NUMBER(7)
CITY VARCHAR2(15)

SQL> drop table tel;

Table dropped.

SQL> desc tel;

Object to be described could not be found.

INTEGRITY CONSTRAINTS:

DESCRIPTION:
Constraints are specified as a part of DDL statements (Mainly Create
command) in the column definition.
DOMAIN INTEGRITY CONSTRAINTS:
Not Null Constraint: When a column is defined as not null, then the column becomes
a mandatory column.
Syntax: Column name datatype size not null;
Column name datatype Constraint <constraint-name> not null;
Check Constraint: Check constraints must be specified as the logical expression that
evaluates either TRUE or FALSE.
Syntax: Column name datatype (size) check (logical expression);
Column name datatype Constraint <con-name> check (logical

21
Expression);
ENTITY INTEGRITY CONSTRAINTS:
Primary Key: A Primary key is a one or more column in a table used to uniquely
identify each row in the table. Primary key cannot have null values.
Syntax: Column name datatype (size) primary key
Column name datatype (size) constraint <cons-name> primary key
Unique Constraint: The purpose of the unique key is to ensure that information in
the column is distinct. Null values are allowed.
Syntax: Columnname datatype (size) unique
Columnname datatype (size) unique constraint <cons-name> unique
REFERENTIAL INTEGRITY CONSTRAINTS:
Foreign Key: A Foreign key is a column in a (referencing) table that references a
(Primary key) field of one other table (Parent Table)
Syntax: Column name datatype (size) references
<referenced table(or) Parent table >(<referenced field name>)
IMPLEMENTATION OF INTEGRITY CONSTRAINTS:
SQL> create table equip030(id number(3) unique,name varchar(10) not null,amount
number(3) check(amount<1000));

Table created.
SQL> desc equip030;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(3)
NAME NOT NULL VARCHAR2(10)
AMOUNT NUMBER(3)
SQL> insert into equip030 values(&id,'&name',&amount);
Enter value for id: 001
Enter value for name: raj
Enter value for amount: 987
old 1: insert into equip030 values(&id,'&name',&amount)

22
new 1: insert into equip030 values(001,'raj',987)

1 row created.
SQL> /
Enter value for id: 002
Enter value for name:
Enter value for amount: 450
old 1: insert into equip030 values(&id,'&name',&amount)
new 1: insert into equip030 values(002,'',450)
insert into equip030 values(002,'',450)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SECONDIT"."EQUIP030"."NAME")
SQL> /
Enter value for id: 002
Enter value for name: kumar
Enter value for amount: 478
old 1: insert into equip030 values(&id,'&name',&amount)
new 1: insert into equip030 values(002,'kumar',478)
insert into equip030 values(002,'kumar',478)
*
ERROR at line 1:
ORA-00001: unique constraint (SECONDIT.SYS_C001413) violated
SQL> /
Enter value for id: 003
Enter value for name: pavi
Enter value for amount: 3654
old 1: insert into equip030 values(&id,'&name',&amount)
new 1: insert into equip030 values(003,'pavi',3654)
insert into equip030 values(003,'pavi',3654)
*

23
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
SQL> select * from equip030;

ID NAME AMOUNT
---------- ---------- ----------
1 raj 987
2 selvi 599

SQL> create table equip01(id number(3) primary key,name varchar(10));

Table created.
SQL> insert into equip01 values(&id,'&name');
Enter value for id: 101
Enter value for name: raj
old 1: insert into equip01 values(&id,'&name')
new 1: insert into equip01 values(101,'raj')
1 row created.
SQL> /
Enter value for id: 102
Enter value for name: naveen
old 1: insert into equip01 values(&id,'&name')
new 1: insert into equip01 values(102,'naveen')
1 row created.
SQL> /
Enter value for id: 103
Enter value for name: selvi
old 1: insert into equip01 values(&id,'&name')
new 1: insert into equip01 values(103,'selvi')
1 row created.

SQL> create table equip02(id number(3) references equip01(id),sal number(5));

24
Table created.
SQL> insert into equip02 values(&id,&sal);
Enter value for id: 101
Enter value for sal: 50000
old 1: insert into equip02 values(&id,&sal)
new 1: insert into equip02 values(101,50000)

1 row created.
SQL> /
Enter value for id: 200
Enter value for sal: 30500
old 1: insert into equip02 values(&id,&sal)
new 1: insert into equip02 values(200,30500)
insert into equip02 values(200,30500)
*
ERROR at line 1:
ORA-02291: integrity constraint (SECONDIT.SYS_C001519) violated - parent key
not found
SQL> delete from equip01 where id=101;
delete from equip01 where id=101
*
ERROR at line 1:
ORA-02292: integrity constraint (SECONDIT.SYS_C001519) violated - child record
Found
SQL> create table equip03 as select * from equip01;

Table created.
SQL> create table equip04 as select * from equip02;

Table created.
SQL> drop table equip02;

25
Table dropped.
SQL> drop table equip01;

Table dropped.
SQL> create table equip1(id number(3) primary key,name varchar(10) not null);

Table created.
SQL> desc equip1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(10)
SQL> insert into equip1 values(&id,'&name');
Enter value for id: 123
Enter value for name: raja
old 1: insert into equip1 values(&id,'&name')
new 1: insert into equip1 values(123,'raja')

1 row created.
SQL> create table equip2(id number(3) references equip1(id) on delete cascade,exp
number(2));
Table created.
SQL> desc equip2;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(3)
EXP NUMBER(2)
SQL> insert into equip2 values(&id,&exp);
Enter value for id: 123
Enter value for exp: 9

26
old 1: insert into equip2 values(&id,&exp)
new 1: insert into equip2 values(123,9)
1 row created.

SQL> delete equip1;

1 row deleted.

SQL> select * from equip1;

no rows selected

DML Commands: ( Data Manipulation Language)

DML commands are used to insert, view, update and delete the values of an
object.
INSERT Command:
This command is used to insert a set of data values into the tables as defined in

the create table command.

SELECT Command:
This command is used to view particular data records or columns.
UPDATE Command:
This command is used to update and change the data values of the table.
DELETE Command:
This command is used to delete a particular record or all records of the table.
Queries:
SQL> create table Tel(name varchar2(10),telno number(7), city varchar2(15));
Table created.
SQL> desc tel;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
TELNO NUMBER(7)
CITY VARCHAR2(15)

27
SQL> insert into tel values('Aarthi',2502157,'Chennai');
1 row created.
SQL> insert into tel values('Banu',2345678,'Mumbai');
1 row created.
SQL> insert into tel values('Chitra',2502387,'Tvl');
1 row created.
SQL> insert into tel values('Sankar',2330289,'Tvl');
1 row created.
SQL> select * from tel;
NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Chitra 2502387 Tvl
Sankar 2330289 Tvl

SQL> select distinct city from tel;

CITY
---------------
Chennai
Mumbai
Tvl

SQL> select * from tel order by telno;

NAME TELNO CITY
---------- ---------- ---------------
Sankar 2330289 Tvl
Banu 2345678 Mumbai
Aarthi 2502157 Chennai
Chitra 2502387 Tvl

SQL> select * from tel order by name desc;

NAME TELNO CITY
---------- ---------- ---------------
Sankar 2330289 Tvl
Chitra 2502387 Tvl
Banu 2345678 Mumbai
Aarthi 2502157 Chennai

SQL> select * from tel where name='Sankar';

NAME TELNO CITY
---------- ---------- ---------------
Sankar 2330289 Tvl

SQL> select * from tel where name='Chitra';

28
NAME TELNO CITY
---------- ---------- ---------------
Chitra 2502387 Tvl
SQL> select * from tel where name='Banu';

NAME TELNO CITY

---------- ---------- ---------------
Banu 2345678 Mumbai

SQL> update tel set city='Tirunelveli' where name='Chitra';

1 row updated.

SQL> select * from tel;

NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Chitra 2502387 Tirunelveli
Sankar 2330289 Tvl

SQL> update tel set city='Tirunelveli' where name='Sankar';

1 row updated.

SQL> select * from tel;

NAME TELNO CITY

---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Chitra 2502387 Tirunelveli
Sankar 2330289 Tirunelveli

SQL> delete from tel where name='Chitra';

1 row deleted.

SQL> select * from tel;

NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Sankar 2330289 Tirunelveli

29
RESULT:
Thus the Data Definition Language(DDL), Integrity constraints and Data
Manipulation Language(DML) commands are implemented in ORACLE with
database tables.

30
Ex. No: 6 Practicing DCL, TCL Commands, Views And Operations On Views.

AIM:
To implement DCL(Data Control Language) commands, TCL(Transaction Control
Language commands, Views and Operations on Views in ORACLE.

DESCRIPTION:
DCL Commands:( Data Control Language)
GRANT Command:
This command is used to grant privileges on tables to other users.
REVOKE Command:
This command is used to the privileges on tables from users.
TCL Commands:( Transaction Control Language)
SAVEPOINT Command:
This command is used to save and store the transaction done till a point.
ROLL BACK Command:
This command is used to undo the transaction up to a save point or commit.
COMMIT Command:
This command is used to save and end the transaction.
IMPLEMENTATION OF SQL COMMANDS(TCL &DCL):
SQL>create table departments(dept_no number(10), dept_name varchar(15),
dept_location varchar(15));
Table created.
SQL>insert into departments values(1,’CSE’,TVL’);
1 row created.
SQL> insert into departments values(2,’ECE’,TRICHY’);
1 row created.
SQL> insert into departments values(3,’EEE’,’MADURAI’);
1 row created.
Select * from departments;
DEPT_NO DEPT_NAME DEPT_LOCATION
---------- ---------- ---------------

31
 1 CSE TVL
2 ECE TRICHY
3 EEE `MADURAI
SQL>create table employees(emp_id number(10), emp_name varchar(15),
emp_salary varchar(10));
Table created.
SQL>insert into employees values(101,’kavin’,10000);
1 row created.
SQL>insert into employees values(102,’Arjun’,20000);
1 row created.
SQL>insert into employees values(103,’Varun’,50000);
1 row created.
SQL>select * from employees;
EMP_ID EMP_NAME EMP_SALARY
---------- ---------- ---------------
101 KAVIN 10000
102 ARJUN 20000
103 VARUN 50000

SQL> Grant all on employees to sys;

Grant succeeded.
SQL> Grant select , update , insert on departments to sys with

grant option;

Grant succeeded.

SQL> Revoke all on employees from sys;

Revoke succeeded.
SQL> Revoke select , update , insert on departments from sys;
Revoke succeeded.

SQL> create table Tel(name varchar2(10),telno number(7), city varchar2(15));

Table created.
SQL> desc tel;
Name Null? Type

32
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
TELNO NUMBER(7)
CITY VARCHAR2(15)

SQL> insert into tel values('Aarthi',2502157,'Chennai');

1 row created.
SQL> insert into tel values('Banu',2345678,'Mumbai');
1 row created.
SQL> insert into tel values('Sankar',2330289,'Tirunelveli');
1 row created.

SQL> select * from tel;

NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Sankar 2330289 Tirunelveli

SQL>commit;
Commit complete.

SQL> insert into tel values(‘Uma’,2345678,’Tirunelveli’);

1 row created

SQL> select * from tel;

NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Sankar 2330289 Tirunelveli
Uma 2345678 Tirunelveli

SQL> roll back;

Roll back complete

SQL> select * from tel;

NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Sankar 2330289 Tirunelveli

SQL> save point s1;

33
SQL>delete from tel where name=’Banu’;
1 row deleted

SQL> select * from tel;

NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Sankar 2330289 Tirunelveli
SQL> roll back to s1;

SQL> select * from tel;

NAME TELNO CITY
---------- ---------- ---------------
Aarthi 2502157 Chennai
Banu 2345678 Mumbai
Sankar 2330289 Tirunelveli

DESCRIPTION:
Views
 A database view is a logical or virtual table based on a query. It is useful to
think of a view as a stored query. Views are queried just like tables.
 A DBA or view owner can drop a view with the DROP VIEW command.

TYPES OF VIEWS
• Updatable views – Allow data manipulation
• Read only views – Do not allow data manipulation

create table employee5(name varchar(20),address varchar(20), phoneno

number(20),salary number(20),gender varchar(6), deptno number(5), deptlocation
varchar(20));
Table created
insert into employee5 values('xxx','120 North
street',9562506020,20000,'f',1,'houseton');
1 row inserted
insert into employee5 values('yyy','12 West street',9555628592,25000,'m',1,'stanford');
1 row inserted
insert into employee5 values('zzz','1 south street',9565689520,15000,'f',1,'bellarel');
1 row inserted

34
insert into employee5 values('aaa','12 East street',9562505142,22000,'f',2,'houseton');
1 row inserted
create view view5 as select name, address from employee5;
view created
select * from employee5;

NAME ADDRESS PHONENO SALARY GENDER DEPTNO DEPTLOCATION

---------- ------------- ------------- ------------ ----------- ----------- ---------------------
xxx 120 North street 9562506020 20000 f 1 houseton

yyy 12 West street 9555628592 25000 m 1 stanford

zzz 1 south street 9565689520 15000 f 1 bellarel

aaa 12 East street 9562505142 22000 f 2 houseton

select * from view5;

NAME ADDRESS
-------------------- --------------------
xxx 120 North street
yyy 12 West street
zzz 1 south street
aaa 12 East street
create view view6 as select * from employee5 where gender='f';
view created
select * from view6;
NAME ADDRESS PHONENO SALARY GENDER DEPTNO DEPTLOCATION
---------- ------------- ------------- ------------ ----------- ----------- ---------------------

xxx 120 North street 9562506020 20000 f 1 houseton

35
zzz 1 south street 9565689520 15000 f 1 bellarel

aaa 12 East street 9562505142 22000 f 2 houseton

create view view7 as select deptno,count(*) as no_of_employees,sum (salary) as
total_salary from employee5 group by deptno;
view created
select * from view7;
DEPTNO NO_OF_EMPLOYEES TOTAL_SALARY
---------- --------------- --------------- ------------------
1 3 60000
2 1 22000
insert into view5 values('bbb','123 middle street');
1 row inserted
select * from employee5;

NAME ADDRESS PHONENO SALARY GENDER DEPTNO DEPTLOCATION

---------- ------------- ------------- ------------ ----------- ----------- ---------------------
xxx 120 North street 9562506020 20000 f 1 houseton

yyy 12 West street 9555628592 25000 m 1 stanford

zzz 1 south street 9565689520 15000 f 1 bellarel

aaa 12 East street 9562505142 22000 f 2 houseton
bbb 123 middle street
insert into employee5 values('ccc','113 south
street',9869598695,25000,'m',3,'Berkeley');
1 row inserted
select * from view5;

NAME ADDRESS

36
-------------------- --------------------
xxx 120 North street
yyy 12 West street
zzz 1 south street
aaa 12 East street
bbb 123 middle street
ccc 113 south street

6 rows selected.
create view v2 as select name,deptno from employee5 where gender='f';
view created
select * from v2;
NAME DEPTNO
-------------------- ----------
xxx 1
zzz 1
aaa 2
update v2 set deptno=5 where name='aaa';
1 row updated
select * from v2;
NAME DEPTNO
-------------------- ----------
xxx 1
zzz 1
aaa 5
drop view view7;
view dropped
create view view8 as select name,address from employee5 where deptno=1 with
check option;
view created
select * from view8;

37
NAME ADDRESS
-------------------- --------------------
xxx 120 North street
yyy 12 West street
zzz 1 south street

RESULT:
Thus, the Transaction Control Language (TCL), Data Control Language(DCL)
commands, Views and Operations on views are implemented in ORACLE with
database tables.

38
 Ex. No: 7 Practicing Queries Using ANY, ALL, IN, EXISTS, NOT EXISTS,
UNION And INTERSECT etc.

AIM:
To relate tables using the set operators in Oracle.
DESCRIPTION:
Create tables with necessary fields and records.
union operator – Returns all distinct rows selected by either query.
union all operator – Returns all rows selected by either query including duplicates.
intersect operator – Returns only rows that are common to both the queries.
minus operator – Returns all distinct rows selected only by the first query and not by
the second.

IMPLEMENTATION:
SQL> create table details(Roll number(10),Name varchar(25),City varchar(25));
Table created.

SQL> insert into details values (101,'Asha','CBE');

1 row created.

SQL> insert into details values (102,'Banu','TVL');

1 row created.

SQL> insert into details values (103,'Chithra','Delhi');

1 row created.

SQL> select * from details;

ROLL NAME CITY

---------- ---------- ----------
101 Asha CBE
102 Banu TVL
103 Chitra Delhi

39
SQL> create table marks(Roll number(10),Mark1 number(10),Mark2 number(10));

Table created.

SQL> insert into marks values(101,90,30);

1 row created.

SQL> insert into marks values(102,78,90);

1 row created.

SQL> insert into marks values(103,56,46);

1 row created.

SQL> insert into marks values(104,20,20);

1 row created.

SQL> select * from marks;

ROLL MARK1 MARK2

---------- ---------- ----------
101 90 30
102 78 90
103 56 46
104 20 20

SQL> select roll from details union select roll from marks;

ROLL
----------
101
102
103
104

SQL> select roll from details intersect select roll from marks;

ROLL
----------
101
102

40
 103

SQL> select roll from details union all select roll from marks;

ROLL
----------
101
102
103
101
102
103
104

7 rows selected.

SQL> select roll from details minus select roll from marks;

no rows selected

SQL> select roll from marks minus select roll from details;

ROLL
----------
104

SQL> select Name from details where Roll = ANY(select Roll from marks where
Mark2=90);

NAME
-------------------------
Banu

SQL> select all City from details;

CITY
-------------------------
CBE
TVL
Delhi

SQL> select all City from details where City='TVL';

CITY
-------------------------
TVL

41
SQL> select all Name from details where City='TVL';

NAME
-------------------------
Banu

SQL> select all Roll from details where City='TVL';

ROLL
----------
102

SQL> insert into details values(104,'Priya','TVL');

1 row created.

SQL> insert into details values(105,'Ramya','TVL');

1 row created.

SQL> select * from details;

ROLL NAME CITY

---------- ------------------------- -------------------------
101 Asha CBE
102 Banu TVL
103 Chitra Delhi
104 Priya TVL
105 Ramya TVL

SQL> select all Roll from details where City='TVL';

ROLL
----------
102
104
105

SQL> select all Name from details where City='TVL';

NAME
-------------------------
Banu
Priya
Ramya

42
SQL> select distinct Roll from details where Roll not in(select Roll from marks);

ROLL
----------
105

SQL> select distinct Roll from details where Roll in(select Roll from marks);

ROLL
----------
102
101
104
103

RESULT:
Thus, the set operators have been executed successfully.

43
Ex. No: 8 Practicing Sub Queries (Nested, Correlated) And Joins.

AIM:
To write SQL database queries for Nested queries and Joins.

NESTED QUERIES:

ACCOUNT TABLE:
SQL> create table account(name varchar(25), accountno number(10), balance
number(10));
Table created.

SQL> insert into account values('ragav',1010,1000);

1 row created.

SQL> insert into account values('santha', 2020,2000);

1 row created.

SQL> insert into account values('kishore', 3030, 3000);

1 row created.

SQL> insert into account values('ramu', 4040, 4000);

1 row created.

SQL> insert into account values('radha', 5040, 5000);

1 row created.

SQL> insert into account values('santhia', 6040, 6000);

1 row created.

SQL>select * from account;

NAME ACCOUNTNO BALANCE

44
Ragav 1010 1000

Santha 2020 2000

Kishor 3030 3000

Ramu 4040 4000

Ratha 5050 5000

Santhia 6060 6000

6 rows selected

SQL> select name from account where balance=(select min(balance) from

account);

NAME

Ragav

SQL> select name from account where balance=(select max(balance)from

account);

NAME

Santhia

SQL> select name from account where balance=(select min(balance) from

account where balance>(select min(balance) from account));

NAME

Santhia

BORROWER TABLE

SQL> create table borrow(name varchar(25),loanno number(10));

Table created.

SQL> insert into borrow values('krishna', 1111);

1 row created.

SQL> insert into borrow values('parthiban', 2222);

45
 1 row created.

SQL> insert into borrow values('sathish', 3333);

1 row created.

SQL> insert into borrow values('vijayaraja', 4444);

1 row created.

SQL> insert into borrow values('abdul', 5555);

1 row created.

SQL> insert into borrow values('karthickraja', 6666);

1 row created.

SQL> select * from borrow;

NAME LOANNO

Krishna 1111

Parthiban 2222

Sathish 3333

Vijayaraja 4444

Abdul 5555

Karthickraja 6666

DEPOSITER TABLE

SQL> create table deposit (name varchar2(20), accountno number(5));

Table created.

SQL> insert into deposit values('parthiban', 1100);

1 row created.

SQL> insert into deposit values('krishna', 6600);

1 row created.

46
SQL> insert into deposit values('vijayaraja', 1108);

1 row created.

SQL> insert into deposit values('pooja', 6608);

1 row created.

SQL> insert into deposit values('raja', 7708);

1 row created.

SQL> insert into deposit values('suresh', 8808);

1 row created.

SQL> select * from deposit;

NAME ACCOUNTNO

Parthiban 1100

Krishna 6600

Vijayaraja 1108

Pooja 6608

Raja 7708

Suresh 8808
6 rows selected

SQL> select distinct name from deposit where name not in (select name from borrow);

NAME
--------------------
Raja

Suresh

pooja

SQL> select distinct name from borrow where name in (select name from
deposit);

47
 NAME
--------------------
Parthiban

Krishna

vijayaraja

JOIN QUERIES
DESCRIPTION:
A Join is actually performed by a where clause which combines the specified
rows of a table.
Types of Joins:
 Simple Join
 Self Join
 Outer Join
Create two tables with necessary fields.
Simple Join:
It is the most common type of join which retrieves the rows from the two tables
having a common column.

Equi join:
To join (concatenate) two tables using Equi Join – Join using Equality
Operator.
Non equi-join:
To join two tables using Non-Equi Join – Join Using Inequality Operators like
<, <=,>,>=,!=.
It specifies relationship between columns belonging to different tables by making use
of relational operators.
Self Join:
To join a table to itself using Self join – Joining a table to itself by creating
aliases.

48
It can compare each row of the table to itself and also with the rows of the same table.
Outer Join:
To join two tables using Outer Join – Equi or Non Equi join with non-matching
records. (+) – denotes the table from which non matching records are to be retrieved.
It extends the result of simple join. It returns all rows returned by a simple join
as well as those rows from any table that do not match any row from other table.

IMPLEMENTATION:
SQL> create table details(Roll number(10),Name varchar(25),City varchar(25));
Table created.

SQL> insert into details values (101,'Asha','CBE');

1 row created.

SQL> insert into details values (102,'Banu','TVL');

1 row created.

SQL> insert into details values (103,'Chithra','Delhi');

1 row created.

SQL> select * from details;

ROLL NAME CITY

---------- ---------- ----------
101 Asha CBE
102 Banu TVL
103 Chitra Delhi

SQL> create table marks(Roll number(10),Mark1 number(10),Mark2 number(10));

Table created.

SQL> insert into marks values(101,90,30);

1 row created.

SQL> insert into marks values(102,78,90);

49
1 row created.

SQL> insert into marks values(103,56,46);

1 row created.

SQL> insert into marks values(104,20,20);

1 row created.

SQL> select * from marks;

ROLL MARK1 MARK2

---------- ---------- ----------
101 90 30
102 78 90
103 56 46
104 20 20

SQL> select * from details s,marks u where s.roll=u.roll;

ROLL NAME CITY ROLL MARK1 MARK2
---------- ---------- ---------- ---------- ---------- ----------
101 Asha CBE 101 90 30
102 Banu TVL 102 78 90
103 Chitra Delhi 103 56 46

SQL> select * from details s,marks u where s.roll<u.roll;

ROLL NAME CITY ROLL MARK1 MARK2
---------- ---------- ---------- ---------- ---------- ----------
101 Asha CBE 102 78 90
101 Asha CBE 103 56 46
102 Banu TVL 103 56 46
101 Asha CBE 104 20 20
102 Banu TVL 104 20 20
103 Chitra Delhi 104 20 20
6 rows selected.

50
SQL> select * from details s,marks u where s.roll=u.roll(+);
ROLL NAME CITY ROLL MARK1 MARK2
---------- ---------- ---------- ---------- ---------- ----------
101 Asha CBE 101 90 30
102 Banu TVL 102 78 90
103 Chitra Delhi 103 56 46

SQL> select * from details s,marks u where s.roll(+)=u.roll;

ROLL NAME CITY ROLL MARK1 MARK2
---------- ---------- ---------- ---------- ---------- -----------------------------
101 Asha CBE 101 90 30
102 Banu TVL 102 78 90
103 Chitra Delhi 103 56 46
104 20 20
SQL> select * from marks u,marks s where s.roll=u.mark1;
no rows selected

SQL> select * from marks u,marks s where s.mark1=u.mark2;

ROLL MARK1 MARK2 ROLL MARK1 MARK2
--------------- ---------- ---------- ---------- ---------- ---------------------
104 20 20 104 20 20
102 78 90 101 90 30

RESULT:

Thus, the Nested queries and Joins have been executed successfully.

51
 Ex. No: 9 Practice Queries Using COUNT, SUM, AVG, MAX, MIN, GROUP
BY, HAVING, VIEWS Creation And Dropping.

AIM:
To create table using Aggregate Functions in ORACLE.

AGGREGATE FUNCTIONS:
An aggregate function summarizes the results of an expression over a number
of rows, returning a single value.

Some of the commonly used aggregate functions are:

•
SUM
•
COUNT
•
AVG
•
MIN
•
MAX

IMPLEMENTATION:

SQL> create table employee(Emp_id number, Name varchar2(50), Dept_name

varchar2(50), Salary number);

Table created.
SQL> insert into employee values(100,'Priya','CSE',50000);

1 row created.

SQL> insert into employee values(101,'Nirmisha','IT',60000);

1 row created.

SQL> insert into employee values(102,'Madhu','ECE',50000);

1 row created.

52
SQL> insert into employee values(103,'Anu','CSBS',70000);

1 row created.

SQL> insert into employee values(104,'Banu','IT',75000);

1 row created.

SQL> select * from employee;

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

EMP_ID NAME DEPT_NAME SALARY

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

100 Priya CSE 50000

101 Nirmisha IT 60000
102 Madhu ECE 50000
103 Anu CSBS 70000
104 Banu IT 75000

SQL> select sum(Salary) from employee;

SUM(SALARY)
-----------
305000

SQL> select sum(Salary) from employee group by Dept_name;

SUM(SALARY)
-----------
70000
135000
50000
50000

SQL> select Dept_name,sum(Salary) from employee group by Dept_name;

DEPT_NAME SUM(SALARY)
-------------------------------------------------- -----------
CSBS 70000
IT 135000

53
CSE 50000
ECE 50000

SQL> select Dept_name, sum(Salary) from employee group by Dept_name having

Dept_name='CSBS';

DEPT_NAME SUM(SALARY)
-------------------------------------------------- -----------
CSBS 70000

SQL> select avg(Salary) from employee;

AVG(SALARY)
-----------
61000

SQL> select count(*) from employee;

COUNT(*)
----------
5

SQL> select count(Salary) from employee;

COUNT(SALARY)
-------------
5

SQL> select min(Salary) from employee;

MIN(SALARY)
-----------
50000

SQL> select max(Salary) from employee;

MAX(SALARY)
-----------
75000

54
Views
 A database view is a logical or virtual table based on a query. It is useful to
think of a view as a stored query. Views are queried just like tables.
 A DBA or view owner can drop a view with the DROP VIEW command.

TYPES OF VIEWS
• Updatable views – Allow data manipulation
• Read only views – Do not allow data manipulation

create table employee5(name varchar(20),address varchar(20),phoneno

number(20),salary number(20),gender varchar(6),deptno number(5),deptlocation
varchar(20));
Table created
insert into employee5 values('xxx','120 North
street',9562506020,20000,'f',1,'houseton');
1 row inserted
insert into employee5 values('yyy','12 West street',9555628592,25000,'m',1,'stanford');
1 row inserted
insert into employee5 values('zzz','1 south street',9565689520,15000,'f',1,'bellarel');
1 row inserted
insert into employee5 values('aaa','12 East street',9562505142,22000,'f',2,'houseton');
1 row inserted
create view view5 as select name, address from employee5;
view created
select * from employee5;

NAME ADDRESS PHONENO SALARY GENDER DEPTNO DEPTLOCATION

---------- ------------- ------------- ------------ ----------- ----------- ---------------------
xxx 120 North street 9562506020 20000 f 1 houseton

yyy 12 West street 9555628592 25000 m 1 stanford

55
zzz 1 south street 9565689520 15000 f 1 bellarel

aaa 12 East street 9562505142 22000 f 2 houseton

select * from view5;

NAME ADDRESS
-------------------- --------------------
xxx 120 North street
yyy 12 West street
zzz 1 south street
aaa 12 East street
create view view6 as select * from employee5 where gender='f';
view created
select * from view6;
NAME ADDRESS PHONENO SALARY GENDER DEPTNO DEPTLOCATION
---------- ------------- ------------- ------------ ----------- ----------- ---------------------

xxx 120 North street 9562506020 20000 f 1 houseton

zzz 1 south street 9565689520 15000 f 1 bellarel

aaa 12 East street 9562505142 22000 f 2 houseton

create view view7 as select deptno,count(*) as no_of_employees,sum (salary) as
total_salary from employee5 group by deptno;
view created
select * from view7;
DEPTNO NO_OF_EMPLOYEES TOTAL_SALARY
---------- --------------- --------------- ------------------
1 3 60000
2 1 22000

56
insert into view5 values('bbb','123 middle street');
1 row inserted
select * from employee5;

NAME ADDRESS PHONENO SALARY GENDER DEPTNO DEPTLOCATION

---------- ------------- ------------- ------------ ----------- ----------- ---------------------
xxx 120 North street 9562506020 20000 f 1 houseton

yyy 12 West street 9555628592 25000 m 1 stanford

zzz 1 south street 9565689520 15000 f 1 bellarel

aaa 12 East street 9562505142 22000 f 2 houseton
bbb 123 middle street
insert into employee5 values('ccc','113 south
street',9869598695,25000,'m',3,'Berkeley');
1 row inserted
select * from view5;

NAME ADDRESS
-------------------- --------------------
xxx 120 North street
yyy 12 West street
zzz 1 south street
aaa 12 East street
bbb 123 middle street
ccc 113 south street

6 rows selected.
create view v2 as select name,deptno from employee5 where gender='f';
view created
select * from v2;

57
NAME DEPTNO
-------------------- ----------
xxx 1
zzz 1
aaa 2
update v2 set deptno=5 where name='aaa';
1 row updated
select * from v2;
NAME DEPTNO
-------------------- ----------
xxx 1
zzz 1
aaa 5
drop view view7;
view dropped
create view view8 as select name,address from employee5 where deptno=1 with
check option;
view created
select * from view8;
NAME ADDRESS
-------------------- --------------------
xxx 120 North street
yyy 12 West street
zzz 1 south street

RESULT:
Thus, the aggregate functions have been executed successfully.

58
 Ex. No: 10 Practicing On Triggers – Creation Of Trigger, Insertion Using
Trigger, Deletion Using Trigger, Updating Using Trigger.

AIM:
To create database triggers for various operations.
TRIGGER:
A Trigger is a stored procedure that defines an action that the database
automatically take when some database-related event such as Insert, Update or Delete
occur.
TYPES OF TRIGGERS:
The various types of triggers are as follows,
 Before: It fires the trigger before executing the trigger statement.
 After: It fires the trigger after executing the trigger statement.
 For each row: It specifies that the trigger fires once per row.
 For each statement: This is the default trigger that is invoked. It specifies that
the trigger fires once per statement.
VARIABLES USED IN TRIGGERS
 :new
 :old
These two variables retain the new and old values of the column updated in the
database.
The values in these variables can be used in the database triggers for data
manipulation
Row Level Trigger vs. Statement Level Trigger:
Row Level Trigger Statement Level Trigger
These are fired for each row affected by These are fired once for the statement
the DML statement. instead of the no of rows modified by it.

These are used for generating/checking These are used for generated the
the values begin inserted or updated. summary information.

59
Before trigger vs. after trigger
Before Triggers After Triggers

Before triggers are fired before the After triggers are fired after the
DML statement is actually executed. DML statement has finished execution.

Syntax:
Create or replace trigger <trg_name> Before /After Insert/Update/Delete
[of column_name, column_name….]
on <table_name>
[for each row]
[when condition]
begin
---statement
end;
Q1: Create a trigger that insert current user into a username column of an
existing table
Procedure for doing the experiment:
Step no. Details of the step

1 Create a table itstudent4 with name and username as arguments

2 Create a trigger for each row that insert the current user as user
name into a table

create table itstudent4(name varchar2(15),username varchar2(15));

create or replace trigger itstudent4 before insert on itstudent4 for each row
declare
name varchar2(20);

60
 begin
select user into name from dual;
:new.username:=name;
end;
Trigger Created
insert into itstudent4 values('akbar','ranjani');
insert into itstudent4 values('suji','priya');
select * from itstudent4;
Q2: Create a Simple Trigger that does not allow Insert Update and Delete
Operations on the Table
create table emp1(ename varchar(10),eid number(10),salary number(10));
insert into emp1 values('xxx',1,10000);
insert into emp1 values('yyy',2,20000);
create trigger ittrigg before insert or update or delete on emp1 for each row
begin
raise_application_error(-20010,'You cannot do manipulation');
end;
Trigger Created
insert into emp1 values('aaa',14,34000);
You cannot do manipulation
delete from emp1 where ename='xxx';
You cannot do manipulation
Q3: Create a Trigger that raises an User Defined Error Message and does not
allow
updating and Insertion
create trigger ittriggs before insert or update of salary on emp1 for each row
declare
triggsal emp1.salary%type;
begin
select salary into triggsal from emp1 where eid=1;
if(:new.salary>triggsal or :new.salary<triggsal) then

61
 raise_application_error(-20100,'Salary has not been changed');
end if;
end;
Trigger Created
insert into emp1 values ('bbb',16,45000);
'Salary has not been changed'
update emp1 set eid=1 where ename='yyy';
You cannot do manipulation
drop trigger ittrigg;

RESULT:
Thus, the PL/SQL Triggers have been executed successfully.

62
 Ex. No: 11 Procedures – Creation Of Stored Procedures, Execution Of
Procedure And Modification Of Procedure.

AIM:
To write a PL/SQL Procedure for various applications
DESCRIPTION:
Syntax: create or replace procedure <procedure_name> (parameters defn) is
<declaration/initialisation >statements;
begin
<conditional control statements>;
<Iteration Statements>;
end;
1. Write a procedure to find out the minimum of the given two numbers.
ALGORITHM:
Main Program:
Step 1: Begin
Step 2: Declare the local variable.
Step 3: Get the Input.
Step 4: Check the conditional statements.
Step 5: End
Procedure:
Step 1: Start
Step 2: Declare the local variable.
Step 3: Perform the process using the SQL functions and constructs.
Step 4: Print the result
Step 5: Return.
Main Program:
create procedure findmin(x in number,y in number,z out number)
is
begin
if x>y then

63
z:=x;
else
z:=y;
end if;
end;
Procedure created
declare
a number;
b number;
c number;
begin
a:=23;
b:=40;
findmin(a,b,c);
dbms_output.put_line('Minimum of(23,40):'||c);
end;
Output:
Minimum of(23,40):40
Statement processed
2. Write a procedure to find out the square of a number.
ALGORITHM:
Main Program:
Step 1: Begin
Step 2: Declare the local variable.
Step 3: Get the Input.
Step 4: Call the square root function.
Step 5: End
Procedure:
Step 1: Start
Step 2: Declare the local variable.
Step 3: Perform the process using the SQL functions and constructs.

64
Step 4: Print the result
Step 5: Return.
Main Program:
create procedure squarenumber(x in out number)
is
begin
x:=x*x;
end;
Procedure created
declare
a number;
begin
a:=23;
squarenumber(a);
dbms_output.put_line('Square of 23:'||a);
end;
Output:
Square of 23:529
Statement processed
3. Write a procedure to print n numbers.
ALGORITHM:
Step 1: Start
Step 2: Declare the local variable.
Step 3: Perform the process using the SQL functions and constructs.
Step 4: Print the result
Step 5: Return.
Procedure:
declare
i number(1);
begin
for i in 1..3

65
loop
dbms_output.put_line(i);
end loop;
end;
Output:
1
2
3
Statement processed.

RESULT:
Thus, the PL/SQL Procedure for various applications have been executed
successfully.

66
Ex. No: 12 Cursors- Declaring and Opening Cursor, Fetching the data,
closing the cursor.

AIM:
To write and execute PL/SQL program using cursor.

CURSOR:
Cursor can be used to retrieve the set of rows returned to by the query.

SYNTAX:
Declaration
Cursor <cursorname> is <select statement>
PROCEDURE:

1. Start.
2. Initialize the cursor procedure based on the table attributes which the actual
operation
has to be carried out.
3. Develop the procedure with the essential operational parameters.
4. Specify the individual operation to be each attributes.
5. Execute the cursor procedure.
6. Stop.

PROGRAM:
Set serveroutput on
declare
s_no number;
avgiat number;
cursor cur select(s_no,iat1+iat2+iat3/3) from stud;
begin
dbms_output.put_line(‘s_no’,||’avgiat’);

67
dbms_output.put_line(‘------------------’);
open cur;
loop
fetch cur into s_no,s_iat;
exit when cur % notfound;
dbms_output.put_line(rpad(s_no,10,’’)||avgiat);
end loop;
close cur;
end;

OUTPUT
SQL*Plus: Release 8.0.3.0.0 - Production on Sat Mar 19 13:42:11 2012
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Personal Oracle8 Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
SQL> create table stud(r_no number,name varchar2(30),iat1 number,iat2 number,iat3
number);
Table created.
SQL> insert into stud values(1,'sankar',60,70,65);
1 row created.
SQL> insert into stud values(2,'sabsac',70,80,65);
1 row created.
SQL> insert into stud values(3,'suthan',95,95,99);
1 row created.
SQL> select * from stud;
R_NO NAME IAT1 IAT2 IAT3
-------- ---------- ----------- ----------- ------------
1 sankar 60 70 65

68
2 sabsac 70 80 65
3 suthan 95 95 99

SQL> Set serveroutput on

SQL> declare
2 s_no number;
3 avgiat number;
4 cursor cur select(s_no,iat1+iat2+iat3/3) from stud;
5 begin
6 dbms_output.put_line('s_no',||'avgiat');
7 dbms_output.put_line('------------------');
8 open cur;
9 loop
10 fetch cur into s_no,s_iat;
11 exit when cur % notfound;
12 dbms_output.put_line(rpad(s_no,10,'')||avgiat);
13 end loop;
14 close cur;
15 end;
16 /
R_NO AVGIAT
---------- ----------------
1 65
2 71.66
3 96.33
PL/SQL procedure successfully completed.
SQL> create table table1(r_no number,name varchar2(30),wek1 number,wek2
number,wek3 number);
Table created.
SQL> insert into table1 values(1,'ram',78,95,64);
1 row created.

69
SQL> insert into table1 values(2,'mani',75,89,62);
1 row created.
SQL> insert into table1 values(3,'suresh',47,52,34);
1 row created.

SQL> select * from stud;

R_NO NAME WEK1 WEK2 WEK3
--------- --------------------- ------------ --------- ---------
1 ram 78 95 64
2 mani 75 89 62
3 suresh 47 52 34

SQL> Set serveroutput on

SQL> declare
2 s_no number;
3 avgwek number;
4 cursor cur select(s_no, wek1+wek2+wek3/3) from table1;
5 begin
6 dbms_output.put_line('s_no',||'avgwek');
7 dbms_output.put_line('------------------');
8 open cur;
9 loop
10 fetch cur into s_no,s_wek;
11 exit when cur % notfound;
12 dbms_output.put_line(rpad(s_no,10,'')||avgwek);
13 end loop;
14 close cur;
15 end;
16 /

70
R_NO AVGWEK
---------- --------------
1 79
2 75.33
3 44.33
PL/SQL procedure successfully completed.
SQL> create table table(r_no number,name varchar2(30),int1 number,int2
number,int3 number);
Table created.
SQL> insert into tablevalues(1,'iyappa',80,47,52);
1 row created.
SQL> insert into table values(2,'sabari',74,50,60);
1 row created.
SQL> insert into table values(3,'nathan',99,99,99);
1 row created.
SQL> select * from stud;
R_NO NAME INT1 INT2 INT3
--------- --------------------- ------------ --------- ---------
1 iyappa 80 47 52
2 sabari 74 50 60
3 nathan 99 99 99

SQL> Set serveroutput on

SQL> declare
2 s_no number;
3 avgint number;
4 cursor cur select(s_no,int1+int2+int3/3) from table;
5 begin
6 dbms_output.put_line('s_no',||'avgint');
7 dbms_output.put_line('------------------');

71
8 open cur;
9 loop
10 fetch cur into s_no,s_int;
11 exit when cur % notfound;
12 dbms_output.put_line(rpad(s_no,10,'')||avgint);
13 end loop;
14 close cur;
15 end;
16 /
R_NO AVGINT
---------- --------------
1 59.66
2 61.33
3 99
PL/SQL procedure successfully completed.

RESULT:
Thus the PL/SQL program using cursor was executed successfully and the
output is verified.

72
73