SQL — DDL and DML · ⁨SQL — DDL et DML⁩

Handout · ⁨Fiche de cours⁩ Watch lesson · ⁨Regarder la leçon⁩

Vocabulary · ⁨Vocabulaire⁩

English	Français
SQL/ˌes kjuː ˈel/	SQL
Data Definition Language/ˈdeɪtə ˌdefɪˈnɪʃn ˈlæŋɡwɪdʒ/	Language de Définition de Données (DDL)
Data Manipulation Language/ˈdeɪtə məˌnɪpjʊˈleɪʃn ˈlæŋɡwɪdʒ/	Language de Manipulation de Données (DML)
INNER JOIN/ˈɪnə dʒɔɪn/	INNER JOIN
aggregate functions/ˈæɡrɪɡeɪt ˈfʌŋkʃnz/	fonctions agrégées

English

DDL: creating the structure

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.

Français

DDL : créer la 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);

Types de données au programme : CHARACTER (un nombre fixe de caractères), VARCHAR(n) (jusqu'à n caractères), BOOLEAN, INTEGER, REAL, DATE, TIME.
PRIMARY KEY (field) nomme la clé ; ALTER TABLE … ADD ajoute un attribut à une table existante.

English

Worked example: two tables with a foreign key

Example

Write the SQL to create an ORDERS table with OrderID as primary key, CustomerID as a foreign key to CUSTOMER, and an OrderDate.

The marks: CREATE TABLE with the name; each attribute with a suitable type; the PRIMARY KEY; the FOREIGN KEY … REFERENCES naming the table and its field.

Français

Exemple résolu : deux tables avec une clé étrangère

Example

Écrivez le SQL pour créer une table ORDERS avec OrderID comme clé primaire, CustomerID comme clé étrangère vers CUSTOMER, et un OrderDate.

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

Les points : CREATE TABLE avec le nom ; chaque attribut avec un type approprié ; la PRIMARY KEY ; la FOREIGN KEY … REFERENCES nommant la table et son champ.

English

DML: asking a question with SELECT

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

Français

DML : poser une question avec SELECT

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

SELECT liste les champs à afficher (* pour tous), FROM nomme la table, WHERE garde uniquement les lignes répondant à une condition, ORDER BY trie le résultat, ASC ou DESC.
Les chaînes sont entre guillemets simples ; les nombres sans guillemets. Comparaisons : =, <, >, <=, >=, <> ; les conditions se joignent avec AND, OR, NOT ; LIKE 'A%' correspond au texte commençant par A ; BETWEEN 10 AND 20 donne une plage.

Une requête SELECT retourne uniquement les lignes correspondant à sa condition

Seules les lignes passant le WHERE atteignent le résultat

English

Worked example: write the query

Example

Write an SQL script to output the names and email addresses of all active customers in Manchester, in alphabetical order of name.

One mark each: the right fields after SELECT; the right table after FROM; the WHERE with both conditions and the string in single quotes; ORDER BY Name. Use the exact table and field names the question gives.

Français

Exemple résolu : écrire la requête

Example

Écrivez un script SQL pour afficher les noms et adresses e-mail de tous les clients actifs à Manchester, par ordre alphabétique de nom.

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

Un point chacun : les bons champs après SELECT ; la bonne table après FROM ; la WHERE avec les deux conditions et la chaîne entre guillemets simples ; ORDER BY Name. Utilisez les noms exacts de table et de champ donnés par la question.

English

Two tables: INNER JOIN

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.

The JOIN matches each order's key to its customer row and stitches the two tables into a single combined result set.

Français

Deux tables : INNER JOIN

SELECT CUSTOMER.Name, ORDERS.OrderDate
FROM CUSTOMER INNER JOIN ORDERS
  ON CUSTOMER.CustomerID = ORDERS.CustomerID
WHERE ORDERS.OrderDate >= '2024-01-01';

Un INNER JOIN 连接 combine les lignes de deux tables où la clé étrangère dans l'une correspond à la clé primaire dans l'autre, nommé dans la clause ON.
Préfixez un champ avec sa table si le même nom apparaît dans les deux. Le programme demande des requêtes sur au maximum deux tables.

Le JOIN associe la clé de chaque commande à son enregistrement client et coud les deux tables en un ensemble de résultats combiné unique.

English

Aggregates and GROUP BY

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.

Français

Fonctions agrégées et 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;

Les fonctions agrégées 聚合函数 résument plusieurs lignes en une valeur : COUNT les lignes, SUM un total, AVG une moyenne.
GROUP BY crée une ligne de résumé par valeur d'un champ : le nombre de commandes par client. Sans cela, une agrégation résume toute la table.

English

Changing the data: INSERT, UPDATE, DELETE

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.

Français

Modifier les données : 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 ajoute une ligne : listez les champs, puis les valeurs dans le même ordre. UPDATE … SET … WHERE change les lignes correspondantes. DELETE FROM … WHERE les supprime.
Donnez toujours à UPDATE et DELETE une clause WHERE, sinon le changement touche toutes les lignes de la table.

English

Worked example: read the statement

Example

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.

Français

Exemple résolu : lire l'instruction

Example

SELECT Name
FROM CUSTOMER INNER JOIN ORDERS
  ON CUSTOMER.CustomerID = ORDERS.CustomerID
WHERE OrderDate = '2024-05-01'
ORDER BY Name DESC;

Indiquez ce que ce script produit. Les noms de tous les clients ayant passé une commande le 1 mai 2024, une ligne par telle commande, par ordre alphabétique inverse.
Lisez-le dans l'ordre d'exécution : joignez les tables sur l'ID client, gardez les lignes pour cette date, affichez le nom, triez descendant. Un client avec deux commandes ce jour-là apparaît deux fois.

Next · ⁨Suivant⁩ →

Full revision notes: Databases · ⁨Bases de données⁩ · ⁨Notes de révision complètes : Databases · ⁨Bases de données⁩⁩