SQL basics: DDL, DML and queries

Keiko has written SQL queries for years, mostly by copying an old report and changing the dates. At the fifth data hour Elena asks her to put that aside and learn the language properly, from the beginning. "The exam will ask you to recognize what a statement does," Elena says. "The job will ask you to write the right one the first time." Walt nods and adds that he has seen more databases damaged by an UPDATE without a WHERE clause than by any hardware failure.

What SQL is

SQL, Structured Query Language, is the standard language for working with relational databases. It is pronounced either as the letters S-Q-L or as "sequel", and both are common. SQL is declarative: you describe the result you want, such as "total sales per store for November", and the database decides how to get it. That is different from most programming languages, where you write the steps yourself. The part of the database that works out the steps is the query optimizer, and lesson 7 shows how indexes help it.

SQL was standardized by ANSI and ISO in the 1980s, and every major relational database supports the core of the standard. Each product also adds its own extensions, called a dialect. Microsoft SQL Server and Azure SQL use Transact-SQL, or T-SQL. PostgreSQL uses its own dialect, usually just called PostgreSQL SQL, and MySQL has its own too. The basic statements in this lesson work almost identically in all of them. The runnable examples use SQLite, a small database built into Python, so they can run in your browser; where T-SQL differs, the lesson says so.

Three groups of statements

SQL statements fall into groups according to what they do. The DP-900 exam expects you to know the three main groups and their key statements.

• Data definition language, DDL, creates and changes the structure of the database: tables, views, indexes and other objects. Its main statements are CREATE, ALTER and DROP.
• Data manipulation language, DML, works with the rows inside tables: SELECT reads rows, INSERT adds them, UPDATE changes them and DELETE removes them. Some sources list SELECT separately as a data query language, DQL, but the exam treats it with DML.
• Data control language, DCL, manages permissions: GRANT gives a user permission, REVOKE removes a permission previously granted, and DENY, in T-SQL, explicitly blocks one.

Data definition: CREATE, ALTER and DROP

CREATE builds a new object. A CREATE TABLE statement names the table and lists its columns, each with a data type and any constraints. ALTER changes an existing object, for example adding a column. DROP removes an object entirely, together with all its data, which is why Walt never runs DROP on a production server without a recent backup and a second person watching.

CREATE TABLE dbo.Stores (
    StoreID   int           NOT NULL PRIMARY KEY,
    StoreName nvarchar(60)  NOT NULL,
    City      nvarchar(60)  NOT NULL,
    OpenedOn  date          NULL
);

ALTER TABLE dbo.Stores ADD Phone varchar(20) NULL;

DROP TABLE dbo.OldPriceImport;

T-SQL groups tables into schemas, which are like folders inside a database; dbo is the default one. A table is often written with its schema name, such as dbo.Stores or Sales.Orders. Schemas help organize large databases and make permissions easier to manage, because a permission can be granted on a whole schema at once.

Reading data with SELECT

SELECT is the statement you will use most. A basic query names the columns to return, the table to read FROM, an optional WHERE clause to filter rows, and an optional ORDER BY clause to sort the results. The examples below run against a small sales table loaded in memory.

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE Stores (StoreID INTEGER PRIMARY KEY, City TEXT NOT NULL);
CREATE TABLE Sales (SaleID INTEGER PRIMARY KEY, StoreID INTEGER REFERENCES Stores(StoreID),
                    SaleDate TEXT NOT NULL, SKU TEXT NOT NULL, Qty INTEGER NOT NULL, Amount REAL NOT NULL);
INSERT INTO Stores VALUES (12, 'North'), (15, 'West'), (18, 'South');
INSERT INTO Sales VALUES
 (1, 12, '2026-11-02', 'PNT-114', 2, 69.98), (2, 12, '2026-11-02', 'BRS-030', 1, 16.42),
 (3, 15, '2026-11-02', 'PNT-114', 1, 34.99), (4, 15, '2026-11-03', 'HMR-200', 1, 24.50),
 (5, 12, '2026-11-03', 'PNT-114', 3, 104.97), (6, 18, '2026-11-03', 'BRS-030', 4, 65.68);
""")

query = """
SELECT SaleDate, SKU, Qty, Amount
FROM Sales
WHERE StoreID = 12
ORDER BY SaleDate, Amount DESC
"""
for row in db.execute(query):
    print(row)

('2026-11-02', 'PNT-114', 2, 69.98)
('2026-11-02', 'BRS-030', 1, 16.42)
('2026-11-03', 'PNT-114', 3, 104.97)

The WHERE clause can combine conditions with AND and OR, compare with operators such as =, <> (not equal), <, >, <= and >=, test ranges with BETWEEN, test lists with IN, and find missing values with IS NULL. Note that a comparison such as Phone = NULL never matches anything, because NULL means unknown; the correct test is Phone IS NULL.

Summarizing with aggregate functions and GROUP BY

Aggregate functions combine many rows into one value: COUNT counts rows, SUM adds, AVG averages, and MIN and MAX find the smallest and largest. On their own, they summarize the whole table. With GROUP BY, they summarize each group separately, which is the heart of most reports. HAVING filters groups after they are formed, in the same way that WHERE filters rows before grouping.

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE Stores (StoreID INTEGER PRIMARY KEY, City TEXT NOT NULL);
CREATE TABLE Sales (SaleID INTEGER PRIMARY KEY, StoreID INTEGER REFERENCES Stores(StoreID),
                    SaleDate TEXT NOT NULL, SKU TEXT NOT NULL, Qty INTEGER NOT NULL, Amount REAL NOT NULL);
INSERT INTO Stores VALUES (12, 'North'), (15, 'West'), (18, 'South');
INSERT INTO Sales VALUES
 (1, 12, '2026-11-02', 'PNT-114', 2, 69.98), (2, 12, '2026-11-02', 'BRS-030', 1, 16.42),
 (3, 15, '2026-11-02', 'PNT-114', 1, 34.99), (4, 15, '2026-11-03', 'HMR-200', 1, 24.50),
 (5, 12, '2026-11-03', 'PNT-114', 3, 104.97), (6, 18, '2026-11-03', 'BRS-030', 4, 65.68);
""")

query = """
SELECT StoreID, COUNT(*) AS Sales, SUM(Qty) AS Units, ROUND(SUM(Amount), 2) AS Total
FROM Sales
GROUP BY StoreID
HAVING SUM(Amount) > 50
ORDER BY Total DESC
"""
for row in db.execute(query):
    print(row)

(12, 3, 6, 191.37)
(18, 1, 4, 65.68)
(15, 2, 2, 59.49)

Combining tables with JOIN

Because a normalized database spreads facts across tables, most real queries join tables back together. A JOIN matches rows from two tables using a condition, usually that a foreign key equals a primary key. An INNER JOIN returns only rows that have a match on both sides. A LEFT JOIN returns every row from the first table, with matching details from the second where they exist and NULL where they do not. RIGHT and FULL joins work similarly from the other side or both sides.

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE Stores (StoreID INTEGER PRIMARY KEY, City TEXT NOT NULL);
CREATE TABLE Sales (SaleID INTEGER PRIMARY KEY, StoreID INTEGER REFERENCES Stores(StoreID),
                    SaleDate TEXT NOT NULL, SKU TEXT NOT NULL, Qty INTEGER NOT NULL, Amount REAL NOT NULL);
INSERT INTO Stores VALUES (12, 'North'), (15, 'West'), (18, 'South');
INSERT INTO Sales VALUES
 (1, 12, '2026-11-02', 'PNT-114', 2, 69.98), (2, 12, '2026-11-02', 'BRS-030', 1, 16.42),
 (3, 15, '2026-11-02', 'PNT-114', 1, 34.99), (4, 15, '2026-11-03', 'HMR-200', 1, 24.50),
 (5, 12, '2026-11-03', 'PNT-114', 3, 104.97), (6, 18, '2026-11-03', 'BRS-030', 4, 65.68);
""")

query = """
SELECT st.City, ROUND(SUM(s.Amount), 2) AS Total
FROM Sales AS s
INNER JOIN Stores AS st ON st.StoreID = s.StoreID
GROUP BY st.City
ORDER BY st.City
"""
for row in db.execute(query):
    print(row)

('North', 191.37)
('South', 65.68)
('West', 59.49)

The short names s and st are table aliases, which keep long queries readable. Aliases for columns, such as AS Total, rename a result column. Joins are where most query mistakes happen: joining on the wrong column, or forgetting a condition so that every row of one table matches every row of the other, can multiply rows and inflate totals. Keiko confesses that her store comparison once showed every store selling three times its real figure because of exactly that.

query = """
SELECT SKU
FROM Sales
"""
result = db.execute(query).fetchall()
print(result)

Changing data: INSERT, UPDATE and DELETE

INSERT adds rows, listing the columns and the values to put in them. UPDATE changes values in existing rows, and DELETE removes rows. Both UPDATE and DELETE take a WHERE clause that chooses which rows to affect, and both affect every row in the table if the WHERE clause is left out. That is Walt's nightmare: UPDATE Products SET Price = 0 with no WHERE sets every price in the company to zero. His habit, worth copying, is to write the WHERE clause first, run it as a SELECT to see exactly which rows match, and only then change SELECT to UPDATE or DELETE, inside a transaction he can roll back.

INSERT INTO dbo.Stores (StoreID, StoreName, City, OpenedOn)
VALUES (24, 'Riverside', 'Riverside', '2027-04-01');

UPDATE dbo.Products
SET    ListPrice = 36.99
WHERE  SKU = 'PNT-114';

DELETE FROM dbo.PriceImport
WHERE  ImportDate < '2026-01-01';

TRUNCATE TABLE removes every row from a table quickly, without logging each row individually, and resets any automatic numbering. It is classed with DDL in SQL Server because it acts on the table as a whole. MERGE, in T-SQL and other dialects, combines insert, update and delete in one statement, which is handy when loading a batch of changes into a table.

Controlling access with GRANT, REVOKE and DENY

DCL statements give and take away permissions. GRANT SELECT ON dbo.Sales TO ReportReaders lets members of the ReportReaders role read the Sales table. REVOKE removes a permission that was granted or denied earlier. DENY, specific to SQL Server, blocks a permission even if the user would otherwise receive it through a role. Permissions are usually granted to roles rather than individual users, so that adding a new analyst to the right role gives them the right access in one step. Lesson 12 returns to this.

GRANT SELECT ON SCHEMA::Reporting TO ReportReaders;
DENY  DELETE ON dbo.Sales TO ReportReaders;
REVOKE INSERT ON dbo.PriceImport FROM PricingApp;

By the end of the hour Keiko has rewritten her store comparison from scratch: one query, joining Sales to Stores, grouped by store and week, with a clear WHERE clause on the dates. It is shorter than the copied version and, she checks carefully, produces the right totals. Walt reviews it and approves, which from Walt is high praise.