OpenEDG PCAD: SQL for Querying and Manipulating Structured Data; Connecting Python to Databases

What this domain tests

SQL for querying and manipulating structured data, plus connecting Python to a database. The exam tests SELECT, WHERE, GROUP BY, HAVING, and JOIN logic, aggregation, subqueries, and safe parameterized access from Python.

Core rules

Clause order matters: SELECT ... FROM ... JOIN ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT.
WHERE filters rows before aggregation; HAVING filters groups after aggregation.
INNER JOIN keeps matches; LEFT JOIN keeps all left rows with NULLs for misses. Join keys must have compatible types.
Use parameter placeholders, not string formatting: cur.execute('SELECT * FROM t WHERE id = ?', (id,)) for SQLite and %s for psycopg2.
In pandas, pd.read_sql('SELECT ...', conn) returns a DataFrame; for writes, call conn.commit().

Worked example

You need customers with more than two orders: SELECT c.name, COUNT(o.id) AS n FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.name HAVING COUNT(o.id) > 2 ORDER BY n DESC;. Reasoning: the join brings orders to customers; GROUP BY collapses to one row per customer; HAVING applies the count threshold after grouping. If you put COUNT(o.id) > 2 in WHERE, the query fails because aggregates are not available there.

Traps that cost marks

Using WHERE for aggregate filters instead of HAVING.
Comparing to NULL with = NULL instead of IS NULL.
Building SQL with f-strings, which enables injection and breaks on quotes.
Forgetting commit() after INSERT, UPDATE, or DELETE.
Assuming LEFT JOIN plus a WHERE condition on the right table still keeps unmatched rows; it usually filters them out.