SQL Cheat Sheet – Quick and Easy SQL Command Reference Examples

SQL Cheat Sheet – Quick and Easy SQL Command Reference Examples

This SQL cheat sheet is designed to make it quick and easy to find that SQL command that’s right on the tip of your tongue, or see an example of a common query in action.

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.

Coming soon: It’ll also be available in a printer-friendly, downloadable PDF form so that you can print it out and reference it easily from your desk.


Quick jump to a section:


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 IF EXISTS bank; CREATE DATABASE IF NOT EXISTS bank;

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

Examples:

DROP DATABASE bank CASCADE; DROP DATABASE bank RESTRICT;

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).

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;

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:

Join Type Description
INNER JOIN Return records with matching values in both tables.
LEFT JOIN Return all records from the left table and matching values from the right table.
RIGHT JOIN Return all records from the right table and matching values from the left table.
FULL JOIN Return all records with matching values in either table.
UNION “Stack” the tables, with rows from the left table returned first, followed by rows from the right table.

Aggregating data

Selected aggregate functions:

Function Use it to…
COUNT() Count the number of rows.
AVG() Average the values in a column.
SUM() Add the values in a column.
MIN() Return the lowest value in a column.
MAX() Return the highest value in a column.

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:

Operator Use it to…
ALL Return TRUE if all values in a subquery meet the specified condition.
AND Return records for which the conditions separated by AND are true.
ANY Return TRUE if any values in a subquery meet the specified condition.
BETWEEN Return records from the query that fall between two specified values.
EXISTS Return TRUE if any record exists in the subquery.
IN Specify multiple values in a WHERE query without having to use OR between each value.
LIKE Return records that match a specified pattern.
NOT Return records that do not meet the specified condition.
OR Return records for which any of the conditions separated by OR are true.

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, a free, serverless distributed SQL database.

Keep Reading

Upsert in SQL: What is an Upsert, and When Should You Use One?

Upserts are useful for anyone who works with a database to know, but the term “upsert” might not even appear in your …

Read More
3 Tips For Startups Who Chose CockroachDB Over Postgres

It’s a bit of a race, isn’t it? You have to get your MVP out the door quickly and you need to use the right technology …

Read More
x
Developer Resources