blog-banner

SQL cheat sheet for developers, with examples (2023)

Last edited on September 21, 2023

0 minute read

    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!

    sql cheat sheet page 1

    Quick jump to a section:Copy Icon


    Getting StartedCopy Icon

    Creating and managing databasesCopy Icon

    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 schemaCopy Icon

    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 tablesCopy Icon

    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 constraintsCopy Icon

    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 dataCopy Icon

    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 dataCopy Icon

    Modifying dataCopy Icon

    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 dataCopy Icon

    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 tablesCopy Icon

    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 dataCopy Icon

    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 operatorsCopy Icon

    Selected logical operators:

    Managing indexesCopy Icon

    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/OpsCopy Icon

    Database accessCopy Icon

    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 investigationCopy Icon

    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.

    sql
    postgresql
    postgres