UCT CSC2001 / 2022
Questions across relational tables
Turn inventory and pricing questions into relational queries, with a synthetic execution that checks a corrected join.
UCT / COURSE CONTEXT / 2022
CSC2001F · Computer Science 2001
Data storage, data structures and database foundations. Individual implementation is described separately below.
SYNTHETIC QUERY EXECUTION
- Two invented products
- Join productLine key
- Filter quantityInStock <100
- One resulting low-stock row
| Product | Stock | Line description |
|---|---|---|
| Demo low stock | 42 | Synthetic demonstration line |
Actual new SQLite execution of the corrected portable query. Synthetic rows, not the original MySQL dataset; no original business records exposed.
ENGINEERING PERSPECTIVE
Inside the work
Translate data questions into explicit relational operations. Joins and aggregates are useful only when key relationships and result grain are understood.
- SQL / Classic Models
- Selection, joins, counts, price aggregates and update exercises on an external sample schema.
- SQLite synthetic check
- A labelled portable example tests the corrected low-stock join without exposing the original dump.
Choose the grain
Decide whether the question concerns product rows, product lines or an aggregate.
- Join contracts
- products.productLine connects a product to its descriptive product-line row.
- Aggregation
- Counts and maximum price answer different questions from returning individual records.
- Transactional changes
- Update scope and rollback behaviour matter before a data-changing query is operationally safe.
What the demonstration establishes
The displayed table is an actual new SQLite execution on invented rows. It does not validate every original statement or MySQL-specific behaviour.
Evidence behind this page
Original query files and a corrected, synthetic portable check; external sample-database ownership is not claimed.
How it works
Join products to productlines through the productLine key, filter inventory and apply aggregates such as counts and maximum buyPrice. A cleaned demo corrects the malformed join-column reference in query13.
Results & recovery
- Original SQL includes a low-stock product/product-line query and a maximum-price calculation.
- A new SQLite-compatible synthetic example verifies the corrected join without publishing the supplied database dump.
Technical limitations
The original MySQL environment was not restored. New SQLite checks cover the selected portable query, not every original statement or MySQL-specific behaviour.
What I would improve now
Use explicit joins, verify foreign-key contracts, make updates transactional and add repeatable query-result tests.