Library Carpentry: SQL: SQL Cheat Sheet

Key Points

Introduction to SQL
  • SQL is a powerful language used to interrogate and manipulate relational databases.

  • People working in library- and information-related roles have skills that allow them to use SQL to organize and access data.

Selecting and sorting data
  • SQL is ideal for querying databases

  • SQL queries have a basic query structure starting with SELECT field FROM table with additional keywords and criteria that can be used.

Filtering
  • Use WHERE to filter and retrieve data based on specific conditions.

  • Use AND, OR, and NOT to add additional conditions.

  • Use the comparison keyword LIKE and wildcard characters such as % to match patterns.

Aggregating & calculating values
  • SQL can be used for reporting purposes.

  • Queries can do arithmetic operations on field values.

Ordering and commenting
  • Queries often have the structure: SELECT data FROM table WHERE certain criteria are present.

Joins and aliases
  • Joining two tables in SQL is an good way to analyse datasets, especially when both datasets provide partial answers to questions you want to ask.

  • Creating aliases allows us to spend less time typing, and more time querying!

Saving queries
  • Saving queries as ‘Views’ allows you to save time and avoid repeating the same operation more than once.

Database design
  • Database design is helpful for creating more efficient databases.

Other database tools
  • There are additional database tools beyond DB Browser and SqliteOnline.

Extra challenges (optional)
  • It takes time and practice to learn how to translate plain English queries into SQL queries.

Good Style
  • There are many ways to write an SQL queries, but some look better than others.

SQL Cheat Sheet


Basic query

SELECT column_names
FROM table_name;
SELECT * 
FROM table_name;
SELECT DINSTINCT column_name 
FROM table_name;
SELECT column_names
FROM table_name
WHERE column_name operator value;
SELECT column_names
FROM table_name
WHERE column_name IN (value1, value2, value3);
SELECT column_names
FROM table_name
ORDER BY column_name ASC;

Aggregation

SELECT aggregate_function(column_name)
FROM table_name;
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

Joins and aliases

SELECT column_names
FROM table_name1
JOIN table_name2 
ON table_name1.column_name = table_name2.column_name;
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;

Saving queries

CREATE VIEW viewname AS
SELECT column_names
FROM table_name;

Commenting

-- Select all columns
SELECT * 
-- From the table_name
FROM table_name

Operators

Arithmetic operators + - * /

Comparison operators = < > <= >= <>

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