s5.07.05 - sql, dml, select, insert, update, delete

Interacting with Data: Data Manipulation Language (DML)

Last lesson, you acted as a Database Architect, using Data Definition Language (DDL) to build the empty skeleton of your 'World' database. Today, you become a Database Administrator and Software Developer, bringing that skeleton to life by inserting and interacting with the data itself. This is where Data Manipulation Language (DML) comes in.

What is Data Manipulation Language (DML)?

Data Manipulation Language (DML) is a subset of SQL (Structured Query Language) used for managing data within database objects (like tables). While DDL defines the structure of your database, DML lets you actually work with the data inside that structure. Think of it as the commands you use to read, write, edit, and delete the actual information.

The four core DML commands, often collectively referred to as CRUD operations (Create, Read, Update, Delete), are:


Collectively, this is known as CRUD.

Adding New Data: The

INSERT INTO

Statement

The INSERT INTO statement allows you to add new records (rows) into an existing table.

INSERT INTO TableName (column1, column2, ...)
VALUES (value1, value2, ...);

You must provide values for all

NOT NULL

columns that don't

AUTOINCREMENT

.

-- Insert a new language
INSERT INTO Language (LanguageCode, LanguageName)
VALUES ("kli", "Klingon");

Retrieving Data: The

SELECT

Statement

The

SELECT

statement is arguably the most common DML command. It allows you to query your database and retrieve information.

SELECT column1, column2 FROM TableName;

-- To see all continent names
SELECT ContinentName FROM Continent;

Filtering Results: The

WHERE

Clause

To retrieve specific data that meets certain criteria, you use the WHERE clause. This is crucial for precise data retrieval and manipulation.

SELECT column1, column2 FROM TableName WHERE Condition;

-- To find cities with a population greater than 8,000,000
SELECT CityName, Population FROM City WHERE Population > 8000000;

Filtering with a pattern

Most SQL flavours, including SQLite, come with pattern matching for the Condition clause.

SELECT column_list FROM table_name WHERE column_name LIKE pattern;

-- To find all the cities names starting with 'P'
SELECT CityName from city WHERE CityName LIKE 'P%';

Sorting Results: The

ORDER BY

Clause

To sort your results, use the ORDER BY clause.

-- List continents in reverse alphabetical order
SELECT ContinentName FROM Continent ORDER BY ContinentName DESC;

Limiting Results: The

LIMIT

Clause

To restrict the number of rows returned, use the LIMIT clause.

-- List the top 3 most populous cities
SELECT CityName, Population FROM City ORDER BY Population DESC LIMIT 3;

Modifying Data: The

UPDATE

Statement

The UPDATE statement is used to modify existing data in one or more records in a table.

UPDATE TableName
SET column1 = new_value1, column2 = new_value2, ...
WHERE Condition;

CRITICAL NOTE: Always, always, ALWAYS use a

WHERE

clause with `UPDATE`. If you omit it, every single record in the table will be updated!

-- Update London's population
UPDATE City
SET Population = 8674000
WHERE CityName = 'London' AND CountryCode = 'GBR';

Removing Data: The

DELETE FROM

Statement

The DELETE FROM statement is used to remove one or more records from a table.

DELETE FROM TableName WHERE Condition;

CRITICAL NOTE: Just like `UPDATE`, always use a

WHERE

clause with `DELETE FROM`. If you omit it, every single record in the table will be deleted!

-- Delete a city
DELETE FROM City
WHERE CityName = 'St Asaph' AND CountryCode = 'GBR';

The DBMS and Data Integrity

A Database Management System (DBMS) (like SQLite, MySQL, PostgreSQL) ensures that all your DML operations respect the DDL rules you defined. For instance, you cannot `INSERT` a record with a foreign key that doesn't exist in the parent table; the DBMS will prevent this to maintain referential integrity. This automatic enforcement by the DBMS is vital for building robust applications (such as your NEA project!) and is a key responsibility of a Database Administrator.