CSE Roadmap

Common tier · about 3 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Create a view (15 minutes)

Write a view listing only books priced above 1000.

Done when: a correct CREATE VIEW ... AS SELECT ... WHERE price > 1000 is given.

Hint

CREATE VIEW pricey_books AS SELECT * FROM books WHERE price > 1000;

Easy 2: Updatable or not (15 minutes)

State whether a view built from SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id is updatable, and why.

Done when: not updatable is correctly stated, with the GROUP BY named as the reason.

Hint

Not updatable, since COUNT(*) and GROUP BY mean a view row represents many underlying rows, not one.

Easy 3: Stale or fresh (15 minutes)

State whether a plain (non-materialized) view ever becomes stale.

Done when: the answer correctly states no, since a plain view always recomputes from current data.

Hint

A plain view always recomputes from current data, so it is never stale.

Medium

Medium 1: Update through a view (25 minutes)

Using the Easy 1 view, write an UPDATE statement through the view that changes one book's price, and confirm it actually changes the underlying books table.

Done when: a correct UPDATE through the view is given, with a note confirming the underlying table changes.

Hint

UPDATE pricey_books SET price = 1100 WHERE book_id = 42; changes the row directly in books.

Medium 2: A join-based view's limits (25 minutes)

Create a view joining orders and customers, then explain why an UPDATE through it, changing a column from each table in the same statement, would not be straightforward.

Done when: the explanation correctly notes the ambiguity of which underlying table a mixed update should apply to.

Hint

The database cannot always tell, from one update statement spanning two joined tables, which underlying row of each table should receive which change.

Medium 3: Decide materialized or not (25 minutes)

Decide whether pune_customers (a simple, cheap filter) or monthly_revenue (an aggregate over the whole order history) is the better candidate to materialize, and justify the choice.

Done when: monthly_revenue is correctly chosen, with a reason tied to the aggregate's higher recomputation cost.

Hint

monthly_revenue aggregates the whole order history on every query, making it far more expensive to recompute than pune_customers' simple filter.

Hard

Hard 1: A view that silently hides a problem (30 minutes)

Explain how a view's stable interface, while good for logical data independence, could also hide a slow underlying query from an application developer who never looks at the view's definition.

Done when: the explanation correctly notes that querying a view can run an expensive hidden query, invisible to someone only looking at the simple SELECT * FROM view_name.

Hint

A developer calling SELECT * FROM monthly_summary_view has no visual indication that it silently performs several joins and an aggregation underneath.

Hard 2: Refresh staleness trade-off (35 minutes)

Describe a concrete business cost of monthly_revenue being refreshed only once a day, versus the cost of refreshing it every minute.

Done when: a concrete trade-off is described, naming the staleness risk on one side and the extra computation cost on the other.

Hint

Daily refresh risks showing numbers up to a day old during a live decision; refreshing every minute costs continuous computation, possibly competing with other workloads.

Hard 3: Nested views (40 minutes)

Explain what happens, performance-wise, if a view is built on top of another view, which is itself built on top of a third view, each adding its own filter.

Done when: the explanation correctly notes the database must resolve and run all three layers' underlying queries every time the outermost view is queried, since none of them are materialized.

Hint

Every query against the outermost view requires the database to expand and execute all three layers' logic together, since none of the intermediate results are stored.