BCOM 4 SEMESTER DBMS WITH ORACLE

BCOM – 4 SEMESTER

PAPER : DATA BASE MANAGEMENT SYSTEM WITH ORACLE

Unit 1:over view of database systems: Introduction to data, information, File

based system, Drawbacks of file based system, database, database
management systems, Objectives of DBMS, classification of Data Base
Management Systems, Services of Database System.

Unit 2: Relational Model: Advantages of DBMS, components of database

system, Database users, Introduction to relational model, Codd's rules,
concept of keys, constraints (Domain, Entity, Referential)

Unit 3: Entity Relationship Model Introduction, The Building Blocks of an

Entity-Relationship, Classification of Entity Set, Attribute Classification,
Relationship Degree, Relationship Classification

Unit 4: BASIC SQL: SQL data types, SQL literals, operators, DDL operations
(create, alter, drop), DML operations (insert, delete, update), queries,
aggregate functions, TCL operations: commit, Rollback, Savepoint , DCL
operations: Grant, Revoke

Unit 5: PL/SQL: Introduction, Structure of PUSQL program, Steps to Create a

PL/SQL program, Data types of PL/SQL, PL/SQL operators, Control
Structures: conditional control statements (if, if..else), Iterative Control
statements (while, do..while, for) .

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

UNIT – I

1.INTRODUCTION TO DATA, INFORMATION:

Q: Write about data and Information. (Data Vs Information)

Data:

1. Data is the raw set of facts such as – customer name, phone number, date
of birth, or price of a book, etc.
2. There are various kinds of data that can be stored and processed in a
computer –
 numbers, characters,
 text,
 images,
 audio and
 video.
3. The data is stored in the computer in binary form (1s or 0s), which
can be processed and stored digitally.
4. The smallest piece of data that can be recognized by the
computer is a single character. A single character requires one Byte of
memory space.
5. Data can be generated by:
 Humans
 Machines
 Human-Machine combines.

Data processing:

The data processing involves following stages:

1. Data Acquisition: This stage includes the methods used to collect raw
data from various sources.
2. Data Preparation: This stage involves tasks like identifying and handling
missing values, correcting inconsistencies, formatting data into a consistent
structure.
3. Data Input: The pre-processed data is loaded into a system suitable for
further processing and analysis.
4. Data Processing: The data undergoes various manipulations and
transformations to extract valuable information.
5. Data Output: The transformed data(output) is then analyzed using various
techniques to generate insights and knowledge. This could involve
statistical analysis or visualization techniques.
6. Data Storage: The processed data and the generated outputs are stored in
a secure and accessible format for future use.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

The data processing cycle is iterative, meaning the output from one stage can
become the input for another.
Data processing commonly occurs in stages, and therefore the “processed data”
from one stage could also be considered as the “raw data” of subsequent stages.

Information:

1. Data is the base component to create the information.

Information is produced by processing data. Meaningful and
useful output is known as Information.

Data Information
PROCESSING
(Input) (Output)

2. Timely and useful information requires accurate data. Data must be

generated properly and it must be stored in a format, that is easy to
access and process.

3. Information gives knowledge, understanding and insights that can be

used for decision-making , problem-solving, communication and various
other purposes.

4. Information is the backbone of any organization. Information helps

Managers and organization to gain knowledge and take decisions.

5. Examples of information:

 Reports: a business financial report -It contains information like sales,

expenses, and profits of a company.
 Visualizations: charts and graphs - they show trends and patterns.

2.Basic File Terminology:

Q: Write about Basic File Terminology.

RAO’S DEGREE COLLEGE

 BCOM 4 SEMESTER DBMS WITH ORACLE

FILE
1.Data:

Data is the raw set of facts such as –

customer name, phone number, date of
birth, or price of a book. RECORD

The smallest piece of data that can be

recognized by the computer is a single
character. A single character requires one FIELD
Byte of memory space.

DATA

2.Field:

A field is used to define and store data. It contains a group of characters

that has a specific meaning.

3.Record:

Record is a logically connected set of one or more fields that describes a

thing, a person or a place.

4.File:

A File is a collection of related records. For example, a STUDENT file may

contain data about students in a college.

Example:

In File Processing System (FPS) the Records and the Fields in a STUDENT data file

NAME COURSE CITY

RAJU BSC NELLORE

RAVI BCOM CHENNAI

SINGH BSC GUDUR

KIRAN BCOM NELLORE

RAGHU BSC KAVALI

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Q: Write about the Historical roots of files and file systems.( or )

Write about Traditional File Processing System (FPS) ( or )

Explain the features of File Based system.

3.File Based system :

The manual file system was used to maintain the records and files,
before the development of computers. It is a time taking process for
retrieving required information. With the development of computers the
File based system was used to create data files.
The traditional File Processing Systems(FPS) were developed using Third
Generation Languages(3GL) like – COBOL, PASCAL, BASIC, FORTRAN,
etc..
In File-Based System, we have a collection of application programs that
performs a particular task for the users. Each program defines and
manages its own data.
Characteristics of File-Based System:
 It stores data of an organization in group of files.
 Each file is independent from one another.
 Each file must have its own application program.
 The application programs are used to perform
operations on data files. Operations include – data
storage, display, modify, etc.
The computer based system reads data, process the data according to
the requirements, and produce necessary reports.
The data processing specialist creates necessary File structures,
Application programs and produces reports based on the data.
Example: The following example illustrates the File Processing System.

A CUSTOMER data file for a small Insurance company.

Policy No customer name CITY Agent name Policy type Amount

80605A RAMESH NELLORE MURALI T5 200000

80609B RAJESH CHENNAI SINGH T2 500000

80620C RAGHU NELLORE MURALI T4 250000

80625A RAKESH GUDUR MURALI T1 350000

80640D RAMU KAVALI SINGH T5 400000

Using the CUSTOMER data file, the data processing specialist developed
programs that produced very useful reports.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Some of the reports produced by the File processing system are given below –

 Monthly report of policies sold by each agent.

 Monthly report of Customers to be contacted for renewal.
The Insurance company needs additional set of programs to produce new reports.
They created SALES file, which helped to observe daily sales report. Another file
AGENT is created to maintain information about agents.
The file system of Insurance company –

FILE MANAGEMENT

PROGRAMS

CUSTOMER SALES FILE

FILE

FILE REPORT

PROGRAMS

FILE MANAGEMENT

PROGRAMS

AGENT FILE

FILE REPORT

PROGRAMS

As the number of files increases, a small file system is developed for the

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Insurance Company. Each data file in the file system uses its own
programs to store and modify the data. In the File system, a large number
of data files are needed to perform various tasks.

4.Drawbacks of File-Based System:

Q: Explain the problems with File system data management. ( or )

Write the disadvantages of File processing system.

The following are the disadvantages of File processing system –

1. More programming
2. File management
3. Modifications
4. Security features
5. Structural and Data dependency
6. Data sharing
7. Data Redundancy
8. Data Inconsistency
9. Data Anomalies
1.More programming :
The traditional File Processing Systems (FPS) were developed using Third
Generation Languages (3GL) like – COBOL, PASCAL, BASIC, FORTRAN, etc...
Each file in the file system uses its own programs to store and modify data. That is,
every data file has its own file management programs and file report programs.
2.File Management:
As the number of files increases the administration becomes very difficult. The file
management programs and file report programs on every data file requires a
execution procedure, which is very difficult.
3.Modifications:
Making changes in a existing file is very difficult. For example, in the Customer file,
the address of the Customer is to be modified. It may require programs to be
executed for opening the file, searching the customer address and finally to modify
the address.
4.Security features:
Security features such as pass word protection, the ability to modify and lock files
are very difficult. For giving access permissions such as – Reading the file, doing
modifications, deletions and inserting data in a data file requires implementing and
working with many programs.

5.Structural and Data dependency:

A file system exhibits structural and data dependency which means the access to a
file is dependent on its structure. For example, adding a new field to the

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

CUSTOMER data file. With this change programs connected to the CUSTOMER
data file will not work.
Therefore all the file system programs must be modified according to the new file
structure. This shows structural dependency.
Every change in the properties of data such as – changing a field from integer to real
number requires changes in all the programs that access the file.
6.Data sharing:
Each application program contains its own data file. For example, the users in
Accounting department, cannot access the data in Sales and Customer departments.
In file system data management, data sharing is not possible.
7.Data Redundancy :
Data Redundancy exists when the same data is stored in different places.
That is,same information is stored in several files. For example, the AGENT phone
no and address are stored in both AGENT and CUSTOMER data files. Data
Redundancy leads to wastage of memory space.
8.Data Inconsistency:
The data inconsistency occurs because of data redundancy. For example, the
AGENT address is stored in two different files. Modifying the address in file will
cause data inconsistency.
9.Data Anomalies:
The data anomalies are commonly defined as follows –
 Insert anomalies – The errors that occur when we insert a record.
 Update anomalies – The errors that occur when we update an existing record.
 Delete anomalies - The errors that occur when we delete an existing record.

Q:5: What is a data base?

(Database:
1. A database is a organized collection of related data.
2. A database is a shared, Integrated computer structure, that stores
a collection of –
o End user data – the raw set of facts of the end user
o Meta data – data about data.
3. Meta data gives complete picture of the data in the data base.
For example, the Meta data stores name of the data element, data
type etc.
4. In a data base data is stored in tables. Table contains Rows and
Columns. A Row contains a record and a column contains field.
5. A database schema is the logical representation of a database, which
shows how the data is stored logically in the entire database.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Example: Schema of EMPLOYEE table(relation) -

EMPLOYEE (EMPNO, ENAME, DEPT,PHNO)

is the relation schema for EMPLOYEE.

6. Example:
Consider the following table –
EMPLOYEE

EMPNO ENAME DEPT PHNO

5000 ARUN SALES 98480223

38
5001 VARUN MARKETING 9848033448

5002 TARUN FINANCE 9848055338

5003 KIRAN SALES 9848066338

5004 SAI MARKETING 9848099338

7. Databases today are used to store objects such as –

documents, images, sound, and video in addition to textual and
numerical data.
Q: 6: Define Meta data.

Meta data:

1. Meta data means – “data about data”.

2. Meta data gives complete picture of the data in the data base. For
example, the Meta data stores name of the data element, data type etc.
3. Meta data describes the data characteristics and set of
relationships that link the data found in the data base.
4. Meta data allows data base designers and users to understand
what data exists and meaning of the data.
5. Example:
Metadata of employee table –

FIELD DATATYPE LENGTH DESCRIPTION

EMPNO NUMBER 5 Employee Number

ENAME TEXT 20 Name of the Employee

DEPT TEXT 15 Name of the

department
PHNO NUMBER 10 Mobile no of employee

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Q: Define Data Base Management System (DBMS).

7.Data Base Management System(DBMS):

1. The Data Base Management System (DBMS) is a collection

of programs that manages the database structure and controls
access to the data stored in the database.
Example:
2. MS-ACCESS is a Relational DBMS (RDBMS) software
developed by Microsoft Corporation.
3. Oracle8, Oracle9i are the popular RDBMS softwares
developed by Oracle Corporation.
4. The Data Base Management System serves as an
intermediary between the user and the database.
5. Relational database management systems use the SQL language to
access the database. SQL (Structured Query Language) is
a programming language used to communicate with data stored in a
relational database management system.
6. The Data Base Management System presents the end user
with a single or integrated view of the data in the database.
7. The DBMS is a general-purpose software. The various applications of DBMS
are – Railway reservation system, library management system, banking etc,

Data
base
END

USER

data request SALES

DATA BASE single
MANAGEMENT VIEW OF THE DATA
SYSTEM Integrated
data request CUSTOMER
END

USER

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

8:Objectives of DBMS:
A Data Base Management System consists of –
 A collection of interrelated and persistent data (data base).
 A set of programs used to access, update and manage data.

The major objectives of the DBMS are –

1. Data availability
2. Data integrity
3. Data security
4. Data independence.
5. Data backup and recovery

1.Data availability:
The database contains large amounts of data. The DBMS provides facilities for
the end users to access data in the database very easily.
A query is the request to the DBMS for data retrieval. For example, to read or
update the data.
2.Data integrity:
DBMS supports data integrity. Data Integrity means that the data contained in the
database is both accurate and consistent.
3.Data security:

LOGIN

USER NAME :

PASS WORD :

OK CANCEL

Data security rules provides the users to access the data base and which data
base operations (add, read, modify, or delete) the user can perform.
The DBMS provides a strong security system for users data security. The data base
users are identified to the DBMS through a user name and pass word.
4.Data independence:
The DBMS provides an abstract view of the data stored in the database. The
separation of data descriptions (Meta data) from application programs is called Data
Independence.
Data Independence helps to change the data without changing the application
programs that process the data.
5. Data Backup and recovery:
The DBMS should provide a backup facility to restore the database. The backup
facility produces a copy of the entire database in another location. Sometimes
database failure occurs, and then the backup copy is used to restore (recover)
the database.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Q: 9: Write about the classification of DBMS.

Write about the various types of data bases.

classification of DBMS:

The databases are classified based on –

1. Number of users
2. Data base location
3. Data usage

Number of users:-

Single user database:

A Single user database supports only one user at a time. If user ‘A’, is
the using the data base, users B and C must wait until the user A
completes the work.
Multi-user database:
A Multi-User Database supports multiple users at the same time.
The Multi-User Database is divided into two types. They are –
Work group database:
When the multiuser database supports a small number of users
(group) then the database is called “workgroup” database.
enterprise database:
When the database is used by the entire organization and
supports many users then the database is called “enterprise”
database.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Data base location:

Centralized database:
A Centralized database is a database located and maintained at one
location. Here number of users (work stations) are connected to the
centralized database by a network. i.e., the data stored and accessed
from the centralized data base only.

Centralized data base

Distributed database:
A data base that supports data distributed across several different
locations is called distributed database.

Distributed networks are normally used in the networks like Internet or any
other network.
Distributed database

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Data usage:

Operational database:

o A data base that is designed to support day-to-day

operations of an organization is called operational database.
o Operational data base represents daily transactions. It is also
called Transactional Database.

Datawarehouse:

 Datawarehouse is a large database.

 The datawarehouse data contains historical data over al longer period of time.
 Datawarehouse stores data that is used to generate information
required to make decisions.
 For example, Pricing decisions, sales estimates, etc.

2. Services of DBMS (Functions of DBMS):

The various Database Management functions based on integrity and consistency of
the data stored in the data base are –
1. Data dictionary management
2. Data storage management
3. Data transformation and presentation\
4. Security management
5. Multi-user access control
6. Back-up and recovery management
7. Data integrity management
8. Data base access languages and API
9. Data base communication interfaces.

1. Data dictionary management:

 The data dictionary contains Meta data. i.e: data about data
 The data dictionary contains all the attribute names and characteristics of
each table in the database.
 Any changes made in the data base structures are automatically recorded in
the data dictionary.
 Using data dictionary management, DBMS removes structural and data
dependency from the system.
2.Data Storage and Management:
DBMS provides a mechanism for permanent storage of the data. A Modern DBMS
provides storage not only for the data but also related entry Forms, Reports, etc.
Databases today are used to store objects such as – documents,
images, sound, and video in addition to textual and numerical data.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

3.Data Transformation and Presentation:

The DBMS transforms the data to the required format. For example, In MS-Access
the data base table reports can be transformed to a web page.
4.Security management:
Data security rules provides the users to access the data base and which data
base operations (add, read, modify, or delete) the user can perform.

LOGIN

USER NAME :

PASS WORD :

OK CANCEL

The DBMS provides a strong security system for user’s data security. The data
base users are identified to the DBMS through a user name and pass word.
5.Multi-user access control:

DBMS provides a mechanism for managing concurrent (concurrency control)

access to the database. Concurrent access means multiple users can access the
database ‘at the same time.’
6. Backup and Recovery management:
The DBMS provides mechanisms for back up of data periodically. This prevents the
loss of data. The Backup file is used to restore the database in the event of failures.
7.Data Integrity management:
The DBMS provides number of rules to improve the integrity of the data. These rules
are called Integrity constraints. These are used to improve data quality.

8. Data base access languages and API :

The DBMS provides access through a query language (SQL). The DBMS also
provides Application Programming Interface through languages such as – java, .net,
etc.
9. Data base communication interfaces:
The DBMS provides access to the database through internet. The web browser
such as internet explorer (or) Mozilla fire fox provides the interface to the user.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

UNIT -2
1. Advantages of DBMS:
The Data Base Management System is a collection of programs that
manages the database structure and controls access to the data stored in the
data base.
The Data Base Management System presents the end user with a single
(or) integrated view of the data in the data base.
The following are the advantages of DBMS -
1. Improved data access.
2. Improved data security
3. Program-Data Independence.
4. Data-Integration
5. Improved Data sharing
6. Minimized data Inconsistency
7. Improved decision making
1.Improved data access:
The database contains large amount of data. The DBMS provides facilities for the
end user to access data in the database very easily.
A Query is the request for the DBMS for data retrieval. For example, to read or
update the data.
2.Improved data security:
Data security rules provides the users to access the data base and which data
base operations (add, read, modify, or delete) the user can perform.

LOGIN

USER NAME :

PASS WORD :

OK CANCEL

The DBMS provides a strong security system for users data security. The data
base users are identified to the DBMS through a user name and pass word.
3.Program - Data independence:

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

The DBMS provides an abstract view of the data stored in the database. The
separation of data descriptions (Meta data) from application programs is called
Data Independence.
Data Independence helps to change the data without changing the application
programs that process the data.
4.Data-Integration:
Data Base Management System provides an integral view of the organization’s
operations. It becomes much easier to see how actions in one department of the
organization influence other departments.

FINANCE
DEPARTMENT

MARKETING
DEPARTMENT DATA Integral
INTEGRATION END
USER
view of the
SALES
DEPARTMENT data base

5. Improved Data Sharing:

The database is designed as a shared resource to authorized users. The DBMS
helps to create an environment in which multiple users can access the data. That
is the data in the database can be shared among multiple users.
6. Minimized Data Inconsistency:
Data inconsistency exists when the same data appears in different places. By
eliminating the redundancy the data inconsistency will also be reduced.
The data inconsistency is reduced in a properly designed data base.
7. Improved Decision Making:
In Database environment better managed data and improved data access
generates better quality information. This helps the end users to take quick
decisions.
2. Components of Data Base System Environment:

The database system refers to an organization of some components.

Those components are used for collection, storage, management and use of data
with in a database environment.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

The database system environment contains five components -

1) Hardware
2) Software
3) People
4) Procedures
5) Data

Database management system components and interfaces

1) Hardware: Hardware refers to all of the system’s physical devices. For

example –Computers, Storage devices, Printers, Network devices etc.

2) Software: Software is a set of programs Or Collection of programs it is used

for managing Hardware.

There are three types of Software’s are needed in data base environment they
are

1) Operating System- Operating system manages all the hardware

components and helps to run any other software.
2) DBMS Software - The Data Base Management System is a collection
of programs that manages the database structure and controls access to the data
stored in the data base.
3) Application Programs and Utilities Software – Application programs are
used to access and modify the data within the DBMS.
3) People :- This component includes all users of the database system. The
following users can be identified in a database system environment.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

People Interacting with

Database

Database Database System Application End User

Administrator Designer Administrator Programmer

 The person responsible for the control of centralized and shared data
base is known as Database Administrator (DBA).
 The Database Administrator’s main role is to plan, define, and
implement policies and procedures in the data base.
 The role of DBA varies from company to company.
 The role of DBA depends on company’s organization structure.
4) Procedures :
Procedures play an important role in a company because they provide the
standards by which Business is conducted within the organization and with
customers.
Procedures are the rules and instructions that control the design and use
of the data base.
5) Data:
Data is collection of raw set of facts stored in database. The data in the
database can be accessed by the end users without difficulty. Database
designer’s job is to identifying the data, relationships and constraints.

3.Database users:
Database users are categorized based up on their interaction with the
database. The various types of database users in DBMS -

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

1.Database Administrator (DBA):

1. The person responsible for the administration and maintenance of the
data base is known as Data base Administrator (DBA).
2. DBA defines the schema and also controls the three- levels of database.
3. The role of DBA varies from company to company. The role of DBA
depends on company’s organization structure.
The main roles of Data base Administrator (DBA) are –

Data base
Administrator
(DBA)

Installing and
Design and Backup & data base
upgrading the Documenation
implementation Recovery Security
DBMS services

DBA is also responsible for providing security to the database and he

allows only the authorized users to access/modify the data base. The DBA
will then create a new account id and password for the user need to access
the database.
DBA is responsible for the problems such as security breaches and poor
system response time. DBA repairs damage caused due to hardware
and/or software failures.
DBA also monitors the recovery and backup and provide technical
support.

DBA is the one having privileges to perform DCL (Data Control Language)
operations such as GRANT and REVOKE, to allow/restrict a particular user
from accessing the database.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

2,System Analyst:
System Analyst is a user who analyses the requirements of the end users.
They check whether all the requirements of end users are satisfied.
3.Database Designers: Data Base Designers are the users who design
the structure of database which includes tables, indexes, views, triggers,
stored procedures and constraints which are usually enforced before the
database is created or populated with data.
It is responsibility of Database Designers to understand the requirements of
different user groups and then create a design which satisfies the need of
all the user groups.
4.Application Programmers: Application Programmers also referred as
System Analysts or simply Software Engineers, are the back-end
programmers who writes the code for the application programs. They are
the computer professionals. These programs could be written in
Programming languages such as Visual Basic, C, C++ , java , PHP, python
etc.

5.Naive / Parametric End Users: Parametric End Users are the

unsophisticated who don’t have any DBMS knowledge but they frequently
use the database applications in their daily life to get the desired results.
Examples:
 Railway’s ticket booking users are naive users.
 Clerks in any bank is a naive user, because they don’t have any
DBMS knowledge but they still use the database and perform their
given task.
6.Sophisticated Users: Sophisticated users can be engineers, scientists,
business analyst, who are familiar with the database. They can develop
their own database applications according to their requirement. They don’t
write the program code but they interact the database by writing SQL
queries directly through the query processor.
7.Casual Users / Temporary Users: Casual Users are the users who
occasionally use/access the database but each time when they access the
database they require the new information, for example, Middle or higher-
level manager.
8.Specialized users: Specialized users are sophisticated users
who write specialized database application that does not fit into the
traditional data- processing framework. Among these applications are
computer aided-design systems, knowledge-base and expert systems etc.

4.RELATIONAL DATABASE MODEL:

 The Relational Model was developed by Dr.E.F.Codd in 1970s. It is the
most common model to represent the data in the database.
 Dr.E.F.Codd developed a set of rules for a Relational DBMS, popularly
known as Codd’s Rules.
 In a relational model, data is stored in tables. Three key terms are used

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

frequently in relational database models: relations, attributes, and

domains.
 A relation is a table with columns and rows.
 The named columns of the relation are called attributes
 The domain is the set of values of the attributes.

LOGICAL VIEW OF DATA:

The relational models allow users to view the data logically rather than
physically. The logical view of the relational database is facilitated by the
creation of data relationships through a table.

A table is a two dimensional structure contains rows & columns. The data is
stored in these rows and columns. A table is also called relation.
Example: consider the following STUDENT table

Field

ROLLNO NAME COURSE CITY

1000 RAJU BSC NELLORE

Record
1001 RAVI BCOM CHENNAI

1002 SINGH BSC GUDUR

1003 KIRAN BCOM NELLORE

1004 RAGHU BSC KAVALI

The student table contains 5 rows and 4 columns. Rollno uniquely

identifies each row. That is , rollno is the primary key.

Properties (or) Characteristics of a Relation (or) Table:

1. A table is a two- dimensional structure that contains rows & columns.
2. Each attribute (or) column within a table has a unique name.
3. All values in a column must have the same data type.
4. Each row / column intersection represents a single data value.
5. Each column has a specific range of values known as the attribute domain.
6. Each table must have an attribute (or) a combination of attributes that
uniquely identifies each row. It means each table has a primary key.
Relational database Terminology :

1) Attribute: Attributes are the properties that define an

entity. In the above table ROLLNO, NAME, COURSE AND CITY

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

are attributes
2) Relational db Schema: A database schema is the logical
representation of a database, which shows how the data is stored
logically in the entire database.
Example:
Schema of STUDENT table(relation) -

STUDENT (ROLLNO, NAME, COURSE,CITY)

is the relation schema for STUDENT.

If a schema has more than 1 relation, it is called Relational data

base Schema.
3) Tuple: Each row in the relation is known as a tuple. The above
relation contains 5 tuples.

4) Relation Instance: The set of tuples of a relation at a particular

instance of time is called a relation instance. The above Table
shows the relation instance of STUDENT at a particular time. It can
change whenever there is an insertion, deletion, or update in the
database.
5) Degree: The number of attributes in the relation is known as the
degree of the relation. The STUDENT relation defined above has
degree 4.

6) Column: The column represents the set of values for a particular

attribute. The column ROLLNO is extracted from the relation
STUDENT.
7) NULL Values: In SQL there may be some records in a table that do
not have values or data for every field and those fields are termed as
a NULL value. A field with a NULL value has no specific data value.

8) Relation Key: These are basically the keys that are used to identify
the rows uniquely or also help in identifying data in the tables.
o Primary Key
o Candidate Key
o Super Key
o Foreign Key
o Alternate Key
o Composite Key

5. Codd’s Rules:
Dr.E.F.Codd’s defined a list of 12 rules to define a Relational data base
System. Any Data base system that follows these rules can be called as Relational data
base system.
The following are the 12 rules proposed by Dr.E.F.Codd –
1. Information

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

2. Guaranteed access
3. Systematic treatment of Nulls
4. Usage of meta data
5. Data sublanguage
6. View updating
7. High level insert, update and delete
8. Physical data independence
9. Logical data independence
10. Integrity independence
11. Distributed database
12. Non-sub version

Rule :1 : Information:

All information in a relational data base must be logically represented in the

form of tables. A table contains rows and columns. A row is called as a
Record. A column is known as a field.

Rule :2 : Guaranteed access:

Every value in a table must be accessible through a combination of table

name, primary key value and the column name.
Rule :3: Systematic treatment of Nulls:

Null values are supported in relational data base management system. Nulls
must be represented and treated in a systematic way.

Rule :4 : Usage of Metadata:

Metadata means data about data. The meta data must be stored and
managed. The metadata must be available to the authorized users.

Rule :5 : Data sub-language:

The relational data base may support many languages. It must support one
well defined language for –

 Define tables and views

 Query and update the data
 Set integrity constraints.

Rule :6: View updating:

Any view that is theoretically updatable must be updatable through the

system.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Rule :7 : High level insert, update and delete:

The database must support insertion, deletion, and updation at the table
level.

Rule :8 : Physical data Independence:

Application programs are unaffected when changes are made to the storage
structures. That is, The changes to the physical level (how the data is stored)
must not require a change in programs.
Rule :9 : Logical data Independence:
Application programs are unaffected when changes are made to the table
structures . For example, changing the order of the column or inserting
columns.That is, changes to the logical level (adding columns, changing
column lengths etc.) must not require a change in programs.

Rule :10: Integrity Independence:

All relational integrity constraints must be definable in the relational language

and stored in the system catalog.
Rule :11 : Distributed data base:

The end users and application programs are unaware and unaffected by the
data location.

Rule :12 : Non-subversion:

If the system supports low-level access to the data, there must not be a way
to bypass the integrity rules of the data.

6.Concept of Keys in DBMS:

Types of Keys in Relational Model :
Keys are one of the basic requirements of a relational database model. It is
widely used to identify the tuples (rows) uniquely in the table.
We also use keys to set up relationship between tables in a relational database.
The various types of Database Keys are -
1. Super Key
2. Candidate Key
3. Primary Key
4. Alternate Key
5. Foreign Key
6. Composite Key

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

1.Super Key
1. The set of attributes that can uniquely identify a tuple is known as Super
Key.
2. Example:
STUD_NO, (STUD_NO, STUD_NAME), etc.
3. A super key is a group of single or multiple keys that identifies rows in a
table. It supports NULL values.
4. Adding zero or more attributes to the candidate key generates the super
key. A candidate key is a super key but vice versa is not true.
5. Super Key values may also be NULL.
Example:
Table STUDENT
STUD_NO SNAME ADDRESS PHONE

1000 Teju Nellore 9988552211

1001 Arun Gudur 9595656565

1002 Venu Kavali 9696696969

1003 Anil nellore 9494010101

(STUD_NO,PHONE) is a super key.

2.Candidate Key
The minimal set of attributes that can uniquely identify a tuple is known as a
candidate key.It is a minimal super key.It is a super key with no repeated data is
called a candidate key.The minimal set of attributes that can uniquely identify a
record.
 It must contain unique values.
 It can contain NULL values.
 Every table must have at least a single candidate key.
 A table can have multiple candidate keys but only one primary key.
 The value of the Candidate Key is unique and may be null for a tuple.
 There can be more than one candidate key in a relationship.
Example:

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

STUD_NO is the candidate key for relation STUDENT.

The candidate key can be simple (having only one attribute) or composite as well.
Example:

(STUD_NO, COURSE_NO) is a composite

candidate key for relation STUDENT_COURSE.
Table : STUDENT_COURSE
STUD_NO TEACHER_NO COURSE_NO

1000 001 C001

1001 056 C005

3.Primary Key
There can be more than one candidate key in relation out of which one can be
chosen as the primary key. For Example, STUD_NO, as well as STUD_PHONE,
are candidate keys for relation STUDENT but STUD_NO can be chosen as
the primary key (only one out of many candidate keys).
 It is a unique key.
 It can identify only one tuple (a record) at a time.
 It has no duplicate values, it has unique values.
 It cannot be NULL.
 Primary keys are not necessarily to be a single column; more than one
column can also be a primary key for a table.
Example:
STUDENT table  Student(STUD_NO, SNAME, ADDRESS, PHONE)
STUD_NO is a primary key
Table STUDENT
PK
STUD_N SNAM ADDRES
O E S PHONE

998855221
1000 Teju Nellore
1

959565656
1001 Arun Gudur
5

969669696
1002 Venu Kavali
9

949401010
1003 Anil nellore
1

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

4.Alternate Key
The candidate key other than the primary key is called an alternate key.
 All the keys which are not primary keys are called alternate keys.
 It is a secondary key.
 It contains two or more fields to identify two or more records.
 These values are repeated.
 Example: SNAME, and ADDRESS are Alternate keys
Example:
Consider the table shown above.STUD_NO, as well as PHONE both, are
candidate keys for relation STUDENT but PHONE will be an alternate key (only
one out of many candidate keys).
5.Foreign Key
If an attribute can only take the values which are present as values of some other
attribute, it will be a foreign key to the attribute to which it refers. The relation
which is being referenced is called referenced relation and the corresponding
attribute is called referenced attribute. The referenced attribute of the referenced
relation should be the primary key to it.
 It is a key it acts as a primary key in one table and it acts as
secondary key in another table.
 It combines two or more relations (tables) at a time.
 They act as a cross-reference between the tables.

Example:
TABLE : DEPARTMENT
PRIMARY KEY : DEPT ID
PK
DEPTID DNAME HOD

10 MCA SUNDER

20 MSC MADHU

30 MBA UDAY

40 MCOM SINGH

TABLE : STUDENT
PRIMARY KEY : STUD_NO
FOREIGN KEY : DEPTID

PK FK

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

STUD_NO SNAME PHONE DEPTID

1000 Teju 9988552211 10

1001 Arun 9595656565 20

1002 Venu 9696696969 40

1003 Anil 9494010101 10

The primary key –deptid in the DEPARTMENT table, appears as foreign key in
the related table STUDENT.

6.Composite Key:
Sometimes, a table might not have a single column/attribute that uniquely
identifies all the records of a table. To uniquely identify rows of a table, a
combination of two or more columns/attributes can be used. Sometimes it may
contain duplicate values also. So, we need to find the optimal set of attributes
that can uniquely identify rows in a table.

 It acts as a primary key if there is no primary key in a table

 Two or more attributes are used together to make a composite key.
 Different combinations of attributes may give different accuracy in terms of
identifying the rows uniquely.
Example:
(STUD_NO, PHONE) is a composite key

7.Integrity Constraints:
An integrity constraint is a rule that cannot be violated by the user. The
constraints are used to prevent invalid data entry into the table. It enforces rules
for the columns in a table. In SQL the integrity constraints are classified into 3
types. They are
1) Domain integrity constraints
2) Referential integrity constraints
3) Entity Integrity constraints
Domain integrity constraints:-
A domain is a set of values, that may be assigned to a column. The domain

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

integrity constraints enforces valid entries for a given column from the set of
possible values. The domain constraints are of 2 types. They are
i) NOT NULL constraints
ii) CHECK constraints
NOT NULL:-
The value which is absent is called NULL. This constraint is used to avoid the
NULL values in a column of a table. i.e if any column is specified as NOT NULL,
then we must have to enter a value for the NOT NULL constraint allows the
duplicate(redundant) values.
Ex:- 1 Create table student(htno number(5) not null, sname varchar(20), address
varchar(20));
In the above example the column htno does not allow NULL values. But other
sname, address is allows NULL values. Suppose we can add the NOT NULL
constraint after creating the table then we can use Alter table command i.e
Alter table student modify sname not null;
CHECK:-
The check constraint is used to specify a condition that should satisfy for each
row in a table before it is stored.
Ex:- create table student(htno number(5), sname varchar(20), fee number(7,3)
check(fee>0)); Suppose we can add the Check constraint after creating the table
then we can use Alter table command i.e
Ex:- alter table student add check(sname like ‘s%’);
Entity integrity constraints:-
These are also used to prevent invalid data into a column of a table. Mainly
entity integrity constraints are divided into 2 types. They are
i) Unique constraint
ii) Primary Key constraint
Unique Constraint:-
It is used to prevent duplicate values. Any column declared with “unique”
constraint in that column does not allow duplicate values. how ever it allows null
values into the column
Ex:- Create table student(htno number(8) unique, sname varchar(10),address

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

varchar(20));
Suppose we can add the Unique constraint after creating the table then we can
use Alter table command i.e
Alter table student add unique(fee);
Primary Key:-
 A field or combination of fields used for identifying a single record will be
called as “Primary key”.
 The primary key constraint does not allow duplicate and NULL values.
Only one primary key constraint can be created for each table.
 The key word primary key is used to define a primary key.

Example:-1
Create table student(htno number(5) primary key, sname varchar(15), address
varchar(20));
Example:2:-

Create table student(sid number(8), htno number(5), sname varchar(15), address

varchar(20), primary key(sid, htno));

Referential Integrity Constraints:-

 A field which references to primary key of another table will be called as a
“foreign Key”. It is used to establish a relationship between multiple
tables.
 For specifying the relationship there must be one common column.
 The table in which the common column it specified as a primary key will be
called as “Master” or “parent” table, Where as the another table will be
called “detail” or “child” table.

Example:-

Master or parent table:

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

DEPARTMENT TABLE
Create table deptartment(deptid number(5) primary key, dname varchar(10), hod
varchar(15));

Detail or child table:

STUDENT TABLE
Create table student (studno number(5) primary key, sname varchar(15),
phone number(10), deptid number(5) references dept(deptid));

The keyword references is used to create the foreign key.

TABLE : DEPARTMENT
PRIMARY KEY : DEPTID
PK
DEPTID DNAME HOD

10 MCA SUNDER

20 MSC MADHU

30 MBA UDAY

40 MCOM SINGH

TABLE : STUDENT
PRIMARY KEY : STUDNO
FOREIGN KEY : DEPTID

PK FK

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

SNAM
STUDNO E PHONE DEPTID

1000 Teju 9988552211 10

1001 Arun 9595656565 20

1002 Venu 9696696969 40

1003 Anil 9494010101 10

The primary key –deptid in the DEPARTMENT table, appears as foreign key in
the related table STUDENT.
The records from the Master table will not be deleted until the corresponding
records are deleted from the detail table. Similarly new records can not be
inserted into the detail table, until the corresponding values are inserted in the
master table.
UNIT - 3
ENTITY-RELATIONSHIP MODEL(ER-MODEL)

1.INTRODUCTION TO ER-MODEL:
1. Peter Chen developed the ER diagram in 1976.
2. The ER model was created to provide a simple and understandable
model for representing the structure and logic of databases.
3. The Entity Relationship Diagram explains the relationship among the
entities present in the database.
4. ER diagrams provides the real-world modeling of objects.
5. ER- Diagram contains three basic components – Entity, attributes,
Relationships.
symbols used in ER Diagrams:

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

 Rectangles: This Entity Relationship Diagram symbol represents entity

types
 Ellipses : Symbol represent attributes
 Diamonds: This symbol represents relationship types
 Lines: It links attributes to entity types and entity types with other
relationship types
 Primary key: attributes are underlined
 Double Ellipses: Represent multi-valued attributes

2.Basic Building Blocks of E-R Model (OR) Components of E-R Model:

1. An Entity Relationship Model(ER-MODEL) is represented as an Entity

Relationship Diagram (ERD).
2. ER diagram gives pictorial(diagramatic) representation of the data
base components-
 Entities
 Attributes
 Relationships

1. Entity:
 An entity is a thing, a person or an event about which data is to be
collected and stored.
 An entity represents a particular type of object in the real world.
 Entities are distinguishable i.e., each entity occurrence is unique and
distinct.
 An entity is represented by a Rectangle symbol.

STUDENT
2.Attributes:
 An attribute is a characteristic of an entity.
 In chen notation the attributes are represented by a oval symbol and are
connected to the rectangle.

Example:
Consider the student entity with the following attributes –

Rollno nameutes
course

STUDENT

The following are the different types of attributes –

1. Simple attribute
2. Composite attribute

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

3. Single-valued attribute
4. Multi-valued attribute
5. Derived attribute

3.Relationships:
 A Relationship is an association between the entities.
 A Relationship is denoted by a diamond symbol.

 There are three types of Relationships –

 One-to-One Relationship (1:1)
 One-to-Many Relationship (1:M)
 Many-to-Many Relationship (M:M)

 All the entities (Rectangle) participating in a relationship are connected

to the
diamond symbol by lines.

Example:

1, “ A CUSTOMER GENERATES MANY ORDERS “

generates
CUSTOMER ORDERS

2.“ A STUDENT joins in a COURSE”.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

2.ATTRIBUTE CLASSIFICATION:

1. An attribute is a characteristic of an entity.

2. In chen notation the attributes are represented by a oval symbol and

are connected to the rectangle.

3. Attributes have a domain. A domain is a set of possible set of values for

a given attribute.
4. The following are the different types of attributes –

 Simple attribute
 Composite attribute
 Single-valued attribute
 Multi-valued attribute
 Derived attribute

1.Simple Attribute:-
A Simple attribute is an attribute that cannot be subdivided into smaller
components. It is denoted by “Ellipse Symbol (oval symbol)”.
For example- age, marriage status can be considered as “simple attributes”.

2.Composite Attribute:
A Composite attribute is an attribute that can be sub-divided into additional
attributes.
The most common example, composite attribute is address, which can be
divided into components like Dno, Street, City, Pincode etc.

3.Single – Valued Attribute:

A Single valued attribute is an attribute that can have only one a single value.
For example, a person can have only one date of birth

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

4.Multi value Attribute:

Multi- valued attribute is an attribute that can have many values. In ER
diagrams, multi valued attributes are represented by using ‘Double-Lined
Ellipse’.
Example:

In the above ER-diagram mob_no and email_id are the multi-valued attributes.

5.Derived or Stored Attribute:

Derived attribute is an attribute whose value is derived from some other
attribute. In ER diagrams derived attributes are indicated by using Dotted
Ellipse.

Example:

Age is a derived attribute. Age is derived from dob.

3. CLASSIFICATION OF ENTITY SETS:

The entity sets are classified into two types -

a) Strong Entity Type

b) Weak Entity Type

a) Strong Entity Type:

 A Strong Entity is an entity, whose existence does not depend on
any other entity.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

 A strong entity contains an attribute which acts as a primary key.

 In E-R Diagrams strong entities are indicated by Rectangle box.

Example: In the following ER-Diagram EMPLOYEE is the strong entity.

b) Weak Entity Type :

 A Weak entity is an entity, whose existence depends on some
other entity.
 In ER diagram weak entity can be indicated by double line
Rectangle box.
 A weak entity does not have an attribute which can be used as a
primary key.

Example: Consider the following ER-Diagram.

“CUSTOMER borrows LOAN”

CUSTOMER is the strong entity.

LOAN is the weak entity

4.Relationship Degree:
 A Relationship degree indicates the number of entities associated with in a
Relationship.
 The ER-Model supports three types of relationship degree –

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

1. Unary Relationship
2. Binary Relationship
3. Ternary Relationship

1.Unary Relationship:
A Unary Relationship exists when an association is maintained within a single
entity.

Example:
“ An Employee manages another Employee”. That is, an employee has
relationship with itself. Such a relationship is known as Recursive Relationship.

2.Binary Relationship:
 A Binary Relationship exists when two entities are associated in a
relationship.
 Binary relationships are most common.
Example:
1. “An EMPLOYEE works in a DEPARTMENT”

2. “ A STUDENT enrolled in a COURSE”

3.” A CUSTOMER places many ORDERS”

This is a One-to-Many relationship.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

3.Ternary Relationships:
A Ternary Relationship exists when three entities are associated in a
Relationship.

Example:

1.“An EMPLOYEE works in a DEPARTMENT in the LOCATION”

2. “A DOCTOR prescribes MEDICINE to a PATIENT”

5.Relationship Classification:

 A Relationship is an association between the entities.

 A Relationship is denoted by a diamond symbol.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

 There are three types of Relationships –

 One-to-One Relationship (1:1)

 One-to-Many Relationship (1:M)
 Many-to-Many Relationship (M:M)

1) One-to-One Relationship (1:1):

When only one instance of an entity is associated with Relationship, it is
known as
One-to-One Relationship (1:1).

The following diagram shows One-to-One Relationship (1:1) –

1 1
ENTITY relationship
ENTITY

Example:
Each EMPLOYEE is allotted with only one PARKING PLACE (or) each
PARKING PLACE Must be given to one EMPLOYEE.

1 1
EMPLOYEE has PARKING PLACE

2) One-to-Many Relation Ship (1 : M) :

When more than one instance of an entity is associated with a Relationship, it
is known as One-to-Many Relationship (1:M).
The following diagram shows One-to-Many Relationship (1:M) –

1 M
ENTITY relationship
ENTITY

Example:
A PAINTER paints many PAINTINGS but each of them is painted by only
one PAINTER.

has

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

1 M
EMPLOYEE PARKING PLACE

3.Many-to-Many Relation ship(M:M):

When more than one instance of an entity on the left and more than one
instance of an entity on the right can be associated with a Relationship, it is
known as Many-to-Many Relationship (M:M).

The following diagram shows Many-to-Many Relationship (M:M) –

M M
ENTITY relationship
ENTITY

Example:
“ An EMPLOYEE can learn many COURSES , a COURSE can be learned by
many EMPLOYEES.”

M M
EMPLOYEE learns COURSES

UNIT - 4 Structured Query Language (SQL)

1. Introduction-Commands in SQL.
2. SQL Literals
3. Data Types in SQL
4. SQL Operators
5. Data Definition Language (DDL)
6. Data Manipulation Language (DML)
7. Aggregate functions
8. Queries
9. TCL operations
10.DCL operations

1. Introduction-Commands in SQL:
Introduction to SQL:- (Q: What is SQL ? )

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

1. The “Structured Query Language”(SQL), contains standard set of

commands used to access data within the relational database.
2. All tasks related to relational database management system, such as
crating tables, modifying the data in the database, granting access to users
and so on cab be done using SQL.
Features and SQL Commands
1) SQL is a non-procedural language
2) It is a general purpose English like language
3) SQL is a Fourth generation language
4) Every command in SQL is terminated by (; ) semi colon.
Characteristics and advantages of SQL
1. SQL is free from syntax that allows the user to write SQL Statements easily
2. There is no need to complete the SQL statement in one line
3. The SQL Commands are very simple and very easy to understand.
4. Each SQL command is analyzed by RDBMS before execution to check for
proper syntax and to process the command.
5. The SQL is used to define the databases, to modify the databases and
remove the databases
6. The SQL provides very simple commands for querying or selecting, inserting,
deleting and updating database’s information.
SQL Commands:
SQL contains following types of commands –
1) Data Definition Language (DDL)
2) Data Manipulation Language (DML)
3) Data Control Language (DCL)
4) Transaction Control Language (TCL)
1) Data Definition Language (DDL):
1. Create
2. Alter
3. Truncate
4. Drop
2) Data Manipulation Language (DML):
1. Insert
2. Select
3. Update

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

4. Delete
3) Data Control Language(DCL):
1. Grant
2. Revoke
4)Transaction Control Language (TCL):
1. Commit
2. Rollback
3. Savepoint

2.SQL Literals :
Literals are explicit representations of data in expressions. They are used in SQL
statements to represent data that does not change.
The following literals are used in SQL –
1. Numeric literals
2. Boolean literals
3. Date literals
4. String literals

1. Numeric Literals:
Both integer and floating-point numbers are represented by numerals.
These numerals may be preceded by either a plus character (+) or a minus
character (–) to denote sign.
Floating-point values use a period to separate the whole number part from the
decimal part. We cannot use a comma as a decimal point.
The following values are examples of numeric literals:
1000
10.0
-3456.43
3454324.9378
Example:
The following is an example of a query with a Numeric literal:
update student set course=’B.Sc’ where sno=101;
2. Boolean Literals:
 The Boolean value True is represented as 1 and a Boolean False is
represented as 0.
 We can use the keywords TRUE instead of 1 and FALSE instead of 0.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

3. String Literals
 A string literal is a sequence of one or more characters enclosed in single
quotation marks.
 Any printable character can appear in a string literal.
 The following are examples of string literals:
'india’
‘university’
Example:
The following is an example of a query with a string literal:
SELECT "Customer ID" FROM CUSTOMER WHERE State = 'ANDHRA PRADESH';
This query selects the Customer ID field from the CUSTOMER table for all
customers whose State field contains an exact match to the characters ANDHRA
PRADESH.

4.Date Literals :
Literal date values are enclosed in single quotes, and use the current date
format setting.
Example:
'04/15/2017'

3.Data types in SQL:

 The data type specifies what type of data that can be entered into
a column of a table.
 The SQL provides different data types. The most commonly used
data types are -
1) Number
2) Char
3) Varchar (Or) Varchar2
4) Long
5) Date
6) Large Objects
1) Number:- This data types is used to store number or numeric data in a
specified column. The data may be integer or floating point data.
Syntax: Number (l,d)

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Here ‘l’ specifies the length and ‘d’ specifies the no of decimal places with in the
length. The length should be in the range from 1 to 38. The default length is ‘38’
digits.
Ex:- rollno number(5)
price number(8,5)
2) Char:- The Char data type is used to store fixed length character data.
Syntax: char (size);
Here size indicates the maximum no of characters
Ex: name char(10)
3) Varchar2:- The “Varchar2” data type is similar to “Char” data type. It is used
to store variable length strings. The maximum no of characters that can be
entered is 4000 bytes.
Syntax:- Varchar2(size)
Here size indicates the maximum no of characters.
Ex: name varchar(10)
Example:
1. name char(10)
I N D I A

10 Memory locations are allocated for the fixed length character data.
2. name varchar2(10)
I N D I A

5 memory locations are allocated for the variable length varchar2(10)

Memory can be saved by using varchar2( ) data type.
4) Long: Long data type is used to store alphanumeric values upto 2GB of size.
Generally this data type is not used. Only one column of a table should have
“long” data type i.e A table can have only one long column.
Syntax:- long(size);
5) Date: This data type is used to store date and time values. The default date
format is “DD-MON-YY”.
Ex:- Custdob date;

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

6) Large Objects :- This data type occupies 4GB memory. These objects are
used to store the binary data and character data. These are two types -
1) BLOB- Binary Large Objects
2) CLOB - Character Large Objects
Example: using SQL DATA TYPES :
In a table each column is identified by a column name and a given data type with
specified width. Data types are used to define columns in a table.
1.Student table:
Create table student(htno number(8) primary key,
name varchar2(15),
course varchar2(6),
phno number(10));
2. Customer table:

Create table customer(custno number(4) primary key,

name varchar2(15),
dob date,
city varchar2(15));

4.Operators in SQL
SQL supports following types of operators -
1. Arithmetic operators
2. Relational operators (comparison)
3. Logical operators
4. Special operators

1.Arithmetic operators:-
In sql the arithmetic operators are used to perform various arithmetic operators
on the data. The different types of arithmetic operators are –

OPERATOR MEANING
+ Addition
- Subtraction
* Multiplication
/ Division
^ (OR) ** Raise to the power of

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Example:-
SQL query to display employee salaries increased by 10%.
select eno,ename, sal+(sal*10/100) from empolye;

2.Relational Operators:-
Relational operators are used to compare two values. The operators are also
called as comparison operators. The relational operators supported by SQL are -
OPERATOR MEANING
= Equal to
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
<> (or) != Not Equal to

Example:

select eno,ename, sal from empolye where sal>=10000;

Logical Operators:-
The Logical Operators are used to combine two or more relational expressions.
These operators are also called as “compound operators”.
The logical operators supported in SQL are –

1. AND.
2. OR.
3. NOT.

AND : It returns true, If both expressions are true.

OR : It returns false, If both expressions are false
NOT : It reverses the value of expression

Example:-
select * from employe where sal>=2000 and job=‘MANAGER’;
The above example retrieve the data from the emp table by using where clause
i.e sal>=2000 and job= ‘MANAGER’;

Special Operators:-
The SQL allows special operators can be used with “where” clause of select
command.
The special operators supported by SQL are -

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

1. Between
2. Is null
3. Like
4. In
1) Between:-
Between operator is used to check whether a column value is with in a range.
Example:-
Select *from emp where sal between 2000 and 5000;
The above example retrieve the data from emp table, whose salary are between
2000 and 5000.
2) Is Null :-
IS NULL operator is used to check for the Null values in a column.
Example:-
select eno,ename,comm from empolye where comm is null;

3) Like:-
Like operator is used to check whether a column value matches a given
character string. It is only used for character pattern.
%(any character)
_ (underscore) (one character)
Ex:-1) Select *from emp where ename like ‘A%’;
This example displays the ‘ename’ whose employee name must be starts
with A and remaining letters should be any.
Ex:-2) select *from emp where ename like ‘_S%’;
This example displays the ‘ename’ values second character should be ‘S’
and remaining letter should be of any.

3) In:-
In operator is used to check whether a column value matches any value with in
specified list of values.
Ex:- 1) select *from emp where job in (‘manager’, ‘clerk’);
2) select *from emp where sal in (2000,3000,5000);

5.Data Definition Language(DDL)Commands:

The Data Definition Language (DDL) commands are used to define, modify
and remove the database objects.
The following are the DDL commands –
1. Create
2. Alter

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

3. Truncate
4. Drop
1. Create :-
 In SQL the database primary object is “table”.
 The table contains rows and columns in which the data is stored.
 In a table each column is identified by a column name and a given data type
with specified width.

COLUMNS

EMPNO NAME JOB SALARY

ROW
S

Logical Structure Of A Table

 In SQL the tables are created by using “Create table” command.

 This command creates only the structure of the table.

Syntax:
Create table <table-name> (Columnname1 data-type(width),
Columnname2 data type (width),….);

 The ‘table-name’ is a user-defined word which identifies the name of the

table.
 The column names are also user-defined words which specify the names of
the fields. The column names must be unique in a table’s structure.
 The data type is any valid SQL data type.

Example:
Create table student(htno number(8),
name varchar2(15),
phno number(10));

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

2.Alter:-
The alter command is used to modify the structure of the existing table. By using
the alter command we can do the following-
 We can add new columns to an existing table.
 We can remove the columns from an existing table
 We can add and drop constraints

Adding a column:
The following command is used to add a new column to the existing table.
Syntax:-
Alter table <table-name> ADD(<column-name> data-type(width));
Example:
Alter table student add(dob date);
The above command adds new column “dob” to the Student table.

Modifying the length of the data type:

The following command is used to modify the length of the data type.
Syntax:- Alter table <table-name> MODIFY(<column-name> data-type(width));
Example:
Alter table student modify(name varchar2(20));
The above command modifies the length of the existing column “name” length
from 15 to 20.
Adding a constraint:
The following command is used add a constraint.
Syntax:- Alter table <table name> add constraint;
Example:
Alter table student add primary key(htno);
The above command adds a primary key constraint to the ‘htno’ column on
Student table.
3.Truncate:-

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

This command is used to remove the all the data permanently from the table and
free the memory space containing in the table.
Syntax:- Truncate table <table name>;
Ex:- Truncate table student;
The above command removes all the data permanently from the table “student”
but it does not remove the structure of “Student ” table.
4.Drop:-
A table can be deleted from the data base using the drop table command. All the
rows in the table are deleted and the structure is removed from the database.
Syntax:-
Drop table <table name>;
Example:- Drop table student;
The above command removes the table “Student”.

6. DML (Data Manipulation Language) Commands

The Data Manipulation Language (DML) commands are used to
manipulate data in a database. i.e., The DML commands are used to insert data,
modify data, delete data and retrieve data in the table.
The DML commands supported by SQL are –
1. Insert
2. Select
3. Update
4. Delete
1.Insert:-
The Insert command is used to enter records into a table.
The following form of Insert command is used for inserting data into all the
columns of table.
Syntax:
insert into <table name> values (val1,val2……);
Example:
insert into student values(1001,’prasad’,’B.Sc’);

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

The following form of Insert command is used for inserting values into particular
columns of the table.
Syntax: insert into <table name> (field1,field2,……..) values (val1,val2……);
Example:
insert into student (sno, sname) values (1002,’rajesh’);
The above insert command is inserts only “sno, sname” values to “student” table.
Syntax for inserting Multiple rows:
The following form of Insert command can be used to enter any number of rows
into the table.
Syntax:
insert into <table name> values (‘&field1’,’&field2’,……..);
Ex:- insert into student values(‘&sno’,’&sname’,’&course’);
The above insert command takes the values for Sno, Sname, course.

2.Select :
 The select command is used to retrieve (or) display the data from the
table.
 we can select all columns (or) specific columns from the table.
Syntax:
select <column-list> from <table name>
[Where <condition>]
[group by <column-name>]
[having <condition>]
[order by <expression>];

Example:
1.Select *from student;
The above command displays all columns and all rows from the “student” table.
2. Select sno, sname from student;
The above command displays specific columns i.e., sno, sname and all rows
from the table “student”
3. select * from student where sno=101;

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

The above command displays all columns and specific rows that will satisfy the
condition sno=101;
4.Select * from student order by sname;
The above command displays all the rows from the student table in sorted order
based on column – sname.

3.Update:-
The update command is used to modify the data in a table. This command uses
“Set” clause.

Syntax :
update <table-name> set <column-name>=<value>,
[<column-name>=<value>…]
[where <condition> ];

The where condition is optional. If we don’t specify the where condition, the
update command will apply the changes to all the rows in the table.
Example:
1. Update student set course=’B.Com’;
The above update command modifies the all records(rows) in the student
table.
For modifying only the specified records(rows),
we have to use “where” clause.
2. update student set course=’B.Sc’ where sno=101;
The above command modifies only particular records that will satisfy the
condition sno=101 from the table “student”.

3.UPDATE employee SET HRA = salary * 20 / 100;

The above command updates HRA column of employee table with 20% of
salary.

4.UPDATE employee SET salary = 20000 WHERE job = ‘MANAGER’;

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

The above command updates salary column of employee table with 20000,
for all rows where job matches with ‘MANAGER’.

4.Delete:
The delete command is used to delete the specific records (or) all records from
the table. For deleting a particular record in the table, we have to use “Where”
clause.

Syntax:
delete from <table name> [where <condition>];
Example:
1. delete from student;
The above command deletes all rows from the table
2. delete from student where sno=101;
The above command deletes specific rows that will satisfy the condition sno=101
from the table “student”.

SQL LAB EXERCISES

Student table:
1. Create table student(rollno number(4) primary key,

name varchar2(15),
dob date,
city varchar2(15));
2. insert into student values(&rollno,’&name’,’&dob’,’&city’);
3. select * from student;
4. select name,dob from student;

employee table:
1. Create table employe(eno number(4) primary key,

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

1. ename varchar2(20),
2. sal numbe(8,2),
3. comm number(8,2));
2. insert into employe values(&eno,’&ename’,&sal,&comm.);
3. select * from employe;
4. select eno,ename,comm from empolye where comm is null;
5. write a SQL query to display employee salaries increased by 10%.
6. select eno,ename, sal+(sal*10/100) from empolye;

7. Aggregate functions:
 Aggregate functions provide a single value for an entire group of table
entries.
 The Aggregate functions are also known as Group functions.
 The Aggregate functions available in SQL are as follows -
1. Max()
2. Min( )
3. Sum( )
4. Avg( )
5. Count( )
1) Max( ):
This function is used to find the maximum or highest value of the specified
column in a table.
Syntax:
Select max(column-name) from table-name;

Example:
select max(sal) from emp;
2) Min( ):
This function is used to find the minimum or lowest value of the specified column
in a table.
Syntax:
Select min(column-name) from table-name;
Example:
select min(sal) from emp;

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

3) Sum():
This function is used to find the total or sum of values in a specified column in a
table.
Syntax:
Select sum(column-name) from table-name;

Example:
select sum(sal)from emp;

4) Avg():
This function is used to find the average of specified column in a table
Syntax:
Select avg(column-name) from table-name;
Example:
Select avg(sal) from emp;

5) Count():
This function is used to count the number of rows or values.
The count( ) function can be used in three ways-
count (*):
The count(*) function counts the nulls and duplicate values. That is it counts the
number of rows.
Syntax:
Select count(*) from table-name;
Example:
select count(*) from emp;
count(<column name>):
The count(<column name>) counts the number of not null values in a column.
Syntax:
Select count(column-name) from table-name;
Example:

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

select count(sal)from emp;

count distinct:
The count(distinct <column name>) counts the values without including nulls and
duplicates. That is, it returns the distinct values of the given column.
Syntax:
Select count(distinct column-name) from table-name;
Example:
select count(distinct deptno) from emp;

8.QUERIES:
Q: What is a query? Explain SQL queries in detail with examples.
(or)
Explain select command with examples.
(or)
How to retrieve data from a data base table ?

Query:
1. The query is a process that retrieves required information from the
database.
2. The SELECT command is used to query the database.

3. A query can be used for the following purposes-

 To retrieve data from one or more tables

 To sort the data on multiple columns either in ascending or
descending order
 To filter data using conditions
 To group the rows based on multiple columns
 To join multiple tables together
Select command:
 The select command is used to retrieve (or) display the data from the
table.
 we can select all columns (or) specific columns from the table.
Syntax:

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

select <column-list> from <table name>

[Where <condition>]
[group by <column-name>]
[having <condition>]
[order by <expression>];

Basic form of SQL query:

The basic form of SQL query displays all the rows from the table.
Example:
1. Select * from student;
The above command displays all columns and all rows from the “student”
table.
2. Select * from emp;
The above command displays all columns and all rows from the “emp” table.
The required columns can be displayed in SQL queries as follows -
Example:
1. Select sno, sname from student;
The above command displays specific columns i.e., sno, sname and all rows
from the table “student”
2.select empno,ename from emp;
Select – where:
The WHERE clause is used to display the rows based on the condition. When it
is used, it displays all the rows that match the given condition.
Example:
1. select * from student where sno=101;
The above command displays all columns and specific rows that will
satisfy the condition sno=101;
2. Select *from emp where sal between 2000 and 5000;
3. select eno,ename,comm from empolye where comm is null;
4. select * from employe where sal>=2000 and job=‘MANAGER’;

Order by:
The order by clause is used to display rows either in ascending or descending
order based on the specified columns.
Example:

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

1. SELECT * FROM employee ORDER BY ename;

The above query displays all the rows in the employee table in the
ascending order based on the column ename.
2. SELECT * FROM employee ORDER BY sal;
The above query displays all the rows in the employee table in the
ascending order based on the column ename.
Descending order:
The keyword DESC is used to display rows in descending order
3. SELECT * FROM employee ORDER BY salary DESC;
Multiple ordering:
The table rows can be sorted using more than one column. It is known as
multiple ordering.
4. SELECT * FROM employee ORDER BY job, ename;
Group by:
The group by clause is used with select command to combine a group of rows
based on values of a column or expression. Aggregate functions are used to
return summary information for each group.
Example:
select deptno,sum(sal) from emp group by deptno;

It displays department wise, total salaries in ‘emp’ table.

Having:
Having clause is used to specify which groups are to be displayed . That is ,
restrict the groups that we want to return on the basis of aggregate functions.
Example:
Select deptno,max(sal) from emp group by deptno having max(sal) > 5000;
9: TRANSCATION CONTROL LANGUAGE(TCL):
Transaction Control Language(TCL) commands are used to
manage transactions in the database.
These are used to manage the changes made to the data in a
table by DML statements.
The following are the TCL commands -
1. Commit
2. Rollback
3. Savepoint

COMMIT:
This command is used to permanently save any transaction into the
database.
Syntax -COMMIT;

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

ROLLBACK:
● The ROLLBACK command to rollback those changes, if
they were not committed using the COMMIT command
Rollback;

● The command restores the database to last

committed state by using SAVEPOINT command.
ROLLBACK TO savepoint_name;

SAVEPOINT:
Using the SAVEPOINT command in SQL, we can save the different parts
of the same transaction using different names.

Syntax: savepoint savepointname;

EXAMPLE:

Consider the following STUDENT table to work with TCL commands :

STUDENT table is created using following fields –

 Htno
 Sname
 Pmarks
 doj
 deptid
htno is the primary key.
SQL> create table student(htno number(5) primary key,sname
varchar2(15),pmarks number(3),doj date,deptid number(2)) ;
Table created.
SQL> insert into student values(&htno,'&sname',&pmarks,'&doj',&deptid);
SQL> select * from student;

HTNO SNAME PMARKS DOJ DEPTID

---------- --------------- ---------- --------- -------------------------
1000 arvind 85 10-MAY-22 10
1001 arjun 95 15-JUN-22 20

SQL> savepoint x1;

Savepoint created.

SQL> delete from student where htno=1001;

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

1 row deleted.

SQL> select * from student;

HTNO SNAME PMARKS DOJ DEPTID

---------- --------------- ---------- --------- -------------------------
1000 arvind 85 10-MAY-22 10

SQL> rollback to x1;

Rollback complete.

SQL> select * from student;

HTNO SNAME PMARKS DOJ DEPTID

---------- --------------- ---------- --------- -----------------------------
1000 arvind 85 10-MAY-22 10
1001 arjun 95 15-JUN-22 20

SQL> select * from student;

HTNO SNAME PMARKS DOJ DEPTID

---------- --------------- ---------- --------- --------------------------------
1000 arvind 85 10-MAY-22 10
1001 arjun 95 15-JUN-22 20
1002 adil 89 20-JUN-22 10
1003 amar 80 05-MAY-22 20

SQL> commit;
SQL> update student set deptid=10 where htno=1003;
1 row updated.

SQL> select * from student;

HTNO SNAME PMARKS DOJ DEPTID

---------- --------------- ---------- --------- ----------------------------
1000 arvind 85 10-MAY-22 10
1001 arjun 95 15-JUN-22 20
1002 adil 89 20-JUN-22 10
1003 amar 80 05-MAY-22 10

SQL> rollback to x2;

Rollback complete.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

SQL> select * from student;

HTNO SNAME PMARKS DOJ DEPTID

---------- --------------- ---------- --------- ---------------------------------
1000 arvind 85 10-MAY-22 10
1001 arjun 95 15-JUN-22 20
1002 adil 89 20-JUN-22 10
1003 amar 80 05-MAY-22 20

10.DATA CONTROL LANGUAGE(DCL COMMANDS):

The administrator can implement DCL commands to add or remove database

permissions on a specific user that uses the database when required.

DCL commands are implemented to grant, revoke and deny permission to

retrieve or modify the data in the database.

DCL commands are used to ensure security and integrity of the data stored in the
data base.

DCL commands are primarily used to implement access control on the data
stored in the database. It is implemented along the DML (Data Manipulation
Language) and DDL (Data Definition Language) commands.

Types of DCL Commands in SQL:

Two types of DCL commands can be used by the user in SQL. These commands
are useful, especially when several users access the database. It enables the
administrator to manage access control.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

The two types of DCL commands are as follows:

1. GRANT
2. REVOKE

GRANT Command:

This command allows the administrator to provide particular privileges or

permissions over a database object, such as a table, view, or procedure. It can
provide user access to perform certain database or component operations.

The GRANT command allows the user to implement other SQL commands on
the database or its objects. The primary function of the GRANT command in SQL
is to provide administrators the ability to ensure the security and integrity of the
data is maintained in the database.

EXAMPLE:

Implementing GRANT Statement

Consider a student table is in the database. Suppose the Data Base

Administrator wants a specific user Amar to only SELECT (read)/ retrieve the
data from the student table.

Then we use GRANT command as given below -

GRANT SELECT ON student TO Amar;

This command will allow Amar to implement the SELECT queries on the student
table. This will enable the user to read or retrieve information from the student
table.

Implementing the above statement will also limit Amar's operations. Amar won't
be able to modify the data stored in the table. It will prevent the user from user to
insert, to update, or deleting the data in the student table in the database.
REVOKE Command:

The REVOKE command enables the database administrator to remove the

previously provided privileges or permissions from a user over a database or
database object, such as a table, view, or procedure. The REVOKE commands
prevent the user from accessing or performing a specific operation on an element
in the database.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

In simple language, the REVOKE command terminates the ability of the user to
perform the mentioned SQL command in the REVOKE query on the database or
its component. The primary reason for implementing the REVOKE query in the
database is to ensure the data's security and integrity.

EXAMPLE:

Implementing REVOKE Command

In the above implementation of the GRANT command, the user Amar was
provided permission to implement a SELECT query on the student table that
allowed Amar to read or retrieve the data from the table. Due to certain
circumstances, the administrator wants to revoke the above mentioned
permission.

To do so, the administrator can implement the below REVOKE statement -

REVOKE SELECT ON student FROM Amar;

Unit-V: PL/SQL
1. Introduction,
2. Structure of PL/SQL program
3. PL/SQL Data Types
4. PL/SQL Operators
5. Steps to create a PL/SQL Program
6. Conditional Control Statements
7. Iterative Control Statements

1.structure of the PL/SQL program:

Introduction: -
 PL/SQL is Oracle’s procedural language. It is the extension of SQL.
 PL/SQL stands for procedural Language/Structured Query language.
 By using PL/SQL user can pass set of SQL statements to oracle engine.

SQL
PL / SQL
Table Database User

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Advantages of PL/SQL:-

PL/SQL has several advantages –

1. PL/SQL is a structured programming language.
2. It is a modular programming language.
3. It supports all types of control statements
4. It supports Cursor programming
5. It supports data security by writing triggers
6. It supports user defined procedures, functions and packages
7. It supports logical error detection by using exception handling
8. PL/SQL also works like front end tool

2,Structure of PL/SQL Program:-

1. PL/SQL is specially designed for Database oriented activities.
2. It combines the data manipulation power of SQL and procedural power of
standard programming languages.
3. The PL/SQL programs are divided and written in logical blocks of code.
4. PL/SQL programs have mainly three blocks.
5. Every PL/SQL statement ends with a semicolon (;).

Following is the basic structure of a PL/SQL program –

Declare

Begin
Executable Section
Exception Section

End;

Declaration Block: -
Declare is a key word. In this section we declare PL/SQL variables, cursors and
local procedures.
Begin Block: -
In this block all the executable statements processing statements and control
statements will be used.
Exception Block: -
This is optional block. This block can be used when ever user wants to handle
the errors and its messages. This block starts with exception keyword.

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

End: -
PL/SQL program ends with a keyword called end.
Comment lines :
1) Single comment lines: ‘ –‘
2) Multiple comment lines: /*…………………….*/
Example
program: Program to find sum of two numbers.
Declare
a number;
b number;
sum number;
begin
a:=100;
b:=300;
sum:=a+b;
dbms_output.put-line(‘ The sum of a and b is ’ ||sum);
end;
Q: 3: Explain the PL/SQL data types.
PL/SQL data types:
Data type specifies the type of the data which can be stored in the specified
variable. PL/SQL supports all the data types of SQL. It supports some other data
types.
They are -

1. Boolean
2. %type
3. %rowtype
4. number
5. date
6. char
7. varchar2

1. Boolean:-
Boolean data type stores logical values. The Boolean data type can hold only
True or False. A Boolean data type doesn’t take any parameters.

Program:

Write a PL/SQL program to demonstrate the working of Boolean data type

declare
a number;
b number;
x boolean;

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

begin
a:=&a;
b:=&b;
x:=(a>b);
if (x) then
dbms_output.put_line('TRUE');
else
dbms_output.put_line('FALSE');
end if;
end;

INPUT:
Enter value for a: 200
Enter value for b: 100
OUTPUT:
TRUE

2. %type:-

This data type is used to store value of column in a table. Column is identified
by %type data type.
Syntax:
VaraibleName TableName.ColumnName%Type;

Example:
heno emp.empno%type
heno is the variable name,
emp is table name,
empno is a column
%Type is data type to hold the value.
3. %rowtype:-

The %rowtype data type is used to inherit the datatype from a record type that
presents a row in a table.
Syntax:
Variablename Tablename%rowtype;
Example:

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

e1 emp%rowtype;
Program:
declare
salary emp.sal%type;
no number;
begin
no:=&no;
select sal into salary from emp where empno=no;
if salary>3000 then
update emp set sal=sal+100 where empno=no;
else
update emp set sal=sal+200 where empno=no;
end if;
commit;
end;

Q: 4: Write about the Operators supported by PL/SQL.

PL/SQL OPERATORS:

An operator is a symbol that performs a mathematical or logical operation.

PL/SQL supports following types of operators −

 Arithmetic operators
 Relational operators
 Comparison operators
 Logical operators

Arithmetic Operators:
The arithmetic operators supported by PL/SQL are as follows –
Let us assume variable A holds 10 and variable B holds 5, then –

Operator Description Example

+ Addition A + B will give 15
- Subtraction A - B will give 5
* Multiplication A * B will give 50
/ Division A / B will give 2
** Exponentiation A ** B will give 100000
Relational Operators:

Relational operators compare two expressions or values and return a Boolean

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

result, that is, true or false.Following table shows all the relational operators
supported by PL/SQL.-

Operator Description
Checks if the values of two operands are equal or not, if yes then
=
condition becomes true.

Checks if the values of two operands are equal or not, if values are not
equal then condition becomes true.
<>

Checks if the value of left operand is greater than the value of right
>
operand, if yes then condition becomes true.
Checks if the value of left operand is less than the value of right
<
operand, if yes then condition becomes true.
Checks if the value of left operand is greater than or equal to the value
>=
of right operand, if yes then condition becomes true.
Checks if the value of left operand is less than or equal to the value of
<=
right operand, if yes then condition becomes true.
Comparison Operators:
Comparison operators are used for comparing one expression to another. The
result is always TRUE, FALSE or NULL.
 LIKE
 BETWEEN
 IN
 IS NULL
Logical Operators:
Logical operators work on Boolean operands and produce Boolean results.
Following table shows the Logical operators supported by PL/SQL.
Operator Description
Called the logical AND operator. If both the operands are true then
and
condition becomes true.
Called the logical OR Operator. If any of the two operands is true then
or
condition becomes true.
Called the logical NOT Operator. Used to reverse the logical state of
Not its operand. If a condition is true then Logical NOT operator will make
it false.

Q: 5: Write about Conditional control statements in PL/SQL.

Conditional control statements:

Sequence of statements can be executed based on some condition is called
conditional control statements.
There are 3 types of conditional control statements in PL/SQL-

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

1. Simple if
2. If…else
3. Elsif

1) Simple if :
If the condition is true, then the statement block will be executed.
Syntax:

if <condition> then
statements;
end if;
Example:
if(marks>=35) then
dbms_output.put_line(‘PASS’);
End if;
2) if then else:-

If the condition is true statement1 is executed, otherwise statement2 will be

executed.
Syntax:
if <condition> then
statement1;
else
statement2;
end if;

Program :
Write a PL/SQL program to find Largest of two numbers.

Declare
a number;
b number;

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

begin
a:=&a;
b:=&b;

if a>b then
dbms_output.put_line(‘a is big’);
else
dbms_output.put_line(‘b is big’);
end if;
end;

2) if then elsif:
If the condition1 is “true”, the statement block-1 will be executed. When the
conditition1 is false , then condition2 is tested. if the condition2 is “true”, the
statement block-2 will be executed.
If all conditions are false then the statements belongs to the else part will be
executed.

Syntax:
if <condition1> then
statement block1;
elsif<condition2> then
statement block2;
----------
----------
else
statement n;

end if;
Program:

Write a PL/SQL program to find Largest of three numbers.

Declare
a number;
b number;
c number;

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

begin

a:=&a;
b:=&b;
c:=&c;

if a>b and a>c then

dbms_output.put_line(‘a is big’);
elsif b>c then
dbms_output.put_line(‘b is big’);
else
dbms_output.put_line(‘c is big’);
end if;
end;

Q: 6 : Explain the Iterative control statements in PL/SQL.

Iterative control statements:-

The sequence of statements can be executed any number of times using looping
statements.
The different types of loops in pl/sql are –

1) simple loop
2) while loop
3) for loop

Simple loop:-
Simple loop is a unconditional iterative statement. To terminate this looping
statement we must use ‘exit’ or exit when statements.
Syntax:

Loop
Statements
Exit when <condition>;
End loop;

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Program :

Declare
i number;
begin
i:=1;
loop
dbms_output.put_line(i);
i:=i+1;
exit when(i>10);
End loop;
end;

While loop:-
While loop is a entry controlled loop which is used to check the condition and
executes the block of statements repeatedly until the condition is true.
Syntax :
while <condition>
Loop
Statements
End loop;

Program :
Write a PL/SQL program to print numbers upto 100.
Declare
i integer;
begin
i:=1;
while (i<=100)
loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;

For loop:-
The no of iterations for a while loop is un known until the loop terminates. The no
of iterations in a for loop is known before the loop gets executed.

Syntax:
for variable name in lower limit……..upper limit
Loop

RAO’S DEGREE COLLEGE

BCOM 4 SEMESTER DBMS WITH ORACLE

Statements
End loop;
Program :
Write a PL/SQL program to generate mathematical table.
Declare
n number(2);
begin
n:=&n;

for i in 1..10

loop

dbms_output.put_line( n || ‘*’ || i || ‘=’ || n*i);

end loop;
end;

RAO’S DEGREE COLLEGE