# Learn CockroachDB SQL

This tutorial walks you through some of the most essential CockroachDB SQL statements using an interactive SQL shell connected to a temporary, single-node CockroachDB cluster.

For a complete list of supported SQL statements and related details, see SQL Statements.

## Start CockroachDB

If you haven't already, install CockroachDB.

Then run the `cockroach demo` command:

    $ cockroach demo

This starts a single-node, temporary cluster with the `movr` dataset pre-loaded.

## Show tables

To see all tables in the active database, use the `SHOW TABLES` statement or the `\dt` shell command:

    > SHOW TABLES;

              table_name
    +----------------------------+
      promo_codes
      rides
      user_promo_codes
      users
      vehicle_location_histories
      vehicles
    (6 rows)

## Create a table

Suppose that you want MovR to offer ride-sharing services, in addition to vehicle-sharing services. You'll need to add a table for drivers to the `movr` database. To create a table, use `CREATE TABLE` followed by a table name, the column names, and the data type and constraint, if any, for each column:

    > CREATE TABLE drivers (
        id UUID NOT NULL,
        city STRING NOT NULL,
        name STRING,
        dl STRING UNIQUE,
        address STRING,
        CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
    );

Table and column names must follow these rules. Also, when you do not explicitly define a primary key, CockroachDB will automatically add a hidden `rowid` column as the primary key.

To avoid an error in case the table already exists, you can include `IF NOT EXISTS`:

    > CREATE TABLE IF NOT EXISTS drivers (
        id UUID NOT NULL,
        city STRING NOT NULL,
        name STRING,
        dl STRING UNIQUE,
        address STRING,
        CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
    );

To show all of the columns from a table, use the `SHOW COLUMNS FROM <table>` statement or the `\d <table>` shell command:

    > SHOW COLUMNS FROM drivers;

      column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden
    +-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
      id          | UUID      |    false    | NULL           |                       | {primary} |   false
      city        | STRING    |    false    | NULL           |                       | {primary} |   false
      name        | STRING    |    true     | NULL           |                       | {}        |   false
      dl          | STRING    |    true     | NULL           |                       | {}        |   false
      address     | STRING    |    true     | NULL           |                       | {}        |   false
    (5 rows)

## Insert rows

To insert a row into a table, use `INSERT INTO` followed by the table name and then the column values listed in the order in which the columns appear in the table:

    > INSERT INTO drivers VALUES
        ('c28f5c28-f5c2-4000-8000-000000000026', 'new york', 'Petee', 'ABC-1234', '101 5th Ave');

If you want to pass column values in a different order, list the column names explicitly and provide the column values in the corresponding order:

    > INSERT INTO drivers (name, city, dl, address, id) VALUES
        ('Adam Driver', 'chicago', 'DEF-5678', '201 E Randolph St', '1eb851eb-851e-4800-8000-000000000006');

To insert multiple rows into a table, use a comma-separated list of parentheses, each containing column values for one row:

    > INSERT INTO drivers VALUES
        ('8a3d70a3-d70a-4000-8000-00000000001b', 'seattle', 'Eric', 'GHI-9123', '400 Broad St'),
        ('9eb851eb-851e-4800-8000-00000000001f', 'new york', 'Harry Potter', 'JKL-456', '214 W 43rd St');

Default values are used when you leave specific columns out of your statement, or when you explicitly request default values. For example, both of the following statements create a row where the `name`, `dl`, and `address` entries each contain their default value, in this case `NULL`:

    > INSERT INTO drivers (id, city) VALUES
        ('70a3d70a-3d70-4400-8000-000000000016', 'chicago');

    > INSERT INTO drivers (id, city, name, dl, address) VALUES
        ('b851eb85-1eb8-4000-8000-000000000024', 'seattle', DEFAULT, DEFAULT, DEFAULT);

    > SELECT * FROM drivers WHERE id in ('70a3d70a-3d70-4400-8000-000000000016', 'b851eb85-1eb8-4000-8000-000000000024');

                       id                  |  city   | name |  dl  | address
    +--------------------------------------+---------+------+------+---------+
      70a3d70a-3d70-4400-8000-000000000016 | chicago | NULL | NULL | NULL
      b851eb85-1eb8-4000-8000-000000000024 | seattle | NULL | NULL | NULL
    (2 rows)

## Create an index

Indexes help locate data without having to look through every row of a table. They're automatically created for the primary key of a table and any columns with a `UNIQUE` constraint.

To create an index for non-unique columns, use `CREATE INDEX` followed by an optional index name and an `ON` clause identifying the table and column(s) to index. For each column, you can choose whether to sort ascending (`ASC`) or descending (`DESC`).

    > CREATE INDEX name_idx ON users (name DESC);

You can create indexes during table creation as well; just include the `INDEX` keyword followed by an optional index name and the column(s) to index:

    > CREATE TABLE IF NOT EXISTS drivers (
        id UUID NOT NULL,
        city STRING NOT NULL,
        name STRING,
        dl STRING,
        address STRING,
        INDEX name_idx (name),
        CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
    );

## Show indexes

To show the indexes on a table, use `SHOW INDEX FROM` followed by the name of the table:

    > SHOW INDEX FROM users;

      table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit
    +------------+------------+------------+--------------+-------------+-----------+---------+----------+
      users      | primary    |   false    |            1 | city        | ASC       |  false  |  false
      users      | primary    |   false    |            2 | id          | ASC       |  false  |  false
      users      | name_idx   |    true    |            1 | name        | DESC      |  false  |  false
      users      | name_idx   |    true    |            2 | city        | ASC       |  false  |   true
      users      | name_idx   |    true    |            3 | id          | ASC       |  false  |   true
    (5 rows)

## Query a table

To query a table, use `SELECT` followed by a comma-separated list of the columns to be returned and the table from which to retrieve the data. You can also use the `LIMIT` clause to restrict the number of rows retrieved:

    > SELECT name FROM users LIMIT 10;

            name
    +-------------------+
      William Wood
      Victoria Jennings
      Tyler Dalton
      Tony Ortiz
      Tina Miller
      Taylor Cunningham
      Susan Morse
      Steven Lara
      Stephen Diaz
      Sarah Wang DDS
    (10 rows)

To retrieve all columns, use the `*` wildcard:

    > SELECT * FROM users LIMIT 10;

                       id                  |   city    |        name        |            address             | credit_card
    +--------------------------------------+-----------+--------------------+--------------------------------+-------------+
      c28f5c28-f5c2-4000-8000-000000000026 | amsterdam | Maria Weber        | 14729 Karen Radial             | 5844236997
      c7ae147a-e147-4000-8000-000000000027 | amsterdam | Tina Miller        | 97521 Mark Extensions          | 8880478663
      cccccccc-cccc-4000-8000-000000000028 | amsterdam | Taylor Cunningham  | 89214 Jennifer Well            | 5130593761
      d1eb851e-b851-4800-8000-000000000029 | amsterdam | Kimberly Alexander | 48474 Alfred Hollow            | 4059628542
      19999999-9999-4a00-8000-000000000005 | boston    | Nicole Mcmahon     | 11540 Patton Extensions        | 0303726947
      1eb851eb-851e-4800-8000-000000000006 | boston    | Brian Campbell     | 92025 Yang Village             | 9016427332
      23d70a3d-70a3-4800-8000-000000000007 | boston    | Carl Mcguire       | 60124 Palmer Mews Apt. 49      | 4566257702
      28f5c28f-5c28-4600-8000-000000000008 | boston    | Jennifer Sanders   | 19121 Padilla Brooks Apt. 12   | 1350968125
      80000000-0000-4000-8000-000000000019 | chicago   | Matthew Clay       | 49220 Lisa Junctions           | 9132291015
      851eb851-eb85-4000-8000-00000000001a | chicago   | Samantha Coffey    | 6423 Jessica Underpass Apt. 87 | 9437219051
    (10 rows)

To filter the results, add a `WHERE` clause identifying the columns and values to filter on:

    > SELECT id, name FROM users WHERE city = 'san francisco';

                       id                  |       name
    ---------------------------------------+-------------------
      75c28f5c-28f5-4400-8000-000000000017 | William Wood
      7ae147ae-147a-4000-8000-000000000018 | Alfred Garcia
      80000000-0000-4000-8000-000000000019 | Matthew Clay
      851eb851-eb85-4000-8000-00000000001a | Samantha Coffey
      8a3d70a3-d70a-4000-8000-00000000001b | Jessica Martinez
    (5 rows)

To sort the results, add an `ORDER BY` clause identifying the columns to sort by. For each column, you can choose whether to sort ascending (`ASC`) or descending (`DESC`).

    > SELECT city, type, current_location FROM vehicles ORDER BY city, type DESC;

          city      |    type    |        current_location
    +---------------+------------+--------------------------------+
      amsterdam     | skateboard | 19202 Edward Pass
      boston        | scooter    | 19659 Christina Ville
      chicago       | skateboard | 69721 Noah River
      detroit       | scooter    | 43051 Jonathan Fords Suite 36
      los angeles   | skateboard | 49164 Anna Mission Apt. 38
      minneapolis   | scooter    | 62609 Stephanie Route
      minneapolis   | scooter    | 57637 Mitchell Shoals Suite 59
      new york      | skateboard | 64110 Richard Crescent
      new york      | scooter    | 86667 Edwards Valley
      paris         | skateboard | 2505 Harrison Parkway Apt. 89
      rome          | bike       | 64935 Matthew Flats Suite 55
      san francisco | skateboard | 81472 Morris Run
      san francisco | scooter    | 91427 Steven Spurs Apt. 49
      seattle       | bike       | 37754 Farmer Extension
      washington dc | scooter    | 47259 Natasha Cliffs
    (15 rows)

## Update rows

To update rows in a table, use `UPDATE` followed by the table name, a `SET` clause identifying the columns to update and their new values, and a `WHERE` clause identifying the rows to update:

    > UPDATE promo_codes SET (description, rules) = ('EXPIRED', '{"type": "percent_discount", "value": "0%"}') WHERE expiration_time < '2019-01-22 03:04:05+00:00';

    > SELECT code, description, rules FROM promo_codes LIMIT 10;

                 code            | description |                    rules
    -----------------------------+-------------+----------------------------------------------
      0_explain_theory_something | EXPIRED     | {"type": "percent_discount", "value": "0%"}
      100_address_garden_certain | EXPIRED     | {"type": "percent_discount", "value": "0%"}
      101_system_skin_night      | EXPIRED     | {"type": "percent_discount", "value": "0%"}
      102_card_professional_kid  | EXPIRED     | {"type": "percent_discount", "value": "0%"}
      103_now_project_focus      | EXPIRED     | {"type": "percent_discount", "value": "0%"}
      104_long_become_prove      | EXPIRED     | {"type": "percent_discount", "value": "0%"}
      105_republican_guess_arm   | EXPIRED     | {"type": "percent_discount", "value": "0%"}
      106_court_especially_plan  | EXPIRED     | {"type": "percent_discount", "value": "0%"}
      107_she_matter_ten         | EXPIRED     | {"type": "percent_discount", "value": "0%"}
      108_wind_marriage_for      | EXPIRED     | {"type": "percent_discount", "value": "0%"}
    (10 rows)

If a table has a primary key, you can use that in the `WHERE` clause to reliably update specific rows; otherwise, each row matching the `WHERE` clause is updated. When there's no `WHERE` clause, all rows in the table are updated.

## Delete rows

To delete rows from a table, use `DELETE FROM` followed by the table name and a `WHERE` clause identifying the rows to delete:

    > DELETE FROM promo_codes WHERE description = 'EXPIRED';

    DELETE 669

Just as with the `UPDATE` statement, if a table has a primary key, you can use that in the `WHERE` clause to reliably delete specific rows; otherwise, each row matching the `WHERE` clause is deleted. When there's no `WHERE` clause, all rows in the table are deleted.

## Remove a table

When you no longer need a table, use `DROP TABLE` followed by the table name to remove the table and all its data:

    > DROP TABLE drivers;

## What's next?

- Explore all SQL Statements
- Use the built-in SQL client to execute statements from a shell or directly from the command line
- Install the client driver for your preferred language and build an app
- Explore core CockroachDB features like automatic replication, rebalancing, and fault tolerance