SQL — DDL and DML

Talking to a database with SQL

• SQL (Structured Query Language) is how we build and use a database.
• It has two halves: DDL changes the structure; DML works with the data.
• Let's see the core statements of each.

DDL — defining the structure

CREATE TABLE Customer (
  CustomerID INTEGER PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Phone VARCHAR(20)
);

ALTER TABLE Customer ADD Email VARCHAR(100);
DROP TABLE Customer;

• CREATE TABLE builds a table with field types and keys; ALTER changes it; DROP deletes it.
• Add a relationship with FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID).
• Common types: INTEGER, VARCHAR(n), CHAR(n), DATE, BOOLEAN, DECIMAL(p, s).

DML — querying with SELECT

SELECT Name, Phone
FROM Customer
WHERE City = 'London'
ORDER BY Name ASC;

• SELECT lists fields · FROM names the table · WHERE filters rows · ORDER BY sorts.
• Strings go in single quotes; comparisons: = < > <= >= <>; patterns: LIKE 'A%'; sets: IN (1,2,3); ranges: BETWEEN 10 AND 20.

Joins and aggregates

SELECT C.Name, O.OrderDate
FROM Customer C INNER JOIN Orders O
  ON C.CustomerID = O.CustomerID;

SELECT CustomerID, COUNT(*) AS NumOrders
FROM Orders
GROUP BY CustomerID;

• A JOIN combines two tables using a foreign-key relationship.
• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) summarise rows, often with GROUP BY.

Insert, update, delete

INSERT INTO Customer (CustomerID, Name) VALUES (101, 'Ada Lovelace');
UPDATE Customer SET Phone = '020-9999' WHERE CustomerID = 101;
DELETE FROM Customer WHERE CustomerID = 101;

• Always put a WHERE clause on UPDATE and DELETE, or the change hits every row.

You've got it