2021-22 Ranking

DBMS
• A Database Management System (DBMS) is a software system that is
designed to manage and organize data in a structured manner. It
allows users to create, modify, and query a database, as well as
manage the security and access controls for that database.
or

• A database is a collection of interrelated data which helps in the efficient

retrieval, insertion, and deletion of data from the database and
organizes the data in the form of tables, views, schemas, reports,
etc. For Example, a university database organizes the data
about students, faculty, admin staff, etc. which helps in the efficient
retrieval, insertion, and deletion of data from it.

1 1
 2021-22 Ranking

Several Types of DBMS

• Relational DBMS (RDBMS): An RDBMS stores data in tables with

rows and columns, and uses SQL (Structured Query Language) to
manipulate the data.
• Object-Oriented DBMS (OODBMS): An OODBMS stores data as
objects, which can be manipulated using object-oriented
programming languages.
• NoSQL DBMS: A NoSQL DBMS stores data in non-relational data
structures, such as key-value pairs, document-based models, or graph
models.
 2021-22 Ranking

Relational DBMS (RDBMS

•Examples of relational databasesSome of the most

well-known RDBMSs include MySQL,
PostgreSQL, MariaDB, Microsoft SQL Server, and
Oracle Database, IBM’s Db2
 2021-22 Ranking

NoSQL DBMS

• Types of NoSQL Databases

• NoSQL databases use a different approach. Based on
a data model there are a few types of databases in
the NoSQL world. Here are the five main types of
NoSQL databases:
2021-22 Ranking
 2021-22 Ranking

• MongoDB, CouchDB, CouchBase , Amazon SimpleDB,

Riak, Lotus Notes are Document-oriented NoSQL
databases.
• Tokyo Cabinet/Tyrant, Redis, Riak, Voldemort, Oracle
BDB, Amazon SimpleDB are Key-value stores
Databases.
• Cassandra, HBase and Hypertable are Column family
stores Databases.
• Neo4J, InfoGrid, Infinite Graph, OrientDB, FlockDB are
Graph databases. Lets discuss these types of
databases in detail.
• Multimodel dbms – Apache Ignite, Azure cosmos DB
 2021-22 Ranking

Languages used by nosql databases

•NoSQL databases use JSON

( JavaScript Object Notation),
XML, YAML(yet another markup
language ), or binary schema,
facilitating unstructured data.
Key-Values Sto r e
202 1-2 2

s
Ran king
 2021-22 Ranking

• The main idea here is using a hash table where there

is a unique key and a pointer to a particular item of
data. The Key/Value model is the simplest and easiest
to implement. But it is inefficient when you are only
interested in querying or updating part of a value,
among other disadvantages.
• Key-value pair storage databases store data as a
hash table where each key is unique, and the value
can be a JSON, BLOB(Binary Large Objects), string,
etc.
 2021-22 Ranking

Graph based data base

2021-22 Ranking
 Key F
2021- 22
tures of DBMS
• Data modeling: A DBMS provides tools for creating and modifying data models,
which define the structure
R ankin and
g relationships of the data in a database.
e a A DBMS is responsible for storing and retrieving data
• Data storage and retrieval:
from the database, and can provide various methods for searching and querying the
data.
• Concurrency control: A DBMS provides mechanisms for controlling concurrent
access to the database, to ensure that multiple users can access the data without
conflicting with each other.
• Data integrity and security: A DBMS provides tools for enforcing data integrity and
security constraints, such as constraints on the values of data and access controls that
restrict who can access the data.
• Backup and recovery: A DBMS provides mechanisms for backing up and recovering
the data in the event of a system failure.
• DBMS can be classified into two types: Relational Database Management System
(RDBMS) and Non-Relational Database Management System (NoSQL or Non-SQL)
• RDBMS: Data is organized in the form of tables and each table has a set of rows and
columns. The data are related to each other through primary and foreign keys.
• NoSQL: Data is organized in the form of key-value pairs, documents, graphs, or
column-based. These are designed to handle large-scale, high-performance scenarios.
 2021-22 Ranking

Database Languages
• Data Definition Language
• Data Manipulation Language
• Data Control Language
• Transactional Control Language
• Data Query Language
 2021-22 Ranking

What is Schema?

• The Skeleton of the database is created by the

attributes and this skeleton is named Schema.
• Database Schema
• A database schema is a logical representation of
data that shows how the data in a database
should be
stored logically. It shows how the data is
organized and the
relationship between the tables.
 2021-22 Ranking

Types of Database Schemas

• There are 3 types of database schema:
• Physical Database Schema
• A Physical schema defines, how the data or
information is stored physically in the storage
systems in the form of files & indices. This is the
actual code or syntax needed to create the
structure of a database, we can say that when we
design a database at a physical level, it’s called
physical schema.
• The Database administrator chooses where and
how to store the data in the different blocks of
storage.
 2021-22 Ranking

Logical Database Schema

• A logical database schema defines all the logical

constraints that need to be applied to the stored
data, and also describes tables, views, entity
relationships, and integrity constraints.
• The Logical schema describes how the data is
stored in the form of tables & how the attributes of
a table are connected.
• Using ER modelling the relationship between
the components of the data is maintained.
• In logical schema different integrity constraints are
defined in order to maintain the quality of insertion
and update the data.
 2021-22 Ranking

View Database Schema

• It is a view level design which is able to

define the interaction between end-user
and database.
• User is able to interact with the database with the
help of the interface without knowing much about
the stored mechanism of data in database.
2021-22 Ranking
2021-22 Ranking
2021-22 Ranking
 2021-22 Ranking

Data Definition Language

DDL is the short name for Data Definition Language, which deals with
database schemas and descriptions, of how the data should reside in the
database.

• CREATE: to create a database and its objects like (table, index, views, store
procedure, function, and triggers)
• ALTER: alters the structure of the existing database
• DROP: delete objects from the database
• TRUNCATE: remove all records from a table, including all spaces allocated for
the records are removed
• COMMENT: add comments to the data dictionary
• RENAME: rename an object
 2021-22 Ranking

Data Manipulation Language

DML is the short name for Data Manipulation Language which deals
with data manipulation and includes most common SQL statements
such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to
store, modify, retrieve, delete and update data in a database.

• SELECT: retrieve data from a database

• INSERT: insert data into a table
• UPDATE: updates existing data within a table
• DELETE: Delete all records from a database table

• MERGE: UPSERT operation (insert or update)

• CALL: call a PL/SQL or Java subprogram
• EXPLAIN PLAN: interpretation of the data access path
• LOCK TABLE: concurrency Control
 2021-22 Ranking

Data Control Language

DCL is short for Data Control Language which acts as an access

specifier to the database.(basically to grant and revoke permissions to
users in the database

• GRANT: grant permissions to the user for running DML(SELECT,

INSERT, DELETE,…) commands on the table
• REVOKE: revoke permissions to the user for running
DML(SELECT, INSERT, DELETE,…) command on the specified
table
 2021-22 Ranking

Transactional Control Language

TCL is short for Transactional Control Language which

acts as an manager for all types of transactional
data and all [Link] of the command of
TCL are

• Roll Back: Used to cancel or Undo changes made

in the database
• Commit: It is used to apply or save changes in the
database
• Save Point: It is used to save the data on the
temporary basis in the database
 2021-22 Ranking

Advantages of DBMS

• Data organization: A DBMS allows for the organization and

storage of data in a structured manner, making it easy to
retrieve and query the data as needed.
• Data integrity: A DBMS provides mechanisms for enforcing
data integrity constraints, such as constraints on the
values of data and access controls that restrict who
can access the data.
• Concurrent access: A DBMS provides mechanisms for
controlling concurrent access to the database, to
ensure that multiple users can access the data
without conflicting with each other.
• Data security: A DBMS provides tools for managing the security
of the data, such as controlling access to the data and
encrypting sensitive data.
• Backup and recovery: A DBMS provides mechanisms for
backing up and recovering the data in the event of a
system failure.
• Data sharing: A DBMS allows multiple users to access and
share the same data, which can be useful in a
 20 21-22 Ranking

Disadvantag e s of
DBMS
• Complexity: DBMS can be complex to set up and
maintain, requiring specialized knowledge and
skills.
• Performance overhead: The use of a DBMS can add
overhead to the performance of an application,
especially in cases where high levels of
concurrency are required.
• Scalability: The use of a DBMS can limit the scalability
of an application, since it requires the use of
locking and other synchronization mechanisms to
ensure data consistency.
• Cost: The cost of purchasing, maintaining and
upgrading a DBMS can be high, especially for large or
complex systems.
• Limited Use Cases: Not all use cases are suitable for a
DBMS, some solutions don’t need high
reliability, consistency or security and may be
 2021-22 Ranking

Application of DBMS
• There are different fields where a database management system is utilized. Following are a few applications
which utilize the information base administration framework –
1. Railway Reservation System –
In the rail route reservation framework, the information base is needed to store the record or information of
ticket appointments, status about train’s appearance, and flight. Additionally, if trains get late, individuals
become acquainted with it through the information base update.

2. Library Management System –

There are lots of books in the library so; it is difficult to store the record of the relative multitude of books in a
register or duplicate. Along these lines, the data set administration framework (DBMS) is utilized to keep up all
the data identified with the name of the book, issue date, accessibility of the book, and its writer.

3. Banking –
Database the executive’s framework is utilized to store the exchange data of the client in the information base.

4. Education Sector –
Presently, assessments are led online by numerous schools and colleges. They deal with all assessment
information through the data set administration framework (DBMS). In spite of that understudy’s enlistments
subtleties, grades, courses, expense, participation, results, and so forth all the data is put away in the
information base.

5. Credit card exchanges –

The database Management framework is utilized for buying on charge cards and age of month to month
proclamations.
 2021-22 Ranking

1. Social Media Sites –

We all utilization of online media sites to associate with companions and to impart our
perspectives to the world. Every day, many people group pursue these online media accounts
like Pinterest, Facebook, Twitter, and Google in addition to. By the utilization of the data set
administration framework, all the data of clients are put away in the information base and, we
become ready to interface with others.

2. Broadcast communications –
Without DBMS any media transmission organization can’t think. The Database the executive’s
framework is fundamental for these organizations to store the call subtleties and month to
month postpaid bills in the information base.

3. Account –
The information base administration framework is utilized for putting away data about deals,
holding and acquisition of monetary instruments, for example, stocks and bonds in a data set.

4. Online Shopping –
These days, web-based shopping has become a major pattern. Nobody needs to visit the shop
and burn through their time. Everybody needs to shop through web based shopping sites, (for
example, Amazon, Flipkart, Snapdeal) from home. So all the items are sold and added
uniquely with the assistance of the information base administration framework (DBMS).
Receipt charges, installments, buy data these are finished with the assistance of DBMS.
 2021-22 Ranking

1. Human Resource Management –

Big firms or organizations have numerous specialists or
representatives working under them. They store data about
worker’s compensation, assessment, and work with the
assistance of an information base administration framework
(DBMS).

2. Manufacturing –
Manufacturing organizations make various kinds of items and
deal them consistently. To keep the data about their items like
bills, acquisition of the item, amount, inventory network the
executives, information base administration framework (DBMS) is
utilized.

3. Airline
Reservation System –
This framework is equivalent to the railroad reservation framework.
This framework additionally utilizes an information base
administration framework to store the records of flight takeoff,
appearance, and defer status.
[Link]: DBMS is used in healthcare to manage patient data,
medical records, and billing information.
 2021-22 Ranking

[Link] retrieval: DBMS provides a way to retrieve

data quickly and easily using search queries.
2. Data manipulation: DBMS provides tools to
manipulate data, such as sorting, filtering, and
aggregating data.
3. Security: DBMS provides security features to
ensure that only authorized users have access to
the data.
4. Data backup and recovery: DBMS provides tools to
back up data and recover it in case of system
failures or data loss.
5. Multi-user access: DBMS allows multiple users to
access and modify data simultaneously.
6. Reporting and analysis: DBMS provides tools to
generate reports and analyze data to gain insights
 DBMS Archit e 2021 -22
ure 1-level, 2-Level,
• 3 - L e v e l
A D a ta ba se st ores a lot of critical information to
R
c t data quickly and
ankin
access
g
securely. Hence it is important
to select the correct architecture for efficient data
management. DBMS Architecture helps users to get their
requests done while connecting to the database. We choose
database architecture depending on several factors like
the size of the database, number of users, and
relationships between the users. There are two types of
database models that we generally use, logical model and
physical model. Several types of architecture are there in the
database which we will deal with in the next section.
• Types of DBMS Architecture
• There are several types of DBMS Architecture that we use
according to the usage requirements. Types of DBMS
Architecture are discussed here.
• 1-Tier Architecture
• 2-Tier Architecture
• 3-Tier Architecture
 2021-22 Ranking

1-Tier Architecture

• In 1-Tier Architecture the database is directly

available to the user, the user can directly sit on
the DBMS and use it that is, the client, server, and
Database are all present on the same machine. For
Example: to learn SQL we set up an SQL server and
the database on the local system. This enables
us to directly interact with the relational database
and execute operations. The industry won’t
use this architecture they logically go for 2-
tier and 3-tier Architecture.
2021-22 Ranking
2-Tier Archit e c 2 021-2 2

t ure
Ran king

• The 2-tier architecture is similar to a basic

client-server model. The application at the
client end directly communicates with the
database on the server side. APIs like ODBC and
JDBC are used for this interaction. The server side is
responsible for providing query processing and
transaction management functionalities. On the
client side, the user interfaces and application
programs are run. The application on the client side
establishes a connection with the server side to
communicate with the DBMS. An advantage
of this type is that maintenance and
understanding are easier, and compatible with
existing systems. However, this model gives
poor performance when there are a large number
of users.
2021-22 Ranking
 3-Ti e
20 21-

r Architecture
22 Ranking

• In 3-Tier Architecture, there is another layer

between the client and the server. The client
does not directly communicate with the server.
Instead, it interacts with an application server
which further communicates with the database
system and then the query processing and
transaction management takes place. This
intermediate layer acts as a medium for the
exchange of partially processed data between the
server and the client. This type of architecture is
used in the case of large web applications.
2021-22 Ranking
 2021-22 Ranking

Introduction of ER Model

•The Entity Relational Model is a model for identifying

entities to be represented in the database and
representation of how those entities are related.
•The ER data model specifies enterprise
represents schemathat overall logical
the structure of a
•graphically.
The Entity Relationshipdatabase
Diagram explains the relationship
among the entities present in the database.
•ER models are used to model real-world objects like a
person, a car, or a company and the relation between
these real-world objects. In short, the ER Diagram is the
structural format of the database.
 2021-22 Ranking

Why Use ER Diagrams In DBMS?

• ER diagrams are used to represent the E-R
model in a database, which makes them easy to
be converted into relations (tables).
• ER diagrams provide the purpose of real-world
modeling of objects which makes them intently
useful.
• ER diagrams require no technical knowledge and
no hardware support.
• These diagrams are very easy to understand and
easy to create even for a naive user.
• It gives a standard solution for visualizing the data
logically.
 2021-22 Ranking

Symbols Used in ER Model

• ER Model is used to model the logical view of the

system from a data perspective which consists of
these symbols:
• Rectangles: Rectangles represent Entities in the ER
Model.
• Ellipses: Ellipses represent Attributes in the ER Model.
• Diamond: Diamonds represent Relationships
among Entities.
• Lines: Lines represent attributes to entities and
entity sets with other relationship types.
• Double Ellipse: Double Ellipses represent Multi-
Valued Attributes.
• Double Rectangle: Double Rectanglerepresents a
Weak Entity.
2021-22 Ranking
 2021-22 Ranking

Components of ER Diagram
• ER Model consists of Entities, Attributes, and
Relationships among Entities in a Database System.
 2021-22 Ranking

Kinds of Entity:
There are two kinds of entities, which are as
follows:
[Link] Entity: It is an entity in DBMS, which is a physical object that we can
touch or see. In simple words, an entity that has a physical existence in the real world is
called a tangible entity.
For example, in a database, a table represents a tangible entity because it contains a
physical object that we can see and touch in the real world. It includes colleges, bank
lockers, mobiles, cars, watches, pens, paintings, etc.
An Entity may be an object with a physical existence – a particular person, car, house,
or employee – or it may be an object with a conceptual existence – a company, a job, or
a university course.
2. Intangible Entity:
It is an entity in DBMS, which is a non-physical object that we
cannot see or touch. In simple words, an entity that does not have
any physical existence in the real world is known as an intangible
entity.
For example, a bank account logically exists, but we cannot see or
touch it.
Entity Set: An Entity is an object of Entity Type and a set of all entities is called an
entity set.
For Example, E1 is an entity having Entity Type Student and the set of all students is
2021-22 Ranking
 2021-22 Ranking

• 1. Strong Entity
• A Strong Entity is a type of entity that has a key Attribute. Strong Entity
does not depend on other Entity in the Schema. It has a primary key,
that helps in identifying it uniquely, and it is represented by a
rectangle. These are called Strong Entity Types.
• 2. Weak Entity
• An Entity type has a key attribute that uniquely identifies each entity in the
entity set. But some entity type exists for which key attributes can’t
be defined. These are called Weak Entity types.
• For Example, A company may store the information of dependents
(Parents, Children, Spouse) of an Employee. But the dependents don’t
have existed without the employee. So Dependent will be a
Weak Entity Type and Employee will be Identifying Entity type for
Dependent, which means it is Strong Entity Type.
• A weak entity type is represented by a Double Rectangle. The participation
of weak entity types is always total. The relationship between the
weak entity type and its identifying strong entity type is called
identifying relationship and it is represented by a double diamond.
2021-22 Ranking
Attributes
2021-22 Ranking

• Attributes are the properties that define the entity

type. For example, Roll_No, Name, DOB, Age,
Address, and Mobile_No are the attributes that
define entity type Student. In ER diagram, the
attribute is represented by an oval.
 2021-22 Ranking

• 1. Key Attribute
• The attribute which uniquely identifies each entity in
the entity set is called the key attribute. For
example, Roll_No will be unique for each student.
In ER diagram, the key attribute is represented by
an oval with underlying lines.
 2021-22 Ranking

2. Composite Attribute

• An attribute composed of many other attributes is called

a composite attribute. For example, the Address
attribute of the student Entity type consists of
Street, City, State, and Country. In ER diagram,
the composite attribute is represented by an
oval comprising of ovals.
2021-22 Ranking
 2021-22 Ranking

• 3. Multivalued Attribute
• An attribute consisting of more than one value for
a given entity. For example, Phone_No (can be more
than one for a given student). In ER diagram, a
multivalued attribute is represented by a double
oval.
 2021-22 Ranking

• 4. Derived Attribute
• An attribute that can be derived from other
attributes of the entity type is known as a derived
attribute. e.g.; Age (can be derived from DOB). In ER
diagram, the derived attribute is represented by a
dashed oval.
 2021-22 Ranking

The Complete Entity Type Student

with its Attributes can be
represented as:
 Relationship T y pe and Relationship
Set
2021 -22 Ra nking

• A Relationship Type represents the association

between entity types. For example, ‘Enrolled in’ is a
relationship type that exists between entity type
Student and Course. In ER diagram, the relationship
type is represented by a diamond and connecting
the entities with lines.
 2021-22 Ranking

• A set of relationships of the same type is known as

a relationship set. The following relationship set
depicts S1 as enrolled in C2, S2 as enrolled in C1,
and S3 as registered in C3.
2021-22 Ranking
 2021-22 Ranking

• Degree of a Relationship Set

• The number of different entity sets
participating in a relationship set is called the
degree of a relationship set.
• 1. Unary Relationship: When there is only ONE entity
set participating in a relation, the relationship is
called a unary relationship. For example, one
person is married to only one person.
2021-22 Ranking
 2021-22 Ranking

• 2. Binary Relationship: When there are TWO entities

set participating in a relationship, the relationship
is called a binary relationship. For example, a
Student is enrolled in a Course.
 2021-22 Ranking

• 3. n-ary Relationship: When there are n entities

set participating in a relation, the relationship is
called an n-ary relationship.
• Cardinality
•The number of times an entity of an entity set
participates in a relationship set is known as
cardinality. Cardinality can be of different types:
• 1. One-to-One: When each entity in each entity set can
take part only once in the relationship, the cardinality
is one-to-one. Let us assume that a male can
marry one female and a female can marry one male.
So the relationship will be one-to-one.
• the total number of tables that can be used in
this is 2.
2021-22 Ranking
 2021-22 Ranking

• 2. One-to-Many: In one-to-many mapping as well

where each entity can be related to more than
one relationship and the total number of tables that
can be used in this is 2. Let us assume that
one surgeon deparment can accomodate many
doctors. So the Cardinality will be 1 to M. It means
one deparment has many Doctors.
• total number of tables that can used is 3.
2021-22 Ranking
 2021-22 Ranking

• 3. Many-to-One: When entities in one entity set can

take part only once in the relationship set and
entities in other entity sets can take part more than
once in the relationship set, cardinality is many to
one. Let us assume that a student can take only one
course but one course can be taken by many
students. So the cardinality will be n to 1. It means
that for one course there can be n students but
for one student, there will be only one course.
• The total number of tables that can be used in this is
3.
2021-22 Ranking
 2021-22 Ranking

• 4. Many-to-Many: When entities in all entity sets can

take part more than once in the relationship
cardinality is many to many. Let us assume that a
student can take more than one course and one
course can be taken by many students. So the
relationship will be many to many.
• the total number of tables that can be used in this is
3.
2021-22 Ranking
2021-22 Ranking
 2021-22 Ranking

• In this example, student S1 is enrolled in C1 and

C3 and Course C3 is enrolled by S1, S3, and S4. So it is
many-to-many relationships.
• Participation Constraint
• Participation Constraint is applied to the entity
participating in the relationship set.
• 1. Total Participation – Each entity in the entity set
must participate in the relationship. If each student
must enroll in a course, the participation of
students will be total. Total participation is shown
by a double line in the ER diagram.
• 2. Partial Participation – The entity in the entity set may
or may NOT participate in the relationship. If some
courses are not enrolled by any of the students, the
participation in the course will be partial.
• The diagram depicts the ‘Enrolled in’ relationship set
with Student Entity set having total participation and
Course Entity set having partial participation.
2021-22 Ranking
2021-22 Ranking
 2021-22 Ranking

How to Draw ER Diagram?

• The very first step is Identifying all the Entities,
and place them in a Rectangle, and labeling them
accordingly.
• The next step is to identify the relationship
between them and pace them accordingly using
the Diamond, and make sure that, Relationships are
not connected to each other.
• Attach attributes to the entities properly.
• Remove redundant entities and relationships.
• Add proper colors to highlight the data present in
the database.
Difference bet w e e n entity, entity set
20 21-22 R ankin g

and entity type

• it is used for the modeling of the logical view of the system from a data
perspective. The entity, Entity Set, and Entity Type all these terms are
very important concepts of ER Model. In this article, we will
understand the diff erence between them.
• 1. Entity : An entity is a thing in a real-world with independent existence.
An entity can exist independently and is distinguishable from
other objects. It can be identified uniquely.
• An entity can be of two types :
• Tangible Entity : Entities that exist in the real world physically. Example:
Person, car, etc.
• Intangible Entity : Entities that exist only logically and have no physical
existence. Example: Bank Account, etc.
• A student with a particular roll number is an entity.
• A company with a particular registration number is an entity.
• Note :
• An entity may be concrete like a student, a book, or abstract like a
holiday or a particular concept.
• An entity is represented by a set of attributes.
• In a particular relation in RDBMS, a particular record is called an
entity.
1 1
 2021-22 Ranking

• 2. Entity Type : It refers to the category that a

particular entity belongs to.

Example :
•A table named student in a university database.
•A table named employee in a company database.
Note :
• The category of a particular entity in the relation in
RDBMS is called the entity type.
•It is represented by the name of the table and its schema.
 2021-22 Ranking

• 3. Entity Set : An entity set is a collection or set of

all entities of a particular entity type at any point
in time. The type of all the entities should be the
same.
 2021-22 Ranking

• Example :
• The collection of all the students from the student
table at a particular instant of time is an example of
an entity set.
• The collection of all the employees from the
employee table at a particular instant of time is an
example of an entity set.
• Note :
• Entity sets need not be disjoint. For example, the
entity set of Article Writer (all content creators for
GeeksforGeeks) and the entity set of Article Reader
(all students who read the article of
GeeksforGeeks) may have members in common.
• The collection of all the entities in the relation of
RDBMS is called an entity set.
 2021-22 Ranking

• Relation With Table :

• Consider a table student as follows :
• Table Name : Student
Student_ID Student_Name Student_Age Student_Gender
1 Avi 19 M

2 Ayush 23 M

3 Nikhil 21 M

4 Riya 16 F
 2021-22 Ranking

Entity Entity Type Entity Set

A thing in the real world with A category of a particular entity Set of all entities of a particular entity
independent existence type.

Any particular row (a record) in a The name of a relation (table) in All rows of a relation (table) in RDBMS
relation(table) is known as an entity. RDBMS is an entity type is entity set
 2021-22 Ranking

Key
s
• It is used to uniquely identify any record or row
of data from the table. It is also used to
establish and identify relationships between
tables.
 2021-22 Ranking

Different Types of Database Keys

• Candidate
Key
• Primary Key
• Super Key
• Alternate
Key
• Foreign Key
• Composite
Key
 2021-22 Ranking

Candidate Key

• The minimal set of attributes that can uniquely identify a

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

Primary Key
• There can be more than one candidate key in
relation out of which one can be chosen as the
primary key.
• It is a unique key.
• It can identify only one tuple (a record) at a time.
• It has no duplicate values, it has unique values.
• It cannot be NULL.
• Primary keys are not necessarily to be a single
column; more than one column can also be a
primary key for a table.
2021-22 Ranking
 2021-22 Ranking

Super Key
• A super key is a combination of all possible
attributes which can uniquely identify two tuples in
a table.
• Super set of any candidate key is super key.
• A super key is a group of single or multiple keys
• that identifies
Adding zero orrows
moreinattributes
a table. Itto
supports
the NULL
values.
candidate
generates key
the super
• key.
A candidate key is a super key but vice versa is
not true.
• Super Key values may also be
NULL.
 2021-22 Ranking

• For eg,
Candidate
key
Roll no. Name Age
 2021-22 Ranking

•Here roll no. is the candidate key.

•Roll no.+ name = Super key
•Roll no.+ age =Super key
•Roll no.+ name + age= Super key
•But name + age cannot be Super key because
name and age are not candidate keys and to
be a super key there has to be at least one
candidate key in it.
2021-22 Ranking
 2021-22 Ranking

Foreign key

•It is an attribute or set of attributes that references

to the primary key of another table (Relation).
•Foreign keys are the column of the
table used to point to the primary
key of another table.
•The base table has the primary key.
(Referenced
table
•)The another table has the foreign key.
(Referencing
table
• )There can be more than one foreign key in a
table.
• The name of the primary key and foreign key
• can be different.
It maintains referential integrity. (we cannot
2021-22 Ranking
2021-22 Ranking
 2021-22 Ranking

Alternate Key

• The total number of the alternate keys is the total

number of candidate keys minus the primary key.
• The alternate key may or may not exist. If there is
only one candidate key in a relation, it does not
have an alternate key.
• For example, employee relation has two
attributes, Employee_Id and PAN_No, that act as
candidate keys. In this relation, Employee_Id is
chosen as the primary key, so the other candidate
key, PAN_No, acts as the Alternate key.
2021-22 Ranking
 2021-22 Ranking

Composite
key a table might not have a single column/attribute that uniquely
• Sometimes,
identifies all the records of a table. To uniquely identify rows of a table,
a combination of two or more columns/attributes can be used. It still
can give duplicate values in rare cases. So, we need to find the
optimal set of attributes that can uniquely identify rows in a table.
• It acts as a primary key if there is no primary key in a table
• Two or more attributes are used together to make a composite key.
• Different combinations of attributes may give different accuracy in terms
of identifying the rows uniquely.
• Whenever a primary key consists of more than one
attribute,
it is known as a composite
key. eg; full name + dob can be combined to
• For
access
details of thea
student.
2021-22 Ranking
2021-22 Ranking
 2021-22 Ranking

Transaction Control in DBMS

• The transaction is a single logical unit that accesses and modifies the
contents of the database.
• Transactions access data using read and write operations.
• Transaction is a single operation of processing that can have many
operations.
• Transaction is needed when more than one user wants to access
same database. Transaction has ACID properties.

1 1
2021-22 Ranking
 2021-22 Ranking

ACID Properties of Transaction:

•Atomicity: All the operations in a transaction are considered to be
atomic and as one unit. If system fails or any read/write conflicts
occur during transaction the system needs to revert back to its
previous state. Atomicity is maintained by the Transaction
Management Component.
 2021-22 Ranking

• Consistency: Every transaction should lead to database connection from

one valid state to other valid state. If system fails because of invalid
data while doing an operation revert back the system to its
previous state. Consistency is maintained by the Application manager.
 2021-22 Ranking

•Isolation: If multiple transactions are executing on single

database, each transaction should be isolated from
other transaction. If multiple transactions are performed
on single database, operation from any
transaction should not interfere with operation in
other transaction. Isolation is maintained by the
concurrency control manager.
 2021-22 Ranking

• Durability: Durability means the changes made during the transactions

should exist after completion of transaction. Changes must be
permanent and must not be lost due to any database failure. It is
maintained by the recovery manager.
2021-22 Ranking
 2021-22 Ranking

Example:
• A has an account with an amount of Rs 150. B has an
account with an amount of Rs 50. A is transferring amount
Rs 100 to B’s account.
1. Atomicity: Operations required for transfer are: Deduct
amount Rs100 from A’s account. Add amount Rs 100 to
B’s account. All operations should be done. If system fails
to add amount in B’s account after deducting from A’s
account, revert the operation on A’s account.
2. Consistency: The sum amount in A’s account and B’s account
should be same before and after the transaction completes.
In the example the sum of both account before and after
transaction is Rs 200, which preserves the consistency.
3. Isolation: If there is any other transaction (let between A
and C) is going on, it should not affect the transaction
between A and B i.e., both the transactions should be
isolated.
4. Durability: It may happen system gets crashed after the
completion of all operations then, after restarting it
should preserve all the changes. The amount in A’s and B’s
account should be same before and after the system restart.
 2021-22 Ranking

Schedule:
• Schedule is process of grouping transactions into
one and executing them in a predefined order. It is
a sequence of execution of operation from various
transactions. Schedule is required in database
because when multiple transactions execute in
parallel, they may affect the result of each other. So,
to resolve this the order of the transactions are
changed by creating a schedule.
• Types of Schedules:
1. Serial Schedule: A schedule in which the transactions
are defined to execute one after another is called
serial schedule.
2. Non- Serial Schedule: A schedule in which the
transactions are defined to execute in any order
is called non-serial schedule.
 2021-22 Ranking

Concurrency Control:
• Two basic concurrency control protocols:
1. Lock Based Protocol: In this protocol the data item to
be accessed is locked by the first transaction. After
performing operations transaction unlocks the data
item, so that it can be accessed by other
transactions. When there is a lock in a data item
other transactions can read it.
2. Time Stamp Based Protocol: Time Stamp Based
Protocol uses time stamp to serialize the
execution of concurrent transactions. This protocol
ensures that every conflicting read and write
operations are executed in timestamp order. The
protocol uses the system time as a timestamp.
Serializability in DBMS
2021-22 Ranking

• What is a serializable schedule, and what is it used for?

• If a non-serial schedule can be transformed
into its corresponding serial schedule, it is said to
be serializable. Simply said, a non-serial schedule
is referred to as a serializable schedule if it
yields the same results as a serial timetable.
 2021-22 Ranking

• Non-serial Schedule
•A schedule where the transactions are
overlapping or switching places. As they are
used to carry out actual database operations,
multiple transactions are running at once. It’s
possible that these transactions are focusing on
the same data set. Therefore, it is crucial that
non-serial schedules can be serialized in order for
our database to be consistent both before and
after the transactions are executed.
 2021-22 Ranking

Transaction-1 Transaction-2

R(a)
W(a)
R(b)
W(b)
R(b)
R(a)
W(b)
W(a)
 2021-22 Ranking

• We can observe that Transaction-2 begins its

execution before Transaction-1 is finished, and they
are both working on the same data, i.e., “a” and “b”,
interchangeably. Where “R”-Read, “W”-Write
 2021-22 Ranking

• Serializability testing
• We can utilize the Serialization Graph or Precedence
Graph to examine a schedule’s serializability. A
schedule’s full transactions are organized into a
Directed Graph, what a serialization graph is.
 2021-22 Ranking

Types of Serializability
• There are two ways to check whether any
non-serial schedule is serializable.
 1. Conflict serializability
2021-22 Ranking

• Conflict serializability refers to a subset of

serializability that focuses on maintaining the
consistency of a database while ensuring that
identical data items are executed in an order. In a
DBMS each transaction has a value and all the
transactions, in the database rely on this
uniqueness. This uniqueness ensures that no two
operations with the conflict value can occur
simultaneously.
• For example lets consider an order table and a
customer table as two instances. Each order is
associated with one customer even though a single
client may place orders. However there are
restrictions for achieving conflict serializability in
the database. Here are a few of them.
[Link] transactions should be used for the two
procedures.
[Link] identical data item should be present in both
 2021-22 Ranking

• Example
• Three transactions—t1, t2, and t3—are active on a
schedule “S” at once. Let’s create a graph of
precedence.
Transaction – 1 (t1) Transaction – 2 (t2) Transaction – 3 (t3)
R(a)
R(b)
R(b)
W(b)
W(a)
W(a)
R(a)
W(a)
 2021-22 Ranking

• It is a conflict serializable schedule as well as a

serial schedule because the graph (a DAG) has no
loops. We can also determine the order of
transactions because it is a serial schedule.
 2021-22 Ranking

• As there is no incoming edge on Transaction 1,

Transaction 1 will be executed first. T3 will run
second because it only depends on T1. Due to its
dependence on both T1 and T3, t2 will finally be
executed.
• Therefore, the serial schedule’s equivalent order is:
t1 –> t3 –> t2
• Note: A schedule is unquestionably consistent if
it is conflicting serializable. A non-conflicting
serializable schedule, on the other hand, might or
might not be serial. We employ the idea of View
Serializability to further examine its serial
behavior.
 2021-22 Ranking

Example of Conflict
T1 T2 T3
Serializability
R(A)
R(B)
R(A)
R(B)
R(C)
W(B)
W(C)
R(C)
W(A)
W(C)
 2021-22 Ranking

• STEP 1
• Check conflict pairs in other transactions and draw edges.

• Step 2
• Precedence graph

• Step 3
• Check IN degree zero of the graph
• i.e any node having no edge coming towards it.
 2021-22 Ranking

Conflict pairs T1 T2 T3
are-WRITE
•READ R(A)
•WRITE -READ R(B)
•WRITE- WRITE
R(A)
R(B)
R(C)
W(B)
W(C)
R(C)
W(A)
W(C)
 2021-22 Ranking

If there is no loop or cycle in the precedence graph then

it is said to be conflict serializable.
Meaning it could be serialized by removing the
conflicts.
• Step 3 (to check which will be the conflict
equivalent schedule)
Check IN degree zero of the graph
i.e any node having no edge coming towards it.
 So here we have T2’s
indegree zero .
 2021-22 Ranking

•Delete T2 from graph and then check indegree

zero of remaining graph, so here T3 has indegree
zero.
•Remove T3 from graph.
 2021-22 Ranking

• This will be the conflict equivalent schedule.

Check wheather this schedule
2021-22 Ranking

is conflict serializable or not ??

T1 T2 T3

R(A)

W(A)

W(A)

W(A)
 2021-22 Ranking

Not conflict serializable because

there is a loop in a precedence
graph.
 View Serializability
2021-22 Ranking

• View serializability is a kind of operation in a

serializable in which each transaction should
provide some results, and these outcomes are the
output of properly sequentially executing the data
item. The view serializability, in contrast to conflict
serialized, is concerned with avoiding database
inconsistency. The view serializability feature of
DBMS enables users to see databases in
contradictory ways.
 2021-22 Ranking

T1 T2 T3

R(A)

W(A)

W(A)

W(A)
 2021-22 Ranking

T1 T2 T3

R(A)

W(A)

W(A)

W(A)
 View Serializability in DBMS
2021-22 Ranking

• Need of View-Serializability
• There may be some schedules that are not Conflict-
Serializable but still gives a consistent result because
the concept of Conflict- Serializability becomes limited
when the Precedence Graph of a schedule contains a
loop/cycle. In such a case we cannot predict whether a
schedule would be consistent or inconsistent. As per the
concept of Conflict-Serializability, We can say that a
schedule is Conflict-Serializable (means serial and
consistent) if its corresponding precedence graph does
not have any loop/cycle.
• But, what if a schedule’s precedence graph contains a
cycle/loop and is giving consistent result/accurate
results as a conflict serializable schedule is giving?

• So, to address such cases we brought the concept

of View- Serializability because we did not want to confine
the concept of serializability only to Conflict-
 T1 2021-22 Ranking
T2 T3
a=100
read(a)

a=a-40
write(a) //60

a=a-40
write(a) //20

a=a-20
write(a) //0
2021-22 Ranking
T1 T2 T3
2021-22 Ranking

R(A) 100

W(A)
A=A-40

W(A)
A=A-40

W(A)
A=A-20
 2021-22 Ranking

• Now we can say that both are equivalent .

 2021-22 Ranking

• In the above example if we do swapping among

some transaction’s operation so our table will
look like this –
T1 T2 T3
a=100
read(a) //100

a=a-40
write(a) //60

a=a-40
write(a) //20

a=a-20
write(a) //0
2021-22 Ranking
 2021-22 Ranking

• Now, we see that the precedence graph of the second table does not contain
any cycle/loop, which means it is conflict serializable (equivalent to serial
schedule, consistent) and the final result is coming the same as the first
table.

• Note: In the above example we understood that if a schedule is Conflict-

serializable so we can easily predict that It would be –

• Equivalent to a serial schedule,

• Consistent,
• And also a View-Serializable.
• But what if it is non-conflict serializable (precedence graph contains loop). In
this situation, we cannot predict whether it is consistent and serializable or
not. As we look in the above example, where the precedence graph of
Schedule S1 was giving consistent results, equivalent to the serializable
result of Schedule S’1, despite containing cycles/loops. So, to address the
limitation of the Conflict-Serializability concept View-Serializability
method came into the picture.
 2021-22 Ranking

• Problem: Prove whether the given schedule is View-Serializable or

not.

• S' : read1(A), write2(A), read3(A), write1(A), write3(A)

 2021-22 Ranking

T1 T2 T3

read(a)

write(a)

read(a)

write(a)

write(a)
 2021-22 Ranking

• First, we check whether it is Conflict-Serializable or not, because if it

is Conflict-Serializable so it will also be View-Serializable, so we will
make a precedence graph for the schedule S’.
• Here we will check whether the Schedule s’ contains any blind write.
We found that the schedule s’ contains a blind-write write2(a) in
transaction T2. Hence schedule S’ may or may not be View-
Serializable. So we will look at another method. Because, if it does
not contain any Blind-write, we can surely state that the schedule
would not be View-Serializable.
• Now, we will draw a dependency graph that is different from the
precedence graph.
2021-22 Ranking
 2021-22 Ranking

•Transaction T1 first reads data_item

“a” and transaction T2 first
updates(write) “a”.
•So, the transaction T1 must execute
before T2.
• In that way, we get the dependency
(T1 → T2) in the graph.
• And, the final update(write) on “a” is
made by transaction T3.
•So, transaction T3 must execute after
all the other transactions(T1, T2).
•Thus, we get the dependency (T1, T2) → T3 in the
graph shown below:
2021-22 Ranking
 2021-22 Ranking

• Normalization is a process of dividing large tables into smaller ones

and defining relationships between them. The main aim of
normalization is to reduce and eliminate anomalies (insertion,
update, and deletion anomalies) that can lead to inconsistencies in
the database.

• The goal of normalization is to create a set of relations that:

1. Are free from undesirable insertion, update, and deletion anomalies.
2. Preserve the consistency and integrity of the data.
3. Make it easier to maintain and extend the database structure
over time.

1 1
 2021-22 Ranking

•Row level duplicity (when more than one rows are exactly
same ) – set a primary key to remove row level duplicacy.

Roll no. Name Subject Teacher

1 Ram DBMS Amar sir

2 Shyam OS Reena mam

3 Arjun DBMS Amar sir

1 Ram DBMS Amar sir

 2021-22 Ranking

Column level
duplicity
Roll no. Name Subject Teacher Contact no.

1 Ram CN Amar sir 1234567

2 Shyam OS Reena mam 9876543

3 Arjun C++ Hemant sir 4561230

4 Kavita CN Amar sir 1234567

• Now if we want to add(insert )a new subject for eg; java in this table
then we cannot add it because there will be no information about
the students name and id which will lead to insertion anomaly.
 2021-22 Ranking

• If we want to delete the data of Roll no 2. then we can write a

to delete it ..but it will lead to deletion of other important data from
query
the table like OS subject and the teacher teaching that subject.
• And in some cases only one student was studying that subject so the
subject details also get deleted which leads to deletion anomaly.

• Now if I want to update the contact number of amar sir it will

the
causeupdation of his contact number every
where in the table repeatedly due toupdation
anomaly.
column level duplicity leading to
2021-22 Ranking
 2021-22 Ranking

Consider the table 1 shown below:

Full Institute Courses Result
Name
Naveen IIT Delhi DBMS, OS Pass
Kumar
Utkarsh IIT CN. COA Fail
Tiwari Bombay
Utkarsh IIT Kanpur OS Fail
Tiwari
Rules of 1st Normal Form
2021-22 Ranking

• single valued attributes) -Each table should contain

a single value.
• Each record needs to be unique.
Table 1 after applying 1st
2021-22 Ranking

Normal Form:
Full Name Institute Subject Result

Naveen Kumar IIT Delhi DBMS Pass

Naveen Kumar IIT Delhi OS Pass

Utkarsh Tiwari IIT Bombay CN Fail

Utkarsh Tiwari IIT Bombay COA Fail

Utkarsh Tiwari IIT Kanpur OS Fail

 2021-22 Ranking

Consider the table 2 shown below,

Full Name Institute Subject Result
Utkarsh Tiwari IIT Bombay COA Fail
Utkarsh Tiwari IIT Kanpur OS Fail

Here, People having the same name are from different

institutes. So, we require Full Name and Institute to Identify a
Row of the database. For this, we have to remove Partial
Dependency.
 2021-22 Ranking

Rules of 2nd Normal Form

• The table should be in 1NF.

• Primary Key does not functionally dependent on any

subset of Candidate Key. (No partial dependency)
dependen
only full
cy
• All the non unique attributes should be fully
dependent on candidate key .

• This is partial dependency -L.H.S should be a

proper subset of C.K and R.H.S should be a non
prime attribute.
 2021-22 Ranking

Table 1
ID Full Name Institute Result
1 Naveen Kumar IIT Delhi Pass
2 Utkarsh Tiwari IIT Bombay Fail
3 Utkarsh Tiwari IIT Kanpur Fail

Table 2
ID Subject
1 DBMS

1 OS

2 CN

2 COA

3 OS
 2021-22 Ranking

•Now, we have to remove Transitive Functional

Dependency from our Table to Normalize
our Database
Rules of 3rd Normal Form
2021-22 Ranking

• The tables should be in 2NF.

• There will be no Transitive Dependency.

•(x->y->z) no non prime attribute can

determine a non prime attribute.

• X is a prime attribute
• Y is a non prime attribute
• Z is a non prime attribute
 2021-22 Ranking

F.D

roll no.  name (prime attribute roll no. determines name which is a non prime attribute)
name  age (non prime attribute name determines age which is also a non prime attribute)

This is called transitive dependency.

Roll no. Name Age

1 A 20
2 B 21
3 B 21
 2021-22 Ranking
• Table 1
ID Full Name Institute Result_ID
1 Naveen Kumar IIT Delhi 1
2 Utkarsh Tiwari IIT Bombay 2
3 Utkarsh Tiwari IIT Kanpur 2

Table 2
Table 3
ID Subject
Result_ID Result
1 DBMS

1 OS 1 Pass

2 CN 2 Fail
2 COA
3 On Hold
3 OS
 2021-22 Ranking

BCNF

• Left hand side must be candidate key or super key.

XY
 2021-22 Ranking

Exam
•ple
Consider a table StudentCourse with the following columns and
functional dependencies:

• StudentID (Primary Key)

• CourseID (Primary Key)
• Instructor

Functional dependencies:
• StudentID, CourseID → Instructor
• Instructor → CourseID
check if this table is in BCNF.
 2021-22 Ranking

Step 1: Check for BCNF

Violations
•The first functional dependency, StudentID,
CourseID Instructor, is fine because StudentID,
→
CourseID is a super key (it's the primary key of the
table).
•The second functional dependency, Instructor →
CourseID, is a problem because Instructor is not
a super key.
Step 2: Decomp o s e to 202 1-22 Ran king

Achieve
• BCNF
To decompose the table and achieve BCNF, we need to split it into two
tables that resolve the violation. We create two new tables:

InstructorCourse Table:

• Instructor (Primary Key)

• CourseID

StudentCourseInstructor Table:

• StudentID (Primary Key)

• CourseID (Primary Key)
• Instructor
Functional Dependencies After
2021-22 Ranking

Decomposition
InstructorCourse:

• Instructor → CourseID (Now, Instructor is a primary key, so this is in

BCNF)

StudentCourseInstructor:

• StudentID, CourseID → Instructor (Primary key is StudentID,

CourseID, so this is in BCNF)
 2021-22 Ranking

4th normal form

•No multivalued dependency

 2021-22 Ranking

5th normal form

•Lossless decomposition
 2021-22 Ranking

Closure method

•Closure means what one attribute can determine.

 2021-22 Ranking

Q1. Find which is the candidate key?

R(ABCD )
FD { AB, BC, CD}
 2021-22 Ranking

Solution

R(ABCD )
FD { AB, BC, CD}
A + = BCDA (C.K because a can determine all
attributes of the table.)
B+ = (not a candidate key)
BCD C+ (not a candidate key)
=CD (not a candidate key)
D+ = D
 2021-22 Ranking

Find which is the candidate key?

Q2. R(ABCD )
FD { AB, BC, CD, DA}
 2021-22 Ranking

Solution

A + = ABCD (C.K because a can determine all attributes of the

table.)
B+ = BCDA (C.K because a can determine all attributes of the
table.)
C+ =CDAB (C.K because a can determine all attributes of the
table.)
D+ = DABC (C.K because a can determine all attributes of the
table.)
C.K ={ ABCD}
Check the highest normal form in
2021-22 Ranking

which this relation is??

R(ABCDEF)
FD{ ABC, CDE, EF , FA}
 Check the highest
2021-22 Ranking

normal form?
Step 1 Find the candidate keys through closure method.
AB+ = { ABCDEF} … AB is a candidate key (but check first if it a super key?)
FD{ ABC, CDE, EF , FA}

C.K( AB)
Check if A is on the right side of any attribute?
FA
Replace A with F
C.K( FB) …check FB CLOSURE …FB+ = FBACDE
Check if F is on the right side of any attribute?
EF
Replace E with F
C.K( EB) …..CHECK EB CLOSURE … EB+ =
EBFACD
Check if E is on the right side of any attribute?
CDE
Replace E with C
C.K(CB)….check CB closure… CB+ = CBDEFA
Check if C is on the right side of any attribute?
ABC
AB closure we have already checked.
 2021-22 Ranking

• So total of 4 candidate keys are determined.

Step 2 : Find all prime attributes from the candidate
keys.
C.K {AB,FB,EB,CB}
{A,B,C,E,F}

Step 3: find the non prime attributes.

{D}
Now, check for BCNF
FD{ ABC, CDE, EF , FA}
 2021-22 Ranking

Question 2

R(ABCD)
FD= ABC, BCD

FIND THE HIGHEST NORMAL FORM FOR

THIS RELATION SCHEMA?
 2021-22 Ranking

Answer: 2NF

• AB+ ={A,B,C,D} ………….C.K

• Prime attributes = {A,B}
• BC+ ={B,C,D} ……………not a C.K
• Check for highest normal form

ABC BCD

BCNF BC is not a
super key
3NF D is not a
prime attribute
2NF BC is not a
proper subset of AB
 2021-22 Ranking

• Example 1. Find the highest normal form of a relation

• R(A,B,C,D,E) with
• FD set { A->D, B->A, BC->D, AC->BE }
 2021-22 Ranking

• Step 1. As we can see, (AC)+ ={A, C, B, E, D} but none of its subsets

can determine all attributes of relation, So AC will be the candidate key.
A can be derived from B, so we can replace A in AC with B. So BC will
also be a candidate key. So there will be two candidate keys {AC, BC}.
• Step 2. The prime attribute is those attribute which is part of candidate
key {A, B, C} in this example and others will be non-prime {D, E} in
this example.
• Step 3. The relation R is in 1st normal form as a relational DBMS does
not allow multi-valued or composite attributes.
• The relation is not in the 2nd Normal form because A->D is partial
dependency (A which is a subset of candidate key AC is determining
non-prime attribute D) and the 2nd normal form does not allow partial
dependency.
• So the highest normal form will be the 1st Normal Form.
 2021-22 Ranking

• Example 2. Find the highest normal form of a

relation
• R(A,B,C,D,E) with
• FD set as {BC->D, AC->BE, B->E}
 2021-22 Ranking

• Step 1. As we can see, (AC)+ ={A,C,B,E,D} but none of its subsets can
determine all attributes of relation, So AC will be the candidate key. A or C
can’t be derived from any other attribute of the relation, so there will
be only 1 candidate key {AC}.
• Step 2. The prime attribute is those attribute which is part of candidate
key
{A,C} in this example and others will be non-prime {B,D,E} in this example.
• Step 3. The relation R is in 1st normal form as a relational DBMS does not
allow multi-valued or composite attributes.
• The relation is in 2nd normal form because BC->D is in 2nd normal form (BC is
not a proper subset of candidate key AC) and AC->BE is in 2nd normal form
(AC is candidate key) and B->E is in 2nd normal form (B is not a proper
subset of candidate key AC).
• The relation is not in 3rd normal form because in BC->D (neither BC is a super
key nor D is a prime attribute) and in B->E (neither B is a super key nor E
is a prime attribute) but to satisfy 3rd normal for, either LHS of an FD
should be super key or RHS should be a prime attribute.
• So the highest normal form of relation will be the 2nd Normal form.
Indexing in
sql
Indexin
g
Indexing is a way to optimize the performance of a database by minimizing
the number of disk accesses required when a query is processed. It is a data
structure technique which is used to quickly locate and access the data in a
database.

Indexes are created using a few database columns.

The first column is the Search key that contains a copy of the primary key or
candidate key of the table. These values are stored in sorted order so that the
corresponding data can be accessed quickly.
Note: The data may or may not be stored in sorted order.
The second column is the Data Reference or Pointer which contains a set of
pointers holding the address of the disk block where that particular key
value can be found.
Indexin
g
Indexin
g
The indexing has various attributes:

Access Types: This refers to the type of access such as value based
search, range access, etc.
Access Time: It refers to the time needed to find particular data
element or set of elements.
Insertion Time: It refers to the time taken to find the appropriate
space and insert a new data.
Deletion Time: Time taken to find an item and delete it as well as
update the index structure.
Space Overhead: It refers to the additional space required by the
index.
Types of file organization
mechanism
In general, there are two types of file organization mechanism
which are followed by the indexing methods to store the data.
1. Sequential File Organization or Ordered Index File.
2. Hash File organization.
Sequential File Organization or
Ordered Index File
In this, the indices are based on a sorted ordering of the values. These
are generally fast and a more traditional type of storing mechanism.
These Ordered or Sequential file organization might store the data in a
dense or sparse format:
Dense Index:
For every search key value in the data file, there is an index record.
This record contains the search key and also a reference to the first
data record with that search key value.
Dense
index
Sparse Index

The index record appears only for a few items in the data file. Each
item points to a block as shown.
To locate a record, we find the index record with the largest search
key value less than or equal to the search key value we are looking
for.
We start at that record pointed to by the index record, and proceed
along with the pointers in the file (that is, sequentially) until we find
the desired record.
Sparse
index
Hash File organization

Indices are based on the values being distributed uniformly across

a range of buckets. The buckets to which a value is assigned is
determined by a function called a hash function.
Methods of
indexing
• Clustered Indexing
• Non-Clustered or Secondary
• Indexing Multilevel Indexing
Clustered Indexing

When more than two records are stored in the same file these types of
storing known as cluster indexing. By using the cluster indexing we can
reduce the cost of searching reason being multiple records related to the
same thing are stored at one place and it also gives the frequent joing of
more than two tables(records).
Clustering index is defined on an ordered data file. The data file is ordered
on a non-key field. In some cases, the index is created on non-primary key
columns which may not be unique for each record. In such cases, in order
to identify the records faster, we will group two or more columns together
to get the unique values and create index out of them. This method is
known as the clustering index. Basically, records with similar
characteristics are grouped together and indexes are created for these
groups.
For example, students studying in each semester are grouped together.
i.e. 1st Semester students, 2nd semester students, 3rd semester students
etc are grouped.
Clustered Indexing

Clustered index sorted according to first name

(Search key)
Primary
Indexing:
This is a type of Clustered Indexing wherein the data is sorted according
to the search key and the primary key of the database table is used to
create the index. It is a default format of indexing where it induces
sequential file organization. As primary keys are unique and are stored in
a sorted manner, the performance of the searching operation is quite
efficient.
Non-clustered or Secondary
Indexing
A non clustered index just tells us where the data lies, i.e. it gives us a
list of virtual pointers or references to the location where the data is
actually stored. Data is not physically stored in the order of the index.
Instead, data is present in leaf nodes. For eg. the contents page of a
book. Each entry gives us the page number or location of the
information stored. The actual data here(information on each page of
the book) is not organized but we have an ordered reference(contents
page) to where the data points actually lie. We can have only dense
ordering in the non-clustered index as sparse ordering is not possible
because data is not physically organized accordingly.
It requires more time as compared to the clustered index because
some amount of extra work is done in order to extract the data by
further following the pointer. In the case of a clustered index, data is
directly present in front of the index.
Non-clustered or Secondary
Indexing
Multilevel Indexing

With the growth of the size of the database, indices also grow. As the
index is stored in the main memory, a single-level index might become
too large a size to store with multiple disk accesses. The multilevel
indexing segregates the main block into various smaller blocks so that
the same can stored in a single block.
The outer blocks are divided into inner blocks which in turn are pointed
to the data blocks. This can be easily stored in the main memory with
fewer overheads.
Multilevel Indexing
 Trigge
r is a stored procedure in database which automatically invokes
A trigger
whenever a special event in the database occurs. For example, a trigger can
be invoked when a row is inserted into a specified table or when certain table
columns are being updated.

Syntax:

create trigger
[trigger_name] [before |
after]
{insert | update |
delete} on
[table_name]
[for each row]
▶ Trigger is a statement that a system executes automatically when
there is any modification to the database. In a trigger, we first
specify when the trigger is to be executed and then the action
to be performed when the trigger executes. Triggers are used to
specify certain integrity constraints and referential constraints
mechanism
that cannot be specified using theofconstraint SQL.

Example –
Suppose, we are adding a tuple to the ‘Donors’ table that is
some person has donated blood. So, we can design a trigger that
will automatically add the value of donated blood to the
‘Blood_record’ table.
▶ Types of Triggers –
We can define 6 types of triggers for each
table:
1. AFTER INSERT activated after data is inserted into the table.

2. AFTER UPDATE: activated after data in the table is modified.

3. AFTER DELETE: activated after data is deleted/removed from the

table.

4. BEFORE INSERT: activated before data is inserted into the table.

5. BEFORE UPDATE: activated before data in the table is modified.

6. BEFORE DELETE: activated before data is deleted/removed from the

table.
▶ 1. Write a trigger to ensure that no employee of age less than 25 can be inserted in the
database.

delimiter $$
CREATE TRIGGER Check_age BEFORE
INSERT ON employee FOR EACH ROW
BEGIN
IF [Link] < 25
THEN SIGNAL
SQLSTATE '45000'
SET MESSAGE_TEXT
= 'ERROR:
AGE MUST BE ATLEAST 25
YEARS!'; END IF;
END; $$
delimiter;
Explanation: Whenever we want to insert any tuple to table ’employee’, then before
inserting this tuple to the table, trigger named ‘Check_age’ will be executed. This trigger will
check the age attribute. If it is greater than 25 then this tuple will be inserted into the tuple
▶ 2. Create a trigger which will work before deletion in employee table and create a duplicate
copy of the record in another table employee_backup.

▶ Before writing trigger, we need to create table

employee_backup.

create table employee_backup (employee_no

int, employee_name varchar(40), job
varchar(40), hiredate date, salary int,
primary
key(employee_no)); delimiter
$$
CREATE TRIGGER Backup BEFORE DELETE ON
employee FOR EACH ROW
BEGIN
INSERT INTO employee_backup
VALUES (OLD.employee_no,
[Link], [Link],
[Link], [Link]);
END; $$
delimiter
;
▶ 3. Write a trigger to count number of new tuples inserted using each insert
statement.

Declare count
int Set
count=0;
CREATE TRIGGER Count_tupples
AFTER INSERT ON
employee
FOR EACH
ROW BEGIN
SET count = count
+
▶ 1; END;
Explanation: We want to keep track of the number of new Tuples in the employee table.
For that, we first create a variable ‘count’ and initialize it to 0. After that, we create a
trigger named Count_tuples that will increment the value of count after insertion of any
new Tuple in the table employee.
Explanation of
syntax
create trigger [trigger_name]: Creates or replaces an existing trigger
with the trigger_name.
[before | after]: This specifies when the trigger will be executed.
{insert | update | delete}: This specifies the DML operation.
on [table_name]: This specifies the name of the table associated with
the trigger.
[for each row]: This specifies a row-level trigger, i.e., the trigger
will be executed for each row being affected.
[trigger_body]: This provides the operation to be performed as
trigger is fired
BEFORE and AFTER of Trigger

BEFORE triggers run the trigger action before the triggering

statement is run. AFTER triggers run the trigger action after the
triggering statement is run.
 Exampl
e Student Report Database, in which student marks assessment is
Given
recorded. In such schema, create a trigger so that the total and average of
specified marks is automatically inserted whenever a record is insert.
Here, as trigger will invoke before record is inserted so, BEFORE Tag can be
used.
Exampl
e
SQL Trigger to problem
statement.
create trigger
stud_marks before
INSERT
on
Stude
nt
for
each
row
set [Link] = Student.subj1 + Student.subj2 + Student.subj3,
[Link] = [Link] * 60 / 100;
SQL Trigger to problem
statement.
Above SQL statement will create a trigger in the student database
in which whenever subjects marks are entered, before inserting this
data into the database, trigger will compute those two values and
insert with the entered values. i.e.,
Assertio
n
An assertion is a statement in SQL that ensures a certain condition
will always exist in the database. Assertions are like column and table
constraints, except that they are specified separately from table
definitions. An
example of a column constraint is NOT NULL, and an example of a
table constraint is a compound foreign key, which, because it's
compound, cannot be declared with column constraints.
Assertions are similar to check constraints, but unlike check constraints
they are not defined on table or column level but are defined on
schema level. (i.e., assertions are database objects of their own right
and are not defined within a create table or alter table statement.)
Relational
Algebra
Relational Algebra is a procedural language used for manipulating
relations. The relational model gives the structure for relations so that
data can be stored in that format but relational algebra enables us to
retrieve information from relations. Some advanced SQL queries
requires explicit relational algebra operations, most commonly outer
join.
Relations are seen as sets of tuples, which means that no duplicates are
allowed. SQL behaves differently in some cases. Remember the SQL
keyword distinct. SQL is declarative, which means that you tell the
DBMS what you want.
Set operations

Relations in relational algebra are seen as sets of tuples, so we can use basic set operations. Review of
concepts and operations from set theory
• Set Element
• No duplicate elements
• No order among the
• elements Subset
• Proper subset (with fewer
• elements) Superset
• Union
• Intersection
• Set
• Difference
• Cartesian
• product
Relational
Algebra
Relational
Thank
you..
Data types main l y c lassified
202 1 -2 2 Rank ing

into three categories for every

database.
• String Data types
• Numeric Data types
• Date and time Data
types

1 1
 2021-22 Ranking

CHAR(Size) It is used to specify a fixed length string

that can contain numbers, letters, and special
characters. Its size can be 0 to 255 characters.
Default is 1.
VARCHAR(Size) It is used to specify a variable length string
that can contain numbers, letters, and special
characters. Its size can be from 0 to 65535
characters.
BINARY(Size) It is equal to CHAR() but stores binary byte
strings. Its size parameter specifies the
column length in the bytes. Default is 1.
VARBINARY(Size) It is equal to VARCHAR() but stores binary
byte strings. Its size parameter specifies the
maximum column length in bytes.
TEXT(Size) It holds a string that can contain a maximum
length of 255 characters.
TINYTEXT It holds a string with a maximum
length of 255 characters.
 2021-22 Ranking

MySQL Numeric Data Types

BIT(Size) It is used for a bit-value type. The number of bits per value is
specified in size. Its size can be 1 to 64. The default value is 1.
INT(size) It is used for the integer value. Its signed range varies
from - 2147483648 to 2147483647 and unsigned range
varies from 0 to 4294967295. The size parameter specifies
the max display width that is 255.
INTEGER(size It is equal to INT(size).
)
FLOAT(size, It is used to specify a floating point number. Its size
d) parameter specifies the total number of digits. The number
of digits after the decimal point is specified by d parameter.
FLOAT(p) It is used to specify a floating point number. MySQL used p
parameter to determine whether to use FLOAT or DOUBLE. If p
is between 0 to24, the data type becomes FLOAT (). If p is
from 25 to 53, the data type becomes DOUBLE().
DOUBLE(size, It is a normal size floating point number. Its size parameter
d) specifies the total number of digits. The number of digits
after the decimal is specified by d parameter.
DECIMAL(size It is used to specify a fixed point number. Its size parameter
 2021-22 Ranking

MySQL Date and Time Data Types

DATE It is used to specify date format YYYY-MM-DD.
Its supported range is from '1000-01-01' to '9999-
12-31'.
DATETIME(fsp) It is used to specify date and time
combination. Its format is YYYY-MM-DD
hh:mm:ss. Its supported range is from '1000-01-
01 00:00:00' to 9999-12-31 23:59:59'.
TIMESTAMP(fsp It is used to specify the timestamp. Its value is
) stored as the number of seconds since the Unix
epoch('1970- 01-01 00:00:00' UTC). Its format
is YYYY-MM-DD
hh:mm:ss. Its supported range is from '1970-
01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC.
TIME(fsp) It is used to specify the time format. Its
format is hh:mm:ss. Its supported range is
from '-838:59:59' to '838:59:59'
 2021-22 Ranking

• SQL is a standard language for storing, manipulating

and retrieving data in databases.
• Lets see how to use SQL in: MySQL, SQL Server, MS
Access, Oracle, Sybase, Informix, Postgres, and other
database systems.

1 1
 2021-22 Ranking

What is
SQL?
• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
 2021-22 Ranking

What Can SQL

do?
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and
views
 2021-22 Ranking

SQL CREATE TABLE Statement

• A Table is a combination of rows and columns. For creating a table we
have to define the structure of a table by adding names to columns and
providing data type and size of data to be stored in columns.
Syntax:
CREATE table
table_name (
Column1 datatype
(size), column2
datatype (size),
.
.
columnN
datatype(size)
);
• Here table_name is name
 2021-22 Ranking

Exam
ple
CREATE TABLE Persons
( PersonID int,
LastName
varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
 2021-22 Ranking

PersonID LastName FirstName Address City

 2021-22 Ranking

The SQL DROP TABLE Statement

• The DROP TABLE statement is used to drop an existing table in a

database.
Syntax
DROP TABLE table_name;
SQL TRUNCAT E
20 21-22
BL
E
Ra nkin g
•T
TheATRUNCATE TABLE statement is used to delete the data inside a
table, but not the table itself.

Syntax

TRUNCATE TABLE table_name;

 2021-22 Ranking

Emp_id Name Address Salary

1 Aryan Allahabad 22000

2 Shurabhi Varanasi 13000

3 Pappu Delhi 24000

Execute the following query to truncate the

table:

TRUNCATE TABLE employee;

 2021-22 Ranking

Difference b/w DROP and

TRUNCATE statements
• When you use the drop statement it deletes the
table's row together with the table's definition so
all the relationships of that table with other tables
will no longer be valid.
• When you drop a table:
• table structure will be dropped
• Relationship will be dropped
• Integrity constraints will be dropped
• Access privileges will also be dropped
• On the other hand when we TRUNCATE a table, the
table structure remains the same, so you will not
face any of the above problems.
 2021-22 Ranking

Create Table Using Another

Table
• A copy of an existing table can also be created using CREATE TABLE.

• The new table gets the same column definitions. All columns or
specific columns can be selected.

• If you create a new table using an existing table, the new table will be
filled with the existing values from the old table.
 2021-22 Ranking

Synt
ax
• CREATETABLE new_table_name
AS SELECT column1,
column2,... FROM
existing_table_name WHERE
....;

The following SQL creates a new table called "Test Tables"

(which is a copy of the "Customers" table):

Example
CREATE TABLE TestTable AS
SELECT customername,
contactname FROM customers;
 2021-22 Ranking

SQL ALTER TABLE Statement

• The ALTER TABLE statement is used to add, delete, or modify columns

in an existing table.
• The ALTER TABLE statement is also used to add and drop various
constraints on an existing table.
 2021-22 Ranking

Synt
ax
• ALTER TABLE - ADD Column
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;

• The following SQL adds an "Email" column to the

"Customers" table:
Example
ALTER TABLE Customers
ADD Email
varchar(255);
 2021-22 Ranking

ALTER TABLE - DROP

COLUMN
• To delete a column in a table, use the following syntax
(notice that some database systems don't allow
deleting a column):
ALTER TABLE table_name
DROP COLUMN column_name;
• The following SQL deletes the "Email" column from the
"Customers" table:
• Example
• ALTER TABLE
Customers DROP
COLUMN Email;
 2021-22 Ranking

ALTER TABLE - ALTER/MODIFY

DATATYPE
• To change the data type of a column in a table, use the
following syntax:
• SQL Server / MS Access:
ALTER TABLE table_name
ALTER COLUMN column_name datatype;

• My SQL / Oracle (prior version 10G):

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
 2021-22 Ranking

SQL ALTER TABLE

Example
ID LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

 2021-22 Ranking

• Now we want to add a column named "DateOfBirth" in

the "Persons" table.
• We use the following SQL statement:

ALTER TABLE Persons

ADD DateOfBirth
date;
 2021-22 Ranking

• The "Persons" table will now look like this:

ID LastName FirstName Address City DateOfBirth

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

 2021-22 Ranking

Change Data Type

Example
• Now we want to change the data type of the column
named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;

• Notice that the "DateOfBirth" column is now of type

year and is going to hold a year in a two- or four-digit
format.
DROP COLUM N E 20 21-22 Ran king

xample
•Next, we want to delete the column named
"DateOfBirth" in the "Persons" table.

•We use the following SQL statement:

•ALTER TABLE Persons

DROP COLUMN
DateOfBirth;
The "Persons " t able will now
2021 -22 Rank ing

look like this:

ID LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

 2021-22 Ranking

SQL RENAME TABLE

• In some situations, database administrators and users

want to change the name of the table in the SQL
database because they want to give a more relevant
name to the table.
• Any database user can easily change the name by
using the RENAME TABLE and ALTER TABLE statement
in Structured Query Language.
• The RENAME TABLE and ALTER TABLE syntax help in
changing the name of the table.
• Syntax of RENAME statement in SQL
RENAME old_table _name To new_table_name ;
//obselete
Alter table old_table_name rename to
new_table_name; // for online compilers
Let's take an ex a m p le of
2 021- 22 Rank ing

a table named Employee:

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 25000 Goa

202 Ankit 45000 Delhi

203 Bheem 30000 Goa

204 Ram 29000 Goa

205 Sumit 40000 Delhi

 2021-22 Ranking

• Suppose, you want to change the name of the

above table into the "Coding_Employees". For this, you
have to type the following RENAME statement in
SQL:

RENAME Employee To Coding_Employees ;

Syntax of ALT E R TABLE
2 021-2 2 Ranking

statement in SQL
ALTER TABLE old_table_name RENAME TO new_tabl
e_name;
• In the Syntax, we have to specify the RENAME TO
keyword after the old name of the table.
Let's take an e x a m 2021 -22 R ankin g

ple of a table named Bikes:

Bike_Name Bike_Color Bike_Cost

KTM DUKE Black 185,000

Royal Enfield Black NULL
Pulsar Red 90,0000
Apache White NULL
Livo Black 80,000
KTM RC Red 195,000
 2021-22 Ranking

• Suppose, you want to change the name of the

above table into "Bikes_Details" using ALTER TABLE
statement. For this, you have to type the following
query in SQL:

ALTER TABLE Bikes RENAME TO Bikes_Details ;

 2021-22 Ranking

SQL INSERT STATEMENT

• SQL INSERT statement is a SQL query. It is
used to insert a single or a multiple
records in a table.
• There are two ways to insert data in a table:

[Link] SQL insert into statement

1. By specifying column names
2. Without specifying column names

[Link] SQL insert into select statement

 2021-22 Ranking

1) Inserting data directly into a

table
• You can insert a row in the table by using SQL INSERT
INTO command.
• There are two ways to insert values in a table.
• In the first method there is no need to specify the
column name where the data will be inserted, you need
only their values.

INSERT INTO table_name

VALUES (value1, value2, value3....);
 2021-22 Ranking

• The second method specifies both the column name and

values which you want to insert.
INSERT INTO table_name (column1, column2, colu
mn3....)
VALUES (value1, value2, value3.....);
 2021-22 Ranking

Let's take an example of table which

has five records within it.
INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)
VALUES (1, ABHIRAM, 22, ALLAHABAD);
INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)
VALUES (2, ALKA, 20, GHAZIABAD);
INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)
VALUES (3, DISHA, 21, VARANASI);
INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)
VALUES (4, ESHA, 21, DELHI);
INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)
VALUES (5, MANMEET, 23, JALANDHAR);
 2021-22 Ranking

ROLL_NO NAME AGE CITY

1 ABHIRAM 22 ALLAHABAD

2 ALKA 20 GHAZIABAD

3 DISHA 21 VARANASI

4 ESHA 21 DELHI

5 MANMEET 23 JALANDHAR

You can create a record in CUSTOMERS table by using this

syntax also.
INSERT INTO CUSTOMERS
VALUES (6, PRATIK, 24, KANPUR);
 2021-22 Ranking

ROLL_NO NAME AGE CITY

1 ABHIRAM 22 ALLAHABAD

2 ALKA 20 GHAZIABAD

3 DISHA 21 VARANASI

4 ESHA 21 DELHI

5 MANMEET 23 JALANDHAR

6 PRATIK 24 KANPUR
2) Inserting d a t a through
2 021- 22 R anking

SELECT Statement
• SQL INSERT INTO SELECT Syntax

INSERT INTO table_name

[(column1, column2, ....
column)]
SELECT column1, column2, ....
Column N
FROM table_name [WHERE
condition];
 2021-22 Ranking

SQL INSERT Multiple Rows

INSERT INTO student(ID, Name, Percentage, Location,

DateO fBirth)
VALUES(1, "Manthan Koli", 79, "Delhi", "2003-08-20"),
(2, "Dev Dixit", 75, "Pune", "1999-06-17"),
(3, "Aakash Deshmukh", 87, "Mumbai", "1997-09-12"),
(4, "Aaryan Jaiswal", 90, "Chennai", "2005-10-02"),
(5, "Rahul Khanna", 92, "Ambala", "1996-03-04"),
(6, "Pankaj Deshmukh", 67, "Kanpur", "2000-02-02"),
(7, "Gaurav Kumar", 84, "Chandigarh", "1998-07-06"),
(8, "Sanket Jain", 61, "Shimla", "1990-09-08"),
(9, "Sahil Wagh", 90, "Kolkata", "1968-04-03"),
(10, "Saurabh Singh", 54, "Kashmir", "1989-01-06");
 2021-22 Ranking

SQL UPDATE

• The SQL commands (UPDATE and DELETE) are used to

modify the data that is already in the
database. The SQL DELETE command uses a
WHERE clause.
• SQL UPDATE statement is used to change the data of
the records held by tables. Which rows is to be
update, it is decided by a condition. To specify
condition, we use WHERE clause.
• The UPDATE statement can be written in following form:
UPDATE table_name SET [column_name1= value1,... column_n
ameN = valueN] [WHERE condition]

UPDATE table_name
SET column_name = expression
WHERE conditions
 2021-22 Ranking

Let's take an example: here we are

going to update an entry in the
source table.
• SQL statement:
UPDATE students
SET User_Name =
'beinghuman'
WHERE Student_Id = '3’
Source Table:
Student_Id FirstName LastName User_Name

1 Ada Sharma sharmi

2 Rahul Maurya sofamous

3 James Walker jonny

 2021-22 Ranking

• See the result after updating

value:

Student_Id FirstName LastName User_Name

1 Ada Sharma sharmili

2 Rahul Maurya sofamous

3 James Walker beinghuman

 2021-22 Ranking

Updating Multiple
Fields:
• Ifyou are going to update multiple fields, you
should separate each field assignment with a
comma.
• SQL UPDATE statement for multiple fields:
UPDATE students
SET User_Name = 'beserious', First_Name = 'Johnny'
WHERE Student_Id = '3'
 2021-22 Ranking

Result of the table is given below:

Student_Id FirstName LastName User_Name

1 Ada Sharma sharmili

2 Rahul Maurya sofamous

3 Johnny Walker beserious

 2021-22 Ranking

• Update Students
• Set contactno=case rollno
• When 1 then “3478965”
• When 2 then “5689734”
• When 3 then “9873456”
• End where rollno between 1 and 10;
 SQL SELECT S t men
202 1-

• The SELECT statement tis the most commonly

used command in Structured Query Language. It
isa used
t e to access the records from one or more
2 2 Ran kin g

database tables and views. It also retrieves the

selected data that follow the conditions we want.
• By using this command, we can also access the
particular record from the particular column of the
table. The table which stores the record returned by
the SELECT statement is called a result-set table.
• Syntax of SELECT Statement in SQL
SELECT Column_Name_1, Column_Name_2, ....., Col
umn_Name_N FROM Table_Name;
• In this SELECT syntax, Column_Name_1,
Column_Name_2,
….., Column_Name_N are the name of those columns
in the table whose data we want to read.
 2021-22 Ranking

• If you want to access all rows from all fields of the

table, use the following SQL SELECT syntax with *
asterisk sign:

SELECT * FROM table_name;

 2021-22 Ranking

Examples of SELECT Statement

in SQL
• Example 1:
• Firstly, we have to create the new table and then
insert some dummy records into it.
CREATE TABLE Student_Records
(
Student_Id Int PRIMARY KEY,
First_Name VARCHAR (20),
Address VARCHAR
(20), Age Int NOT
NULL,
Percentage Int NOT
NULL,
Grade VARCHAR (10)
);
 2021-22 Ranking

• The following query inserts the record of

intelligent students into the Student_Records table:
INSERT INTO Student VALUES (201, Akash, Delhi, 18, 89,
A2),

(202, Bhavesh, Kanpur, 19, 93, A1),

(203, Yash, Delhi, 20, 89, A2),
(204, Bhavna, Delhi, 19, 78, B1),
(05, Yatin, Lucknow, 20, 75, B1),
(206, Ishika, Ghaziabad, 19, 51, C1),
(207, Vivek, Goa, 20, 62, B2);
 2021-22 Ranking

• The following SQL query displays all the values

of each column from the above Student_records
table:
SELECT * FROM Student_Records;
Student_ID First_Name Address Age Percentage Grade

201 Akash Delhi 18 89 A2

202 Bhavesh Kanpur 19 93 A1

203 Yash Delhi 20 89 A2

204 Bhavna Delhi 19 78 B1

205 Yatin Lucknow 20 75 B1

206 Ishika Ghaziabad 19 91 C1

207 Vivek Goa 20 80 B2

 Example 2:
2021-22 Ranking

• The following query displays the values of particular

column from the above Student_Record table:
SELECT Student_Id, Age, Percentage, Grade FROM
Employee;
Student_ID Age Percentage Grade

201 18 89 A2

202 19 93 A1

203 20 89 A2

204 19 78 B1

205 20 75 B1

206 19 91 C1

207 20 80 B2
 2021-22 Ranking

SELECT Statement with WHERE

clause
• The WHERE clause is used with SELECT statement to
return only those rows from the table, which satisfy
the specified condition in the query.
• In SQL, the WHERE clause is not only used with
SELECT, but it is also used with other SQL
statements such as UPDATE, ALTER, and DELETE
statements.
•Syntax of SELECT Statement with WHERE clause
SELECT * FROM Name_of_Table WHERE [condition];
Example of SEL E C T Statement with
2021 -22 R ankin g

WHERE clause
• Firstly, we have to create the new table and then
insert some dummy records into it.
• Use the following query to
create the Employee_Details table in SQL:
CREATE TABLE
Employee_Details (
Employee_ID INT AUTO_INCREMENT PRIMARY KEY,
Emp_Name VARCHAR (50),
Emp_City VARCHAR (20),
Emp_Salary INT NOT
NULL,
Emp_Panelty INT NOT
NULL
);
 2021-22 Ranking

• The following INSERT query inserts the record of

employees into the Employee_Details table:
INSERT INTO Employee_Details (Employee_ID,
Emp_Name, E mp_City, Emp_Salary, Emp_Panelty)
VALUES (101, Anuj, Ghazi abad, 25000, 500),
(102, Tushar, Lucknow, 29000, 1000),
(103, Vivek, Kolkata, 35000, 500),
(104, Shivam, Goa, 22000, 500);
 2021-22 Ranking

• The following SELECT query shows the data

of the Employee_Details table:
SELECT * FROM Employee_Details;

Employee_Id Emp_Name Emp_City Emp_Salary Emp_Panelty

101 Anuj Ghaziabad 25000 500

102 Tushar Lucknow 29000 1000

103 Vivek Kolkata 35000 500

104 Shivam Goa 22000 500

 2021-22 Ranking

• The following query shows the record of those

employees from the above table whose Emp_Panelty
is 500:
SELECT * FROM Employee_Details WHERE Emp_Panelty =
500Employee_Id Emp_Name Emp_City Emp_Salary Emp_Panelty
;
101 Anuj Ghaziabad 25000 500

103 Vivek Kolkata 35000 500

104 Shivam Goa 22000 500

 2021-22 Ranking

SQL DELETE
• The SQL DELETE statement is used to delete rows from
a table. Generally DELETE statement removes one
or more records from a table.
• SQL DELETE Syntax

DELETE FROM table_name [WHERE condition];

• Here table_name is the table which has to be deleted.

• The WHERE clause in SQL DELETE statement is optional
here.
 2021-22 Ranking

SQL DELETE Example

ID EMP_NAME CITY SALARY

101 Adarsh Singh Obra 20000

102 Sanjay Singh Meerut 21000

103 Priyank Raipur 25000

a
Sharma
104 Esha Singhal Delhi 26000
• Example of delete with WHERE clause is given
below:
DELETE FROM EMPLOYEE WHERE ID=101;
 2021-22 Ranking

ID EMP_NAME CITY SALARY

102 Sanjay Singh Meerut 21000

103 Priyank Raipur 25000

a
Sharma
104 Esha Singhal Delhi 26000
 2021-22 Ranking

• Another example of delete statement is

given below
DELETE FROM EMPLOYEE;
ID EMP_NAME CITY SALARY

It will delete all the records of

EMPLOYEE table.
 2021-22 Ranking

• The WHERE clause in the SQL DELETE statement is

optional and it identifies the rows in the column that
gets deleted.
• WHERE clause is used to prevent the deletion of all
the rows in the table, If you don't use the WHERE
clause you might loss all the rows.

• Invalid DELETE Statement for ORACLE database

• You cannot use * (asterisk) symbol to delete all the
records.
DELETE * FROM EMPLOYEE;
 2021-22 Ranking

Difference between DELETE and

TRUNCATE statements
• There is a slight difference b/w delete and
truncate statement. The DELETE statement only
deletes the rows from the table based on the
condition defined by WHERE clause or delete all the
rows from the table when condition is not specified.
• But it does not free the space containing by the
table.
• The TRUNCATE statement: it is used to delete all the
rows from the table and free the containing space.
 2021-22 Ranking

SQL COPY TABLE

• If you want to copy the data of one SQL table into

another SQL table in the same SQL server, then it
is possible by using the SELECT INTO statement in
SQL.
• The SELECT INTO statement in Structured Query
Language copies the content from one existing
table into the new table. SQL creates the new
table by using the structure of the existing table.
• Syntax*of
SELECT SELECT
INTO INTO statement
New_table_name FROMin SQL
old_table_name;
not //
working
create table table_name as
select * from old _table _name;
Examples of S E L ECT INTO
2 021- 22 Ran king

statement in SQL
Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Cars
 2021-22 Ranking

• Suppose you want to copy the content of the

above Car table into the new table Car_Details. For
this, you have to type the following query in SQL:
SELECT * INTO Car_Details FROM Cars;
• Let's check the Car_D e t a i l s table is created
2 021 -22 Ra nk in g

successfully or not in the database:

[Link] * FROM Car_Details;

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

 2021-22 Ranking

SQL WHERE Clause

• WHERE keyword is used for fetching filtered data in a result set. It is

used to fetch data according to particular criteria. WHERE keyword
can also be used to filter data by matching patterns.
SELECT column1,column2 FROM table_name
WHERE column_name operator value;

Select column from table where condition;

1 1
 2021-22 Ranking

1. column1,column2: fields in the

table
table_name: name
2. column_name:
3. name of of
table
field used for filtering the
data
4. operator: operation to be considered for filtering
5. value: exact value or pattern to get related data in
result
 2021-22 Ranking

List of Operators that Can be Used with

WHERE Clause Operator Description
> Greater Than

>= Greater than or Equal

to
< Less Than
<= Less than or Equal to
= Equal to
<> Not Equal to
BETWEEN In an inclusive Range
LIKE Search for a pattern

To specify multiple
IN possible values for a
column
 Quer
2021-22 Ranking

y:
CREATE TABLE
Emp1( EmpID INT
PRIMARY KEY, Name
VARCHAR(50),
Country VARCHAR(50),
Age int(2),
mob int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Emp1 (EmpID, Name,Country, Age, mob)
VALUES (1, 'Shubham', 'India','23','738479734'),
(2, 'Aman ', 'Australia','21','436789555'),
(3, 'Naveen', 'Sri lanka','24','34873847'),
(4, 'Aditya', 'Austria','21','328440934'),
(5, 'Nishant', 'Spain','22','73248679');
Select * from Emp1;
Where Clause w i t h Logical 2021 -2 2R anking

Operators
To fetch records of Employee with ages equal to 24.

Query:

SELECT * FROM Emp1 WHERE Age=24;

 2021-22 Ranking

To fetch the EmpID, Name and Country of Employees with Age greater
than 21.

Query:

SELECT EmpID, Name, Country FROM Emp1 WHERE Age > 21;
2021-22 Ranking
 2021-22 Ranking

Where Clause with BETWEEN Operator

It is used to fetch filtered data in a given range inclusive of two values.

Syntax:

SELECT column1,column2 FROM table_name

WHERE column_name BETWEEN value1 AND

value2; Parameter Explanation:

1. BETWEEN: operator name

value1 AND value2: exact value from value1 to
value2 to get related data in result set.
2. To fetch records of Employees where Age is
between 22 and 24 (inclusive).
 2021-22 Ranking

Query:

SELECT * FROM Emp1 WHERE Age BETWEEN 22 AND 24;

 2021-22 Ranking

Where Clause with LIKE

It isOperator
used to fetch filtered data by searching for a particular pattern in the where clause.

Syntax:

SELECT column1,column2 FROM table_name WHERE column_name LIKE pattern;

Parameters Explanation:

LIKE: operator name

pattern: exact value extracted from the pattern to get related data in the result set.
Note: The character(s) in the pattern is case sensitive.

To fetch records of Employees where Name starts with the letter S.

Query:

SELECT * FROM Emp1 WHERE Name LIKE 'S%';

The ‘%'(wildcard) signifies the later characters here which can be
of any length and value.
2021-22 Ranking
 2021-22 Ranking

To fetch records of Employees where Name contains the pattern ‘M’.

Query:

SELECT * FROM Emp1 WHERE Name LIKE '%M%';

2021-22 Ranking
 2021-22 Ranking

Where Clause with IN Operator

• It is used to fetch the filtered data same as fetched by ‘=’ operator just the difference
is that here we can specify multiple values for which we can get the result set.

• Syntax:

SELECT column1,column2 FROM table_name WHERE column_name IN

(value1,value2,..);

• Parameters Explanation:

• IN: operator name

• value1,value2,..: exact value matching the values given and get related data in the
result set.
• To fetch the Names of Employees where Age is 21 or 23.
 2021-22 Ranking

• Query:

SELECT Name FROM Emp1 WHERE Age IN (21,23);

 2021-22 Ranking

The SQL
ORDER
• The ORDER BYBYkeyword is used to sort the result-set in ascending or
descending order

• The ORDER BY command sorts the result set in ascending order by default. To
sort the records in descending order, use the DESC keyword.

• Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column1,
column2, ... ASC|DESC;

• Example
• Sort the products by price:

• SELECT * FROM
Customers ORDER BY
CustomerName;

SELECT * FROM Products

ORDER BY Price;
 2021-22 Ranking

DESC
• The ORDER BY keyword sorts the records in ascending order by
default. To sort the records in descending order, use the DESC
keyword.

• Example
• Sort the products from highest to lowest price:

SELECT * FROM Products

ORDER BY Price DESC;
 2021-22 Ranking

• ASC
• The ASC command is used to sort the data returned in ascending order.

• The following SQL statement selects all the columns from the
"Customers" table, sorted by the "CustomerName" column:

• Example
SELECT * FROM Customers
ORDER BY CustomerName ASC;
 2021-22 Ranking

DES
•C
The DESC command is used to sort the data returned in descending
order.

• Example

• SELECT * FROM Customers

ORDER BY CustomerName
DESC;
 2021-22 Ranking

Order
Alphabetically
• For string values the ORDER BY keyword will order alphabetically:

• Example
• Sort the products alphabetically by ProductName:

SELECT * FROM Products

ORDER BY ProductName;
 2021-22 Ranking

Alphabetically
DESC
• To sort the table reverse alphabetically, use the DESC keyword:

• Example
• Sort the products by ProductName in reverse order:

SELECT * FROM Products

ORDER BY ProductName DESC;
ORDER BY Sev e r a l
202 1-22 Ran king

Columns
• The following SQL statement selects all customers from
the "Customers" table, sorted by the "Country" and the
"CustomerName" column. This means that it orders by
Country, but if some rows have the same Country, it
orders them by CustomerName:
• Example
SELECT * FROM Customers
ORDER BY Country, CustomerName;
 2021-22 Ranking

Using Both ASC and DESC

• The following SQL statement selects all customers from
the "Customers" table, sorted ascending by the
"Country" and descending by the "CustomerName"
column:

• Example
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
 2021-22 Ranking

The SQL GROUP BY

Statement
•The GROUP BY command is used to group the result set (used
with aggregate functions: COUNT, MAX, MIN, SUM, AVG).

• The GROUP BY statement groups rows that have the same values into summary
rows, like "find the number of customers in each country".

• The GROUP BY statement is often used with aggregate functions (COUNT(),

MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more
columns.

GROUP BY Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
 2021-22 Ranking

SQL GROUP BY
Examples
• The following SQL statement lists the number of customers in each
country:
• Example
SELECT COUNT(CustomerID),
Country FROM Customers
GROUP BY Country;
• or
select count(country),country from Customers group by country;
Or // with where
select count(country),country from Customers where age>22 group by country;

SELECT function,
Column FROM
table_name
GROUP BY Column;
•or
 2021-22 Ranking

• Example
• SELECT COUNT(CustomerID),
Country FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID)
DESC;
 2021-22 Ranking

•Write a query to display all the

departments along with no. of emps
working in it?
E_ID E_NAME DEPT SALARY

1 RAM HR 10000

2 AMRIT MRKT 20000

3 RAVI HR 30000

4 NITIN MRKT 40000

5 VARUN IT 50000
 2021-22 Ranking

• What we need is …
HR=2
MRKT=2
IT=1

HR
MRKT
IT
 2021-22 Ranking

Select DEPT from emp group by DEPT;

• This will create a group of HR ,MRKT and IT.
• Now we want to count how many are in HR,MRKT and IT.
• So ,
Select DEPT, count(*) from emp group by DEPT;
 2021-22 Ranking

SQL
HAVING
The HAVING Clause is used instead of WHERE with aggregate functions.

• The following SQL lists the number of customers in each country. Only
include countries with more than 5 customers:

•ExampleGet your own SQL Server

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
 2021-22 Ranking

• Write a query to display all the dept names where no. emps are less
than 2.
 2021-22 Ranking

Select dept from emp group by dept having

count(*)<2
•where cannot be used after Group by because where
works on the whole table but group by has already
created a group and we want to apply the condition on
that particular group only.
•So we use having instead of where.
•If the query is to find the name of the employee who is
working in a department having employees less than
2???
Select E_NAME from emp where DEPT IN
(Select dept from emp group by dept having count(*)<2 );
 2021-22 Ranking

• The following SQL lists the number of customers in each

country, sorted high to low (Only include countries with
more than 5 customers):
• Example
• SELECT COUNT(CustomerID),
Country FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID)
DESC;
 2021-22 Ranking

Difference between Where and Having

Clause
[Link].
in WHERE
SQLClause HAVING Clause

WHERE Clause is used to filter the

1. records from the table based on HAVING Clause is used to filter record from the
the specified condition. groups based on the specified condition.

2. WHERE Clause can be HAVING Clause cannot be used without GROUP

used without GROUP BY Clause BY Clause

3. WHERE Clause implements in row HAVING Clause implements in column operation

operations

4. WHERE Clause cannot HAVING Clause can contain aggregate function

contain aggregate function

WHERE Clause can be used with

5. SELECT, UPDATE, HAVING Clause can only be used with SELECT
DELETE statement.
statement.
6. WHERE Clause is used HAVING Clause is used after GROUP BY Clause
before GROUP BY Clause

WHERE Clause is used with single

7. row function like UPPER, LOWER HAVING Clause is used with multiple
etc. row function like SUM, COUNT etc.
 Difference be t w een View and
2021- 22 R anking

Table:
Basis View Table

A view is a database object that A table is a database object or an

Definition allows generating a logical subset of entity that stores the data of a
data from one or more tables. database.

Dependency The view depends on the table. The table is an independent data
object.

Database space The view is utilized database space The table utilized database
when a query runs. space throughout its existence.

Manipulate data We can not add, update, or delete We can easily add, update, or delete
any data from a view. any data from a table.

Recreate We can easily use replace option to We can only create or drop the table.
recreate the view.

Aggregation of data Aggregate data in views. We can not aggregate data in tables.

In the table, we can maintain

table/view The view contains complex multiple
relationship tables joins. relationships using a primary and
foreign key.
 2021-22 Ranking

SQL SELECT DISTINCT

• The SQL DISTINCT command is used with SELECT key
word to retrieve only distinct or unique data.
• In a table, there may be a chance to exist a
duplicate value and sometimes we want to retrieve
only unique values. In such scenarios, SQL SELECT
DISTINCT statement is used.
• Note: SQL SELECT UNIQUE and SQL SELECT
DISTINCT
statements are same.
•Let's see the syntax of select distinct
statement. SELECT DISTINCT
column_name ,column_name FROM
table_name;

1 1
 2021-22 Ranking

Student_Name Gender Mobile_Number HOME_TOWN

Rahul Ojha Male 7503896532 Lucknow

Disha Rai Female 9270568893 Varanasi

Sonoo Jaiswal Male 9990449935 Lucknow

 2021-22 Ranking

• Here is a table of students from where we want to

retrieve distinct information For example: distinct
home-town.
SELECT DISTINCT home_town
FROM students
Now, it will return two rows.
HOME_TOWN

Lucknow

Varanasi
 2021-22 Ranking

SQL SELECT
COUNT
• The SQL COUNT() is a function that returns the
number of records of the table in the output.
• This function is used with the SQL SELECT statement.
• Let's take a simple example: If you have a record of the
voters in the selected area and want to count the
number of voters, then it is very diffi cult to do it
manually, but you can do it easily by using SQL
SELECT COUNT query.
• Syntax of Select Count Function in SQL
SELECT COUNT(column_name) FROM table_name;
• In the syntax, we have to specify the column's
name after the COUNT keyword and the name of
the table on which the Count function is to be
executed.
 2021-22 Ranking

Bike_Name Bike_Color Bike_Cost

Pulsar Black 185,000

Apache Black NULL

KTM RC Red 90,0000

Royal Enfield White NULL

Livo Black 80,000

KTM DUKE Red 195,000

 2021-22 Ranking

• Suppose, you want to count the total number of bike

colors from Bike Table. For this operation, you have
to write the following SQL statement:
SELECT COUNT (Bike_Color) AS TotalBikeColor FROM Bikes
;
• This query will show the following output on the
screen:
TotalBikeColor

6
 2021-22 Ranking

• The of this query is six

the
outputBike_Color column
becausedoes not contain any NULL
value.
• Suppose, you want to count the total values
of the Bike_Cost column from the above Bike Table.
For this operation, you have to write the following
statement in SQL:
SELECT COUNT (Bike_Cost) AS TotalBikeCost FROM Bikes ;
• This query will show the following output on the
TotalBikeCost
screen:

4
 2021-22 Ranking

Emp_Id Emp_Name Emp_Salary Emp_City

2001 Saurabh 25000 NULL

2002 Ram 29000 Delhi

2003 Sumit 30000 NULL

2004 Ankit 45000 Goa

2005 Bheem 40000 NULL

 2021-22 Ranking

• Suppose, you want to count the total values

of the Emp_City column of the above Employee_details
table. For this query, you have to write the
following statement in Structured Query Language:

SELECT COUNT (Emp_City) AS TotalCity FROM

Employee_deta ils ;
TotalCity

2
 2021-22 Ranking

Select Count(*) Function in SQL

• The count(*) function in SQL shows all the Null and

Non- Null records present in the table.
• Syntax of Count (*) Function in SQL
SELECT COUNT(*) FROM table_name;
 2021-22 Ranking

Bike_Name Bike_Color Bike_Cost

Livo Black 185,000

Apache Red NULL

Pulsar Red 90,0000

Royal Enfield Black NULL

KTM DUKE Black 80,000

KTM RC White 195,000

 2021-22 Ranking

• Suppose, you want to count the total number of

records from the Bike Table. For this condition, you
have to write the following statement in Structured
Query Language:
SELECT COUNT (*) FROM Bikes ;
Count(*)

6
SQL Count() F u
202 1-22
tion With
Claus WHERE
e
•nWec can
R ankin g

also use the Count() function with the

WHERE clause. The Count Function with WHERE
clause in the SELECT statement shows those
records that matched the specified
criteria.

• Syntax of Count() Function With WHERE clause in SQL

SELECT COUNT(column_name) FROM table_name WHERE
[co ndition];
 2021-22 Ranking

Bike_Name Bike_Color Bike_Cost

Apache Black 90,0000

Livo Black NULL

KTM RC Red 185,000

KTM DUKE White NULL

Royal Enfield Red 80,000

Pulsar Black 195,000

 2021-22 Ranking

• Suppose, you want to count the total number of

bikes whose color is black. For this, you have to type
the following statement in SQL:
SELECT COUNT (Bike_Name) AS TotalBikeBlackColor
FROM
Bikes WHERE Bike_Color = 'Black';
 2021-22 Ranking

Emp_Id Emp_Name Emp_Salary Emp_City

2001 Bheem 30000 Jaipur

2002 Ankit 45000 Delhi

2003 Sumit 40000 Delhi

2004 Ram 29000 Goa

2005 Abhay 25000 Delhi

 2021-22 Ranking

• Suppose, you want to count the total number of

those employees who belong to Delhi city. For this, you
have to write the following SQL statement:
SELECT COUNT (Emp_Name) AS TotalEmpCity FROM
Employee_d etails WHERE Emp_City = 'Delhi';

•Write a query to display the no. of residents whose city

is USA.
•Select count(customer_id) from Customers where
country=“USA”;
•Select count(*),first_name from Customers where
country=“USA” group by first_name;
SQL Count Fu n c t ion With
202 1-22 R anki ng

DISTINCT keyword
• The DISTINCT keyword with the COUNT function
shows only the numbers of unique rows of a column.
• Syntax of Count Function With DISTINCT keyword in
SQL
SELECT COUNT(DISTINCT column_name) FROM table_name
WHERE [condition];
 2021-22 Ranking

Examples of Count Function With

DISTINCT keyword in SQL

Car_Name Car_Color Car_Cost

i20 White 10,85,000

Hyundai Venue Black 9,50,000

Swift Dezire Red 9,00,000

Hyundai Creta White 7,95,000

Kia Seltos White 8,00,000

Kia Sonet Red 10,00,000

 2021-22 Ranking

• Suppose, you want to count the unique colors of a

car from the above table. For this query, you have to
write the below statement in SQL:
SELECT COUNT (DISTINCT Car_Color) AS Unique_Car_Color
FROM Cars ;
• Write a query to find the unique no. of countries from customers
table whose country is USA?

• Select count(distinct country) from customers where

country=USA;
 2021-22 Ranking

Unique_Car_Color

3
 2021-22 Ranking

Emp_Id Emp_Name Emp_Salary Emp_City

2001 Sumit 25000 Jaipur

2002 Ram 45000 Delhi

2003 Bheem 25000 Delhi

2004 Ankit 29000 Goa

2005 Abhay 40000 Delhi

 2021-22 Ranking

• Suppose, you want to count the unique

values of the Emp_Salaryfield from the
Employee_details table. For this, you have to write
the following statement in Structured Query
Language:
SELECT COUNT (DISTINCT Emp_Salary) AS Unique_Salary
FROM Employee ;
Unique_Salary

4
 2021-22 Ranking

SQL SELECT TOP

• The SELECT TOP statement in SQL shows the

limited number of records or rows from the
database table. The TOP clause in the statement
specifies how many rows are returned.
• It shows the top N number of rows from the tables
in the output. This clause is used when there are
thousands of records stored in the database tables.
• Note: All the database systems do not support the
TOP keyword for selecting the limited number of
records. Oracle supports the ROWNUM keyword,
and MySQL supports the LIMIT keyword.
 2021-22 Ranking

• Syntax of TOP Clause in SQL

• SELECT TOP number | percent column_Name1,
column_Na me2, ....., column_NameN
FROM table_name WHERE [Condition] ;
 2021-22 Ranking

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

 2021-22 Ranking

• Suppose, you want to show the first three Names

and Color of Car from the above table. To do this,
you have to type the following query in SQL:
SELECT TOP 3 Car_Name, Car_Color FROM
Cars;
select * from table_name limit number; // eqivalent
query for mysql
For eg select * from Cars limit 2;
• This query shows the following table on the screen:
Car_Name Car_Color

Hyundai Creta White

Hyundai Venue White

Hyundai i20 Red

 2021-22 Ranking

Stu_ID Stu_Name Stu_Marks

1001 Abhay 85
1002 Ankit 75
1003 Bheem 60
1004 Ram 79
1005 Sumit 80
 2021-22 Ranking

• Suppose, you want to show the details of the

first four students in the result from the above
table. To do this, you have to type the following
query in SQL:
SELECT TOP 4 * FROM Student;

Stu_ID Stu_Name Stu_Marks

1001 Abhay 85

1002 Ankit 75

1003 Bheem 60

1004 Ram 79
 2021-22 Ranking

examp
le
Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 25000 Goa

202 Ankit 45000 Delhi

203 Bheem 30000 Goa

204 Ram 29000 Goa

205 Sumit 40000 Delhi

•Suppose, you want to show the details of those first four

employees whose city is Goa from the above table. To do this,
you have to type the following query in SQL:

S E L E C T TO P 4 * FR O M Em p lo yee
s e l e c t fi rs t _n a m e f ro m
W H ER E Em p _ Cit y = G o a ;
C u s to m e r s w h e r e country
 2021-22 Ranking

Output

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 25000 Goa

203 Bheem 30000 Goa

204 Ram 29000 Goa

 2021-22 Ranking

SQL TOP, LIMIT, FETCH

FIRST or
ROWNUM Clause
• SQL Server / MS Access Syntax:

SELECT TOP number|percent column_name(s)

FROM table_name
WHERE condition;

• MySQL Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
 2021-22 Ranking

LIMI
T
• The following SQL statement shows the equivalent
example for MySQL:
• Example
• Select the first 3 records of the Customers table:
SELECT * FROM
Customers LIMIT 3;
 2021-22 Ranking

FETCH FIRST

• The following SQL statement shows the equivalent

example for Oracle:
• Example
• Select the first 3 records of the Customers table:
SELECT * FROM
Customers FETCH FIRST
3 ROWS ONLY;
SQL TOP PERC E N T
202 1-22 Ranking

Example
• The following SQL statement selects the first 50% of
the records from the "Customers" table (for SQL
Server/MS Access):
• Example
SELECT TOP 50 PERCENT * FROM Customers;
 2021-22 Ranking

•Display the last three records of customer

table?

Select * from Customers

order by (customer_id) desc
limit 3;
Alternative way
// Select * from Customers
limit 2,3;
 2021-22 Ranking

•Write a query to find the third highest

salary of an employee from the employee
table? (mind tree interview question)

•Select name,salary from employee order

by salary desc limit 1 offset 2;
1. Calculate the total quantity sold for each product.
• SELECT Product, SUM(Quantity) AS TotalQuantity
• FROM Sales
• GROUP BY Product;
• Find the total revenue generated from each country.
SELECT Country, SUM(Quantity * Price) AS TotalRevenue
FROM Sales
GROUP BY Country;
• Find products with total sales quantity greater than 5.
SELECT Product, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY Product
HAVING SUM(Quantity) > 5;
• Get the countries with total revenue greater than $2000.
• SELECT Country, SUM(Quantity * Price) AS TotalRevenue
• FROM Sales
• GROUP BY Country
• HAVING SUM(Quantity * Price) > 2000;
• Constraints:
• There are no employees with the exact same
name, salary and department.
 2021-22 Ranking

SQL | Functions (Aggregate and Scalar

Functions)
• For doing operations on data SQL has many
built-in functions, they are categorized in two
categories and further sub-categorized in different
seven functions under each category. The categories
are:
[Link] functions:
These functions are used to do operations from the
values
1 AVG(of the column and a single value is
returned.
)
.2 COUNT(
)
. FIRST(
3.
)
4.
5 LAST(
MAX(
.6 )MIN()
. SUM(
7
. )
 2021-22 Ranking

Scalar functions:

These functions are based on user input, these too

returns single value.
2. UCASE()
3. LCASE()
4. MID()
5. LEN()
6. ROUND()
7. NOW()
8. FORMAT()
2021-22 Ranking
 2021-22 Ranking

• AVG(): It returns the average value after calculating from values in a

numeric column.

• Syntax:

SELECT AVG(column_name) FROM table_name;

• Query ?
Computing average marks of students.
SELECT AVG(MARKS) AS AvgMarks FROM Students;
• Computing average age of students.
SELECT AVG(AGE) AS AvgAge FROM Students;
 2021-22 Ranking

• COUNT(): It is used to count the number of rows returned in a SELECT

statement. It can’t be used in MS ACCESS.

• Syntax:

SELECT COUNT(column_name) FROM table_name;

Query

• Computing total number of students.

SELECT COUNT(*) AS NumStudents FROM Students;
• Computing number of students with unique/distinct age.
SELECT COUNT(DISTINCT AGE) AS NumStudents FROM Students;
 2021-22 Ranking

• FIRST(): The FIRST() function returns the first value of the selected
column.
• Syntax:

SELECT FIRST(column_name) FROM table_name;

• Queries:
Fetching marks of first student from the Students table.
SELECT FIRST(MARKS) AS MarksFirst FROM Students;
•Fetching age of first student from the Students
table. SELECT FIRST(AGE) AS AgeFirst FROM Students;
 2021-22 Ranking

• LAST(): The LAST() function returns the last value of the selected column. It
can be used only in MS ACCESS.

• Syntax:

SELECT LAST(column_name) FROM table_name;

• Queries:
Fetching marks of last student from the Students table.
SELECT LAST(MARKS) AS MarksLast FROM Students;
•Fetching age of last student from the Students
table. SELECT LAST(AGE) AS AgeLast FROM Students;
 2021-22 Ranking

• MAX(): The MAX() function returns the maximum value of the

selected column.

• Syntax:

SELECT MAX(column_name) FROM table_name;

• Fetching maximum marks among students from the Students table.

• SELECT MAX(MARKS) AS MaxMarks FROM Students;
•Fetching max age among students from the Students
table. SELECT MAX(AGE) AS MaxAge FROM Students;
 2021-22 Ranking

• MIN(): The MIN() function returns the minimum value of the selected
column.

• Syntax:

SELECT MIN(column_name) FROM table_name;

•Fetching minimum marks among students from the Students

table. SELECT MIN(MARKS) AS MinMarks FROM Students;
 2021-22 Ranking

• SUM(): The SUM() function returns the sum of all the values of the
selected column.

• Syntax:

SELECT SUM(column_name) FROM table_name;

• Fetching summation of total marks among students from the Students

table.
SELECT SUM(MARKS) AS TotalMarks FROM Students;
 2021-22 Ranking

Scalar Functions

• UCASE(): It converts the value of a field to uppercase.

• Syntax:

SELECT UCASE(column_name) FROM table_name; //not working

//SELECT Upper(column_name) FROM table_name;

•Converting names of students from the table Students to

uppercase. SELECT UCASE(NAME) FROM Students;
 2021-22 Ranking

• LCASE(): It converts the value of a field to lowercase.

• Syntax:

SELECT LCASE(column_name) FROM table_name; not working

// SELECT Lower(column_name) FROM table_name;

•Converting names of students from the table Students to

lowercase. SELECT LCASE(NAME) FROM Students;
 2021-22 Ranking

• MID(): The MID() function extracts texts from the text field.

• Syntax:

SELECT MID(column_name,start,length) AS some_name FROM

table_name; // NOT WORKING
• SELECT SUBSTRING(column_name,start,length) AS some_name FROM
table_name;
• SELECT SUBSTR(column_name,start,length) AS some_name FROM
table_name;

• specifying length is optional here, and start signifies start position ( starting from 1 )

•Fetching first four characters of names of students from the Students

table. SELECT MID(NAME,1,4) FROM Students;
 2021-22 Ranking

• LEN(): The LEN() function returns the length of the

value in a text field.
• Syntax:

SELECT LENGTH(column_name) FROM table_name;

•Fetching length of names of students from Students

table. SELECT LENGTH(NAME) FROM Students;
 2021-22 Ranking

• ROUND(): The ROUND() function is used to round a numeric field to the

number of decimals [Link]: Many database systems have
adopted the IEEE 754 standard for arithmetic operations, which says that
when any numeric .5 is rounded it results to the nearest even integer
i.e,
5.5 and 6.5 both gets rounded off to 6.

• Syntax:

SELECT ROUND(column_name,decimals) FROM table_name;

• decimals- number of decimals to be fetched.

•Fetching maximum marks among students from the Students
table. SELECT ROUND(MARKS,0) FROM table_name;
 2021-22 Ranking

• NOW(): The NOW() function returns the current system date and
time.

• Syntax:

SELECT NOW() FROM table_name;

SELECT datetime() FROM table_name;// for online compilers

• Fetching current system time.

SELECT NAME, NOW() AS DateTime FROM Students;
 2021-22 Ranking

• FORMAT(): The FORMAT() function is used to format how a field is to

be displayed.

• Syntax:

SELECT FORMAT(column_name,format) FROM table_name;

• Formatting current date as ‘YYYY-MM-DD’.

SELECT NAME, FORMAT(Now(),'YYYY-MM-DD') AS Date FROM
Students;
 SQL NULL fu n c t
20 21-22 Rank ing

ions
• Null values serve as placeholders for data that is either missing or not available. a null
value is a flexible data type that can be placed in the column of any data type
• Some key points regarding the use of NULL in SQL:
1. Comparison with NULL: Comparisons with NULL using regular comparison operators
like “=”, “<>”, “<“, “>” do not yield true or false but rather produce a result of unknown or
NULL. Instead, you need to use the IS NULL or IS NOT NULL operators to check for
NULL values.
2. Handling NULL in expressions: When performing arithmetic or other operations
involving NULL values, the result typically becomes NULL. For example, any arithmetic
operation that involves a NULL operand will result in a NULL result.
3. Aggregating NULL values: Most aggregate functions in SQL, such as SUM, AVG,
COUNT, etc., ignore NULL values when calculating results. However, there are some
aggregate functions like COUNT(*) that consider NULL values.
4. Indexing and NULL: Some database systems handle NULL values differently when it
comes to indexing.
5. It’s important to consult the specific documentation of your database management system
to understand how NULL values are treated in index structures. Potential pitfalls: The
presence of NULL values can introduce complexity and potential pitfalls when querying or
manipulating data. It requires careful consideration to handle NULL values appropriately in
SQL queries to avoid unexpected or incorrect results.
 2021-22 Ranking

Indexing
• It is a data structure technique used to locate and
quickly access data in databases.
• Indexing is a data structure technique which allows you to quickly
retrieve records from a database file.
• An Index is a small table having only two columns.
• The first column comprises a copy of the primary or candidate key of
a table.
• Its second column contains a set of pointers for holding the
address of the disk block where that specific key value stored.
• An index –
• Takes a search key as input
• Efficiently returns a collection of matching records.
2021-22 Ranking
 2021-22 Ranking

Primary Index in
DBMSIndex is an ordered file which is fixed length
• Primary
size with two fields. The first field is the same a
primary key and second, filed is pointed to that
specific data block. In the primary Index, there is
always one to one relationship between the entries in
the index table.
• The primary Indexing in DBMS is also further divided
into two types.
• Dense Index
• Sparse Index
 Dense
2021-22 Ranking

Index
• In a dense index, a record is created for every search
key valued in the database. This helps you to search
faster but needs more space to store index records. In
this Indexing, method records contain search key value
and points to the real record on the disk.
2021-22 Ranking
 2021-22 Ranking

Sparse
Index
• It is an index record that appears for only some of the values in the
file. Sparse Index helps you to resolve the issues of dense Indexing in
DBMS. In this method of indexing technique, a range of index
columns stores the same data block address, and when data needs to
be retrieved, the block address will be fetched.

• However, sparse Index stores index records for only some search-key
values. It needs less space, less maintenance overhead for insertion,
and deletions but It is slower compared to the dense Index for
locating records.

• Below is an database index Example of Sparse Index

2021-22 Ranking
 2021-22 Ranking

Secondary Index in
•DBMS
The secondary Index in DBMS can be generated by a field
which has a unique value for each record, and it should
be a candidate key. It is also known as a non-clustering
index.
• This two-level database indexing technique is used to
reduce the mapping size of the first level. For the first
level, a large range of numbers is selected because of
this; the mapping size always remains small.
 Following are the NULL functions
2021-22 Ranking

defined in SQL:
ISNULL()
• The ISNULL function has different uses in SQL Server and MySQL. In SQL
Server, ISNULL() function is used to replace NULL values.

• Syntax:

SELECT column(s), ISNULL(column_name, value_to_replace)

FROM table_name;
 2021-22 Ranking

• Example: Consider the following Employee

table,
 2021-22 Ranking

• Find the sum of the salary of all Employees, if the Salary of any
employee is not available (or NULL value), use salary as 10000.

• Query:

SELECT SUM(ISNULL(Salary, 10000) AS Salary

FROM Employee;
Output:
 2021-22 Ranking

• In MySQL, ISNULL() function is used to test whether an expression is NULL

or not. If the expression is NULL it returns TRUE, else FALSE.

• Syntax:

SELECT column(s)
FROM table_name
WHERE
ISNULL(column_na
me);

• Example:
Consider the
following
Employee table
 2021-22 Ranking

• Fetch the name of all employees whose salary is available in the table
(not NULL).

• Query:

SELECT Name
FROM Employee
WHERE
ISNULL(Salary);
• Output:
 2021-22 Ranking

IFNUL
L()
• This function is available in MySQL, and not in SQL Server or Oracle.
This function take two arguments. If the first argument is not NULL,
the function returns the first argument. Otherwise, the second
argument is returned. This function is commonly used to replace
NULL value with another value.

• Syntax:

SELECT column(s), IFNULL(column_name, value_to_replace)

FROM table_name;
 2021-22 Ranking

• Example: Consider the following Employee table,

Find the sum of the salary of all Employees, if

the Salary of any employee is not available (or
NULL value), use salary as 10000.
Query;
SELECT SUM(IFNULL(Salary, 10000) AS Salary
FROM
Employee;
 2021-22 Ranking

• Output:
 2021-22 Ranking

COALESC
•E()
COALESCE function in SQL returns the first non-NULL expression among its
arguments. If all the expressions evaluate to null, then the COALESCE
function will return null.

• Syntax:

SELECT column(s), COALESCE(expression_1,….,expression_n)

FROM table_name;
 2021-22 Ranking

• Example:
Consider the following Contact_info table,
 2021-22 Ranking

•Fetch the name and contact number of each

employee. Query:

SELECT Name, COALESCE(Phone1, Phone2) AS Contact

FROM Contact_info;
 2021-22 Ranking

NULLIF()
• The NULLIF function takes two arguments. If the two arguments are
equal, then NULL is returned. Otherwise, the first argument is returned.

• Syntax:

SELECT column(s), NULLIF(expression1, expression2)

FROM table_name;
 2021-22 Ranking

• Example: Consider the following Sales

table
 2021-22 Ranking

SELECT Store, NULLIF(Actual, Goal)

FROM Sales;
 2021-22 Ranking

For example we have a student

table having student details
STUDENT _ID STUDENT _NAME STUDENT _CITY

1 AMAN BHOPAL

2 RAJ INDORE

3 SHIKHA BHOPAL

4 SHRUTI INDORE
This is dept table which has
2021-22 Ranking

department details
DEPT _NO. DEPT _NAME STUDENT _ID

D1 CSE 1

D2 EC 2

D3 IT 3
2021-22 Ranking
 2021-22 Ranking

• If we want to access student details we can use student table

• If we want to access department details we can use the department
table
• In such cases we don’t need join
• But when the query is
•Find student names whose department
is IT ????
•For such questions we use joins where we need help of two
or more tables.
•There must be some common attribute in both the tables for
performing join on them.
 2021-22 Ranking

Join is always

Join =cross product + Select statement (condition)

 2021-22 Ranking

Natural Join
•We use natural join when we want to equalize
the values of common attributes in two tables.
•“Find the student names who belong to
any department?”
•Now find the tables which will help us to find the result.
Select Student_name from Student,Department where
Student.Student_id=Department.Student_id;
•Student, department means cross product of the
two tables.
 2021-22 Ranking

Natural join
 2021-22 Ranking

STUDENT_ID STUDENT_NA DEPT_NO. STUDENT_ID

ME
1 AMAN D1 1

1 AMAN D2 2

1 AMAN D3 3

2 RAJ D1 1

2 RAJ D2 2

2 RAJ D3 3
 2021-22 Ranking

• Actual way of writing this query

•Select Student_name from Student natural join Department

• Syntax :
• We will perform the natural join query by using the following syntax.

SELECT * FROM TABLE1

NATURAL JOIN TABLE2;
 2021-22 Ranking

Features of Natural Join

• Here, we will discuss the features of natural join.

[Link] will perform the Cartesian product.
[Link] finds consistent tuples and deletes inconsistent
tuples.
[Link] it deletes the duplicate attributes.
 2021-22 Ranking

Self Join
• Here the table is joined with itself.
• Lets see in what scenario we need a table to join a table with itself

S_ID C_ID YEAR

S1 C1 2016

S2 C2 2017

S1 C2 2017
 2021-22 Ranking

•Here the S_ID takes reference from a student

table
•And C_ID takes reference from a Course table.
•But we don’t need the base tables. We only need
•the
Findgiven table.
Student_id who is enrolled in at least two
•For example
courses ???we have a query :

Answer query:
Select T1.S_ID from student_course as T1,
student_course as T2
where T1.S_ID=T2.S_ID
and
T1.C_ID<>T2.C_ID
 2021-22 Ranking

So check now
S1 =S1
And
C1 <> C2
 2021-22 Ranking

S1 C1 S1 C1

S1 C1 S1 C1

S1 C1 S2 C2

S1 C1 S1 C2
 2021-22 Ranking

• It basically allows us to combine the rows from

the same table based on some specific
conditions.
• It is very useful and easy to work with, and it
allows us to retrieve data or information which
involves comparing records within the same
table.
 2021-22 Ranking

Equi Join
E_NO E_NAME ADDRESS
[Link] LOCATION E_NO

1 AMAN BHOPAL
D1 BHOPAL 1

2 SHRUTI INDORE D2 GWALIOR 2

3 RAJ INDORE D3 SHIVPURI 4

4 SHIKHA BHOPAL
 2021-22 Ranking

•Select E_NAME from Emp, Dept

•where Emp.E_NO=Dept.E_NO
•and [Link]=[Link];

• Difference between Natural join and equi join is that :

• Natural join only equalizes the common attribute in both tables ,but
equi join can make equal any two attributes from both the tables.
 2021-22 Ranking

1 AMAN BHOPAL D1 BHOPAL 1

1 AMAN BHOPAL D2 GWALIOR 2
1 AMAN BHOPAL D3 SHIVPURI 4
2 SHRUTI INDORE D1 BHOPAL 1
2 SHRUTI INDORE D2 GWALIOR 2
2 SHRUTI INDORE D3 SHIVPURI 4
3 RAJ INDORE D1 BHOPAL 1
3 RAJ INDORE D2 GWALIOR 2
3 RAJ INDORE D3 SHIVPURI 4
 2021-22 Ranking

• EQUI JOIN creates a JOIN for equality or matching column(s) values of the relative
tables. EQUI JOIN also create JOIN by using JOIN with ON and then providing the
names of the columns with their relative tables to check equality using equal sign (=).

• Syntax :

SELECT column_list
FROM table1, table2....
WHERE table1.column_name =
table2.column_name;

• Example –

SELECT [Link], [Link], [Link], [Link]

FROM student, record
WHERE [Link] = [Link];
 2021-22 Ranking

Or another syntax for equi join

Syntax :

SELECT column_list
FROM table1
JOIN table2
[ON
(join_condition)]

• Example –

SELECT [Link], [Link], [Link], [Link]

FROM student
JOIN record
ON [Link] = [Link];
 2021-22 Ranking

NON EQUI
JOIN :
• NON EQUI JOIN performs a JOIN using comparison operator other than equal(=) sign like >,
<, >=, <= with conditions.

• Syntax:

SELECT *
FROM table_name1, table_name2
WHERE table_name1.column [> | < | >= | <= ] table_name2.column;

• Example –

SELECT [Link], [Link], [Link]

FROM student, record
WHERE [Link] < [Link] ;
 2021-22 Ranking

LEFT JOIN OR LEFT

OUTER
• This JOIN
join returns all the rows of the table on the left side of the join and matching rows for
the table on the right side of the join.
• For the rows for which there is no matching row on the right side, the result-set will
contain null. LEFT JOIN is also known as LEFT OUTER JOIN.

• Syntax:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

1. table1: First table.

2. table2: Second table
3. matching_column: Column common to both the tables.
2021-22 Ranking
 2021-22 Ranking

Example to understand

Emp_no E_name Dept_no Dept_no D_name city

E1 AKASH D1 D1 DEVELOP MUMBAI

MENT

E2 AMAN D2 D2 TESTING PUNE

E3 RAM D1 D3 QUALITY KOLKATA

ASSURAN
CE
E4 SHYAM -
 2021-22 Ranking

Select Emp_no, E_name, D_name, city

from emp
left outer join
dept on(emp Dept_no = dept.Dept_no);
Start matching rows from t h e l e ft table so, D1 is present in table1 and
202 1- 22 Ran ki ng

table 2 ,d2 is present in table 1 and table 2,D1 is present in table 1 and
table 2 so we will select the corresponding rows but for E4 the deptno is
null so it has no matching value in table 2 hence it will get displayed as it
is.
 2021-22 Ranking

This should be the output

E_no E_name D_name City

E1 AKASH DEVELOPMENT MUMBAI
E2 AMAN TESTING PUNE
E3 RAM DEVELOPMENT MUMBAI
E4 SHYAM
 2021-22 Ranking

Example Queries(LEFT
JOIN):

SELECT [Link],StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
2021-22 Ranking
 2021-22 Ranking

RIGHT
JOIN
• RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on
the right side of the join and matching rows for the table on the left side of
the join. For the rows for which there is no matching row on the left side, the
result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.

• Syntax:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

1. table1: First table.

2. table2: Second table
3. matching_column: Column common to both the
tables.
2021-22 Ranking
 2021-22 Ranking

Consider the following query:

Select emp_no, e_name,d_name, city

from emp
right outer join dept on
(emp.dept_no.=dept.dept_no);
 Consider the two tables and match
2021-22 Ranking

the rows of right table with left

table
Emp_no E_name Dept_no Dept_no D_name city

E1 Sid D1 D1 IT Mumbai

D2 HR Indore
E2 Raj D2

D3 FINANCE Bhopal
E3 Riya D3

D4 TESTING Agra
 2021-22 Ranking

Emp_no E_name D_name city

E1 Sid IT Mumbai
E2 Raj HR Indore
E3 Riya FINANCE Bhopal
TESTING Agra
 2021-22 Ranking

Example Queries(RIGHT
JOIN):

SELECT [Link],StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
2021-22 Ranking
 2021-22 Ranking

FULL JOIN

• FULL JOIN creates the result-set by combining

results of both LEFT JOIN and RIGHT JOIN. The result-
set will contain all the rows from both tables. For
the rows for which there is no matching, the result-
set will contain NULL values.
 2021-22 Ranking

• Syntax:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

1. table1: First table.

2. table2: Second table
3. matching_column: Column common to both the
tables.

• Example QuerY (FULL JOIN):

SELECT [Link],StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
 2021-22 Ranking

• Dataset: Orchestras
• The following exercises use the orchestras dataset
that contains three tables.

1 1
 2021-22 Ranking

• The orchestras table stores all orchestras. The columns are id, name,
rating, city_origin, country_origin, and year in which the orchestra
was founded.
• The concerts table contains all concerts played by the orchestras. The
columns are id, city, country, year, rating, and orchestra_id (references
the orchestras table).
• The members table stores the members of (i.e. musicians playing in)
each orchestra. The columns are id, name, position (i.e. the
instrument played), wage, experience, and orchestra_id (references
the orchestras table).
 2021-22 Ranking

• Exercise:
• Select the names of all orchestras that have the
same city of origin as any city in which any orchestra
performed in 2013.
 2021-22 Ranking

SELECT name FROM orchestras

WHERE city_origin IN (SELECT city FROM concerts WHERE year =
2013);

Or
Select distinct name from orchestras join concerts c on
c.city_origin=[Link] where [Link]=2013;
 2021-22 Ranking

• Exercise:
• Select the names and positions (i.e. instrument
played) of all orchestra members that have above
10 years of experience and do not belong to
orchestras with a rating below 8.0.
 2021-22 Ranking

SELECT
name,
position
FROM members
WHERE experience > 10
AND orchestra_id NOT IN (SELECT id FROM orchestras WHERE rating < 8.0);
Or
SELECT
[Link],
[Link]
FROM members join orchestras on
[Link]=[Link]
WHERE [Link] > 10
AND [Link] >= 8.0
 •The dataset consists of four tables: author, book, adaptation, and
book_review. The first table shows the author data in the following columns:
id name birth_year death_year
1 Marcella Cole 1983 NULL
2 Lisa Mullins 1891 1950
3 Dennis Stokes 1935 1994
4 Randolph Vasquez 1957 2004
5 Daniel Branson 1965 1990
The second table,
book
id author_id title publish_ye publishing rating
ar _house

1 NULL Soulless girl 2008 Golden 4.3

Albatros

2 NULL Weak 1980 Diarmud 3.8

Heart Inc.

3 4 Faith Of 1995 White 4.3

Light Cloud Press

4 NULL Memory Of 2000 Rutis 2.7

Hope Enterprises

5 6 Warrior Of 2005 Maverick 4.6

Wind
The adaptation table
book_id type title release_year rating
Gone With The
1 movie Wolves: The 2008 3
Beginning

3 movie Companions 2001 4.2

Of Tomorrow
5 movie Homeless 2008 4
Warrior
2 movie Blacksmith 2014 4.3
With Silver
4 movie Patrons And 2004 3.2
Bearers
The final table is
book_review
book_id review author
1 An incredible book Sylvia Jones

1 Great, although it has Jessica Parker

some flaws
Dennis Stokes takes the
2 reader for a ride full of Thomas Green
emotions

3 Incredible craftsmanship Martin Freeman

of the author
4 Not the best book by this Jude Falth
author
5 Claudia Johnson at her Joe Marqiz
best!
6 I cannot recall more Alexander Durham
captivating plot
Exercis
e:1
• Show the name of each author together with the title of the book
they wrote and the year in which that book was published.
SELECT name,title, publish_year
FROM author
JOIN book ON [Link] =
book.author_id;
Exercis
e:2
•
Show the name of each author together with the title of the book
they wrote and the year in which that book was published. Show only
books published after 2005.
SELECT name, title, publish_year
FROM author
JOIN book ON [Link] = book.author_id
WHERE publish_year > 2005;
Exercis
e:3
• Show the title of each book together with the title of its adaptation
and the date of the release. Show all books, regardless of whether
they had adaptations.

•
SELECT [Link], [Link], adaptation.release_year
FROM book LEFT JOIN adaptation ON [Link] = adaptation.book_id;
Exercis
e:4
•
Show all books with their movie adaptations. Select each book's title,
the name of its publishing house, the title of its adaptation, and the
type of the adaptation. Keep the books with no adaptations in the
result.
SELECT [Link], publishing_house,
[Link], [Link] FROM book LEFT
JOIN adaptation ON [Link] = adaptation.book_id
WHERE type = 'movie’ OR type IS NULL;
Exercis
e:5
•
Join the book_review and book tables using a RIGHT JOIN. Show the
title of the book, the corresponding review, and the name of the
review's author. Consider all books, even those that weren't reviewed.
SELECT [Link], book_review.review, book_review.author
FROM book_review RIGHT JOIN book ON [Link] =
book_review.book_id;
Exercise:6

• Display the title of each book along with the name of its author. Show
all books, even those without an author. Show all authors, even those
who haven't published a book yet.
SELECT title, name
FROM book
FULL JOIN author
ON
book.author_id =
[Link];
Join practice on customers and
orders table
1. Select the names of those customers who have ordered any item ?

Select first_name ,item from Customers natural join order;

Or
Select first_name ,item from Customers join order on
customers.customer_id=orders.customer_id;
1. Select the names of all customers regardless of whether they have
ordered any item or not?
• Select first_name ,item from Customers left join order on
customers.customer_id=orders.customer_id;
departm
ent
id name
1 fruits
2 vegetables
3 seafood
4 deli
5 bakery
6 meat
7 dairy
produ
ct
id name departmen shelf_id producer_i price
t_id d
1 Apple 1 1 NULL 0.5
2 Avocado 1 1 7 1
3 Banana 1 1 7 0.5
4 Grapefruit NULL 1 1 0.5
5 Grapes 1 1 4 2
nutrition_d
ata
product_id calories fat carbohydrate protein
1 130 0 5 1
2 50 4.5 3 1
3 110 0 30 1
4 60 0 15 1
NULL 90 0 23 0
… … … … …
produc
er
id name
1 BeHealthy
2 HealthyFood Inc.
3 SupremeFoods
4 Foodie
5 Gusto
6 Baker n Sons
7 GoodFoods
8 Tasty n Healthy
sales_hist
ory
date product_id amount
2015-01-14 1 14
2015-01-14 1 13
2015-01-15 2 2
2015-01-16 2 6
2015-01-17 3 8
• List all products that have fewer than 150 calories. For each product,
show its name (rename the column product) and the name of the
department where it can be found (name the column department).
SELECT
[Link] AS product,
[Link] AS department
FROM department d
JOIN product p
ON [Link] = p.department_id
JOIN nutrition_data nd
ON nd.product_id = [Link]
WHERE [Link] < 150;
Exercise: For each product, display the:

Name of the company that produced it (name the column producer_name).

Name of the department where the product is located (name it
department_name).
Product name (name it product_name).
Total number of carbohydrates in the product.
Your query should still consider products with no information about
producer_id or department_id.
SELECT
[Link] AS producer_name,
[Link] AS department_name,
[Link] AS product_name,
[Link]
FROM product p
LEFT JOIN producer prod
ON [Link] = p.producer_id
LEFT JOIN department d
ON [Link] = p.department_id
LEFT JOIN nutrition_data nd
ON nd.product_id = [Link];
• Exercise: For each product, show its name, price, producer name, and
department name.

• Alias the columns as product_name, product_price, producer_name,

and department_name, respectively. Include all the products, even
those without a producer or department. Also, include the producers
and departments without a product.
SELECT
[Link] AS product_name,
[Link] AS product_price,
[Link] AS producer_name,
[Link] AS department_name
FROM product p
FULL JOIN producer prod
ON p.producer_id = [Link]
FULL JOIN department d
ON [Link] = p.department_id;
workshop_wor
kers specializati
id name master_id experience project_id
on
1 Mathew woodworki NULL 20 1
Conn ng
2 Kate Brown woodworki 1 4 1
ng
3 John Doe incrusting 5 3 1

4 John watchmaki 7 2 3
Kowalsky ng
5 Suzan incrusting NULL 15 4
Gregowitch
• Exercise: Show all workers' names together with the names of their
direct supervisors. Rename the columns apprentice_name and
master_name, respectively. Consider only workers who have a
supervisor (i.e. a master).
SELECT
[Link] AS apprentice_name,
[Link] AS master_name
FROM workshop_workers apprentice
JOIN workshop_workers master
ON apprentice.master_id =
[Link];
Select country ,sum(quantity*price) from sales
group by country
having sum (quantity*price) >2000;
 2021-22 Ranking

MySQL | Grant / Revoke Privileges

• Syntax:
GRANT privileges_names ON object TO user;

Parameters Used
• privileges_name: These are the access rights or
privileges granted to the user.
• object: It is the name of the database object to
which permissions are being granted. In the case
of granting privileges on a table, this would be the
table name.
• user: It is the name of the user to whom the privileges
would be granted.

1 1
• Privileges: The privil e g e s that can be granted to the
20 21- 22 R an king

users are listed below along with the description:

 2021-22 Ranking

• Let us now learn about different ways of granting

privileges to the users:
• Granting SELECT Privilege to a User in a Table:
1. To grant Select Privilege to a table named “users” where User
Name is Amit, the following GRANT statement should be
executed.
[Link] general syntax of specifying a username is:
‘user_name’@’address’
3. If the user ‘Amit’ is on the local host then we have to
mention it as ‘Amit’@’localhost’. Or suppose if the ‘Amit’
username is on [Link] IP address then we have to
mention it as ‘Amit’@’[Link]’.
• ‘user_name’@’address’ – When you’re granting or revoking
permissions in MySQL, you use the ‘username’ or
‘hostname’ format to tell which users are allowed or denied.
This is important for keeping security and access control
in place, so here’s why we use it:
• Granularity of Access Control
• Multi-User Environments
• User identification
 2021-22 Ranking

• Granting more than one Privilege to a User in a Table: To grant multiple

Privileges to a user named “Amit” in a table “users”, the following GRANT
statement should be executed.
• GRANT SELECT, INSERT, DELETE, UPDATE ON Users TO 'Amit'@'localhost';
• Granting All the Privilege to a User in a Table: To Grant all the privileges to a
user named “Amit” in a table “users”, the following Grant statement should
be executed.
• GRANT ALL ON Users TO 'Amit'@'localhost';
• Granting a Privilege to all Users in a Table: To Grant a specific privilege to all
the users in a table “users”, the following Grant statement should be
executed.
• GRANT SELECT ON Users TO '*'@'localhost';
• In the above example the “*” symbol is used to grant select permission to
all the users of the table “users”.
 2021-22 Ranking

• Granting Privileges on Functions/Procedures: While using functions

and procedures, the Grant statement can be used to grant users the
ability to execute the functions and procedures in MySQL. Granting
Execute Privilege: Execute privilege gives the ability to execute a
function or [Link]:
• GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user;
 2021-22 Ranking

• Different ways of granting EXECUTE Privileges

• Granting EXECUTE privileges on a function in MySQL.: If there is a
function named “CalculateSalary” and you want to grant EXECUTE
access to the user named Amit, then the following GRANT statement
should be executed.
• GRANT EXECUTE ON FUNCTION Calculatesalary TO
'Amit'@'localhost';
 2021-22 Ranking

• Granting EXECUTE privileges to all Users on a function in MySQL.: If

there is a function named “CalculateSalary” and you want to grant
EXECUTE access to all the users, then the following GRANT statement
should be executed.
• GRANT EXECUTE ON FUNCTION Calculatesalary TO '*'@'localhost';
 2021-22 Ranking

• Granting EXECUTE privilege to a Users on a procedure in MySQL.: If

there is a procedure named “DBMSProcedure” and you want to grant
EXECUTE access to the user named Amit, then the following GRANT
statement should be executed.
• GRANT EXECUTE ON PROCEDURE DBMSProcedure TO
'Amit'@'localhost';
 2021-22 Ranking

• Granting EXECUTE privileges to all Users on a procedure in MySQL.: If

there is a procedure called “DBMSProcedure” and you want to grant
EXECUTE access to all the users, then the following GRANT statement
should be executed.
• GRANT EXECUTE ON PROCEDURE DBMSProcedure TO '*'@'localhost';
 2021-22 Ranking

• Checking the Privileges Granted to a User: To see the privileges

granted to a user in a table, the SHOW GRANTS statement is used. To
check the privileges granted to a user named “Amit” and host as
“localhost”, the following SHOW GRANTS statement will be executed:
• SHOW GRANTS FOR 'Amit'@'localhost';
• Output:

• GRANTS FOR Amit@localhost

• GRANT USAGE ON *.* TO `SUPER`@`localhost`
 2021-22 Ranking

• Revoking Privileges from a Table

• The Revoke statement is used to revoke some or all of the privileges which
have been granted to a user in the past.

• Syntax:

• REVOKE privileges ON object FROM user;

• Parameters Used:

• object: It is the name of the database object from which permissions are
being revoked. In the case of revoking privileges from a table, this
would be the table name.
• user: It is the name of the user from whom the privileges are being
revoked.
2021-22 Ranking
 2021-22 Ranking

• Different Ways of revoking privileges from a user

• Revoking SELECT Privilege to a User in a Table: To revoke Select
Privilege to a table named “users” where User Name is Amit, the
following revoke statement should be executed.
• REVOKE SELECT ON Users FROM 'Amit'@'localhost';
 2021-22 Ranking

• Revoking more than Privilege to a User in a Table: To revoke multiple

Privileges to a user named “Amit” in a table “users”, the following
revoke statement should be executed.
• REVOKE SELECT, INSERT, DELETE, UPDATE ON Users FROM
'Amit'@'localhost';
 2021-22 Ranking

• Revoking All the Privilege to a User in a Table: To revoke all the

privileges to a user named “Amit” in a table “users”, the following
revoke statement should be executed.
• REVOKE ALL ON Users FROM 'Amit'@'localhost';
 2021-22 Ranking

• Revoking a Privilege to all Users in a Table: To Revoke a specific

privilege to all the users in a table “Users”, the following revoke
statement should be executed.
• REVOKE SELECT ON Users FROM '*'@'localhost';
 2021-22 Ranking

• Revoking Privileges on Functions/Procedures: While using functions

and procedures, the revoke statement can be used to revoke the
privileges from users which have been EXECUTE privileges in the past.
Syntax:
• REVOKE EXECUTE ON [ PROCEDURE | FUNCTION ] object FROM User;
 2021-22 Ranking

• Revoking EXECUTE privileges on a function in MySQL: If there is a

function called “CalculateSalary” and you want to revoke EXECUTE
access to the user named Amit, then the following revoke statement
should be executed.
• REVOKE EXECUTE ON FUNCTION Calculatesalary FROM
'Amit'@'localhost';
 2021-22 Ranking

• Revoking EXECUTE privileges to all Users on a function in MySQL: If

there is a function called “CalculateSalary” and you want to revoke
EXECUTE access to all the users, then the following revoke statement
should be executed.
• REVOKE EXECUTE ON FUNCTION Calculatesalary FROM
'*'@'localhost';
 2021-22 Ranking

• Revoking EXECUTE privilege to a Users on a procedure in MySQL: If

there is a procedure called “DBMSProcedure” and you want to
revoke EXECUTE access to the user named Amit, then the following
revoke statement should be executed.
• REVOKE EXECUTE ON PROCEDURE DBMSProcedure FROM
'Amit'@'localhost';
 2021-22 Ranking

• Revoking EXECUTE privileges to all Users on a procedure in MySQL: If

there is a procedure called “DBMSProcedure” and you want to
revoke EXECUTE access to all the users, then the following revoke
statement should be executed.
• REVOKE EXECUTE ON PROCEDURE DBMSProcedure FROM
'*'@'localhost';