Test Yourself!

# Run the three question files; compare with Appendix I. PostgreSQL runs in a container.
duckdb < questions-1-5.sql
python questions-6-8.py
docker run -d --name quiz-pg -e POSTGRES_PASSWORD=quiz postgres:18
until docker exec quiz-pg pg_isready -q; do sleep 1; done; sleep 2
docker exec -i quiz-pg psql -U postgres -q < setup.sql     # tables from Section 1.14.3
docker exec -i quiz-pg psql -U postgres < questions-9-10.sql
docker rm -f quiz-pg

-- 1. Two sums of the same numbers, one as DECIMAL and one as DOUBLE. Are both equal to
--    0.3? (Section 1.7.3)
SELECT 0.1 + 0.2 = 0.3 AS decimal_eq, 0.1::DOUBLE + 0.2::DOUBLE = 0.3 AS double_eq;
-- 2. What does DuckDB return for 7 / 2, and what type does a DECIMAL price divided by an
--    integer page count have? (Section 1.12.3)
SELECT 7 / 2 AS seven_halves, typeof(14.99::DECIMAL(6,2) / 312) AS price_per_page;
-- 3. A product code "007" is written to CSV and read back with schema-on-read. What value
--    and type come back? (Sections 1.7.3 and 1.12.1)
COPY (SELECT '007' AS code) TO 'q3.csv';
SELECT code, typeof(code) AS type FROM 'q3.csv';
-- 4. The same load runs twice. How many rows does each table end with, and does anything
--    fail? (Section 1.1.4)
CREATE TABLE plain (order_id INT, qty INT);
CREATE TABLE keyed (order_id INT PRIMARY KEY, qty INT);
INSERT INTO plain VALUES (1001, 2); INSERT INTO plain VALUES (1001, 2);
INSERT OR REPLACE INTO keyed VALUES (1001, 2); INSERT OR REPLACE INTO keyed VALUES (1001, 2);
SELECT (SELECT count(*) FROM plain) AS plain_rows, (SELECT count(*) FROM keyed) AS keyed_rows;
-- 5. A transaction inserts a new row, then a duplicate key, then commits. Which order IDs
--    does keyed hold afterwards? (Section 1.4.1)
BEGIN;
INSERT INTO keyed VALUES (1002, 1);
INSERT INTO keyed VALUES (1001, 5);
COMMIT;
SELECT list(order_id) AS ids FROM keyed;

import csv, hashlib, io
import pyarrow.csv as pacsv
# 6. Booleans written to CSV and read back with the csv module. How many books does the
#    loop count as in stock? (Section 1.12.1)
buf = io.StringIO()
csv.writer(buf).writerows([["title", "in_stock"], ["Salt and Saffron", False],
                           ["Gardens in Glass", True]])
rows = list(csv.DictReader(io.StringIO(buf.getvalue())))
print("6:", sum(1 for r in rows if r["in_stock"]), "in stock")
# 7. The "007" file from question 3, read by PyArrow instead of DuckDB. What comes back?
#    (Sections 1.7.3 and 1.12.2)
table = pacsv.read_csv(io.BytesIO(b"code\n007\n"))
print("7:", table.column("code")[0].as_py(), table.schema.field("code").type)
# 8. An analyst "anonymizes" emails with plain SHA-256. Can someone holding a customer list
#    tell whose hash this is? (Section 1.14.1)
leaked = hashlib.sha256(b"john.smith@example.com").hexdigest()
customers = ["aiko@example.com", "john.smith@example.com", "mara@example.com"]
print("8:", [c for c in customers if hashlib.sha256(c.encode()).hexdigest() == leaked])

-- 9. The same expression as question 2, in PostgreSQL 18. What does 7 / 2 return?
--    (Sections 1.4.3 and 1.12.3)
SELECT 7 / 2 AS seven_halves;
-- 10. A reader role gets SELECT on every table in the schema; then a new table appears.
--     Can the role read it? (Section 1.14.3)
CREATE ROLE reader NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;
CREATE TABLE refunds (order_id int, amount numeric(8,2));
SET ROLE reader;
SELECT count(*) AS orders FROM orders;
SELECT count(*) AS refunds FROM refunds;