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

Handout · ⁨Material de apoio⁩ Watch lesson · ⁨Assistir aula⁩

Vocabulary · ⁨Vocabulário⁩

English	Português
SQL/ˌes kjuː ˈel/	SQL
Data Definition Language/ˈdeɪtə ˌdefɪˈnɪʃn ˈlæŋɡwɪdʒ/	Linguagem de Definição de Dados
Data Manipulation Language/ˈdeɪtə məˌnɪpjʊˈleɪʃn ˈlæŋɡwɪdʒ/	Linguagem de Manipulação de Dados
INNER JOIN/ˈɪnə dʒɔɪn/	INNER JOIN
aggregate functions/ˈæɡrɪɡeɪt ˈfʌŋkʃnz/	funções agregadas

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.

Português

DDL: criando a estrutura

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);

Tipos de dados no programa escolar: CHARACTER (um número fixo de caracteres), VARCHAR(n) (até n caracteres), BOOLEAN, INTEGER, REAL, DATE, TIME.
PRIMARY KEY (field) nomeia a chave; ALTER TABLE … ADD adiciona um atributo a uma tabela existente.

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.

Português

Exemplo resolvido: duas tabelas com uma chave estrangeira

Example

Escreva o SQL para criar uma tabela ORDERS com OrderID como chave primária, CustomerID como chave estrangeira para CUSTOMER, e um OrderDate.

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

As marcas: CREATE TABLE com o nome; cada atributo com um tipo adequado; a PRIMARY KEY; a FOREIGN KEY … REFERENCES nomeando a tabela e seu campo.

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

Português

DML: fazendo uma pergunta com SELECT

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

SELECT lista os campos a serem exibidos (* para todos), FROM nomeia a tabela, WHERE mantém apenas as linhas que atendem a uma condição, ORDER BY ordena o resultado, ASC ou DESC.
Strings vão entre aspas simples; números não. Comparações: =, <, >, <=, >=, <>; condições são unidas por AND, OR, NOT; LIKE 'A%' corresponde a texto começando com A; BETWEEN 10 AND 20 dá uma faixa.

Uma consulta SELECT retorna apenas as linhas que correspondem à sua condição

*Apenas as linhas que passam no WHERE chegam ao resultado

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.

Português

Exemplo resolvido: escreva a consulta

Example

Escreva um script SQL para exibir os nomes e endereços de e-mail de todos os clientes ativos em Manchester, em ordem alfabética de nome.

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

Uma marca cada: os campos certos após SELECT; a tabela certa após FROM; o WHERE com ambas as condições e a string entre aspas simples; ORDER BY Name. Use os nomes exatos de tabela e campo que a questão fornece.

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.

Português

Duas tabelas: INNER JOIN

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

Um INNER JOIN 连接 combina as linhas de duas tabelas onde a chave estrangeira em uma corresponde à chave primária na outra, nomeada na cláusula ON.
Prefixe um campo com sua tabela quando o mesmo nome aparecer em ambas. O programa escolar pede consultas em no máximo duas tabelas.

O JOIN associa a chave de cada pedido à linha de seu cliente e costura as duas tabelas em um único conjunto de resultados combinado.

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.

Português

Agregados e 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;

Funções agregadas 聚合函数 resumem muitas linhas em um único valor: COUNT contar as linhas, SUM somar um total, AVG calcular uma média.
GROUP BY cria uma linha de resumo por valor de um campo: o número de pedidos por cliente. Sem isso, um agregado resume toda a tabela.

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.

Português

Alterando os dados: 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 adiciona uma linha: liste os campos, depois os valores na mesma ordem. UPDATE … SET … WHERE altera linhas correspondentes. DELETE FROM … WHERE remove-as.
Sempre dê a UPDATE e DELETE uma cláusula WHERE, ou a alteração atingirá todas as linhas da tabela.

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.

Português

Exemplo resolvido: leia a instrução

Example

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

Afirme o que este script exibe. Os nomes de todos os clientes que fizeram um pedido em 1º de maio de 2024, uma linha por tal pedido, em ordem alfabética reversa.
Leia-o na ordem de execução: junte as tabelas pelo ID do cliente, mantenha as linhas daquela data, exiba o nome, ordene descendente. Um cliente com dois pedidos naquele dia aparece duas vezes.

Next · ⁨Próximo⁩ →

Full revision notes: Databases · ⁨Bancos de Dados⁩ · ⁨Notas de revisão completas: Databases · ⁨Bancos de Dados⁩⁩