cs480 - Database Systems

Overview

Overview
Lecture <2026-04-29 Wed>
- Exam recap
Lecture <2026-04-27 Mon>
Lecture <2026-04-20 Mon>
Lecture <2026-03-16 Mon>
- Requirements - Music collection
- ER Model
  - music collection
Lecture <2026-03-09 Mon>
- Exam recap
Lecture <2026-03-04 Wed>
- Example for triggers: track outdated row versions in a history table
Lecture <2026-02-25 Wed>
Lecture <2026-02-23 Mon>
- DDL
Lecture <2026-02-18 Wed>
Lecture <2026-02-16 Mon>
- nested subqueries
- window functions
Lecture <2026-02-11 Wed>
- Example SQL queries
Lecture <2026-02-02 Mon>
- Project topic selection
- Relational Algebra
Lecture <2026-01-28 Wed>
- Expressing min and max without the aggregation operator
  - logic
  - University example
Lecture <2026-01-21 Wed>
- Cross product
  - set difference

Lecture <2026-04-29 Wed>

Exam recap

finding candidate keys

super key \(A\): set of attributes that uniquely identifies the rows in the table
- \(A \rightarrow R\) where \(R\) are all the attributes of the table
candidate: (minimal) set of attributes that uniquely identifies rows in a table
- a minimal superkey
R(a,b,c)
\(\Sigma = \{ a \rightarrow b, b \rightarrow c \}\)
Attribute closure of \(\alpha\), \(\alpha^+\) is the attribute closure
\(A^{+} = R\) then \(A\) is a superkey
look for attributes that do not appear in the RHS -> they have to be part of every candidate key

example computing attribute closure

initialize \(a^+ = a\)
fix point computation
- if we have LHS of \(\sigma\) in \(a^+\) then add RHS
we have a add b: \(a^+ = ab\)
we have b add c: \(a^+ = abc\)

computing a canonical cover

look at slides for tests of extraneousness

3NF + BCNF decomposition example

3NF decomposition

R(a,b,c)
\(\Sigma = \{ a \rightarrow b, b \rightarrow c \}\) is a canonical cover already
R1(a,b), R2(b,c)
we already have a candidate key! We are good
no contained fragments -> we are done

contained fragments example

R1(a,b,c), R2(b,c), R3(c,d,e)
delete R2 because it is contained

harder relational algebra examples

Lecture <2026-04-27 Mon>

disk-resident index structures (B-trees in depth)
postgres internals and query plan demo
vector databases (RAG)
query processing on modern hardware
more details on query optimization
data provenance (tracking where data is coming from)

Lecture <2026-04-20 Mon>

\[ S = w_1(A), r_1(B), r_2(C), w_2(B), r_3(B), w_4(C) \]

../conflictgraph.jpg

\[ S_2 = r_1(X), w_1(X), r_2(X), w_2(X), w_3(X), w_3(Y),r_1(Y), w_1(Y), r_2(Y), w_2(Y) \]

../conflictgraph2.jpg

Lecture <2026-03-16 Mon>

Requirements - Music collection

albums
- title
- which songs are on the album (and in which order)
- total length
- release date
- record company
- which artists are on the album
artists
- name
- album count
song
- title
- genre
- duration
- artist (who wrote it)
- when added
- streamcount (play count)

ER Model

music collection

../musiccollection.jpg

Lecture <2026-03-09 Mon>

Exam recap

relational basics
SQL - using CTE example
2025 spring midterm - question 1.3.1

CTES

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


WITH headcnt AS (
  SELECT count(*) AS headcnt, dept_name
  FROM student
  GROUP BY dept_name
),
maxheadcnt AS (
  SELECT max(headcnt) AS maxheadcnt
  FROM headcnt
)
SELECT *
FROM headcnt
WHERE headcnt = (SELECT * FROM maxheadcnt);

headcnt	dept_name
4

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


WITH headcnt AS (
  SELECT count(*) AS headcnt, dept_name
  FROM student
  GROUP BY dept_name
),
maxheadcnt AS (
  SELECT max(headcnt) AS maxheadcnt
  FROM headcnt
)
SELECT hc.*
FROM headcnt hc,
     maxheadcnt m
WHERE hc.headcnt = m.maxheadcnt;

headcnt	dept_name
4

substitution (only true if no side effects)

1
2
3
4
5
6
7
8
9


SELECT hc.*
FROM (SELECT count(*) AS headcnt, dept_name
  FROM student
  GROUP BY dept_name) hc,
     (SELECT max(headcnt) AS maxheadcnt
  FROM (SELECT count(*) AS headcnt, dept_name
  FROM student
  GROUP BY dept_name) headcnt) m
WHERE hc.headcnt = m.maxheadcnt;

headcnt	dept_name
4

oil on route to Amsterdam

1
2
3
4


SELECT DISTINCT sname, capacity, lengthfeet
FROM route NATURAL JOIN ship NATURAL JOIN cargo
WHERE destination = 'Amsterdam'
      AND item = 'oil'

1
2
3
4


SELECT DISTINCT sname, capacity, lengthfeet
FROM ship NATURAL JOIN cargo
WHERE destination = 'Amsterdam'
      AND item = 'oil'

midterm 2025 - DDL question 1.2.1

1
2
3
4


CREATE TABLE item (
  name TEXT PRIMARY KEY,
  category TEXT
);

1
2
3
4
5
6
7
8


ALTER TABLE cargo
      ADD CONSTRAINT item_fk FOREIGN KEY item REFERENCES item ON UPDATE SET NULL ON DELETE RESTRICT;

ALTER TABLE cargo
      ADD CONSTRAINT item_all CHECK(item IN ('x', 'y'));

ALTER TABLE cargo
      ADD CONSTRAINT item_unique UNIQUE(item);

1
2
3
4
5
6
7


CREATE price (
  item TEXT REFERENCES item,
  country TEXT,
  date DATE,
  price NUMERIC(12,2),
  PRIMARY (item, count, date)
);

midterm 2025 - Q1.1.1 (relational algebra)

\[ \pi_{destination,item}(\sigma_{(source = Amsterdam \lor source = New York) \land (item = oil \lor item = coal) \land (quantity > 200}(cargo)) \]

Lecture <2026-03-04 Wed>

Example for triggers: track outdated row versions in a history table

design: keep all row versions (current and old ones) in a inventory_hist table. Use t_end = NULL to encode that a row is currently valid. Use triggers to automatically maintain inventory_hist.

the basic table (inventory of a company)

1
2
3
4
5
6


DROP TABLE IF EXISTS inventory;
CREATE TABLE inventory (
  itemid INT PRIMARY KEY,
  itemname TEXT,
  quantity INT CHECK (quantity >= 0)
);

CREATE TABLE

the history table for old row versions

for each old row version we track from when to when (a time interval [t_start,t_end) is was valid)

1
2
3
4
5
6
7
8
9


DROP TABLE IF EXISTS inventory_hist;
CREATE TABLE inventory_hist (
  t_start TIMESTAMP  DEFAULT CURRENT_TIMESTAMP,
  t_end TIMESTAMP DEFAULT NULL,
  itemid INT,
  itemname TEXT,
  quantity INT,
  PRIMARY KEY (itemid, t_start)
);

CREATE TABLE

dealing with inserts

on insert, insert tuple with [now,NULL] into inventory_hist table (let insert to inventory proceed)

1
2
3
4
5
6
7
8


CREATE OR REPLACE FUNCTION invhist_track_insert() RETURNS trigger AS
$$
  BEGIN
    INSERT INTO inventory_hist VALUES (CURRENT_TIMESTAMP, NULL, NEW.itemid, NEW.itemname, NEW.quantity);
    RETURN NEW;
  END;
$$
LANGUAGE plpgsql;

CREATE FUNCTION

1
2
3


CREATE OR REPLACE TRIGGER inventory_insert AFTER INSERT ON inventory
  FOR EACH ROW
EXECUTE FUNCTION invhist_track_insert();

CREATE TRIGGER

dealing with deletes

on delete, we know that the previous row version is already in the inventory_hist table, we just have to set its end timestamp

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


CREATE OR REPLACE FUNCTION invhist_track_delete () RETURNS trigger AS
$$
  BEGIN
    UPDATE inventory_hist
    SET t_end = CURRENT_TIMESTAMP
    WHERE t_start = (SELECT max(t_start) FROM inventory_hist h WHERE h.itemid = OLD.itemid)
          AND itemid = OLD.itemid;
    RETURN OLD;
  END;
$$
LANGUAGE plpgsql;

CREATE FUNCTION

1
2
3


CREATE OR REPLACE TRIGGER inventory_delete AFTER DELETE ON inventory
  FOR EACH ROW
EXECUTE FUNCTION invhist_track_delete();

CREATE TRIGGER

dealing with updates

update the end timestamp of the current row version and insert the new row version with [now,NULL]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


CREATE OR REPLACE FUNCTION invhist_track_update() RETURNS trigger AS
$$
  BEGIN
    UPDATE inventory_hist
    SET t_end = CURRENT_TIMESTAMP
    WHERE t_start = (SELECT max(t_start) FROM inventory_hist h WHERE h.itemid = OLD.itemid)
          AND itemid = OLD.itemid;
    INSERT INTO inventory_hist VALUES (CURRENT_TIMESTAMP, NULL, NEW.itemid, NEW.itemname, NEW.quantity);
    RETURN NEW;
  END;
$$
LANGUAGE plpgsql;

CREATE FUNCTION

1
2
3


CREATE OR REPLACE TRIGGER inventory_update AFTER UPDATE ON inventory
  FOR EACH ROW
EXECUTE FUNCTION invhist_track_update();

CREATE TRIGGER

COMMENT try out the history triggers

clean the history and table

1
2
3


DELETE FROM inventory;
DELETE FROM inventory_hist;
SELECT * FROM inventory_hist;

DELETE 3
DELETE 3
t_start	t_end	itemid	itemname	quantity

1
2
3
4


INSERT INTO inventory
VALUES (1,'lawnmover', 4),
       (2,'axe', 5),
       (3,'flowerseeds', 200);

INSERT 0 3

1

SELECT * FROM inventory_hist;

t_start	itemid	itemname	quantity
2026-03-04 13:31:36.498399	1	lawnmover	4
2026-03-04 13:31:36.498399	2	axe	5
2026-03-04 13:31:36.498399	3	flowerseeds	200

1

UPDATE inventory SET quantity = quantity + 5 WHERE itemname IN ('lawnmover', 'axe');

UPDATE 2

1

SELECT * FROM inventory;

itemid	itemname	quantity
3	flowerseeds	200
1	lawnmover	9
2	axe	10

1

SELECT * FROM inventory_hist;

t_start	t_end	itemid	itemname	quantity
2026-03-04 13:31:36.498399		3	flowerseeds	200
2026-03-04 13:31:36.498399	2026-03-04 13:31:38.603468	1	lawnmover	4
2026-03-04 13:31:38.603468		1	lawnmover	9
2026-03-04 13:31:36.498399	2026-03-04 13:31:38.603468	2	axe	5
2026-03-04 13:31:38.603468		2	axe	10

1

DELETE FROM inventory WHERE itemname = 'axe';

DELETE 1

1

SELECT * FROM inventory;

itemid	itemname	quantity
3	flowerseeds	200
1	lawnmover	9

1

SELECT * FROM inventory_hist;

t_start	t_end	itemid	itemname	quantity
2026-03-04 13:31:36.498399		3	flowerseeds	200
2026-03-04 13:31:36.498399	2026-03-04 13:31:38.603468	1	lawnmover	4
2026-03-04 13:31:38.603468		1	lawnmover	9
2026-03-04 13:31:36.498399	2026-03-04 13:31:38.603468	2	axe	5
2026-03-04 13:31:38.603468	2026-03-04 13:31:51.046015	2	axe	10

get table AS OF a certain time

1
2
3
4


SELECT itemid, itemname, quantity
FROM inventory_hist
WHERE t_start <= '2026-03-04 13:31:37'::TIMESTAMP
      AND (t_end IS NULL OR t_end > '2026-03-04 13:31:37'::TIMESTAMP);;

itemid	itemname	quantity
3	flowerseeds	200
1	lawnmover	4
2	axe	5

let's turn this into a function

1
2
3
4
5
6
7
8
9


CREATE OR REPLACE FUNCTION inventory_snapshot (asof TIMESTAMP)
RETURNS SETOF inventory AS
$$
SELECT itemid, itemname, quantity
FROM inventory_hist
WHERE t_start <= asof
      AND (t_end IS NULL OR t_end > asof);
$$
LANGUAGE SQL;

CREATE FUNCTION

1
2


SELECT *
FROM inventory_snapshot('2026-03-04 13:31:37'::TIMESTAMP);

itemid	itemname	quantity
3	flowerseeds	200
1	lawnmover	4
2	axe	5

Lecture <2026-02-25 Wed>

1
2
3
4


CREATE TABLE testr AS (
  SELECT a, random(1,10) AS b, random(1,1000) AS c
  FROM generate_series(1,1000000) AS s(a)
);

SELECT 1000000

1

SELECT count(*) FROM testr;

count
1000000

1

EXPLAIN SELECT * FROM testr WHERE a > 300000;

QUERY PLAN
Seq Scan on testr (cost=0.00..17940.00 rows=695107 width=12)
Filter: (a > 300000)

1

CREATE INDEX testra ON testr(a);

CREATE INDEX

1

EXPLAIN SELECT * FROM testr WHERE a > 500000;

QUERY PLAN
Index Scan using testra on testr (cost=0.42..16780.89 rows=494541 width=12)
Index Cond: (a > 500000)

1

CREATE INDEX testrb ON testr(b);

CREATE INDEX

1

EXPLAIN SELECT * FROM testr WHERE b > 7;

QUERY PLAN
Bitmap Heap Scan on testr (cost=3376.80..12598.05 rows=302500 width=12)
Recheck Cond: (b > 7)
-> Bitmap Index Scan on testrb (cost=0.00..3301.18 rows=302500 width=0)
Index Cond: (b > 7)

Lecture <2026-02-23 Mon>

DDL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


DROP TABLE building CASCADE;
DROP TABLE dept_buildings CASCADE;

CREATE TABLE building (
  name VARCHAR(1000),
  numrooms INT NOT NULL CHECK (numrooms > 0),
  totalcap INT NOT NULL CHECK (totalcap >= 0),
  numfloors INT NOT NULL CHECK (numfloors > 0),
  numelevators INT NOT NULL CHECK (numelevators >= 0),
  address VARCHAR(100) NOT NULL PRIMARY KEY -- come back later
);

-- departments
CREATE TABLE dept_buildings (
  buildaddress VARCHAR(100) REFERENCES building ON DELETE CASCADE,
  deptname VARCHAR(100) REFERENCES department ON DELETE CASCADE
);
-- rooms

DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE

1

SELECT * FROM department LIMIT 1;

dept_name	building	budget
Biology	Watson	90000.00

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


  -- name VARCHAR(1000),
  -- numrooms INT NOT NULL CHECK (numrooms > 0),
  -- totalcap INT NOT NULL CHECK (totalcap >= 0),
  -- numfloors INT NOT NULL CHECK (numfloors > 0),
  -- numelevators INT NOT NULL CHECK (numelevators > 0),
  -- address VARCHAR(100) NOT NULL PRIMARY KEY -- come back later


INSERT INTO building
VALUES
  ('Lecture Center C', 4, 400, 1, 0, 'asdljasdhlajhd'),
  ('CDRLC', 200, 1000, 5, 2, '800 Taylor'),
  ('Library', 150, 500, 4, 5, 'Something something');

INSERT 0 3

1

SELECT * FROM building;

name	numrooms	totalcap	numfloors	numelevators	address
Lecture Center C	4	400	1	0	asdljasdhlajhd
CDRLC	200	1000	5	2	800 Taylor
Library	150	500	4	5	Something something

1

SELECT * FROM department;

dept_name	building	budget
Biology	Watson	90000.00
Comp. Sci.	Taylor	100000.00
Elec. Eng.	Taylor	85000.00
Finance	Painter	120000.00
History	Painter	50000.00
Music	Packard	80000.00
Physics	Watson	70000.00

1
2
3
4


INSERT INTO department VALUES ('Comp. Sci', 'Taylor', 1000000.0);

INSERT INTO dept_buildings
VALUES ('800 Taylor', 'Comp. Sci.');

INSERT 0 1

1
2


DELETE FROM department
WHERE dept_name = 'Comp. Sci.';

DELETE 0

1
2
3


UPDATE department
SET budget = budget + 1000000
WHERE building = 'Taylor'

UPDATE 2

1
2
3
4


UPDATE department
SET budget = CASE WHEN building = 'Taylor' THEN budget + 1000000
                  ELSE budget - 10000
             END;

UPDATE 7

Lecture <2026-02-18 Wed>

1

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

tablename
department
course
instructor
section
classroom
teaches
student
takes
advisor
time_slot
prereq

1
2
3


SELECT name, ROW_NUMBER() OVER (ORDER BY name)
FROM student
ORDER BY name ASC;

name	row_number
Aoi	1
Bourikas	2
Brandt	3
Brown	4
Chavez	5
Levy	6
Peltier	7
Sanchez	8
Shankar	9
Snow	10
Tanaka	11
Williams	12
Zhang	13

1
2
3


SELECT name, dept_name, ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY dept_name)
FROM student
ORDER BY dept_name, name ASC;

name	dept_name	row_number
Tanaka	Biology	1
Brown	Comp. Sci.	4
Shankar	Comp. Sci.	1
Williams	Comp. Sci.	2
Zhang	Comp. Sci.	3
Aoi	Elec. Eng.	1
Bourikas	Elec. Eng.	2
Chavez	Finance	1
Brandt	History	1
Sanchez	Music	1
Levy	Physics	3
Peltier	Physics	1
Snow	Physics	2

top 3 students in terms of their total credits, ties broken arbitrarily

1
2
3
4
5


SELECT *
FROM  (SELECT name, tot_cred, ROW_NUMBER() OVER (ORDER BY tot_cred DESC) AS creditcrank
       FROM student) sub
WHERE sub.creditcrank <= 3
ORDER BY creditcrank ASC;

name	tot_cred	creditcrank
Tanaka	120	1
Chavez	110	2
Zhang	102	3
Bourikas	98	4
Brandt	80	5
Aoi	60	6
Brown	58	7
Peltier	56	8
Williams	54	9
Levy	46	10
Sanchez	38	11
Shankar	32	12
Snow	0	13

top 3 students in terms of their total credits, ties outputted all

1
2
3
4
5


SELECT *
FROM  (SELECT name, (tot_cred / 10)::int8 AS cred, RANK() OVER (ORDER BY (tot_cred / 10)::int8 DESC) AS creditcrank
       FROM student) sub
WHERE sub.creditcrank <= 3
ORDER BY creditcrank ASC;

name	cred	creditcrank
Tanaka	12	1
Chavez	11	2
Zhang	10	3
Bourikas	10	3

for each student get the difference in credits to the next student ordered by credits

1
2
3


SELECT name, tot_cred, tot_cred - COALESCE(lead(tot_cred,1) OVER (ORDER BY tot_cred DESC),0) AS creditcrank
       FROM student
ORDER BY tot_cred DESC;;

name	tot_cred	creditcrank
Tanaka	120	10
Chavez	110	8
Zhang	102	4
Bourikas	98	18
Brandt	80	20
Aoi	60	2
Brown	58	2
Peltier	56	2
Williams	54	8
Levy	46	8
Sanchez	38	6
Shankar	32	32
Snow	0	0

1
2


--ALTER TABLE prereq DROP CONSTRAINT prereq_course_id_fkey;
ALTER TABLE prereq DROP CONSTRAINT prereq_prereq_id_fkey;

ALTER TABLE

1
2
3
4
5


INSERT INTO prereq
VALUES ('CS-480', 'CS-315'),
       ('CS-580', 'CS-480'),
       ('CS-581', 'CS-480')
;

INSERT 0 3

1

SELECT * FROM prereq;

course_id	prereq_id
BIO-301	BIO-101
BIO-399	BIO-101
CS-190	CS-101
CS-315	CS-101
CS-319	CS-101
CS-347	CS-101
EE-181	PHY-101
CS-480	CS-315
CS-580	CS-480
CS-581	CS-480

direct + one level indirect

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


WITH direct AS (
         SELECT * FROM prereq
     ),
     ind1 AS (
         SELECT p1.course_id, p2.prereq_id
         FROM direct p1, prereq p2
         WHERE p1.prereq_id = p2.course_id
     ),
     ind2 AS (
         SELECT p1.course_id, p2.prereq_id
         FROM ind1 p1, prereq p2
         WHERE p1.prereq_id = p2.course_id
     )
SELECT * FROM direct
UNION
SELECT * FROM ind1
UNION
SELECT * FROM ind2;

course_id	prereq_id
CS-315	CS-101
CS-581	CS-480
BIO-301	BIO-101
CS-480	CS-315
EE-181	PHY-101
CS-319	CS-101
CS-190	CS-101
CS-581	CS-315
CS-480	CS-101
CS-347	CS-101
BIO-399	BIO-101
CS-580	CS-315
CS-580	CS-480

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


WITH RECURSIVE reachable AS (
     -- initial reachable
     SELECT * FROM prereq
     UNION
     -- recursive
     SELECT p1.course_id, p2.prereq_id
     FROM reachable p1, prereq p2
     WHERE p1.prereq_id = p2.course_id
     )
SELECT * FROM reachable;

course_id	prereq_id
BIO-301	BIO-101
BIO-399	BIO-101
CS-190	CS-101
CS-315	CS-101
CS-319	CS-101
CS-347	CS-101
EE-181	PHY-101
CS-480	CS-315
CS-580	CS-480
CS-581	CS-480
CS-480	CS-101
CS-580	CS-315
CS-581	CS-315
CS-580	CS-101
CS-581	CS-101

determine all courses one has to take to be able to take a particular course (all direct and any level of indirect prerequisites)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


WITH RECURSIVE reachable AS (
     -- initial reachable
     SELECT *, 1 AS len FROM prereq
     UNION
     -- recursive
     SELECT p1.course_id, p2.prereq_id, p1.len + 1
     FROM reachable p1, prereq p2
     WHERE p1.prereq_id = p2.course_id
     )
SELECT * FROM reachable;

course_id	prereq_id	len
BIO-301	BIO-101	1
BIO-399	BIO-101	1
CS-190	CS-101	1
CS-315	CS-101	1
CS-319	CS-101	1
CS-347	CS-101	1
EE-181	PHY-101	1
CS-480	CS-315	1
CS-580	CS-480	1
CS-581	CS-480	1
CS-480	CS-101	2
CS-580	CS-315	2
CS-581	CS-315	2
CS-580	CS-101	3
CS-581	CS-101	3

1
2
3
4
5
6
7


WITH RECURSIVE infinity AS (
   SELECT 1 AS n
   UNION
   SELECT n+1 AS n
   FROM infinity
   WHERE n < 10)
SELECT * FROM infinity;

n
1
2
3
4
5
6
7
8
9
10

Lecture <2026-02-16 Mon>

nested subqueries

1

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

tablename
department
course
instructor
section
classroom
teaches
student
takes
advisor
time_slot
prereq

instructors that have taught some courses

1
2
3


SELECT *
FROM instructor i
WHERE EXISTS (SELECT * FROM teaches t WHERE i.id = t.id)

id	name	dept_name	salary
10101	Srinivasan	Comp. Sci.	65000.00
12121	Wu	Finance	90000.00
15151	Mozart	Music	40000.00
22222	Einstein	Physics	95000.00
32343	El Said	History	60000.00
45565	Katz	Comp. Sci.	75000.00
76766	Crick	Biology	72000.00
83821	Brandt	Comp. Sci.	92000.00
98345	Kim	Elec. Eng.	80000.00

not taught any courses, negate truth value

1
2
3


SELECT *
FROM instructor i
WHERE NOT EXISTS (SELECT t.id FROM teaches t WHERE i.id = t.id)

id	name	dept_name	salary
33456	Gold	Physics	87000.00
58583	Califieri	History	62000.00
76543	Singh	Finance	80000.00

return all instructors if at least one instructor has taught a course

1
2
3


SELECT *
FROM instructor i
WHERE EXISTS (SELECT * FROM teaches t)

id	name	dept_name	salary
10101	Srinivasan	Comp. Sci.	65000.00
12121	Wu	Finance	90000.00
15151	Mozart	Music	40000.00
22222	Einstein	Physics	95000.00
32343	El Said	History	60000.00
33456	Gold	Physics	87000.00
45565	Katz	Comp. Sci.	75000.00
58583	Califieri	History	62000.00
76543	Singh	Finance	80000.00
76766	Crick	Biology	72000.00
83821	Brandt	Comp. Sci.	92000.00
98345	Kim	Elec. Eng.	80000.00

instructors that have taught some courses (1st one)

1
2
3


SELECT *
FROM instructor i
WHERE i.id IN  (SELECT id FROM teaches t);

id	name	dept_name	salary
10101	Srinivasan	Comp. Sci.	65000.00
12121	Wu	Finance	90000.00
15151	Mozart	Music	40000.00
22222	Einstein	Physics	95000.00
32343	El Said	History	60000.00
45565	Katz	Comp. Sci.	75000.00
76766	Crick	Biology	72000.00
83821	Brandt	Comp. Sci.	92000.00
98345	Kim	Elec. Eng.	80000.00

instructors that have taught some courses (2nd one)

1
2
3
4


SELECT DISTINCT i.*
FROM instructor i,
     teaches t
WHERE i.id = t.id;

id	name	dept_name	salary
10101	Srinivasan	Comp. Sci.	65000.00
32343	El Said	History	60000.00
12121	Wu	Finance	90000.00
98345	Kim	Elec. Eng.	80000.00
22222	Einstein	Physics	95000.00
45565	Katz	Comp. Sci.	75000.00
76766	Crick	Biology	72000.00
15151	Mozart	Music	40000.00
83821	Brandt	Comp. Sci.	92000.00

1
2
3
4
5


SELECT i.id, i.name, i.dept_name, i.salary
FROM instructor i,
     teaches t
WHERE i.id = t.id
GROUP BY i.id, i.name, i.dept_name, i.salary;

id	name	dept_name	salary
83821	Brandt	Comp. Sci.	92000.00
22222	Einstein	Physics	95000.00
76766	Crick	Biology	72000.00
12121	Wu	Finance	90000.00
98345	Kim	Elec. Eng.	80000.00
15151	Mozart	Music	40000.00
32343	El Said	History	60000.00
10101	Srinivasan	Comp. Sci.	65000.00
45565	Katz	Comp. Sci.	75000.00

1

SELECT * FROM teaches LIMIT 1;

id	course_id	sec_id	semester	year
10101	CS-101	1	Fall	2009

1
2
3


SELECT DISTINCT i.*
FROM instructor i JOIN
     teaches t ON (i.id = t.id);

id	name	dept_name	salary
10101	Srinivasan	Comp. Sci.	65000.00
32343	El Said	History	60000.00
12121	Wu	Finance	90000.00
98345	Kim	Elec. Eng.	80000.00
22222	Einstein	Physics	95000.00
45565	Katz	Comp. Sci.	75000.00
76766	Crick	Biology	72000.00
15151	Mozart	Music	40000.00
83821	Brandt	Comp. Sci.	92000.00

window functions

every instructor (pick random 3) paired with the total number of instructors

1
2
3


SELECT *, count(*) OVER() AS numinstr
FROM instructor i
LIMIT 3;

id	name	dept_name	salary	numinstr
10101	Srinivasan	Comp. Sci.	65000.00	12
12121	Wu	Finance	90000.00	12
15151	Mozart	Music	40000.00	12

total salary of every instructor

1
2


SELECT sum(salary) AS ttlsal
FROM instructor

ttlsal
898000.00

total salary of every instructor per departmant

1
2
3


SELECT dept_name, sum(salary) AS ttldsal
FROM instructor
GROUP BY dept_name;

dept_name	ttldsal
Finance	170000.00
History	122000.00
Physics	182000.00
Music	40000.00
Comp. Sci.	232000.00
Biology	72000.00
Elec. Eng.	80000.00

1
2
3
4
5
6
7


WITH allttl AS (SELECT sum(salary) AS ttlsal
                FROM instructor),
     deptttl AS (SELECT dept_name, sum(salary) AS ttldsal
                 FROM instructor
                 GROUP BY dept_name)
SELECT *
FROM deptttl, allttl;

dept_name	ttldsal	ttlsal
Finance	170000.00	898000.00
History	122000.00	898000.00
Physics	182000.00	898000.00
Music	40000.00	898000.00
Comp. Sci.	232000.00	898000.00
Biology	72000.00	898000.00
Elec. Eng.	80000.00	898000.00

1
2


SELECT DISTINCT dept_name, sum(salary) OVER (PARTITION BY dept_name) AS ttldsal,  sum(salary) OVER() AS ttlsal
FROM instructor;

dept_name	ttldsal	ttlsal
Music	40000.00	898000.00
Finance	170000.00	898000.00
Physics	182000.00	898000.00
History	122000.00	898000.00
Comp. Sci.	232000.00	898000.00
Elec. Eng.	80000.00	898000.00
Biology	72000.00	898000.00

1
2
3


SELECT id, dept_name, sum(salary) AS sumsal
FROM instructor
GROUP BY GROUPING SETS ((), (dept_name), (id));

id	dept_name	sumsal
		898000.00
	Finance	170000.00
	History	122000.00
	Physics	182000.00
	Music	40000.00
	Comp. Sci.	232000.00
	Biology	72000.00
	Elec. Eng.	80000.00
83821		92000.00
76543		80000.00
33456		87000.00
58583		62000.00
22222		95000.00
76766		72000.00
12121		90000.00
98345		80000.00
15151		40000.00
32343		60000.00
10101		65000.00
45565		75000.00

1
2
3


SELECT *, count(*) OVER (ORDER BY name  ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS before
FROM instructor
ORDER BY name ASC;

id	name	dept_name	salary	before
83821	Brandt	Comp. Sci.	92000.00	4
58583	Califieri	History	62000.00	5
76766	Crick	Biology	72000.00	6
22222	Einstein	Physics	95000.00	7
32343	El Said	History	60000.00	7
33456	Gold	Physics	87000.00	7
45565	Katz	Comp. Sci.	75000.00	7
98345	Kim	Elec. Eng.	80000.00	7
15151	Mozart	Music	40000.00	7
76543	Singh	Finance	80000.00	6
10101	Srinivasan	Comp. Sci.	65000.00	5
12121	Wu	Finance	90000.00	4

Lecture <2026-02-11 Wed>

Example SQL queries

1
2


SELECT name, dept_name
FROM student;

name	dept_name
Zhang	Comp. Sci.
Shankar	Comp. Sci.
Brandt	History
Chavez	Finance
Peltier	Physics
Levy	Physics
Williams	Comp. Sci.
Sanchez	Music
Snow	Physics
Brown	Comp. Sci.
Aoi	Elec. Eng.
Bourikas	Elec. Eng.
Tanaka	Biology

unique department

1
2


SELECT DISTINCT dept_name
FROM student;

dept_name
Finance
History
Physics
Music
Comp. Sci.
Biology
Elec. Eng.

1
2
3


SELECT name, tot_cred, round((tot_cred / 8.0) + 45,2) AS semcred
FROM student
WHERE tot_cred > 80 OR tot_cred < 20;

name	tot_cred	semcred
Zhang	102	57.75
Chavez	110	58.75
Snow	0	45.00
Bourikas	98	57.25
Tanaka	120	60.00

Querying the database catalog (your database's schema)

get the tables in the database pg_tables

1

SELECT tablename, tableowner  FROM pg_tables;

tablename	tableowner
department	postgres
course	postgres
instructor	postgres
section	postgres
classroom	postgres
teaches	postgres
student	postgres
takes	postgres
advisor	postgres
time_slot	postgres
prereq	postgres
pg_statistic	lord_pretzel
pg_type	lord_pretzel
pg_foreign_table	lord_pretzel
pg_authid	lord_pretzel
pg_statistic_ext_data	lord_pretzel
pg_user_mapping	lord_pretzel
pg_subscription	lord_pretzel
pg_attribute	lord_pretzel
pg_proc	lord_pretzel
pg_class	lord_pretzel
pg_attrdef	lord_pretzel
sql_features	lord_pretzel
sql_implementation_info	lord_pretzel
pg_constraint	lord_pretzel
pg_inherits	lord_pretzel
pg_index	lord_pretzel
pg_operator	lord_pretzel
pg_opfamily	lord_pretzel
pg_opclass	lord_pretzel
pg_am	lord_pretzel
pg_amop	lord_pretzel
pg_amproc	lord_pretzel
pg_language	lord_pretzel
pg_largeobject_metadata	lord_pretzel
pg_aggregate	lord_pretzel
pg_statistic_ext	lord_pretzel
pg_rewrite	lord_pretzel
pg_trigger	lord_pretzel
pg_event_trigger	lord_pretzel
pg_description	lord_pretzel
pg_cast	lord_pretzel
pg_enum	lord_pretzel
pg_namespace	lord_pretzel
pg_conversion	lord_pretzel
pg_depend	lord_pretzel
pg_database	lord_pretzel
pg_db_role_setting	lord_pretzel
pg_tablespace	lord_pretzel
pg_auth_members	lord_pretzel
pg_shdepend	lord_pretzel
pg_shdescription	lord_pretzel
pg_ts_config	lord_pretzel
pg_ts_config_map	lord_pretzel
pg_ts_dict	lord_pretzel
pg_ts_parser	lord_pretzel
pg_ts_template	lord_pretzel
pg_extension	lord_pretzel
pg_foreign_data_wrapper	lord_pretzel
pg_foreign_server	lord_pretzel
pg_policy	lord_pretzel
pg_replication_origin	lord_pretzel
pg_default_acl	lord_pretzel
pg_init_privs	lord_pretzel
pg_seclabel	lord_pretzel
pg_shseclabel	lord_pretzel
pg_collation	lord_pretzel
pg_parameter_acl	lord_pretzel
pg_partitioned_table	lord_pretzel
pg_range	lord_pretzel
pg_transform	lord_pretzel
pg_sequence	lord_pretzel
pg_publication	lord_pretzel
pg_publication_namespace	lord_pretzel
pg_publication_rel	lord_pretzel
pg_subscription_rel	lord_pretzel
pg_largeobject	lord_pretzel
sql_parts	lord_pretzel
sql_sizing	lord_pretzel

casting

casting

1
2


SELECT CAST(tot_cred AS float8) / 3.4 AS dcred, tot_cred::float8
FROM student;

dcred	tot_cred
30	102
9.411764705882353	32
23.529411764705884	80
32.35294117647059	110
16.47058823529412	56
13.529411764705882	46
15.882352941176471	54
11.176470588235295	38
0	0
17.058823529411764	58
17.647058823529413	60
28.823529411764707	98
35.294117647058826	120

potential student advisors

potential advisors (instructors from same department as students)

1
2
3


SELECT s.name AS sname, i.name AS iname
FROM student s JOIN instructor i ON (s.dept_name = i.dept_name)
ORDER BY sname ASC;

sname	iname
Aoi	Kim
Bourikas	Kim
Brandt	Califieri
Brandt	El Said
Brown	Brandt
Brown	Katz
Brown	Srinivasan
Chavez	Singh
Chavez	Wu
Levy	Einstein
Levy	Gold
Peltier	Gold
Peltier	Einstein
Sanchez	Mozart
Shankar	Srinivasan
Shankar	Katz
Shankar	Brandt
Snow	Gold
Snow	Einstein
Tanaka	Crick
Williams	Srinivasan
Williams	Katz
Williams	Brandt
Zhang	Srinivasan
Zhang	Katz
Zhang	Brandt

should not use a natural join here as both tables have a name attribute and we only want to join on dept_name

1
2


SELECT * FROM student LIMIT 1;
SELECT * FROM instructor LIMIT 1;

id	name	dept_name	tot_cred
00128	Zhang	Comp. Sci.	102

id	name	dept_name	salary
10101	Srinivasan	Comp. Sci.	65000.00

string concatenation as an aggregation function

with candidates in one column with one row per student

1
2
3


SELECT s.name AS sname, string_agg(i.name, ' ' ORDER BY i.name) AS inames
FROM student s JOIN instructor i ON (s.dept_name = i.dept_name)
GROUP BY s.name;

sname	inames
Aoi	Kim
Bourikas	Kim
Brandt	Califieri El Said
Brown	Brandt Katz Srinivasan
Chavez	Singh Wu
Levy	Einstein Gold
Peltier	Einstein Gold
Sanchez	Mozart
Shankar	Brandt Katz Srinivasan
Snow	Einstein Gold
Tanaka	Crick
Williams	Brandt Katz Srinivasan
Zhang	Brandt Katz Srinivasan

Different ways to express joins

students with dept information

1
2


SELECT *
FROM student s JOIN department d ON (s.dept_name = d.dept_name);

id	name	dept_name	tot_cred	dept_name	building	budget
00128	Zhang	Comp. Sci.	102	Comp. Sci.	Taylor	100000.00
12345	Shankar	Comp. Sci.	32	Comp. Sci.	Taylor	100000.00
19991	Brandt	History	80	History	Painter	50000.00
23121	Chavez	Finance	110	Finance	Painter	120000.00
44553	Peltier	Physics	56	Physics	Watson	70000.00
45678	Levy	Physics	46	Physics	Watson	70000.00
54321	Williams	Comp. Sci.	54	Comp. Sci.	Taylor	100000.00
55739	Sanchez	Music	38	Music	Packard	80000.00
70557	Snow	Physics	0	Physics	Watson	70000.00
76543	Brown	Comp. Sci.	58	Comp. Sci.	Taylor	100000.00
76653	Aoi	Elec. Eng.	60	Elec. Eng.	Taylor	85000.00
98765	Bourikas	Elec. Eng.	98	Elec. Eng.	Taylor	85000.00
98988	Tanaka	Biology	120	Biology	Watson	90000.00

students with dept information

1
2
3


SELECT *
FROM student s, department d
WHERE s.dept_name = d.dept_name;

id	name	dept_name	tot_cred	dept_name	building	budget
00128	Zhang	Comp. Sci.	102	Comp. Sci.	Taylor	100000.00
12345	Shankar	Comp. Sci.	32	Comp. Sci.	Taylor	100000.00
19991	Brandt	History	80	History	Painter	50000.00
23121	Chavez	Finance	110	Finance	Painter	120000.00
44553	Peltier	Physics	56	Physics	Watson	70000.00
45678	Levy	Physics	46	Physics	Watson	70000.00
54321	Williams	Comp. Sci.	54	Comp. Sci.	Taylor	100000.00
55739	Sanchez	Music	38	Music	Packard	80000.00
70557	Snow	Physics	0	Physics	Watson	70000.00
76543	Brown	Comp. Sci.	58	Comp. Sci.	Taylor	100000.00
76653	Aoi	Elec. Eng.	60	Elec. Eng.	Taylor	85000.00
98765	Bourikas	Elec. Eng.	98	Elec. Eng.	Taylor	85000.00
98988	Tanaka	Biology	120	Biology	Watson	90000.00

Inspecting execution plans

get the execution plan using EXPLAIN

1
2
3


EXPLAIN SELECT *
FROM student s, department d
WHERE s.dept_name = d.dept_name;

QUERY PLAN Hash Join (cost=22.15..37.84 rows=450 width=274) Hash Cond: ((s.dept_name)::text = (d.dept_name)::text) -> Seq Scan on student s (cost=0.00..14.50 rows=450 width=152) -> Hash (cost=15.40..15.40 rows=540 width=122) -> Seq Scan on department d (cost=0.00..15.40 rows=540 width=122)

now for the same query, but using NATURAL JOIN instead of multiple FROM clause items and WHERE

1
2


EXPLAIN SELECT *
FROM student s NATURAL JOIN department d;

QUERY PLAN Hash Join (cost=22.15..37.84 rows=450 width=216) Hash Cond: ((s.dept_name)::text = (d.dept_name)::text) -> Seq Scan on student s (cost=0.00..14.50 rows=450 width=152) -> Hash (cost=15.40..15.40 rows=540 width=122) -> Seq Scan on department d (cost=0.00..15.40 rows=540 width=122)

Set operations

names from either instructors or student retaining duplicates

1
2
3
4
5


SELECT *
FROM  (SELECT name FROM student
       UNION ALL
       SELECT name FROM instructor) names
ORDER BY name ASC;

name
Aoi
Bourikas
Brandt
Brandt
Brown
Califieri
Chavez
Crick
Einstein
El Said
Gold
Katz
Kim
Levy
Mozart
Peltier
Sanchez
Shankar
Singh
Snow
Srinivasan
Tanaka
Williams
Wu
Zhang

names of persons that are both instructors and students

1
2
3
4
5


SELECT *
FROM  (SELECT name FROM student
       INTERSECT
       SELECT name FROM instructor) names
ORDER BY name ASC;

name
Brandt

names of persons that are instructors, but not students

1
2
3
4
5


SELECT *
FROM  (SELECT name FROM student
       EXCEPT
       SELECT name FROM instructor) names
ORDER BY name ASC;

name
Aoi
Bourikas
Brown
Chavez
Levy
Peltier
Sanchez
Shankar
Snow
Tanaka
Williams
Zhang

Subqueries

1

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

tablename
department
course
instructor
section
classroom
teaches
student
takes
advisor
time_slot
prereq

instructor that not teach courses (IN subquery)

1
2
3


SELECT *
FROM instructor
WHERE instructor.id NOT IN (SELECT id FROM teaches);

id	name	dept_name	salary
33456	Gold	Physics	87000.00
58583	Califieri	History	62000.00
76543	Singh	Finance	80000.00

alternative correlating on i.id (NOT EXISTS subquery with correlations)

1
2
3


SELECT *
FROM instructor i
WHERE NOT EXISTS (SELECT * FROM teaches t WHERE i.id = t.id);

id	name	dept_name	salary
33456	Gold	Physics	87000.00
58583	Califieri	History	62000.00
76543	Singh	Finance	80000.00

for each instructor the number of teaching assignments (scalar subquery)

1
2


SELECT name, (SELECT count(*) FROM teaches t WHERE i.id = t.id) AS numassign
FROM instructor i

name	numassign
Srinivasan	3
Wu	1
Mozart	1
Einstein	1
El Said	1
Gold	0
Katz	2
Califieri	0
Singh	0
Crick	2
Brandt	3
Kim	1

use FROM subquery - COALESCE(a,b,c...) return the first non-null value

1
2
3
4
5
6


SELECT name, COALESCE(numassign,0) AS numassign
FROM instructor i
     LEFT OUTER JOIN
     (SELECT t.id AS tid, count(*) AS numassign
      FROM teaches t GROUP BY t.id) na
     ON (i.id = na.tid);

name	numassign
Srinivasan	3
Wu	1
Mozart	1
Einstein	1
El Said	1
Gold	0
Katz	2
Califieri	0
Singh	0
Crick	2
Brandt	3
Kim	1

use FROM subquery - CASE WHEN c1 THEN e1 WHEN c2 THEN e2 ... ELSE en END -

1
2
3
4
5
6


SELECT name, (CASE WHEN numassign IS NULL THEN 0 ELSE numassign END) AS numassign
FROM instructor i
     LEFT OUTER JOIN
     (SELECT t.id AS tid, count(*) AS numassign
      FROM teaches t GROUP BY t.id) na
     ON (i.id = na.tid);

name	numassign
Srinivasan	3
Wu	1
Mozart	1
Einstein	1
El Said	1
Gold	0
Katz	2
Califieri	0
Singh	0
Crick	2
Brandt	3
Kim	1

universal quantification as double negation \(∀ x: ɸ(x) ⇔ ¬ ∃ x: ¬ ɸ(x)

1
2
3
4
5
6
7
8
9


SELECT *
FROM instructor i
WHERE NOT EXISTS (SELECT c.course_id
                  FROM course c
                  WHERE i.dept_name = c.dept_name
                        AND NOT EXISTS (SELECT *
                                        FROM teaches t
                                        WHERE c.course_id = t.course_id
                                              AND i.id = t.id))

id	name	dept_name	salary
12121	Wu	Finance	90000.00
15151	Mozart	Music	40000.00
22222	Einstein	Physics	95000.00
32343	El Said	History	60000.00
98345	Kim	Elec. Eng.	80000.00

Lecture <2026-02-02 Mon>

Project topic selection

movie theater seat booking (35)
HR payroll (0)
gift tracker (birthday tracker) (10)
animal shelter system (25)
web store (10)
hospital admittance system(~20)
financial budget tracker (personal finance management) (13)

Relational Algebra

\list;

database relations: department(dept_name:string, building:string, budget:number) course(course_id:string, title:string, dept_name:string, credits:number) instructor(id:string, name:string, dept_name:string, salary:number) section(course_id:string, sec_id:string, semester:string, year:number, building:string, room_number:string, time_slot_id:string) classroom(building:string, room_number:string, capacity:number) teaches(id:string, course_id:string, sec_id:string, semester:string, year:number) student(id:string, name:string, dept_name:string, tot_cred:number) takes(id:string, course_id:string, sec_id:string, semester:string, year:number, grade:string) advisor(s_id:string, i_id:string) time_slot(time_slot_id:string, day:string, start_hr:number, start_min:number, end_hr:number, end_min:number) prereq(course_id:string, prereq_id:string)

// join instructor with advisor
instructor \join_{i_id = id} advisor;

id:string	name:string	dept_name:string	salary:number	s_id:string	i_id:string
45565	Katz	Comp. Sci.	75000.00	00128	45565
10101	Srinivasan	Comp. Sci.	65000.00	12345	10101
76543	Singh	Finance	80000.00	23121	76543
22222	Einstein	Physics	95000.00	44553	22222
22222	Einstein	Physics	95000.00	45678	22222
45565	Katz	Comp. Sci.	75000.00	76543	45565
98345	Kim	Elec. Eng.	80000.00	76653	98345
98345	Kim	Elec. Eng.	80000.00	98765	98345
76766	Crick	Biology	72000.00	98988	76766

get adviser - advisee pairs

// get adivsior-advisee pairs by joining instructors and students throught the advisor table
\project_{iname,name}(\rename_{iid,s_id,iname}(\project_{i_id,s_id,name}(instructor \join_{i_id = id} advisor)) \join_{s_id = id} student);

iname:string	name:string
Katz	Zhang
Srinivasan	Shankar
Singh	Chavez
Kim	Aoi
Kim	Bourikas
Einstein	Peltier
Crick	Tanaka
Katz	Brown
Einstein	Levy

number of students per department

\aggr_{dept_name:count(1)}(student);

dept_name:string	_:number
Finance	1
History	1
Physics	3
Music	1
Comp. Sci.	4
Biology	1
Elec. Eng.	2

large departments (3+ student)

// calculate number of
\select_{numst > 2}( // only retain departments with > 2 students
  \rename_{dept,numst}( // rename attributes
     \aggr_{dept_name:count(1)}( // compute number of students per department
        student)));

dept:string	numst:number
Physics	3
Comp. Sci.	4

department with max students

// compute number of students per department
numst :- \rename_{dept,numst}(\aggr_{dept_name:count(1)}(student));
// number of students of the department with the most students
maxst :- \rename_{numst}(\aggr_{max(numst)}(numst));
// get the department by joining back
numst \join maxst;

view numst defined view maxst defined

dept:string	numst:number
Comp. Sci.	4

Lecture <2026-01-28 Wed>

Expressing min and max without the aggregation operator

logic

To determine the maximum we can find values for which at least one larger value exists (these are the values we do not want). This can be done by joining the table with itself on A < A if A is the column for which we want to compute the maximum and then projecting on the left copy of the A attribute in the result of the join. To get the maximum remove from the whole column A the values we do not want using set difference.

University example

Calculate the minimum total_cred value in the student table.

generate all pairs of total_cred values from the student table through cross product

1

./radb.sh "\project_{tot_cred}(student) \cross \rename_{t2}(\project_{tot_cred}(student));"

tot_cred:number	t2:number
60	60
60	120
60	110
60	56
60	46
60	80
60	58
60	102
60	54
60	0
60	98
60	38
60	32
120	60
120	120
120	110
120	56
120	46
120	80
120	58
120	102
120	54
120	0
120	98
120	38
120	32
110	60
110	120
110	110
110	56
110	46
110	80
110	58
110	102
110	54
110	0
110	98
110	38
110	32
56	60
56	120
56	110
56	56
56	46
56	80
56	58
56	102
56	54
56	0
56	98
56	38
56	32
46	60
46	120
46	110
46	56
46	46
46	80
46	58
46	102
46	54
46	0
46	98
46	38
46	32
80	60
80	120
80	110
80	56
80	46
80	80
80	58
80	102
80	54
80	0
80	98
80	38
80	32
58	60
58	120
58	110
58	56
58	46
58	80
58	58
58	102
58	54
58	0
58	98
58	38
58	32
102	60
102	120
102	110
102	56
102	46
102	80
102	58
102	102
102	54
102	0
102	98
102	38
102	32
54	60
54	120
54	110
54	56
54	46
54	80
54	58
54	102
54	54
54	0
54	98
54	38
54	32
0	60
0	120
0	110
0	56
0	46
0	80
0	58
0	102
0	54
0	0
0	98
0	38
0	32
98	60
98	120
98	110
98	56
98	46
98	80
98	58
98	102
98	54
98	0
98	98
98	38
98	32
38	60
38	120
38	110
38	56
38	46
38	80
38	58
38	102
38	54
38	0
38	98
38	38
38	32
32	60
32	120
32	110
32	56
32	46
32	80
32	58
32	102
32	54
32	0
32	98
32	38
32	32

find set of total_cred values for which at least one larger one exists (that are not maximal). Note how 120 (the maximum) disappeared.

1

./radb.sh "\project_{tot_cred}(\select_{tot_cred < t2}(\project_{tot_cred}(student) \cross \rename_{t2}(\project_{tot_cred}(student))));"

tot_cred:number
60
110
56
46
80
58
102
54
0
98
32
38

take the whole set of total_cred values from the student table and remove the ones that are not maximal => the remaining ones are the maximal ones

1

./radb.sh "\project_{tot_cred}(student) \diff \project_{tot_cred}(\select_{tot_cred < t2}(\project_{tot_cred}(student) \cross \rename_{t2}(\project_{tot_cred}(student))));"

tot_cred:number
120

Lecture <2026-01-21 Wed>

Cross product

\(\mathbb{D}_1 = \{ 1, 2 \}\)
\(\mathbb{D}_2 = \{ a, b \}\)
cross product: \(\mathbb{D}_1 \times \mathbb{D}_2\) = \( \{ (1,a), (2,a), (1,b) , (2,b) \} \)
\( \{B\}\) is a key
any set with \(B\) such as \(\{A,B\}\) is unique
\(\{A,B,C\}\) is always a key
\(\{A,C\}\)

A	B	C
a	1	1
b	2	3
a	3	3

A	C
a	1
b	3
a	3

A	B	C
a	1	1
a	2	3
a	3	3

set difference

\( \{a,b,c \} - \{b,d\} = \{a,c\} \)
\( \{a,b\} \cup \{b,c\} = \{a,b,c\}\)

tot_cred:number	t2:number
60	60
60	120
60	110
60	56
60	46
60	80
60	58
60	102
60	54
60	0
60	98
60	38
60	32
120	60
120	120
120	110
120	56
120	46
120	80
120	58
120	102
120	54
120	0
120	98
120	38
120	32
110	60
110	120
110	110
110	56
110	46
110	80
110	58
110	102
110	54
110	0
110	98
110	38
110	32
56	60
56	120
56	110
56	56
56	46
56	80
56	58
56	102
56	54
56	0
56	98
56	38
56	32
46	60
46	120
46	110
46	56
46	46
46	80
46	58
46	102
46	54
46	0
46	98
46	38
46	32
80	60
80	120
80	110
80	56
80	46
80	80
80	58
80	102
80	54
80	0
80	98
80	38
80	32
58	60
58	120
58	110
58	56
58	46
58	80
58	58
58	102
58	54
58	0
58	98
58	38
58	32
102	60
102	120
102	110
102	56
102	46
102	80
102	58
102	102
102	54
102	0
102	98
102	38
102	32
54	60
54	120
54	110
54	56
54	46
54	80
54	58
54	102
54	54
54	0
54	98
54	38
54	32
0	60
0	120
0	110
0	56
0	46
0	80
0	58
0	102
0	54
0	0
0	98
0	38
0	32
98	60
98	120
98	110
98	56
98	46
98	80
98	58
98	102
98	54
98	0
98	98
98	38
98	32
38	60
38	120
38	110
38	56
38	46
38	80
38	58
38	102
38	54
38	0
38	98
38	38
38	32
32	60
32	120
32	110
32	56
32	46
32	80
32	58
32	102
32	54
32	0
32	98
32	38
32	32

tot_cred:number	t2:number
60	60
60	120
60	110
60	56
60	46
60	80
60	58
60	102
60	54
60	0
60	98
60	38
60	32
120	60
120	120
120	110
120	56
120	46
120	80
120	58
120	102
120	54
120	0
120	98
120	38
120	32
110	60
110	120
110	110
110	56
110	46
110	80
110	58
110	102
110	54
110	0
110	98
110	38
110	32
56	60
56	120
56	110
56	56
56	46
56	80
56	58
56	102
56	54
56	0
56	98
56	38
56	32
46	60
46	120
46	110
46	56
46	46
46	80
46	58
46	102
46	54
46	0
46	98
46	38
46	32
80	60
80	120
80	110
80	56
80	46
80	80
80	58
80	102
80	54
80	0
80	98
80	38
80	32
58	60
58	120
58	110
58	56
58	46
58	80
58	58
58	102
58	54
58	0
58	98
58	38
58	32
102	60
102	120
102	110
102	56
102	46
102	80
102	58
102	102
102	54
102	0
102	98
102	38
102	32
54	60
54	120
54	110
54	56
54	46
54	80
54	58
54	102
54	54
54	0
54	98
54	38
54	32
0	60
0	120
0	110
0	56
0	46
0	80
0	58
0	102
0	54
0	0
0	98
0	38
0	32
98	60
98	120
98	110
98	56
98	46
98	80
98	58
98	102
98	54
98	0
98	98
98	38
98	32
38	60
38	120
38	110
38	56
38	46
38	80
38	58
38	102
38	54
38	0
38	98
38	38
38	32
32	60
32	120
32	110
32	56
32	46
32	80
32	58
32	102
32	54
32	0
32	98
32	38
32	32

tot_cred:number	t2:number
60	60
60	120
60	110
60	56
60	46
60	80
60	58
60	102
60	54
60	0
60	98
60	38
60	32
120	60
120	120
120	110
120	56
120	46
120	80
120	58
120	102
120	54
120	0
120	98
120	38
120	32
110	60
110	120
110	110
110	56
110	46
110	80
110	58
110	102
110	54
110	0
110	98
110	38
110	32
56	60
56	120
56	110
56	56
56	46
56	80
56	58
56	102
56	54
56	0
56	98
56	38
56	32
46	60
46	120
46	110
46	56
46	46
46	80
46	58
46	102
46	54
46	0
46	98
46	38
46	32
80	60
80	120
80	110
80	56
80	46
80	80
80	58
80	102
80	54
80	0
80	98
80	38
80	32
58	60
58	120
58	110
58	56
58	46
58	80
58	58
58	102
58	54
58	0
58	98
58	38
58	32
102	60
102	120
102	110
102	56
102	46
102	80
102	58
102	102
102	54
102	0
102	98
102	38
102	32
54	60
54	120
54	110
54	56
54	46
54	80
54	58
54	102
54	54
54	0
54	98
54	38
54	32
0	60
0	120
0	110
0	56
0	46
0	80
0	58
0	102
0	54
0	0
0	98
0	38
0	32
98	60
98	120
98	110
98	56
98	46
98	80
98	58
98	102
98	54
98	0
98	98
98	38
98	32
38	60
38	120
38	110
38	56
38	46
38	80
38	58
38	102
38	54
38	0
38	98
38	38
38	32
32	60
32	120
32	110
32	56
32	46
32	80
32	58
32	102
32	54
32	0
32	98
32	38
32	32