Advanced SQL test (Advanced)

Sample questions

5 sample questions from the Advanced SQL test, with the answers. Samples are taken out of the live test, so you will not meet these when you sit it.

1. Consider a PostgreSQL table named 'sales' that is partitioned using list partitioning on the 'region' column, with the following partitions: 'sales_2023_q3' for regions 'North America' and 'Europe', and 'sales_2023_q4' for regions 'Asia' and 'Africa'. If a query runs with the filter 'WHERE region IN ('North America', 'Europe')', which partitions will be accessed?

   1. The query will return data from the 'sales_2023_q3' and 'sales_2023_q4' partitions, as the filter matches their list values.
   2. The query will return data from all partitions, as list partitioning allows wildcard filtering.
   3. The query will fail due to a syntax error in the WHERE clause.
   4. The query will return data from the 'sales_2023_q3' partition only, as it is the first partition defined.

   Answer: A. The query will return data from the 'sales_2023_q3' and 'sales_2023_q4' partitions, as the filter matches their list values.

   In PostgreSQL, when using list partitioning, the query will only access the partitions that match the exact values in the list. Since the query uses 'WHERE region IN ('North America', 'Europe')', it will only return data from the 'sales_2023_q3' and 'sales_2023_q4' partitions, as those are the ones defined with the specified region values. Other partitions are skipped due to partition pruning.

2. Which isolation level in SQL ensures that no phantom reads or lost updates can occur, providing the highest level of transaction isolation?

   1. Read committed
   2. Serializable
   3. Read uncommitted
   4. Repeatable read

   Answer: B. Serializable

   Serializable is the highest isolation level in SQL, preventing all anomalies including phantom reads and lost updates. It ensures that transactions are processed in a completely isolated manner, as if they were executed sequentially. The other options are lower isolation levels that allow certain anomalies to occur.

3. In a high-concurrency application where frequent updates are made to a large table, which of the following strategies is most effective for preventing lock escalation?

   1. Increase the transaction isolation level to serializable
   2. Perform vacuum full on the table regularly
   3. Use row-level locking instead of table-level locking
   4. Implement read-write split configuration

   Answer: C. Use row-level locking instead of table-level locking

   Lock escalation occurs when a database system promotes many row-level locks to a single table-level lock to reduce overhead. To prevent this, row-level locking should be used in scenarios where multiple rows are being accessed, as it reduces the likelihood of escalation and ensures finer-grained concurrency control.

4. In a PostgreSQL database using MVCC, what happens when a transaction encounters a serialization failure during a write operation, such as a conflict with another transaction?

   1. The transaction will be rolled back manually by the application, and the changes will not be visible to other transactions.
   2. The transaction will be committed, and the changes will be visible to other transactions.
   3. The transaction will be paused, and the changes will be visible to other transactions.
   4. The transaction will be rolled back automatically by the database, and the changes will not be visible to other transactions.

   Answer: D. The transaction will be rolled back automatically by the database, and the changes will not be visible to other transactions.

   In PostgreSQL, if a transaction encounters a serialization failure (e.g., due to a conflict with another transaction), it will be rolled back automatically. This is because MVCC ensures that transactions are isolated and consistent, and any conflicting transactions are aborted to maintain data integrity. The changes made by the transaction are not visible to other transactions, as they are rolled back and the database reverts to a consistent state.

5. You are designing a partitioning strategy for a time-series table storing historical sensor data. The table is expected to grow to millions of rows, and queries will frequently filter by date ranges. Which approach would best optimize query performance and data management?

   1. Use range partitioning with monthly intervals and prune partitions using the partition key in the WHERE clause.
   2. Use hash partitioning with a high-cardinality column and avoid partition pruning.
   3. Use list partitioning with arbitrary values and skip pruning partitions.
   4. Use range partitioning with yearly intervals and prune partitions using the partition key in the WHERE clause.

   Answer: A. Use range partitioning with monthly intervals and prune partitions using the partition key in the WHERE clause.

   When designing a partitioning strategy for a time-series table with historical data, using range partitioning with monthly intervals ensures efficient query performance and data management. Pruning partitions by including the partition key (e.g., the date column) in the WHERE clause helps the optimizer skip irrelevant partitions, improving query speed. Other options are incorrect because using yearly intervals may result in large partitions that are inefficient for querying, using hash partitioning is not suitable for time-based data, and not using partition pruning would prevent the optimizer from skipping partitions, leading to slower queries.