SQL

• Data represented in terms of set theory:
• Sets: Relations, tables
• Set members: tuples, rows; attributes, fields

• Interact with data:
• Retrieving data: SELECT <attributes> FROM <relations>
• joining sets
• cross join: SELECT a.b,c.d FROM a,c
• inner/outer join: SELECT a.b,c.d FROM a left outer join b on a.id=b.id
• union/intersect: SELECT a.b FROM a UNION SELECT c.d FROM c
• qualifying sets
• where: SELECT a.b,c.d FROM a,c WHERE a.id=c.id AND a.id>10
• except: SELECT a.b FROM a EXCEPT c.d FROM c
• limiting sets
• SELECT a.b, c.d FROM a,c LIMIT 10 OFFSET 1
• sorting sets
• SELECT a.b FROM a ORDER BY a.b DESC
• grouping & aggregation of sets:
• SELECT a.b,count(a.id) FROM a GROUP BY 1
• Storing data: INSERT INTO a VALUES(a₁,a₂, ...)
• Deleting data: DELETE FROM a WHERE a.id>5
• Modifying data (delete + insert): UPDATE a SET a.id=6 WHERE a.id=5

• SQL datatypes:
• Numeric: all unsigned
• smallint, int2 (2 bytes)
• int, int4 (4 bytes)
• bigint, int8 (8 bytes)
• real (4 bytes)
• double (8 bytes)
• numeric (user specified): numeric(digits,decimal places)
• Character:
• char(n) (4 + n bytes )
• varchar(n) ( 4 + n bytes)
• text (4 + n bytes)
• Date/time:
• timestamp (with time zone)
• interval
• date
• time (with time zone)
• Boolean
• Geometric
• Network Address
• Bitstring

• SQL operators
• Logical: AND, OR, NOT
• Comparison: <, >, <=, >=, <>, !=, =
• Mathematical: +, -, *, /
• Strings: ||, LIKE, ~, ~*
• Conditional: CASE
• Relation creation & manipulation:
• CREATE TABLE a (id int, b bigint, description text)
• ALTER TABLE a ADD COLUMN c int2
• DROP TABLE a
• SQL & transactions:
• BEGIN
• END
• ROLLBACK
• SQL & administration:
• CREATE/ALTER/DROP USER
• CREATE/DROP DATABASE
• VACUUM (ANALYZE)
• GRANT/REVOKE