SQL basics

Data Manipulation Language

Please do not copy without permission. © ALX 2024.
SQL basics

Data Manipulation Language (DML)

DML is a sublanguage of SQL responsible for

manipulating data in a database.

Most commonly used to add, edit, or delete

data from a database.

The main DML statements include INSERT,

UPDATE, and DELETE.

2
SQL basics

DML in practice
To better understand the three basic DML statements, INSERT, UPDATE, and DELETE, let’s consider the
following scenario.

We are data professionals in charge of managing

the database for an organization that operates a
Database:
Diabetes_care type 1 diabetes care program. We will focus on three
tables:

● Patient: Stores details on the diabetes

patients, such as their name and age.
● Clinic: Stores details on all the clinics that
administer insulin to patients, such as location
and insulin vials available.
● Insulin: Stores details on the insulin vials
administered, such as the type and expiry
date.

Now let’s explore how we can use DML to manage the Diabetes_care database.
3
SQL basics

INSERT

The INSERT statement is used to add new data to

a database table by inserting new records.

It allows us to insert new data into specific

columns of a table by providing values that
correspond to the column definitions.

Scenario: If we receive a new patient at a clinic,

we will use it to add a new record for the patient
to our Patient table.
SQL basics

INSERT syntax

| The INSERT statement contains two key parts, the INSERT INTO clause and the VALUES
clause.

Syntax Example
INSERT INTO INSERT INTO
Database_name.table_name Diabetes_care.Patient
(column1, column2) (First_name, Last_name)
VALUES VALUES
(value1, value2), ('Kennedy', ’Ngoma’),
(value1, value2); (‘Mulenga’, ’Mwamba’);

Specify the table name, the columns to be inserted, 1. Specify the table to insert the data (Patient).
and the corresponding values for each column. Specify the column names (First_name,
2.
Each list of values (value1, value2) becomes a new Last_name).
record in the table.
3. Specify the values to be inserted.

Note: Make sure the order of the values is in the same order as the columns in the table. 5
SQL basics

UPDATE

The UPDATE statement allows us to modify

existing data within a database table.

It only modifies the data in a table and does not

alter its structure.

Scenario: If a clinic finishes its insulin supply, we

will update the specific clinic’s record to reflect
this in the Clinic table.
SQL basics

UPDATE syntax

| The UPDATE statement contains two key parts, the UPDATE and SET clauses. We can
optionally include the WHERE clause to specify a condition for the update.

Syntax Example

UPDATE database_name.table_name UPDATE Diabetes_care.Clinic

SET column1 = value1, column2 = value2, ... SET Doses_available = 0
WHERE condition; WHERE Clinic_name = 'Kasama';

It specifies the table name, the columns to be 1. Specify the table name for the update (Clinic).
updated, the new values for each column, and the
condition that should be true for the update to be 2. Specify each column and the values the columns
implemented. should be updated to (Doses_available updated
to 0).
3. Specify the condition for the update (Clinic_name
is `Kasama`).

Note: The WHERE keyword specifies a condition for updates in a table, ensuring that changes are
made only where the condition is true. WHERE will be covered in detail later on. 7
SQL basics

DELETE

The DELETE statement is used to remove specific

records from a database table.

It provides a means to selectively delete data

based on specified conditions allowing for the
precise removal of unwanted or outdated records.

Scenario: If an insulin dose expires we can delete

the record from the Insulin table. However, this
should be done with caution due to potential
effects on related records within the database.
SQL basics

DELETE syntax

| The DELETE statement contains two key parts, the DELETE FROM and WHERE clauses.

Syntax Example

DELETE FROM database_name.table_name DELETE FROM Diabetes_care.Insulin

WHERE condition; WHERE Expiry_year = '2022';

It specifies the table name for the delete operation 1. Specify the table to delete from (Insulin).
and the condition that must be true for the record to
be deleted.
2. Specify the condition for the delete (Expiry_year
is equal to ‘2022’).

Note: The WHERE keyword specifies a condition for deletes in a database, ensuring that records are
only deleted where the condition is true. If WHERE is left out, every record in the table will be deleted.

9
SQL basics

Summary of DML statements

INSERT UPDATE DELETE

The INSERT statement is used The UPDATE statement is used The DELETE statement is used
to add new records to a to modify existing records in to remove records from a
database table. a database table. database table.

It specifies the table name, It specifies the table name, It specifies the table name for
the columns to be inserted, the columns to be updated, the delete operation and the
and the corresponding values and the new values for each condition that must be true
for each column. column. for the record to be deleted.

Always use the WHERE clause for selective updates and deletions.

10