Databases & SQL worksheet with answers

In PostgreSQL and MySQL, which clause returns only the first 10 rows of a result?

1. ATOP ROWS 10
2. BMAX 10
3. CFIRST 10 ONLY
4. DLIMIT 10

What is a candidate key?

1. AAny column that contains NULL values
2. BA column that is waiting to become a foreign key
3. CA set of columns shared between two tables to link their rows
4. DA minimal set of columns that uniquely identifies each row

Statements such as CREATE, ALTER and DROP belong to which part of SQL?

1. AData Control Language (DCL)
2. BData Definition Language (DDL)
3. CData Manipulation Language (DML)
4. DTransaction Control Language (TCL)

First normal form (1NF) requires that:

1. AEach column contains atomic values and there are no repeating groups
2. BEvery table has exactly one column
3. CThere are no foreign keys
4. DEvery non-key column depends on the whole key and on nothing but the key

A table stores the customer's address on every order row. Changing the address in one row but not in the others is an example of:

1. AAn update anomaly
2. BAn insertion anomaly
3. CA deletion anomaly
4. DA phantom read

Which query returns the number of orders per customer?

1. ASELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id
2. BSELECT DISTINCT COUNT(*) FROM orders
3. CSELECT customer_id, COUNT(*) FROM orders ORDER BY customer_id
4. DSELECT customer_id FROM orders WHERE COUNT(*) > 0

Transaction T1 locks row A and waits for row B, while T2 locks row B and waits for row A. What does a typical DBMS do?

1. ALets both transactions wait until one of them commits normally
2. BDetects the deadlock and aborts one transaction as the victim
3. CCommits both transactions at once
4. DMerges the two transactions into one

What does the write-ahead logging (WAL) rule require?

1. AEvery query must be logged before it is parsed
2. BData pages must be written before the log
3. CThe log is written only after a crash
4. DA change's log record must reach stable storage before the changed data page does