sql – language for structured databases queries

Querying

SELECT

• is used to select which columns to include
• can use AS operator with each column to alias its name
• uses the ALL keyword by default

FROM

• identifies tables that contain desired columns
• can use JOIN, INNER JOIN, etc.
• can use AS operator with each table to alias its name
• can use a subquery to generate a temporary table

WHERE

• filters rows meeting specified conditions

GROUP BY

• groups (combines) rows based on a common value

ORDER BY

• sorts rows by column(s)
• should also use ASC or DESC
• can use an expression function

Altering

• TABLE
  • RENAME
  • ALTER
    • COLUMN
      • ADD
      • RENAME
      • DROP
      • ALTER
        • SET DEFAULT
        • SET NULL

Updating

UPDATE

SET

FROM

WHERE

RETURNING (Postgres-only)

TOOLS

psql

• \list: list all databases
• \c connect to a database
• \dt list tables in current database
• \d+ describe table

Other

• DB Browser for SQLite

REFERENCES

• SQL. Wikipedia.
• SQL Statement Syntax. MySQL Reference.
• The SQL Language. PostgresSQL Manual.
• SQL As Understood By SQLite. SQLite.
• Query Reference. Google BigQuery Documentation.
• Streaming SQL Concepts. Amazon Kinesis Analytics Developer Guide.
• Modern SQL. Markus Winand.
• SQLite Tutorial.

Books

• Learning SQL. Alan Beaulieu. O’Reilly. April 27, 2009.