TABLESAMPLE for random sampling in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What does this TABLESAMPLE query return?

Consider a table employees with 1000 rows. What is the expected output of this query?

SELECT * FROM employees TABLESAMPLE SYSTEM (10);

PostgreSQL

SELECT * FROM employees TABLESAMPLE SYSTEM (10);

A Syntax error due to incorrect TABLESAMPLE usage

B Exactly 10 rows randomly sampled from employees

C All rows where a column named SYSTEM equals 10

D Approximately 10% of rows randomly sampled from employees

💡 Hint

SYSTEM sampling method picks a percentage of rows approximately.

The SYSTEM method in TABLESAMPLE picks approximately the given percentage of rows randomly. So SYSTEM (10) means about 10% of rows.

📝 Syntax

intermediate

Which option is the correct syntax for TABLESAMPLE in PostgreSQL?

Identify the valid TABLESAMPLE syntax to sample 5% of rows from a table named orders.

A SELECT * FROM orders TABLESAMPLE (5) BERNOULLI;

B SELECT * FROM orders TABLESAMPLE BERNOULLI 5%;

C SELECT * FROM orders TABLESAMPLE BERNOULLI (5);

D SELECT * FROM orders TABLESAMPLE SYSTEM 5;

💡 Hint

The sampling method name comes right after TABLESAMPLE, followed by the percentage in parentheses.

The correct syntax is TABLESAMPLE method_name (percentage). Option C follows this format correctly.

❓ optimization

advanced

Which TABLESAMPLE method is more efficient for large tables and why?

You want to sample 1% of rows from a very large table. Which TABLESAMPLE method is generally more efficient and why?

A SYSTEM, because it samples data blocks instead of individual rows

B SYSTEM, because it guarantees exact percentage of rows sampled

C BERNOULLI, because it uses indexes to speed up sampling

D BERNOULLI, because it checks each row individually for sampling

💡 Hint

Think about how sampling by blocks vs rows affects performance.

SYSTEM samples whole data blocks, which is faster on large tables but less precise. BERNOULLI checks each row, which is slower.

🧠 Conceptual

advanced

Why might TABLESAMPLE SYSTEM not return exactly the requested percentage of rows?

Explain why TABLESAMPLE SYSTEM (10) might return more or fewer than exactly 10% of rows.

A Because SYSTEM samples entire data blocks, and block sizes vary, so exact row count varies

B Because SYSTEM filters rows based on a hidden column, causing variation

C Because SYSTEM uses a random seed that changes the percentage each time

D Because SYSTEM rounds the percentage to the nearest integer

💡 Hint

Think about how data is stored physically in blocks.

SYSTEM samples whole blocks, so if blocks have different numbers of rows, the total rows sampled can vary from the exact percentage.

🔧 Debug

expert

What error does this query raise and why?

Given the query:

SELECT * FROM sales TABLESAMPLE BERNOULLI (105);

What error will PostgreSQL raise and why?

PostgreSQL

SELECT * FROM sales TABLESAMPLE BERNOULLI (105);

A ERROR: syntax error near 'BERNOULLI'

B ERROR: sampling percentage must be between 0 and 100

C No error, returns all rows because percentage > 100

D ERROR: table 'sales' does not exist

💡 Hint

Sampling percentage must be a valid percentage value.

PostgreSQL requires the sampling percentage to be between 0 and 100 inclusive. 105 is invalid and causes an error.