kota's “memex”

databases

Tables are grouped into databases, and a collection of databases managed by a single SQL server constitutes a cluster.

types

int - Normal 4 byte integer type
smallint - 2 byte int (-32768 to +32767)
char(80) - Fixed-length, blank padded string of up to 80 characters
varchar(80) - Arbitrary-length character string of up to 80 characters
real - Single precision floating-point numbers
date - Store a date Each table is a named collection of rows. Each row of a given table has the same set of columns, and each column is of a specific data type. Whereas columns have a fixed order in each row, it's important to remember that SQL does not guarantee the order of the rows within the table in any way (although they can be explicitly sorted for display).

tables

create

You can create a new table by specifying the table name, along with all column names and their types:

CREATE TABLE weather (
  city    varchar(80),
  temp_lo int,
  temp_hi int,
  prcp    real,
  date    date
);

insert

INSERT INTO cities VALUE ('San Francisco', '(-194.0, 53.0)');

You can also list the columns explicitly so you don't need to know the order of the columns. You can also re-order or omit columns using this form. This form is generally considered better for any non-trivial insert.

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

You can also use COPY to load large amounts of data from flat-text files. This is usually faster as COPY is heavily optimized (but less flexible than INSERT).

COPY weather FROM '/home/user/weather.txt';

update

UPDATE kudos SET item_id = ?, creator_username = ?, frame = ?, emoji = ?, body = ? WHERE id = ?

drop

DROP TABLE tablename;

querying

The select statement is divided into:

SELECT * FROM weather;
-- or
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

You can write expressions with AS, not just simple column references, in the select list. For example:

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

You can use WHERE to select rows where a boolean expression is true:

SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;

You can request that the results are sorted with ORDER BY:

SELECT * FROM weather
    ORDER BY city, temp_lo;

You can request that duplicate rows are removed from the result of a query:

SELECT DISTINCT city
    FROM weather
    ORDER BY city, temp_lo;

join

Queries can also access multiple tables at once, or access the same table in multiple ways at once:

SELECT * FROM weather JOIN cities ON city = name;

Most of the time you'll want to list the columns manually to avoid duplicate information (such as names):

SELECT weather.city, weather.temp_lo, weather.temp_hi,
    weather.prcp, weather.date, cities.location
    FROM weather JOIN cities ON weather.city = cities.name;

There's an issue with the above queries, if you have a city in weather that is not present in cities it will be left out of the result. If we would instead like empty values to be printed, we can use OUTER JOIN:

SELECT *
    FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;

This is called a LEFT OUTER JOIN as the left table will have each of it's rows at least once and the one on the right will have null for any missing values. There are also RIGHT OUTER JOIN and FULL OUTER JOIN.

aggregate functions

Compute a single result from multiple input row. For example, there are aggregate functions count, avg, max, min:

SELECT max(temp_lo) FROM weather;

That would simply print the max value, you usually want other values from that row such as the city name in this case:

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

-- This simpler version will not work. Max cannot be used in the where clause
-- because it must be evaluated before aggregate functions.
SELECT city FROM weather WHERE temp_lo = max(temp_lo);