SQL — DDL and DML
| English | Chinese | Pinyin |
|---|---|---|
| SQL | 结构化查询语言 | jié gòu huà chá xún yǔ yán |
| Data Definition Language | 数据定义语言 | shù jù dìng yì yǔ yán |
| Data Manipulation Language | 数据操纵语言 | shù jù cāo zòng yǔ yán |
| INNER JOIN | 连接 | lián jiē |
| aggregate functions | 聚合函数 | jù hé hán shù |
The language your grandparents' programmers also used
- In 1974 two IBM researchers designed a query language for Codd's tables and called it SEQUEL: Structured English Query Language. The name was trimmed to SQL 结构化查询语言, and the language never went away.
- Fifty years later, every bank, airline, hospital and website you use runs on it. A student typing
SELECTtoday is writing the same statement a programmer wrote before her parents were born. - It has two halves, and the exam asks you to read either and to write both: the Data Definition Language 数据定义语言 that builds the structure, and the Data Manipulation Language 数据操纵语言 that fills, changes and questions the data.
- This lesson is the subset of SQL on the syllabus, statement by statement, with the marks each one carries.
DDL and DML
- The DBMS carries out all creation and modification of the database's structure through its DDL: creating a database, creating and altering tables, adding keys.
- It carries out all queries and maintenance of the data through its DML: selecting, inserting, updating and deleting rows.
- SQL is the industry standard for both. Sorting a statement into the right half is a common one-mark question:
CREATE TABLEis DDL,SELECTis DML.
Structure on one side, data on the other
Which is part of the Data Definition Language (DDL)?
DDL changes the structure (CREATE, ALTER, DROP). SELECT/INSERT/UPDATE/DELETE are DML (working with data).
DDL defines the structure (e.g. CREATE TABLE), while DML works with the data inside it (SELECT, INSERT, UPDATE, DELETE).
Definition vs Manipulation: DDL shapes the tables; DML reads and changes the rows.
DDL: creating the structure
CREATE DATABASE Shop;
CREATE TABLE CUSTOMER (
CustomerID INTEGER,
Name VARCHAR(50),
Town VARCHAR(30),
Joined DATE,
Active BOOLEAN,
PRIMARY KEY (CustomerID)
);
ALTER TABLE CUSTOMER ADD Email VARCHAR(100);
- Data types on the syllabus:
CHARACTER(a fixed number of characters),VARCHAR(n)(up to n characters),BOOLEAN,INTEGER,REAL,DATE,TIME. PRIMARY KEY (field)names the key;ALTER TABLE … ADDadds an attribute to an existing table.
Match each item of data to the SQL data type for it.
Two states, variable-length text, a number with a fractional part, a calendar date. INTEGER is for whole counts and TIME for a time of day.
Worked example: two tables with a foreign key
- Write the SQL to create an
ORDERStable withOrderIDas primary key,CustomerIDas a foreign key toCUSTOMER, and anOrderDate.
CREATE TABLE ORDERS (
OrderID INTEGER,
CustomerID INTEGER,
OrderDate DATE,
PRIMARY KEY (OrderID),
FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID)
);
- The marks:
CREATE TABLEwith the name; each attribute with a suitable type; thePRIMARY KEY; theFOREIGN KEY … REFERENCESnaming the table and its field.
DML: asking a question with SELECT
SELECT Name, Town
FROM CUSTOMER
WHERE Town = 'London'
ORDER BY Name ASC;
SELECTlists the fields to output (*for all),FROMnames the table,WHEREkeeps only the rows that meet a condition,ORDER BYsorts the result,ASCorDESC.- Strings go in single quotes; numbers do not. Comparisons:
=,<,>,<=,>=,<>; conditions join withAND,OR,NOT;LIKE 'A%'matches text starting with A;BETWEEN 10 AND 20gives a range.
Only the rows that pass the WHERE reach the result
Which SQL keyword retrieves data from a table? (one word)
SELECT lists the fields to retrieve; FROM names the table.
The WHERE clause in a SELECT statement:
WHERE filters rows by a condition; ORDER BY sorts; the SELECT list chooses columns.
Worked example: write the query
- Write an SQL script to output the names and email addresses of all active customers in Manchester, in alphabetical order of name.
SELECT Name, Email
FROM CUSTOMER
WHERE Town = 'Manchester' AND Active = TRUE
ORDER BY Name;
- One mark each: the right fields after
SELECT; the right table afterFROM; theWHEREwith both conditions and the string in single quotes;ORDER BY Name. Use the exact table and field names the question gives.
Put the clauses of a SELECT statement in the order they are written.
Fields, table, filter, sort. ORDER BY is always last, and the statement ends with a semicolon.
Two tables: INNER JOIN
SELECT CUSTOMER.Name, ORDERS.OrderDate
FROM CUSTOMER INNER JOIN ORDERS
ON CUSTOMER.CustomerID = ORDERS.CustomerID
WHERE ORDERS.OrderDate >= '2024-01-01';
- An INNER JOIN 连接 combines the rows of two tables where the foreign key in one matches the primary key in the other, named in the
ONclause. - Prefix a field with its table when the same name appears in both. The syllabus asks for queries over at most two tables.
Stitch two tables with INNER JOIN
A join matches rows where the foreign key equals the primary key — here Orders.CustomerID = Customer.CustomerID — and combines each matching pair into one wider row.
An INNER JOIN is used to:
A JOIN combines two tables on a relationship (usually a foreign key matching a primary key).
Aggregates and GROUP BY
SELECT CustomerID, COUNT(*) AS NumOrders
FROM ORDERS
GROUP BY CustomerID;
SELECT AVG(Price) FROM PRODUCT;
SELECT SUM(Quantity) FROM ORDER_LINE WHERE OrderID = 1042;
- Aggregate functions 聚合函数 summarise many rows into one value:
COUNTthe rows,SUMa total,AVGa mean. GROUP BYmakes one summary row per value of a field: the number of orders per customer. Without it, an aggregate summarises the whole table.
What does COUNT(*) return?
COUNT(*) counts rows; SUM/AVG/MIN/MAX are the other aggregate functions.
To output the number of orders placed by each customer, the query needs: select all that apply.
Count the rows, one group per customer, from the orders table. Sorting is optional.
Changing the data: INSERT, UPDATE, DELETE
INSERT INTO CUSTOMER (CustomerID, Name, Town, Joined, Active)
VALUES (101, 'Ada Lovelace', 'London', '2024-03-01', TRUE);
UPDATE CUSTOMER SET Town = 'Bristol' WHERE CustomerID = 101;
DELETE FROM CUSTOMER WHERE CustomerID = 101;
INSERT INTO … VALUESadds a row: list the fields, then the values in the same order.UPDATE … SET … WHEREchanges matching rows.DELETE FROM … WHEREremoves them.- Always give
UPDATEandDELETEaWHEREclause, or the change hits every row in the table.
What happens if you run UPDATE or DELETE without a WHERE clause?
With no WHERE, the operation affects all rows — a common and dangerous mistake.
Match each DML statement to what it does.
SELECT reads; INSERT adds; UPDATE changes; DELETE removes — the four core DML verbs.
Worked example: read the statement
SELECT Name
FROM CUSTOMER INNER JOIN ORDERS
ON CUSTOMER.CustomerID = ORDERS.CustomerID
WHERE OrderDate = '2024-05-01'
ORDER BY Name DESC;
- State what this script outputs. The names of every customer who placed an order on 1 May 2024, one row per such order, in reverse alphabetical order.
- Read it in execution order: join the tables on the customer ID, keep the rows for that date, output the name, sort descending. A customer with two orders that day appears twice.
Marks that slip away
- Strings in single quotes, numbers bare:
Town = 'London',CustomerID = 101. ORDER BYcomes afterWHERE; a join needs itsONclause; every statement ends with a semicolon.COUNT(*)counts rows, not distinct values; the per-group question needsGROUP BY.CREATE,ALTERandPRIMARY KEYare DDL;SELECT,INSERT,UPDATE,DELETEare DML. Use the exact names the question gives.
You've got it
- DDL creates and changes the structure:
CREATE DATABASE,CREATE TABLEwith typed attributes,PRIMARY KEY,FOREIGN KEY … REFERENCES,ALTER TABLE … ADD· DML works with the data - types:
CHARACTER,VARCHAR(n),BOOLEAN,INTEGER,REAL,DATE,TIME SELECT fields FROM table WHERE condition ORDER BY field;INNER JOIN … ONfor two tables;COUNT,SUM,AVGwithGROUP BYfor one row per groupINSERT INTO … VALUES,UPDATE … SET … WHERE,DELETE FROM … WHERE: never an update or delete withoutWHERE