SQL Keywords and Commands: SELECT, DDL, DML and Transactions

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

SELECT reads a result set. FROM supplies the inputs, WHERE filters rows, GROUP BY forms groups, HAVING filters those groups, and ORDER BY determines presentation order. JOIN combines related table inputs, while UNION combines compatible query results. These are parts of statements rather than commands to execute separately. DISTINCT removes duplicate selected rows, and CASE is an expression that chooses a value according to conditions.

INSERT adds rows, UPDATE changes selected rows and DELETE removes selected rows. These are commonly called data manipulation language, or DML. UPDATE and DELETE need special attention to their WHERE conditions: without one they can affect every row. INSERT should name the destination columns explicitly so its values do not silently depend on table column order. SELECT is sometimes classified separately as a query language operation; the classification name is less important than understanding its effect.

CREATE, ALTER and DROP define, modify and remove objects such as tables, views and indexes. This family is called data definition language, or DDL. DROP TABLE removes the table itself; DELETE removes rows while leaving the table available. TRUNCATE is another table-clearing operation in several engines, with permission, trigger and transaction behaviour that differs from DELETE. SQLite does not provide a TRUNCATE command. Do not substitute these operations merely because they can all make a table appear empty.

Transaction commands group changes into a unit. COMMIT makes a transaction’s changes durable; ROLLBACK abandons uncommitted changes. The transaction-start syntax differs between engines. GRANT and REVOKE manage permissions in server databases, while SQLite does not have the same server user-and-role privilege system. Reserved keywords also differ by dialect and version. Prefer descriptive unquoted names such as customer_status rather than naming a column order or group and requiring special quoting throughout the application.

The demonstrated SELECT, GROUP BY and COUNT syntax is shared by all four systems. Database creation, transaction-start commands, table alteration and permission commands have substantial differences. SQLite creates a database by opening a file and does not implement CREATE DATABASE, DROP DATABASE or server-style GRANT.

Check the engine-specific syntax and version requirements in PostgreSQL SQL commands.

Write a read-only summary showing how many sales have a known amount and how many rows exist in total.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT COUNT(*) AS all_sales,COUNT(amount) AS known_amounts FROM sales;

all_sales | known_amounts
6 | 5

SELECT performs the read. COUNT(*) includes every sale record, while COUNT(amount) includes only present amounts. The query returns one summary row because no GROUP BY is requested. It does not UPDATE missing amounts or alter the amount column.

Identify every keyword in the status-summary example and classify its role: SELECT starts the reading statement, GROUP BY forms groups and ORDER BY controls presentation. Rewrite it with WHERE status='paid' and confirm only the paid summary remains. In a disposable table, compare DELETE and DROP by checking whether a later SELECT can still refer to the table object.