SQL #

Different SQL software have slightly different syntax. Check before proceeding.

Use single quote… mostly probably.

(Maybe) Useful resources #

SQL Teaching - The easiest tutorial to learn SQL

Basic operations #

CREATE TABLE & INSERT INTO #

-- SQLite
CREATE TABLE books (
    id INTEGER PRIMARY KEY NOT NULL,
    title TEXT,
    rating INTEGER
);

INSERT INTO books VALUES (1, 'Book one', 3);
INSERT INTO books VALUES (2, 'Book two', 2);
INSERT INTO books VALUES (3, 'Book three', 5);

SELECT #

ORDER BY is case sensitive.

-- SQLite
SELECT * FROM books;

SELECT title, rating FROM books WHERE rating > 2 ORDER BY rating DESC;

-- ORDER BY is case sensitive
SELECT * FROM books ORDER BY LOWER(title);
SELECT * FROM books ORDER BY title COLLATE NOCASE;

SELECT DISTINCT rating FROM books;

UPDATE #

UPDATE books WHERE id = 3 SET rating = 4;

DELETE #

DELETE FROM books WHERE id > 1 AND rating < 2;

JOIN #

SQLite only has INNER JOIN and LEFT JOIN.

Refs:

A Visual Explanation of SQL Joins

Use relational table (character_actor) to display info: (For inner joins, the order of table, aka FROM which and JOIN which, is irrelevant.)

-- SQLite
SELECT character.name, actor.name
FROM character
INNER JOIN character_actor
ON character.id = character_actor.character_id
INNER JOIN actor
ON character_actor.actor_id = actor.id;

Or use AS to simplify the query:

-- SQLite
SELECT c.name AS character, a.name AS actor
FROM character AS c
LEFT JOIN character_actor AS ca
ON c.id = ca.character_id
LEFT JOIN actor AS a
ON ca.actor_id = a.id;

Functions #

Function	Description
`COUNT(table.col)`	Number of rows
`COUNT(table.col) AS [Name for col]`	Show with name
`SUM(...)`
`AVG(...)`
`MAX(...)`
`MIN(...)`
`SUBSTR(table.col, start, length)`	1-based-index substring, omit `length` for all the rest of the string

Statements #

LIKE #

In SQLite, MySQL, and SQL Server, LIKE is case insensitive by default. In PostgreSQL, it is case sensitive by default (use ILIKE).

String wildcards:

%: 0, 1, or multiple characters
_: 1 character

CASE (if else) #

CASE WHEN condition1 THEN value1
WHEN condition2 THEN value2
...
ELSE ...
END AS new_col