Code & Clay – Notes to self. Mainly Ruby/Rails.

SQL Notes

SQL keywords are capatalized. SQL statements are terminated with a ;.

SELECT

Select all columns from a table:

SELECT * FROM Authors;

Select a single column from a table:

SELECT LastName FROM Authors;

Retrieve a list of all different values in a column:

SELECT DISTINCT LastName FROM Authors;

WHERE

Retrieve row where column matches:

SELECT * FROM Authors WHERE Language = 'English';

Retrieve a row where the column does not match:

SELECT * FROM Authors WHERE NOT Language = 'English';

Multiple conditions where all must match:

SELECT * FROM Authors
WHERE Language = 'English'
AND LastName = 'Hardinge';

Multiple conditions where either must match:

SELECT * FROM Authors
WHERE Language = 'English'
OR LastName = 'Garner';

ORDER BY

Order the result:

SELECT * FROM Authors ORDER BY LastName;

Return the result in reverse order:

SELECT * FROM Authors ORDER BY LastName DESC;

Order the result alphabetically by last name then language:

SELECT * FROM Authors ORDER BY LastName, Language;

INSERT

Insert a new row into a table:

INSERT INTO Authors (
    LastName,
    Language
)
VALUES (
    'Jansson',
    'Swedish'
);

NULL

Select a row with an empty column:

SELECT * FROM Authors WHERE DateOfDeath IS NULL;

Select a row where a specified column is not empty:

SELECT * FROM Authors WHERE DateOfDeath IS NOT Null;

UPDATE

Update a column in all rows:

UPDATE Authors SET Published = 'true';

Update only specific columns (I’m stretching the example a bit now):

UPDATE Authors SET Read = 'true' WHERE Published = 'true';

Update multiple columns:

UPDATE Authors
SET Read = 'true',
Published = 'true',
WHERE AuthorId = 23;

DELETE

Delete rows:

DELETE FROM Authors WHERE Published = 'false';

Delete all rows:

DELETE FROM Authors;

Functions

Select the smallest value:

SELECT MIN(Titles) FROM Authors;

Select the highest value:

SELECT MAX(Titles) FROM Authors;

Return the count of rows that have Titles set to 3:

SELECT COUNT(*) FROM Authors WHERE Titles = 3;

Calculate an average:

SELECT AVG(Books) FROM Authors;

Sum all the values of a column:

SELECT SUM(Books) FROM Authors;

Select all the records where the given column starts with a given letter:

SELECT Authors WHERE LastName LIKE 'A%';

Select all the records where the given column ends with a letter:

SELECT Authors WHERE LastName LIKE '%s';

Select all the records where a given column contains a given letter:

SELECT Authors WHERE LastName LIKE '%d';

Select all rows where a given column starts and ends with the given letters:

SELECT Authors WHERE LastName LIKE 'A%s';

Select all rows where a given column does not start with the given letter:

SELECT Authors WHERE LastName NOT LIKE `A%`;

Select all rows where the second letter of a column is a given letter:

SELECT Authors WHERE LastName LIKE ' l%';

Select all rows where the first letter of a column begins with one of the given letters:

SELECT Authors WHERE LastName LIKE '[ABC]%';

Select all rows where the first letter of a column falls within an inclusive range:

SELECT Authors WHERE LastName LIKE '[A-F]%';

Select all rows where the first letter of a column does begin with one of:

SELECT Authors WHERE LastName LIKE '[!A-F]%';

IN

Select rows where a column matches:

SELECT Authors WHERE LastName IN ('Aldiss', 'Ballard');

Select rows where a country does not match:

SELECT Authors WHERE LastName NOT IN ('Archer', 'Jonker');

Select all rows where column value falls within an numerical range:

SELECT * Authors WHERE Titles BETWEEN 5 AND 10

Select all rows where column value does not fall within a numerical range:

SELECT * Authors WHERE Titles NOT BETWEEN 5 AND 10

Select all rows where the column name falls within an alphabetical range:

SELECT * Authors WHERE LastName BETWEEN 'Cooper' AND 'Masefield';

ALIAS

Display column under a different name:

SELECT LastName, Books, Language AS Lang FROM Authors;

Display a table under a different name:

SELECT * FROM Authors AS Writers;

JOIN

A JOIN is done ON something.

Join two tables using the AuthorID field:

SELECT *
FROM Publishers
LEFT JOIN Authors
ON Publishers.AuthorID=Authors.AuthorID;

Select intersection:

SELECT *
FROM Publishers
INNER JOIN Authors
ON Publishers.AuthorID = Authors.AuthorID;

Select all of one table plus all that matches in another:

SELECT * 
FROM Publishers
RIGHT JOIN Authors
ON Publishers.AuthorID = Authors.AuthorID;

GROUP BY

List the number of authors by language:

SELECT COUNT(AuthorID)
Language
From Authors
GROUP BY Language;

List number of authors by language, ordered by language with most authors first:

SELECT COUNT(AuthorID)
Language
FROM Authors
GROUP BY Language
ORDER BY COUNT(AuthorID) DESC;

SQL

Create a db:

CREATE DATABASE db;

Drop a db:

DROP DATABASE db;

Create a table:

CREATE TABLE Authors (
    AuthorID int,
    LastName varchar(255),
    Language varchar(255)
);

Drop a table:

DROP TABLE Authors;

Delete all rows within a table:

TRUNCATE TABLE Authors;

Add a column:

ALTER TABLE Authors ADD Birthday DATE;

Delete a colum:

ALTER TABLE Authors DROP COLUMN Birthday;