KAILASH

SRI

WOMEN’SCOLLEGE
(Affiliated to Periyar University)
Periyeri(village),Thalaivasal(Via)
Attur (Tk), Salem (Dt) -636112.

DEPARTMENT
OF
COMPUTERSCIENCE

DBMS
2025-2026
III CS
23UCS05
 UNIT-I-DATABASE CONCEPT:
Database Concepts:Database Systems - Data vs Information - Introducing the database -File
system - Problems with file system – Database systems. Data models - Importance - Basic
Building Blocks - Business rules - Evolution of Data models - Degrees of Data Abstraction

UNIT-II-DESIGN CONCEPT
Design Concepts: Relational database model - logical view of data-keys –Integrity rules -
relational set operators - data dictionary and the system catalog - relationships -data redundancy
revisited -indexes - codd's rules. Entity relationship model - ER diagram

UNIT-III-NORMALIZATION OF DATABASE TABLES:

Normalization of Database Tables: Database tables and Normalization – The Need for
Normalization –The Normalization Process – Higher level Normal Form. Introduction to SQL:
Data Definition Commands – Data Manipulation Commands – SELECT Queries – Additional
Data Definition Commands – Additional SELECT Query Keywords – Joining Database Tables.

UNIT-IV-ADVANCED SQL:
Advanced SQL:Relational SET Operators: UNION – UNION ALL – INTERSECT - MINUS.SQL Join
Operators: Cross Join – Natural Join – Join USING Clause – JOIN ON Clause – Outer Join.Sub Queries
and Correlated Queries: WHERE – IN – HAVING – ANY and ALL – FROM. SQL Functions: Date and
Time Function – Numeric Function – String Function – Conversion

UNIT-V-PL/SQL
PL/SQL:A Programming Language: History – Fundamentals – Block Structure – Comments –
Data Types – Other Data Types – Variable Declaration – Assignment operation –Arithmetic
operators.Control Structures and Embedded SQL: Control Structures – Nested Blocks – SQL in
PL/SQL – Data Manipulation – Transaction Control statements. PL/SQL Cursors and Exceptions:
Cursors – Implicit Cursors, Explicit Cursors and Attributes – Cursor FOR loops – SELECT…FOR
UPDATE – WHERE CURRENT OF clause – Cursor with Parameters – Cursor Variables –
Exceptions – Types of Exceptions.
 DATABASE MANAGEMENT SYSTEM
UNIT-I
Database Concepts:Database Systems - Data vs Information - Introducing the database -File system -
Problems with file system – Database systems. Data models - Importance - Basic Building
Blocks - Business rules - Evolution of Data models - Degrees of Data Abstraction.

DBMS:
 A Database Management System (DBMS) is a software that enables users to define, create,
maintain, and control access to the database.
 It provides an interface for performing various operations such as database creation, data storage,
updating, and deletion.

User

Applications DBMS Database

OSSoftware

Hardware

The history of Database Management Systems (DBMS) can be summarized as follows:

Early Developments (1960s)

 Flat File Systems: Initially, data was stored in flat files, which lacked structure and were prone to
redundancy and inconsistency.
 Hierarchical and Network Models: Early DBMS, such as the IMS (Information Management
System) developed by IBM, implemented hierarchical data models. The network model
followed, allowing more complex relationships.

Relational Model (1970s)

 E.F. Codd's Relational Model: In 1970, Edgar F. Codd introduced the relational model, which
organized data into tables (relations). This revolutionized data management and led to the
development of SQL (Structured Query Language).
 First Relational DBMS: Systems like IBM's System R and Oracle emerged, implementing Codd's
principles.
 Growth and Standardization (1980s)

 Commercialization: The 1980s saw the rise of commercial relational DBMS like Oracle, Ingres,
and Sybase.
 SQL Standardization: The American National Standards Institute (ANSI) standardized SQL, which
became the standard language for interacting with relational databases.

Object-Oriented and NoSQL Databases (1990s)

 Object-Oriented DBMS: With the rise of object-oriented programming, object-oriented DBMS

emerged, allowing complex data types and relationships.
 NoSQL Databases: The late 1990s brought about NoSQL databases designed for unstructured
data, scalability, and high performance (e.g., MongoDB, Cassandra).

Big Data and Cloud Databases (2000s and Beyond)

 Big Data Technologies: The explosion of data led to the development of technologies like
Hadoop and distributed databases to handle large volumes of data.
 Cloud-Based DBMS: Cloud computing introduced new DBMS models, offering scalable and
managed database services (e.g., Amazon RDS, Google Cloud SQL).

Modern Trends

 Multi-Model Databases: Recent developments include multi-model databases that

support various data models (e.g., document, graph, relational) within a single
database system.
 AI and Machine Learning: Integration of AI and machine learning capabilities into DBMS for
advanced data analytics and management

Components of a Database:

Data: The actual information stored (e.g., names, addresses, orders).

DBMS (Database Management System): Software used to manage and interact with
the database (e.g., MySQL, PostgreSQL, Oracle).
 Schema: The structure of the database, including tables, fields, and relationships.
 Queries: Instructions written in a query language (like SQL) to access or modify data.

Types of Databases:

 Relational Database: Uses tables with rows and columns (e.g., SQL-based).
 NoSQL Database: For unstructured or semi-structured data (e.g., MongoDB, Redis).
 Distributed Database: Data is spread across multiple physical locations.
 Cloud Database: Hosted on cloud platforms (e.g., Amazon RDS, Google Firebase).

DATABASE SYSTEM:

1.Database:

 A structured collection of data that can be easily accessed, managed, and updated.
 Examples: MySQL, PostgreSQL, Oracle, MongoDB
2. Database Management System (DBMS)

 Software that interacts with the user, applications, and the database itself to capture and analyze
data.
 Functions include:

 Data storage, retrieval, and update

 Transaction processing
 Backup and recovery

3. Data Models

 Ways to structure and organize data:

o Relational Model (tables): Most common (e.g., SQL)
o Entity-Relationship Model: High-level conceptual model
o Object-Oriented Model
o Document or NoSQL Models

4. Schemas and Instances

 Schema: The structure/blueprint of the database (e.g., tables and relationships)

 Instance: The actual data at a given point in time

5. SQL (Structured Query Language)

 Standard language for querying and managing relational databases.

Key operations:

 SELECT, INSERT, UPDATE, DELETE

6. Normalization

 Process of organizing data to reduce redundancy and improve data integrity.

 Involves dividing a database into two or more tables and defining relationships.

 CREATE TABLE, DROP, ALTER

7. Transactions and ACID Properties

 Transaction: A sequence of operations treated as a single unit.

 ACID:

 Atomicity: All or nothing

 Consistency: Maintains database rules
 Isolation: Transactions don’t interfere with each other
 Durability: Once committed, it stays committed

8. Indexing

 Improves the speed of data retrieval operations.

 Like an index in a book: makes lookup faster without scanning everything.

9. Concurrency Control

 Manages simultaneous operations without conflicting, ensuring data consistency

10. Backup and Recovery

 Ensures data is not lost and can be restored in case of failure.

DATA VS INFORMATION:

 Data refers to raw facts, figures, or symbols that lack context.

 Information is the result of processing and organizing data to make it meaningful and
useful.

What is Data?
 Data refers to raw, unprocessed facts and figures that lack context or interpretation on
their own. This information, in its raw form, can be difficult to understand or apply
without extra processing.

 Text, multimedia (pictures, videos, audio), and numerical numbers are all acceptable
formats for data collection. These types of data are critical in many disciplines, including
science, business, and technology, where they serve as the foundation for analysis and
decision-making.

 To illustrate, consider a dataset containing temperature readings from a specific city.

These temperature readings, recorded at various times throughout the day, are the raw
data.

 On their own, these numbers do not convey much meaning. However, when processed,
organized, and analyzed, this data can provide valuable insights.

 Trends in temperature changes over time can be identified, helping meteorologists

forecast weather conditions. Additionally, city planners might use this information to
address urban heat islands or optimize energy consumption based on temperature
variations

What is Information?
Information is data that has been processed, organized, or structured to convey meaning and
significance.

Unlike raw data, information is more comprehensible and provides context that aids in
understanding the data.

The transformation from data to information generally involves several key steps:

1. Data Collection: Gathering raw data from various sources, such as weather stations, satellites,
or sensors.

2. Data Cleaning: Ensuring the data is accurate, consistent, and free from errors or outliers.

3. Data Analysis: Applying statistical methods and computational algorithms to identify patterns,
correlations, and trends within the data.

4. Data Interpretation: Making sense of the analyzed data by providing context and explaining
what the data signifies.
 5. Data Presentation: Organizing the data in a coherent and visually appealing manner, such as
charts, graphs, or reports, to effectively communicate the information.

To illustrate, consider a dataset that includes the average temperature, humidity, and wind
direction of a city. When this data is processed and presented in an organized manner, it becomes
information that describes the city’s climate conditions

Difference between information and data

Data Information

Data is the raw fact. It is a processed form of data.

It is not significant to a business. It is significant to a business.

Data is an atomic level piece of

It is a collection of data.
information.

Example: Product name, Name of student. Example: Report card of student.

It is a phenomenal fact. It is organized data.

It is a secondary level of
This is the primary level of intelligence.
intelligence.

May or may not be meaningful. Always meaningful.

Understanding is difficult. Understanding is easy.

INTRODUCING THE DATABASE:

Databases and database technology have a major impact on the growing use of
computers. It is fair to say that databases play a critical role in almost all areas
where computers are used, including business, electronic commerce, engineering,
medicine, genetics, law, education, and library science.

FILE SYSTEM:

The file system in a Database Management System (DBMS) refers to the way data is stored,
organized, and accessed on a storage medium. Here's an overview of how file systems operate
within a DBMS:
Key Concepts of File Systems in DBMS

1. Data Storage:
o Data is stored in files on a physical storage medium (like hard drives or SSDs).
o Files can be structured (e.g., tables in a relational database) or unstructured (e.g.,
images, documents).
2. File Organization:
o Heap Files: Unordered file organization where records are stored as they arrive.
o Sequential Files: Records are stored in a specific sequence based on a key attribute.
o Indexed Files: Files that use indexes to enhance data retrieval speed.
3. Access Methods:
o Sequential Access: Reading data in a linear fashion.
o Random Access: Accessing data directly through an index.
4. Buffer Management:
o Buffers are used to temporarily store data in memory to optimize reading and writing
operations.
o The DBMS manages buffer pools to reduce disk I/O operations and improve
performance.
5. File Formats:
o Data files can be in various formats, such as CSV, binary, or proprietary formats defined
by the DBMS.
6. Security and Integrity:
o The DBMS implements security measures (like access controls) to protect data stored in
files.
o Integrity constraints ensure that the data is accurate and consistent.
7. Backup and Recovery:
o The file system is responsible for managing backups and recovery processes to prevent
data loss.

The file system is a critical component of a DBMS, enabling efficient data storage, retrieval, and
management while ensuring data integrity and security

FILE SYSTEM VS DBMS

Basics File System DBMS

The file system is a way of

arranging the files in a
storage medium within a DBMS is software for managing
Structure computer. the database.

Data Redundant data can be In DBMS there is no redundant

Redundancy present in a file system. data.
 It doesn't provide Inbuilt
It provides in house tools for
Backup and mechanism for backup and backup and recovery of data
Recovery recovery of data if it is lost. even if it is lost.

Query There is no efficient query Efficient query processing is

processing processing in the file system. there in DBMS.

There is less data

There is more data consistency
consistency in the file because of the process
Consistency system. of normalization .

It has more complexity in

It is less complex as handling as compared to the file
Complexity compared to DBMS. system.

File systems provide less

DBMS has more security
Security security in comparison to mechanisms as compared to file
Constraints DBMS. systems.

It is less expensive than It has a comparatively higher

Cost DBMS. cost than a file system.

In DBMS data
independence exists, mainly of
two types:

1) Logical Data Independence .

Data There is no data
Independence independence. 2)Physical Data Independence.

Only one user can access Multiple users can access data
User Access data at a time. at a time.

The user has to write

The users are not required to procedures for managing
Meaning write procedures. databases

Data is distributed in many

files. So, it is not easy to Due to centralized nature data
Sharing share data. sharing is easy
Data It give details of storage and It hides the internal details
Abstraction representation of data of Database

Integrity Integrity Constraints are Integrity constraints are easy to

Constraints difficult to implement implement

To access data in a file , user

requires attributes such as
Attribute s file name, file location. No such attributes are required.

Example Cobol , C++ Oracle , SQL Server

File systems in Database Management Systems (DBMS) can encounter various problems that
may affect performance, reliability, and data integrity. Here are some common issues associated
with file systems in DBMS:

Common Problems with File Systems in DBMS

1. Data Corruption:
o Data can become corrupted due to unexpected shutdowns, hardware failures, or
software bugs.
o Corrupted files can lead to loss of data integrity and make it difficult to retrieve or
manipulate data.
2. Performance Issues:
o Fragmentation: Over time, files may become fragmented, leading to slower access
times as the system struggles to read scattered pieces of data.
o Inefficient Indexing: Poorly designed indexing structures can lead to slow query
performance and increased I/O operations.
3. Concurrency Control:
o When multiple users access and modify data simultaneously, it can lead to conflicts and
inconsistencies.
o Without proper locking mechanisms, data may be overwritten or left in an inconsistent
state.
4. Limited Scalability:
o Traditional file systems may struggle to handle large volumes of data or high transaction
rates, leading to performance bottlenecks.
o Upgrading or scaling the file system can be complex and costly.
5. Backup and Recovery Challenges:
o Inadequate backup strategies may result in data loss in case of failure.
o Recovery processes can be complicated and time-consuming if the file system does not
support efficient snapshots or rollbacks.
6. Security Vulnerabilities:
o File systems may be susceptible to unauthorized access, leading to data breaches.
o Inadequate encryption and access control measures can expose sensitive data.
7. Compatibility Issues:
 o Different file formats or structures may create compatibility issues when integrating
with other systems or migrating to new platforms.
o Legacy systems may not support modern file formats, complicating data access and
management.
8. Resource Management:
o Inefficient use of storage resources can lead to wasted space, particularly if the file
system does not support dynamic resizing or compression.
o Poorly managed buffer pools can lead to increased disk I/O and reduced performance.

DATA MODEL:

A model is a simplified version of real-life, complex objects The entity-relationship(E-R)model is a

very popular modeling tool among many such tools available today.Many tools are available for data
modeling with E-R. All tools have some variations in representation of components.The E-R
model provides:

 An excellent communication tool.

 A simple graphical representation of data
Entity-Relationship Model

Entity-Relationship (ER) Model is based on the notion of real-world entities and relationships among them.
While formulating real-world scenario into the database model, the ER Model creates entity set, relationship
set, general attributes and constraints.

ER model based on’

 Entities and their attributes.

 Relationship among entities

The E-R modeluses E-Rdiagrams(ERD) for graphical representation of the database

components. An entity(or an entity set) is represented by a rectangle. The name of the
entity(set) is written with in the rectangle. Some tools prefer to use uppercase letters only for
entities. The name of an entity set is a singular noun .

RELATIONAL MODEL

The most popular data model in DBMS is the Relational Model. It is more scientific a
model than others. This model is based on first-order predicate logic and defines a
table as an n-ary relation
 The main highlights of this model

 Data is stored in tables called relations.

 Relations can be normalized.
 In normalized relations, values saved are atomic values.
 Each row in a relation contains a unique value.
 Each column in a relation contains values from a same domain.
The basic building blocks of all data models are entities, attributes, relationships, and
constraints.

 Anentity is a person, place, thing, or event about which data will be collected and stored.
An entity represents a particular type of object in the real world, which means that an
entity is “distinguishable” that is, each occurrence is unique and distinct.
 An entity may be a tangible object, i.e., one that you can touch such as a person or a
product. An entity may also be intangible, such as a flight route, or a rock concert (an
event).
 An attribute is a characteristic of an entity. For example, a customer entity would be
described by attributes such as last name, first name, phone numbers, address, etc.. As we
will see, it is also possible for relationships to have attributes.
 A relationship describes a bi-directional association among entities. For example, a
relationship exists between customers and sales agents that could be described as follows:
A sales agent can serve many customers, and each customer may be served by one sales
agent.
 Data models use three types of relationships: one-to-many, many-to-many, and one-to-
one. Database designers and most data modeling tools use the shorthand notations 1:M or
1..* M:N or *..*, and 1:1 or 1..1, respectively.

 , 1:M– a painter creates many different paintings, but each is painted by only one painter.
 M:N– an employee may learn many job skills, and each job skill may be learned by many
employees.

 1:1– Each department must have only one employee who is a manager and each
employee who is a m anager can manage only one dep

The fourth and final basic building block is a constraint, which is a restriction placed on the
data.

 Constraints are important because they help to ensure data integrity.

 Constraints are normally expressed in the form of rules. For example:–
Anemployee’s salary must have values between 45,000 and 200,000.–
Astudent’s GPA must be between 0.00 and 4.00.– Eachclass must have one
and only one teacher
 Constraints arise from business rules which are derived from a detailed
description of how an organization operates

Business Rules:
 A business rule is a brief, precise, and unambiguous description of a policy,
procedure, or principle within a specific organization.
  Properly written business rules are used to define entities, attributes, relationship,
and constraints.
 Any time you see relationship statements such as “an agent can server many
customers, and each customer can be served by only one agent,” business rules are
at work.
 To be effective, business rules must be easy to understand and widely disseminated
to ensure that every person in the organization shares a common interpretation of
the rules. Business rules describe, in simple language, the main and distinguishing
characteristics of the data as viewed by the company

Examples of business rules are as follows:

 Acustomer may generate many invoices

 Aninvoice is generated by only one customer
 Atraining session cannot be scheduled for fewer than 10 employees or for more than 30
employees.

• Note that the business rules establish entities, relationships, and constraints. For example, the first two
business rules above establish two entities (Customer and Invoice) and a 1:M relationship between them.
The third business rule above establishes a constraint (no fewer than 10 people and no more than 30
people, two entities (Employee and Training), and a relationship between Employee and Training

Discovering Business Rules

 The process of identifying and documenting business rules is essential to database design for
several reasons:
 Theyhelp to standardize the organization’s view of data.
 Theycan be a communication tool between users and designers
 .– They allow the designer to understand the nature, role, and scope of the data
 .– Theyallow the designer to understand business processes
 .– They allow the designer to develop appropriate relationship participation rules and constraints
and to create an accurate data model.

• Not all business rules can be modeled. For example, a business rule that specifies “no pilot can fly
more than 10 hours within any 24 hour period” cannot be modeled. However, such a business rule can
be enforced by application software.

Translating Business Rules Into Data Model Components

 Business rules set the stage for the proper identification of entities, attributes, relationships,
and constraints. In the real world, names are used to identify object. If the business environment
wants to keep track of the objects, there will be specific business rules for the objects.
 As a general rule, a noun in a business rule will translate to an entity in the model, and a verb
(active or passive) that associates the nouns will translate into a relationship among the entities.–
For example, the business rule “a customer may generate many invoices” contains two nouns
(customer and invoices) and a verb (generate) that associates the nouns. From this business rule,
you could deduce that
 Customer and invoice are objects of interest for the environment and should be
represented by their respective entities.
 • There is a “generate” relationship between customer and invoice.

Business Rules: Naming ConventionS
 It is also good practice to prefix the name of an attribute with the name (or an
abbreviation) of the entity in which it occurs. called– For example, in the CUSTOMER
entity, the customer’s credit limit might be CUSTOMER_CREDIT_LIMIT,
CUS_CREDIT_LIMIT
 The reason for this will become more apparent later on when you or perhaps learn about the
need to use common attributes to specify relationships between entities.
 The use of a proper naming convention will improve the data model’s ability to facilitate
communication among the designer, application programmer, and the end users.
 A proper naming convention can go a long way toward making your model self-documenting.
Evolution Of Data Model
Hierarchical Model

 The hierarchical model was developed in the 1960s to manage large amounts of data from
complex manufacturing projects, such as the Apollo rocket program (moon landing in 1969).
 The model’s basic logical structure is represented as a tree. The tree contains levels, or segments.
A segment is the equivalent of a file system’s record type. Within the hierarchy, a higher layer is
perceived as the parent of the segment directly beneath it, which is called the child.
 The hierarchical model depicts a set of 1:M relationships between a parent and its children
segments. Each parent can have many children, but each child has only one parent

Network Model

 The network model was created to represent complex data relationships more effectively than the
hierarchical model, to improve database performance, and to impose a database standard.
 In the network model, the user perceives the database as a collection of records in 1:M
relationships. Unlike, the hierarchical model, the network model allows a record to have more than
one parent.
 Although it is generally not used today, the network model defined many standards and concepts
that are still in use today, such as the terms schema and sub-schema and definitions for a data
manipulation language (DML) and data definition language (DDL)
 RELATIONAL MODEL

The relational model was introduced in 1970 by E.F. Codd (working for IBM at the time), in his landmark
paper “A Relational Model of Data for Large Shared Databanks” (Communications of the ACM, June
1970, pp.377-387).

 The relational model’s foundation is a mathematical concept known as a relation. To avoid the
complexity of abstract mathematical theory, think of a relation as a matrix composed of intersecting
rows and columns. Each row is called a tuple. Each column represents an attribute.
 The relational model also describes a precise set of data manipulation constructs based on advanced
mathematical concepts.
 The relational model is implemented through a very sophisticated relational database management
system (RDBMS). The RDBMS performs the same basic functions provided by the hierarchical and
network DBMS systems, in addition to a host of other functions that make the relational data model
easier to understand and implement.
 Arguably, the most important advantage of the RDBMS is its ability to hide the complexities of the
relational model from the user.
 The RDBMS manages all of the physical details, while the user sees the relational database as a
collection of tables in which the data are stored. The user can manipulate and query the data in a
way that seems intuitive and logical.

The end-user interface. Basically, the interface allows the end user to interact with the data (by
automatically generating SQL code). Each interface is a product of the software vendor’s ides of
meaningful interaction with the data
.– A collection of tables stored in the database. In a relational database, all data are perceived to be stored
in tables. The tables simply present the data to the end user in a way that is easy to understand. Each table is
independent. Rows in different tables are related by common values in common attributes.

– SQL engine. Largely hidden from the end user, the SQL engine executes all queries or data requests.

Object-Oriented (OO) Model

ncreasingly complex real-world problems demonstrated a need for a data model that more closely
represented the real world.

• In the object-oriented data model (OODM), both data and their relationships are contained in a single
structure known as an object. In turn, the OODM is the basis for the object-oriented database
management system (OODBMS).

• AnOODMreflects a very different way to define and use entities. Like the relational model’s entity, an
object is described by its factual content. But, quite unlike an entity, an object includes information
about relationships between the facts within the object, as well as information about its relationship
with other objects. Therefore, facts within the object are given greater meaning. The OODM is a
semantic data model.

Data Abstraction in DBMS

 database system contains intricate data structures and relations. The developers keep away the complex
data from the user and remove the complications so that the user can comfortably access data in the
database and can only access the data they want, which is done with the help of data abstraction.
 The main purpose of data abstraction is to hide irrelevant data and provide an abstract view of the data.
With the help of data abstraction, developers hide irrelevant data from the user and provide them the
relevant data. By doing this, users can access the data without any hassle, and the system will also work
efficiently.
 In DBMS, data abstraction is performed in layers which means there are levels of data abstraction in
DBMS.

Levels of abstraction for DBMS:

 Database systems include complex data-structures. In terms of retrieval of data, reduce complexity in terms
of usability of users and in order to make the system efficient, developers use levels of abstraction that hide
irrelevant details from the users. Levels of abstraction simplify database design.
 Mainly there are three levels of abstraction for DBMS, which are as follows –

 Physical or Internal Level

 Logical or Conceptual Level
 View or External Level

ONE MARK:

1.n E-R model, Y is the dominant entity and X is subordinate entity

A None of the above

B If Y is deleted, then X is also deleted

C If Y is deleted, then X is not deleted

D If X is deleted, then Y is also deleted

2. . What is the full form of DBMS?

a) Data of Binary Management System
b) Database Management System
c) Database Management Service
d) Data Backup Management System
View Answer
Answer: b

2. What is a database?
a) Organized collection of information that cannot be accessed, updated, and managed
b) Collection of data or information without organizing
c) Organized collection of data or information that can be accessed, updated, and managed
d) Organized collection of data that cannot be updated
View Answer
Answer: c

3. What is DBMS?
a) DBMS is a collection of queries
b) DBMS is a high-level language
c) DBMS is a programming language
d) DBMS stores, modifies and retrieves data
View Answer
Answer: d

4. Who created the first DBMS?

a) Edgar Frank Codd
b) Charles Bachman
c) Charles Babbage
d) Sharon B. Codd
View Answer
Answer: b
5. Which type of data can be stored in the database?
a) Image oriented data
b) Text, files containing data
c) Data in the form of audio or video
d) All of the above
View Answer

Answer: d
6. In which of the following formats data is stored in the database management system?
a) Image
b) Text
c) Table
d) Grap

Answer: c

8. Which of the following is not a type of database?

a) Hierarchical
b) Network
c) Distributed
d) Decentralized

7. Which of the following is not a type of database?

a) Hierarchical
b) Network
c) Distributed
d) Decentralized
View Answer
8. Which of the following is not an example of DBMS?
a) MySQL
b) Microsoft Acess
c) IBM DB2
d) Google
View Answer
Answer: d.

9. Which of the following is not a feature of DBMS?

a) Minimum Duplication and Redundancy of Data
b) High Level of Security
c) Single-user Access only
d) Support ACID Property
View Answer
Answer: c

10. Which of the following is a feature of the database?

a) No-backup for the data stored
b) User interface provided
c) Lack of Authentication
d) Store data in multiple locations
View Answer
Answer: b
1) Provides backup for the data stored by the user and the user can retrieve the data whenever required.
2) Provides User-interface to access the data.
3) Only authorized users can access the stored data.
4) Data is stored in one central location but multiple authorized users can access the data.

11. Which of the following is not a function of the database?

a) Managing stored data
b) Manipulating data
c) Security for stored data
d) Analysing code
View Answer
Answer: d

12. Which of the following is a function of the DBMS?

a) Storing data
b) Providing multi-users access control
c) Data Integrity
d) All of the above
View Answer
Answer: d

13. Which of the following is a component of the DBMS?

a) Data
b) Data Languages
c) Data Manager
d) All of the above
View Answer
14. Which of the following is known as a set of entities of the same type that share same properties, or
attributes?
a) Relation set
b) Tuples
c) Entity set
d) Entity Relation model
View Answer
Answer: c

15. What is information about data called?

a) Hyper data
b) Tera data
c) Meta data
d) Relations
View Answer
Answer: c
16. What does an RDBMS consist of?
a) Collection of Records
b) Collection of Keys
c) Collection of Tables
d) Collection of Fields
View Answer
Answer: c

17. The values appearing in given attributes of any tuple in the referencing relation must likewise occur in
specified attributes of at least one tuple in the referenced relation, according to _____________________
integrity constraint.
a) Referential
b) Primary
c) Referencing
d) Specific
View Answer
Answer: a.

18. _____________ is a hardware component that is most important for the operation of a database
management system.
a) Microphone
b) High speed, large capacity disk to store data
c) High-resolution video display
d) Printer
View Answer
Answer: b.

19. The DBMS acts as an interface between ________________ and ________________ of an enterprise-
class system.
a) Data and the DBMS
b) Application and SQL
c) Database application and the database
d) The user and the software
View Answer
Answer: c

20. The ability to query data, as well as insert, delete, and alter tuples, is offered by ____________
a) TCL (Transaction Control Language)
b) DCL (Data Control Language)
c) DDL (Data Definition Langauge)
d) DML (Data Manipulation Langauge)
View Answer
Answer: d

21. ______________ is a set of one or more attributes taken collectively to uniquely identify a record.
a) Primary Key
b) Foreign key
c) Super key
d) Candidate key
View Answer
Answer: c
Explanation: Foreign key creates a relationship between two relations. Super key is the superset of all the
keys in a relation. A candidate key is used to identify tuples in a relation.

22. Which command is used to remove a relation from an SQL?

a) Drop table
b) Delete
c) Purge
d) Remove
View Answer
Answer: a

23. Which of the following set should be associated with weak entity set for weak entity to be
meaningful?
a) Neighbour set
b) Strong entity set
c) Owner set
d) Identifying set
View Answer
Answer: d

24. Which of the following command is correct to delete the values in the relation teaches?
a) Delete from teaches;
b) Delete from teaches where Id =’Null’;
c) Remove table teaches;
d) Drop table teaches;
View Answer
Answer: a

25. Procedural language among the following is __________

a) Domain relational calculus
b) Tuple relational calculus
c) Relational algebra
d) Query language
View Answer
Answer: c

26. _________________ operations do not preserve non-matched tuples.

a) Left outer join
b) Inner join
c) Natural join
d) Right outer join
View Answer
27. Which forms have a relation that contains information about a single entity?
a) 4NF
b) 2NF
c) 5NF
d) 3NF
View Answer
Answer: d

28. The top level of the hierarchy consists of ______ each of which can contain _____.
a) Schemas, Catalogs
b) Schemas, Environment
c) Environment, Schemas
d) Catalogs, Schemas
View Answer
Answer: d

29. _______ indicates the maximum number of entities that can be involved in a relationship.
a) Greater Entity Count
b) Minimum cardinality
c) Maximum cardinality
d) ERD
View Answer
Answer: c

30. The user IDs can be added or removed using which of the following fixed roles?
a) db_sysadmin
b) db_accessadmin
c) db_securityadmin
d) db_setupadmin
View Answer
Answer: b

31. Why the following statement is erroneous?

SELECT dept_name, ID, avg (salary)

FROM instructor
GROUP BY dept_name;
a) Dept_id should not be used in group by clause
b) Group by clause is not valid in this query
c) Avg(salary) should not be selected
d) None
View Answer
Answer: a

32. The traditional storage of data organized by the customer, stored in separate folders in filing cabinets
is an example of ______________ type of ‘database’ management system.
a) Object-oriented database management system
b) Relational database management system
c) Network database management system
d) Hierarchical database management system
View Answer
Answer: d

33. After groups have been established, SQL applies predicates in the ___________ clause, allowing
aggregate functions to be used.
a) Where
b) Having
c) Group by
d) With
View Answer
Answer: b

34. Which of the following is not the utility of DBMS?

a) Backup
b) Data Loading
c) Process Organization
d) File organization
View Answer
Answer: c

35. What does a foreign key combined with a primary key create?
a) Network model between the tables that connect them
b) Parent-Child relationship between the tables that connects them
c) One to many relationship between the tables that connects them
d) All of the mentioned
View Answer
Answer: b

36. Which of the following is correct according to the technology deployed by DBMS?
a) Pointers are used to maintain transactional integrity and consistency
b) Cursors are used to maintain transactional integrity and consistency
c) Locks are used to maintain transactional integrity and consistency
d) Triggers are used to maintain transactional integrity and consistency
View Answer
Answer: c
Explanation: Pointers are used to access data with great speed and accuracy. Consistency is maintained
using locks.

37. Which of the following is correct regarding the file produced by a spreadsheet?
a) can be used as it is by the DBMS
b) stored on disk in an ASCII text format
c) all of the mentioned
d) none of the mentioned
View Answer
Answer: a

38. What is the function of the following command?

Delete from r where P;

a) Clears entries from relation

b) Deletes relation
c) Deletes particular tuple from relation
d) All of the mentioned
View Answer
Answer: c

39. ______ resembles Create view.

a) Create table . . . as
b) Create view as
c) Create table . . .like
d) With data
View Answer
Answer: a

40. The query specifying the SQL view is said to be updatable if it meets which of the following
conditions?
a) select clause contains relation attribute names but not have expressions, aggregates, or distinct
specification
b) from clause has 1 relation
c) query does not have group by or having clause
d) All of the mentioned
View Answer
Answer: d

41. When the “ROLLUP” operator for expression or columns within a “GROUP BY” clause is used?
a) Find the groups that make up the subtotal in a row
b) Create group-wise grand totals for the groups indicated in a GROUP BY clause
c) Group expressions or columns specified in a GROUP BY clause in one direction, from right to left, for
computing the subtotals
d) To produce a cross-tabular report for computing subtotals by grouping phrases or columns given within
a GROUP BY clause in all available directions
View Answer
Answer: c

42. Which of the following is the best way to represent the attributes in a large db?
a) Dot representation
b) Concatenation
c) Relational-and
d) All of the mentioned
View Answer
Answer: b

43. Which of the following is the subset of SQL commands used to manipulate Oracle Structures,
including tables?
a) Data Described Language
b) Data Retrieval Language
c) Data Manipulation Language
d) Data Definition Language
View Answer
Answer: d.

44. Which of the following functions construct histograms and use buckets for ranking?
a) Ntil()
b) Newtil()
c) Rank()
d) All of the mentioned
View Answer
Answer: a

45. __________ command is used in SQL to issue multiple CREATE TABLE, CREATE VIEW and
GRANT statements in a single transaction.
a) CREATE CLUSTER
b) CREATE PACKAGE
c) CREATE SCHEMA
d) All of the mentioned
View Answer
46. Which of the following key is required in to handle the data when the encryption is applied to the data
so that the unauthorised user cannot access the data?
a) Primary key
b) Authorised key
c) Encryption key
d) Decryption key
View Answer
Answer: c

47. Which of the following is known as the process of viewing cross-tab with a fixed value of one
attribute?
a) Dicing
b) Pivoting
c) Slicing
d) Both Pivoting and Dicing
View Answer
Answer: c

48. For designing a normal RDBMS which of the following normal form is considered adequate?
a) 4NF
b) 3NF
c) 2NF
d) 5NF
View Answer
Answer: b

49. Which of the following is popular for applications such as storage of log files in a database
management system since it offers the best write performance?
a) RAID level 0
b) RAID level 1
c) RAID level 2
d) RAID level 3
View Answer
Answer: b

50. Which of the following represents a query in the tuple relational calculus?
a) { }{P(t) | t }
b) {t | P(t)}
c) t | P() | t
d) All of the mentioned
View Answer
Answer: b

51. The oldest DB model is _______________

a) Network
b) Physical
c) Hierarchical
d) Relational
View Answer
Answer: c

5MARK:

1.explain the DBMS?

2. what is data & information?

3.Describe the basic problem with file system?

4.explain the evoulation of data model?

5.describe the file system?

10 MARK:

1.Briefly explain the database concept?

2.differance between data & information?

3.what is file system? Explain problem with file system?

4.brifly explain th e basic building blocks?

5.describe the degree of the data abstraction?

UNIT I COMPLETED
 UNIT II
Design Concepts: Relational database model - logical view of data-keys -Integrity rules -
relational set operators - data dictionary and the system catalog - relationships -data
redundancy revisited -indexes - codd's rules. Entity relationship model - ER diagram

DESIGN CONCEPT:

 Database Design is a collection of processes that facilitate the designing, development,

implementation and maintenance of enterprise data management systems. Properly designed
database are easy to maintain, improves data consistency and are cost effective in terms of disk
storage space. The database designer decides how the data elements correlate and what data must
be stored.

 The main objectives of database design in DBMS are to produce logical and physical designs
models of the proposed database system.

 The logical model concentrates on the data requirements and the data to be stored independent of
physical considerations. It does not concern itself with how the data will be stored or where it will
be stored physically.

 The physical data design model involves translating the logical DB design of the database onto
physical media using hardware resources and software systems such as database management
systems (DBMS).

Why Database Design is Important ?

It helps produce database systems

1. That meet the requirements of the users

2. Have high performance.

Database design process in DBMS is crucial for high performance database system. the genius of a
database is in its design. Data operations using SQL is relatively simple

 The database development life cycle has a number of stages that are followed when developing
database systems.
  The steps in the development life cycle do not necessarily have to be followed religiously in a
sequential manner.

 On small database systems, the process of database design is usually very simple and does not
involve a lot of steps.

 In order to fully appreciate the above diagram, let’s look at the individual components listed in
each step for overview of design process in DBMS.

Requirements analysis
 Planning – This stages of database design concepts are concerned with planning of entire
Database Development Life Cycle. It takes into consideration the Information Systems strategy
of the organization.
 System definition – This stage defines the scope and boundaries of the proposed database
system.

Database designing
 Logical model – This stage is concerned with developing a database model based on
requirements. The entire design is on paper without any physical implementations or specific
DBMS considerations.
 Physical model – This stage implements the logical model of the database taking into account
the DBMS and physical implementation factors.

Implementation
 Data conversion and loading – this stage of relational databases design is concerned with
importing and converting data from the old system into the new database.
 Testing – this stage is concerned with the identification of errors in the newly implemented
system. It checks the database against requirement specifications.

Two Types of Database Techniques

1. Normalization
2. ER Modeling

Relational Model?

 Relational Model (RM) represents the database as a collection of relations. A relation is

nothing but a table of values. Every row in the table represents a collection of related data
values. These rows in the table denote a real-world entity or relationship.

 The table name and column names are helpful to interpret the meaning of values in each
row. The data are represented as a set of relations. In the relational model, data are stored
as tables. However, the physical storage of the data is independent of the way the data are
logically organized.
 Relational Model Concepts in DBMS

1. Attribute: Each column in a Table. Attributes are the properties which define a relation. e.g.,
Student_Rollno, NAME,etc.
2. Tables – In the Relational model the, relations are saved in the table format. It is stored along
with its entities. A table has two properties rows and columns. Rows represent records and
columns represent attributes.
3. Tuple – It is nothing but a single row of a table, which contains a single record.
4. Relation Schema: A relation schema represents the name of the relation with its attributes.
5. Degree: The total number of attributes which in the relation is called the degree of the relation.
6. Cardinality: Total number of rows present in the Table.
7. Column: The column represents the set of values for a specific attribute.
8. Relation instance – Relation instance is a finite set of tuples in the RDBMS system. Relation
instances never have duplicate tuples.
9. Relation key – Every row has one, two or multiple attributes, which is called relation key.
10. Attribute domain – Every attribute has some pre-defined value and scope which is known as
attribute domain

Relational Integrity Constraints

Relational Integrity constraints in DBMS are referred to conditions which must be present for a
valid relation. These Relational constraints in DBMS are derived from the rules in the mini-
world that the database represents.

There are many types of Integrity Constraints in DBMS. Constraints on the Relational database
management system is mostly divided into three main categories are:

1. Domain Constraints
2. Key Constraints
3. Referential Integrity Constraints
Domain Constraints
 Domain constraints can be violated if an attribute value is not appearing in the
corresponding domain or it is not of the appropriate data type.

 Domain constraints specify that within each tuple, and the value of each attribute must be
unique. This is specified as data types which include standard data types integers, real
numbers, characters, Booleans, variable length strings, etc.

Example:

Create DOMAIN CustomerName

CHECK (value not NULL)

The example shown demonstrates creating a domain constraint such that CustomerName is not
NULL

View of data in DBMS

Abstraction is one of the main features of database systems. Hiding irrelevant details from user
and providing abstract view of data to users, helps in easy and efficient user-
database interaction. In the previous tutorial, we discussed the three level of DBMS architecture,
The top level of that architecture is “view level”. The view level provides the “view of data” to
the users and hides the irrelevant details such as data relationship, database schema, constraints,
security etc from the user.

To fully understand the view of data, you must have a basic knowledge of data abstraction and
instance & schema. Refer these two tutorials to learn them in detail.

1. Data abstraction:Database systems are made-up of complex data structures. To ease the user
interaction with database, the developers hide internal irrelevant details from users. This process of
hiding irrelevant details from user is called data abstraction.
2. Instance and schema: Design of a database is called the schema. Schema is of three types: Physical
schema, logical schema and view schema. The data stored in database at a particular moment of
time is called instance of database. Database schema defines the variable declarations in tables that
belong to a particular database; the value of these variables at a moment of time is called the
instance of that database.

Data Abstraction in DBMS

Database systems are made-up of complex data structures. To ease the user interaction with
database, the developers hide internal irrelevant details from users. This process of hiding
irrelevant details from user is called data abstraction. The term “irrelevant” used here with
respect to the user, it doesn’t mean that the hidden data is not relevant with regard to the whole
database. It just means that the user is not concerned about that data.

For example: When you are booking a train ticket, you are not concerned how data is
processing at the back end when you click “book ticket”, what processes are happening when
you are doing online payments. You are just concerned about the message that pops up when
your ticket is successfully booked. This doesn’t mean that the process happening at the back
end is not relevant, it just means that you as a user are not concerned what is happening in the
database.

Three levels of abstraction

Physical level: This is the lowest level of data abstraction. It describes how data is actually
stored in database. You can get the complex data structure details at this level.

Logical level: This is the middle level of 3-level data abstraction architecture. It describes what
data is stored in database.

View level: Highest level of data abstraction. This level describes the user interaction with
database system.

Example: Let’s say we are storing customer information in a customer table. At physical
level these records can be described as blocks of storage (bytes, gigabytes, terabytes etc.) in
memory. These details are often hidden from the programmers.

At the logical level these records can be described as fields and attributes along with their data
types, their relationship among each other can be logically implemented. The programmers
generally work at this level because they are aware of such things about database systems.
At view level, user just interact with system with the help of GUI and enter the details at the
screen, they are not aware of how the data is stored and what data is stored; such details are
hidden from them.

stance and schema in DBMS

DBMS Schema
Definition of schema: Design of a database is called the schema. For example:
An employee table in database exists with the following attributes:

EMP_NAME EMP_ID EMP_ADDRESS EMP_CONTACT

-------- ------ ----------- -----------
This is the schema of the employee table. Schema defines the attributes of tables in the
database. Schema is of three types: Physical schema, logical schema and view schema.

 Schema represents the logical view of the database. It helps you understand what data needs to go
where.
 Schema can be represented by a diagram as shown below.
 Schema helps the database users to understand the relationship between data. This helps in
efficiently performing operations on database such as insert, update, delete, search etc.

In the following diagram, we have a schema that shows the relationship between three tables:
Course, Student and Section. The diagram only shows the design of the database, it doesn’t
show the data present in those tables. Schema is only a structural view(design) of a database as
shown in the diagram below.
 The design of a database at physical level is called physical schema, how the data stored in
blocks of storage is described at this level.

Design of database at logical level is called logical schema, programmers and database
administrators work at this level, at this level data can be described as certain types of data
records gets stored in data structures, however the internal details such as implementation of data
structure is hidden at this level (available at physical level).

Design of database at view level is called view schema. This generally describes end user
interaction with database systems.

To learn more about these schemas, refer 3 level data abstraction architecture.

DBMS Instance
Definition of instance: The data stored in database at a particular moment of time is called
instance of database. Database schema defines the attributes in tables that belong to a particular
database. The value of these attributes at a moment of time is called the instance of that database.

For example, we have seen the schema of table “employee” above. Let’s see the table with the
data now. At this moment the table contains two rows (records). This is the the current instance
of the table “employee” because this is the data that is stored in this table at this particular
moment of time.

EMP_NAME EMP_ID EMP_ADDRESS EMP_CONTACT

------- ------ ----------- -----------
Chaitanya 101 Noida 95********
Ajeet 102 Delhi 99********
Let’s take another example: Let’s say we have a single table student in the database, today the
table has 100 records, so today the instance of the database has 100 records. We are going to add
another 100 records in this table by tomorrow so the instance of database tomorrow will have
200 records in table. In short, at a particular moment the data stored in database is called the
instance, this changes over time as and when we add, delete or update data in the database.

Integrity Rules in DBMS


In DBMS systems, integrity rules, which take a prime place, are designed to ensure that the
quality of data is always high, with no inconsistencies or errors. The set of principles, also
known as the integrity rules or constraints, helps to manage the data stored in the system in the
right way and determine whether it’s suitable for certain purposes. It is not without reason that
these rules are so crucial since they are key to the overall integrity of the database and its
quality. Herein, we investigate the centrality of governance regulations, illustrate the different
types that exist, and provide their implementation in DBMS.
What are Integrity Rules?
Integrity rules normally represent the pre-existing conditions, or constraints, of data stored in
the database that will ensure the data is valid and consistent. These rules, defining permissible
values, relationships, and data operations enclosed in the database system, ensure the accuracy
and reliability of the data to be used for its purposes. Rules for integrity are considered barriers
towards the possibility of some operations over the database, such as deletion, modification, or
insertion of data whose accuracy is doubtful or inconsistent, thus enabling the database to be of
high quality.
Types of Integrity Rules
 Entity Integrity: Also called business key integrity, this rule proclaims that each row in a
table must contain some unique data, which is known as the business key. Through this
verification, a primary key will be ensured of non-duplication, which also implies that no
null values are allowed in the primary key column, thus uniquely identifying each instance
of an entity in the table.
 Referential Integrity: Referential integrity guarantees the consistent pass of key reference
constraints between data through main table and foreign keys. This rule means that for
every foreign key value in a child table, that value has to possess the same primary key
value in the parent table. This is the only way to maintain foreign keys and make father
child relationships work properly.
 Domain Integrity: Verifying domain validity means finding out if the value lies within the
allowed range for a particular attribute's column in the data table. It implements data
integrity by specifying data formats, ranges, and constraints, for example,
check constraints and limitations, that ensure that all officially recognized data are kept.
 User-defined Integrity: This is an enterprise-specific set of integrity rules that would be
established by different organizations depending on their business edges and the rules
ascribed to sensitive data. These rules can be the user-defined ones like business rules or
validation criteria or can even be the custom constraints that are different from organization
to organization.
Implementation of Integrity Rules
Integrity rules are typically implemented through a combination of mechanisms within
the DBMS, including:
 Constraints: Constraints including a primary key constraint, a foreign key constraint, a
unique constraint, and a check constraint are to maintain the integrity rules at the schema
level.
 Triggers: Triggers are database elements that are activated on the fly to conduct given
activities (executed on insert, update, or delete operations, for example) on specific
database tables. Invoking triggers provides leverage to impose tougher restrictions, thus
enabling the enforcement of more advanced integrity rules or conducting validation
operations, which are a more complex kind of comparison beyond checks of constraints.
 Stored Procedures: Stored procedures are precompiled sets of SQL operations that specify
business logic and data processing operations, and they are stored in a database. They may
need to be applied to the forces of rule-following and data consistency tasks during the
events of transactions.
Significance of Integrity Rules
The rules of integrity, including the truth, credibility, and consistency, is one of the key
elements in ensuring proper functioning of the data in database. Their significance extends to
various aspects of database management, including:Their significance extends to various
aspects of database management, including:
 Data Quality: Integrity rules make sure data is precisely moderated, elaborated, and of
high- quality in the database, just like the data quality and reliability.
 Data Security: Integrity rules classify together while limiting and validating requests. They
are effective in defending against unauthorized access, data breaches, and data
manipulation attacks.
 Data Consistency: Ethical parameters help create a homogeneous database, preventing
data inconsistencies, anomalies, and errors that could arise from invalid and incorrect data
keystrokes, hence allowing for data stemming from valid data sources that are consistently
coherent and reliable both qualitatively and quantitatively.
  Compliance: Extent rules make sure all regulations, industry standards, and company
policies governing data security, privacy, and management are complied with.

In Conclusion, integrity regulations constitute the fundamental codes of the DBMS that assure
proper, correct, and readable data. Through the specification and enforcement of the normal
definition as well as relationships and actions, the integrity rules preserve the data integrity and
the trust in the database systems. The correct place of integrity rules in the data industry is to
take further steps in ensuring that data is of good quality, secure, and in compliance with
regulations. With the increasing demand to employ data as an imperative strategic resource,
sticking to the rules that are related to information safety becomes a must for ensuring the
trustworthiness and dependability of database schemes.

Set Theory Operations in Relational Algebra


Relational Algebra in DBMS These Set Theory operations are the standard mathematical
operations on set. These operations are Binary operations that are, operated on 2 relations
unlike PROJECT, SELECT and RENAME operations. These operations are used to merge 2
sets in various ways.
The set operation is mainly categorized into the following:
1. Union operation
2. Intersection operation
3. Set difference or Minus operation
Before we apply one of the 3 set operations on relations, the two relations on which we are
performing the operations must have same type of tuples. This is also known as be Union
compatibility (or Type compatibility).
Type compatibility: Two relations A(P1, P2, ..., Pn) and B(Q1, Q2, ..., Qn) are said to be
Type compatible (or Union compatible) if both the relation have the same degree 'k' and
domain(Pi) = domain(Qi) for 1<= i <= k.
1. UNION Operation: Notation:
A∪S
where, A and S are the relations, symbol ‘∪’ is used to denote the Union operator. The result
of Union operation, which is denoted by A ∪ S, is a relation that basically includes all the
tuples that are present in A or in S, or in both, eliminating the duplicate tuples.

Important points on UNION Operation:

1. The UNION operation is commutative, that is :
A∪B=B∪A
2. The UNION is associative, that means it is applicable to any number of relation.
A∪(B∪C)=(A∪B)∪C
3. In SQL, the operation UNION is as same as UNION operation here.
4. Moreover, In SQL there is multiset operation UNION ALL.
2. INTERSECTION Operation:
Notations:
A∩S
where, A and S are the relations,
symbol ‘∩’ is used to denote the Intersection operator.
The result of Intersection operation, which is denoted by A ∩ S, is a relation that basically
includes all the tuples that are present in both A an S.
Important points on INTERSECTION Operation:
1. The INTERSECTION operation is commutative, that is :
A∩B= B∩A
2. The INTERSECTION is associative, that means it is applicable to any number of relation.
A∩(B∩C)=( A∩B)∩C
3. INTERSECTION can be formed using UNION and MINUS as follows:
A ∩ B = ((A ∪ B) - (A - B)) - (B - A)
4. In SQL, the operation INTERSECT is as same as INTERSECTION operation here.
5. Moreover, In SQL there is multiset operation INTERSECT ALL.
3. MINUS (or SET DIFFERENCE) Operation:
Notations:
A-S
where, A and S are the relations,
symbol ‘ - ’ is used to denote the Minus operator.
The result of Intersection operation, which is denoted by A - S, is a relation that basically
includes all the tuples that are present in A but not in S.
Important points on MINUS (or SET DIFFERENCE) Operation:
1. The SET DIFFERENCE operation is not commutative, that means :
A - B != B - A
2. In SQL, the operation EXCEPT is as same as MINUS operation here.
3. Moreover, In SQL there is multiset operation EXCEPT ALL.

Set Operators

Intersection (∩) Operator

Suppose R and S are two relations. The Set Intersection operation selects all the tuples that are in both relations
R and S.

For a Set Intersection to be valid, the following conditions must hold:

1. Both relations R and S have the same number of attributes.

 2. Corresponding attributes (columns) have the same domain (type).

3. The attributes of R and S must occur in the same order.

⇒ Symbol: ∩

⇒ Syntax: R ∩ S

⇒ Relational Algebra: R ∩ S

This version ensures the conditions for compatibility are clear and corrects any grammatical or conceptual
errors.

Difference ( - ) Operator
Suppose R and S are two relations. The Set Difference operation selects all the tuples that are present in the
first relation R but not in the second relation S.

For a Set Difference to be valid, the following conditions must hold:

1. Both relations R and S have the same number of attributes.

2. Corresponding attributes (columns) have the same domain (type).

3. The attributes of R and S must occur in the same order.

⇒ Symbol: -

⇒ Syntax: R - S

⇒ Relational Algebra: R – S

What is Data Dictionary?


In a database management system (DBMS), a data dictionary can be defined as a component
that stores a collection of names, definitions, and attributes for data elements used in the
database. The database stores metadata, that is, information about the database. These data
elements are then used as part of a database, research project, or information system.

It stores all information about relationships or tables, from the schema and constraints used.
All metadata is preserved. In general, metadata refers to information about data. Thus,
storing the connection scheme and other metadata in a single structure called a data
dictionary or system directory. A data dictionary is like an A-Z dictionary of a relational
database system that stores all the information about every relationship in the database.
What is a Data Dictionary?

The data dictionary consists of two words, data, which represents data collected from several
sources, and dictionary, which represents where this data is available. The data dictionary
is an important part of the relational database because it provides additional
information about the relationship between several tables in the database. A data dictionary
in a DBMS helps users manage data in an orderly and orderly manner, thereby
preventing data redundancy.
Below is a data dictionary that describes the table that contains employee details.
Field Size for
Field Name Data Type Description Example
Display

Unique ID of each
EmployeeID Integer 8 100025
employee

Emily
FullName Text 30 Full name of the employee
Johnson

DOB Date/Time 10 Date of birth of employee 1990-05-15

PhoneNumber Integer 10 Phone number of employee 555-123-4567

Some of the advantages of using a data dictionary are:
 In DBMS, the data model provides very little information about the database, so the data
dictionary is very important to get the right knowledge about the entities, relationships,
and attributes that exist in the data model.
 The data dictionary provides consistency by reducing data redundancy in data collection
and use among different team members.
 Data dictionaries provide structured analysis and design tools by implementing data
standards. Data standards are sets of rules that govern the collection, recording, and
presentation of data.
 Using a data dictionary helps define the naming convention used in the model.
Types of Data Dictionary in DBMS

Types of data dictionary

There are basically two types of data dictionaries in a database management system:
 Integrated Data Dictionary
 Stand Alone Data Dictionary
Integrated Data Dictionary
Every relational database has an Integrated Data Dictionary available in the DBMS. This
integrated data dictionary acts as a system directory that is accessed and updated by the
relational database. The old database does not have an integrated data dictionary, so the
database administrator must use the Stand Alone Data Dictionary. An Integrated Data
Dictionary in a DBMS can link metadata.
The integrated data dictionary can be further divided into two types:
Active: When any changes are made to the database, the active data dictionary is
automatically updated by the DBMS. It is also known as a self-updating dictionary because it
continuously updates its data.
Passive: Unlike active dictionaries, passive dictionaries must be updated manually when
there are changes in the database. This type of data dictionary is difficult to manage because
it requires proper functionality. Else, the database and data dictionary will be synchronized.
Stand Alone Data Dictionary
This type of database in the DBMS is very adaptive because it grants the administrator in
charge of the confidential information complete autonomy to define and manage all crucial
data. Whether the information is printed or not has nothing to do with it. A data dictionary
that has a stand-alone format enables database designers to have the flexibility to
communicate with end users regardless of their data dictionaries format.
There is no standard format for data dictionaries. Here are some common elements:
 Data Elements: The data dictionary describes each data element by specifying the names,
data types, storage formats and validation rules.
 Table: All information about the table, such as the user who created the table, the number
of rows and columns, the date the table was created and entered, etc.
 Indexes: Indexes for database tables are stored in the data dictionary. The DBMS stores
the index name used and index attributes, locations, and properties, as well as the creation
date, in each index.
 Programs: Applications defined for database access, reports, application formats and
screens, SQL queries, etc. also stored in the data dictionary.
 Relationships between data elements: A data dictionary stores relationship types; for
example, if it is mandatory or optional, the nature of the relationship and connection, etc.
 Administration and End Users: The data dictionary stores all administrative and end user
data.
Metadata in a DBMS, stored in a data dictionary, is like a monitor that controls database
usage and whether users are allowed to access the database.
How to Create a Data Dictionary?
As mentioned above, most businesses rely on a database management system that has an
integrated data dictionary because it is automatically updated and easy to maintain.
Documentation for databases including MySQL, SQL Server, Oracle, etc. This can be done
in various relational databases such as
Database administrators can use templates in SQL Server, Oracle, or Microsoft Excel to
create a stand-alone data dictionary

The various notations used to create a data dictionary are:

Data Construct Notation Stands For

Composition = is composed of

Sequence + AND

Selection [|] OR

Repetition { }^n n repetitions

Parentheses () to represent optional data

Employee Table:
Column Name Data Type Description

EmployeeID INT Unique identifier for each employee

FirstName VARCHAR First name of the employee

LastName VARCHAR Last name of the employee

DepartmentID INT Foreign key referencing the `Department` table

Salary DECIMAL Numeric value representing the employee's salary

 Disadvantages of Data Dictionary

 Data dictionaries have little functional detail.

 Non-technical users may experience difficulties when using the data dictionary for the
first time.
 Data dictionary relational diagrams are often not visible.

 The Data Dictionary in the DBMS provides additional information about the
relationship between several database tables, helps organize data, and prevents data
redundancy in the DBMS.
 A database dictionary is a set of files that contain database metadata. Therefore, it is
also known as metadata repository.
 The data dictionary in the database management system is wide and consists of two
types:
o Integrated Data Dictionary
o Stand Alone Data Dictionary
 This article discusses the topic of Data Dictionary in DBMS in detail. We have seen
definitions, examples, types, needs, advantages of Data Dictionary in DBMS.
 We hope this blog has helped you to improve your knowledge about Data Dictionary in
DBMS.
What is Data Catalog?

As enterprise data volumes explode into petabyte scale, getting a handle on burgeoning and
ever-changing data assets becomes imperative. But siloed, scattered data lakes provide little
strategic value. This is where a meticulous data catalogue comes in - to bring order to the
data deluge. When executed effectively, data cataloguing delivers a unified view of the
organization's information landscape that powers productivity and innovation.
In this article, We will explore Data Cataloging, its features, Benefits, Implementation, and
Role of Data Cataloging.

Defining Data Catalogs and Analogy

Data Catalogs is a centralized metadata repository that serves as an inventory of available
data across the enterprise. For each identified dataset or data object, the catalogue collates
comprehensive technical, administrative, and business metadata. Technical metadata
includes structural schemas, data types, size, source databases, and more. Administrative
metadata covers attributes like owners, access rules, and SLAs. Business metadata provides
contextual annotations that convey meaning, relationships, calculations, processes, and
origin stories.
Advanced data catalogues auto-generate much of this metadata through capabilities like
automatic crawling of databases and object storage. Additional enrichment by data
stewards, SMEs, and business users curates glossaries, taxonomies, data definitions, and
application information.
What is MetaData?
Metadata is often described as "data about data". It provides various information
regarding documentation of other data that are managed within an application or
environment. It offers context and various additional information about a data item. It
provides information to help and mange a resource, like when and how it was created, file
type and other technical information. Metadat helps organizations and locating data, such
as through a library catalog. In databases, metadata describes the structure of database
objects such as tables, columns, keys and indexes.
Capabilities and Features of Data Catalogs
Mature data catalogs deliver powerful capabilities including:
1. Searchable central directory for easily locating data assets organization-wide: This
improves discoverability and reduces duplicative datasourcing efforts.
2. Data lineage documentation that traces datasets from origin through various
transformations over time: This supports auditability and data governance.
3. Automated schema inference, classification, and catalog updating when new data
sources are added or changed: This provides dynamic updating with little manual effort
as data environments evolve.
4. Data profiling and statistics for accurately assessing data quality, distribution, and
semantics: This enables trust in data.
5. Business glossary and taxonomy creation to define standard business concepts and their
relationships: This provides consistency and understanding for end users.
6. Annotation and tagging features for domain experts to elucidate definitions and
contextual details: This makes data more findable and useful.
7. Collaboration features like discussions and workspaces for commentary and knowledge
sharing on data: This facilitates collective data understanding.
8. Role-based access controls, security policies, and usage auditing to manage permissions
and compliance: This ensures proper data security and privacy.
9. API integrations with data visualization, analytics, and other applications to leverage
catalog metadata: This promotes widespread metadata utilization.
Benefits of Data Cataloging
Effective data cataloging confers multiple benefits:
1. Accelerated Data Discovery: With relevant documentation and context, users can
rapidly pinpoint the most applicable data for their needs. This self-service access
reduces reliance on technical specialists. Data search moves from literal string matching
to conceptual relevance matching.
2. Increased Trust in Data: By providing transparency on data lineage, governance
policies, access levels and expert perspectives, catalogs build user confidence in data
reliability. Documentation acts as a type of data audit trail.
3. Enabled Data Reuse: With enhanced visibility into available data, redundant efforts to
create duplicate data can be avoided. Cross-team data sharing and accessibility of
authoritative enterprise data is improved. This data democratization fuels enhanced
productivity.
4. Compliance and Governance: Data lineage tracking provides auditability to
demonstrate compliance with regulations. Access controls and policies can be
centralized and managed holistically vs individually on databases. This strengthens data
governance across systems.
5. Agility and Innovation: With data cataloging, organizations can more quickly
integrate, analyze, and extract value from burgeoning data. New opportunities and use
cases can be identified through improved enterprise data knowledge. Data becomes an
asset driving competitive advantage.
Implementing Data Catalogs
Leading data catalog solutions include Informatica, Collibra, Alation, AWS Glue,
Microsoft Azure Purview, Google Cloud Data Catalog, Waterline Data, and open source
options like Apache Atlas. Capabilities vary, but key considerations for implementation
include:
1. dentifying Catalog Scope - What data domains, use cases, and stakeholders will the
catalog support? Balance completeness with achievability.
2. Profiling Technical Environment - Inventory critical systems and map out data
flows/lineage to provide necessary starting metadata.
3. Stakeholder Engagement - Get agreement on catalog purpose, policies, funding, and
participation from affected teams. Address concerns.
4. Automated Crawling and Classification - Leverage auto-indexing of sources to
jumpstart technical metadata collection.
5. Building Business Glossaries and Taxonomies - Standardize language and
relationships around data concepts.
6. Manual Enrichment - Capture tribal knowledge via annotations from domain experts,
data owners, and business users.
7. Integrations with Existing Tools - Incorporate catalog within current workflows e.g.
data ingestion, analytics.
8. Training and Change Management - Educate end users on catalog capabilities and get
their buy-in on participation.
9. Continuous Improvement - Treat the catalog as a dynamic asset requiring ongoing
governance, enhancement, and user engagement.
With these building blocks, organizations can implement successful data catalogs that meet
their specific environment needs and scale up adoption.
Techniques for High-Impact Data Catalogs
Beyond baseline implementation, there are several advanced techniques that information
architects and data governance leaders leverage to maximize data catalog value:
 Curating multiple layers of business metadata - This includes definitions, tagging,
examples, calculations, relationships, and context.
 Embedding data stewardship responsibilities into business roles - This drives better
accuracy and buy-in.
 Automating metadata capture from data processing pipelines - This reduces manual
effort as updates occur.
 Monitoring usage metrics - This identifies enhancement opportunities around
findability, access, and utility.
 Developing a metadata quality scorecard - This benchmarks and maintains metadata
completeness, consistency and accuracy.
 Cataloging model metadata from ML experiments - This provides lineage and
insight into models.
 Identifying new cross-functional data insights - This helps realize synergistic value
from merging perspectives.
 Promoting collaborative crowdsourced metadata curation - This harnesses
collective intelligence.
 Integrating with data preparation tools - This bridges raw data to other
environments.
With these advanced practices, data catalogs evolve into evergreen and trusted enterprise
assets.
The Critical Role of Cataloging in DataOps
As DataOps methodologies gain adoption, data cataloging is proving integral. By providing
visibility into data flows, predecessors, access rights, and business meaning, catalogs
enable smooth coordination between data producers, custodians, and consumers. Data can
be efficiently utilized for diverse analytics use cases. Catalog metadata improves developer
productivity. And cataloging facilitates important DataOps cultural shifts like
communication, transparency, and democratization. Essentially, cataloging provides the
map to navigate the data landscape.
The data cataloging journey requires significant strategic investment and a multi-year
timeline to mature. But through robust metadata management, data can be transformed
 from isolated, opaque pools into interconnected, transparent information streams. The
promise of data is realized through cataloging. In the data-driven future, catalogs will serve
as the GPS for organizational data voyages.

The Problem of Redundancy in Database

Last Updated : 16 Nov, 2023


Redundancy means having multiple copies of the same data in the database. This problem arises
when a database is not normalized. Suppose a table of student details attributes is: student ID,
student name, college name, college rank, and course opted.
Name
Student_ID Contact College Course Rank

100 Himanshu 7300934851 GEU B.Tech 1

101 Ankit 7900734858 GEU B.Tech 1

102 Ayush 7300936759 GEU B.Tech 1

103 Ravi 7300901556 GEU B.Tech 1

It can be observed that values of attribute college name, college rank, and course are being
repeated which can lead to problems. Problems caused due to redundancy are:
 Insertion anomaly
 Deletion anomaly
 Updation anomaly
Insertion Anomaly
If a student detail has to be inserted whose course is not being decided yet then insertion will not
be possible till the time course is decided for the student.
Student_ID
Name Contact College Course Rank

100 Himanshu 7300934851 GEU 1

This problem happens when the insertion of a data record is not possible without adding some
additional unrelated data to the record.
Deletion Anomaly
If the details of students in this table are deleted then the details of the college will also get
deleted which should not occur by common sense. This anomaly happens when the deletion of a
data record results in losing some unrelated information that was stored as part of the record that
was deleted from a table.
It is not possible to delete some information without losing some other information in the table as
well.
Updation Anomaly
Suppose the rank of the college changes then changes will have to be all over the database which
will be time-consuming and computationally costly.
Student_ID Name Contact College Course Rank

100 Himanshu 7300934851 GEU B.Tech 1

101 Ankit 7900734858 GEU B.Tech 1

102 Ayush 7300936759 GEU B.Tech 1

103 Ravi 7300901556 GEU B.Tech 1

All places should be updated, If updation does not occur at all places then the database will be in
an inconsistent state.
Redundancy in a database occurs when the same data is stored in multiple places. Redundancy
can cause various problems such as data inconsistencies, higher storage requirements, and slower
data retrieval.
Problems Caused Due to Redundancy
 Data Inconsistency: Redundancy can lead to data inconsistencies, where the same data is
stored in multiple locations, and changes to one copy of the data are not reflected in the other
copies. This can result in incorrect data being used in decision-making processes and can lead
to errors and inconsistencies in the data.
 Storage Requirements: Redundancy increases the storage requirements of a database. If the
same data is stored in multiple places, more storage space is required to store the data. This
can lead to higher costs and slower data retrieval.
 Update Anomalies: Redundancy can lead to update anomalies, where changes made to one
copy of the data are not reflected in the other copies. This can result in incorrect data being
used in decision-making processes and can lead to errors and inconsistencies in the data.
 Performance Issues: Redundancy can also lead to performance issues, as the database must
spend more time updating multiple copies of the same data. This can lead to slower data
retrieval and slower overall performance of the database.
 Security Issues: Redundancy can also create security issues, as multiple copies of the same
data can be accessed and manipulated by unauthorized users. This can lead to data breaches
and compromise the confidentiality, integrity, and availability of the data.
 Maintenance Complexity: Redundancy can increase the complexity of database
maintenance, as multiple copies of the same data must be updated and synchronized. This can
make it more difficult to troubleshoot and resolve issues and can require more time and
resources to maintain the database.
 Data Duplication: Redundancy can lead to data duplication, where the same data is stored in
multiple locations, resulting in wasted storage space and increased maintenance complexity.
This can also lead to confusion and errors, as different copies of the data may have different
values or be out of sync.
 Data Integrity: Redundancy can also compromise data integrity, as changes made to one
copy of the data may not be reflected in the other copies. This can result in inconsistencies
and errors and can make it difficult to ensure that the data is accurate and up-to-date.
  Usability Issues: Redundancy can also create usability issues, as users may have difficulty
accessing the correct version of the data or may be confused by inconsistencies and errors.
This can lead to frustration and decreased productivity, as users spend more time searching
for the correct data or correcting errors.
To prevent redundancy in a database, normalization techniques can be used. Normalization is the
process of organizing data in a database to eliminate redundancy and improve data
integrity. Normalization involves breaking down a larger table into smaller tables and
establishing relationships between them. This reduces redundancy and makes the database more
efficient and reliable.
Advantages of Redundant Data
 Enhanced Query Performance: By eliminating the need for intricate joins, redundancy
helps expedite data retrieval.
 Offline Access: In offline circumstances, redundant copies allow data access even in the
absence of continuous connectivity.
 Increased Availability: Redundancy helps to increase fault tolerance, which makes data
accessible even in the event of server failures.
Disadvantages of Redundant Data
 Increased storage requirements: Redundant data takes up additional storage space within
the database, which can increase costs and slow down performance.
 Inconsistency: If the same data is stored in multiple places within the database, there is a risk
that updates or changes made to one copy of the data may not be reflected in other copies,
leading to inconsistency and potentially incorrect results.
 Difficulty in maintenance: With redundant data, it becomes more difficult to maintain the
accuracy and consistency of the data. It requires more effort and resources to ensure that all
copies of the data are updated correctly.
 Increased risk of errors: When data is redundant, there is a greater risk of errors in
the database. For example, if the same data is stored in multiple tables, there is a risk of
inconsistencies between the tables.
 Reduced flexibility: Redundancy can reduce the flexibility of the database. For example, if a
change needs to be made to a particular piece of data, it may need to be updated in multiple
places, which can be time-consuming and error-prone.

In databases, data redundancy is a prevalent issue. It can cause a number of problems , such
as inconsistent data, wasted storage space, decreased database performance, and increased
security risk.
The most effective technique to reduce redundancy is to normalize the database. The use of
views materialized views, and foreign keys are additional techniques to reduce redundancy

Codd's Rules in DBMS


Codd's rules are proposed by a computer scientist named Dr. Edgar F. Codd and he also
invent the relational model for database management. These rules are made to ensure data
integrity, consistency, and usability. This set of rules basically signifies the characteristics
and requirements of a relational database management system (RDBMS). In this article, we
will learn about various Codd's rules.
Codd's Rules in DBMS
Rule 1: The Information Rule
All information, whether it is user information or metadata, that is stored in a database must
be entered as a value in a cell of a table. It is said that everything within the database is
organized in a table layout.
 Rule 2: The Guaranteed Access Rule
Each data element is guaranteed to be accessible logically with a combination of the table
name, primary key (row value), and attribute name (column value).
Rule 3: Systematic Treatment of NULL Values
Every Null value in a database must be given a systematic and uniform treatment.
Rule 4: Active Online Catalog Rule
The database catalog, which contains metadata about the database, must be stored and
accessed using the same relational database management system.
Rule 5: The Comprehensive Data Sublanguage Rule
A crucial component of any efficient database system is its ability to offer an easily
understandable data manipulation language (DML) that facilitates defining, querying, and
modifying information within the database.
Rule 6: The View Updating Rule
All views that are theoretically updatable must also be updatable by the system.
Rule 7: High-level Insert, Update, and Delete
A successful database system must possess the feature of facilitating high-level insertions,
updates, and deletions that can grant users the ability to conduct these operations with ease
through a single query.
Rule 8: Physical Data Independence
Application programs and activities should remain unaffected when changes are made to the
physical storage structures or methods.
Rule 9: Logical Data Independence
Application programs and activities should remain unaffected when changes are made to the
logical structure of the data, such as adding or modifying tables.
Rule 10: Integrity Independence
Integrity constraints should be specified separately from application programs and stored in
the catalog. They should be automatically enforced by the database system.
Rule 11: Distribution Independence
The distribution of data across multiple locations should be invisible to users, and the
database system should handle the distribution transparently.
Rule 12: Non-Subversion Rule
If the interface of the system is providing access to low-level records, then the interface must
not be able to damage the system and bypass security and integrity constraints.

introduction of ER Model


The Entity-Relationship Model (ER Model) is a conceptual model for designing a databases.
This model represents the logical structure of a database, including entities, their attributes
and relationships between them.
 Entity: An objects that is stored as data such as Student, Course or Company.
 Attribute: Properties that describes an entity such as StudentID, CourseName,
or EmployeeEmail.
 Relationship: A connection between entities such as "a Student enrolls in a Course".
Components of ER Diagram

The graphical representation of this model is called an Entity-Relation Diagram (ERD).

ER Model in Database Design Process
We typically follow the below steps for designing a database for an application.
 Gather the requirements (functional and data) by asking questions to the database users.
 Create a logical or conceptual design of the database. This is where ER model plays a
role. It is the most used graphical representation of the conceptual design of a database.
 After this, focus on Physical Database Design (like indexing) and external design (like
views)
Why Use ER Diagrams In DBMS?
 ER diagrams represent the E-R model in a database, making them easy to convert into
relations (tables).
 These diagrams serve the purpose of real-world modeling of objects which makes them
intently useful.
 Unlike technical schemas, ER diagrams require no technical knowledge of the underlying
DBMS used.
 They visually model data and its relationships, making complex systems easier to
understand.
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, such as a student's
multiple phone numbers
 Double Rectangle: Represents weak entities, which depend on other entities for
identification.
Symbols used in ER Diagram

What is an Entity?
An Entity represents a real-world object, concept or thing about which data is stored in a
database. It act as a building block of a database. Tables in relational database represent these
entities.
Example of entities:
 Real-World Objects: Person, Car, Employee etc.
 Concepts: Course, Event, Reservation etc.
 Things: Product, Document, Device etc.
The entity type defines the structure of an entity, while individual instances of that type
represent specific entities.
What is an Entity Set?
An entity refers to an individual object of an entity type, and the collection of all entities of a
particular type is called an entity set. For example, E1 is an entity that belongs to the entity
type "Student," and the group of all students forms the entity set.
In the ER diagram below, the entity type is represented as:
 Entity Set

We can represent the entity sets in an ER Diagram but we can't represent individual entities
because an entity is like a row in a table, and an ER diagram shows the structure and
relationships of data, not specific data entries (like rows and columns). An ER diagram is a
visual representation of the data model, not the actual data itself.
Types of Entity
There are two main types of entities:
1. Strong Entity
A Strong Entity is a type of entity that has a key Attribute that can uniquely identify each
instance of the entity. A Strong Entity does not depend on any other Entity in the Schema for
its identification. It has a primary key that ensures its uniqueness and is represented by a
rectangle in an ER diagram.
2. Weak Entity
A Weak Entity cannot be uniquely identified by its own attributes alone. It depends on a
strong entity to be identified. A weak entity is associated with an identifying entity (strong
entity), which helps in its identification. A weak entity are 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.
Example:
A company may store the information of dependents (Parents, Children, Spouse) of an
Employee. But the dependents can't exist 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.

Strong Entity and Weak Entity

Attributes in ER Model
Attributes are the properties that define the entity type. For example, for a Student entity
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.

Attribute

Types of Attributes
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 an underline.

Key Attribute

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.

Composite Attribute

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.
 Multivalued Attribute

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.

Derived Attribute

The Complete Entity Type Student with its Attributes can be represented as:

Entity and Attributes

Relationship Type and Relationship Set

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.

Entity-Relationship Set

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.

Relationship Set

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.

Unary Relationship
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.

Binary Relationship

3. Ternary Relationship: When there are three entity sets participating in a relationship, the
relationship is called a ternary relationship.
4. N-ary Relationship: When there are n entities set participating in a relationship, the
relationship is called an n-ary relationship.
Cardinality in ER Model
The maximum 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.

One to One Cardinality

Using Sets, it can be represented as:

Set Representation of One-to-One

2. One-to-Many
In one-to-many mapping as well where each entity can be related to more than one entity.
Let us assume that one surgeon department can accommodate many doctors. So the
Cardinality will be 1 to M. It means one department has many Doctors.

one to many cardinality

Using sets, one-to-many cardinality can be represented Set Representation of One-to-Many

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.

many to one cardinality

Using Sets, it can be represented as:

 Set Representation of Many-to-One

In this case, each student is taking only 1 course but 1 course has been taken by many
students.
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.

many to many cardinality

Using Sets, it can be represented as:

Many-to-Many
Set Representation
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.

Total Participation and Partial Participation

Using Set, it can be represented as,

Set representation of Total

Participation and Partial Participation

Every student in the Student Entity set participates in a relationship but there exists a course C4 that is not
taking part in the relationship.
How to Draw an ER Diagram
1. Identify Entities: The very first step is to identify all the Entities. Represent these entities in a
Rectangle and label them accordingly.
2. Identify Relationships: The next step is to identify the relationship between them and represent them
accordingly using the Diamond shape. Ensure that relationships are not directly connected to each other.
3. Add Attributes: Attach attributes to the entities by using ovals. Each entity can have multiple
attributes (such as name, age, etc.), which are connected to the respective entity.
4. Define Primary Keys: Assign primary keys to each entity. These are unique identifiers that help
distinguish each instance of the entity. Represent them with underlined attributes.
5. Remove Redundancies: Review the diagram and eliminate unnecessary or repetitive entities and
relationships.
6. Review for Clarity: Review the diagram make sure it is clear and effectively conveys the relationships
between the entities.
 ONE MARK

1. A ___ is normalized after it has been organized.

A. Table
B. Database
C. Row
D. Column

Answer: B) Database

2. By normalizing relations or sets of relations, one minimizes ___.

A. Data
B. Fields
C. Redundancy
D. Database

Ans wer: C) Redundancy

3. In addition to removing undesirable characteristics, normalization also eliminates ___ anomalies.

A. Insert
B. Update
C. Delete
D. All of the above

Answer: D) All of the above

5. Redundancy is reduced in a database table by using the ___ form.

A. Abnormal
B. Normal
C. Special
D. None

Answer: B) Normal

6. In practical applications, how many types of Normal Forms are there?

A. 3
B. 4
C. 5
D. 6

Answer: B) 4

7. Which of the following is not a type of Normal Form?

A. 1NF
B. 2NF
C. 3NF
D. 10NF

Answer: D) 10NF

8. Which of the following is a type of Normal Form?

 A. ACNF
B. BCNF
C. CCNF
D. DCNF

9. When a relation contains an atomic value, it is a ___ relation.

A. 1NF
B. 2NF
C. 3NF
D. BCNF

Answer: A) 1NF

10. 2NF relations are those that are in 1NF with all the attribute types dependent on the ___ key.

A. Primary
B. Foreign
C. Composite
D. Alternate

Answer: A) Primary

11. When a relation is in 2NF and there is ___, it is in 3NF.

A. Transition Dependency
B. No Transition Dependency
C. Relational Dependency
D. No Relational Dependency

Answer: B) No Transition Dependency

12. A relation is in ___ if it is in Boyce Codd normal form and does not have any multivalued
dependencies.

A. 1NF
B. 2NF
C. 3NF
D. 4NF

Answer: D) 4NF

13. If a relation has a 4NF and no join dependency, and when it joins, it should be___, it is
considered 5NF.

A. Lossful
B. Lesser
C. Lossless
D. Full

Answer: C) Lossless

14. What is TRUE about the First Normal Form (1NF)?

A. If a relation contains an atomic value, it will be 1NF.

B. A table attribute cannot contain more than one value, according to this rule.
C. A single-valued attribute can only be stored in it.
 D. All of the above

Answer: D) All of the above

15. Neither multivalued nor composite attributes, nor their combinations, may be used in the ___
normal form.

A. First
B. Second
C. Third
D. fourth

Answer: A) First

16. What is TRUE about the Second Normal Form (2NF)?

A. Relational must belong to 1NF in the 2NF.

B. All attributes other than the primary key are fully functional in the second normal form
C. Both A and B
D. None of the above

Answer: C) Both A and B

17. What is TRUE about the Third Normal Form (3NF)?

A. When a relation is in 2NF and does not contain transitive partial dependencies, it will be in 3NF.
B. Data duplication is reduced by using 3NF.
C. It helps maintain the integrity of the data.
D. All of the above

Answer: D) All of the above

18. Non-prime attributes cannot be transitively dependent, so the relation must have the ___
normal form.

A. First
B. Second
C. Third
D. Fourth

Answer: C) Third

19. There needs to be which of the following conditions for each nontrivial dependency of function
X on function Y for a relation to be in third normal form.

A. A super key is X.
B. Every element of Y is a part of some candidate key, i.e, Y is a prime attribute.
C. Either A or B
D. None of the above

Answer: C) Either A or B

20. What is TRUE about BCNF?

A. The advanced version of 3NF is BCNF.

 B. BCNF is stricter than 3NF.
C. The super key is X for any functional dependency of X -> Y in the table.
D. All of the above

Answer: D) All of the above

21. A relation is in ___ if it is in Boyce Codd normal form and does not have any multivalued
dependencies.

A. 1NF
B. 2NF
C. 3NF
D. 4NF

Answer: D) 4NF

22. If more than one value of B is present for a single value of A in a dependency A -> B, then the
relationship is ___.

A. Single
B. Multi-valued
C. Both a and b
D. None of the above

Answer: B) Multi-valued

23. What is TRUE about 5NF?

A. A relation is in 5NF if it is in 4NF, does not contain any join dependencies, and has lossless
joining.
B. In order to avoid redundancy, 5NF ensures that the tables are broken up in as many ways as
possible.
C. Project-join normal form (5NF) is sometimes referred to as Project-join NF.
D. All of the above

Answer: D) All of the above

24. Which of the following is not a level of data abstraction?

a.Physical Level

b.Critical Level

c.Logical Level

d.View Level

Answer: (b).Critical Level

25. What is the basic client/server architecture, one has to deal with?

A. Large number of PCs

B. Web servers
C. Database Servers
D. All of the above

Answer: D) All of the above

26. How many types of DBMS architectures are there?

A. 1
B. 2
C. 3
D. 4

Answer: C) 3

27. What is TRUE about 1-tier architecture?

A. It is directly not available to the user

B. Changes are not done on the database
C. No handy tool is provided for the end user
D. It is not used for the development of local application

Answer: C) No handy tool is provided for the end user

28. Basic client-server model is similar to –

A. 2-tier architecture
B. 3-tier architecture
C. 4-tier architecture
D. 5-tier architecture

Answer: A) 2-tier architecture

29. Which API is used for the interaction in 2-tier architecture?

A. ODBC
B. JDBC
C. Both A. and B.
D. None of the above

Answer: C) Both A. and B.

30. What is done through client-side in the 2-tier architecture?

A. Running of user interfaces and application program

B. To provide query processing and transaction management functionalities
C. Establish a connection with the other side
D. Both A. and C.

Answer: D) Both A. and C

31. In which architecture, another layer is there between client and server?

A. 1-tier architecture
B. 2-tier architecture
C. 3-tier architecture
D. 4-tier architecture

Answer: C) 3-tier architecture

32. In which architecture, client can't directly communicate with the server?
 A. 1-tier architecture
B. 2-tier architecture
C. 3-tier architecture
D. None of the above

Answer: C) 3-tier architecture

33. What is present in Server but not in Client?

A. Database
B. Application Server
C. User
D. None

Answer: A) Database

34. 3-tier architecture is used in –

A. Large web application

B. Small web application
C. Both large and small web application
D. Neither small nor large web application

Answer: A) Large web application

35. What is three schema architecture also known as?

A. ANSI/SPARC architecture
B. Three-level architecture
C. Both A. and B.
D. None of the above

Answer: C) Both A. and B.

36. What is TRUE about three schema architecture?

A. A specific database system is described by this framework.

B. Separating user applications from physical databases is also achieved by using the three-schema
architecture.
C. Based on this architecture, there are three tiers. Three different categories are identified for the
database.
D. All of the above

Answer: D) All of the above

37. Requests and responses are transformed using ________ between different database layers.

A. Mapping
B. Deflection
C. Taping
D. Reflection

Answer: A) Mapping

38. For _____ mapping, the request must be transformed from external level to conceptual level.

A. External
 B. Conceptual
C. Internal
D. Both A. and B.

Answer: D) Both A. and B.

39. DBMS transform requests from the conceptual to the internal level through ____ mapping.

A. External
B. Internal
C. Hidden
D. External / Conceptual

Answer: B) Internal

40. An important objective of a ___ is to make sure each user has a customized view of the same
data while only storing the underlying information once.

A. One-level architecture
B. Two-level architecture
C. Three-level architecture
D. Four-level architecture

Answer: C) Three-level architecture

41. By using three schemas, the database structure can be separated from the user's view. What's
the reason behind the same?

A. The same data needs to be viewed differently by different users.

B. Data access needs may change over time for a particular user.
C. The same data should be available to all users based on their needs.
D. All of the above

Answer: D) All of the above

42. Which Schema is also known as Physical Schema?

A. Internal
B. Conceptual
C. External
D. None

Answer: A) Internal

43. What is TRUE about internal level?

A. An internal database structure is described by its external schema.

B. Intangible data model is used.
C. In a block, it determines how data is stored.
D. Simple low-level data structures are described at the physical level in detail.

Answer: C) In a block, it determines how data is stored

44. What is internal level concerned with?

A. Access Paths
B. Storage Space Allocations
 C. Data Compression
D. All of the above

Answer: D) All of the above

45. __________ schemas describe the structure of a database from a conceptual perspective.

A. Internal
B. Conceptual
C. External
D. None

Answer: B) Conceptual

46. Conceptual level is also termed as __________ level.

A. Physical
B. Logical
C. Rational
D. Transitional

 Answer: B) Logical

47. What is TRUE about Conceptual level?

A. Describes how the entire database is structured conceptually.

B. Conceptually, a database describes what data will be stored in it and what relationships exist
among them.
C. Details of internal implementation such as a data structure are hidden at the conceptual level.
D. All of the above

Answer: D) All of the above

48. Who exactly works at the Conceptual level?

A. Programmers
B. Database Administrators
C. Both A. and B.
D. None of the above

Answer: C) Both A. and B.

49. What is an external level also known as –

A. Physical
B. Logical
C. View
D. Rational

Answer: C) View
50. There are several schemas in a database that sometimes are referred to as ___________ on the
external level.

A. Schema
B. Superschema
 C. Subschema
D. Adschema

Answer: C) Subschema

51. In view schemas, a particular _______ group can view only the database parts that are relevant
to them while hiding the rest of the database.

A. Admin
B. User
C. Employee
D. Team

Answer: B) User

52. _____ User interaction with a database system is described by the view schema.

A. End
B. Inter
C. Front
D. Back

Answer: A) End

53. ____________ are what connects the three types of schema within the DBMS.

A. Mapping
B. Copying
C. Layering
D. Breaking

Answer: A) Mapping

54. How many types of mapping are there in database architecture?

A. 1
B. 2
C. 3
D. 4 Answer: B) 2

5MARK:

1.What are two integrity rules of the relational model?

2.
3.
4.
5.

10MARK

3
 4

UNIT-III
Normalization of Database Tables: Database tables and Normalization – The Need for Normalization –
The Normalization Process – Higher level Normal Form. Introduction to SQL: Data Definition
Commands – Data Manipulation Commands – SELECT Queries – Additional Data Definition Commands
– Additional SELECT Query Keywords – Joining Database Tables.

DATABASE NORMALIZATION:

Database normalization is the process of structuring a relational database in accordance with a series of
so-called normal forms in order to reduce data redundancy and improve data integrity. It was first
proposed by British computer scientist Edgar F. Codd as part of his relational model.

Normalization entails organizing the columns (attributes) and tables (relations) of a database to ensure
that their dependencies are properly enforced by database integrity constraints. It is accomplished by
applying some formal rules either by a process of synthesis (creating a new database design)
or decomposition (improving an existing database design).

Introduction of Database Normalization

 ensures thatconsistency,
Normalization
efficiency, theisdatabase
an important
and
is adaptable
accuracy.
processto
Itinchanging
makes
database
it easier
business
designtothat
manage
needs.
helpsand
improve
maintain
the database's
the data and
 Database normalization is the process of organizing the attributes of the database to reduce or
eliminate data redundancy (having the same data but at different places).
 Data redundancy unnecessarily increases the size of the database as the same data is repeated
in many places. Inconsistency problems also arise during insert, delete, and update
operations.
 In the relational model, there exist standard methods to quantify how efficient a databases is.
These methods are called normal forms and there are algorithms to covert a given database
into normal forms.
 Normalization generally involves splitting a table into multiple ones which must be linked
each time a query is made requiring data from the split tables.
Why do we need Normalization?
The primary objective for normalizing the relations is to eliminate the below anomalies. Failure
to reduce anomalies results in data redundancy, which may threaten data integrity and cause
additional issues as the database increases. Normalization consists of a set of procedures that
assist you in developing an effective database structure.
 Insertion Anomalies: Insertion anomalies occur when it is not possible to insert data into a
database because the required fields are missing or because the data is incomplete. For
example, if a database requires that every record has a primary key, but no value is provided
for a particular record, it cannot be inserted into the database.
 Deletion anomalies: Deletion anomalies occur when deleting a record from a database and
can result in the unintentional loss of data. For example, if a database contains information
about customers and orders, deleting a customer record may also delete all the orders
associated with that customer.
 Updation anomalies: Updation anomalies occur when modifying data in a database and can
result in inconsistencies or errors. For example, if a database contains information about
employees and their salaries, updating an employee’s salary in one record but not in all
related records could lead to incorrect calculations and reporting.
Normalization Process in DBMS:


Database Normalization is any systematic process of organizing a database schema such that no data
redundancy occurs and there is least or no anomaly while performing any update operation on data. In
other words, it means dividing a large table into smaller pieces such that data redundancy should be
eliminated. The normalizing procedure depends on the functional dependencies among the attributes
inside a table and uses several normal forms to guide the design process.
The Normalization Process Begins With the Following:
 First Normal Form (1NF): Ensures that each column contains only atomic values that cannot be
divided, and each record is unique.
 Second Normal Form (2NF): Includes 1NF and removes subsets of data that would apply for more
than one row and places that data in a separate table. It deals with a partial dependency example when
a non-key attribute depends on part of a composite primary key.
 Third Normal Form(3NF): This applies further normalization to the schema by removing transitive
dependencies, which are where the non-key attributes depend on other non-key attributes.
Besides these, BCNF is a stronger version of 3NF, developed by Raymond F. Boyce and Edgar F. Codd,
which handles some more cases of redundancy not dealt with by 3NF. Further normal forms like Fourth
Normal Form and Fifth Normal Form apply to the more complex scenarios for multi-valued dependencies
and join dependencies, respectively. These are also less commonly applied.
Boyce-Codd Normal Form (BCNF)
Boyce-Codd Normal Form, or BCNF for short, is an extension of the Third Normal Form, 3NF, in that it
seeks to eliminate certain types of redundancy that the latter does not catch. In BCNF, for a table to be
said to be in BCNF, it needs to have the following condition met: for any nontrivial functional
dependency
X→Y, X must be a superkey.
X needs to be a superkey. That is, for every functional dependency of the table, on the left of that
dependency, an attribute or set of attributes should be a superkey of the table.
This requirement is stronger than that of 3NF, which only had a non-key attribute non-transitively
dependent on a primary key. BCNF takes care of situations where, even for a table in 3NF, redundancy
would still arise whenever there is added a dependency whose left-hand side is not a superkey, hence
ensuring a higher level of normalization and, therefore, reduced redundancy.

Features of Database Normalization:

 Elimination of Data Redundancy: One of the main features of normalization is to
eliminate the data redundancy that can occur in a database. Data redundancy refers to the
repetition of data in different parts of the database. Normalization helps in reducing or
eliminating this redundancy, which can improve the efficiency and consistency of the
database.
 Ensuring Data Consistency: Normalization helps in ensuring that the data in the database
is consistent and accurate. By eliminating redundancy, normalization helps in preventing
inconsistencies and contradictions that can arise due to different versions of the same data.
 Simplification of Data Management: Normalization simplifies the process of managing
data in a database. By breaking down a complex data structure into simpler tables,
normalization makes it easier to manage the data, update it, and retrieve it.
 Improved Database Design: Normalization helps in improving the overall design of the
database. By organizing the data in a structured and systematic way, normalization makes it
easier to design and maintain the database. It also makes the database more flexible and
adaptable to changing business needs.
 Avoiding Update Anomalies: Normalization helps in avoiding update anomalies, which
can occur when updating a single record in a table affects multiple records in other tables.
Normalization ensures that each table contains only one type of data and that the
relationships between the tables are clearly defined, which helps in avoiding such
anomalies.
 Standardization: Normalization helps in standardizing the data in the database. By
organizing the data into tables and defining relationships between them, normalization
helps in ensuring that the data is stored in a consistent and uniform manner.
Normal Forms Description of Normal Forms

First Normal A relation is in first normal form if every attribute in that relation is single-valued
Form (1NF) attribute.

A relation that is in First Normal Form and every non-primary-key attribute is fully
Second Normal functionally dependent on the primary key, then the relation is in Second Normal
Form (2NF) Form (2NF).

A relation is in the third normal form, if there is no transitive dependency for non-
prime attributes as well as it is in the second normal form. A relation is in 3NF if at
least one of the following conditions holds in every non-trivial function dependency
X –> Y.
Third Normal  X is a super key.
Form (3NF)  Y is a prime attribute (each element of Y is part of some candidate key).

For BCNF the relation should satisfy the below conditions

Boyce-Codd  The relation should be in the 3rd Normal Form.
Normal Form  X should be a super-key for every functional dependency (FD) X−>Y in a given
(BCNF) relation.

A relation R is in 4NF if and only if the following conditions are satisfied:

Fourth Normal  It should be in the Boyce-Codd Normal Form (BCNF).
Form (4NF)  The table should not have any Multi-valued Dependency.

A relation R is in 5NF if and only if it satisfies the following conditions:

Fifth Normal  R should be already in 4NF.
Form (5NF)  It cannot be further non loss decomposed (join dependency).

Advantages of Normalization
 Normalization eliminates data redundancy and ensures that each piece of data is stored in only one
place, reducing the risk of data inconsistency and making it easier to maintain data accuracy.
 By breaking down data into smaller, more specific tables, normalization helps ensure that each table
stores only relevant data, which improves the overall data integrity of the database.
 Normalization simplifies the process of updating data, as it only needs to be changed in one place
rather than in multiple places throughout the database.
 Normalization enables users to query the database using a variety of different criteria, as the data is
organized into smaller, more specific tables that can be joined together as needed.
 Normalization can help ensure that data is consistent across different applications that use the same
database, making it easier to integrate different applications and ensuring that all users have access
to accurate and consistent data.
Disadvantages of Normalization
 Normalization can result in increased performance overhead due to the need for
additional join operations and the potential for slower query execution times.
 Normalization can result in the loss of data context, as data may be split across multiple
tables and require additional joins to retrieve.
 Proper implementation of normalization requires expert knowledge of database design
and the normalization process.
 Normalization can increase the complexity of a database design, especially if the data
model is not well understood or if the normalization process is not carried out correctly.
 Database normalization is a key concept in organizing data efficiently within a database.
By reducing redundancy, ensuring data consistency, and breaking data into well-structured
tables, normalization enhances the accuracy, scalability, and maintainability of a database.
It simplifies data updates, improves integrity, and supports flexible querying, making it an
essential practice for designing reliable and efficient database systems.

What is SQL?

SQL stands for Structured Query Language. It is a standardized programming language used
to manage and manipulate relational databases. It enables users to perform a variety of tasks
such as querying data, creating and modifying database structures, and managing access
permissions. SQL is widely used across various relational database management systems such
as MySQL, PostgreSQL, Oracle, and SQL Server
.
In this article, we will learn about what SQL is, and its functionality, structure, and practical
uses. We will also learn its characteristics, rules, commands, etc from basic to advanced
technical features.

SQL History
 SQL was invented in 1970s and was first commercially distributed by Oracle.
 The original name was given by IBM as Structured English Query Language,
abbreviated by the acronym SEQUEL.
Components of a SQL System
A SQL system consists of several key components that work together to enable efficient
data storage, retrieval, and manipulation. Understanding these components is crucial for
mastering SQL and its role in relational database systems. Some of the Key components of
a SQL System are:
 Databases: Databases are structured collections of data organized into tables, rows, and
columns. Databases serve as repositories for storing information efficiently and provide
a way to manage and access data.
 Tables: Tables are the fundamental building blocks of a database, consisting of rows
(records) and columns (attributes or fields). Tables ensure data integrity and consistency
by defining the structure and relationships of the stored information.
 Queries: Queries are SQL commands used to interact with databases. They enable users
to retrieve, update, insert, or delete data from tables, allowing for efficient data
manipulation and retrieval.
 Constraints: Constraints are rules applied to tables to maintain data
integrity. Constraints define conditions that data must meet to be stored in the database,
ensuring accuracy and consistency.
 Stored Procedures: Stored procedures are pre-compiled SQL statements stored in the
database. Stored procedures can accept parameters, execute complex operations, and
return results, enhancing efficiency, reusability, and security in database management.
 Transactions: Transactions are groups of SQL statements that are executed as a single
unit of work. Transactions ensure data consistency and integrity by allowing for the
rollback of changes if any part of the transaction fails.
Some other important components include:
 Data Types
 Indexes
 Views
 Security and Permissions
 Joins
SQL Injection is a cyberattack where malicious SQL queries are injected into input fields
to manipulate a database, enabling unauthorized access, data modification, or corruption.
Using parameterized queries and input validation helps prevent such attacks.

What are the Characteristics of SQL?

 User-Friendly and Accessible: SQL is designed for a broad range of users,
including those with minimal programming experience, making it approachable for
non-technical individuals.
 Declarative Language: As a non-procedural language, SQL allows users to specify
what data is needed rather than how to retrieve it, focusing on the desired results
rather than the retrieval process.
 Efficient Database Management: SQL enables the creation, modification, and
management of databases efficiently, saving time and simplifying complex database
operations.
 Standardized Language: Based on ANSI (American National Standards Institute)
and ISO (International Organization for Standardization) standards, SQL ensures
consistency and stability across various database management systems (DBMS).
 Command Structure: SQL does not require a continuation character for multi-line
queries, allowing flexibility in writing commands across one or multiple lines.
 Execution Mechanism: Queries are executed using a termination character (e.g., a
semicolon ;), enabling immediate and accurate command processing.
 Built-in Functionality: SQL includes a rich set of built-in functions for data
manipulation, aggregation, and formatting, empowering users to handle diverse
data-processing needs effectively.
How SQL Works?
 Structured Query Language (SQL) operates on a server machine, where it processes
database queries and returns results efficiently. Below are the key software
components involved in the SQL execution process.
 Input: The process begins when a user submits an SQL query through a database
interface or application. This query typically specifies the desired operation, such as
data retrieval, insertion, updating, or deletion.
 Parsing: The query is passed to the query processor, which breaks it into smaller
units called tokens. These tokens represent keywords, table names, column names,
and other elements of the query. The processor then validates the syntax against
SQL standards and the database schema to ensure the query is well-formed and
executable.
 Optimization: After parsing, the query is handed to the optimizer, which evaluates
multiple ways to execute the query. The optimizer considers factors like indexes,
table statistics, and available resources to generate the most efficient execution plan.
This step ensures that the query runs with minimal resource consumption and
maximum performance.
 Execution: The execution engine follows the plan provided by the optimizer. It
interacts with the storage engine, which retrieves, manipulates, or updates the
required data from the database tables. During this step, SQL statements like
SELECT, INSERT, UPDATE, or DELETE are translated into actions performed on
the underlying data.
  Output: Once the execution engine processes the query, the result is formatted and
returned to the user. Depending on the query type, the output could be a result set
(for SELECT queries) or an acknowledgment of the operation (for INSERT,
UPDATE, or DELETE queries).
 By combining these steps, SQL ensures the seamless interaction between users and
relational databases, enabling efficient data manipulation and retrieval.
Rules for Writing SQL Queries
 There are certain rules for SQL which would ensure consistency and functionality
across databases. By following these rules, queries will be well formed and well
executed in any database.
 Statement Termination: Every SQL statement ends with a semicolon (;), signaling
the DBMS to execute the command.
 Case Insensitivity: SQL keywords (e.g., SELECT, INSERT) are case-insensitive,
but database names and column names may be case-sensitive depending on the
DBMS.
 Whitespace Flexibility: SQL statements can span multiple lines, but keywords and
identifiers must be separated by at least one space.
 Unique Identifiers: Reserved words (e.g., SELECT, FROM) cannot be used as
table or column names unless enclosed in double quotes (") or backticks (`),
depending on the DBMS.
 Comments: Comments enhance readability:
o Single-line comments: --
o Multi-line comments: /* ... */
 Data Integrity: Constraints like NOT NULL, UNIQUE, and PRIMARY KEY must
be defined correctly to maintain data consistency.
 String Literals: String values must be enclosed in single quotes (').
 Valid Identifiers: Table and column names must:
o Begin with an alphabetic character.
o Contain up to 30 characters.
o Avoid special characters except underscores (_).
 By following these rules, SQL users ensure reliable query execution and
maintainable database structures.
What are SQL Commands?
 Structured Query Language (SQL) commands are standardized instructions used by
developers to interact with data stored in relational databases. These commands
allow for the creation, manipulation, retrieval, and control of data, as well as
database structures. SQL commands are categorized based on their specific
functionalities:
o Data Definition Language
 These commands are used to define the structure of database objects
by creating, altering, and dropping the database objects. Based on the needs of the
business, database engineers create and modify database objects using DDL.
The CREATE command, for instance, is used by the database engineer to create
database objects like tables, views, and indexes.

Command Description

Creates a new table, a view on a table, or some other object in the

CREATE database.
Command Description

ALTER Modifies an existing database object, such as a table

DROP Deletes an entire table, a view of a table, or other objects in the database

. Data Manipulation Language

A relational database can be updated with new data using data manipulation language (DML)
statements. The INSERT command, for instance, is used by an application to add a new record
to the database.
Command Description

INSERT Creates a record.

UPDATE Modifies records.

DELETE Deletes records.

3. Data Query Language

Data retrieval instructions are written in the data query language (DQL), which is used to
access relational databases. The SELECT command is used by software programs to filter and
return particular results from a SQL table.
4. Data Control language
DCL commands manage user access to the database by granting or revoking permissions.
Database administrators use DCL to enforce security and control access to database objects.
Command Description

GRANT Gives a privilege to the user.

REVOKE Takes back privileges granted by the user.

5. Transaction Control Language

TCL commands manage transactions in relational databases, ensuring data integrity and
consistency. These commands are used to commit changes or roll back operations in case
of errors.

Command Description

Saves all changes made during the current transaction on a permanent

basis. Some databases provide an auto-commit feature, which can be
COMMIT configured using settings.

ROLLBACK Reverts changes made during the current transaction, ensuring no

 Command Description

unwanted changes are saved.

Sets a point within a transaction to which changes can be rolled back,

SAVEPOINT allowing partial rollbacks

Benefits of SQL
 Efficiency: SQL is designed to handle complex queries and large datasets with optimal
performance, making data retrieval and manipulation seamless.
 Standardization: As an ANSI and ISO standard language, SQL provides a universal
method to interact with relational databases across platforms.
 Scalability: SQL supports databases ranging from small-scale applications to
enterprise-level systems, ensuring smooth operations regardless of size.
 Flexibility: SQL can be extended with procedural programming (e.g., PL/SQL, T-SQL)
to build complex business logic and custom functions.
Limitations of SQL
 Complexity in Advanced Operations: Advanced functionalities such as indexing,
query optimization, and performance tuning require in-depth technical knowledge.
 Scalability Concerns: SQL performs best with structured data; handling unstructured
data or massive distributed systems can pose challenges.
 Platform-Specific Variations: While SQL is standardized, many databases implement
unique extensions, leading to portability and compatibility issues.
SQL Use Cases
 E-Commerce: Manage customer orders, product catalogs, and inventory.
 Healthcare: Maintain patient records and appointment schedules.
 Banking: Analyze transaction histories and generate financial reports.
 Web Development: Power dynamic websites with user-specific content.
 Machine Learning and Data Science: Combine SQL databases with tools like Python,
R, and TensorFlow to streamline machine learning workflows.

SQL SELECT Query

The select query in SQL is one of the most commonly used SQL commands to retrieve data from
a database. With the select command in SQL, users can access data and retrieve specific records
based on various conditions, making it an essential tool for managing and analyzing data.

In this article, we’ll learn the SQL SELECT statement syntax, show you SQL query examples,
and explore advanced techniques to help you use SELECT queries for data manipulation and
analysis.

What is the SQL SELECT Statement?

The SELECT clause is the first clause and is one of the last clauses of the select statement
that the database server evaluates. The reason for this is that before we can determine what
to include in the final result set, we need to know all of the possible columns that could be
included in the final result set.
The SELECT statement in SQL is used to fetch or retrieve data from a database. It allows
users to access the data and retrieve specific data based on specific conditions. We can
fetch either the entire table or according to some specified rules. The data returned is stored
in a result table. With the SELECT clause of a SELECT command statement, we specify
the columns that we want to be displayed in the query result.
Syntax:
SELECT column1,column2.... FROM table_name ;
Examples of SELECT Statement
Let's look at some examples of the SQL SELECT statement, to understand it better. To
demonstrate the examples, let's create a table which will be used in examples:
CREATE TABLE Customer(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
LastName VARCHAR(50),
Country VARCHAR(50),
Age int(2),
Phone int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age,
Phone)
VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),
(2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
(3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
(4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
(5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');
Output:

SQL Commands are mainly categorized into five categories:

 DDL – Data Definition Language
 DQL – Data Query Language
 DML – Data Manipulation Language
 DCL – Data Control Language
 TCL - Transaction Control Language

DDL - Data Definition Language

DDL or Data Definition Language actually consists of the SQL commands that can be
used for defining, altering, and deleting database structures such as tables, indexes,
and schemas. It simply deals with descriptions of the database schema and is used
to create and modify the structure of database objects in the database
Common DDL Commands
Command Description Syntax

Create database or its objects CREATE TABLE table_name

CREATE (table, index, function, views, (column1 data_type, column2 data_type,
store procedure, and triggers) ...);

Delete objects from the

DROP DROP TABLE table_name;
database

Alter the structure of the ALTER TABLE table_name ADD

ALTER
database COLUMN column_name data_type;

Remove all records from a

table, including all spaces
TRUNCATE TRUNCATE TABLE table_name;
allocated for the records are
removed

Add comments to the data COMMENT 'comment_text' ON

COMMENT
dictionary TABLE table_name;

Rename an object existing in RENAME TABLE old_table_name TO

RENAME
the database new_table_name;

Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);
In this example, a new table called employees is created with columns for employee ID, first name, last
name, and hire date.
2. DQL - Data Query Language
DQL statements are used for performing queries on the data within schema objects. The purpose of
the DQL Command is to get some schema relation based on the query passed to it. This command
allows getting the data out of the database to perform operations with it. When a SELECT is fired
against a table or tables the result is compiled into a further temporary table, which is displayed or
perhaps received by the program.
DQL Command
Command Description Syntax

It is used to retrieve data from SELECT column1, column2, ...FROM table_name

SELECT
the database WHERE condition;
Example:
SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Sales'
ORDER BY hire_date DESC;
This query retrieves employees' first and last names, along with their hire dates, from the employees
table, specifically for those in the 'Sales' department, sorted by hire date.
3. DML - Data Manipulation Language
The SQL commands that deal with the manipulation of data present in the database belong
to DML or Data Manipulation Language and this includes most of the SQL statements. It
is the component of the SQL statement that controls access to data and to the database.
Basically, DCL statements are grouped with DML statements.
Common DML Commands
Command Description Syntax

INSERT INTO table_name (column1, column2, ...)

INSERT Insert data into a table
VALUES (value1, value2, ...);

Update existing data UPDATE table_name SET column1 = value1, column2 =

UPDATE
within a table value2 WHERE condition;

Delete records from a

DELETE DELETE FROM table_name WHERE condition;
database table

Table control
LOCK LOCK TABLE table_name IN lock_mode;
concurrency

Call a PL/SQL or JAVA

CALL CALL procedure_name(arguments);
subprogram

EXPLAIN Describe the access path

EXPLAIN PLAN FOR SELECT * FROM table_name;
PLAN to data

Example:
INSERT INTO employees (first_name, last_name, department)
VALUES ('Jane', 'Smith', 'HR');
This query inserts a new record into the employees table with the first name 'Jane', last
name 'Smith', and department 'HR'.
4. DCL - Data Control Language
DCL (Data Control Language) includes commands such as GRANT and REVOKE which mainly
deal with the rights, permissions, and other controls of the database system. These commands are
used to control access to data in the database by granting or revoking permissions.
Common DCL Commands

Command Description Syntax

Assigns new privileges to a user

GRANT privilege_type [(column_list)]
account, allowing access to
GRANT ON [object_type] object_name TO user
specific database objects, [WITH GRANT OPTION];
actions, or functions.

Removes previously granted

privileges from a user account, REVOKE [GRANT OPTION FOR]
privilege_type [(column_list)] ON
REVOKE taking away their access to
[object_type] object_name FROM user
certain database objects or [CASCADE];
actions.

Example of DCL
GRANT SELECT, UPDATE ON employees TO user_name;
This command grants the user user_name the permissions to select and update records in
the employees table.
5. TCL - Transaction Control Language
Transactions group a set of tasks into a single execution unit. Each transaction begins with a
specific task and ends when all the tasks in the group are successfully completed. If any of
the tasks fail, the transaction fails. Therefore, a transaction has only two
results: success or failure. We can explore more about transactions here.
Common TCL Commands

Command Description Syntax

BEGIN BEGIN TRANSACTION

Starts a new transaction
TRANSACTION [transaction_name];

Saves all changes made during the

COMMIT COMMIT;
transaction

Undoes all changes made during the

ROLLBACK ROLLBACK;
transaction

Creates a savepoint within the

SAVEPOINT SAVEPOINT savepoint_name;
current transaction

Example:
BEGIN TRANSACTION;
UPDATE employees SET department = 'Marketing' WHERE department = 'Sales';
SAVEPOINT before_update;
UPDATE employees SET department = 'IT' WHERE department = 'HR';
ROLLBACK TO SAVEPOINT before_update;
COMMIT;
In this example, a transaction is started, changes are made, and a savepoint is set. If
needed, the transaction can be rolled back to the savepoint before being committed.
Most Important SQL Commands
There are also a few other SQL Commands we often rely on when writing powerful
queries. While they don’t fit neatly into the five main categories, they’re absolutely
essential for working with data effectively.
Command Description

SELECT Retrieves data from one or more tables.

INSERT Adds new rows (records) to a table.

UPDATE Modifies existing data in a table.

DELETE Removes specific rows from a table.

CREATE TABLE Creates a new table in the database.

Modifies the structure of an existing table (e.g., add or remove

ALTER TABLE
columns).

DROP TABLE Permanently deletes a table and its data.

TRUNCATE TABLE Removes all rows from a table but keeps its structure intact.

WHERE Filters records based on a condition.

ORDER BY Sorts the result set in ascending or descending order.

GROUP BY Groups rows that have the same values in specified columns.

HAVING Filters grouped data (used with GROUP BY).

JOIN Combines rows from two or more tables based on a related column.
 Command Description

DISTINCT Removes duplicate values from the result set.

IN / BETWEEN /
Used for advanced filtering conditions.
LIKE

UNION Combines the result of two or more SELECT queries.

GRANT Gives user privileges or permissions.

REVOKE Removes user privileges.

COMMIT Saves all changes made in the current transaction.

ROLLBACK Undoes changes if something goes wrong in a transaction.

SAVEPOINT Sets a point in a transaction to roll back to if needed.

SQL commands such as DDL, DML, DCL, DQL, and TCL are foundational for
effective database management. From creating and modifying tables with DDL
commands to managing transactions with TCL commands in SQL, understanding each
type of command enhances our database skills. Whether we are manipulating data, or
managing data, SQL provides all sets of tools. Now, with this detailed guide, we hope you
have gained a deep understanding of SQL commands, their categories,
and syntax with examples.

ONE MARK:

What is the main purpose of normalization in databases?

A) Increase data redundancy

B) Improve query speed
C) Eliminate data redundancy and improve data integrity
D) Reduce database size
Answer: C
2. Which of the following is a consequence of data redundancy?

A) Faster access to data

B) Data consistency
C) Data anomalies
D) Higher normalization
Answer: C

3. Which normal form removes partial dependency?

A) 1NF
B) 2NF
C) 3NF
D) BCNF
Answer: B

4. What does 1NF primarily deal with?

A) Functional dependency
B) Atomicity of values
C) Multivalued dependency
D) Transitive dependency
Answer: B

5. Which of the following anomalies is caused by data redundancy?

A) Insertion anomaly
B) Deletion anomaly
C) Update anomaly
D) All of the above
Answer: D

6. In normalization, a relation is in 2NF if it is in 1NF and:

A) Has no multivalued dependencies

B) Has no partial dependencies
C) Has no transitive dependencies
D) Is in BCNF
Answer: B

7. Transitive dependency is removed in which normal form?

A) 1NF
B) 2NF
C) 3NF
D) 4NF
Answer: C

8. A relation in BCNF must also be in which normal form?

A) 1NF only
B) 2NF and 3NF
C) Only 4NF
D) None
Answer: B

9. What is a functional dependency?

A) Relationship between tables

B) Relationship between columns
C) Relationship between rows
D) Relationship between databases
Answer: B

10. What is a candidate key?

A) Primary key that is always selected

B) Foreign key
C) Attribute that uniquely identifies a row
D) Composite key only
Answer: C

11. Which of the following is a higher normal form beyond 3NF?

A) 2NF
B) BCNF
C) 1NF
D) All of the above
Answer: B

12. What is required for a table to be in 1NF?

A) Atomic values in cells

B) No transitive dependencies
C) No partial dependencies
D) Foreign key constraints
Answer: A

13. BCNF stands for:

A) Basic Composite Normal Form

B) Boyce-Codd Normal Form
C) Binary Column Normal Form
D) Best Condition Normal Form
Answer: B

14. Which of the following is true about 3NF?

A) Allows transitive dependency

B) Removes partial dependency only
C) Removes transitive dependency
D) Does not remove any dependencies
Answer: C
15. The normalization process is aimed at:

A) Reducing storage
B) Making queries fast
C) Eliminating anomalies
D) Increasing redundancy
Answer: C

16. A relation with multivalued dependencies is not in:

A) 1NF
B) 2NF
C) 3NF
D) 4NF
Answer: D

17. When a table is in 2NF, it:

A) Must be in 3NF
B) Must be in 1NF
C) Must be in 4NF
D) Must be denormalized
Answer: B

18. A primary key can be:

A) Only a single attribute

B) Only numeric
C) A set of attributes
D) Always a foreign key
Answer: C

19. The process of normalization:

A) Creates redundant data

B) Increases inconsistency
C) Organizes data efficiently
D) Merges multiple tables
Answer: C

20. What is a composite key?

A) A key made from foreign keys

B) A primary key made up of more than one attribute
C) A key with multivalued attributes
D) A key with transitive dependency
Answer: B

1. Which SQL command is used to create a table?

a) CREATE TABLE
b) MAKE TABLE
c) NEW TABLE
d) ADD TABLE
Answer: a) CREATE TABLE

2. What is the purpose of the INSERT command in SQL?

a) To delete data
b) To select data
c) To insert data into a table
d) To update a table structure
Answer: c) To insert data into a table

3. Which command is used to remove a table from a database?

a) DELETE TABLE
b) REMOVE TABLE
c) DROP TABLE
d) ERASE TABLE
Answer: c) DROP TABLE

4. The SELECT statement is used to:

a) Insert data
b) Update records
c) Retrieve data
d) Define tables
Answer: c) Retrieve data

5. Which SQL clause is used to filter records?

a) ORDER BY
b) GROUP BY
c) WHERE
d) HAVING
Answer: c) WHERE

6. Which command updates existing data in a table?

a) CHANGE
b) UPDATE
c) EDIT
d) MODIFY
Answer: b) UPDATE

7. What keyword is used to remove duplicate rows in a SELECT query?

a) UNIQUE
b) DISTINCT
c) REMOVE
d) SINGLE
Answer: b) DISTINCT
8. Which of the following is a valid SQL data type?

a) STRING
b) INTEGER
c) CHARACTERS
d) WORD
Answer: b) INTEGER

9. Which SQL clause sorts the result set?

a) WHERE
b) ORDER BY
c) SORT BY
d) GROUP BY
Answer: b) ORDER BY

10. The command DELETE FROM table_name; will:

a) Delete the table

b) Delete all rows from the table
c) Delete only NULL values
d) Drop the database
Answer: b) Delete all rows from the table

11. What does the HAVING clause do?

a) Filters after grouping

b) Filters before grouping
c) Deletes rows
d) Joins tables
Answer: a) Filters after grouping

12. Which SQL function returns the number of rows?

a) SUM()
b) TOTAL()
c) COUNT()
d) NUMBER()
Answer: c) COUNT()

13. Which clause groups rows that have the same values in specified columns?

a) GROUP
b) ORDER
c) GROUP BY
d) AGGREGATE
Answer: c) GROUP BY

14. What is the result of SELECT * FROM employees WHERE salary BETWEEN 30000 AND
50000;?

a) Employees with salary 30000

b) Employees with salary over 50000
c) Employees with salary under 30000
d) Employees with salary between 30000 and 50000
Answer: d) Employees with salary between 30000 and 50000

15. Which keyword is used to rename a column in the result set?

a) AS
b) RENAME
c) SET
d) ALIAS
Answer: a) AS

16. Which JOIN returns all matching records from both tables?

a) LEFT JOIN
b) INNER JOIN
c) RIGHT JOIN
d) OUTER JOIN
Answer: b) INNER JOIN

17. Which JOIN returns unmatched rows from the left table?

a) LEFT JOIN
b) RIGHT JOIN
c) FULL JOIN
d) SELF JOIN
Answer: a) LEFT JOIN

18. What is a PRIMARY KEY used for?

a) To update rows
b) To uniquely identify records
c) To delete records
d) To join tables
Answer: b) To uniquely identify records

19. Which keyword adds a new column to an existing table?

a) INSERT COLUMN
b) MODIFY COLUMN
c) ADD COLUMN
d) CREATE COLUMN
Answer: c) ADD COLUMN

20. The DEFAULT keyword is used to:

a) Set a default value

b) Define a foreign key
c) Delete a column
d) Drop a constraint
Answer: a) Set a default value
21. Which of these is not a DDL command?

a) CREATE
b) ALTER
c) DELETE
d) DROP
Answer: c) DELETE

22. What does the ALTER TABLE command do?

a) Adds rows
b) Deletes table
c) Changes table structure
d) Displays table
Answer: c) Changes table structure

23. In SQL, NULL means:

a) 0
b) Empty string
c) Unknown or missing value
d) False
Answer: c) Unknown or missing value

24. Which SQL command is used to grant privileges?

a) ALLOW
b) GRANT
c) PERMIT
d) ENABLE
Answer: b) GRANT

25. What does a FOREIGN KEY do?

a) Enforces uniqueness
b) Creates a new database
c) Establishes a relationship between tables
d) Deletes records
Answer: c) Establishes a relationship between tables

26. Which clause limits the number of rows returned?

a) TOP
b) LIMIT
c) BOTH a and b (depending on the database)
d) NONE
Answer: c) BOTH a and b (depending on the database)

27. Which of the following is used to remove a column?

a) DELETE COLUMN
b) DROP COLUMN
c) REMOVE COLUMN
d) CLEAR COLUMN
Answer: b) DROP COLUMN

28. In which clause can aggregate functions like SUM, AVG be used directly?

a) SELECT
b) WHERE
c) ORDER BY
d) JOIN
Answer: a) SELECT

29. Which operator is used for pattern matching in SQL?

a) BETWEEN
b) LIKE
c) IN
d) MATCH
Answer: b) LIKE

30. Which statement creates a relationship between two tables?

a) FOREIGN KEY
b) PRIMARY KEY
c) UNIQUE
d) INDEX
Answer: a) FOREIGN KEY.

5 MARK:

1. Explain the process of normalization with suitable examples.

2. What is the need for normalization in database design? Explain with examples.
3. Describe various Data Definition Language (DDL) commands in SQL with
examples.
4. Write and explain different types of SELECT queries with suitable examples.
5. Explain different types of JOINs in SQL with syntax and example for each.

10 MARK:

1. Discuss the different normal forms in detail from 1NF to BCNF. Explain the normalization
process with examples.?
2. Explain the importance of normalization in database design. How does it help in reducing
redundancy and anomalies? Illustrate with examples.?
3. Write detailed notes on SQL Data Definition Language (DDL) and Data Manipulation
Language (DML) commands with syntax and examples.?
4. Explain the SELECT statement in SQL. Discuss various clauses and keywords used in
SELECT queries with examples.?
5. Describe the different types of joins in SQL. Explain their syntax, use cases, and differences
using suitable examples.?
 UNIT-IV

Advanced SQL:Relational SET Operators: UNION – UNION ALL – INTERSECT - MINUS.SQL Join
Operators: Cross Join – Natural Join – Join USING Clause – JOIN ON Clause – Outer Join.Sub Queries
and Correlated Queries: WHERE – IN – HAVING – ANY and ALL – FROM. SQL Functions: Date and
Time Function – Numeric Function – String Function – Conversion

ADVANCED SQL:

This basic to advanced SQL tutorial covers the entire SQL syllabus in a structured way and
provides the best learning material and strategies to master complete SQL in 30 Days. We have
laid out the complete SQL roadmap, and following this roadmap, you will learn all the concepts
of SQL.

All Important concepts in the SQL syllabus like SQL queries, syntax, datatypes, operators,
expressions, comments, etc will be explained in an easy way with examples to provide the best
learning experience. Be it your school/college exams or dream job interview, this tutorial will
help you get through it all. SQL are quite important for data analytics also if you wish to learn
data analytics and about sql and more tools that are used in the industry then you should
checkout our Complete Data analytics course.

SQL Overview:

Structured Query Language or SQL is a standard database language that is used to create,
maintain, destroy, update, and retrieve data from relational databases like MySQL, Oracle,
SQL Server, PostgreSQL, etc.
Whether you want to become a successful Data Scientist or a Business Analyst or a full-stack
web developer or even a software developer, learning and practicing SQL is very important for
all fields. Almost every big tech company uses SQL to manage and organize data be it Uber,
Netflix, Airbnb, Facebook, Google, LinkedIn, or any other, SQL is everywhere.
SQL is also the top and most popular language among data scientists or data engineers. Despite
lots of hype around NoSQL, Hadoop, and other technologies, it's one of the most-used
languages in the entire tech industry, and one of the most popular languages for developers of
all sorts.
Complete SQL Roadmap:

Now that we know, how much learning SQL is important or what is the significance of SQL in
today's world, we shall know how to learn SQL in a systematic way. To help you with this we
have created a practical SQL roadmap of 30 days , based on a lot of study and research.
This roadmap will give you a complete guideline with reference to learning and practicing
SQL for all types of job roles be it, Full Stack Developer, using SQL to retrieve information
from the database, or Data Scientist/Data Analyst using SQL to understand the dataset and then
analyzing it for different models.
Considering the depth of this topic, we would be requiring 30 days to learn SQL from the
beginning to the advanced level. After 30 days, you will be able to apply SQL to real-life
problems and crack the interviews.
Stay committed to the roadmap and solve as many questions as you can solve daily to maintain
consistency.
 SET Operations in SQL
SQL supports few Set operations which can be performed on the table data. These are used to get
meaningful results from data stored in the table, under different special conditions.

In this tutorial, we will cover 4 different types of SET operations, along with example:

1. UNION

2. UNION ALL

3. INTERSECT

4. MINUS

UNION Operation:

UNION is used to combine the results of two or more SELECT statements. However it will
eliminate duplicate rows from its resultset. In case of union, number of columns and datatype
must be same in both the tables, on which UNION operation is being applied.

These separate queries show results from the ‘Founders’ table

(Query 1) and ‘Employees’ table (Query 2) in each of their own result sets
before the operator is applied:
These SELECT statements use the UNION operator to combine the names of
‘Founders’ and ‘Employees’ into a single result set:

The Result Set:

Notice how ‘Shawn Johnson’ and ‘Shane Johnson’ are present in each table before
the UNION operator is applied but are only listed ONCE in the result set.

UNION ALL
hese SELECT statements use the UNION ALL operator to combine the names of
‘Founders’ and ‘Employees’ into a single result set:

The Result Set:

Notice how ‘Shawn Johnson’ and ‘Shane Johnson’ are present in each table before
the UNION ALL operator is applied and are duplicated in the result set.

INTERSECT
These SELECT statements use the INTERSECT operator to include values from the
‘Founders’ and ‘Employees’ tables that are listed in BOTH tables:

The Result Set:

EXCEPT

\
SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.

Let's look at a selection from the "Orders" table:

OrderID CustomerID OrderDate

10308 2 1996-09-18

10309 37 1996-09-19

10310 77 1996-09-20

Then, look at a selection from the "Customers" table:

CustomerID CustomerName ContactName

1 Alfreds Futterkiste Maria Anders

2 Ana Trujillo Emparedados y helados Ana Trujillo

3 Antonio Moreno Taquería Antonio Moreno

Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID"
in the "Customers" table. The relationship between the two tables above is the
"CustomerID" column.

Then, we can create the following SQL statement (that contains an INNER JOIN), that
selects records that have matching values in both tables:

ExampleGet your own SQL Server

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Try it Yourself »

and it will produce something like this:

OrderID CustomerName

10308 Ana Trujillo Emparedados y helados

10365 Antonio Moreno Taquería

10383 Around the Horn

10355 Around the Horn

10278 Berglunds snabbköp

 SQL Natural Join


A Natural Join is a SQL join operation that merges rows from two tables based on the columns with
the same name and data type in both tables. The key feature of a natural join is that it automatically
finds the common columns between the tables and uses them to perform the join. In this article, we will
provide an in-depth overview of SQL Natural Join, the steps to implement it, and compare it
with Inner Join.
What is SQL Natural Join?
A Natural Join performs a join between two tables based on columns that have the same name and
compatible data types. This join operation automatically determines the common columns between the
tables and removes duplicates in the result set. The resulting table will contain only one copy of the
common columns, making the result concise and free from redundant data.
Syntax
SELECT *
FROM TABLE1
NATURAL JOIN TABLE2;
Features of Natural Join
 Cartesian Product: Like other join operations, a natural join essentially combines every row from
one table with all rows from another table, but with additional conditions to match common
attributes.
 Eliminates Duplicate Columns: If two tables share a column (or columns) with the same name, the
natural join will keep only one copy of the shared column(s), eliminating redundancy.
 Automatic Column Matching: Natural joins automatically identify and match columns with the
same name and compatible data types. This eliminates the need to manually specify the columns in
the ON clause as with an INNER JOIN
Steps to implement SQL Natural Join
Let’s break down the process of performing a Natural Join through a practical example. We will create
two tables and join them using SQL Natural Join.
Step 1: Creating Database
First create a database named geeks:
create database geeks;
Step 2: Use the Database
Now, use the geeks database to create the tables:
use geeks;
Step 3: Create Tables
Let’s define two tables in the geeks database: department and employee.
1. Department Table
Create Table department (
DEPT_NAME Varchar(20),
MANAGER_NAME Varchar(255)
);
2. Employee Table
Create Table employee (
EMP_ID int,
EMP_NAME Varchar(20),
DEPT_NAME Varchar(255)
);
Step 4: Insert Data into the Tables
Let’s insert sample data into both the department and employee tables.
1. Insert data into the department table:
INSERT INTO department(DEPT_NAME,MANAGER_NAME) VALUES ( "IT", "ROHAN");
INSERT INTO department(DEPT_NAME,MANAGER_NAME) VALUES ( "SALES", "RAHUL");
INSERT INTO department(DEPT_NAME,MANAGER_NAME) VALUES ( "HR", "TANMAY");
INSERT INTO department(DEPT_NAME,MANAGER_NAME) VALUES ( "FINANCE",
"ASHISH");
INSERT INTO department(DEPT_NAME,MANAGER_NAME) VALUES ("MARKETING",
"SAMAY");
2. Insert data into the employee table.
INSERT INTO employee(EMP_ID, EMP_NAME, DEPT_NAME) VALUES (1, "SUMIT", "HR");
INSERT INTO employee(EMP_ID, EMP_NAME, DEPT_NAME) VALUES (2, "JOEL", "IT");
INSERT INTO employee(EMP_ID, EMP_NAME, DEPT_NAME) VALUES (3, "BISWA",
"MARKETING");
INSERT INTO employee(EMP_ID, EMP_NAME, DEPT_NAME) VALUES (4, "VAIBHAV", "IT");
INSERT INTO employee(EMP_ID, EMP_NAME, DEPT_NAME) VALUES (5, "SAGAR",
"SALES");
Step 5: Verify the Inserted Data
To ensure the data has been inserted correctly, run the following queries to view the data in both tables:
SELECT * FROM employee;
Output :

Query:
SELECT * FROM department;
Output :
 Step 6: Perform SQL Natural Join
Now, we can perform the Natural Join between the employee and department tables. The join will be
based on the DEPT_NAME column, which is common to both tables.
Query:
SELECT *
FROM employee
NATURAL JOIN department;
Output :

Explanation: As shown, the DEPT_NAME column appears only once in the result, even
though it is present in both tables. The other columns from both tables are included, and we
now have a joined table that combines employee details with department information.
Difference Between Natural Join and Inner Join
While Natural Join and Inner Join are both used to combine data from two tables, they
differ in the following ways:
1. Natural Join:
 Automatically joins tables based on common columns (with the same name and data
type).
 Only one copy of each common column is included in the result.
 No need to specify the joining condition explicitly.
2. Inner Join:
 Requires you to explicitly specify the columns to join using the ON clause.
 Includes all common columns from both tables in the result, which may lead to
duplication of column names.
 Provides more control over the join condition.

In summary, a Natural Join is a powerful SQL feature that simplifies the process of
joining two tables based on common columns. It automatically handles the column
matching and removes duplicate columns, making it an efficient choice when working with
tables that share common attributes. However, for more complex joins or when you need
more control over the join conditions, an Inner Join might be a better choice.


What is the SQL USING Clause?
The USING clause is used in SQL JOIN statements to specify the column(s) that two tables
have in common. It eliminates the need to qualify column names with table aliases, making
queries more readable and concise.
 Simplifies JOIN operations by matching common columns.
 Ensures cleaner syntax by avoiding column qualification.
 Works with INNER JOIN, LEFT JOIN, and RIGHT JOIN.
Syntax
SELECT column_list
FROM table1
JOIN table2
USING (common_column);
Key Terms
 table1andtable2 are the tables we want to join.
 common_columnis the shared column used for the join condition.
Example 1: Finding Employee Working Locations
The Employeestable contains information about employees, including
their EMPLOYEE_ID, LAST_NAME, and the department they belong to (DEPARTMENT_ID).
The Departments table lists department details, including DEPARTMENT_ID and LOCATION_ID.

Employee Table
Department Table

Query:
SELECT e.EMPLOYEE_ID, e.LAST_NAME, d.LOCATION_ID
FROM Employees e JOIN Departments d
USING(DEPARTMENT_ID);
Output:

USING Clause Example1

 Common Mistakes When Using the SQL USING Clause
1. Qualifying Columns in the USING Clause
Do not qualify the column specified in the USING clause with a table name or alias. Doing so
will result in an error.
Incorrect:
SELECT e.EMPLOYEE_ID, e.LAST_NAME, d.LOCATION_ID
FROM Employees e JOIN Departments d
USING (d.DEPARTMENT_ID);
Correct:
SELECT e.EMPLOYEE_ID, e.LAST_NAME, d.LOCATION_ID
FROM Employees e JOIN Departments d
USING (DEPARTMENT_ID);
2. Reusing Columns from the USING Clause in WHERE Conditions
Avoid referencing the USING clause column in WHERE conditions without proper handling, as
it may cause errors.
Incorrect:
SELECT l.location_id, l.street_address, l.postal_code, c.country_name
FROM locations l JOIN countries c
USING (country_id)
WHERE c.country_id = 'IT';
Correct:
SELECT l.location_id, l.street_address, l.postal_code, c.country_name
FROM locations l JOIN countries c
USING (country_id)
WHERE country_id = 'IT';
Why Use the USING Clause in SQL?
The USING clause helps avoid ambiguity when joining tables with multiple common
columns. It ensures that only specified columns are used for the join condition, resulting in
more precise and understandable queries.
 Reduces redundancy in SQL queries.
 Improves code readability by eliminating repetitive column references.
 Ensures accurateJOIN conditions with minimal effort.

The SQL USING clause is a valuable tool for simplifying JOIN operations by
reducing redundancy and improving query readability. By understanding its syntax, use
cases, and best practices, we can write cleaner, more efficient SQL queries and avoid
common pitfalls. It is especially useful when dealing with large databases, as it
helps reduce errors caused by column ambiguity. Leveraging the USING clause effectively
can enhance query performance and make our SQL code more maintainable in the long run.
SQL Outer Join

SQL Outer Joins allow retrieval of rows from two or more tables based on a related column.
Unlike inner Joins, they also include rows that do not have a corresponding match in one or both of the
tables. This capability makes Outer Joins extremely useful for comprehensive data analysis and
reporting, especially when dealing with incomplete data or wanting to show all records regardless of
matching conditions.
In this article, we will learn the concept of SQL Outer Joins, its types, syntax, and practical use cases.
We will also explore examples that show how Outer Joins can effectively solve real-world data retrieval
problems.
What is an SQL Outer Join?
Outer Join ensures that all rows from one or both tables are included in the result, even if there is no
match in the other table. It is particularly useful when you need to show all records from one table,
including those that don’t have a match in the other table.
Types of Outer Joins
There are three main types of Outer Joins in SQL:
 LEFT OUTER JOIN (or LEFT JOIN)
 RIGHT OUTER JOIN (or RIGHT JOIN)
 FULL OUTER JOIN
Each of these join types handles unmatched rows differently, and understanding how they work will help
you use them effectively in your SQL queries.
Let’s Consider the two tables, Employees and Departments for understanding all the above outer join
with examples
Employees Table:
EmployeeID Name DepartmentID

1 John 101

2 Sarah 102

3 Michael –

4 Emma 103

Departments Table:
DepartmentID DepartmentName

101 HR

102 IT

103 Marketing

LEFT OUTER JOIN (or LEFT JOIN)

The LEFT OUTER JOIN (referred to as LEFT JOIN) returns all rows from the left table,
and the matching rows from the right table. If there is no match, the result will
include NULL values for columns from the right table.

LEFT OUTER JOIN

Syntax:
SELECT table1.column1, table1.column2, table2.column1, ...
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
Example : To retrieve all employees along with their respective departments, even if they
don’t belong to any department (i.e., the department is NULL), we can use the LEFT
OUTER JOIN
Query:
SELECT Employees.Name, Employees.DepartmentID, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Output:
Name DepartmentID DepartmentName

John 101 HR

Sarah 102 IT

Michael – –

Emma 103 Marketing

In this example, Michael does not belong to any department, so the DepartmentName for
Michael is NULL.
RIGHT OUTER JOIN (RIGHT JOIN)
The RIGHT OUTER JOIN (often called RIGHT JOIN) returns all rows from the right
table, and the matching rows from the left table. If there is no match, the result will
include NULL values for columns from the left table.

RIGHT OUTER JOIN

Syntax:
SELECT table1.column1, table1.column2, table2.column1, ...
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
Example: Let’s now look at a RIGHT OUTER JOIN on the Employees and Departments
tables. Suppose we want to retrieve all departments, even if no employees belong to a
specific department.
Query:
SELECT Employees.Name, Employees.DepartmentID, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Output:
Name DepartmentID DepartmentName

John 101 HR

Sarah 102 IT

Emma 103 Marketing

FULL OUTER JOIN

The FULL OUTER JOIN returns all rows when there is a match in either the left or right
table. If there is no match, the result will include NULL for the missing side of the table.
Essentially, it combines the results of both LEFT JOIN and RIGHT JOIN.

FULL OUTER JOIN

Syntax:
SELECT table1.column1, table1.column2, table2.column1, ...
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
Example: Let’s now use a FULL OUTER JOIN to get all employees and all departments,
regardless of whether an employee belongs to a department or a department has employees.
Query:
SELECT Employees.Name, Employees.DepartmentID, Departments.DepartmentName
FROM Employees
FULL JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Output:
Name DepartmentID DepartmentName

John 101 HR
Name DepartmentID DepartmentName

Sarah 102 IT

Michael – –

Emma 103 Marketing

In this example, Michael has no department so his department name is NULL.

When to Use SQL Outer Joins?
Outer joins are particularly useful in the following situations:
 Incomplete Data: When you need to include all records from one table even if there is no match in
the other table. For example, when retrieving all employees and their assigned departments, including
employees without departments.
 Data Integrity Issues: When working with datasets that might have missing or incomplete
relationships, outer joins can help ensure you don’t miss out on important records.
 Reporting and Analysis: When generating reports that need to show all records, regardless of
matching conditions, such as when summarizing employee data alongside department data.
 Data Merging: When merging datasets from different sources where some records might not have
corresponding matches.

SQL Outer Joins are used for combining data from multiple tables while including unmatched rows.
Whether you need to retrieve all records from one table or both tables, outer joins offer a flexible way to
manage and analyze relational data. By understanding LEFT OUTER JOIN, RIGHT OUTER JOIN, and
FULL OUTER JOIN, you can optimize your SQL queries for complex data retrieval scenarios.
These joins allow you to handle incomplete data and generate comprehensive reports that include all
necessary records. Mastering Outer Joins is an essential skill for any SQL practitioner, whether you’re
working on business intelligence, data analysis, or general database management.
Nested Subqueries Versus Correlated Subqueries
With a normal nested subquery, the inner SELECT query runs first and executes once,
returning values to be used by the main query. A correlated subquery, however, executes
once for each candidate row considered by the outer query. In other words, the inner query is
driven by the outer query.
1. Fetching Data Based on Row-Specific Conditions
Correlated subqueries are often used when you need to filter data based on a condition that
involves comparing values from the outer query.
Example: Fetch Employees Who Earn More Than Their Department's Average Salary
SELECT last_name, salary, department_id
FROM employees outer
WHERE salary >
(SELECT AVG(salary)
FROM employees
WHERE department_id =
outer.department_id group by department_id);
Explanation:
In this example, the subquery calculates the average salary for each department, and for each
employee, the outer query checks if their salary is greater than the average for their
department.
2. Using Correlated Subqueries with UPDATE
Correlated subqueries can also be used with UPDATE statements to modify data based on
related information from another table.
Example: Update Employee Salary Based on Department Average
UPDATE employees e
SET salary = (SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id)
WHERE department_id = 101;
Explanation:
This query updates the salaries of employees in department 101 based on the average salary
for that department. The subquery is evaluated for each row in the employees table.
3. Using Correlated Subqueries with DELETE
We can use a correlated subquery within a DELETE statement to remove rows from one
table based on conditions in another table.
Example: Delete Employees Who Do Not Belong to Department 101
DELETE FROM table1 alias1
WHERE column1 operator
(SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
Explanation:
This query deletes employees who do not belong to the 'HR' department by using a correlated
subquery in the NOT EXISTS clause.
4. Using EXISTS with Correlated Subqueries
The EXISTS operator is often used in correlated subqueries to test if a subquery returns any
rows. It returns TRUE if the subquery has at least one row.
Example: Find Employees Who Have at Least One Person Reporting to Them
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT ’X’
FROM employees
WHERE manager_id =
outer.employee_id);

Output
5. Using NOT EXISTS with Correlated Subqueries
The NOT EXISTS operator is used to check if a subquery does not return any rows. This is
useful for finding records that do not match specific criteria.
Example: Find Departments Without Any Employees
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT ’X’
FROM employees
WHERE department_id
= d.department_id);
Output

Explanation:
This query retrieves departments that do not have any employees assigned to them.
The NOT EXISTS clause ensures that only those departments without employees are
selected.

The SQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate
functions.

HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:

CustomerID CustomerName ContactName Address City

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin

2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D

4 Around the Horn Thomas Hardy 120 Hanover Sq. London

5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå

SQL HAVING Examples

The following SQL statement 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;

And a selection from the "Employees" table:

EmployeeID LastName FirstName BirthDate Photo Notes

1 Davolio Nancy 1968-12-08 EmpID1.pic Education incl

 2 Fuller Andrew 1952-02-19 EmpID2.pic Andrew receiv

3 Leverling Janet 1963-08-30 EmpID3.pic Janet has a BS

SQL | ALL and ANY


In SQL, the ALL and ANY operators are logical operators used to compare a value with a
set of values returned by a subquery. These operators provide powerful ways to filter
results based on a range of conditions.
In this article, we will explore ALL and ANY in SQL, their differences, and how to use
them effectively to boost your query performance and efficiency.
What is the SQL ALL Operator?
The ALL operator is used to compare a value with all the values returned by a subquery.
The condition will be evaluated to TRUE if the value meets the specified condition for
every value in the result set of the subquery.
 The ALL must be preceded by comparison operators and evaluates true if all of the
subqueries values meet the condition.
 ALL is used with SELECT, WHERE, and HAVING statements.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name comparison_operator ALL
(SELECT column_name
FROM table_name
WHERE condition(s));
 comparison_operator: This is the comparison operator that can be one of =, >, <, >=,
<=, <>, etc.
 subquery: A query that returns the set of values to be compared with the column in the
outer query.
How to Use SQL ALL with SELECT, WHERE, and HAVING
The ALL operator can be used in conjunction with SELECT, WHERE,
and HAVING statements to refine your data filtering.
lets Consider the following Products Table and OrderDetails Table for explaining the
examples.
Products Table
 Queries
Example 1 : Retrieve all product names from the Products table.
Query:
SELECT ALL ProductName
FROM Products
WHERE TRUE;

Output:
This query retrieves all product names from the Products table because TRUE always
evaluates as true for every row.
Example 2: Retrieve product names if all records in the OrderDetails table
have a quantity of 6 or 2.
SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID
FROM OrderDetails
WHERE Quantity = 6 OR Quantity = 2);

Output:
This query ensures that the product names returned have ALL quantities of 6 or 2 in the
OrderDetails table.
Example 3 : Find the OrderIDs where the maximum quantity in the order
exceeds the average quantity of all orders.
SELECT OrderID
FROM OrderDetails
GROUP BY OrderID
HAVING MAX(Quantity) > ALL (SELECT AVG(Quantity)
FROM OrderDetails
GROUP BY OrderID);

Output:
ANY
This query filters out OrderIDs where the maximum quantity is greater than the average
quantity of the orders.
What is the SQL ANY Operator?
ANY compares a value to each value in a list or results from a query and evaluates to true
if the result of an inner query contains at least one row.
 ANY return true if any of the subqueries values meet the condition.
 ANY must be preceded by comparison operators.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name comparison_operator ANY
(SELECT column_name
FROM table_name
WHERE condition(s));
How to Use SQL ANY with SELECT, WHERE, and HAVING
Example 1 : Find distinct category IDs of products that appear in the
OrderDetails table.
Query:
SELECT DISTINCT CategoryID
FROM Products
WHERE ProductID = ANY (SELECT ProductID
FROM OrderDetails);

Output:
This query finds the distinct CategoryIDs of products that exist in the OrderDetails table.
Example 2 : Find product names with a quantity of 9 in the OrderDetails
table.
Query:
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID
FROM OrderDetails
WHERE Quantity = 9);
Output:
 product names

This query retrieves product names where at least one record in the OrderDetails table has
a quantity of 9.
Differences Between SQL ALL and ANY
 ALL requires that the condition be true for every value in the subquery result, while
ANY only needs the condition to be true for at least one value in the subquery.
 ALL is used when you want to compare a value against all values in the subquery, while
ANY is useful when you want to compare a value against any one of the values.

The ALL and ANY operators in SQL are essential for advanced filtering based on dynamic
conditions. The key differences lie in how they evaluate the conditions across the results of
the subquery. ALL requires the condition to be true for all values in the subquery, while
ANY only needs the condition to be true for one value. By mastering these operators, you
can write more efficient and flexible SQL queries.

SQL FUNCTION:

SQL Functions are built-in programs that are used to perform different operations on the
database.
There are two types of functions in SQL:
 Aggregate Functions
 Scalar Functions
SQL Aggregate Functions
SQL Aggregate Functions operate on a data group and return a singular output. They are
mostly used with the GROUP BY clause to summarize data.
Some common Aggregate functions with Syntax and description are shown in the table
below.
Aggregate
Function Description Syntax

SELECT AVG(column_name) FROM

Calculates the average value
AVG() table_name;

SELECT COUNT(column_name)
Counts the number of rows
COUNT() FROM table_name

Returns the first value in an ordered SELECT FIRST(column_name) FROM

FIRST() set of values table_name;
 Aggregate
Function Description Syntax

Returns the last value in an ordered SELECT LAST(column_name) FROM

LAST() set of values table_name;

Retrieves the maximum value from SELECT MAX(column_name) FROM

MAX() a column table_name;

Retrieves the minimum value from SELECT MIN(column_name) FROM

MIN() a column table_name;

Calculates the total sum of values SELECT SUM(column_name) FROM

SUM() in a numeric column table_name;

SQL Scalar functions

SQL Scalar Functions are built-in functions that operate on a single value and return a single value.
Scalar functions in SQL helps in efficient data manipulation and simplification of complex calculations
in SQL queries.
Scalar
function Description Syntax

SELECT UCASE(column_name) FROM

Converts a string to uppercase
UCASE() table_name;

SELECT LCASE(column_name) FROM

Converts a string to lowercase
LCASE() table_name;

SELECT MID(column_name, start, length)

Extracts a substring from a string
MID() FROM table_name;

SELECT LEN(column_name) FROM

Returns the length of a string
LEN() table_name;

Rounds a number to a specified SELECT ROUND(column_name, decimals)

ROUND() number of decimals FROM table_name;

NOW() Returns the current date and time SELECT NOW();

 Scalar
function Description Syntax

Formats a value with the specified SELECT FORMAT(column_name, format)

FORMAT() format FROM table_name;

SQL Functions Examples

Let's look at some examples of SQL Functions. We will cover examples of SQL aggregate functions
and scalar functions.
We will perform queries on the given SQL table:

Aggregate Functions Examples

Let's look at the examples of each aggregate function in SQL.
AVG() Function Example
Computing average marks of students.
Query:
SELECT AVG(MARKS) AS AvgMarks FROM Students;
Output:
AvgMarks

80

COUNT() Function Example

Computing total number of students.
Query:
SELECT COUNT(*) AS NumStudents FROM Students;
Output:
NumStudents

FIRST() Function Example

Fetching marks of first student from the Students table.
Query:
SELECT FIRST(MARKS) AS MarksFirst FROM Students;
Output:
MarksFirst

90

LAST() Function Example

Fetching marks of last student from the Students table.
Query:
SELECT LAST(MARKS) AS MarksLast FROM Students;
Output:
MarksLast

85

MAX() Function Example

Fetching maximum marks among students from the Students table.
Query:
SELECT MAX(MARKS) AS MaxMarks FROM Students;
Output:
MaxMarks

95

MIN() Function Example

Fetching minimum marks among students from the Students table.
Query:
SELECT MIN(MARKS) AS MinMarks FROM Students;
Output:
MinMarks

50

SUM() Function Example

Fetching summation of total marks among students from the Students table.
Query:
SELECT SUM(MARKS) AS TotalMarks FROM Students;
Output:
TotalMarks

400
Scalar Functions Examples
Let's look at some examples of each Scalar Function in SQL.
UCASE() Function Example
Converting names of students from the table Students to uppercase.
Query:
SELECT UCASE(NAME) FROM Students;
Output:
NAME

HARSH

SURESH

PRATIK

DHANRAJ

RAM

LCASE() Function Example

Converting names of students from the table Students to lowercase.
Query:
SELECT LCASE(NAME) FROM Students;
Output:
NAME

harsh

suresh

pratik

dhanraj

ram

MID() Function Example

Fetching first four characters of names of students from the Students table.
Query:
SELECT MID(NAME,1,4) FROM Students;
Output:
NAME

HARS

SURE

PRAT

DHAN

RAM

LEN() Function Example

Fetching length of names of students from Students table.
Query:
SELECT LENGTH(NAME) FROM Students;
Output:
NAME

ROUND() Function Example

Fetching maximum marks among students from the Students table.
Query:
SELECT ROUND(MARKS,0) FROM Students;
Output:
MARKS
 MARKS

90

50

80

95

85

NOW() Function Example

Fetching current system time.
Query:
SELECT NAME, NOW() AS DateTime FROM Students;
Output:
FORMAT() Function Example
Formatting current date as 'YYYY-MM-DD'.
Query:
SELECT NAME, FORMAT(Now(),'YYYY-MM-DD') AS Date FROM Students;
Output:
NAME Date

HARSH 2017-01-13

SURESH 2017-01-13

PRATIK 2017-01-13

DHANRAJ 2017-01-13

RAM 2017-01-13

MySQL Date and Time Functions


Handling date and time data in MySQL is essential for many database operations, especially when it
comes to handling timestamps, scheduling tasks, or generating time-based. MySQL provides a variety
of date and time functions that help users work with date values, perform calculations, and format them
as needed.
These functions allow developers to perform calculations, extract specific parts of a date, or even
format the output for better readability. In this article, we will explore the most commonly used
MySQL date functions, explain their syntax, provide examples, and walk you through how to
effectively use them

Understanding MySQL Date and Time Data Types

When working with dates in MySQL, it can be tricky for beginners, especially because the format of
the date in the database must match the format of the input data during insertion. In many cases, instead
of just using a simple date, we may need to store both a date and time, depending on the use case. This
is where the DATETIME and TIMESTAMP data types come into play.
MySQL provides the following data types for storing date and time values:
1. DATE:
 Format: YYYY-MM-DD
 Used for storing only the date (year, month, and day), without any time component.
2. DATETIME:
 Format: YYYY-MM-DD HH:MM:SS
 Stores both the date and the time, making it useful when you need to capture specific times along
with the date.
3. TIMESTAMP:
 Format: YYYY-MM-DD HH:MM:SS
 Similar to DATETIME, but TIMESTAMP also includes time zone support. It automatically updates
to the current timestamp when a record is modified, making it ideal for tracking changes to records
over time.
4. YEAR:
 Format: YYYY or YY
 Used for storing just the year, which can be useful for applications where only the year is needed,
such as tracking birth years or fiscal years.
MySQL Date and Time Functions
Now, let's dive into the MySQL date functions that you can use to manipulate and query date and time
data effectively.
1. NOW()- Get Current Date and Time
The NOW() function retrieves the current date and time in YYYY-MM-DD HH:MI:SS format.
Query:
SELECT NOW();
Output:

2. CURDATE() - Get Current Date Only

If we only need the current date without the time portion, we can use CURDATE(), which returns the
date in YYYY-MM-DD format.
Query:
SELECT CURDATE();
Output:
3. CURTIME() - Get Current Time Only
The CURTIME() function returns the current time in HH:MI:SS format, excluding the date.
Query:
SELECT CURTIME();
Output:

4. DATE() - Extract the Date Part from a DATETIME Value

The DATE() function extracts only the date part from a DATETIME or TIMESTAMP value,
discarding the time.
Example: For a table called users:
Id Name BirthTime

4120 Pratik 1996-09-26 16:44:15.581

Query:
SELECT Name, DATE(BirthTime)
AS BirthDate FROM Test;
Output:
Name BirthDate

Pratik 1996-09-26

5. EXTRACT() - Extract Specific Date Parts

EXTRACT() is used to extract specific parts of a date, such as the day, month, or year. This function
can be especially helpful when analyzing or comparing different parts of a date. Several units can be
considered but only some are used such as MICROSECOND, SECOND, MINUTE, HOUR, DAY,
WEEK, MONTH, QUARTER, YEAR, etc. And 'date' is a valid date expression.
Syntax:
EXTRACT(unit FROM date);
Query to Extract Day:
SELECT Name, Extract(DAY FROM
BirthTime) AS BirthDay FROM Test;
Output:
Name Birthday

Pratik 26

Query to Extract Year:

SELECT Name, Extract(YEAR FROM BirthTime)
AS BirthYear FROM Test;
Output:
Name BirthYear

Pratik 1996

Query to Extract Seconds:

SELECT Name, Extract(SECOND FROM
BirthTime) AS BirthSecond FROM Test;
Output:
Name BirthSecond

Pratik 581

6. DATE_ADD() - Add Intervals to a Date

The DATE_ADD() function allows you to add time intervals (e.g., days, months, years) to a date
or DATETIME value.
Syntax:
DATE_ADD(date, INTERVAL expr type);
7. DATEDIFF() – Find the Difference Between Two Dates
This Function returns the number of days between two dates.
Syntax:
DATEDIFF(interval,date1, date2);
interval - minute/hour/month/year,etc
date1 & date2- date/time expression
Query to Find the Difference Between Two Dates
SELECT DATEDIFF(day, '2017-01-13', '2017-01-03') AS DateDiff;
Output:
DateDiff

8. DATE_FORMAT() - Foramt Date and Time

DATE_FORMAT() allows us to format a DATE, DATETIME, or TIMESTAMP value into a custom
format using placeholders.
Syntax:
DATE_FORMAT(date,format);
the date is a valid date and the format specifies the output format for the date/time. The formats that can
be used are:
 %a-Abbreviated weekday name (Sun-Sat)
 %b-Abbreviated month name (Jan-Dec)
 %c-Month, numeric (0-12)
 %D-Day of month with English suffix (0th, 1st, 2nd, 3rd)
 %d-Day of the month, numeric (00-31)
 %e-Day of the month, numeric (0-31)
 %f-Microseconds (000000-999999)
 %H-Hour (00-23)
 %h-Hour (01-12)
 %I-Hour (01-12)
 %i-Minutes, numeric (00-59)
 %j-Day of the year (001-366)
 %k-Hour (0-23)
 %l-Hour (1-12)
 %M-Month name (January-December)
 %m-Month, numeric (00-12)
 %p-AM or PM
 %r-Time, 12-hour (hh:mm: ss followed by AM or PM)
 %S-Seconds (00-59)
 %s-Seconds (00-59)
 %T-Time, 24-hour (hh:mm: ss)
 %U-Week (00-53) where Sunday is the first day of the week
 %u-Week (00-53) where Monday is the first day of the week
 %V-Week (01-53) where Sunday is the first day of the week, used with %X
 %v-Week (01-53) where Monday is the first day of the week, used with %x
 %W-Weekday name (Sunday-Saturday)
 %w-Day of the week (0=Sunday, 6=Saturday)
 %X-Year for the week where Sunday is the first day of the week, four digits, used with %V
 %x-Year for the week where Monday is the first day of the week, four digits, used with %v
 %Y-Year, numeric, four digits
 %y-Year, numeric, two digits
Query to Format a Date
SELECT DATE_FORMAT('2025-04-10 12:34:56', '%W, %M %d, %Y') AS formatted_date;
Output:
Formatted_date

Thursday, April 10, 2025

STRING FUNCTION:

SQL string functions are used primarily for string manipulation. The following table
details the important string functions −

Sr.No. Function & Description

ASCII()
1
Returns numeric value of left-most character

CHAR()
2
Returns the character for each integer passed
 CHARINDEX()
3
Returns the position of a substring within the given string.

CONCAT_WS()
4
Returns concatenate with separator

CONCAT()
5
Returns concatenated string

DIFFERENCE()
6
returns an integer value measuring the difference between the SOUNDEX() values of two different
expressions(strings).

ESCAPE()
7
Returns a text with escaped characters.

FORMAT()
8
Returns the formatted string.

LEFT()
9
Returns the extracting string.

LEN()
10
Returns the length of the given string.

LOWER()
11
Returns returns the lower case of the given string.

LTRIM()
12
Returns a string after removing all the white spaces and characters from the string found on the left side.

NCHAR()
13
Returns the Unicode character based on the number code.

PATINDEX()
14
Returns the position of a pattern in a string.

QUOTENAME()
15
Returns a string with a delimiter

REPLACE()
16
Returns a new string by replacing all the occurrences of the particular part of a string (substring) with a
specified string.
 REPLICATE()
17
Returns the repeated value.

REVERSE()
18
Returns a reversed string.

RIGHT()
19
Returns the rightmost characters from the actual(current) string.

RTRIM()
20
Returns a string after removing all trailing blanks.

SOUNDEX()
21
Returns the Soundex string.

SPACE()
22
returns a string consisting of N number of space characters.

STR()
23
Returns a number as string.

STRING_AGG()
24
Concatenates the values of string expressions and places separator values between them.

STRING_SPLIT()
25
Splits a string into rows of substrings.

STUFF()
26
Returns a new string by inserting the second expression at the specified deleted place.

SUBSTRING()
27
Returns the part of the character.

TRANSLATE()
28
Returns a string from the first argument.

TRIM()
29
Returns a trimmed string.

UNICODE()
30
Returns an integer value of the first character.

UPPER()
31
returns a string that has all the characters in upper case.
.No. Function & Description

ABS()
1
Returns the absolute value of numeric expression.

ACOS()
2
Returns the arccosine of numeric expression. Returns NULL if the value is not in the range -1 to 1.

ASIN()
3
Returns the arcsine of numeric expression. Returns NULL if value is not in the range -1 to 1

ATAN()
4
Returns the arctangent of numeric expression.

ATN2()
5
Returns the arctangent of the two variables passed to it.

CEILING()
6
Returns the smallest (closest to negative infinity) integer value that is greater than or equal to this value.

COS()
7
Returns the trigonometric cosine of the given value.

COT()
8
Returns the trigonometric cotangent of the given value.

DEGREES()
9
Returns numeric expression converted from radians to degrees.

EXP()
10
Returns the base of the natural logarithm (e) raised to the power of passed numeric expression.

FLOOR()
11
Returns the largest integer value that is not greater than passed numeric expression.
 LOG()
12
Returns the natural logarithm of the passed numeric expression.

LOG10()
13
Returns the base-10 logarithm of the passed numeric expression.

PI()
14
Returns the value of pi

POWER()
15
Returns the value of one expression raised to the power of another expression

RADIANS()
16
Returns the value of passed expression converted from degrees to radians.

RAND()
17
Returns the random value between 0 and 1.

ROUND()
18 Returns numeric expression rounded to an integer. Can be used to round an expression to a number of
decimal points

SIGN()
19
Returns the sign of a number, indicating whether it is positive, negative, or zero.

SIN()
20
Returns the sine of numeric expression given in radians.

SQRT()
21
Returns the non-negative square root of numeric expression.

TAN()
22
Returns the tangent of numeric expression expressed in radians.

ONE MARK:
1. What does the UNION operator do in SQL?
a) Combines results and removes duplicates
b) Combines results with duplicates
c) Returns only distinct values from one table
d) Returns all null values
Answer: a

2. Which operator includes duplicates in the result?

a) UNION
b) INTERSECT
c) UNION ALL
d) MINUS
Answer: c

3. Which SQL operator returns common rows between two SELECT statements?
a) UNION
b) INTERSECT
c) MINUS
d) JOIN
Answer: b

4.Which SQL operator returns rows from the first query that are not in the second?
a) UNION
b) INTERSECT
c) MINUS
d) JOIN
Answer: c

5.What condition must be met to use set operators like UNION or INTERSECT?
a) Same number of columns
b) Same data types in corresponding columns
c) Both a and b
d) No conditions
Answer: c

6. Which join returns all possible combinations of rows from two tables?
a) Inner Join
b) Left Join
c) Cross Join
d) Natural Join
Answer: c
7. Which join matches columns with the same name in both tables automatically?
a) Cross Join
b) Inner Join
c) Natural Join
d) Left Join
Answer: c
8. The USING clause is used in which type of join?
a) Full Outer Join
b) Join with common column name
c) Cross Join
d) Join without condition
Answer: b
9. Which join condition specifies the exact columns to join on?
a) USING clause
b) ON clause
c) WHERE clause
d) GROUP BY clause
Answer: b
 10. Which join includes all rows from the left table and matching rows from the right?
a) Inner Join
b) Left Outer Join
c) Right Outer Join
d) Full Outer Join
Answer: b
11. Which join includes only rows with matching values in both tables?
a) Outer Join
b) Inner Join
c) Cross Join
d) Left Join
Answer: b
12. Which join returns all records from both tables when there is a match?
a) Inner Join
b) Right Join
c) Full Outer Join
d) Self Join
Answer: c
13. Which clause is best used to specify multiple join conditions?
a) WHERE
b) USING
c) ON
d) GROUP BY
Answer: c

🔹 Subqueries and Correlated Queries

14. A subquery is enclosed in which symbols?

a) {}
b) []
c) ()
d) <>
Answer: c
15. Which clause can have a subquery?
a) WHERE
b) FROM
c) HAVING
d) All of the above
Answer: d
16. What is a correlated subquery?
a) Executes once
b) Independent of outer query
c) References column from outer query
d) Only used in FROM
Answer: c
17. Which keyword is used to test if a value matches any value in a list or subquery?
a) ALL
b) EXISTS
c) ANY
d) IN
Answer: d
18. What does the ANY operator do?
a) Compares with all values
b) Compares with the maximum
c) Compares with at least one value
d) Compares with null
Answer: c
19. What does the ALL operator do?
a) Compares with at least one value
 b) Compares with all values
c) Compares with none
d) Compares with null
Answer: b
20. Which clause filters the grouped results?
a) WHERE
b) HAVING
c) GROUP BY
d) ORDER BY
Answer: b
21. Can a subquery be used in the SELECT clause?
a) Yes
b) No
c) Only in MySQL
d) Only in WHERE clause
Answer: a
22. Which subquery type is executed first?
a) Outer query
b) Subquery
c) Correlated query
d) Inline view
Answer: b

🔹 SQL Functions

📆 Date & Time Functions

23. Which function returns the current system date and time?
a) GETDATE()
b) SYSDATE
c) NOW()
d) All of the above
Answer: d
24. Which function extracts year from a date?
a) EXTRACT(YEAR FROM date)
b) GETYEAR(date)
c) YEAROF(date)
d) YEAR()
Answer: a
25. Which function returns the difference between two dates?
a) DATEDIFF()
b) DATE_SUB()
c) DIFF()
d) TIME_DIFF()
Answer: a

📆 Numeric Functions

26. What does the ABS() function return?

a) Absolute value
b) Average value
c) Negative value
d) Square root
Answer: a
27. What is the result of ROUND(123.456, 2)?
a) 123.45
b) 123.456
c) 123.46
 d) 124
Answer: c
28. Which function returns the largest integer less than or equal to a number?
a) CEIL
b) FLOOR
c) ROUND
d) TRUNC
Answer: b
29. Which function returns the remainder of a division?
a) MOD()
b) REMAINDER()
c) DIVIDE()
d) SPLIT()
Answer: a

📆 String Functions

30. Which function converts text to lowercase?

a) LOWER()
b) LCASE()
c) TOLOWER()
d) a and b
Answer: d
31. Which function removes leading and trailing spaces?
a) SPACE()
b) TRIM()
c) SUBSTRING()
d) REPLACE()
Answer: b
32. Which function returns a portion of a string?
a) LEFT()
b) SUBSTRING()
c) MID()
d) All of the above
Answer: d
33. Which function finds the length of a string?
a) LENGTH()
b) LEN()
c) SIZE()
d) a and b
Answer: d
34. Which function replaces characters in a string?
a) REPLACE()
b) SUBSTITUTE()
c) OVERLAY()
d) INSERT()
Answer: a

📆 Conversion Functions

35. Which function converts string to number?

a) TO_NUMBER()
b) CAST()
c) CONVERT()
d) All of the above
Answer: d
36. Which function converts number to string?
a) TO_CHAR()
 b) TO_STRING()
c) CAST()
d) a and c
Answer: d
37. CAST(100 AS VARCHAR) returns:
a) Integer
b) Float
c) '100'
d) Error
Answer: c
38. What is the result of CONVERT(VARCHAR, GETDATE(), 101)?
a) Date in mm/dd/yyyy format
b) Time only
c) Year only
d) Error
Answer: a
39. What does TO_DATE() function do?
a) Converts number to date
b) Converts string to date
c) Converts date to string
d) Converts time to number
Answer: b
40. Which function ensures explicit data type conversion?
a) CONVERT
b) CAST
c) TO_CHAR
d) All of the above
Answer: d

🔹 Mixed Concept Questions

41. Which clause is required for grouping rows?

a) WHERE
b) HAVING
c) GROUP BY
d) ORDER BY
Answer: c
42. HAVING clause is used with which SQL function?
a) JOIN
b) AGGREGATE
c) INSERT
d) UPDATE
Answer: b
43. Which SQL command is used to retrieve data?
a) SELECT
b) INSERT
c) UPDATE
d) DELETE
Answer: a
44. Which clause is used to sort the result set?
a) ORDER BY
b) GROUP BY
c) SORT
d) WHERE
Answer: a
45. Which function returns the current timestamp?
a) CURRENT_TIMESTAMP
b) GETDATE
c) SYSDATE
 d) All of the above
Answer: d
46. What is the result of SELECT COUNT(*) FROM table?
a) Total rows
b) Null rows
c) Non-null rows
d) Column count
Answer: a
47. What is used to combine rows vertically in SQL?
a) UNION
b) JOIN
c) CROSS JOIN
d) INTERSECT
Answer: a
48. Which function finds the maximum value?
a) MAX()
b) GREATEST()
c) TOP()
d) HIGHEST()
Answer: a
49. Which join type is used to compare table to itself?
a) Self Join
b) Inner Join
c) Cross Join
d) Natural Join
Answer: a
50. Which of the following can be used in the FROM clause as a subquery?
a) Inline View
b) Temporary Table
c) Both
d) None
Answer: c

5MARK:

1.Explain the differences between UNION and UNION ALL with examples.?

2. Describe the different types of JOINs in SQL with examples.?

3. Write a query using a correlated subquery and explain how it works.?

4. Explain the use of SQL functions with examples from each category: Date-Time, Numeric, String, and
Conversion.?

5. Differentiate between WHERE, HAVING, IN, ANY, and ALL clauses in SQL with examples.?

10 MARK:

1.Compare and contrast the SQL set operators: UNION, UNION ALL, INTERSECT, and MINUS.
Provide examples.?

2. Explain all types of SQL JOINs: CROSS JOIN, NATURAL JOIN, JOIN USING, JOIN ON, and
OUTER JOIN with suitable examples.?

3. Write and explain various subqueries, including correlated subqueries. How do WHERE, IN, ANY,
ALL, HAVING, and FROM clauses use subqueries?
4. Describe various SQL functions: Date and Time, Numeric, String, and Conversion functions. Provide
practical examples.?

5. Design a complex SQL query involving JOINs, subqueries, set operators, and SQL functions. Explain
the logic of the query.?

UNIT-IV COMPLETED

UNIT-V

PL/SQL:A Programming Language: History – Fundamentals – Block Structure – Comments –

Data Types – Other Data Types – Variable Declaration – Assignment operation –Arithmetic
operators.Control Structures and Embedded SQL: Control Structures – Nested Blocks – SQL in
PL/SQL – Data Manipulation – Transaction Control statements. PL/SQL Cursors and
Exceptions: Cursors – Implicit Cursors, Explicit Cursors and Attributes – Cursor FOR loops –
SELECT…FOR UPDATE – WHERE CURRENT OF clause – Cursor with Parameters – Cursor
Variables – Exceptions – Types of Exceptions.

PL/SQL Introduction
What is PL/SQL?
PL/SQL is a combination of SQL and procedural programming constructs, enabling developers to
write code that performs database operations efficiently. It was developed by Oracle to enhance
SQL’s capabilities and allow for advanced error handling, complex calculations, and programmatic
control over database operations.
PL/SQL allows developers to:
 Execute SQL queries and DML commands inside procedural blocks.
 Define variables and perform complex calculations.
 Create reusable program units, such as procedures, functions, and triggers.
 Handle exceptions, ensuring the program runs smoothly even when errors occur
Key Features of PL/SQL
PL/SQL brings the benefits of procedural programming to the relational database world. Some of the
most important features of PL/SQL include:
1. Block Structure: PL/SQL can execute a number of queries in one block using single command.
2. Procedural Constructs: One can create a PL/SQL unit such as procedures, functions, packages,
triggers, and types, which are stored in the database for reuse by applications.
3. Error Handling: PL/SQL provides a feature to handle the exception which occurs in PL/SQL block
known as exception handling block.
4. Reusable Code: Create stored procedures, functions, triggers, and packages, which can be executed
repeatedly.
5. Performance: Reduces network traffic by executing multiple SQL statements within a single block
Differences Between SQL and PL/SQL
 SQL PL/SQL
Feature

PL/SQL is a block of codes that used to

SQL is a single query that is used to
write the entire program blocks/
Purpose perform DML and DDL operations.
procedure/ function, etc.

It is declarative, that defines what needs

PL/SQL is procedural that defines how
to be done, rather than how things need
Nature the things needs to be done.
to be done.

Executes single statement. Executes block of code

Execution

Data retrieval, manipulation and

definition( eg. SELECT, INSERT, Mainly used to create an application.
Use Case
UPDATE)

SQL Statements combined with

SQL statements only
Syntax procedural logic

Data Performs actions directly on the Can contain SQL inside its blocks and is
Handling database. used for more control over data handling

Structure of PL/SQL Block

PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural
language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs
are made up of blocks, which can be nested within each other.

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

BEGIN
 executable statements

EXCEPTIONS
exception handling statements

END;
PL/SQL code is written in blocks, which consist of three main sections:
 Declare section starts with DECLARE keyword in which variables, constants, records as cursors
can be declared which stores data temporarily. It basically consists definition of PL/SQL identifiers.
This part of the code is optional.
 Execution section starts with BEGIN and ends with END keyword.This is a mandatory section and
here the program logic is written to perform any task like loops and conditional statements. It
supports all DML commands, DDL commands and SQL*PLUS built-in functions as well.
 Exception section starts with EXCEPTION keyword.This section is optional which contains
statements that are executed when a run-time error occurs. Any exceptions can be handled in this
section.
PL/SQL Identifiers
In PL/SQL, identifiers are names used to represent various program elements like variables, constants,
procedures, cursors, triggers etc. These identifiers allow you to store, manipulate, and access data
throughout your PL/SQL code.
1. Variables in PL/SQL
Like several other programming languages, variables in PL/SQL must be declared prior to its use. A
variable is like a container that holds data during program execution. Each variable must have a valid
name and a specific data type.
Syntax for declaration of variables:
variable_name datatype [NOT NULL := value ];
 variable_name: The name of the variable.
 datatype: The data type of the variable (e.g., INTEGER, VARCHAR2).
 NOT NULL: This optional constraint means the variable cannot be left empty.
 := value: This optional assignment assigns an initial value to the variable.
Example: Declaring Variables
SQL> SET SERVEROUTPUT ON;

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

BEGIN
null;
END;
/
Output:
PL/SQL procedure successfully completed.
Explanation:
 SET SERVEROUTPUT ON: It is used to display the buffer used by the dbms_output.
 var1 INTEGER : It is the declaration of variable, named var1 which is of integer type. There are
many other data types that can be used like float, int, real, smallint, long etc. It also supports
variables used in SQL as well like NUMBER(prec, scale), varchar, varchar2 etc.
 Slash (/) after END;: The slash (/) tells the SQL*Plus to execute the block.
 Assignment operator (:=) : It is used to assign a value to a variable.
2. Displaying Output in PL/SQL
The outputs are displayed by using DBMS_OUTPUT which is a built-in package that enables the user
to display output, debugging information, and send messages from PL/SQL blocks, subprograms,
packages, and triggers. Let us see an example to see how to display a message using PL/SQL :
Example: Displaying Output
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
var varchar2(40) := 'I love GeeksForGeeks' ;
 BEGIN
dbms_output.put_line(var);

END;
/
Output:
I love GeeksForGeeks

PL/SQL procedure successfully completed.

Explanation:
dbms_output.put_line : This command is used to direct the PL/SQL output to a screen.
3. Comments in PL/SQL
Like in many other programming languages, in PL/SQL also, comments can be put within the code
which has no effect in the code. There are two syntaxes to create comments in PL/SQL :
 Single Line Comment: To create a single line comment , the symbol - - is used.
 Multi Line Comment: To create comments that span over several lines, the symbol /* and */ is
used.
Example: Adding Comments
-- This is a single-line comment

/*
This is a multi-line comment
that spans over multiple lines.
*/
4. Taking input from users
In PL/SQL we can take input from the user and store it in a variable using substitution variables.
These variables are preceded by an & symbol. Let us see an example to show how to take input from
users in PL/SQL:
Example: Taking Input from Users
SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

-- taking input for variable a

a number := &amp;a;

-- taking input for variable b

b varchar2(30) := &amp;b;

BEGIN
null;

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

PL/SQL procedure successfully completed.

BLOCK STRUCTURE:
In PL/SQL, All statements are classified into units that is called Blocks. PL/SQL blocks can include
variables, SQL statements, loops, constants, conditional statements and exception handling. Blocks can
also build a function or a procedure or a package.

The Declaration section: Code block start with a declaration section, in which memory variables,
constants, cursors and other oracle objects can be declared and if required initialized.
The Begin section: Consist of set of SQL and PL/SQL statements, which describe processes that have
to be applied to table data. Actual data manipulation, retrieval, looping and branching construct s are
specified in this section.
The Exception section: This section deals with handling errors that arise during execution data
manipulation statements, which make up PL/SQL code block. Errors can arise due to syntax, logic
and/or validation rule.
The End section: This marks the end of a PL/SQL block.

Introduction to PL/SQL Data Types

PL/SQL uses many data types corresponding to different types of data including scalar and composite
data types and reference data types along with large objects (LOBs).
These data types are used to declare the sharable variables and constants in the packages, parameters
for subroutines, and return values of function and procedure subroutines
in PL/SQL programs. PL/SQL data types are very much similar to SQL data types and have extra
characteristics attached to PL/SQL.
Scalar Data Types in PL/SQL
Scalar data types are basic types that store only one value at a time.like numbers, characters, or even
logical values each representing an individual value. The scalar data types are categorized into:
 Numeric Types: It Stores any integer value along with a fractional entity whatever large it is or as
per the requirement of the program.
 Character Types: Nodes represent strings of text and These act as structures of text whereby an
individual string is represented by a node.
 Boolean Types: It Contains ‘true’ or ‘false’ values.
 Datetime Types: It is Used to represent date and time values which are of typical usage in
computer systems.
Subtypes are defined based on the base scalar types and are formed by placing additional constraints
upon values that can be assigned.
Numeric Data Types and Subtypes in PL/SQL
Numeric data types store numbers, both integers and real numbers, and allow developers to perform
arithmetic operations. The main numeric types include:
 NUMBER: A highly flexible type that can store fixed-point or floating-point numbers. It has
precision and scale parameters. For example, NUMBER(5, 2) can store up to 5 digits, with 2 of
them after the decimal point.
 BINARY_INTEGER/PLS_INTEGER: These types are used for signed integers and are often
faster than the generic NUMBER type because they use machine-dependent formats.
 FLOAT: It is a subtype of NUMBER designed for storing floating-point numbers. You can specify
an optional precision such as FLOAT(10) which allows for storing a number with up to 10 digits of
precision
Subtypes of Numeric Data Types
 NATURAL: A subtype of BINARY_INTEGER representing non-negative integers (0 or greater).
 NATURALN: It is Similar to NATURAL but cannot be null.
 POSITIVE: A subtype of BINARY_INTEGER representing positive integers (greater than 0).
 POSITIVEN: It is Similar to POSITIVE but cannot be null.
Character Data Types and Subtypes in PL/SQL
Character data types are designed to store any text, numbers or symbols in the form of alphanumeric.
They are used specifically for string manipulation and are a vital part of PL/SQL.
 CHAR: It Handles variable-length binary data and fixed-length character strings. If the string is
less than the defined length, then the rest is filled up with spaces. For instance, CHAR(10) would
store string as CHAR data type that has a length of 10 characters regardless of the actual string
length.
 VARCHAR2: To store character strings of varying lengths. VARCHAR2 is slightly different
because it only allocates the required amount of space required to store the string. For
instance, VARCHAR2(10) data type can accommodate a string with as many as 10 characters.
 LONG: It Can store variable-length character strings of up to 2 gigabytes. However, it is
deprecated and it should be replaced with CLOB to 2 GB. However, it is deprecated and should be
avoided in favor of CLOB.
Subtypes of Character Data Types
 STRING: A subtype of VARCHAR2, used to represent variable-length strings.
 LONG VARCHAR: A deprecated subtype of VARCHAR2, previously used to store large strings.
PL/SQL Boolean Data Types
The Boolean data type is unique to PL/SQL, allowing you to store logical values and use them in
conditional expressions.
BOOLEAN: This type can have three possible values: TRUE, FALSE, or NULL. It is used in
conditional statements and logical comparisons. Notably, the BOOLEAN data type is unique to
PL/SQL and cannot be used in SQL statements directly.
PL/SQL Datetime and Interval Types
Datetime data types contain date and time and interval types contain the difference between two
datetime values. PL/SQL provides the following datetime and interval types:
 DATE: It stores date and time values. These are the year, month, day, hour, minute, and second as
a part of the Date type.
 TIMESTAMP: It is an extension of the DATE data type with the added feature of fractional
seconds.
 TIMESTAMP WITH TIME ZONE: Saves a TIMESTAMP, but with information about the time
zone in which it has been set.
 TIMESTAMP WITH LOCAL TIME ZONE: Standalone function that converts the
TIMESTAMP to the time zone of the current database session.
 INTERVAL YEAR TO MONTH: Saves the amount of time measured in years and months.
 INTERVAL DAY TO SECOND: Saves as the time duration in terms of days, hours, minutes, and
seconds.
PL/SQL Large Object (LOB) Data Types
LOB data types store large amounts of unstructured data, such as text, images, videos, and audio.
PL/SQL provides several LOB types:
 BLOB(Binary Large Object): Stores binary large objects, such as images or multimedia files.
 CLOB(Character Large Object): Stores large character data.
 NCLOB(National Character Large Object): Stores large character data using the national
character set.
 BFILE(Binary File): Stores a reference to a binary file stored outside of the database.
 LOB types can store up to 4 gigabytes of data, making them ideal for handling large and complex
datasets.
Important Points for Using Data Types in PL/SQL
 Choosing the correct data type (e.g., CHAR for fixed text, VARCHAR2 for variable text) optimizes
storage and improves readability.
 By using subtypes like NATURAL or POSITIVE, you can enforce constraints (such as no negative
values) at the data type level, improving code reliability.
 Use LOB types (like CLOB and BLOB) for large datasets to efficiently handle multimedia,
documents, or other large data objects without performance degradation.
 When working with timestamps, consider TIMESTAMP WITH TIME ZONE or TIMESTAMP
WITH LOCAL TIME ZONE to avoid time-related issues, especially if your application spans
multiple time zones.

How to Declare a Variable in PL/SQL?


Declaring variables in PL/SQL is a fundamental step towards building powerful and efficient database
applications. Variables act as placeholders for data which enable us to manipulate and store information
within our PL/SQL programs.
Here, we will explore various methods of declaring variables in PL/SQL, including syntax, examples, and
practical use cases. We will cover variable initialization, scope, and the use of variable attributes
like %TYPE and %ROWTYPE.
How to Declare PL/SQL Variables?
When writing PL/SQL code it is important to declare variables properly to store and manipulate data
effectively. Variables act as containers for values and enable various operations on the stored data.
Variables in PL/SQL are declared using the DECLARE keyword within an anonymous block or a named
program unit such as a procedure, function, or package.
Common Methods for Declaring Variables in PL/SQL
The below methods are used to declare a variable in PL/SQL are as follows:
Table of Content
 Using Declare Variables in PL/SQL
 Using Initializing Variables in PL/SQL
 Using Variable Scope in PL/SQL
 Using Variable Attributes
Let's understand each method one be one along with the Examples.
1. Using Declare Variables in PL/SQL
To declare a variable in PL/SQL, use the DECLARE keyword followed by the variable name and its data
type. Optionally, you can also assign an initial value to the variable using the ':=' operator.
Syntax:
DECLARE
variable_name datatype := initial_value;
here,
 variable_name: It is the name of the variable.
 datatype: It is the data type of the variable.
 := initial_value: It is an optional assignment of an initial value to the variable.
Example:
DECLARE
name VARCHAR2(20) := 'GeeksForGeeks';
BEGIN
DBMS_OUTPUT.PUT_LINE(name);
END;
Output:

Output
Explanation: In the above Query, We have declares a variable named "name" with a size
of 20 characters and initializes it with the value GeeksForGeeks then prints the value of the variable
using DBMS_OUTPUT.PUT_LINE.
2. Using Initializing Variables in PL/SQL
In this method Variables can be initialized in two ways either during declaration or later in the code.
a. Initializing during declaration
Variables can be assigned values when declared, as shown below:
Syntax:
DECLARE
my_variable NUMBER := value;
BEGIN
-- PL/SQL code
END;
Example:
DECLARE
name VARCHAR2(20) := 'GeeksForGeeks';
BEGIN
DBMS_OUTPUT.PUT_LINE(name);
END;
Output:

b. Initialization After Declaration

You can also assign a value to a variable later in the code using the := operator.
Syntax:
DECLARE
my_variable NUMBER;
BEGIN
my_variable := value;
END;
Example:
DECLARE
num1 NUMBER;
num2 NUMBER;
result NUMBER;
BEGIN
num1 := 5;
num2 := 3;
result := num1 + num2;
DBMS_OUTPUT.PUT_LINE('Sum: ' || result);
END;
Output:
3. Using Variable Scope in PL/SQL
Variable scope determines where a variable can be accessed within a program. In PL/SQL, variable scope
can be either local or global.
 Local Variables: Declared within a block or subprogram, accessible only inside that block or
subprogram.
 Global Variables: Declared in the outermost block and accessible by nested blocks.
Example:
DECLARE
global_var NUMBER; -- global variable
BEGIN
-- PL/SQL code using global_var
DECLARE
local_var NUMBER; -- local variable
BEGIN
-- PL/SQL code using local_var and global_var
END;
-- Here you can't access local_var
END;
Explanation: The global_var can be accessed throughout the entire program, while the local_var is only
accessible within the inner block
4. Using Variable Attributes (%TYPE and %ROWTYPE)
PL/SQL provides two powerful attributes, %TYPE and %ROWTYPE, which allow variables to inherit
data types from existing columns or entire rows.
 %TYPE: It defines a variable with the same data type as another variable or column.
 %ROWTYPE: It defines a record with the same structure as a table or cursor.
Example: The below example is demonstrating the use of %TYPE and %ROWTYPE attribute
Create a employees table and Insert some records into a employees table
-- Creating a employees table
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER
);
-- Inserting some records
INSERT INTO employees VALUES (1, 'John', 'Doe', 50000);
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 60000);
INSERT INTO employees VALUES (3, 'Bob', 'Johnson', 55000);
1. Using %TYPE Attribute
In this example, we have declared a variable salary_var using %TYPE to match the data type of
the salary column in the employees table then we have assigned a value to salary_var and displayed the
assigned value using DBMS_OUTPUT.PUT_LINE.
DECLARE
salary_var employees.salary%TYPE;
BEGIN
-- Assign a value to the variable
salary_var := 70000;

-- Display the assigned value

DBMS_OUTPUT.PUT_LINE('Assigned Salary: ' || salary_var);
END;
Output:

Explanation: In the above Query, We have declares a variable salary_var with the same data type as
the salary column in the employees table, assigns a value of 70000 to it, and then prints the assigned
salary using DBMS_OUTPUT.PUT_LINE.
2. Using %ROWTYPE Attribute
In this example, We have declared a record variable employee_record using %ROWTYPE to match the
structure of the employees table and fetched the data from the employees table into
the employee_record variable using a SELECT INTO statement then we have displayed the retrieved
data from the employee_record using DBMS_OUTPUT.PUT_LINE.
DECLARE
employee_record employees%ROWTYPE;
BEGIN
-- Fetch data from the table into the record variable
SELECT * INTO employee_record FROM employees WHERE employee_id = 2;

-- Display the retrieved data

DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_record.employee_id);
DBMS_OUTPUT.PUT_LINE('First Name: ' || employee_record.first_name);
 DBMS_OUTPUT.PUT_LINE('Last Name: ' || employee_record.last_name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || employee_record.salary);
END;
Output:

Explanation: In the above Query, We have declares a record variable employee_record that matches the
structure of the employees table. It then fetches the data for the employee with employee_id 2 into
the employee_record variable using a SELECT INTO statement and prints the retrieved data
using DBMS_OUTPUT.PUT_LINE.

PL/SQL Operator

The PL/SQL language offers various operators for data manipulation and logical processing. There are
several types of these operators which include arithmetic operators, relational operators,
comparison operators, and logical operators. In this guide, we will learn about the various PL/SQL
operators with the help of examples and so on.
PL/SQL Operators
PL/SQL operators are used to operate on variables, constants, or expressions in PL/SQL blocks.
They enable us to process data through arithmetic, relational or logical operations that is calculations,
comparisons, or logical decisions. Operators are essential in PL/SQL for performing calculations and
making decisions, from simple arithmetic to complex logic.
Types of PL/SQL Operators
PL/SQL offers several types of operators, each serving a unique purpose:
1. Arithmetic Operators
Arithmetic operators in PL/SQL are used to perform basic mathematical operations such as addition,
subtraction, multiplication, division, and modulus.
Operator Description Example

+ Addition 10+5

- Subtraction 10-5

* Multiplication 10*5

/ Division 10/5

MOD Modulus 1MOD(10,3)

Example:
DECLARE
num1 NUMBER := 20;
num2 NUMBER := 4;
result NUMBER;
BEGIN
result := num1 + num2;
DBMS_OUTPUT.PUT_LINE('Addition Result: ' || result);
result := num1 - num2;
DBMS_OUTPUT.PUT_LINE('Subtraction Result: ' || result);
result := num1 * num2;
DBMS_OUTPUT.PUT_LINE('Multiplication Result: ' || result);
result := num1 / num2;
DBMS_OUTPUT.PUT_LINE('Division Result: ' || result);
result := MOD(num1, num2);
DBMS_OUTPUT.PUT_LINE('Modulus Result: ' || result);
END;
2. Relational Operators
Comparison operators compare two values and return a Boolean result (TRUE, FALSE, or NULL).
These operators are commonly used in conditional statements such as IF and LOOP.
Operator Description Example

= Equal to x=y

!= or <> Not Equal to x!=y or x<>y

> Greater than x>y

< Less than x<y

>= Greater than or equal to x>=y

<= Less than or equal to x<=y

Example:
DECLARE
age1 NUMBER := 25;
age2 NUMBER := 30;
BEGIN
IF age1 <> age2 THEN
DBMS_OUTPUT.PUT_LINE('Ages are not equal.');
END IF;
IF age1 < age2 THEN
DBMS_OUTPUT.PUT_LINE('Age1 is less than Age2.');
END IF;
END;
3. Logical Operators
Logical operators are used to combine or negate conditions, and they evaluate to a Boolean value.
These operators are often used in IF, CASE, and LOOP statements.
Operator Description Examples

AND Returns TRUE if both conditions are TRUE x > 5 AND y < 10

OR Returns TRUE if at least one condition is TRUE x > 5 OR y < 10

NOT Negates a condition NOT (x > 5)

Example:
DECLARE
score1 NUMBER := 80;
score2 NUMBER := 70;
BEGIN
IF score1 >= 75 AND score2 >= 75 THEN
DBMS_OUTPUT.PUT_LINE('Both scores are above average.');
ELSIF score1 >= 75 OR score2 >= 75 THEN
DBMS_OUTPUT.PUT_LINE('At least one score is above average.');
ELSE
DBMS_OUTPUT.PUT_LINE('Both scores are below average.');
END IF;
END;
4. Comparison Operators
These operators are used to compare values and return TRUE or FALSE based on the condition.
Operator Description Examples

BETWEEN Checks if a value is within a range x BETWEEN 10 AND 20

LIKE Matches a value against a pattern name LIKE 'A%' (names starting with 'A')

IN Checks if a value exists in a list x IN (1, 2, 3)

IS NULL Checks if a value is NULL x IS NULL

Example:
DECLARE
student_name VARCHAR2(50) := 'Alice';
student_age NUMBER := 19;
BEGIN
IF student_name LIKE 'A%' THEN
DBMS_OUTPUT.PUT_LINE('Student name starts with "A".');
END IF;

IF student_age BETWEEN 18 AND 22 THEN

DBMS_OUTPUT.PUT_LINE('Student is in college age range.');
 END IF;
END;

PL/SQL operators are powerful tools for building conditions, performing calculations, and
manipulating data effectively. Handling arithmetic operations, logical decisions, or complex
comparisons, understanding these operators is essential for efficient PL/SQL programming. Mastery of
these operators can significantly improve the robustness and flexibility of your database applications.
Control Structures are just a way to specify flow of control in programs. Any algorithm or program
can be more clear and understood if they use self-contained modules called as logic or control
structures. It basically analyzes and chooses in which direction a program flows based on certain
parameters or conditions. There are three basic types of logic, or flow of control, known as:

1. Sequence logic, or sequential flow

2. Selection logic, or conditional flow
3. Iteration logic, or repetitive flow
Let us see them in detail:

1. Sequential Logic (Sequential Flow) Sequential logic as the name suggests follows a serial or
sequential flow in which the flow depends on the series of instructions given to the computer.
Unless new instructions are given, the modules are executed in the obvious sequence. The
sequences may be given, by means of numbered steps explicitly. Also, implicitly follows the order
in which modules are written. Most of the processing, even some complex problems, will generally

follow this elementary flow pattern. Sequential Control flow

2. Selection Logic (Conditional Flow) Selection Logic simply involves a number of conditions or
parameters which decides one out of several written modules. The structures which use these type
of logic are known as Conditional Structures. These structures can be of three types:
 Single AlternativeThis structure has the form:
 If (condition) then:
 [Module A]
[End of If structure]
Implementation:

o C/C++ if statement with Examples

o Java if statement with Examples
 Double AlternativeThis structure has the form:
 If (Condition), then:
 [Module A]
 Else:
 [Module B]
 [End if structure]
Implementation:

o C/C++ if-else statement with Examples

o Java if-else statement with Examples
 Multiple AlternativesThis structure has the form:
 If (condition A), then:
 [Module A]
 Else if (condition B), then:
  [Module B]
 ..
 ..
 Else if (condition N), then:
 [Module N]
 [End If structure]
Implementation:

o C/C++ if-else if statement with Examples

o Java if-else if statement with Examples
In this way, the flow of the program depends on the set of conditions that are written. This can be
more understood by the following flow charts:

Double Alternative Control

Flow

3. Iteration Logic (Repetitive Flow) The Iteration logic employs a loop which involves a repeat
statement followed by a module known as the body of a loop. The two types of these structures are:
 Repeat-For Structure This structure has the form:
 Repeat for i = A to N by I:
 [Module]
 [End of loop]
 Here, A is the initial value, N is the end value and I is the increment. The loop ends when A>B.
K increases or decreases according to the positive and negative value of I respectively.

Repeat-For FlowImplementation:

o C/C++ for loop with Examples

o Java for loop with Examples
 Repeat-While Structure It also uses a condition to control the loop. This structure has the
form:
 Repeat while condition:
 [Module]
 [End of Loop]

PL/SQL Nested Table

Last Updated : 22 Oct, 2024


PL/SQL, Oracle's procedural extension of SQL, offers powerful capabilities for
managing and processing data within the database. One of its key features is
support for complex data structures, including collections like nested tables. A
nested table in PL/SQL is a dynamic, array-like data structure that allows you to
store multiple elements of the same data type in a single variable. Every PL/SQL
query contains BEGIN, and END keywords. We can nest blocks in PL/SQL. It
supports anonymous blocks and named blocks. Anonymous blocks are not
stored in the database while named blocks are stored.
Here, we’ll explore nested tables in PL/SQL. We’ll look at what they are, how they
work, and what they can be used for.
PL/SQL block Structure
A typical PL/SQL block has the following structure:
-- Declaration Section
DECLARE
-- Variable and nested table declarations
BEGIN
-- Execution Section
-- Statements to perform operations
EXCEPTION
-- Exception Handling Section
-- Optional: Handle any runtime errors
END;
What are PL/SQL Nested Tables?
Nested tables in PL/SQL are data structures that function like arrays, allowing you to store multiple
values in a single column. They are stored in a database. However, unlike traditional arrays, nested tables
in PL/SQL are dynamic i.e. the amount of memory utilized can increase or decrease as per use. They
allow you to work on variable amounts of data dynamically. They are used when an unknown number of
elements are to be stored. Elements of any datatype can be stored in a nested table.
PL/SQL nested tables are stored in the database and can hold elements of any data type. They are
particularly useful when dealing with complex data structures and dynamic datasets. Two main types of
nested tables exist:
 Bounded Nested Tables: Have a fixed size.
 Unbounded Nested Tables: Are dynamic and can grow or shrink based on usage
Syntax of PL/SQL nested tables
DECLARE
--DECLARE THE NESTED TABLE TYPE
TYPE nested_table_type IS TABLE OF data_type;
--Declare variable of the nested table type
variable nested_table_type;
BEGIN
--initialize the variable
variable := nested_table_type( element1,element2 .....elementn)
-- Perform the required operations
-- free up the allocated memory
variable : =NULL;
END;
 The nested table type is declared using the TYPE keyword.
 The variable is created using IS TABLE OF.
Working with PL/SQL Nested Tables
1. Declaring a Nested Table Variable
A nested table in oracle is declared in the declaration section like cursors or procedure. The datatype is
declared along with the name of the nested table.
TYPE nested_table_type IS TABLE OF data_type;
variable nested_table_type;
2. Initializing a Nested Table
When a variable is declared using the nested table type it is initialized to NULL. Constructor initializes
the value to the variable.
Syntax:
variable_of_nested_table := nested_table_type();
Arithmetic Operators in PL/SQL

In PL/SQL (Oracle's Procedural Language extension to SQL), arithmetic operators are used to
perform calculations in variable assignments, expressions, and control structures.

List of Arithmetic Operators in PL/SQL

Operator Description Example Result

+ Addition x := 10 + 5; 15

- Subtraction x := 10 - 5; 5

* Multiplication x := 10 * 5; 50

/ Division x := 10 / 5; 2.0

** Exponentiation x := 2 ** 3; 8

📌 PL/SQL uses ** for exponentiation (unlike standard SQL which doesn't support
exponentiation directly).

Example Program in PL/SQL

pl
CopyEdit
DECLARE
a NUMBER := 10;
b NUMBER := 5;
sum_result NUMBER;
diff_result NUMBER;
prod_result NUMBER;
div_result NUMBER;
exp_result NUMBER;
BEGIN
sum_result := a + b;
diff_result := a - b;
prod_result := a * b;
div_result := a / b;
exp_result := a ** b;

DBMS_OUTPUT.PUT_LINE('Addition: ' || sum_result);

DBMS_OUTPUT.PUT_LINE('Subtraction: ' || diff_result);
DBMS_OUTPUT.PUT_LINE('Multiplication: ' || prod_result);
DBMS_OUTPUT.PUT_LINE('Division: ' || div_result);
DBMS_OUTPUT.PUT_LINE('Exponentiation: ' || exp_result);
END;

Output
makefile
CopyEdit
Addition: 15
Subtraction: 5
Multiplication: 50
Division: 2
Exponentiation: 100000
Control Structures and Embedded SQL in PL/SQL

PL/SQL enhances SQL by adding control structures (like loops, conditionals) and allows
embedded SQL (using SQL commands inside PL/SQL code). This makes PL/SQL powerful for
creating procedures, triggers, and scripts.

1. Control Structures in PL/SQL

Control structures manage the flow of execution in PL/SQL blocks.

A. Conditional Statements

IF-THEN
plsql
CopyEdit
IF salary > 5000 THEN
bonus := 1000;
END IF;

IF-THEN-ELSE
plsql
CopyEdit
IF salary > 5000 THEN
bonus := 1000;
ELSE
bonus := 500;
END IF;

IF-THEN-ELSIF
plsql
CopyEdit
IF salary > 10000 THEN
bonus := 2000;
ELSIF salary > 5000 THEN
bonus := 1000;
ELSE
bonus := 500;
END IF;

B. Loops

Simple Loop
plsql
CopyEdit
LOOP
total := total + 1;
EXIT WHEN total = 10;
END LOOP;

WHILE Loop
plsql
CopyEdit
WHILE total < 10 LOOP
total := total + 1;
END LOOP;

FOR Loop
plsql
CopyEdit
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
END LOOP;

2. Embedded SQL in PL/SQL

Embedded SQL allows using SQL statements within PL/SQL blocks.

A. SELECT INTO (Single Row)

plsql
CopyEdit
DECLARE
emp_name VARCHAR2(50);
BEGIN
SELECT ename INTO emp_name
FROM emp
WHERE empno = 1001;

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);

END;

B. DML Statements (INSERT, UPDATE, DELETE)

INSERT
plsql
CopyEdit
BEGIN
INSERT INTO employees (empno, ename)
VALUES (1002, 'John');
END;

UPDATE
plsql
CopyEdit
BEGIN
UPDATE employees
SET salary = salary + 1000
WHERE empno = 1002;
END;

DELETE
plsql
CopyEdit
BEGIN
DELETE FROM employees
WHERE empno = 1002;
END;

C. Transaction Control (COMMIT, ROLLBACK, SAVEPOINT)

pl
CopyEdit
BEGIN
UPDATE employees SET salary = salary + 500 WHERE empno = 1001;
COMMIT;
END;
PL/SQL Control Structures – Nested Blocks

In PL/SQL, a nested block means placing one PL/SQL block inside another. This is useful for
modularizing your code, isolating variables, and handling errors locally.

Structure of a PL/SQL Block

plsql
CopyEdit
DECLARE
-- Declarations (optional)
BEGIN
-- Executable statements
EXCEPTION
-- Exception handling (optional)
END;

Nested Block Example

You can write a block within another block, just like this:

plsql
CopyEdit
DECLARE
outer_var NUMBER := 10;
BEGIN
DBMS_OUTPUT.PUT_LINE('Outer Block Start');

DECLARE
inner_var NUMBER := 5;
BEGIN
DBMS_OUTPUT.PUT_LINE('Inner Block');
DBMS_OUTPUT.PUT_LINE('Sum = ' || (outer_var + inner_var));
END;

DBMS_OUTPUT.PUT_LINE('Outer Block End');

END;

🔹 Output:
mathematica
CopyEdit
Outer Block Start
Inner Block
Sum = 15
Outer Block End

Key Points About Nested Blocks

Feature Description

Scope Inner blocks can access variables from outer blocks, but not vice versa.

Isolation Inner blocks can have variables with the same name (shadowing outer ones).
 Feature Description

Local Handling Each block can have its own EXCEPTION section.

Example with Exception Handling in Nested Blocks

plsql
CopyEdit
DECLARE
x NUMBER := 10;
BEGIN
DBMS_OUTPUT.PUT_LINE('Outer Block');

BEGIN
DBMS_OUTPUT.PUT_LINE('Inner Block');
x := x / 0; -- causes division by zero
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero in Inner Block');
END;

DBMS_OUTPUT.PUT_LINE('After Inner Block');

END;

🔹 Output:
mathematica
CopyEdit
Outer Block
Inner Block
Error: Division by zero in Inner Block
After Inner Block

Data Manipulation in PL/SQL

In PL/SQL, Data Manipulation Language (DML) statements such as INSERT, UPDATE,

DELETE, and SELECT INTO are used to modify or retrieve data from tables.

PL/SQL enhances SQL with procedural capabilities like variables, control structures, and error
handling.

1. INSERT Statement in PL/SQL

Used to add new rows to a table.

plsql
CopyEdit
BEGIN
INSERT INTO employees (empno, ename, salary)
VALUES (1001, 'John', 5000);
DBMS_OUTPUT.PUT_LINE('Record Inserted');
END;

2. UPDATE Statement in PL/SQL

Used to modify existing data in a table.

plsql
CopyEdit
BEGIN
UPDATE employees
SET salary = salary + 1000
WHERE empno = 1001;
DBMS_OUTPUT.PUT_LINE('Record Updated');
END;

3. DELETE Statement in PL/SQL

Used to remove rows from a table.

plsql
CopyEdit
BEGIN
DELETE FROM employees
WHERE empno = 1001;
DBMS_OUTPUT.PUT_LINE('Record Deleted');
END;

4. SELECT INTO Statement in PL/SQL

Used to retrieve data from a table and store it in a PL/SQL variable. It must return exactly one
row.

pl
CopyEdit
DECLARE
v_name employees.ename%TYPE;
v_salary employees.salary%TYPE;
BEGIN
SELECT ename, salary INTO v_name, v_salary
FROM employees
WHERE empno = 1001;

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

END;

If SELECT INTO returns no rows or multiple rows, it will raise an exception.

5. Complete Example:
plsql
CopyEdit
DECLARE
v_empno employees.empno%TYPE := 1002;
v_name employees.ename%TYPE;
BEGIN
-- Insert
INSERT INTO employees (empno, ename, salary) VALUES (v_empno, 'Alice', 6000);

-- Update
 UPDATE employees SET salary = salary + 500 WHERE empno = v_empno;

-- Select Into
SELECT ename INTO v_name FROM employees WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);

-- Delete
DELETE FROM employees WHERE empno = v_empno;

-- Commit
COMMIT;
END;

Cursors in PL/SQL


A Cursor in PL/SQL is a pointer to a context area that stores the result set of a query.
PL/SQL Cursors
The cursor is used to retrieve data one row at a time from the results set, unlike other SQL
commands that operate on all rows at once.
Cursors update table records in a singleton or row-by-row manner.
The Data that is stored in the Cursor is called the Active Data Set. Oracle DBMS has
another predefined area in the main memory Set, within which the cursors are opened. Hence
the size of the cursor is limited by the size of this pre-defined area.

Cursor Actions
Key actions involved in working with cursors in PL/SQL are:
1. Declare Cursor: A cursor is declared by defining the SQL statement that returns a result
set.
2. Open: A Cursor is opened and populated by executing the SQL statement defined by the
cursor.
3. Fetch: When the cursor is opened, rows can be fetched from the cursor one by one or in a
block to perform data manipulation.
4. Close: After data manipulation, close the cursor explicitly.
5. Deallocate: Finally, delete the cursor definition and release all the system resources
associated with the cursor.
Types of Cursors in PL/SQL
Cursors are classified depending on the circumstances in which they are opened.
 Implicit Cursor: If the Oracle engine opened a cursor for its internal processing it is
known as an Implicit Cursor. It is created "automatically" for the user by Oracle when a
query is executed and is simpler to code.
 Explicit Cursor: A Cursor can also be opened for processing data through a PL/SQL
block, on demand. Such a user-defined cursor is known as an Explicit Cursor.
Explicit cursor
An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on
a SELECT Statement which returns more than one row.

Syntax for creating cursor

CURSOR cursor_name IS select_statement;

Where,
 cursor_name: A suitable name for the cursor.
 select_statement: A select query which returns multiple rows
How to use Explicit Cursor?
There are four steps in using an Explicit Cursor.
1. DECLARE the cursor in the Declaration section.
2. OPEN the cursor in the Execution Section.
3. FETCH the data from the cursor into PL/SQL variables or records in the Execution
Section.
4. CLOSE the cursor in the Execution Section before you end the PL/SQL Block.
Syntax
General Syntax of using an explicit cursor in PL/SQL is:
DECLARE
variables;
records;
CURSOR cursor_name IS select_statement;
BEGIN
OPEN cursor_name;
LOOP
FETCH cursor_name INTO variables OR records;
EXIT WHEN cursor_name%NOTFOUND;

process the records;

END LOOP;
CLOSE cursor_name;
END;

1. Implicit Cursor Attributes (for SQL)

Attribute Description
 Attribute Description

SQL%ROWCOUNT Number of rows affected by the last DML operation.

SQL%FOUND Returns TRUE if DML statement affected one or more rows.

SQL%NOTFOUND Returns TRUE if DML statement did NOT affect any row.

SQL%ISOPEN Always returns FALSE for implicit cursors.

🔹 Example:
plsql
CopyEdit
BEGIN
DELETE FROM employees WHERE deptno = 10;

IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows deleted.');
ELSE
DBMS_OUTPUT.PUT_LINE('No rows deleted.');
END IF;
END;

2. Explicit Cursor Attributes (for user-defined cursors)

Suppose you define a cursor like this:

plsql
CopyEdit
CURSOR emp_cur IS SELECT * FROM employees;

Then, you can use the following attributes:

Attribute Description

cursor_name%ISOPEN Returns TRUE if the cursor is open.

cursor_name%FOUND Returns TRUE if the last fetch returned a row.

cursor_name%NOTFOUND Returns TRUE if the last fetch did NOT return a row.

cursor_name%ROWCOUNT Returns number of rows fetched so far.

🔹 Example of Explicit Cursor Attributes

plsql
CopyEdit
DECLARE
CURSOR emp_cur IS SELECT empno, ename FROM employees;
v_empno employees.empno%TYPE;
v_ename employees.ename%TYPE;
BEGIN
OPEN emp_cur;
 LOOP
FETCH emp_cur INTO v_empno, v_ename;

EXIT WHEN emp_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Emp No: ' || v_empno || ', Name: ' || v_ename);

END LOOP;

DBMS_OUTPUT.PUT_LINE('Total Rows Fetched: ' || emp_cur%ROWCOUNT);

CLOSE emp_cur;
END;

Summary Table
Attribute Implicit Cursor (SQL) Explicit Cursor (cursor_name)

%ROWCOUNT ✔ ✔

%FOUND ✔ ✔

%NOTFOUND ✔ ✔

%ISOPEN ✖(always FALSE) ✔

PL/SQL Cursor FOR LOOP

The PL/SQL FOR LOOP is a construct designed for repetitive execution, enabling
developers to iterate over a specified range of values or through elements in collections.
When used with cursors, the FOR LOOP can handle the processing of query results
efficiently, automatically fetching rows and iterating over them without requiring
explicit OPEN, FETCH, and CLOSE commands for the cursor
The FOR LOOP in PL/SQL is purpose-built for seamless iteration, whether traversing a
range of values or cycling through collection elements. The fundamental syntax is
elegantly straightforward:

---Standard FOR LOOP

FOR loop_index IN [REVERSE] lower_bound..upper_bound
LOOP
-- Statements to be executed in each iteration
END LOOP;
 loop_index: The loop index or counter variable.
 lower_bound and upper_bound: The range of values for the loop index.
 REVERSE (optional): Allows looping in reverse order.
Process:
 Initialize the loop index to the lower bound.
 Execute the statements within the loop.
 Increment or decrement the loop index.
 Repeat the process until the loop index reaches the upper bound.

Syntax:
For Standard FOR LOOP:
-- Basic FOR LOOP
FOR loop_index IN lower_bound..upper_bound
LOOP
-- Statements to be executed in each iteration
END LOOP;
For Reverse FOR LOOP:
-- FOR LOOP in Reverse
FOR loop_index IN REVERSE lower_bound..upper_bound
LOOP
-- Statements to be executed in each iteration
END LOOP;
Using FOR LOOP to Print Numbers
DECLARE
-- Loop index
loop_index NUMBER := 1;
BEGIN
FOR loop_index IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || loop_index);
END LOOP;
END;
/
Output:
Number: 1
Number: 2
Number: 3
Number: 4

PL/SQL Cursors with Parameters

The cursor can be declared with the parameter or without the parameter. It can have any
number of parameters as per requirement.Cursors with Parameters are used to work with
particular data. Parameters are used to create reusable and adaptable code. Explicit cursors
may be declared with parameters. The parameter contains a variable and its datatype. The
parameter can have a default value associated with a variable.
Syntax:
DECLARE
declare variables;
--create a cursor with parameter;
BEGIN OPEN cursor;
FETCH cursor;
-- process the rows
CLOSE cursor;
END;
Example of a Parameterized Cursor
GFG cursor is initialized with a parameter to retrieve the Id, name, and rank of Geek from
the Geeks Table. The requested data must satisfy the specified condition.
SET SERVEROUTPUT ON;
DECLARE
 CURSOR GFG (Min_rank NUMBER) IS
SELECT Id, name, rank
FROM Geeks
WHERE rank > Min_rank;
-- Declare variables
cur_id Geeks.Id%TYPE;
cur_name Geeks.name%TYPE;
cur_rank Geeks.rank%TYPE;
BEGIN
-- Open and fetch data using the cursor
OPEN GFG(951);
LOOP
FETCH GFG INTO cur_id, cur_name, cur_rank;
EXIT WHEN GFG%NOTFOUND;
-- Process fetched data
DBMS_OUTPUT.PUT_LINE('ID: ' || cur_id || ', Name: ' || cur_name || ', Rank: ' || cur_rank);
-- Close the loop
END LOOP;
-- Close the cursor
CLOSE GFG;
END;
Output:

PL/SQL Parameterized Cursor with Default Value

Default values can be passed in the parameterized cursor. If default values are passed in the
parameterized cursor in the DECLARE block, the cursor can be called without argument
in the BEGIN block if default values are to be used. If arguments are mentioned then they
overwrite the default value.
Syntax:
DECLARE
declare variables;
create a cursor with default value for parameter;
BEGIN OPEN cursor;
FETCH cursor;
process the rows;
CLOSE cursor;
END;
Example of PL/SQL Parameterized Cursor with Default Value
GFG cursor is initialized with a default value for the parameter to retrieve the Id, name,
and rank of Geek from the Geeks Table. The requested data must satisfy the specified
condition mentioned in the cursor.
SET SERVEROUTPUT ON;
DECLARE
--default value for the parameter
CURSOR GFG (Min_rank NUMBER :=951) IS
SELECT Id, name, rank
FROM Geeks
WHERE rank > Min_rank;
-- Declare variables
cur_id Geeks.Id%TYPE;
cur_name Geeks.name%TYPE;
cur_rank Geeks.rank%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('PL/SQL parameterized cursor with default value');
-- Open and fetch data using the cursor with no argument
OPEN GFG;
LOOP
FETCH GFG INTO cur_id, cur_name, cur_rank;
EXIT WHEN GFG%NOTFOUND;
-- Process fetched data
DBMS_OUTPUT.PUT_LINE('ID: ' || cur_id || ', Name: ' || cur_name || ', Rank: ' || cur_rank);
-- Close the loop
END LOOP;
-- Close the cursor
CLOSE GFG;
END;
Output of Parameterized Cursor with Default Value:
One mark:

1. PL/SQL stands for?

A) Procedural Language Extension of SQL
B) Pattern Language Extension of SQL
C) Primary Language Extension of SQL
D) Private Language Extension of SQL
Answer: A
atozplsql.blogspot.com+11freetimelearning.com+11includehelp.com+11engineeringinter
viewquestions.com+2top100mcq.com+2vskills.in+2
2. PL/SQL is a
A) Brick Structured Language
B) Block Structured Language
C) Banner Structured Language
D) Build Structured Language
Answer: B mcqtest.in+6includehelp.com+6freetimelearning.com+6
3. **Oracle released PL/SQL around which year?**
A) 1991
B) 1992
C) 1993
D) 1994
Answer: A sanfoundry.com+7freetimelearning.com+7mcqtest.in+7
4. *What symbol denotes single-line comments?**
A) / … */
B) //
C) – –
D) <– –
Answer: C sanfoundry.com+1letsfindcourse.com+1
5. **Which are lexical units in PL/SQL?**
A) Identifiers
B) Literals
C) Delimiters
D) All of the above
Answer: D
careerride.com+8sanfoundry.com+8includehelp.com+8latestinterviewquestions.com+4fr
eetimelearning.com+4includehelp.com+4
6. **Basic PL/SQL block structure is**
A) DECLARE–BEGIN–END
B) BEGIN–DECLARE–END
 C) EXCEPTION–DECLARE–BEGIN
D) BEGIN–EXCEPTION–DECLARE
Answer: A
atozplsql.blogspot.com+8sanfoundry.com+8vskills.in+8tutorialspoint.com+3top100mcq.
com+3vskills.in+3
7. BEGIN and END are
A) Mandatory sections
B) Optional sections
C) Comments
D) Variables
Answer: A freetimelearning.com+1engineeringinterviewquestions.com+1
8. **PL/SQL variables end with?**
A) =
B) :=
C) ==
D) =:
Answer: B letsfindcourse.com+1freetimelearning.com+1
9. PL/SQL identifiers include?
A) Variables
B) Constants
C) Exceptions
D) All of the above
Answer: D latestinterviewquestions.com+6letsfindcourse.com+6includehelp.com+6
10. **Variables in PL/SQL are by default**
A) Case-sensitive
B) Uppercase-sensitive
C) Lowercase-sensitive
D) Not case-sensitive
Answer: D
atozplsql.blogspot.com+9includehelp.com+9freetimelearning.com+9freetimelearning.co
m

11–20: Data Types & Variable Declaration

11. **Which is not a scalar data type?**
A) NUMBER
B) DATE
C) BOOLEAN
D) VARRAY
Answer: D tutorialspoint.com
12. **Max length of VARCHAR2 in PL/SQL block scope is**
A) 255
B) 1000
C) 4000
D) 32767
Answer: D top100mcq.com
13. **Which datatype stores large text?**
A) VARCHAR2
B) BLOB
C) CLOB
D) NUMBER
Answer: C top100mcq.com
 14. Referential datatype declaration uses %TYPE
A) TRUE
B) FALSE
Answer: A latestinterviewquestions.com
15. **Default variable initialization can use**
A) :=
B) DEFAULT
C) Both A & B
D) None
Answer: C
16. **Declaration with size/precision limit is called**
A) Constant declaration
B) Constrained declaration
C) Constrained decision
D) None
Answer: B
A) More memory
B) Less memory
C) Same memory
D) None
Answer: A
17. **Naming rule: first character must be**
A) Digit
B) ASCII letter
C) Symbol
D) None
Answer: B Record type is a structured datatype
A) TRUE
B) FALSE
Answer: A
18. %ROWTYPE creates a record based on table row
A) TRUE
B) FALSE
Answer: A

Assignment & Arithmetic Operators

21. **Which assignment operator is used?**
A) =
B) :=
C) ==
D) =:
Answer: B
22. Arithmetic operators include
A) +, -, *, /
B) ** for exponentiation
C) Both A & B
D) None
Answer:
23. **Exponentiation operator is**
A) ^
B) **
C) %
 D) $
Answer: B
24. **Concatenation operator is**
A) +
B) &
C) ||
D) #
Answer: C
25. **Expression 10 + 5 * 2 evaluates to**
A) 20
B) 30
C) 15
D) 12
Answer: A Modulus operator “%” is supported?
A) Yes
B) No
Answer: B (Standard SQL uses MOD; PL/SQL supports %)
26. Operator precedence: exponentiation before multiplication
A) TRUE
B) FALSE
Answer: A
27. Division is floating-point in PL/SQL
A) TRUE
B) FALSE
Answer: A
28. Arithmetic errors raise VALUE_ERROR
A) TRUE
B) FALSE
Answer: A
29. Syntax for exponentiation: a := 2 ** 3;
A) Valid
B) Invalid
Answer: A

Control Structures & Embedded SQL

31. **IF-THEN-ELSE exists?**
A) TRUE
B) FALSE
Answer: A
32. CASE statement exists?
A) TRUE
B) FALSE
Answer: A
33. Loop types: LOOP, WHILE, FOR
A) TRUE
B) FALSE
Answer: A
34. Cursor FOR loops handle open/fetch/close implicitly
A) TRUE
B) FALSE
Answer: A
35. EXIT WHEN prevents infinite simple loops
A) TRUE
 B) FALSE
Answer: A
36. Nested blocks allow local scope and exceptions
A) TRUE
B) FALSE
Answer: A
37. SELECT INTO requires exactly one row
A) TRUE
B) FALSE
Answer: A
38. Implicit cursors automatically created for all DML
A) TRUE
B) FALSE
Answer: A
39. Implicit cursor attributes include %FOUND, %NOTFOUND
A) TRUE
B) FALSE
Answer: A
40. You must OPEN explicit cursors before FETCH
A) TRUE
B) FALSE
Answer: A

Data Manipulation, Transaction Control, Cursors &

Exceptions
41. INSERT, UPDATE, DELETE allowed in PL/SQL
A) TRUE
B) FALSE
Answer: A
42. Commit saves changes permanently
A) TRUE
B) FALSE
Answer: A
43. Rollback undoes changes since last commit or savepoint
A) TRUE
B) FALSE
Answer: A
44. SAVEPOINT allows partial rollback
A) TRUE
B) FALSE
Answer: A
45. Cursor attribute %ROWCOUNT shows rows fetched
A) For explicit cursors
B) Implicit only
C) Both
D) None
Answer: A
46. WHERE CURRENT OF can be used with a cursor
A) TRUE
B) FALSE
Answer: A
 47. Cursors can accept parameters
A) TRUE
B) FALSE
Answer: A
48. Implicit exceptions include NO_DATA_FOUND
A) TRUE
B) FALSE
Answer: A
49. System exceptions include ZERO_DIVIDE, VALUE_ERROR
A) TRUE
B) FALSE
Answer: A
50. User-defined exceptions raised via RAISE
A) TRUE
B) FALSE
Answer:A

5MARK

1.. Explain the basic structure of a PL/SQL block with an example.

2. Describe different data types in PL/SQL and explain the use of %TYPE and %ROWTYPE.

3. What are control structures in PL/SQL? Explain with examples.

4. What is a cursor in PL/SQL? Differentiate between implicit and explicit cursors with
examples.

5. What are exceptions in PL/SQL? Explain the types and how they are handled.
10 MARK:

1. Discuss the structure and components of a PL/SQL block. Write a program to demonstrate
variable declaration, arithmetic operations, and exception handling.

2. Explain the different types of PL/SQL cursors. Write a PL/SQL program using an explicit
cursor with parameters, and explain the use of cursor attributes.

3. Describe the various control structures available in PL/SQL. How are nested blocks and
loops implemented in a PL/SQL program? Give an example.

4. What are the different types of exceptions in PL/SQL? How are user-defined exceptions
declared and handled? Write a PL/SQL block that demonstrates both predefined and user-
defined exceptions.

5. Write a PL/SQL program that performs insert, update, and delete operations using SQL
statements embedded within PL/SQL. Use transaction control statements like COMMIT,
ROLLBACK, and SAVEPOINT to manage the changes. Explain each step clearly.
UNIT V COMPLETED