Information Systems - SQL

An incomplete list of some of the more important SQL commands

1. DDL / Creating Tables

Defining Tables

attr means attribute. With options (A|B), the first is the default.

CREATE TABLE R(
   attr1 TYPE (NULL | NOT NULL | DEFAULT "value" | UNIQUE | PRIMARY KEY | REFERENCES table2 (attr6)),
   ...
   CHECK (expression AND attr1 > 0 AND LENGTH(attr1) < 5),
   PRIMARY KEY (attr1, attr2),
   CONSTRAINT constraint_name UNIQUE (attr1, attr2)          for compound uniqueness
   FOERIGN KEY (attr3, attr4) REFERENCES table2 (attr6, attr7) 
      ON DELETE (NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT)
);

$\{~ \forall R \in \mathcal{P}(\boldsymbol{S} \rightarrow \mathbb{R})~| \\ (\forall t \in R, support(t) = \{ "attr1", "attr2"\} \land \\ ~~t.value \in \mathbb{N} \cup \{null\} \land \\ ~~t.value < 2 \land |t.name| < 5) \land \\ (\forall t, s \in R, t.attr1 = s.attr1 \land t.attr2 = s.attr2 \implies t = s)~\}$

Line by line:

Definition of a relation
Support of each tuple must match the table columns
Set domains, null has to be explicitly allowed, opposite to sql
- setting max characters in second, done with LENGTH in sql
Set checks on values
Primary key/s

Notes:

Primary key is equivalent to unique not null, compound keys are not the same as individual unique not nulls
SQL allows duplicates unless states with NOT NULL

Operations on tables

DROP TABLE table1 (IF EXIST)

Change table structure

ALTER TABLE table1
 REMANE TO newtable
 REMANE attr1 TO newAttribute
 ADD COLUMN name TYPE ...
 ALTER COLUMN name SET DEFAULT value
 DROP COLUMN name

Operations on Rows

INSERT INTO table1(attr1, attr2) VALUES
   (a.value1, a.value2), (b.value1, b.value2);
DELETE FROM table1 WHERE expression

Update rows, note table1 could be a nested query

UPDATE table1 SET attr1 = "newValue" WHERE 

Extensions as Partial Functions

Partial function, does not have to map all values in the domain. Each value in domain only has 1 mapping

Every Function is a Partial Function is a Relation
$Functions \subset Partial Functions \subset Relations$

Map each attribute to a value. Note: tuples are unordered in table, should have same support

attr1	attr2
tuple2.value1	tuple2.value2
tuple1.value1	tuple1.value2

$\{attr1 \mapsto tuple1.value1, attr2 \mapsto tuple1.value2\}, \\ \{attr1 \mapsto tuple2.value1, attr2 \mapsto tuple2.value2\} $

order of attribute mappings is irrelevant (as its a set)

Extensions as elements of Cartesian Product

If we define $D_1$ as the domain of attribute 1, then tuples are elements of $D_1 \times D_2 \dots D_n$ Tuples are then written without mappings, order matters now within a tuple. For the same table above

$\{ (tuple1.value1, tuple1.value2), \\(tuple2.value1, tuple2.value2) \}$

2. DML / Queries

SELECT * | attr1, SUM(attr2) AS sum_name ...
FROM table1 | table1 JOIN table2 | subquery
WHERE expr
HAVING after_Aggreg_expr
ORDER BY attr1 (ASC | DESC) (NULLS LAST | NULLS FIRST)

UNION INTERSECT EXCEPT
For bag (with dupli’s) operations use ALL, e.g. UNION ALL

Joins

T1 JOIN T2 USING attr1 for natural joins, to state which attribute to use for the join, explicitly, prevents ambiguities

T1 JOIN T2 ON T1.attr1 = T2.attr4 theta join

Lower-case are defaults

cross join
inner JOIN
LEFT outer JOIN
RIGHT outer JOIN
FULL outer JOIN

Rollup

SELECT a1, a2, a3, SUM(measure)
FROM table
WHERE slice_attr IN ('value1', 'value2' ...)       using a set
   WHERE slice_attr = 'value'
GROUP BY ROLLUP (a1, a1, a4)
ORDER BY SUM(measure) DESC