SQL Cheat Sheet

Last updated on 2024-03-26 | Edit this page

Basic query

SQL

SELECT column_names
FROM table_name;
  • selects only the specified columns from a table.

SQL

SELECT * 
FROM table_name;
  • select all of the columns in a table.

SQL

SELECT DISTINCT column_name 
FROM table_name;
  • selects only the unique values from a table.

SQL

SELECT column_names
FROM table_name
WHERE column_name operator value;
  • selects only the data that meets certain criteria.
  • you can use operators =,<,>, etc
  • you can also combine tests using AND, OR in the WHERE clause.

SQL

SELECT column_names
FROM table_name
WHERE column_name IN (value1, value2, value3);
  • selects only the data where column_name equals to value1, value2, and so on.

SQL

SELECT column_names
FROM table_name
ORDER BY column_name ASC;
  • selects only the specified columns from a table, sort the results by a column in ASC (ascending) or DESC (descending) order.

Aggregation

SQL

SELECT aggregate_function(column_name)
FROM table_name;
  • Aggregate results by grouping records based on value and calculating combined values in groups.
  • E.g. SELECT COUNT(*) FROM table_name will display the total number of records.
  • You can use aggregate functions COUNT, SUM, MAX, MIN, AVG.

SQL

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
  • GROUP BY tells SQL what field or fields we want to use to aggregate the data. If we want to group by multiple fields, we give GROUP BY a comma separated list.

SQL

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
  • The HAVING keyword works exactly like the WHERE keyword, but uses aggregate functions instead of database fields.

Joins and aliases

SQL

SELECT column_names
FROM table_name1
JOIN table_name2 
ON table_name1.column_name = table_name2.column_name;
  • Combine data from two tables where the values of column_name in the two tables are the same.
  • Instead of ON, you can use the USING keyword as a shorthand. E.g. USING (column_name).

SQL

SELECT alias1.column_name1, alias1.column_name2, alias2.column_name3
FROM table_name1 AS alias1
JOIN table_name2 AS alias2
ON alias1.column_name = alias2.column_name;
  • we can use aliases to assign new names to things in the query.
  • we can use as to rename column names too. E.g. SELECT journal_title AS journal.

Saving queries

SQL

CREATE VIEW viewname AS
SELECT column_names
FROM table_name;
  • create a stored query or virtual table in database for repeated queries

Commenting

SQL

-- Select all columns
SELECT * 
-- From the table_name
FROM table_name
  • we can create comments in SQL queries to help us/others understand the query better

Operators

Arithmetic operators + - * /

Comparison operators = < > <= >= <>

Logical operators ALL AND ANY BETWEEN EXISTS IN LIKE NOT OR SOME