SQL — DDLおよびDML

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 SELECT today 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 TABLE is DDL, SELECT is DML.

Structure on one side, data on the other

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 … ADD adds an attribute to an existing table.

Worked example: two tables with a foreign key

CREATE TABLE ORDERS (
  OrderID INTEGER,
  CustomerID INTEGER,
  OrderDate DATE,
  PRIMARY KEY (OrderID),
  FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID)
);

DML: asking a question with SELECT

SELECT Name, Town
FROM CUSTOMER
WHERE Town = 'London'
ORDER BY Name ASC;

• SELECT lists the fields to output (* for all), FROM names the table, WHERE keeps only the rows that meet a condition, ORDER BY sorts the result, ASC or DESC.
• Strings go in single quotes; numbers do not. Comparisons: =, <, >, <=, >=, <>; conditions join with AND, OR, NOT; LIKE 'A%' matches text starting with A; BETWEEN 10 AND 20 gives a range.

Only the rows that pass the WHERE reach the result

Worked example: write the query

SELECT Name, Email
FROM CUSTOMER
WHERE Town = 'Manchester' AND Active = TRUE
ORDER BY Name;

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 ON clause.
• Prefix a field with its table when the same name appears in both. The syllabus asks for queries over at most two tables.

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: COUNT the rows, SUM a total, AVG a mean.
• GROUP BY makes one summary row per value of a field: the number of orders per customer. Without it, an aggregate summarises the whole table.

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 … VALUES adds a row: list the fields, then the values in the same order. UPDATE … SET … WHERE changes matching rows. DELETE FROM … WHERE removes them.
• Always give UPDATE and DELETE a WHERE clause, or the change hits every row in the table.

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;

Marks that slip away

You've got it