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 */
SELECT *
FROM airports
WHERE state = 'NY'
AND (city = 'Perry' OR city != 'Middletown');
SELECT *
FROM airports
WHERE city LIKE '%er%';
/* % is 0 or more arbitrary char, _ is one arbitrary char */
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');
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);
SELECT *
FROM sailors AS s
WHERE s.rating > ALL
(SELECT s2.rating
FROM sailors AS s2
WHERE s2.sname = 'Horatio');
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 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 staff
SET salary = salary * 1.3, vacation = vacation + 5
WHERE title = 'Manager';
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;
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)
);
CREATE ASSERTION small_club
CHECK ((SELECT COUNT(s.sid) FROM sailors AS s)
+ (SELECT COUNT(b.bid) FROM boats AS b) < 100);
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;
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.
