SQL Commands: A List of Common SQL Statements With Examples

The SQL Commands at a Glance

SQL commands fall into a few groups by what they do. The table lists the ones you will use most, and every section below runs one of them.

DDL is data definition language, DML is data manipulation language, DQL is data query language and TCL is transaction control language. The names matter less than the split: DDL changes the shape of the database, DML changes what is inside it.

CREATE TABLE: Define a Table

CREATE TABLE names the table and lists its columns with their types and rules. Every other command on this page needs a table to work on.

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    grade INTEGER
);

SELECT name, sql FROM sqlite_master WHERE type = 'table';

The page on CREATE TABLE covers rules such as NOT NULL, UNIQUE and DEFAULT.

INSERT: Add Rows

INSERT INTO adds one row or several. List the columns, then the values in the same order.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, grade INTEGER);

INSERT INTO students (name, grade) VALUES ('Ada', 91);
INSERT INTO students (name, grade) VALUES
    ('Boris', 78),
    ('Rosa', 85);

SELECT * FROM students;

The id column filled itself in: an INTEGER PRIMARY KEY gets the next number when you leave it out. More on this in INSERT.

SELECT: Read Rows

SELECT is the command you will write most. Choose the columns, filter rows with WHERE and sort them with ORDER BY.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, grade INTEGER);
INSERT INTO students (name, grade) VALUES ('Ada', 91), ('Boris', 78), ('Rosa', 85), ('Chen', 62);

SELECT name, grade
FROM students
WHERE grade >= 80
ORDER BY grade DESC;

Only rows that pass the WHERE test come back, highest grade first. See SELECT basics and WHERE for the details.

UPDATE: Change Rows

UPDATE sets new values on rows that already exist. The WHERE clause decides which rows; leave it out and every row changes.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, grade INTEGER);
INSERT INTO students (name, grade) VALUES ('Ada', 91), ('Boris', 78), ('Rosa', 85);

UPDATE students
SET grade = 82
WHERE name = 'Boris';

SELECT * FROM students;

When you are not sure what a WHERE clause matches, run it as a SELECT first. The UPDATE page shows more patterns.

DELETE: Remove Rows

DELETE FROM removes the rows that match its WHERE clause. Like UPDATE, it affects every row if the clause is missing.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, grade INTEGER);
INSERT INTO students (name, grade) VALUES ('Ada', 91), ('Boris', 78), ('Rosa', 85), ('Chen', 62);

DELETE FROM students
WHERE grade < 70;

SELECT * FROM students;

Chen's row is gone and the others are untouched. See DELETE.

ALTER TABLE and DROP TABLE: Change or Remove a Table

ALTER TABLE changes an existing table without losing its rows. DROP TABLE removes the table completely.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO students (name) VALUES ('Ada'), ('Boris');

ALTER TABLE students ADD COLUMN email TEXT;
UPDATE students SET email = lower(name) || '@school.test';
SELECT * FROM students;

CREATE TABLE old_notes (id INTEGER PRIMARY KEY, body TEXT);
DROP TABLE old_notes;
SELECT name FROM sqlite_master WHERE type = 'table';

A new column starts as NULL in existing rows until you fill it. ALTER and DROP TABLE lists what SQLite allows.

JOIN: Combine Tables

A JOIN reads rows from two tables at once, matched on a column they share.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE enrollments (student_id INTEGER, course TEXT);

INSERT INTO students (name) VALUES ('Ada'), ('Boris'), ('Rosa');
INSERT INTO enrollments VALUES (1, 'Math'), (1, 'Physics'), (3, 'Art');

SELECT students.name, enrollments.course
FROM students
INNER JOIN enrollments ON enrollments.student_id = students.id
ORDER BY students.name;

Boris has no enrollments, so an INNER JOIN leaves him out. A LEFT JOIN would keep him, with an empty course.

GROUP BY: Summarize Rows

GROUP BY collapses rows that share a value into one row, and an aggregate function such as COUNT or AVG summarizes each group.

CREATE TABLE enrollments (student TEXT, course TEXT, score INTEGER);
INSERT INTO enrollments VALUES
    ('Ada', 'Math', 91), ('Boris', 'Math', 78),
    ('Ada', 'Art', 88), ('Rosa', 'Art', 95), ('Chen', 'Art', 70);

SELECT course, COUNT(*) AS students, AVG(score) AS average
FROM enrollments
GROUP BY course
ORDER BY course;

Aggregate functions and GROUP BY and HAVING go further.

BEGIN, COMMIT and ROLLBACK: Group Changes

A transaction makes several changes count as one. COMMIT keeps them all; ROLLBACK undoes them all.

CREATE TABLE accounts (name TEXT, balance INTEGER);
INSERT INTO accounts VALUES ('Ada', 100), ('Boris', 50);

BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE name = 'Ada';
UPDATE accounts SET balance = balance + 30 WHERE name = 'Boris';
COMMIT;

BEGIN;
UPDATE accounts SET balance = 0;
ROLLBACK;

SELECT * FROM accounts;

The transfer was committed and the second change was rolled back, so Ada ends with 70 and Boris with 80. Transactions explains when to use them.

Where SQLite Differs

The commands on this page are standard SQL, and what you learn here carries over to MySQL and PostgreSQL. The runnable examples are written for SQLite, though, and three details change on the larger databases:

• Listing tables. The examples read sqlite_master, SQLite's own schema table. MySQL uses SHOW TABLES, and PostgreSQL uses the information_schema.tables view (or \dt in psql).
• Automatic ids. A column declared INTEGER PRIMARY KEY numbers new rows by itself in SQLite, which is why the INSERT examples leave id out. MySQL needs AUTO_INCREMENT on the column, and PostgreSQL needs an identity column (GENERATED BY DEFAULT AS IDENTITY) or SERIAL.
• Permissions and schema changes. SQLite has no GRANT or REVOKE, because a SQLite database is a single file with no user accounts, and its ALTER TABLE cannot change a column's type.