Creating & managing tables — quiz

Which type should store a currency amount?

1. FLOAT
2. DECIMAL(10,2)
3. REAL
4. DOUBLE

Answer: DECIMAL(10,2) — DECIMAL is exact; floating point accumulates rounding error.

CHAR(10) storing "cat" occupies:

1. 3 characters
2. 10 characters, space-padded
3. Variable
4. 1 character

Answer: 10 characters, space-padded — The padding is why CHAR comparisons sometimes fail unexpectedly.

The difference between PRIMARY KEY and UNIQUE:

1. None
2. UNIQUE allows nulls, PRIMARY KEY does not
3. PRIMARY KEY allows duplicates
4. UNIQUE cannot be indexed

Answer: UNIQUE allows nulls, PRIMARY KEY does not — A table also has only one primary key.

A FOREIGN KEY constraint prevents:

1. Duplicate rows
2. Orphaned rows referencing a non-existent parent
3. Null values
4. Slow queries

Answer: Orphaned rows referencing a non-existent parent — It guarantees referential integrity.

UPDATE customers SET credit_limit = 0; with no WHERE:

1. Errors
2. Updates every row
3. Updates nothing
4. Asks for confirmation

Answer: Updates every row — There is no confirmation step outside a transaction.

DELETE differs from TRUNCATE because DELETE:

1. Is faster
2. Can be filtered with WHERE and rolled back
3. Removes the table
4. Resets identity columns

Answer: Can be filtered with WHERE and rolled back — TRUNCATE empties everything and usually cannot be rolled back.

A standard view stores:

1. A copy of the data
2. The query definition
3. An index
4. A snapshot

Answer: The query definition — The query runs each time the view is selected from.

A materialised view differs because it:

1. Runs faster queries by storing the result
2. Cannot be indexed
3. Updates in real time
4. Only works on one table

Answer: Runs faster queries by storing the result — The cost is staleness until it is refreshed.