Prev: course-introduction-and-the-relational-model Next: database-storage-i
Comprised of 3 different classes of commands:
An aggregation function takes in a bag of tuples as its input and then produces a single scalar value as output.
Count will turn a scalar (the rows of the student table) into one number.
SELECT COUNT(*) FROM student WHERE login like '%@cs';
You can use multiple aggregates within a single SELECT
statement:
SELECT AVG(gpa), COUNT(sid) FROM student WHERE login LIKE '%@cs';
Or distinct users (if you have duplicates).
SELECT COUNT(DISTINCT login) FROM student WHERE login LIKE '%@cs';
If you want to output an aggregate, use a HAVING
clause.
SELECT AVG(s.gpa) AS avg_gpa, e.cid
FROM enrolled AS e, student AS s
WHERE e.sid = s.sid
GROUP BY e.cid
HAVING avg_gpa > 3.9;
The SQL standard says that strings are case sensitive and use single-quotes only.
LIKE
or ILIKE
keywords (insensitive match)
Instead of having the output of a query returned in the terminal, you can store it in another table, for querying later on.
New Table:
SELECT DISTINCT cid INTO CourseIds FROM enrolled;
INSERT INTO CourseIds (SELECT DISTINCT cid FROM enrolled);
ORDER BY
imposes a sort on tuplesSELECT sid FROM enrolled WHERE cid = '15-721' ORDER BY grade DESC;
Multiple ORDER BY
s can be used to break ties:
SELECT sid FROM enrolled WHERE cid = '15-721' ORDER BY grade DESC, sid ASC;
Any arbitrary expression may be used:
SELECT sid FROM enrolled WHERE cid = '15-721' ORDER BY UPPER(grade) DESC, sid + 1 ASC;
LIMIT
imposes a restriction on the number of
tuples:SELECT sid, name FROM student WHERE login LIKE '%@cs' LIMIT 10;
OFFSET
allows you to return a range in the results (be
careful, as this requires LIMIT
+ OFFSET
memory.SELECT sid, name FROM student WHERE login LIKE '%@cs' LIMIT 10 OFFSET 20;
SELECT name FROM student WHERE sid IN (SELECT sid FROM enrolled WHERE cid = '15-445');
ALL
= Must satisfy expression for all rows.ANY
= Must satisfy expression for at least one
row.IN
= Equivalent to =ANY()
.EXISTS
= At least one row is returned.ROW_NUMBER
: the number of the current row.RANK
: the order position of the current row.OVER
clause to specify how to group
tuples in the window function, and PARTITION BY
to specify
group.SELECT cid, sid, ROW_NUMBER() OVER (PARTITION BY cid) FROM enrolled ORDER BY cid;
You can also use an ORDER BY
for deterministic
sorting:
SELECT *, ROW_NUMBER() OVER (ORDER BY cid) FROM enrolled ORDER BY cid;
WITH
clause binds the output of the inner query to
a temporary result with that name.WITH nums AS (SELECT 1)
SELECT * from nums;
You can bind output columns to names before the AS
:
WITH nums (one, two) AS (SELECT 1, 2)
SELECT one + two FROM nums;
Use the RECURSIVE
keyword after WITH
to
allow a CTE to reference itself.
WITH RECURSIVE iterator (counter) AS (
SELECT 1)
(UNION
SELECT counter + 1 FROM iterator WHERE counter < 10)
(
)SELECT * FROM iterator;
Prev: course-introduction-and-the-relational-model Next: database-storage-i