SQL #

Different SQL software have slightly different syntax. Check before proceeding. Most examples on this page use SQLite because of sql.js compatibility.

Use single quotes… mostly probably.

(Maybe) Useful resources #

Edit tables #

CREATE TABLE & INSERT INTO (add row) #

CREATE TABLE IF NOT EXISTS books (
    id INTEGER PRIMARY KEY NOT NULL,
    title TEXT UNIQUE,
    rating REAL
);

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

INSERT INTO books (id, title) VALUES (4, 'Book Four');

UPDATE #

UPDATE books SET rating = 4 WHERE id = 3;

DELETE (row) #

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

-- delete everything
DELETE FROM books

ALTER TABLE (add or delete column) #

-- add column
ALTER TABLE books ADD author TEXT;

-- delete column
ALTER TABLE books DROP author;

-- rename table
ALTER TABLE books RENAME TO book_collection;

DROP (delete table) #

DROP TABLE IF EXISTS books;

Inspect tables #

SELECT #

Order of execution:

FROM mytable1
    JOIN mytable2
      ON mytable1.column = mytable2.column
    WHERE condition1
    GROUP BY col
    HAVING condition2

SELECT DISTINCT col, FUNC(col), ...

    ORDER BY col ASC/DESC
    LIMIT count OFFSET count;

ORDER BY is case sensitive.

Some examples:

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;

Nested SELECT:

-- Show European Countries' population as % of Germany population
SELECT
name,
CONCAT(ROUND(population / (SELECT population
                            FROM world
                            WHERE name = 'Germany') * 100), '%') AS 'percentage'
FROM world
WHERE continent = 'Europe'

Self-compare:

-- Find largest country in each continent
SELECT continent, name, area FROM world AS x
WHERE area > ALL
    (SELECT area FROM world AS y
    WHERE y.continent = x.continent
    AND y.name != x.name
    AND area > 0)

GROUP BY & HAVING #

SELECT group_col, FUNC(col) AS Result, ...
FROM mytable
WHERE condition
GROUP BY group_col
HAVING group_condition;

Example:

-- Find continents where all countries have population <= 25000000
SELECT continent, MAX(population) FROM world
GROUP BY continent
HAVING MAX(population) <= 25000000

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.)

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:

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;

An extreme self JOIN example (SQLZoo Self JOIN Q10):

SELECT T1.num, T1.company, T1.name, T2.num, T2.company
FROM
    (SELECT DISTINCT a.company, a.num, stopb.name
    FROM route a JOIN route b ON
      (a.company=b.company AND a.num=b.num)
      JOIN stops stopa ON (a.stop=stopa.id)
      JOIN stops stopb ON (b.stop=stopb.id)
    WHERE stopa.name='Craiglockhart'
    AND stopb.name != 'Lochend') AS T1

JOIN
    (SELECT DISTINCT c.company, c.num, stopc.name
    FROM route c JOIN route d ON
      (c.company=d.company AND c.num=d.num)
      JOIN stops stopc ON (c.stop=stopc.id)
      JOIN stops stopd ON (d.stop=stopd.id)
    WHERE stopc.name !='Craiglockhart'
    AND stopd.name = 'Lochend') AS T2
ON T1.name = T2.name
ORDER BY T1.num, T1.company, T1.name, T2.num, T2.company

Functions #

Function	Description
`COUNT(table.col)`	Number of rows
`COUNT(table.col) AS 'Name for col'`	Show with name
`SUM(...)`
`AVG(...)`
`MAX(...)`
`MIN(...)`
`ROUND(..., n)`	Round to n decimals. Could be negative.
`SUBSTR(table.col, start, length)`	1-based-index substring, omit `length` for all the rest of the string
`CONCAT(str1, str2, ...)`	Concatenate strings (can use col name)
`COALESCE(col1, col2, ...)`	Mix columns with priority = order

CONCAT example:

SELECT capital, name
  FROM world
 WHERE capital LIKE concat('%', name, '%')

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