SQL cheat sheet for developers, with examples (2023)

Most SQL content on the web seems to be written with data analysts in mind. And that’s fine, but developers need SQL too! Your application is only as available and performant as your database, and solid database performance doesn’t just mean knowing how to INNER JOIN or SELECT *, it also means understanding monitoring and ops commands, using EXPLAIN ANALYZE, etc.

So we’re going to cover all of those things…and more!

Whether you’re a developer or an analyst, this SQL cheat sheet is designed to make it quick and easy to find the right SQL commands, and see examples of queries in action. It covers all the fundamentals you need for analytics work and the fundamentals required for basic performance monitoring and query optimization.

A quick note: this cheat sheet uses the PostgreSQL dialect. We’ve also included some CockroachDB-specific commands (marked with an asterisk) where CockroachDB provides useful features that don’t exist in Postgres. However, most of the commands on this cheat sheet will work with most flavors of SQL.

Want a printable version you can hang on your wall? Check out this printer-friendly version of this cheat sheet for easy reference (preview below). Or, scroll down for the entire thing with easy-to-copy code snippets!

Quick jump to a section:

• Getting started

  • Creating and managing databases
  • Creating tables and schema
  • Managing SQL tables
  • Managing SQL constraints
  • Inserting data

• Working with your data

  • Modifying data
  • Querying data
  • Joining tables
  • Aggregating data
  • Logical operators

• Managing indexes

• Administration/Ops

  • Access management
  • Performance investigation

Getting Started

Creating and managing databases

CREATE DATABASE

Creates a new database.

CREATE DATABASE bank;

DROP DATABASE

Delete a database and all of its contents.

DROP DATABASE bank;

SHOW DATABASES*

Show all databases in your cluster.

SHOW DATABASES;

IF EXISTS can be used to prevent errors if we (for example) attempt to delete a database that doesn’t exist.

IF EXISTS can also be used with many other SQL statements and combined with other operators.

Examples:

DROP DATABASE … CASCADE can be used to remove all objects that rely on the database that is being dropped. DROP DATABASE … RESTRICT can be used to prevent the DROP DATABASE command from executing unless the database is empty.

Examples:

Creating tables and schema

CREATE TABLE

Create a new table in the database.

CREATE TABLE users (
        id UUID PRIMARY KEY,
        city STRING,
        name STRING,
        address STRING,
        credit_card STRING,
        dl STRING
);

For each row in the table, you must specify at least a row name (i.e. city) and a datatype (i.e. STRING). But you can also do a lot more with CREATE TABLE statements, such as:

Define a primary key column:

CREATE TABLE users (
        id UUID PRIMARY KEY,
        city STRING,
        name STRING
);

Define a multi-column primary key:

CREATE TABLE users (
        id UUID,
        city STRING,
        name STRING,
        PRIMARY KEY (city, id)
);

Define a foreign key referencing another table in the database:

(In this case, referencing a column called city in table called locations).

CREATE TABLE users (
        id UUID PRIMARY KEY,
        city STRING REFERENCES locations(city),
        name STRING
);

Create an index based on a column:

CREATE TABLE users (
        id UUID PRIMARY KEY,
        city STRING,
        name STRING,
        INDEX (name)
);

Define a default for a column:

In this case, using CockroachDB’s gen_random_uuid() function to generate a random UUID as the default value. (Most database management systems include built-in functions like this for auto-generating certain types of data).

CREATE TABLE users (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        city STRING,
        name STRING
);

Disallow NULL values from a column:

CREATE TABLE users (
        id UUID NOT NULL,
        city STRING,
        name STRING
);

Create a table with a computed column:

CREATE TABLE users (
        id UUID NOT NULL,
        city STRING,
        name STRING,
        name_and_city STRING AS (CONCAT(name, ' ', city)) STORED
);

Specify what happens when a referenced object is updated:

CREATE TABLE users (
        id UUID PRIMARY KEY,
        city STRING REFERENCES locations(city) ON DELETE CASCADE,
        name STRING
);

Create a new table using the results of a query:

CREATE TABLE users_ny(user_id, name, city) 
AS SELECT * FROM users 
WHERE city = 'new york';

Managing SQL tables

ALTER TABLE

Apply a schema change to a table.

ALTER TABLE bank ADD COLUMN active BOOL;

ALTER TABLE is used with subcommands such as:

ADD COLUMN

Add a column.

ALTER TABLE bank ADD COLUMN active BOOL;

DROP COLUMN

Remove a column.

ALTER TABLE bank DROP COLUMN active;

ALTER COLUMN

Change column constraints, datatypes, etc.

ALTER TABLE bank ALTER account_balance TYPE FLOAT;

RENAME COLUMN

Rename a column.

ALTER TABLE bank RENAME COLUMN account_balance TO balance;

RENAME TO

Rename a table.

ALTER TABLE bank RENAME TO users;

DROP TABLE

Remove a table.

DROP TABLE bank;

DROP TABLE … CASCADE can be used to remove all objects (constraints, views, etc.) that rely on the table being dropped.

DROP TABLE … RESTRICT can be used to prevent the DROP TABLE command from executing unless the table is empty.

Use DROP TABLE statements with caution!

Managing SQL constraints

ADD CONSTRAINT

Add a key, check, or unique constraint to a column.

ALTER TABLE users ADD CONSTRAINT id_name_unique UNIQUE (id, name);

DROP CONSTRAINT

Remove a constraint from a column.

ALTER TABLE users DROP CONSTRAINT id_name_unique;

ALTER COLUMN

Add or remove DEFAULT and NOT NULL constraints, change datatypes.

ALTER TABLE subscriptions ALTER COLUMN newsletter SET NOT NULL;

Inserting data

INSERT INTO … VALUES

Insert rows with specified values into a table.

INSERT INTO users (name, city) VALUES('Alice', 'New York');

INSERT INTO … SELECT

Insert rows into a table from the results of a query.

INSERT INTO drivers (id, city, name, address)
    SELECT id, city, name, address FROM users
    WHERE name IN ('Anita Atkinson', 'Devin Jordan');

Working with your data

Modifying data

UPDATE

Update row(s) in a table.

UPDATE users SET address = '201 E Randolph St' WHERE id = '851eb851-eb85-4000-8000-00000000001a';

Note: without a WHERE statement, UPDATE will update the value of the specified column or columns for all rows.

INSERT INTO … ON CONFLICT

Insert a new row, or perform a different action if a conflict with an existing row is detected (i.e., an “upsert”).

INSERT INTO employees (id, name, email) 
VALUES (2, ‘Dennis’, ‘dennisp@weyland.corp’)
ON CONFLICT (id) DO UPDATE;

UPSERT*

Upsert a row into the database.

UPSERT INTO employees (id, name, email) VALUES (6, ‘Lambert’, ‘lambert@weyland.corp`);

Note: By default, CockroachDB uses the primary key column’s value to determine whether or not there is a conflict (i.e., whether an existing row should be updated or a new row should be inserted). More information about upserts in SQL is available on our blog.

DELETE FROM

Delete a specific row or rows.

DELETE FROM promo_codes WHERE code = 'HAPPY50';

Querying data

SELECT … FROM …

Return the values of specific columns in a table.

SELECT id, city, name FROM users;

SELECT * FROM …

Return the values of all columns in a table.

SELECT * FROM users;

LIMIT

Limit the number of rows returned by a query.

SELECT * FROM users LIMIT 5;

OFFSET

Skip the first n rows before returning a query result.

SELECT * FROM users LIMIT 5 OFFSET 5;

Note: in real-world applications with large databases where performance matters, keyset pagination is recommended over using LIMIT/OFFSET because in most circumstances it will execute significantly faster.

WHERE

Filter the results of a query based on a condition or conditions.

SELECT * FROM vehicles WHERE city = 'seattle' AND status = 'available';

GROUP BY

Group the results of a query based on a column.

SELECT city FROM rides
WHERE city IN ('new york', 'chicago', 'seattle') GROUP BY city;

HAVING

Filter a query based on the results of running an aggregate function.

SELECT city, AVG(revenue) as avg FROM rides GROUP BY city
HAVING AVG(revenue) BETWEEN 50 AND 60;

Joining tables

A join statement in SQL looks like this:

SELECT table1.column1, table2.column1, table2.column2
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column1;

Note that “left” refers to the table listed first in your SQL statement, so in the example above, table1 is the left table. The output of that query is a table containing column1 and column2 for all of the rows in table1 and table2 that match on column1.

Types of joins:

Aggregating data

Selected aggregate functions:

Example:

SELECT AVG(balance) FROM accounts WHERE balance > 0;

This SQL query that would return the average value of the balance column from the table accounts, not including rows with a balance of zero.

Logical operators

Selected logical operators:

Managing indexes

CREATE INDEX

Create an index for a table using one or more columns.

CREATE INDEX ON table1 (column1, column2);

ALTER INDEX … RENAME TO

Rename an index.

ALTER INDEX usersname_idx RENAME TO users_name_idx;

DROP INDEX

Remove an index.

DROP INDEX users_name_idx;

Administration/Ops

Database access

CREATE ROLE

Create a SQL user role (group of users).

CREATE ROLE basic_user;

DROP ROLE

Remove a SQL user role.

DROP ROLE basic_user;

CREATE USER

Create a new user.

CREATE USER alex;

DROP USER

Remove a user.

DROP USER alex;

GRANT

Grant privileges to a user or a role.

GRANT DELETE ON TABLE rides TO alex;

REVOKE

Revoke database privileges from a user or role.

REVOKE ALL ON DATABASE defaultdb FROM alex;

SHOW ROLES*

List the roles for all databases in a cluster.

SHOW ROLES;

SHOW USERS*

List the users for all databases in a cluster.

SHOW USERS;

SHOW GRANTS*

View the privileges granted to a user or role.

SHOW GRANTS FOR alex;

Performance investigation

EXPLAIN

View the query plan for a query without executing it.

EXPLAIN SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;

EXPLAIN ANALYZE

Execute a query and generate a physical query plan with execution statistics.

EXPLAIN ANALYZE SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;

Build what you dream. Never worry about your database again. Start building today with CockroachDB.