Quiz · Medium
Normal forms, keys, indexes and the leftmost-prefix rule, isolation levels, SQL injection, NULL logic, star schemas and views.
A table stores order_id, customer_id, customer_city, where customer_city depends only on customer_id. Which normal form does this violate?
First normal form
Second normal form
Third normal form (transitive dependency)
It violates none: it is fully normalized
customer_city depends on customer_id, which is a non-key column, so order_id -> customer_id -> customer_city is a transitive dependency (3NF violation). Move customer data to its own table.
A composite index exists on (last_name, first_name). Which query can use it most effectively?
WHERE last_name = 'Rossi'
WHERE first_name = 'Marco'
WHERE LOWER(last_name) = 'rossi'
WHERE last_name LIKE '%ssi'
A composite B-tree index is usable for the leftmost prefix. Filtering only on first_name skips the leading column; wrapping the column in LOWER() or starting a LIKE pattern with % prevents an index seek.
Why does WHERE YEAR(order_date) = 2025 often ignore an index on order_date?
Indexes only work on text columns
YEAR() returns NULL for indexed columns
Indexes cannot be used with equality comparisons
The function on the column prevents using the sorted index order
The index stores order_date values, not YEAR(order_date). Rewrite as a range: order_date >= '2025-01-01' AND order_date < '2026-01-01' to allow an index range scan.
Which isolation level is the default in PostgreSQL?
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
PostgreSQL defaults to READ COMMITTED (each statement sees data committed before it started). MySQL InnoDB defaults to REPEATABLE READ, and PostgreSQL does not implement a true READ UNCOMMITTED.
Transaction 1 runs the same SELECT ... WHERE amount > 100 twice. Between the two runs, transaction 2 inserts and commits a new row with amount 150, and the second run returns an extra row. What is this anomaly called?
Phantom read
Dirty read
Lost update
Non-repeatable read of the same row
A phantom read is a change in the SET of rows matching a condition. A non-repeatable read means the same existing row has a different value on re-read. A dirty read means seeing uncommitted data.
A web app builds "SELECT * FROM users WHERE name = '" + name + "'". What is the best fix for SQL injection?
Remove all quotes from the input by hand
Convert the input to lowercase
Use SELECT DISTINCT
Use parameterized queries with placeholders
Placeholders send the SQL and the values separately, so input can never change the query structure. Manual escaping and filtering are incomplete defenses.
In a star schema, what does one row of the fact table represent?
One descriptive attribute value such as a product category
One event at the declared grain (for example one order line), with numeric measures and dimension keys
A copy of every dimension row
One database user
The grain defines what a fact row is. Dimensions hold the descriptive context. Declaring the grain first prevents double-counting measures.
What does SELECT * FROM t WHERE NULL = NULL return?
All rows
An error
No rows
Rows where the column is NULL
NULL = NULL evaluates to UNKNOWN, and WHERE keeps only rows where the condition is TRUE. Use IS NULL to test for NULL.
NULL OR TRUE in SQL evaluates to:
NULL (unknown)
FALSE
TRUE
An error
Three-valued logic: if either operand of OR is TRUE, the result is TRUE regardless of the unknown. NULL AND FALSE is FALSE for the same reason, while NULL AND TRUE is UNKNOWN.
An orders table has a foreign key to customers declared with ON DELETE CASCADE. What happens when a customer row is deleted?
The customer's orders are deleted automatically
The delete is blocked while orders exist
The orders' customer_id is set to NULL
The orders are kept unchanged and become orphans
CASCADE propagates the delete to child rows. RESTRICT/NO ACTION blocks the delete; SET NULL clears the foreign key. Orphan rows are exactly what foreign keys prevent.
Which type is best for storing prices that must add up exactly?
FLOAT
DOUBLE PRECISION
REAL
NUMERIC(12,2) / DECIMAL
Binary floating-point types cannot represent many decimal fractions exactly, so sums drift. Exact numeric types (or integer cents) avoid that.
What is the main difference between a view and a materialized view?
A view stores its result; a materialized view does not
A materialized view stores its result on disk and must be refreshed; a view runs its query each time
Only a view can be indexed
A materialized view is always updated instantly
A plain view is a saved query. A materialized view trades freshness for speed: reads are fast, but data are as old as the last refresh.
What best describes a covering index for a query?
An index containing every column the query needs, so the table need not be read
An index on every column of the table
A unique index on the primary key
An index that covers all rows with NULL
With all needed columns in the index, the engine can do an index-only scan. It is often built with INCLUDE columns.
Which workload is OLAP?
Inserting one new order when a customer checks out
Updating a single user's email address
Fetching one account by primary key
Aggregating millions of sales rows by month and region for a dashboard
OLAP means large analytical scans and aggregations, usually in a star schema or column store. The other options are classic OLTP operations.
Which statement about DELETE and TRUNCATE is accurate?
TRUNCATE accepts a WHERE clause
DELETE can remove selected rows with WHERE; TRUNCATE empties the whole table quickly
DELETE always resets identity counters
TRUNCATE never needs permissions
TRUNCATE removes all rows with minimal logging and has no WHERE clause. DELETE can target rows, fires row-level triggers and is logged per row.
Which diagram shows an INNER JOIN?

A

B

C

D
Only the overlap of A and B is shaded in diagram B. The others show left, full and right joins. Images: GermanX, CC BY-SA 4.0, via Wikimedia Commons.
What data structure do most relational indexes use, supporting both equality and range lookups?
Hash table only
Linked list
B-tree
Bloom filter
B-trees keep keys sorted in a balanced tree, giving O(log n) lookups and efficient range scans. Hash indexes support only equality.