RELATIONAL DATABASE MANAGEMENT SYSTEM

UNIT-I
Data
Data is nothing but facts and statistics stored or free flowing over a
network, generally it's raw and unprocessed. For example: When you
visit any website, they might store you IP address, that is data, in
return they might add a cookie in your browser, marking you that you
visited the website, that is data, your name, it's data, your age, it's
data.

Data becomes information when it is processed, turning it into

something meaningful. Like, based on the cookie data saved on user's
browser, if a website can analyse that generally men of age 20-25 visit
us more, that is information, derived from the data collected.

Database
A Database is a collection of related data organised in a way that data
can be easily accessed, managed and updated. Database can be
software based or hardware based, with one sole purpose, storing
data.

During early computer days, data was collected and stored on tapes,
which were mostly write-only, which means once data is stored on it, it
can never be read again. They were slow and bulky, and soon
computer scientists realised that they needed a better solution to this
problem.

Larry Ellison, the co-founder of Oracle was amongst the first few,
who realised the need for a software based Database Management
System.

DBMS

A DBMS is a software that allows creation, definition and manipulation

of database, allowing users to store, process and analyse data easily.
DBMS provides us with an interface or a tool, to perform various
operations like creating database, storing data in it, updating data,
creating tables in the database and a lot more.

DBMS also provides protection and security to the databases. It also

maintains data consistency in case of multiple users.

Here are some examples of popular DBMS used these days:

 MySql
 Oracle
 SQL Server
 IBM DB2
 PostgreSQL
 Amazon SimpleDB (cloud based) etc.

Characteristics of Database Management System

A database management system has following characteristics:

1. Data stored into Tables: Data is never directly stored into the
database. Data is stored into tables, created inside the database.
DBMS also allows to have relationships between tables which
makes the data more meaningful and connected. You can easily
understand what type of data is stored where by looking at all the
tables created in a database.
2. Reduced Redundancy: In the modern world hard drives are very
cheap, but earlier when hard drives were too expensive,
unnecessary repetition of data in database was a big problem. But
DBMS follows Normalisation which divides the data in such a
way that repetition is minimum.
3. Data Consistency: On Live data, i.e. data that is being
continuosly updated and added, maintaining the consistency of
data can become a challenge. But DBMS handles it all by itself.
4. Support Multiple user and Concurrent Access: DBMS allows
multiple users to work on it(update, insert, delete data) at the
same time and still manages to maintain the data consistency.
5. Query Language: DBMS provides users with a simple Query
language, using which data can be easily fetched, inserted,
deleted and updated in a database.
 6. Security: The DBMS also takes care of the security of data,
protecting the data from un-authorised access. In a typical DBMS,
we can create user accounts with different access permissions,
using which we can easily secure our data by restricting user
access.
7. DBMS supports transactions, which allows us to better handle
and manage data integrity in real world applications where multi-
threading is extensively used.

Advantages of DBMS

 Segregation of applicaion program.

 Minimal data duplicacy or data redundancy.
 Easy retrieval of data using the Query Language.
 Reduced development time and maintainance need.
 With Cloud Datacenters, we now have Database Management
Systems capable of storing almost infinite data.
 Seamless integration into the application programming languages
which makes it very easier to add a database to almost any
application or website.

Disadvantages of DBMS

 It's Complexity
 Except MySQL, which is open source, licensed DBMSs are
generally costly.
 They are large in size.

Components of DBMS
The database management system can be divided into five major
components, they are:

1. Hardware
 2. Software
3. Data
4. Procedures
5. Database Access Language
Let's have a simple diagram to see how they all fit together to form a
database management system.

DBMS Components: Hardware

When we say Hardware, we mean computer, hard disks, I/O channels

for data, and any other physical component involved before any data
is successfully stored into the memory.

When we run Oracle or MySQL on our personal computer, then our

computer's Hard Disk, our Keyboard using which we type in all the
commands, our computer's RAM, ROM all become a part of the DBMS
hardware.

DBMS Components: Software

This is the main component, as this is the program which controls

everything. The DBMS software is more like a wrapper around the
physical database, which provides us with an easy-to-use interface to
store, access and update data.

The DBMS software is capable of understanding the Database Access

Language and intrepret it into actual database commands to execute
them on the DB.

DBMS Components: Data

Data is that resource, for which DBMS was designed. The motive
behind the creation of DBMS was to store and utilise data.

In a typical Database, the user saved Data is present and meta

data is stored.

Metadata is data about the data. This is information stored by the

DBMS to better understand the data stored in it.
For example: When I store my Name in a database, the DBMS will
store when the name was stored in the database, what is the size of
the name, is it stored as related data to some other data, or is it
independent, all this information is metadata.

DBMS Components: Procedures

Procedures refer to general instructions to use a database

management system. This includes procedures to setup and install a
DBMS, To login and logout of DBMS software, to manage databases, to
take backups, generating reports etc.

DBMS Components: Database Access Language

Database Access Language is a simple language designed to write

commands to access, insert, update and delete data stored in any
database.

A user can write commands in the Database Access Language and

submit it to the DBMS for execution, which is then translated and
executed by the DBMS.

User can create new databases, tables, insert data, fetch stored data,
update data and delete the data using the access language.

Users

 Database Administrators: Database Administrator or DBA is

the one who manages the complete database management
system. DBA takes care of the security of the DBMS, it's
availability, managing the license keys, managing user accounts
and access etc.
 Application Programmer or Software Developer: This user
group is involved in developing and desiging the parts of DBMS.
 End User: These days all the modern applications, web or mobile,
store user data. How do you think they do it? Yes, applications are
programmed in such a way that they collect user data and store
the data on DBMS systems running on their server. End users are
the one who store, retrieve, update and delete data.

Understanding DBMS Architecture

A Database Management system is not always directly available for
users and applications to access and store data in it. A Database
Management system can be centralised(all the data stored at one
location), decentralised(multiple copies of database at different
locations) or hierarchical, depending upon its architecture.

1-tier DBMS architecture also exist, this is when the database is

directly available to the user for using it to store data. Generally such a
setup is used for local application development, where programmers
communicate directly with the database for quick response.

Database Architecture is logically of two types:

1. 2-tier DBMS architecture

2. 3-tier DBMS architecture

2-tier DBMS Architecture

2-tier DBMS architecture includes an Application layer between the

user and the DBMS, which is responsible to communicate the user's
request to the database management system and then send the
response from the DBMS to the user.

An application interface known as ODBC(Open Database Connectivity)

provides an API that allow client side program to call the DBMS. Most

DBMS vendors provide ODBC drivers for their DBMS.

Such an architecture provides the DBMS extra security as it is not

exposed to the End User directly. Also, security can be improved by
adding security and authentication checks in the Application layer too.
3-tier DBMS Architecture

3-tier DBMS architecture is the most commonly used architecture for

web applications.

It is an extension of the 2-tier architecture. In the 2-tier architecture,

we have an application layer which can be accessed programatically to
perform various operations on the DBMS. The application generally
understands the Database Access Language and processes end users
requests to the DBMS.

In 3-tier architecture, an additional Presentation or GUI Layer is added,

which provides a graphical user interface for the End user to interact
with the DBMS.

For the end user, the GUI layer is the Database System, and the end
user has no idea about the application layer and the DBMS system.

If you have used MySQL, then you must have seen PHPMyAdmin, it
is the best example of a 3-tier DBMS architecture.
Types of Database Model
A Database model defines the logical design and structure of a database. It defines how
data will be stored, accessed, and updated in a database management system.

 As per your application's requirement, you can use a database model to define
your database.

 The database model sets the rule, relationships, constraints, etc. to define how
data is stored in the database.

 It's like creating a blueprint of your Database.

 There are different types of Database models and each one has its own set of
features.

 You can define how you want to structure the application data using a database
model.

In this tutorial you will learn about the 7 database model that are popularly used.

Type of Database models

There are several different Database model types, some of them are old, while some of
them are new, to cater to the new age requirements. Here is a list of the 7 popular
Database models:

1. Hierarchical Model

2. Network Model

3. Entity-relationship Model

4. Relational Model

5. Object-oriented Model

6. NoSQL Model

7. Graph Model

Let's learn about the different types of database models along with their main features
and when should you use them.

1. Hierarchical Model
 The hierarchical database model organizes data into a tree-like structure, with
a single root, to which all the other data is linked.

 The hierarchy starts from the Root data, and expands like a tree,
adding child nodes to the parent nodes.

 In this model, a child node will only have a single parent node.
  This model efficiently describes many real-world relationships like the index of a
book, etc.

 IBM's Information Management System (IMS) is based on this model.

 Data is organized into a tree-like structure with a one-to-many

relationship between two different types of data, for example,
one department can have many courses, many teachers, and of course
many students(like shown in the diagram below).

Advantages/Disadvantages of the Hierarchical Model

Here are a few points to mark the advantages and disadvantages of the Hierarchical
database model:

1. Because it has one-to-many relationships between different types of data so it is

easier and fast to fetch the data.

2. But the Hierarchical model is less flexible.

3. And it doesn't support many-to-many relationships.

2. Network Model
 The Network Model is an extension of the Hierarchical model.

 In this model, data is organized more like a graph, and allowed to have more than
one parent node.

 In the network database model, data is more related as more relationships are
established in this database model.

 Also, as the data is more related, hence accessing the data is also
easier and fast.

 This database model uses many-to-many data relationships.

 Integrated Data Store (IDS) is based on this database model.

 This was the most widely used database model before Relational Model was
introduced.

 The implementation of the Network model is complex, and it's very difficult to
maintain it.

 The Network model is difficult to modify also.

 You may want to explore this if you are developing some social networking
applications, although the Graph Database model is new and is far better than the
Network Database model.
Advantages of the Network Model

1. It supports complex relationships

2. It allows more flexibility

3. Entity-relationship Model
 In this database model, relationships are created by dividing objects of interest
into entities and their characteristics into attributes.

 Different entities are related using relationships.

 ER Models are defined to represent the relationships in pictorial form to make it

easier for different stakeholders to understand.

 This model is good to design a database, which can then be turned into tables in a
relational model (explained below).

 Let's take an example, If we have to design a School Database, then

the Student will be an entity with attributes name, age, address, etc. As
an Address is generally complex, it can be
another entity with attributes street, pincode, city, etc, and there will be a
relationship between them.

 Relationships can also be of different types. You can learn about ER Diagrams in
detail if you want to learn about entities and relationships.

Advantages of the ER Model

1. It is easy to understand and design.

2. Using the ER model we can represent data structures easily.

3. As the ER model cannot be directly implemented into a database model, it is just a

step toward designing the relational database model.

4. Relational Model
 In this model, data is organized in two-dimensional tables and the relationship is
maintained by storing a common field.

 This model was introduced by E.F Codd in 1970, and since then it has been the
most widely used database model.

 The basic structure of data in the relational model is tables. All the information
related to a particular type is stored in rows of that table.

 Hence, tables are also known as relations in the relational model.

 You can design tables, normalize them to reduce data redundancy,

and use Structured Query language or SQL to access data from the tables.
  Some of the most popular databases are based on this database model. For
example, Oracle, MySQL, etc.

Advantages of the Relational Model

1. It's simple and easy to implement.

2. Poplar database software is available for this database model.

3. It supports SQL using which you can easily query the data.

5. Object-oriented Model
 In this model, data is stored in the form of objects.

 The behavior of the object-oriented database model is just like object-oriented

programming.

 A very popular example of an Object Database management system

or ODBMS is MongoDB which is also a NoSQL database.

 This database model is not mature enough as compared to the relational database
model.

Advantages of the Object-oriented Model

1. It can easily support complex data structures, with relationships.

2. It also supports features like Inheritance, Encapsulation, etc.

6. NoSQL Model
 The NoSQL database model supports an unstructured style of storing data.

 Data is stored as documents.

 The documents look more like JSON strings or Key-value based object
representations.

 It provides a flexible schema.

 It does provide features like indexing, relationships between data, etc.

 The support for data querying is limited in the NoSQL database model.

 This database model is well-suited for Big data applications, real-time analytics,
CMS (Content Management systems), etc.
 Advantages of the NoSQL Model

1. This database model is scalable.

2. This database model functions with high performance.

3. The NoSQL database model can handle large volumes of data.

7. Graph Model
 The Graph database model is based on more real-world like relationships.

 Data is represented using Nodes or entities.

 The nodes are related using edges.

 The popular database Neo4j is based on the Graph database model.

 If your application has simple data requirements, then you should not use the
graph database model.

 In modern applications like social networks, recommendation systems, etc. the

graph database model is well-suited.

Advantages of the Graph Model

1.It handles complex relationships very well.

1. In the modern world where there is so much data and the data has to be related in
different ways, the graph database model is very useful.

System Development Life Cycle





The System Development Life Cycle (SDLC) provides a well-structured

framework that gives an idea, of how to build a system. It consists of steps as
follows - Plan, Analyze, Design, Develop, Test, Implement and Maintain. In this
article, we will see all the stages of system development.
System Development Life Cycle
Table of Content
 Stages (Phases) of System Development Life Cycle
o Planning
o Analysis
o Design
o Development
o Testing
o Implementation
o Maintenance
 How is System Development Life Cycle different from System Design Life
Cycle?
 Significance of System Design in System Development Life Cycle
We will delve into the significance of each stage, emphasizing the critical role
played by System Design in the overall process.
Stages (Phases) of System Development Life Cycle
The System Development Life Cycle (SDLC) consists of several interconnected
phases that provide a structured framework for developing a system. These
phases include Planning, Analysis, Design, Development, Testing,
Implementation, and Maintenance. Each phase plays a vital role in ensuring
the system is successfully developed, with System Design being especially
critical in shaping the final product.
Stage 1: Planning
The Planning phase sets the foundation for the entire SDLC. This stage
involves identifying the system's objectives, defining the scope, setting
timelines, and allocating necessary resources. Effective planning ensures that
the development process aligns with the organization's goals, guiding the
project in a clear and structured direction.
Stage 2: Analysis
In the Analysis phase, the focus is on understanding and documenting the
system’s requirements. This involves gathering input from stakeholders,
reviewing current processes, and identifying the system’s needs. The data
collected forms the basis for developing a system that addresses both user
expectations and organizational challenges.
Stage 3: Design
The Design phase translates the requirements gathered during Analysis into a
detailed technical blueprint. This includes designing the system’s architecture,
database models, user interfaces, and defining system components. The
outcome of this phase provides the technical structure needed to guide the
upcoming development and implementation activities.
Stage 4: Development
In this phase, the actual coding and development of the system take place.
Developers build the system according to the design specifications,
implementing features, creating databases, and writing code. This phase also
includes initial internal testing to ensure the system functions as expected and
adheres to design and functional requirements.
Stage 5: Testing
Testing is a crucial phase that ensures the system is free of errors and
functions correctly under various conditions. This phase includes multiple
types of testing, such as unit testing, integration testing, system testing, and
user acceptance testing. The goal is to identify and fix any issues before the
system is deployed.
Stage 6: Implementation
The Implementation phase involves deploying the developed system into a live
environment. Key activities include system installation, migrating data,
training users, and configuring infrastructure. This phase requires thorough
planning to ensure a smooth transition from the existing system to the new
one with minimal disruptions.
Stage 7: Maintenance
Maintenance is an ongoing phase where the system is monitored, maintained,
and updated as needed. This includes bug fixes, performance enhancements,
security patches, and responding to user feedback. Proper maintenance
ensures the system remains efficient, secure, and adaptable to future business
needs.

Basic Concepts of ER Model in DBMS

As we described in the tutorial Database models, Entity-relationship
model is a model used for design and representation of relationships
between data.

The main data objects are termed as Entities, with their details defined
as attributes, some of these attributes are important and are used to
identity the entity, and different entities are related using
relationships.

In short, to understand about the ER Model, we must understand

about:

 Entity and Entity Set

 What are Attributes? And Types of Attributes.
 Keys
  Relationships
Let's take an example to explain everything. For a School
Management Software, we will have to
store Student information, Teacher information, Classes, Subjects t
aught in each class etc.

ER Model: Entity and Entity Set

Considering the above example, Student is an entity, Teacher is an

entity, similarly, Class, Subject etc are also entities.

An Entity is generally a real-world object which has characteristics and

holds relationships in a DBMS.

If a Student is an Entity, then the complete dataset of all the students

will be the Entity Set

ER Model: Attributes

If a Student is an Entity, then student's roll no., student's name,

student's age, student's gender etc will be its attributes.

An attribute can be of many types, here are different types of

attributes defined in ER database model:

1. Simple attribute: The attributes with values that are atomic and
cannot be broken down further are simple attributes. For
example, student's age.
2. Composite attribute: A composite attribute is made up of more
than one simple attribute. For example, student's address will
contain, house no., street name, pincode etc.
3. Derived attribute: These are the attributes which are not
present in the whole database management system, but are
derived using other attributes. For example, average age of
students in a class.
4. Single-valued attribute: As the name suggests, they have a
single value.
5. Multi-valued attribute: And, they can have multiple values.
ER Model: Keys

If the attribute roll no. can uniquely identify a student entity, amongst
all the students, then the attribute roll no. will be said to be a key.

Following are the types of Keys:

1. Super Key
2. Candidate Key
3. Primary Key
We have covered Keys in details here in Database Keys tutorial.

ER Model: Relationships

When an Entity is related to another Entity, they are said to have a

relationship. For example, A Class Entity is related to Student entity,
becasue students study in classes, hence this is a relationship.

Depending upon the number of entities involved, a degree is assigned

to relationships.

For example, if 2 entities are involved, it is said to be Binary

relationship, if 3 entities are involved, it is said to
be Ternary relationship, and so on.

Working with ER Diagrams

ER Diagram is a visual representation of data that describes how data
is related to each other. In ER Model, we disintegrate data into entities,
attributes and setup relationships between entities, all this can be
represented visually using the ER diagram.

For example, in the below diagram, anyone can see and understand
what the diagram wants to convey: Developer develops a website,
whereas a Visitor visits a website.

Components of ER Diagram
Entitiy, Attributes, Relationships etc form the components of ER
Diagram and there are defined symbols and shapes to represent each
one of them.

Let's see how we can represent these in our ER Diagram.

Entity
Simple rectangular box represents an Entity.

Relationships between Entities - Weak and Strong

Rhombus is used to setup relationships between two or more entities.
Attributes for any Entity
Ellipse is used to represent attributes of any entity. It is connected to
the entity.

Weak Entity

A weak Entity is represented using double rectangular boxes. It is

generally connected to another entity.

Key Attribute for any Entity

To represent a Key attribute, the attribute name inside the Ellipse is

underlined.

Derived Attribute for any Entity

Derived attributes are those which are derived based on other

attributes, for example, age can be derived from date of birth.

To represent a derived attribute, another dotted ellipse is created

inside the main ellipse.

Multivalued Attribute for any Entity

Double Ellipse, one inside another, represents the attribute which can
have multiple values.

Composite Attribute for any Entity

A composite attribute is the attribute, which also has attributes.

ER Diagram: Entity

An Entity can be any object, place, person or class. In ER Diagram,

an entity is represented using rectangles. Consider an example of an
Organisation- Employee, Manager, Department, Product and many
more can be taken as entities in an Organisation.

The yellow rhombus in between represents a relationship.

ER Diagram: Weak Entity

Weak entity is an entity that depends on another entity. Weak entity

doesn't have anay key attribute of its own. Double rectangle is used to
represent a weak entity.
ER Diagram: Attribute

An Attribute describes a property or characterstic of an entity. For

example, Name, Age, Address etc can be attributes of a Student.
An attribute is represented using eclipse.

ER Diagram: Key Attribute

Key attribute represents the main characterstic of an Entity. It is used

to represent a Primary key. Ellipse with the text underlined, represents
Key Attribute.
ER Diagram: Composite Attribute

An attribute can also have their own attributes. These attributes are
known as Composite attributes.

ER Diagram: Relationship

A Relationship describes relation between entities. Relationship is

represented using diamonds or rhombus.
There are three types of relationship that exist between Entities.

1. Binary Relationship
2. Recursive Relationship
3. Ternary Relationship

ER Diagram: Binary Relationship

Binary Relationship means relation between two Entities. This is

further divided into three types.

One to One Relationship

This type of relationship is rarely seen in real world.

The above example describes that one student can enroll only for one
course and a course will also have only one Student. This is not what
you will usually see in real-world relationships.

One to Many Relationship

The below example showcases this relationship, which means that 1
student can opt for many courses, but a course can only have 1
student. Sounds weird! This is how it is.
Many to One Relationship
It reflects business rule that many entities can be associated with just
one entity. For example, Student enrolls for only one Course but a
Course can have many Students.

Many to Many Relationship

The above diagram represents that one student can enroll for more
than one courses. And a course can have more than 1 student enrolled
in it.

ER Diagram: Recursive Relationship

When an Entity is related with itself it is known

as Recursive Relationship.
ER Diagram: Ternary Relationship

Relationship of degree three is called Ternary relationship.

A Ternary relationship involves three entities. In such relationships we

always consider two entites together and then look upon the third.
For example, in the diagram above, we have three related
entities, Company, Product and Sector. To understand the
relationship better or to define rules around the model, we should
relate two entities and then derive the third one.

A Company produces many Products/ each product is produced by

exactly one company.

A Company operates in only one Sector / each sector has many

companies operating in it.

Considering the above two rules or relationships, we see that although

the complete relationship involves three entities, but we are looking at
two entities at a time.

*************************************unit-1 completed*******************************

UNIT-2
Relational database model:
The Relational Model organizes data using tables (relations) consisting of rows and
columns. Each column represents a specific attribute with a unique name, while each
row holds data about a real-world entity or relationship. As a record-based model, it
stores data in fixed-format records with defined attributes.
Introduced by E.F. Codd, the relational model transforms conceptual designs from ER
diagrams into implementable structures. These structures are used in relational
database systems like Oracle SQL and MySQL.
What is the Relational Model?
The relational model represents how data is stored and managed in
Relational Databases. Data is organized into tables, each known as a relation,
consisting of rows (tuples) and columns (attributes).
Each row represents an entity or record, and each column represents a
particular attribute of that entity. A relational database consists of a collection of tables
each of which is assigned a unique name.

For example, consider a relation STUDENT with

attributes ROLL_NO, NAME, ADDRESS, PHONE, and AGE shown in the table.
Key Terms in the Relational Model
1. Attribute: Attributes are the properties that define an entity.
 Example: ROLL_NO, NAME, ADDRESS etc.
2. Relation Schema: A relation schema defines the structure of the relation and represents
the name of the relation with its attributes.
 Example: STUDENT (ROLL_NO, NAME, ADDRESS, PHONE, and AGE) is the
relation schema for STUDENT. If a schema has more than 1 relation it is called
Relational Schema.
3. Tuple: A Tuple represents a row in a relation. Each tuple contains a set of attribute
values that describe a particular entity.
 Example: (1, RAM, DELHI, 9455123451, 18) is a tuple in the STUDENT table.
4. Relation Instance: The set of tuples of a relation at a particular instance of time is called
a relation instance. 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.
 Example: The STUDENT relation has a degree of 5, as it has 5 attributes.
6. Cardinality: The number of tuples in a relation is known as cardinality.
 Example: The STUDENT relation defined above has cardinality 4.
7. Column: The column represents the set of values for a particular attribute.
 Example: The column ROLL_NO is extracted from the relation STUDENT.
8. NULL Values: The value which is not known or unavailable is called a NULL value. It is
represented by NULL.
 Example: PHONE of STUDENT having ROLL_NO 4 is NULL.
Types of Keys in the Relational Model

1. Primary Key:
 A Primary Key uniquely identifies each tuple in a relation. It must contain unique
values and cannot have NULL values. Example: ROLL_NO in the STUDENT table is the primary
key.
2. Candidate Key
A Candidate Key is a set of attributes that can uniquely identify a tuple in a relation.
There can be multiple candidate keys, and one of them is chosen as the primary key.
3. Super Key
A Super Key is a set of attributes that can uniquely identify a tuple. It may contain
extra attributes that are not necessary for uniqueness.
4. Foreign Key
A Foreign Key is an attribute in one relation that refers to the primary key of another
relation. It establishes relationships between tables. Example: BRANCH_CODE in
the STUDENT table is a foreign key that refers to the primary key BRANCH_CODE in
the BRANCH table.
5. Composite Key
A Composite Key is formed by combining two or more attributes to uniquely identify a
tuple. Example: A combination of FIRST_NAME and LAST_NAME could be a composite key if
no one in the database shares the same full name.

Introduction to Database Keys

Keys are very important part of Relational database model. They are
used to establish and identify relationships between tables and also to
uniquely identify any record or row of data inside a table.

A Key can be a single attribute or a group of attributes, where the

combination may act as a key.

The video below covers all about the different keys in an RDBMS.

Why we need a Key?

In real world applications, number of tables required for storing the

data is huge, and the different tables are related to each other as well.

Also, tables store a lot of data in them. Tables generally extends to

thousands of records stored in them, unsorted and unorganised.

Now to fetch any particular record from such dataset, you will have to
apply some conditions, but what if there is duplicate data present and
every time you try to fetch some data by applying certain condition,
you get the wrong data. How many trials before you get the right data?

To avoid all this, Keys are defined to easily identify any row of data in
a table.

Let's try to understand about all the keys using a simple example.
 student_id name phone age

1 Akon 9876723452 17

2 Akon 9991165674 19

3 Bkon 7898756543 18

4 Ckon 8987867898 19

5 Dkon 9990080080 17

Let's take a simple Student table, with

fields student_id, name, phone and age.

Super Key

Super Key is defined as a set of attributes within a table that can

uniquely identify each record within a table. Super Key is a superset of
Candidate key.

In the table defined above super key would

include student_id, (student_id, name), phone etc.

Confused? The first one is pretty simple as student_id is unique for

every row of data, hence it can be used to identity each row uniquely.

Next comes, (student_id, name), now name of two students can be same,
but their student_id can't be same hence this combination can also be a
key.

Similarly, phone number for every student will be unique, hence

again, phone can also be a key.

So they all are super keys.

Candidate Key

Candidate keys are defined as the minimal set of fields which can
uniquely identify each record in a table. It is an attribute or a set of
attributes that can act as a Primary Key for a table to uniquely identify
each record in that table. There can be more than one candidate key.

In our example, student_id and phone both are candidate keys for
table Student.

 A candiate key can never be NULL or empty. And its value should
be unique.
 There can be more than one candidate keys for a table.
 A candidate key can be a combination of more than one
columns(attributes).

Primary Key

Primary key is a candidate key that is most appropriate to become the

main key for any table. It is a key that can uniquely identify each
record in a table.

For the table Student we can make the student_id column as the
primary key.
Composite Key

Key that consists of two or more attributes that uniquely identify any
record in a table is called Composite key. But the attributes which
together form the Composite key are not a key independentely or
individually.

In the above picture we have a Score table which stores the marks
scored by a student in a particular subject.

In this table student_id and subject_id together will form the primary key,
hence it is a composite key.

Secondary or Alternative key

The candidate key which are not selected as primary key are known as
secondary keys or alternative keys.
Non-key Attributes

Non-key attributes are the attributes or fields of a table, other

than candidate key attributes/fields in a table.

Non-prime Attributes

Non-prime Attributes are attributes other than Primary Key

attribute(s)..

What is Relational Algebra?

Every database management system must define a query language to
allow users to access the data stored in the database. Relational
Algebra is a procedural query language used to query the database
tables to access data in different ways.

In relational algebra, input is a relation(table from which data has to be

accessed) and output is also a relation(a temporary table holding the
data asked for by the user).

Relational Algebra works on the whole table at once, so we do not

have to use loops etc to iterate over all the rows(tuples) of data one by
one. All we have to do is specify the table name from which we need
the data, and in a single line of command, relational algebra will
traverse the entire given table to fetch data for you.
The primary operations that we can perform using relational algebra
are:

1. Select
2. Project
3. Union
4. Set Different
5. Cartesian product
6. Rename

Select Operation (σ)

This is used to fetch rows(tuples) from table(relation) which satisfies a

given condition.

Syntax: σ (r) p

Where, σ represents the Select Predicate, r is the name of

relation(table name in which you want to look for data), and p is the
prepositional logic, where we specify the conditions that must be
satisfied by the data. In prepositional logic, one can
use unary and binary operators like =, <, > etc, to specify the
conditions.

Let's take an example of the Student table we specified above in the

Introduction of relational algebra, and fetch data
for students with age more than 17.
σage > 17 (Student)

This will fetch the tuples(rows) from table Student, for which age will
be greater than 17.

You can also use, and, or etc operators, to specify two conditions, for
example,
σage > 17 and gender = 'Male' (Student)

This will return tuples(rows) from table Student with information of

male students, of age more than 17.(Consider the Student table has an
attribute Gender too.)
Project Operation (∏)

Project operation is used to project only a certain set of attributes of a

relation. In simple words, If you want to see only the names all of the
students in the Student table, then you can use Project Operation.

It will only project or show the columns or attributes asked for, and will
also remove duplicate data from the columns.

Syntax: ∏ A1, A2... (r)

where A1, A2 etc are attribute names(column names).

For example,
∏Name, Age(Student)

Above statement will show us only the Name and Age columns for all
the rows of data in Student table.

Union Operation (∪)

This operation is used to fetch data from two relations(tables) or

temporary relation(result of another operation).

For this operation to work, the relations(tables) specified should have

same number of attributes(columns) and same attribute domain. Also
the duplicate tuples are autamatically eliminated from the result.

Syntax: A ∪ B

where A and B are relations.

For example, if we have two tables RegularClass and ExtraClass,

both have a column student to save name of student, then,

∏Student(RegularClass) ∪ ∏Student(ExtraClass)

Above operation will give us name of Students who are attending

both regular classes and extra classes, eliminating repetition.
Set Difference (-)

This operation is used to find data present in one relation and not
present in the second relation. This operation is also applicable on two
relations, just like Union operation.

Syntax: A - B

where A and B are relations.

For example, if we want to find name of students who attend the

regular class but not the extra class, then, we can use the below
operation:
∏Student(RegularClass) - ∏Student(ExtraClass)

Cartesian Product (X)

This is used to combine data from two different relations(tables) into

one and fetch data from the combined relation.

Syntax: A X B

For example, if we want to find the information for Regular Class and
Extra Class which are conducted during morning, then, we can use the
following operation:
σtime = 'morning' (RegularClass X ExtraClass)

For the above query to work,

both RegularClass and ExtraClass should have the attribute time.

Rename Operation (ρ)

This operation is used to rename the output relation for any query
operation which returns result like Select, Project etc. Or to simply
rename a relation(table)

Syntax: ρ(RelationNew, RelationOld)

Apart from these common operations Relational Algebra is also used
for Join operations like,

 Natural Join
 Outer Join
 Theta join etc.

Normalization in DBMS
Normalization in DBMS is a technique using which you can organize the data in the
database tables so that:

 There is less repetition of data,

 A large set of data is structured into a bunch of smaller tables,

 and the tables have a proper relationship between them.

DBMS Normalization is a systematic approach to decompose (break down) tables to

eliminate data redundancy(repetition) and undesirable characteristics like Insertion
anomaly in DBMS, Update anomaly in DBMS, and Delete anomaly in DBMS.

It is a multi-step process that puts data into tabular form, removes duplicate data, and
set up the relationship between tables.

Why we need Normalization in DBMS?

Normalization is required for,

 Eliminating redundant(useless) data, therefore handling data integrity, because

if data is repeated it increases the chances of inconsistent data.

 Normalization helps in keeping data consistent by storing the data in one table
and referencing it everywhere else.

 Storage optimization although that is not an issue these days because Database
storage is cheap.

 Breaking down large tables into smaller tables with relationships, so it makes the
database structure more scalable and adaptable.

 Ensuring data dependencies make sense i.e. data is logically stored.

This video will give you a good overview of Database Normalization. If you want you can skip the video, as
the concept is covered in this tutorial as well - Normalization in DBMS (YouTube Video).

Problems without Normalization in DBMS

If a table is not properly normalized and has data redundancy(repetition) then it will not
only eat up extra memory space but will also make it difficult for you to handle and
update the data in the database, without losing data.

Insertion, Updation, and Deletion Anomalies are very frequent if the database is not
normalized.

To understand these anomalies let us take an example of a Student table.

In the
table rollno name branch hod office_tel

401 Akon CSE Mr. X 53337

402 Bkon CSE Mr. X 53337

403 Ckon CSE Mr. X 53337

404 Dkon CSE Mr. X 53337

above, we have data for four Computer Sci. students.

As we can see, data for the fields branch, hod(Head of Department), and office_tel are
repeated for the students who are in the same branch in the college, this is Data
Redundancy.

1. Insertion Anomaly in DBMS

 Suppose for a new admission, until and unless a student opts for a branch, data of
the student cannot be inserted, or else we will have to set the branch information
as NULL.

 Also, if we have to insert data for 100 students of the same branch, then the
branch information will be repeated for all those 100 students.

 These scenarios are nothing but Insertion anomalies.

 If you have to repeat the same data in every row of data, it's better to keep the
data separately and reference that data in each row.

 So in the above table, we can keep the branch information separately, and just use
the branch_id in the student table, where branch_id can be used to get the
branch information.
2. Updation Anomaly in DBMS

 What if Mr. X leaves the college? or Mr. X is no longer the HOD of the computer
science department? In that case, all the student records will have to be updated,
and if by mistake we miss any record, it will lead to data inconsistency.

 This is an Updation anomaly because you need to update all the records in your
table just because one piece of information got changed.

3. Deletion Anomaly in DBMS

 In our Student table, two different pieces of information are kept together,
the Student information and the Branch information.

 So if only a single student is enrolled in a branch, and that student leaves the
college, or for some reason, the entry for the student is deleted, we will lose the
branch information too.

 So never in DBMS, we should keep two different entities together, which in the
above example is Student and branch,

The solution for all the three anomalies described above is to keep the student information and
the branch information in two different tables. And use the branch_id in the student table to reference
the branch.

Primary Key and Non-key attributes

Before we move on to learn different Normal Forms in DBMS, let's first understand what
is a primary key and what are non-key attributes.

As you can see in the table above, the student_id column is a primary key because
using the student_id value we can uniquely identify each row of data, hence
the remaining columns then become the non-key attributes.
Types of DBMS Normal forms
Normalization rules are divided into the following normal forms:

1. First Normal Form

2. Second Normal Form

3. Third Normal Form

4. BCNF

5. Fourth Normal Form

6. Fifth Normal Form

Let's cover all the Database Normal forms one by one with some basic examples to help
you understand the DBMS normal forms.

1. First Normal Form (1NF)

For a table to be in the First Normal Form, it should follow the following 4 rules:

1. It should only have single(atomic) valued attributes/columns.

2. Values stored in a column should be of the same domain.

3. All the columns in a table should have unique names.

4. And the order in which data is stored should not matter.

Watch this YouTube video to understand First Normal Form (if you like videos) - DBMS First Normal
Form 1NF with Example

Let's see an example.

If we have an Employee table in which we store the employee information along with
the empl oyee
skillset, the table
will look like this:
emp_id emp_name emp_mobile emp_skills
The above
table has 4
columns:
1 John Tick 9999957773 Python, JavaScript
 All the
columns
have
different
2 Darth Trader 8888853337 HTML, CSS, JavaScript names.

 All the
columns
hold
3 Rony Shark 7777720008 Java, Linux, C++ values of
the same
 type like emp_name has all the names, emp_mobile has all the contact
numbers, etc.

 The order in which we save data doesn't matter

 But the emp_skills column holds multiple comma-separated values, while as per
the First Normal form, each column should have a single value.

Hence the above table fails to pass the First Normal form.

So how do you fix the above table? There are two ways to do this:

1. Remove the emp_skills column from the Employee table and keep it in some
other table.

2. Or add multiple rows for the employee and each row is linked with one skill.

1. Create Separate tables for Employee and Employee Skills

So
the Em
plo ye
emp_id emp_name emp_mobile
e ta ble
will look
like

1 John Tick 9999957773

2 Darth Trader 8888853337

3 Rony Shark 7777720008

this,

And the new Employee_Skill table:

emp_id emp_skill

Python

JavaScript
 emp_id emp_skill

HTML

CSS

JavaScript

Java

Linux

C++

2. Add Multiple rows for Multiple skills

You can also simply add multiple rows to add multiple skills. This will lead to repetition of
the data, but that can be handled as you further Normalize your data using the
Second Normal form and the Third Normal form.

mp_id emp_name emp_mobile emp_skill

John Tick 9999957773 Python

John Tick 9999957773 JavaScript

Darth Trader 8888853337 HTML

mp_id emp_name emp_mobile emp_skill

Darth Trader 8888853337 CSS

Darth Trader 8888853337 JavaScript

Rony Shark 7777720008 Java

Rony Shark 7777720008 Linux

Rony Shark 7777720008 C++

If you want to learn about the First Normal Form in detail, check out DBMS First
Normal Form tutorial.

2. Second Normal Form (2NF)

For a table to be in the Second Normal Form,

1. It should be in the First Normal form.

2. And, it should not have Partial Dependency.

Watch this YouTube video to understand Second Normal Form (if you like videos) - DBMS Second
Normal Form 2NF with Example

Let's take an example to understand Partial dependency and the Second Normal Form.

What is Partial Dependency?

When a table has a primary key that is made up of two or more columns, then all the
columns(not included in the primary key) in that table should depend on the entire
primary key and not on a part of it. If any column(which is not in the primary key)
depends on a part of the primary key then we say we have Partial dependency in the
table.

Confused? Let's take an example.

If we have two tables Students and Subjects, to store student information and
information related to subjects.
 student_id student_name branch

1 Akon CSE

2 Bkon Mechanical

subject_id subject_name

1 C Language

2 DSA

3 Operating System

Student table:

Subject Table:

And we have another table Score to store the marks scored by students in any subject
like this,

student_id subject_id marks teacher_name

1 1 70 Miss. C

1 2 82 Mr. D

2 1 65 Mr. Op
 student_id subject_id marks teacher_name

Now in the above table, the primary key is student_id + subject_id, because both
these information are required to select any row of data.

But in the Score table, we have a column teacher_name, which depends on the subject
information or just the subject_id, so we should not keep that information in
the Score table.

The column teacher_name should be in the Subjects table. And then the entire system
will be Normalized as per the Second Normal Form.

Updated Subject table:

subject_id subject_name teacher_name

C Language Miss. C

DSA Mr. D

Operating System Mr. Op

Updated Score table:

To understand what is Partial Dependency and how you can normalize a table to 2nd
normal form, jump to the DBMS Second Normal Form tutorial.

3. Third Normal Form (3NF)

A table is said to be in the Third Normal Form when,

1. It satisfies the First Normal Form and the Second Normal form.

2. And, it doesn't have Transitive Dependency.

Watch this YouTube video to understand the Third Normal Form (if you like videos) - DBMS Third Normal
Form 3NF with Example
What is Transitive Dependency?
In a table we have
some column
that acts student_id subject_id marks exam_type total_marks as the
primary key and
other columns
depends on this
column. But what
1 1 70 Theory 100
if a column
that is not the
primary key
depends on
another column
1 2 82 Theory 100
that is also not a
primary key or
part of it? Then we
have Transitive
2 1 42 Practical 50

dependency in our table.

Let's take an example. We had the Score table in the Second Normal Form above. If we
have to store some extra information in it, like,

1. exam_type

2. total_marks

To store the type of exam and the total marks in the exam so that we can later calculate
the percentage of marks scored by each student.

The Score table will look like this,

 In the table above, the column exam_type depends on

both student_id and subject_id, because,

o a student can be in the CSE branch or the Mechanical branch,

o and based on that they may have different exam types for different subjects.

o The CSE students may have both Practical and Theory for Compiler Design,

o whereas Mechanical branch students may only have Theory exams for
Compiler Design.

 But the column total_marks just depends on the exam_type column. And
the exam_type column is not a part of the primary key. Because the primary key
is student_id + subject_id, hence we have a Transitive dependency here.

How to Transitive Dependency?

You can create a separate table for ExamType and use it in the Score table.

New ExamType table,

 exam_type_id exam_type total_marks duration

1 Practical 50 45

2 Theory 100 180

3 Workshop 150 300

We have created a new table ExamType and we have added more related information
in it like duration(duration of exam in mins.), and now we can use the exam_type_id in
the Score table.

Here is the DBMS Third Normal Form tutorial. But we suggest you first study the
second normal form and then head over to the third normal form.

4. Boyce-Codd Normal Form (BCNF)

 Boyce and Codd Normal Form is a higher version of the Third Normal Form.

 This form deals with a certain type of anomaly that is not handled by 3NF.

 A 3NF table that does not have multiple overlapping candidate keys is said to
be in BCNF.

 For a table to be in BCNF, the following conditions must be satisfied:

o R must be in the 3rd Normal Form

o and, for each functional dependency ( X → Y ), X should be a Super Key.

You can also watch our YouTube video to learn about BCNF - DBMS BCNF with Example

To learn about BCNF in detail with a very easy-to-understand example, head to

the Boye-Codd Normal Form tutorial.

5. Fourth Normal Form (4NF)

A table is said to be in the Fourth Normal Form when,

1. It is in the Boyce-Codd Normal Form.

2. And, it doesn't have Multi-Valued Dependency.

You can also watch our YouTube video to learn about Fourth Normal Form - DBMS Fourth Normal Form
4NF with Example

Here is the Fourth Normal Form tutorial. But we suggest you understand other normal
forms before you head over to the fourth normal form.

5. Fifth Normal Form (5NF)

 The fifth normal form is also called the PJNF - Project-Join Normal Form

 It is the most advanced level of Database Normalization.

 Using Fifth Normal Form you can fix Join dependency and reduce data
redundancy.

 It also helps in fixing Update anomalies in DBMS design.

*******************unit-2 completed****************************

UNIT-3
Introduction to SQL
Structure Query Language(SQL) is a database query language used for storing and managing data in Relational
DBMS. SQL was the first commercial language introduced for E.F Codd's Relational model of database.
Today almost all RDBMS(MySql, Oracle, Infomix, Sybase, MS Access) use SQL as the standard database
query language. SQL is used to perform all types of data operations in RDBMS.

SQL Command
SQL defines Command Description following ways to
manipulate data stored in an RDBMS.

DDL: Data Definition Language

create to create new table or database
This includes changes to the structure of the
table like creation of table, altering table,
deleting a table etc.All DDL commands are
auto-committed. That Alter for alteration means it saves all the
changes permanently in the database.

truncate delete data from table

Drop to drop a table

rename to rename a table

DML: Data Manipulation Language

DML commands are used for manipulating the data stored in the table and not the table itself.

DML commands are not auto-committed. It means changes are not permanent to database, they can be rolled
back.

Command Description

insert to insert a new row

update to update existing row

delete to delete a row

merge merging two rows or two tables

TCL: Transaction Control Language

These commands are to keep a check on other commands and their affect on the database. These commands can
annul changes made by other commands by rolling the data back to its original state. It can also make any
temporary change permanent.
 Command Description

commit to permanently save

rollback to undo change

savepoint to save temporarily

DCL: Data Control Language

Data control language are the commands to grant and take back authority from any database user.

Command Description

grant grant permission of right

revoke take back permission.

DQL: Data Query Language

Data query language is used to fetch data from tables based on conditions that we can easily apply.

Command Description

select retrieve records from one or more table

DDL
SQL: create command
create is a DDL SQL command used to create a table or a database in
relational database management system.

Creating a Database

To create a database in RDBMS, create command is used. Following is

the syntax,

CREATE DATABASE <DB_NAME>;

Copy

Example for creating Database

CREATE DATABASE Test;

Copy
The above command will create a database named Test, which will be
an empty schema without any table.

To create tables in this newly created database, we can again use

the create command.

Creating a Table

create command can also be used to create tables. Now when we

create a table, we have to specify the details of the columns of the
tables too. We can specify the names and datatypes of various
columns in the create command itself.

Following is the syntax,

CREATE TABLE <TABLE_NAME>

column_name1 datatype1,
 column_name2 datatype2,

column_name3 datatype3,

column_name4 datatype4

);

Copy
create table command will tell the database system to create a new
table with the given table name and column information.

Example for creating Table

CREATE TABLE Student(

student_id INT,

name VARCHAR(100),

age INT);

Copy
The above command will create a new table with name Student in the
current database with 3 columns, namely student_id, name and age.
Where the column student_id will only store integer, name will hold upto
100 characters and age will again store only integer value.

If you are currently not logged into your database in which you want to
create the table then you can also add the database name along with
table name, using a dot operator .

For example, if we have a database with name Test and we want to

create a table Student in it, then we can do so using the following
query:

CREATE TABLE Test.Student(

student_id INT,

name VARCHAR(100),

age INT);
Copy

Most commonly used datatypes for Table columns

Here we have listed some of the most commonly used datatypes used
for columns in tables.

Dataty
Use
pe

INT used for columns which will store integer values.

FLOAT used for columns which will store float values.

DOUBLE used for columns which will store float values.

VARCHA used for columns which will be used to store characters and
R integers, basically a string.

CHAR used for columns which will store char values(single character).

DATE used for columns which will store date values.

used for columns which will store text which is generally long in
length. For example, if you create a table for storing profile
TEXT
information of a social networking website, then for about
me section you can have a column of type TEXT.

SQL: ALTER command

alter command is used for altering the table structure, such as,
  to add a column to existing table
 to rename any existing column
 to change datatype of any column or to modify its size.
 to drop a column from the table.

ALTER Command: Add a new Column

Using ALTER command we can add a column to any existing table.

Following is the syntax,

ALTER TABLE table_name ADD(

column_name datatype);

Copy
Here is an Example for this,

ALTER TABLE student ADD(

address VARCHAR(200)

);

Copy
The above command will add a new column address to the
table student, which will hold data of type varchar which is nothing but
string, of length 200.

ALTER Command: Add multiple new Columns

Using ALTER command we can even add multiple new columns to any
existing table. Following is the syntax,

ALTER TABLE table_name ADD(

column_name1 datatype1,

column-name2 datatype2,
 column-name3 datatype3);

Copy
Here is an Example for this,

ALTER TABLE student ADD(

father_name VARCHAR(60),

mother_name VARCHAR(60),

dob DATE);

Copy
The above command will add three new columns to the student table

ALTER Command: Add Column with default value

ALTER command can add a new column to an existing table with a

default value too. The default value is used when no value is inserted
in the column. Following is the syntax,

ALTER TABLE table_name ADD(

column-name1 datatype1 DEFAULT some_value

);

Copy
Here is an Example for this,

ALTER TABLE student ADD(

dob DATE DEFAULT '01-Jan-99'

);

Copy
The above command will add a new column with a preset default value
to the table student.
ALTER Command: Modify an existing Column

ALTER command can also be used to modify data type of any existing
column. Following is the syntax,

ALTER TABLE table_name modify(

column_name datatype

);

Copy
Here is an Example for this,

ALTER TABLE student MODIFY(

address varchar(300));

Copy
Remember we added a new column address in the beginning? The
above command will modify the address column of the student table,
to now hold upto 300 characters.

ALTER Command: Rename a Column

Using ALTER command you can rename an existing column. Following

is the syntax,

ALTER TABLE table_name RENAME

old_column_name TO new_column_name;

Copy
Here is an example for this,

ALTER TABLE student RENAME

address TO location;

Copy
The above command will rename address column to location.
ALTER Command: Drop a Column

ALTER command can also be used to drop or remove columns.

Following is the syntax,

ALTER TABLE table_name DROP(

column_name);

Copy
Here is an example for this,

ALTER TABLE student DROP(

address);

Copy
The above command will drop the address column from the
table student.

SQL Truncate, Drop or Rename a Table

In this tutorial we will learn about the various DDL commands which
are used to re-define the tables.

TRUNCATE command

TRUNCATE command removes all the records from a table. But this
command will not destroy the table's structure. When we
use TRUNCATE command on a table its (auto-increment) primary key is
also initialized. Following is its syntax,

TRUNCATE TABLE table_name

Copy
Here is an example explaining it,

TRUNCATE TABLE student;

Copy
The above query will delete all the records from the table student.
In DML commands, we will study about the DELETE command which is
also more or less same as the TRUNCATE command. We will also learn
about the difference between the two in that tutorial.

DROP command

DROP command completely removes a table from the database. This

command will also destroy the table structure and the data stored in it.
Following is its syntax,

DROP TABLE table_name

Copy
Here is an example explaining it,

DROP TABLE student;

Copy
The above query will delete the Student table completely. It can also
be used on Databases, to delete the complete database. For example,
to drop a database,

DROP DATABASE Test;

Copy
The above query will drop the database with name Test from the
system.

RENAME query

RENAME command is used to set a new name for any existing table.
Following is the syntax,

RENAME TABLE old_table_name to new_table_name

Copy
Here is an example explaining it.

RENAME TABLE student to students_info;

Copy
The above query will rename the table student to students_info.

DML
Using INSERT SQL command
Data Manipulation Language (DML) statements are used for managing
data in database. DML commands are not auto-committed. It means
changes made by DML command are not permanent to database, it
can be rolled back.

Talking about the Insert command, whenever we post a Tweet on

Twitter, the text is stored in some table, and as we post a new tweet, a
new record gets inserted in that table.

INSERT command

Insert command is used to insert data into a table. Following is its

general syntax,

INSERT INTO table_name VALUES(data1, data2, ...)

Copy
Lets see an example,

Consider a table student with the following fields.

s_id name age

INSERT INTO student VALUES(101, 'Adam', 15);

Copy
The above command will insert a new record into student table.

s_id name age

101 Adam 15

Insert value into only specific columns

We can use the INSERT command to insert values for only some specific
columns of a row. We can specify the column names along with the
values to be inserted like this,

INSERT INTO student(id, name) values(102, 'Alex');

Copy
The above SQL query will only insert id and name values in the newly
inserted record.

Insert NULL value to a column

Both the statements below will insert NULL value into age column of
the student table.

INSERT INTO student(id, name) values(102, 'Alex');

Copy
Or,

INSERT INTO Student VALUES(102,'Alex', null);

Copy
The above command will insert only two column values and the other
column is set to null.

S_id S_Name age

101 Adam 15
102 Alex

Insert Default value to a column

INSERT INTO Student VALUES(103,'Chris', default)

Copy

S_id S_Name age

101 Adam 15

102 Alex

103 chris 14

Suppose the column age in our tabel has a default value of 14.

Also, if you run the below query, it will insert default value into the age
column, whatever the default value may be.

INSERT INTO Student VALUES(103,'Chris')

Using UPDATE SQL command

Let's take an example of a real-world problem. These days, Facebook
provides an option for Editing your status update, how do you think it
works? Yes, using the Update SQL command.

Let's learn about the syntax and usage of the UPDATE command.

UPDATE command
UPDATE command is used to update any record of data in a table.
Following is its general syntax,

UPDATE table_name SET column_name = new_value WHERE

some_condition;

Copy
WHERE is used to add a condition to any SQL query, we will soon study
about it in detail.
Lets take a sample table student,

student_id name age

101 Adam 15

102 Alex

103 chris 14

UPDATE student SET age=18 WHERE student_id=102;

Copy

S_id S_Name age

101 Adam 15

102 Alex 18

103 chris 14

In the above statement, if we do not use the WHERE clause, then our
update query will update age for all the columns of the table to 18.
Updating Multiple Columns

We can also update values of multiple columns using a

single UPDATE statement.

UPDATE student SET name='Abhi', age=17 where s_id=103;

Copy
The above command will update two columns of the record which
has s_id 103.

s_id name age

101 Adam 15

102 Alex 18

103 Abhi 17

UPDATE Command: Incrementing Integer Value

When we have to update any integer value in a table, then we can

fetch and update the value in the table in a single statement.

For example, if we have to update the age column of student table

every year for every student, then we can simply run the
following UPDATE statement to perform the following operation:

UPDATE student SET age = age+1;

Copy
As you can see, we have used age = age + 1 to increment the value of
age by 1.

NOTE: This style only works for integer values.

Using DELETE SQL command

When you ask any question in Studytonight's Forum it gets saved into
a table. And using the Delete option, you can even delete a question
asked by you. How do you think that works? Yes, using the Delete DML
command.

Let's study about the syntax and the usage of the Delete command.

DELETE command

DELETE command is used to delete data from a table.

Following is its general syntax,

DELETE FROM table_name;

Copy

Let's take a sample table student:

s_id name age

101 Adam 15

102 Alex 18

103 Abhi 17

Delete all Records from a Table

DELETE FROM student;

Copy

The above command will delete all the records from the
table student.
Delete a particular Record from a Table

In our student table if we want to delete a single record, we can use

the WHERE clause to provide a condition in our DELETE statement.

DELETE FROM student WHERE s_id=103;

Copy

The above command will delete the record where s_id is 103 from the
table student.

S_id S_Name age

101 Adam 15

102 Alex 18

Isn't DELETE same as TRUNCATE

TRUNCATE command is different from DELETE command. The delete

command will delete all the rows from a table whereas truncate
command not only deletes all the records stored in the table, but it
also re-initializes the table(like a newly created table).

For eg: If you have a table with 10 rows and

an auto_increment primary key, and if you use DELETE command to
delete all the rows, it will delete all the rows, but will not re-initialize
the primary key, hence if you will insert any row after using
the DELETE command, the auto_increment primary key will start from
11. But in case of TRUNCATE command, primary key is re-initialized, and
it will again start from 1.

Commit, Rollback and Savepoint SQL

commands
Transaction Control Language(TCL) commands are used to manage
transactions in the database.

Before moving forward with TCL commands, check these topics out
first:

 DML commands
 DDL COMMAND

These are used to manage the changes made to the data in a table by
DML statements. It also allows statements to be grouped together into
logical transactions.

COMMIT command

COMMIT command is used to permanently save any transaction into the

database.

When we use any DML command like INSERT, UPDATE or DELETE, the
changes made by these commands are not permanent, until the
current session is closed, the changes made by these commands can
be rolled back.

To avoid that, we use the COMMIT command to mark the changes as

permanent.

Following is commit command's syntax,

COMMIT;

Copy

ROLLBACK command

This command restores the database to last commited state. It is also

used with SAVEPOINT command to jump to a savepoint in an ongoing
transaction.

If we have used the UPDATE command to make some changes into the
database, and realise that those changes were not required, then we
can use the ROLLBACK command to rollback those changes, if they were
not commited using the COMMIT command.

Following is rollback command's syntax,

ROLLBACK TO savepoint_name;

Copy

SAVEPOINT command

SAVEPOINT command is used to temporarily save a transaction so that

you can rollback to that point whenever required.

Following is savepoint command's syntax,

SAVEPOINT savepoint_name;

Copy

In short, using this command we can name the different states of our
data in any table and then rollback to that state using
the ROLLBACK command whenever required.

Using Savepoint and Rollback

Following is the table class,

id name

1 Abhi

2 Adam

4 Alex
Lets use some SQL queries on the above table and see the results.

INSERT INTO class VALUES(5, 'Rahul');

COMMIT;

UPDATE class SET name = 'Abhijit' WHERE id = '5';

SAVEPOINT A;

INSERT INTO class VALUES(6, 'Chris');

SAVEPOINT B;

INSERT INTO class VALUES(7, 'Bravo');

SAVEPOINT C;

SELECT * FROM class;

Copy

NOTE: SELECT statement is used to show the data stored in the table.

The resultant table will look like,

id name
1 Abhi

2 Adam

4 Alex

5 Abhijit

6 Chris

7 Bravo

Now let's use the ROLLBACK command to roll back the state of data to
the savepoint B.

ROLLBACK TO B;

SELECT * FROM class;

Copy

Now our class table will look like,

id name

1 Abhi

2 Adam

4 Alex
5 Abhijit

6 Chris

Now let's again use the ROLLBACK command to roll back the state of
data to the savepoint A

ROLLBACK TO A;

SELECT * FROM class;

Copy

Now the table will look like,

id name

1 Abhi

2 Adam

4 Alex

5 Abhijit

So now you know how the

commands COMMIT, ROLLBACK and SAVEPOINT works.

Using GRANT and REVOKE

Data Control Language(DCL) is used to control privileges in Database.
To perform any operation in the database, such as for creating tables,
sequences or views, a user needs privileges. Privileges are of two
types,
  System: This includes permissions for creating session, table, etc
and all types of other system privileges.
 Object: This includes permissions for any command or query to
perform any operation on the database tables.
In DCL we have two commands,

 GRANT: Used to provide any user access privileges or other

priviliges for the database.
 REVOKE: Used to take back permissions from any user.

Allow a User to create session

When we create a user in SQL, it is not even allowed to login and

create a session until and unless proper permissions/priviliges are
granted to the user.

Following command can be used to grant the session creating

priviliges.

GRANT CREATE SESSION TO username;

Copy

Allow a User to create table

To allow a user to create tables in the database, we can use the below
command,

GRANT CREATE TABLE TO username;

Copy

Provide user with space on tablespace to store table

Allowing a user to create table is not enough to start storing data in

that table. We also must provide the user with priviliges to use the
available tablespace for their table and data.
ALTER USER username QUOTA UNLIMITED ON SYSTEM;

Copy
The above command will alter the user details and will provide it
access to unlimited tablespace on system.

NOTE: Generally unlimited quota is provided to Admin users.

Grant all privilege to a User

sysdba is a set of priviliges which has all the permissions in it. So if we

want to provide all the privileges to any user, we can simply grant
them the sysdba permission.

GRANT sysdba TO username

Copy

Grant permission to create any table

Sometimes user is restricted from creating come tables with names

which are reserved for system tables. But we can grant privileges to a
user to create any table using the below command,

GRANT CREATE ANY TABLE TO username

Copy

Grant permission to drop any table

As the title suggests, if you want to allow user to drop any table from
the database, then grant this privilege to the user,

GRANT DROP ANY TABLE TO username

Copy
To take back Permissions

And, if you want to take back the privileges from any user, use
the REVOKE command.

REVOKE CREATE TABLE FROM username

Copy

SELECT SQL Query

SELECT query is used to retrieve data from a table. It is the most used
SQL query. We can retrieve complete table data, or partial by
specifying conditions using the WHERE clause.

Syntax of SELECT query

SELECT query is used to retieve records from a table. We can specify

the names of the columns which we want in the resultset.

SELECT

column_name1,

column_name2,

column_name3,

...

column_nameN

FROM table_name;

Copy

Time for an Example

Consider the following student table,

 s_id name age address

101 Adam 15 Chennai

102 Alex 18 Delhi

103 Abhi 17 Banglore

104 Ankit 22 Mumbai

SELECT s_id, name, age FROM student;

Copy

The above query will fetch information of s_id, name and age columns of
the student table and display them,

s_id name age

101 Adam 15

102 Alex 18

103 Abhi 17

104 Ankit 22

As you can see the data from address column is absent, because we did
not specif it in our SELECT query.
Select all records from a table

A special character asterisk * is used to address all the

data(belonging to all columns) in a query. SELECT statement
uses * character to retrieve all records from a table, for all the
columns.

SELECT * FROM student;

Copy

The above query will show all the records of student table, that
means it will show complete dataset of the table.

s_id name age address

101 Adam 15 Chennai

102 Alex 18 Delhi

103 Abhi 17 Banglore

104 Ankit 22 Mumbai

Select a particular record based on a condition

We can use the WHERE clause to set a condition,

SELECT * FROM student WHERE name = 'Abhi';

Copy

The above query will return the following result,

103 Abhi 17 Rohtak

Performing Simple Calculations using SELECT Query

Consider the following employee table.

eid name age salary

101 Adam 26 5000

102 Ricky 42 8000

103 Abhi 25 10000

104 Rohan 22 5000

Here is our SELECT query,

SELECT eid, name, salary+3000 FROM employee;

Copy

The above command will display a new column in the result,

with 3000 added into existing salaries of the employees.

eid name salary+3000

101 Adam 8000

102 Ricky 11000

103 Abhi 13000

104 Rohan 8000

So you can also perform simple mathematical operations on the data

too using the SELECT query to fetch data from table.

Using the WHERE SQL clause

WHERE clause is used to specify/apply any condition while retrieving,
updating or deleting data from a table. This clause is used mostly
with SELECT, UPDATE and DELETEquery.

When we specify a condition using the WHERE clause then the query
executes only for those records for which the condition specified by
the WHERE clause is true.

Syntax for WHERE clause

Here is how you can use the WHERE clause with a DELETE statement, or
any other statement,

DELETE FROM table_name WHERE [condition];

Copy
The WHERE clause is used at the end of any SQL query, to specify a
condition for execution.

Time for an Example

Consider a table student,

s_id name age address

101 Adam 15 Chennai

102 Alex 18 Delhi

103 Abhi 17 Banglore

104 Ankit 22 Mumbai

Now we will use the SELECT statement to display data of the table,
based on a condition, which we will add to our SELECT query
using WHERE clause.

Let's write a simple SQL query to display the record for student
with s_id as 101.

SELECT s_id,

name,

age,

address

FROM student WHERE s_id = 101;

Copy
Following will be the result of the above query.

s_id name age address

101 Adam 15 Noida

Applying condition on Text Fields

In the above example we have applied a condition to an integer value
field, but what if we want to apply the condition on name field. In that
case we must enclose the value in single quote ' '. Some databases
even accept double quotes, but single quotes is accepted by all.

SELECT s_id,

name,

age,

address

FROM student WHERE name = 'Adam';

Copy
Following will be the result of the above query.

s_id name age address

101 Adam 15 Noida

Operators for WHERE clause condition

Following is a list of operators that can be used while specifying

the WHERE clause condition.

Operator Description

= Equal to

!= Not Equal to

< Less than

> Greater than

<= Less than or Equal to

>= Greate than or Equal to

BETWEEN Between a specified range of values

LIKE This is used to search for a pattern in value.

IN In a given set of values

SQL LIKE clause

LIKE clause is used in the condition in SQL query with
the WHERE clause. LIKE clause compares data with an expression using
wildcard operators to match pattern given in the condition.

Wildcard operators

There are two wildcard operators that are used in LIKE clause.

 Percent sign %: represents zero, one or more than one

character.
 Underscore sign _: represents only a single character.

Example of LIKE clause

Consider the following Student table.

 s_id s_Name age

101 Adam 15

102 Alex 18

103 Abhi 17

SELECT * FROM Student WHERE s_name LIKE 'A%';

Copy
The above query will return all records where s_name starts with
character 'A'.

s_id s_Name age

101 Adam 15

102 Alex 18

103 Abhi 17

Using _ and %

SELECT * FROM Student WHERE s_name LIKE '_d%';

Copy
The above query will return all records from Student table
where s_name contain 'd' as second character.
 s_id s_Name age

101 Adam 15

Using % only

SELECT * FROM Student WHERE s_name LIKE '%x';

Copy
The above query will return all records from Student table
where s_name contain 'x' as last character.

s_id s_Name age

102 Alex 18

SQL ORDER BY Clause

Order by clause is used with SELECT statement for arranging retrieved
data in sorted order. The Order by clause by default sorts the
retrieved data in ascending order. To sort the data in descending
order DESC keyword is used with Order by clause.

Syntax of Order By
SELECT column-list|* FROM table-name ORDER BY ASC | DESC;

Copy

Using default Order by

Consider the following Emp table,

 eid name age salary

401 Anu 22 9000

402 Shane 29 8000

403 Rohan 34 6000

404 Scott 44 10000

405 Tiger 35 8000

SELECT * FROM Emp ORDER BY salary;

Copy

The above query will return the resultant data in ascending order of
the salary.

eid name age salary

403 Rohan 34 6000

402 Shane 29 8000

405 Tiger 35 8000

401 Anu 22 9000

404 Scott 44 10000

Using Order by DESC

Consider the Emp table described above,

SELECT * FROM Emp ORDER BY salary DESC;

Copy

The above query will return the resultant data in descending order of
the salary.

eid name age salary

404 Scott 44 10000

401 Anu 22 9000

405 Tiger 35 8000

402 Shane 29 8000

403 Rohan 34 6000

Check out other DCL commands and their usage:

 SELECT query
 WHERE clause
 LIKE clause
 Group BY clause
 Having clause
  SQL Group By Clause
 Group by clause is used to group the results of a SELECT query
based on one or more columns. It is also used with SQL functions
to group the result from one or more tables.
 Syntax for using Group by in a statement.
 SELECT column_name, function(column_name)
 FROM table_name
 WHERE condition
 GROUP BY column_name
 Copy


 Example of Group by in a Statement

 Consider the following Emp table.

eid name age salary

401 Anu 22 9000

402 Shane 29 8000

403 Rohan 34 6000

404 Scott 44 9000

405 Tiger 35 8000

 Here we want to find name and age of employees grouped by

their salaries or in other words, we will be grouping employees
based on their salaries, hence, as a result, we will get a data set,
with unique salaries listed, along side the first employee's name
and age to have that salary. Hope you are getting the point here!
 group by is used to group different row of data together based on
any one column.
 SQL query for the above requirement will be,
 SELECT name, age
 FROM Emp GROUP BY salary
 Copy
  Result will be,

name age

Rohan 34

Shane 29

Anu 22


 Example of Group by in a Statement with WHERE clause
 Consider the following Emp table

eid name age salary

401 Anu 22 9000

402 Shane 29 8000

403 Rohan 34 6000

404 Scott 44 9000

405 Tiger 35 8000

 SQL query will be,

 SELECT name, salary
 FROM Emp
 WHERE age > 25
 GROUP BY salary
 Copy
  Result will be.

name salary

Rohan 6000

Shane 8000

Scott 9000

 You must remember that Group By clause will always come at the
end of the SQL query, just like the Order by clause.
 SQL HAVING Clause
 Having clause is used with SQL Queries to give more precise
condition for a statement. It is used to mention condition in Group
by based SQL queries, just like WHERE clause is used
with SELECT query.
 Syntax for HAVING clause is,
 SELECT column_name, function(column_name)
 FROM table_name
 WHERE column_name condition
 GROUP BY column_name
 HAVING function(column_name) condition
 Copy


 Example of SQL Statement using HAVING

 Consider the following Sale table.

oid order_name previous_balance customer

11 ord1 2000 Alex

12 ord2 1000 Adam

13 ord3 2000 Abhi

14 ord4 1000 Adam

15 ord5 2000 Alex

 Suppose we want to find

the customer whose previous_balance sum is more than 3000.
 We will use the below SQL query,
 SELECT *
 FROM sale GROUP BY customer
 HAVING sum(previous_balance) > 3000
 Copy
 Result will be,

oid order_name previous_balance customer

11 ord1 2000 Alex

 The main objective of the above SQL query was to find out the
name of the customer who has had a previous_balance more
than 3000, based on all the previous sales made to the customer,
hence we get the first row in the table for customer Alex.


 DISTINCT keyword
 The distinct keyword is used with SELECT statement to retrieve
unique values from the table. Distinct removes all the duplicate
records while retrieving records from any table in the database.


 Syntax for DISTINCT Keyword

 SELECT DISTINCT column-name FROM table-name;
 Copy


 Example using DISTINCT Keyword

  Consider the following Emp table. As you can see in the table
below, there is employee name, along with
employee salary and age.
 In the table below, multiple employees have the same salary, so
we will be using DISTINCT keyword to list down distinct salary
amount, that is currently being paid to the employees.

eid name age salary

401 Anu 22 5000

402 Shane 29 8000

403 Rohan 34 10000

404 Scott 44 10000

405 Tiger 35 8000

 SELECT DISTINCT salary FROM Emp;

 Copy
 The above query will return only the unique salary
from Emp table.

sala
ry

5000

8000

1000
0

SQL AND & OR operator

The AND and OR operators are used with the WHERE clause to make
more precise conditions for fetching data from database by combining
more than one condition together.

AND operator

AND operator is used to set multiple conditions with the WHERE clause,
alongside, SELECT, UPDATE or DELETE SQL queries.

Example of AND operator

Consider the following Emp table

eid name age salary

401 Anu 22 5000

402 Shane 29 8000

403 Rohan 34 12000

404 Scott 44 10000

405 Tiger 35 9000

SELECT * FROM Emp WHERE salary < 10000 AND age > 25
The above query will return records where salary is less
than 10000 and age greater than 25. Hope you get the concept here.
We have used the AND operator to specify two conditions
with WHERE clause.

eid name age salary

402 Shane 29 8000

405 Tiger 35 9000

OR operator

OR operator is also used to combine multiple conditions

with WHERE clause. The only difference between AND and OR is their
behaviour.

When we use AND to combine two or more than two conditions, records
satisfying all the specified conditions will be there in the result.

But in case of OR operator, atleast one condition from the conditions

specified must be satisfied by any record to be in the resultset.

Example of OR operator

Consider the following Emp table

eid name age salary

401 Anu 22 5000

402 Shane 29 8000

403 Rohan 34 12000

404 Scott 44 10000

405 Tiger 35 9000

SELECT * FROM Emp WHERE salary > 10000 OR age > 25

The above query will return records where either salary is greater
than 10000 or age is greater than 25.

402 Shane 29 8000

403 Rohan 34 12000

404 Scott 44 10000

405 Tiger 35 9000

SQL JOIN
SQL Join is used to fetch data from two or more tables, which is joined
to appear as single set of data. It is used for combining column from
two or more tables by using values common to both tables.

JOIN Keyword is used in SQL queries for joining two or more tables.
Minimum required condition for joining table, is (n-1) where n, is
number of tables. A table can also join to itself, which is known as, Self
Join.
Types of JOIN

Following are the types of JOIN that we can use in SQL:

 Inner
 Outer
 Left
 Right

Cross JOIN or Cartesian Product

This type of JOIN returns the cartesian product of rows from the tables
in Join. It will return a table which consists of records which combines
each row from the first table with each row of the second table.

Cross JOIN Syntax is,

SELECT column-name-list

FROM

table-name1 CROSS JOIN table-name2;

Copy

Example of Cross JOIN

Following is the class table,

ID NAME

1 abhi

2 adam
4 alex

and the class_info table,

ID Address

1 DELHI

2 MUMBAI

3 CHENNAI

Cross JOIN query will be,

SELECT * FROM

class CROSS JOIN class_info;

Copy
The resultset table will look like,

ID NAME ID Address

1 abhi 1 DELHI

2 adam 1 DELHI

4 alex 1 DELHI

1 abhi 2 MUMBAI

2 adam 2 MUMBAI
4 alex 2 MUMBAI

1 abhi 3 CHENNAI

2 adam 3 CHENNAI

4 alex 3 CHENNAI

As you can see, this join returns the cross product of all the records
present in both the tables.

INNER Join or EQUI Join

This is a simple JOIN in which the result is based on matched data as

per the equality condition specified in the SQL query.

Inner Join Syntax is,

SELECT column-name-list FROM

table-name1 INNER JOIN table-name2

WHERE table-name1.column-name = table-name2.column-name;

Copy

Example of INNER JOIN

Consider a class table,

ID NAME
1 abhi

2 adam

3 alex

4 anu

and the class_info table,

ID Address

1 DELHI

2 MUMBAI

3 CHENNAI

Inner JOIN query will be,

SELECT * from class INNER JOIN class_info where class.id =

class_info.id;

Copy
The resultset table will look like,

ID NAME ID Address

1 abhi 1 DELHI

2 adam 2 MUMBAI
3 alex 3 CHENNAI

Natural JOIN

Natural Join is a type of Inner join which is based on column having

same name and same datatype present in both the tables to be joined.

The syntax for Natural Join is,

SELECT * FROM

table-name1 NATURAL JOIN table-name2;

Copy

Example of Natural JOIN

Here is the class table,

ID NAME

1 abhi

2 adam

3 alex

4 anu

and the class_info table,

ID Address
1 DELHI

2 MUMBAI

3 CHENNAI

Natural join query will be,

SELECT * from class NATURAL JOIN class_info;

Copy
The resultset table will look like,

ID NAME Address

1 abhi DELHI

2 adam MUMBAI

3 alex CHENNAI

In the above example, both the tables being joined

have ID column(same name and same datatype), hence the records
for which value of ID matches in both the tables will be the result of
Natural Join of these two tables.

OUTER JOIN

Outer Join is based on both matched and unmatched data. Outer Joins
subdivide further into,
 1. Left Outer Join
2. Right Outer Join
3. Full Outer Join

LEFT Outer Join

The left outer join returns a resultset table with the matched
data from the two tables and then the remaining rows of the left table
and null from the right table's columns.

Syntax for Left Outer Join is,

SELECT column-name-list FROM

table-name1 LEFT OUTER JOIN table-name2

ON table-name1.column-name = table-name2.column-name;

Copy
To specify a condition, we use the ON keyword with Outer Join.

Left outer Join Syntax for Oracle is,

SELECT column-name-list FROM

table-name1, table-name2 on table-name1.column-name =

table-name2.column-name(+);

Copy

Example of Left Outer Join

Here is the class table,

ID NAME

1 abhi
2 adam

3 alex

4 anu

5 ashish

and the class_info table,

ID Address

1 DELHI

2 MUMBAI

3 CHENNAI

7 NOIDA

8 PANIPAT

Left Outer Join query will be,

SELECT * FROM class LEFT OUTER JOIN class_info ON (class.id

= class_info.id);

Copy
The resultset table will look like,

ID NAME ID Address
1 abhi 1 DELHI

2 adam 2 MUMBAI

3 alex 3 CHENNAI

4 anu null null

5 ashish null null

RIGHT Outer Join

The right outer join returns a resultset table with the matched
data from the two tables being joined, then the remaining rows of
the right table and null for the remaining left table's columns.

Syntax for Right Outer Join is,

SELECT column-name-list FROM

table-name1 RIGHT OUTER JOIN table-name2

ON table-name1.column-name = table-name2.column-name;

Copy
Right outer Join Syntax for Oracle is,

SELECT column-name-list FROM

table-name1, table-name2

ON table-name1.column-name(+) = table-name2.column-name;

Copy
Example of Right Outer Join

Once again the class table,

ID NAME

1 abhi

2 adam

3 alex

4 anu

5 ashish

and the class_info table,

ID Address

1 DELHI

2 MUMBAI

3 CHENNAI

7 NOIDA

8 PANIPAT
Right Outer Join query will be,

SELECT * FROM class RIGHT OUTER JOIN class_info ON

(class.id = class_info.id);

Copy
The resultant table will look like,

ID NAME ID Address

1 abhi 1 DELHI

2 adam 2 MUMBAI

3 alex 3 CHENNAI

null null 7 NOIDA

null null 8 PANIPAT

Full Outer Join

The full outer join returns a resultset table with the matched data of
two table then remaining rows of both left table and then
the right table.

Syntax of Full Outer Join is,

SELECT column-name-list FROM

table-name1 FULL OUTER JOIN table-name2

ON table-name1.column-name = table-name2.column-name;

Copy
Example of Full outer join is,

The class table,

ID NAME

1 abhi

2 adam

3 alex

4 anu

5 ashish

and the class_info table,

ID Address

1 DELHI

2 MUMBAI

3 CHENNAI

7 NOIDA
8 PANIPAT

Full Outer Join query will be like,

SELECT * FROM class FULL OUTER JOIN class_info ON (class.id

= class_info.id);

Copy
The resultset table will look like,

Now that
we ID NAME ID Address have

1 abhi 1 DELHI

2 adam 2 MUMBAI

3 alex 3 CHENNAI

4 anu null null

5 ashish null null

null null 7 NOIDA

null null 8 PANIPAT

learned SQL JOIN, you can check these SQL topics as well and their
usage:

 SQL function
 SQL Alias
 SQL SET operation
 SQL Views
SQL Constraints
SQL Constraints are rules used to limit the type of data that can go
into a table, to maintain the accuracy and integrity of the data inside
table.

Constraints can be divided into the following two types,

1. Column level constraints: Limits only column data.

2. Table level constraints: Limits whole table data.
Constraints are used to make sure that the integrity of data is
maintained in the database. Following are the most used constraints
that can be applied to a table.

 NOT NULL
 UNIQUE
 PRIMARY KEY
 FOREIGN KEY
 CHECK
 DEFAULT

NOT NULL Constraint

By default, a column can hold NULL values. If you do not want a

column to have a NULL value, use the NOT NULL constraint.

 It restricts a column from having a NULL value.

 We use ALTER statement and MODIFY statement to specify this
constraint.

One important point to note about this constraint is that it cannot be

defined at table level.

Example using NOT NULL constraint:

CREATE TABLE Student

( s_id int NOT NULL,

 name varchar(60),

age int

);

Copy

The above query will declare that the s_id field of Student table will
not take NULL value.

If you wish to alter the table after it has been created, then we can use
the ALTER command for it:

ALTER TABLE Student

MODIFY s_id int NOT NULL;

Copy

UNIQUE Constraint

It ensures that a column will only have unique values. A UNIQUE

constraint field cannot have any duplicate data.

 It prevents two records from having identical values in a column

 We use ALTER statement and MODIFY statement to specify this
constraint.

Example of UNIQUE Constraint:

Here we have a simple CREATE query to create a table, which will have
a column s_id with unique values.

CREATE TABLE Student

( s_id int NOT NULL,

name varchar(60),

age int NOT NULL UNIQUE

);
Copy

The above query will declare that the s_id field of Student table will
only have unique values and wont take NULL value.

If you wish to alter the table after it has been created, then we can use
the ALTER command for it:

ALTER TABLE Student

MODIFY age INT NOT NULL UNIQUE;

Copy

The above query specifies that s_id field of Student table will only
have unique value.

Primary Key Constraint

Primary key constraint uniquely identifies each record in a database. A

Primary Key must contain unique value and it must not contain null
value. Usually Primary Key is used to index the data inside the table.

PRIMARY KEY constraint at Table Level

CREATE table Student

( s_id int PRIMARY KEY,

Name varchar(60) NOT NULL,

Age int);

Copy

The above command will creates a PRIMARY KEY on the s_id.

PRIMARY KEY constraint at Column Level

ALTER table Student

ADD PRIMARY KEY (s_id);

Copy

The above command will creates a PRIMARY KEY on the s_id.

Foreign Key Constraint

Foreign Key is used to relate two tables. The relationship between the
two tables matches the Primary Key in one of the tables with a Foreign
Key in the second table.

 This is also called a referencing key.

 We use ALTER statement and ADD statement to specify this
constraint.

To understand FOREIGN KEY, let's see its use, with help of the below
tables:

Customer_Detail Table

c_id Customer_Name address

101 Adam Noida

102 Alex Delhi

103 Stuart Rohtak

Order_Detail Table

Order_id Order_Name c_id

10 Order1 101

11 Order2 103
12 Order3 102

In Customer_Detail table, c_id is the primary key which is set as

foreign key in Order_Detail table. The value that is entered
in c_id which is set as foreign key in Order_Detail table must be
present in Customer_Detail table where it is set as primary key. This
prevents invalid data to be inserted into c_id column
of Order_Detail table.

If you try to insert any incorrect data, DBMS will return error and will
not allow you to insert the data.

FOREIGN KEY constraint at Table Level

CREATE table Order_Detail(

order_id int PRIMARY KEY,

order_name varchar(60) NOT NULL,

c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id)

);

Copy

In this query, c_id in table Order_Detail is made as foriegn key, which

is a reference of c_id column in Customer_Detail table.

FOREIGN KEY constraint at Column Level

ALTER table Order_Detail

ADD FOREIGN KEY (c_id) REFERENCES Customer_Detail(c_id);

Copy
Behaviour of Foriegn Key Column on Delete

There are two ways to maintin the integrity of data in Child table, when
a particular record is deleted in the main table. When two tables are
connected with Foriegn key, and certain data in the main table is
deleted, for which a record exits in the child table, then we must have
some mechanism to save the integrity of data in the child table.

1. On Delete Cascade : This will remove the record from child

table, if that value of foriegn key is deleted from the main table.
2. On Delete Null : This will set all the values in that record of child
table as NULL, for which the value of foriegn key is deleted from
the main table.
3. If we don't use any of the above, then we cannot delete data from
the main table for which data in child table exists. We will get an
error if we try to do so.

ERROR : Record in child table exist

CHECK Constraint

CHECK constraint is used to restrict the value of a column between a

range. It performs check on the values, before storing them into the
database. Its like condition checking before saving data into a column.

Using CHECK constraint at Table Level

CREATE table Student(

s_id int NOT NULL CHECK(s_id > 0),

Name varchar(60) NOT NULL,

Age int

);
Copy

The above query will restrict the s_id value to be greater than zero.

Using CHECK constraint at Column Level

ALTER table Student ADD CHECK(s_id > 0);

Copy

Related Tutorials:

 SQL function
 SQL Join
 SQL Alias
 SQL SET operation
 SQL Sequences
 SQL Views
 UNIT-4

PL/SQL BASICS

PL/SQL (Procedural Language/Structured Query Language) is a block-structured language developed

by Oracle that allows developers to combine the power of SQL with procedural programming constructs. The
PL/SQL language enables efficient data manipulation and control-flow logic, all within the Oracle Database.

In this article, we’ll cover PL/SQL basics, including its core features, PL/SQL block structure, and practical
examples that demonstrate the power of PL/SQL. We’ll also explore the differences between SQL and
PL/SQL, how variables and identifiers work, and how the PL/SQL execution environment operates within
Oracle.

What is PL/SQL?
PL/SQL is a combination of SQL and procedural programming constructs, enabling developers to write code
that performs database operations efficiently. It was developed by Oracle to enhance SQL’s capabilities and
allow for advanced error handling, complex calculations, and programmatic control over database operations.

PL/SQL allows developers to:

 Execute SQL queries and DML commands inside procedural blocks.

 Define variables and perform complex calculations.

 Create reusable program units, such as procedures, functions, and triggers.

 Handle exceptions, ensuring the program runs smoothly even when errors occur

Key Features of PL/SQL

PL/SQL brings the benefits of procedural programming to the relational database world. Some of the most
important features of PL/SQL include:

1. Block Structure: PL/SQL can execute a number of queries in one block using single command.

2. Procedural Constructs: One can create a PL/SQL unit such as procedures, functions, packages, triggers,
and types, which are stored in the database for reuse by applications.

3. Error Handling: PL/SQL provides a feature to handle the exception which occurs in PL/SQL block known
as exception handling block.

4. Reusable Code: Create stored procedures, functions, triggers, and packages, which can be executed
repeatedly.

5. Performance: Reduces network traffic by executing multiple SQL statements within a single block

Differences Between SQL and PL/SQL

Feature SQL PL/SQL
Purpose SQL is a single query that is used to perform PL/SQL is a block of codes that used to write the
 DML and DDL operations. entire program blocks/ procedure/ function, etc.
It is declarative, that defines what needs to be PL/SQL is procedural that defines how the things
Nature
done, rather than how things need to be done. needs to be done.
Execution Executes single statement. Executes block of code
Data retrieval, manipulation and definition( eg.
Use Case Mainly used to create an application.
SELECT, INSERT, UPDATE)
Syntax SQL statements only SQL Statements combined with procedural logic
Data Can contain SQL inside its blocks and is used for
Performs actions directly on the database.
Handling more control over data handling
Structure of PL/SQL Block
PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language
that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of
blocks, which can be nested within each other.

Typically, each block performs a logical action in the program. A block has the following structure:

DECLARE
declaration statements;

BEGIN
executable statements

EXCEPTIONS
exception handling statements

END;
PL/SQL code is written in blocks, which consist of three main sections:

 Declare section starts with DECLARE keyword in which variables, constants, records as cursors can be
declared which stores data temporarily. It basically consists definition of PL/SQL identifiers. This part
of the code is optional.

 Execution section starts with BEGIN and ends with END keyword.This is a mandatory section and here
the program logic is written to perform any task like loops and conditional statements. It supports all
DML commands, DDL commands and SQL*PLUS built-in functions as well.
  Exception section starts with EXCEPTION keyword.This section is optional which contains statements
that are executed when a run-time error occurs. Any exceptions can be handled in this section.

PL/SQL Identifiers
In PL/SQL, identifiers are names used to represent various program elements like variables, constants,
procedures, cursors, triggers etc. These identifiers allow you to store, manipulate, and access data throughout
your PL/SQL code.

1. Variables in PL/SQL

Like several other programming languages, variables in PL/SQL must be declared prior to its use. A variable is
like a container that holds data during program execution. Each variable must have a valid name and a specific
data type.

Syntax for declaration of variables:

variable_name datatype [NOT NULL := value ];

 variable_name: The name of the variable.

 datatype: The data type of the variable (e.g., INTEGER, VARCHAR2).

 NOT NULL: This optional constraint means the variable cannot be left empty.

 := value: This optional assignment assigns an initial value to the variable.

Example: Declaring Variables

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE
var1 INTEGER;
var2 REAL;
var3 varchar2(20) ;

BEGIN
null;
END;
/
Output:

PL/SQL procedure successfully completed.

Explanation:

 SET SERVEROUTPUT ON: It is used to display the buffer used by the dbms_output.

 var1 INTEGER : It is the declaration of variable, named var1 which is of integer type. There are many
other data types that can be used like float, int, real, smallint, long etc. It also supports variables used in
SQL as well like NUMBER(prec, scale), varchar, varchar2 etc.

 Slash (/) after END;: The slash (/) tells the SQL*Plus to execute the block.

 Assignment operator (:=) : It is used to assign a value to a variable.

2. Displaying Output in PL/SQL

The outputs are displayed by using DBMS_OUTPUT which is a built-in package that enables the user to
display output, debugging information, and send messages from PL/SQL blocks, subprograms, packages, and
triggers. Let us see an example to see how to display a message using PL/SQL :

Example: Displaying Output

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE
var varchar2(40) := 'I love GeeksForGeeks' ;

BEGIN
dbms_output.put_line(var);

END;
/
Output:

I love GeeksForGeeks

PL/SQL procedure successfully completed.

Explanation:

dbms_output.put_line : This command is used to direct the PL/SQL output to a screen.

3. Comments in PL/SQL

Like in many other programming languages, in PL/SQL also, comments can be put within the code which has
no effect in the code. There are two syntaxes to create comments in PL/SQL :

 Single Line Comment: To create a single line comment , the symbol - - is used.

 Multi Line Comment: To create comments that span over several lines, the symbol /* and */ is used.

Example: Adding Comments

-- This is a single-line comment

/*
This is a multi-line comment
that spans over multiple lines.
*/
4. Taking input from users

In PL/SQL we can take input from the user and store it in a variable using substitution variables. These
variables are preceded by an & symbol. Let us see an example to show how to take input from users in PL/SQL:

Example: Taking Input from Users

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

-- taking input for variable a

a number := &a;
 -- taking input for variable b
b varchar2(30) := &b;

BEGIN
null;

END;
/
Output:

Enter value for a: 24

old 2: a number := &a;
new 2: a number := 24;
Enter value for b: 'GeeksForGeeks'
old 3: b varchar2(30) := &b;
new 3: b varchar2(30) := 'GeeksForGeeks';

PL/SQL procedure successfully completed.

Explanation:

 &a and &b are substitution variables where the user will be prompted to provide values.

 The user is asked to enter values for a and b when the code runs.

PL/SQL Practical Example

Let’s combine all the above concepts into one practical example. We’ll create a PL/SQL block that takes two
numbers from the user, calculates their sum, and displays the result.

--PL/SQL code to print sum of two numbers taken from the user.
SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

-- taking input for variable a

a integer := &a ;

-- taking input for variable b

b integer := &b ;
c integer ;

BEGIN
c := a + b ;
dbms_output.put_line('Sum of '||a||' and '||b||' is = '||c);

END;
Execution:

Enter value for a: 2

Enter value for b: 3

Sum of 2 and 3 is = 5

PL/SQL procedure successfully completed.

PL/SQL Execution Environment
The PL/SQL engine resides in the Oracle engine. When a PL/SQL block is executed, it sends a single request
to the Oracle engine, which processes the SQL and PL/SQL statements in the block together. This reduces
network traffic, making PL/SQL more efficient for batch processing and handling complex logic.

PL/SQL is a powerful tool in Oracle for combining SQL with procedural programming capabilities. With
PL/SQL features like error handling, reusable program units, and support for loops and conditionals, PL/SQL
extends SQL’s data manipulation capabilities and enables developers to create sophisticated applications
within the database. By understanding SQL vs PL/SQL and the advantages of the PL/SQL execution
environment, developers can unlock the full potential of Oracle’s PL/SQL language for robust database
applications.

The PL/SQL Character Set

A PL/SQL program consists of a sequence of statements, each made up of one or more lines of text. The precise
characters available to you will depend on what database character set you’re using. For example, Table 3-2
illustrates the available characters in the US7ASCII character set.

Table 3-2. Characters available to PL/SQL in the US7ASCII character set

Type Characters
Letters A-Z, a-z
Digits 0-9
Symbols ~ ! @ # $ % & * ( ) _ - + = | [ ] { } : ; " ' < > , . ? / ^
Whitespace Tab, space, newline, carriage return

Every keyword in PL/SQL is made from various combinations of characters in this character set. Now you just
have to figure out how to put them all together!

By default, PL/SQL is a case-insensitive language. That is, uppercase letters are treated the same way as
lowercase letters except when characters are surrounded by single quotes, which makes them a literal string.

A number of these characters—both singly and in combination with other characters—have a special
significance in PL/SQL. Table 3-3 lists these special symbols.

Table 3-3. Simple and compound symbols in PL/SQL

Symbo
Description
l
; Semicolon: terminates declarations and statements
Percent sign: attribute indicator (cursor attributes like %ISOPEN and indirect declaration attributes
%
like %ROWTYPE); also used as multibyte wildcard symbol with the LIKE condition
_ Single underscore: single-character wildcard symbol in LIKE condition
@ At- sign: remote ...
PL\SQL STRUCTURE
PL/SQL, or Procedural Language/SQL, is a powerful extension of SQL developed by Oracle. It combines
SQL's data manipulation capabilities with procedural programming constructs, enabling developers to write
complex database applications with better control and error handling. The fundamental structure of a PL/SQL
block is: DECLARE (optional), BEGIN, EXCEPTION (optional), and END.
Structure of a PL/SQL Block:

1. 1. DECLARE (Optional):
o This section is used to declare variables, constants, cursors, user-defined functions, procedures,
and other code elements that will be used within the block.
o It's not mandatory; if no declarations are needed, this section can be omitted.
o Example: DECLARE v_count NUMBER := 0;
2. 2. BEGIN:
o This is the executable part of the block and contains the main logic and code that interacts with
the database.
o All executable statements, including SQL queries and PL/SQL control structures, reside within
this section.
o Example: BEGIN ... SQL statements ... PL/SQL control structures ... END;
3. 3. EXCEPTION (Optional):
o This section handles runtime errors (exceptions) that might occur during the execution of the
code within the BEGIN section.
o Exception handlers are defined to catch specific exceptions and take appropriate actions.
o Example: EXCEPTION WHEN OTHERS THEN ...
4. 4. END:
o This keyword marks the end of the PL/SQL block.

4.
o

Example:
Code
DECLARE
v_employee_id NUMBER := 100;
v_salary NUMBER;
BEGIN
-- Select the salary of the employee
SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id;

-- Display the salary

DBMS_OUTPUT.PUT_LINE('Salary of employee ' || v_employee_id || ' is: ' || v_salary);

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;
/
In this example:

 v_employee_id and v_salary are declared as variables.

 The SELECT statement retrieves the salary of a specific employee.
 DBMS_OUTPUT.PUT_LINE displays the result or an error message.
 The EXCEPTION section handles NO_DATA_FOUND and other potential errors.

PL/SQL Conditional Statements

PL/SQL (Procedural Language/Structured Query Language) is an extension of SQL used in Oracle
databases to write procedural code. It includes various conditional statements that allow developers to execute
different blocks of code based on specific conditions. Decision-making statements in programming languages
decide the direction of the flow of program execution. Conditional Statements available in PL/SQL are defined
below:

1. IF THEN

2. IF THEN ELSE

3. NESTED-IF-THEN

4. IF THEN ELSIF-THEN-ELSE Ladder

1. IF THEN

if then the statement is the most simple decision-making statement. It is used to decide whether a certain
statement or block of statements will be executed or not i.e if a certain condition is true then a block of
statement is executed otherwise not.

Syntax:

if condition then
-- do something
end if;
Here, condition after evaluation will be either true or false. if statement accepts boolean values – if the value is
true then it will execute the block of statements below it otherwise not. if and endif consider as a block here.

Example:
declare
-- declare the values here

begin

if condition then
dbms_output.put_line('output');

end if;

dbms_output.put_line('output2');
end;

-- pl/sql program to illustrate If statement

declare
num1 number:= 10;
num2 number:= 20;

begin

if num1 > num2 then

dbms_output.put_line('num1 small');
end if;

dbms_output.put_line('I am Not in if');

end;
As the condition present in the if statement is false. So, the block below the if statement is not executed.
Output:

I am Not in if

2. IF THEN ELSE
The if statement alone tells us that if a condition is true it will execute a block of statements and if the condition
is false it won’t. But what if we want to do something else if the condition is false. Here comes the else
statement. We can use the else statement with if statement to execute a block of code when the condition is
false.

Syntax:-

if (condition) then
-- Executes this block if
-- condition is true
else
-- Executes this block if
-- condition is false

Example:-

-- pl/sql program to illustrate If else statement

declare
num1 number:= 10;
num2 number:= 20;

begin

if num1 < num2 then

dbms_output.put_line('i am in if block');

ELSE
dbms_output.put_line('i am in else Block');
end if;

dbms_output.put_line('i am not in if or else Block');

end;
Output:-
i'm in if Block
i'm not in if and not in else Block
The block of code following the else statement is executed as the condition present in the if statement is false
after calling the statement which is not in block(without spaces).

3. NESTED-IF-THEN

A nested if-then is an if statement that is the target of another if statement. Nested if-then statements mean an if
statement inside another if statement. Yes, PL/SQL allows us to nest if statements within if-then statements. i.e,
we can place an if then statement inside another if then statement. Syntax:-

if (condition1) then
-- Executes when condition1 is true
if (condition2) then
-- Executes when condition2 is true
end if;
end if;

-- pl/sql program to illustrate nested If statement

declare
num1 number:= 10;
num2 number:= 20;
num3 number:= 20;

begin
if num1 < num2 then
dbms_output.put_line('num1 small num2');

if num1 < num3 then

dbms_output.put_line('num1 small num3 also');
end if;

end if;
dbms_output.put_line('after end if');
end;
Output:-

num1 small num2

num1 small num3 also
after end if
4. IF THEN ELSIF-THEN-ELSE Ladder

Here, a user can decide among multiple options. The if then statements are executed from the top down. As
soon as one of the conditions controlling the if is true, the statement associated with that if is executed, and the
rest of the ladder is bypassed. If none of the conditions is true, then the final else statement will be executed.
Syntax:-

if (condition) then
--statement
elsif (condition) then
--statement
.
.
else
--statement
endif
Flow Chart:-
 Example:-

-- pl/sql program to illustrate if-then-elif-then-else ladder

declare
num1 number:= 10;
num2 number:= 20;

begin

if num1 < num2 then

dbms_output.put_line('num1 small');

ELSEIF num1 = num2 then

dbms_output.put_line('both equal');

ELSE
dbms_output.put_line('num2 greater');
end if;

dbms_output.put_line('after end if');

end;
Output:-

num1 small
after end if
 PL/SQL conditional statements are essential for effective procedural programming in Oracle databases.
The IF THEN and IF THEN ELSE statements provide straightforward decision-making, while NESTED-IF-THEN
supports complex nested logic. The IF THEN ELSIF-THEN-ELSE ladder allows handling multiple conditions in
a structured manner.

LOOPING STATEMENTS

One of the key features in PL/SQL for controlling program flow is the LOOP statement. The LOOP statement
is a feature of PL/SQL that allows you to repeatedly execute a block of code until a specified condition is
satisfied.

Procedural Language/Structured Query Language (PL/SQL) provides a robust environment for database
programming, allowing developers to create powerful and efficient code for Oracle databases.

Syntax

LOOP
-- Code block to be executed repeatedly
END LOOP;

EXIT Statement
The EXIT statement is used to break the loop whether the loop condition has been satisfied or not. This
statement is particularly useful when you want to terminate the loop based on certain conditions within the loop
block.

Syntax

LOOP
-- Code block
IF condition THEN
EXIT;
END IF;
END LOOP;
Example of PL/SQL LOOP with Conditional EXIT

In this example, we showcase the application of a PL/SQL LOOP construct with a conditional EXIT statement.
The code demonstrates a scenario where a loop iterates a specific block of code, printing iteration numbers, and
breaks out of the loop when a predefined condition is met.

DECLARE
counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('This is iteration number ' || counter);
IF counter = 3 THEN
EXIT;
END IF;
counter := counter + 1;
END LOOP;
END;
/
Output:

Statement processed.
This is iteration number 1
This is iteration number 2
This is iteration number 3
Explanation:

 Initially counter variable is set to 1.

 The LOOP statement repeatedly executes the code block within it.

 Inside the loop, DBMS_OUTPUT.PUT_LINE is used to print Iteration number (value of counter).

 The counter is incremented by 1 in each iteration.

 IF statement is executed when the value of counter will become 3 and The EXIT statement is executed
and loop stops.

EXIT WHEN Statement

The EXIT WHEN statement allows for a more concise way to specify the condition under which a loop
should exit. It checks the condition directly within the loop's syntax.

Syntax

LOOP
-- Code block
EXIT WHEN condition;
END LOOP;
Example of PL/SQL LOOP with EXIT WHEN

The purpose of this example is to show how to print "GeeksForGeeks" repeatedly using a PL/SQL LOOP
construct. With the help of the EXIT WHEN statement, the loop can be controlled to end when a counter
variable reaches a predetermined threshold.
DECLARE
counter NUMBER := 1; -- Initialization of the counter variable

BEGIN
-- Loop that prints "GeeksForGeeks" five times
LOOP
DBMS_OUTPUT.PUT_LINE('GeeksForGeeks');

counter := counter + 1; -- Increment the counter

EXIT WHEN counter > 5; -- Exit the loop when counter exceeds 5
END LOOP;
END;
/
Output:

Statement processed.
GeeksForGeeks
GeeksForGeeks
GeeksForGeeks
GeeksForGeeks
GeeksForGeeks
Explanation:

 Initially counter variable is set to 1.

 The LOOP statement repeatedly executes the code block within it.

 Inside the loop, DBMS_OUTPUT.PUT_LINE is used to print "GeeksForGeeks".

 The counter is incremented by 1 in each iteration.

 The EXIT WHEN statement is executed when the loop when the counter exceeds 5.

Nested Loops
Nested Loop is a Loop inside Loop and PL/SQL supports nested loops that allows you to have multiple levels
of iteration within a program. This is achieved by placing one or more LOOP statements inside another. Each
nested loop has its own set of loop control statements.

Syntax

-- Outer Loop
LOOP
-- Code block
-- Inner Loop
LOOP
-- Inner loop code block
EXIT WHEN inner_condition;
END LOOP;
EXIT WHEN outer_condition;
END LOOP;
Example of PL/SQL Nested FOR Loop Simultaneous Iteration

In this example, we will create nested FOR loops that iterate over two ranges, demonstrating simultaneous
iteration.
DECLARE
outer_counter NUMBER := 1;
inner_counter NUMBER := 1;
BEGIN
FOR outer_counter IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE('Outer Loop - Iteration ' || outer_counter);

FOR inner_counter IN 1..2 LOOP

DBMS_OUTPUT.PUT_LINE('Inner Loop - Iteration ' || inner_counter);
END LOOP;
END LOOP;
END;
/
Output:

Statement processed.
Outer Loop - Iteration 1
Inner Loop - Iteration 1
Inner Loop - Iteration 2
Outer Loop - Iteration 2
Inner Loop - Iteration 1
Inner Loop - Iteration 2
Outer Loop - Iteration 3
Inner Loop - Iteration 1
Inner Loop - Iteration 2
Explanation:

 There are two nested loops

 The outer FOR loop (FOR outer_counter IN 1..3 LOOP) runs three times.

 Inside the outer FOR loop, there is an inner FOR loop (FOR inner_counter IN 1..2 LOOP) that runs
two times for each iteration of the outer loop.

 DBMS_OUTPUT.PUT_LINE statements is used to print output.

Pl/SQL is a Procedural Language that is used to write program blocks, procedures, functions, cursors, triggers
for databases. It Provides a LOOP statement facility that is used to repeatedly execute a block of code. and It's
other feature like EXIT and EXIT WHEN statement are used to stop loop based on specific conditions and It
also support Nested loop functionality.

SQL Cursor – Overview, Syntax & Examples

A cursor in SQL is a database object used to retrieve, process, and manipulate rows one at a time from a
result set.

While SQL is a set-based language (it usually works with entire result sets at once), cursors allow row-by-row
processing, which is useful when more control is needed in business logic or procedural code.

Types of SQL Cursors

Cursor Type Description
Implicit Cursor Automatically created by the system (e.g., SELECT INTO)
Explicit Cursor Declared and controlled by the programmer (manual steps)
Steps to Use an Explicit Cursor (PL/SQL / T-SQL)
1. Declare the cursor
2. Open the cursor (executes the query)
3. Fetch rows into variables
4. Process the data
5. Close the cursor

Cursor in Oracle PL/SQL – Example

sql
CopyEdit
DECLARE
CURSOR emp_cursor IS
SELECT name, salary FROM employee;

v_name employee.name%TYPE;
v_salary employee.salary%TYPE;
BEGIN
OPEN emp_cursor;

LOOP
FETCH emp_cursor INTO v_name, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Salary: ' || v_salary);

END LOOP;

CLOSE emp_cursor;
END;

Advantages of Cursors
 Useful for row-by-row processing when complex logic is needed
 Ideal for operations not possible in set-based queries
 Helps in audit, logging, calculations, and conditional updates

Disadvantages of Cursors
 Slower than set-based operations (looping is expensive)
 More memory-intensive
 Can make code more complex

Alternatives to Cursors
Whenever possible, try using:

 Set-based SQL operations (UPDATE, MERGE, JOIN)

 Window functions
 CTEs (Common Table Expressions)
 T-SQL (SQL
Element PL/SQL
Server)
DECLARE,
Cursor Steps OPEN, FETCH, Same
CLOSE
LOOP or FOR WHILE loop with
Loop Control
loop @@FETCH_STATUS
Implicit or Must
Cleanup
manual CLOSE DEALLOCATE

Subprograms in RDBMS (PL/SQL / T-SQL)

Subprograms are named blocks of code in a database that perform a

specific task and can be invoked (called) as needed. They are essential for
modular, reusable, and maintainable programming in relational
databases like Oracle and SQL Server.

Types of Subprograms
Type Description

Procedures Perform actions but do not return a value

Functions Perform actions and return a single value

1. Procedure (PL/SQL)
A procedure is a subprogram that performs a task but does not return a
value directly (though it can return values through OUT parameters).

✅ Syntax:
sql
CopyEdit
CREATE OR REPLACE PROCEDURE procedure_name (
param1 IN datatype,
param2 OUT datatype
)
IS
BEGIN
-- Procedure logic
END;
Example:
sql
CopyEdit
CREATE OR REPLACE PROCEDURE greet_user(p_name IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END;

Calling the Procedure:

sql
CopyEdit
BEGIN
 T-SQL (SQL
Element PL/SQL
Server)
greet_user('John');
END;

2. Function (PL/SQL)
A function is a subprogram that returns a value and can be used in SQL
statements.

Syntax:
sql
CopyEdit
CREATE OR REPLACE FUNCTION function_name (
param1 IN datatype
) RETURN datatype IS
BEGIN
-- Function logic
RETURN value;
END;

Example:
sql
CopyEdit
CREATE OR REPLACE FUNCTION get_bonus(salary NUMBER) RETURN
NUMBER IS
BEGIN
RETURN salary * 0.10;
END;

Calling the Function:

sql
CopyEdit
DECLARE
bonus NUMBER;
BEGIN
bonus := get_bonus(50000);
DBMS_OUTPUT.PUT_LINE('Bonus: ' || bonus);
END;

✅Also usable in SQL:

sql
CopyEdit
SELECT name, get_bonus(salary) FROM employee;

Function in SQL Server:

sql
CopyEdit
CREATE FUNCTION get_bonus (@salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN @salary * 0.10;
END

Use:

sql
 T-SQL (SQL
Element PL/SQL
Server)
CopyEdit
SELECT name, dbo.get_bonus(salary) FROM employee;

Differences Between Procedures and

Functions
Feature Procedure Function

No (use OUT
Returns Value Yes (must return one value)
parameters)

Use in SQL Queries No Yes

Can have OUT

Yes No (only return value)
Params

No (used in
Called with EXEC Yes
expressions/SELECT)

Benefits of Subprograms
 Reusability – Code once, use many times
 Modularity – Divide logic into smaller parts
 Maintainability – Easy to debug and update
 Security – Hide complex logic behind callable names
 UNIT-5

Exception Handling in RDBMS

In Relational Database Management Systems (RDBMS), exception handling refers to the

ability to detect and respond to errors or unexpected conditions during the execution of
SQL queries, stored procedures, functions, or triggers.

It helps ensure data integrity, prevents system crashes, and improves error reporting and
debugging.

Where Is Exception Handling Used in RDBMS?

 Stored Procedures
 Functions
 Triggers
 PL/SQL or T-SQL blocks
 Transactions

Why Is Exception Handling Important in RDBMS?

 Prevents data corruption or loss during execution errors

 Allows custom responses to errors (e.g., log them or return user-friendly messages)
 Helps in debugging and maintaining database code
 Maintains control flow during failures

Types of Errors in RDBMS

Type Example
Syntax Error Missing keywords or incorrect SQL format
Runtime Error Division by zero, constraint violations
Logical Error Wrong calculations or query logic
System Error Disk space full, network failure

Exception Handling in Oracle (PL/SQL)

PL/SQL provides structured exception handling using the following syntax:

sql
CopyEdit
BEGIN
-- SQL or PL/SQL statements
EXCEPTION
WHEN exception_name THEN
 -- Exception handling code
WHEN OTHERS THEN
-- Handles any other exceptions
END;

Example:

sql
CopyEdit
DECLARE
v_num NUMBER := 10;
v_denom NUMBER := 0;
v_result NUMBER;
BEGIN
v_result := v_num / v_denom;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Cannot divide by zero.');
END;

Exception Handling in SQL Server (T-SQL)

T-SQL (used in Microsoft SQL Server) uses TRY...CATCH for error handling:

sql
CopyEdit
BEGIN TRY
-- SQL statements
END TRY
BEGIN CATCH
-- Error handling code
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Example:

sql
CopyEdit
BEGIN TRY
DECLARE @result INT;
SET @result = 10 / 0;
PRINT 'Result is ' + CAST(@result AS VARCHAR);
END TRY
BEGIN CATCH
PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH

Common Predefined Exceptions in PL/SQL

Exception Name Description

NO_DATA_FOUND No rows returned by a SELECT statement
TOO_MANY_ROWS SELECT returns more than one row
ZERO_DIVIDE Attempt to divide a number by zero
 Exception Name Description
INVALID_CURSOR Illegal cursor operation
DUP_VAL_ON_INDEX Duplicate value violates unique index

Conclusion

 Exception handling in RDBMS is crucial for writing robust and safe database
programs.
 It allows you to catch and handle predictable or unexpected errors.
 Both Oracle (PL/SQL) and SQL Server (T-SQL) provide structured ways to handle
exceptions.

PREDEFINED EXCEPTIONS

Predefined exceptions are system-defined error conditions that occur during the execution of
database programs such as PL/SQL (Oracle) or T-SQL (SQL Server). These exceptions
automatically get triggered when certain common runtime errors occur.

In Oracle PL/SQL: Predefined Exceptions

Oracle provides several predefined exception names to handle frequent errors in PL/SQL.
These exceptions are part of the STANDARD package, so they can be used without explicit
declaration.

Common Predefined Exceptions in Oracle PL/SQL

Error
Exception Name Description
Code
ORA-
NO_DATA_FOUND Raised when a SELECT INTO returns no rows
01403
ORA-
TOO_MANY_ROWS Raised when a SELECT INTO returns more than one row
01422
ORA-
ZERO_DIVIDE Raised when attempting to divide by zero
01476
ORA-
INVALID_CURSOR Raised when an illegal cursor operation is attempted
01001
DUP_VAL_ON_INDEX
ORA- Raised when duplicate values are inserted into a column with
00001 a unique index
ORA-
VALUE_ERROR Raised when a value is too large for the variable
06502
ORA-
INVALID_NUMBER Raised when conversion of a string to number fails
01722
ORA-
LOGIN_DENIED Raised when login credentials are invalid
01017
ORA-
STORAGE_ERROR Raised when memory allocation fails
06500
Example: Using Predefined Exceptions in Oracle
sql
CopyEdit
DECLARE
v_result NUMBER;
BEGIN
v_result := 100 / 0; -- This will raise ZERO_DIVIDE
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero not allowed.');
END;

In SQL Server (T-SQL): Error Handling Instead of

Named Exceptions
T-SQL (used in Microsoft SQL Server) does not have named predefined exceptions like
PL/SQL, but it provides error functions inside TRY...CATCH blocks to capture system
errors.

Common Error Codes in SQL Server

Error Number Description

8134 Divide by zero error
2627 Violation of PRIMARY KEY constraint (duplicate)
547 Foreign key constraint violation
245 Conversion failed when converting string to int
1205 Deadlock victim

T-SQL Example Using TRY...CATCH

sql
CopyEdit
BEGIN TRY
DECLARE @x INT = 10, @y INT = 0;
DECLARE @z INT;
SET @z = @x / @y;
END TRY
BEGIN CATCH
PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR);
PRINT 'Error Message: ' + ERROR_MESSAGE();
END CATCH

 Predefined exceptions in Oracle PL/SQL have names and are automatically raised
for common errors.
 SQL Server (T-SQL) handles errors using error numbers and TRY...CATCH blocks.
 Handling predefined exceptions is essential for robust database programs.
User-Defined Exceptions

In Relational Database Management Systems (RDBMS), user-defined exceptions are

custom error conditions that you (the developer) create to handle specific, meaningful error
situations in your stored procedures, functions, or PL/SQL/T-SQL blocks.

They are useful when built-in exceptions are not sufficient to describe business rules or
custom validations.

User-Defined Exceptions in Oracle (PL/SQL)

In PL/SQL, you can declare and raise your own exceptions using:

1. DECLARE – to define the exception

2. RAISE – to trigger the exception
3. EXCEPTION – to handle it

Syntax
sql
CopyEdit
DECLARE
custom_exception EXCEPTION;
BEGIN
-- Your logic
IF <condition> THEN
RAISE custom_exception;
END IF;
EXCEPTION
WHEN custom_exception THEN
DBMS_OUTPUT.PUT_LINE('Custom error occurred.');
END;

Example: User-Defined Exception in Oracle

sql
CopyEdit
DECLARE
insufficient_balance EXCEPTION;
v_balance NUMBER := 500;
v_withdrawal NUMBER := 1000;
BEGIN
IF v_withdrawal > v_balance THEN
 RAISE insufficient_balance;
END IF;

v_balance := v_balance - v_withdrawal;

DBMS_OUTPUT.PUT_LINE('New balance: ' || v_balance);

EXCEPTION
WHEN insufficient_balance THEN
DBMS_OUTPUT.PUT_LINE('Error: Insufficient balance for withdrawal.');
END;

User-Defined Error Handling in SQL Server (T-SQL)

In T-SQL, you can't define exceptions by name, but you can throw custom errors using the
THROW or RAISERROR statement.

Using THROW (SQL Server 2012+)

sql
CopyEdit
BEGIN TRY
DECLARE @stock INT = 5;

IF @stock < 10
BEGIN
THROW 50001, 'Stock is below the minimum required level.', 1;
END
END TRY
BEGIN CATCH
PRINT 'Custom Error: ' + ERROR_MESSAGE();
END CATCH
Key Points
Feature Oracle (PL/SQL) SQL Server (T-SQL)
Yes (e.g., my_exception No (uses error
Exception Name
EXCEPTION) numbers/messages)
Raise Statement RAISE THROW or RAISERROR
Custom Messages Yes Yes
Error Handling EXCEPTION ... WHEN TRY...CATCH
Block

 User-defined exceptions are vital for enforcing business logic and ensuring data
integrity.
 Use them when predefined exceptions don’t match the logic you're trying to handle.
 PL/SQL supports named user-defined exceptions, while T-SQL uses custom error
messages with error numbers.

Triggers and Cursors in RDBMS

1. Triggers in RDBMS
A trigger is a stored procedure that automatically executes in response to specific events
on a table or view, such as INSERT, UPDATE, or DELETE.

Triggers help enforce business rules, auditing, and data validation without modifying
application code.

Types of Triggers

1. Implicit Triggers (Automatic Execution)

 These are automatically fired by the database when certain DML operations occur.
 They respond to INSERT, UPDATE, or DELETE operations.
 You do not call them manually — they are implicit.

Example in Oracle:

sql
CopyEdit
CREATE OR REPLACE TRIGGER before_insert_employee
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
:NEW.hire_date := SYSDATE;
END;

Example in SQL Server:

sql
CopyEdit
CREATE TRIGGER trg_after_insert
ON employee
AFTER INSERT
AS
BEGIN
PRINT 'Employee record inserted.'
END

2. Explicit Triggers (Manually Fired – Not Applicable in SQL/PLSQL)

 In some systems or programming contexts, explicit triggers refer to logic that mimics
trigger-like behavior, manually invoked by code.
 Note: In standard RDBMS (Oracle, SQL Server), triggers are always implicitly
fired. There is no "explicit trigger" mechanism like in some programming
languages.
Trigger Execution Timing
Type Description

BEFORE Trigger executes before DML op.

AFTER Trigger executes after DML op.

INSTEAD OF Used mainly on views, replaces the DML action

Triggers:

 Automatically execute on data changes (INSERT/UPDATE/DELETE)

 Types: BEFORE, AFTER, INSTEAD OF
 Used for: Auditing, Enforcing rules, Auto-updates

2. Cursors in RDBMS
A cursor is a pointer to a result set of a query. It allows row-by-row processing of data
from a table or query result, especially useful when the logic can't be handled in a single SQL
statement.

Types of Cursors

Type Description

Implicit Cursor Automatically created by the database for single-row queries like SELECT INTO

Explicit Cursor Defined by the programmer to process multiple rows, manually controlled

1. Implicit Cursor (Automatic)

 Created by default for DML operations (INSERT, UPDATE, DELETE, SELECT INTO)
 Accessed using system attributes like:
o %FOUND
o %NOTFOUND
o %ROWCOUNT
o %ISOPEN

Example (PL/SQL):

sql
CopyEdit
DECLARE
v_name employee.name%TYPE;
BEGIN
SELECT name INTO v_name FROM employee WHERE id = 101;
DBMS_OUTPUT.PUT_LINE('Employee name: ' || v_name);
END;
2. Explicit Cursor (User-Defined)
  You define it for multi-row queries
 Requires 4 steps:
1. DECLARE the cursor
2. OPEN the cursor
3. FETCH rows one by one
4. CLOSE the cursor

Example (PL/SQL):

sql
CopyEdit
DECLARE
CURSOR emp_cursor IS
SELECT name FROM employee;
v_name employee.name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END LOOP;
CLOSE emp_cursor;
END;

Cursors in SQL Server (T-SQL)

Example:

sql
CopyEdit
DECLARE @name VARCHAR(50)
DECLARE emp_cursor CURSOR FOR
SELECT name FROM employee

OPEN emp_cursor
FETCH NEXT FROM emp_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @name
FETCH NEXT FROM emp_cursor INTO @name
END

CLOSE emp_cursor
DEALLOCATE emp_cursor

Cursors:

 Allow row-by-row processing of result sets

 Types:
o Implicit – handled by the system
o Explicit – defined and controlled by the user
Loops in Explicit Cursor (PL/SQL)

When working with explicit cursors in PL/SQL, loops are commonly used to fetch and
process each row from the result set.

PL/SQL supports several looping structures with explicit cursors:

1. Basic Structure of Explicit Cursor with Loop

To process multiple rows using an explicit cursor, you typically follow these steps:

1. DECLARE the cursor

2. OPEN the cursor
3. FETCH each row inside a LOOP
4. EXIT when no more rows (%NOTFOUND)
5. CLOSE the cursor

Example: Using LOOP with Explicit Cursor

sql
CopyEdit
DECLARE
CURSOR emp_cursor IS
SELECT name, salary FROM employee;

v_name employee.name%TYPE;
v_salary employee.salary%TYPE;
BEGIN
OPEN emp_cursor;

LOOP
FETCH emp_cursor INTO v_name, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Salary: ' || v_salary);

END LOOP;

CLOSE emp_cursor;
END;

2. Using WHILE Loop with Cursor

sql
CopyEdit
DECLARE
CURSOR emp_cursor IS
SELECT name FROM employee;
v_name employee.name%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_name;
 WHILE emp_cursor%FOUND LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
FETCH emp_cursor INTO v_name;
END LOOP;

CLOSE emp_cursor;
END;

3. Using FOR Loop with Cursor (Simplified Cursor Loop)

This is the most concise and safest method. You don't need to open, fetch, or close manually
— PL/SQL handles everything.

sql
CopyEdit
DECLARE
CURSOR emp_cursor IS
SELECT name, salary FROM employee;
BEGIN
FOR emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_record.name || ', Salary: ' ||
emp_record.salary);
END LOOP;
END;

Benefits of Cursor FOR Loop:

 Automatically opens, fetches, and closes the cursor

 Cleaner and less error-prone

Cursor Loops Comparison

Loop Manual
Exit Condition Use Case
Type OPEN/FETCH/CLOSE
EXIT WHEN Full control over fetch and
LOOP Yes %NOTFOUND logic
%FOUND in When condition-based looping
WHILE Yes condition needed
Handled by Cleaner code for row-by-row
FOR No
PL/SQL processing

*********************** UNIT-5 COMPLETED*********************************