Languages

• DISTINCT

SELECT DISTINCT state FROM airports;

• COUNT / SUM / AVG / MAX / MIN

SELECT COUNT(DISTINCT state) AS state_count
  FROM airports;

SELECT s.sname, s.age
  FROM sailors AS s
 WHERE s.age =
       (SELECT MAX(s2.age)
          FROM sailors AS s2);

/* NULL is counted in COUNT, but ignored in other aggregate operators */

• WHERE & Conditions

SELECT *
  FROM airports
 WHERE state = 'NY' 
   AND (city = 'Perry' OR city != 'Middletown');

• LIKE

SELECT *
  FROM airports
 WHERE city LIKE '%er%';
/* % is 0 or more arbitrary char, _ is one arbitrary char */

• BETWEEN

SELECT *
  FROM airports
 WHERE lat BETWEEN 30 AND 30.5;

• ORDER BY & ASC & DSC & LIMIT

SELECT *
FROM airports
ORDER BY lat, city DESC
LIMIT 20;

• UNION / INTERSECT / EXCEPT

SELECT r.sid
  FROM boats AS b JOIN reserves AS r
    ON r.bid = b.bid AND b.color = 'red'
INTERSECT
SELECT r.sid
  FROM boats AS b JOIN reserves AS r
    ON r.bid = b.bid AND b.color = 'green';

/* INTERSECT (and EXCEPT) can be written as follow */
/* Method No.1 */
SELECT s.sid
  FROM sailors AS s
       JOIN boats AS b1 JOIN reserves AS r1
       JOIN boats AS b2 JOIN reserves AS r2
    ON s.sid = r1.sid AND r1.bid = b1.bid
   AND s.sid = r2.sid AND r2.bid = b2.bid
   AND (b1.color = 'red' AND b2.color = 'green');

/* Method No.2 */
SELECT s.name
  FROM sailors AS s
       JOIN boats AS b
       JOIN reserves AS r
    ON s.sid = r.sid AND r.bid = b.bid AND b.color = 'red'
   AND s.sid IN 
       (SELECT r2.sid
          FROM boats AS b2 JOIN reserves AS r2
            ON r2.bid = b2.bid
           AND b2.color = 'green');

• IN / EXISTS / UNIQUE

SELECT s.sname
  FROM sailors AS s
 WHERE EXISTS
       (SELECT *
          FROM reserves AS r
         WHERE r.bid = 103
           AND s.sid = r.sid);

SELECT s.sname
  FROM sailors AS s
 WHERE s.sid IN (12, 13, 14, 15);

• ALL / ANY

SELECT *
  FROM sailors AS s
 WHERE s.rating > ALL 
       (SELECT s2.rating
          FROM sailors AS s2
         WHERE s2.sname = 'Horatio');

• GROUP BY / HAVING

SELECT s.rating, MIN(s.age) AS min_age
  FROM sailors AS s
 WHERE s.age >= 18
GROUP BY s.rating
HAVING COUNT(*) > 1;

/* 
- HAVING clause must be aggregation among each group 
  or attributes listed in GROUP BY.
- Aggregation operations cannot be nested like MIN(AVG(...)).
*/

• INSERT INTO

INSERT INTO albums (title, release_date, recording_date)
VALUES ('Charcoal Lane', '1990-01-01 01:01:01.00000', '1990-01-01 01:01:01.00000'),
       ('The New Danger', '2008-01-01 01:01:01.00000', '1990-01-01 01:01:01.00000');

• UPDATE

UPDATE staff
   SET salary = salary * 1.3, vacation = vacation + 5
 WHERE title = 'Manager';

• DELETE FROM

DELETE FROM albums
 WHERE title = 'Charcoal Lane';

• LEFT JOIN / RIGHT JOIN / FULL JOIN (outer joins)

/* Preserve all entries in either side or both sides after join */
SELECT s.sid, r.bid
  FROM reserves AS r 
       RIGHT JOIN sailors AS s
       on r.sid = s.sid
          AND s.rating > 3;

• CHECK & CONSTRAINT

CREATE TABLE staff (
    PRIMARY KEY (staff_num),
    staff_num      INT(5)       NOT NULL,
    first_name     VARCHAR(100) NOT NULL,
    pens_in_drawer INT(2)       NOT NULL,
                   CONSTRAINT pens_in_drawer_range
                   CHECK(pens_in_drawer BETWEEN 1 AND 99)
);

• ASSERTION

CREATE ASSERTION small_club
 CHECK ((SELECT COUNT(s.sid) FROM sailors AS s)
       + (SELECT COUNT(b.bid) FROM boats AS b) < 100);

• TRIGGER

CREATE TRIGGER young_sailor_update
AFTER INSERT ON sailors
REFERENCING NEW TABLE new_sailors
FOR EACH STATEMENT
INSERT INTO young_sailors (sid, name, age, rating)
     SELECT sid, name, age, rating
       FROM new_sailors as n
      WHERE n.age <= 18;

• Division example

A / B means “selecting the keys of A which contain all the values of B”.
For example, find sailors who’ve reserved all boats.

sailors: (*sid, sname, rating, age)
boats: (*bid, bname, color)
reserves: (*sid, *bid, day)

SELECT s.name
  FROM sailors AS s
 WHERE NOT EXIST
       ((SELECT b.bid
           FROM boats AS b)
        EXCEPT
        (SELECT r.bid
           FROM reserves AS r
          WHERE r.sid = s.sid)
       );

* We can actually write EXCEPT using IN as described previously.