Chapter 6:- SQL: Data Manipulation

Introduction to SQL
Objectives of SQL:- Ideally, a database language should allow a user to:
 create the database and relation structures;
 perform basic data management tasks, such as the insertion, modification, and deletion of
data from the relations;
 perform both simple and complex queries.

A database language must perform these tasks with minimal user effort, and its command structure
and syntax must be relatively easy to learn. Finally, the language must be portable; that is, it must
confirm to some recognized standard so that we can use the same command structure and syntax
when we move from one DBMS to another. SQL is intended to satisfy these requirements.

SQL is an example of a transform-oriented language, or a language designed to use relations to

transform inputs into required outputs. As a language, the ISO SQL standard has two major
components:

 a Data Definition Language (DDL) for defining the database structure and controlling access
to the data;
 a Data Manipulation Language (DML) for retrieving and updating data.

SQL is a relatively easy language to learn:

 It is a nonprocedural language; you specify what information you require, rather than how
to get it. In other words, SQL does not require you to specify the access methods to the data.
 Like most modern languages, SQL is essentially free-format, which means that parts of
statements do not have to be typed at particular locations on the screen.
 The command structure consists of standard English words such as CREATE TABLE, INSERT,
SELECT. For example:
o CREATE TABLE Staff (staffNo VARCHAR(5), IName VARCHAR(15), salary
DECIMAL(7,2));
o INSERT INTO Staff VALUES (‘SG16’, ‘Brown’, 8300);
o SELECT staffNo, IName, salary FROM Staff WHERE salary > 10000;
 SQL can be used by a range of users including database administrators (DBA), management
personnel, application developers, and many other types of end-user.

History of SQL:-
 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.
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.
Data is at the core of every application, and SQL (Structured Query Language) manages and
interacts with this data. Whether we’re handling a small user database or analyzing terabytes of
sales records, SQL allows efficient querying, updating, and management of relational databases.

When data needs to be retrieved from a database, SQL is used to construct and send the request.
The Database Management System (DBMS) processes the SQL query, retrieves the requested
data, and returns it to the user or application. Instead of specifying step-by-step procedures, SQL
statements describe what data should be retrieved, organized, or modified, allowing the DBMS to
handle how the operations are executed efficiently.

In common usage, SQL encompasses DDL and DML commands for CREATE, UPDATE, MODIFY, or
other operations on database structure.

Components of 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.

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.
1. 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.
2. 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.
3. 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.
4. 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.
5. 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:

1-Data Definition Language:- SQL commands used to create the database structure are known
as data definition language (DDL). 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

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

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

2-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

COMMIT Saves all changes made during the current transaction.

ROLLBACK Reverts changes made during the current transaction.

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

Data Definition Language (DDL)

DDL or Data Definition Language actually consists of the SQL commands that can be used
to 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 (table, index,
CREATE TABLE table_name (column1
CREATE function, views, store
data_type, column2 data_type, ...);
procedure, and
triggers)

Delete objects from

DROP DROP TABLE table_name;
the database

Alter the structure of ALTER TABLE table_name ADD COLUMN

ALTER
the database column_name data_type;

TRUNCATE Remove all records TRUNCATE TABLE table_name;

from a table, including
all spaces allocated for
the records are
 Command Description Syntax

removed

Add comments to the COMMENT 'comment_text' ON TABLE

COMMENT
data dictionary table_name;

Rename an object
RENAME TABLE old_table_name TO
RENAME existing in the
new_table_name;
database

The CREATE
INDEX statement is CREATE INDEX index_name
CREATE INDEX
used to create indexes ON table_name (column1, column2, ...);
in tables.

The DROP
INDEX statement is
DROP INDEX DROP INDEX index_name ON table_name;
used to delete an index
in a table.

Example of DDL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);

Important SQL Commands

1. SELECT: Used to retrieve data from a database.
2. INSERT: Used to add new data to a database.
3. UPDATE: Used to modify existing data in a database.
4. DELETE: Used to remove data from a database.
5. CREATE TABLE: Used to create a new table in a database.
6. ALTER TABLE: Used to modify the structure of an existing table.
7. DROP TABLE: Used to delete an entire table from a database.
8. WHERE: Used to filter rows based on a specified condition.
9. ORDER BY: Used to sort the result set in ascending or descending order.
10. JOIN: Used to combine rows from two or more tables based on a related column between
them.

Action Queries
Action Queries, also known as Data Manipulation Language (DML) statements, do not return a
dataset like Select Queries, but makes changes to the data in the base tables. There are four types of
action queries: Delete Queries, Update Queries, Append Queries and Make-Table Queries.
Make-Table Query: A Make-Table Query is a shortcut method to create a table based on values in
another table. In SQL, it's called a SELECT...INTO statement. The basic syntax looks like this:
SELECT ISBN, Title, PubID, Price INTO BooksTemp
FROM Books;

Delete Query: Delete Queries delete records from a record source, that is, a table, a join, or another
query.
DELETE * FROM Books;
DELETE * FROM Books WHERE PubID=1;
Update Query: An Update Query will make bulk changes to records in the record source. . The record
source must be updateable. For instance, suppose I want to raise the Price of every book in the Books
table by 10%. I could create a SQL statement like this:
UPDATE Books SET Price = 550
WHERE Price=500;

Append Query: An Append Query, also called an INSERT INTO in SQL, adds records to a record
source (table or query). It can either append individual record values or a dataset queried from
another record source. To append values, use the VALUES keyword:

INSERT INTO Books(ISBN, Title, PubID, Price)

VALUES ("0-103-45678-9", "Iliad", 1, 300);

Data Integrity

Data integrity refers to the accuracy, consistency, and reliability of data throughout its lifecycle. It
ensures that data remains unaltered and uncorrupted from its original state during storage,
processing, retrieval, and transmission.

Types of Data Integrity: Data integrity is applied in all databases can be categorized into two main
types:

Physical Integrity

Protecting data against external factors, such as natural calamities, power outages, or
hackers, falls under the domain of physical integrity. Moreover, human faults, storage
attrition, and several other problems can make data operators unable to obtain information
from a database.

Logical Integrity

It concerns the rationality of data present within the relational database. Logical integrity constraints
can be categorized into four types:

Entity Integrity

It depends on the making of primary keys or exclusive values that classify data items. The purpose is
to ensure that data is not recorded multiple times (i.e., each data item is unique), and the table has
no null fields.

Entity integrity is a critical feature of a relational database that stores data in a tabular format, which
can be interconnected and used in various ways.
Referential Integrity

It denotes a series of procedures that ensure proper and consistent data storage and usage.
Referential integrity ensures that only the required alterations, additions, or removals happen via
rules implanted into the database’s structure about how foreign keys are used.

These rules might include conditions that remove duplicate data records, warrant that data is
precise, and prohibit unsuitable recording data.

Domain Integrity

It’s an assortment of procedures that ensures the precision of every data item is maintained in a
domain. Here, a domain is defined as a set of suitable values that a column is permitted to enclose.

Domain integrity encompasses rules and other processes restricting the format, type, and volume of
data recorded in a database. It ensures that every column in a relational database is in a defined
domain.

User-Defined Integrity

It comprises the rules defined by the user to fulfill their specific requirements. Entity, referential, and
domain integrity are not enough to refine and secure data. Time in time again, particular business
rules must be considered and integrated into data integrity processes to meet enterprise standards.