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.

Practice

1.

What is the main purpose of using TABLESAMPLE in a PostgreSQL query?

easy

A. To retrieve a random subset of rows from a table

B. To sort the table rows in random order

C. To permanently delete random rows from a table

D. To create a backup of the table

Solution

1. Step 1: Understand the purpose of TABLESAMPLE

   TABLESAMPLE is used to get a random sample of rows from a table without scanning the entire table.

2. Step 2: Compare options with TABLESAMPLE usage

   Only To retrieve a random subset of rows from a table describes retrieving a random subset of rows, which matches TABLESAMPLE's purpose.

3. Final Answer:

   To retrieve a random subset of rows from a table -> Option A

4. Quick Check:

   TABLESAMPLE = random subset retrieval [OK]

Hint: TABLESAMPLE returns random rows, not sorting or deleting [OK]

Common Mistakes:

• Confusing TABLESAMPLE with ORDER BY RANDOM()
• Thinking TABLESAMPLE deletes rows
• Assuming TABLESAMPLE creates backups

2.

Which of the following is the correct syntax to sample approximately 10% of rows from a table named employees using the BERNOULLI method?

easy

A. SELECT * FROM employees TABLESAMPLE BERNOULLI(10);

B. SELECT * FROM employees TABLESAMPLE SYSTEM(10);

C. SELECT * FROM employees SAMPLE BERNOULLI(10);

D. SELECT * FROM employees TABLESAMPLE BERNOULLI 10;

Solution

1. Step 1: Recall correct TABLESAMPLE syntax

   The correct syntax uses TABLESAMPLE followed by the method name and percentage in parentheses.

2. Step 2: Check each option

   SELECT * FROM employees TABLESAMPLE BERNOULLI(10); matches the correct syntax exactly. SELECT * FROM employees TABLESAMPLE SYSTEM(10); uses SYSTEM instead of BERNOULLI. SELECT * FROM employees SAMPLE BERNOULLI(10); misses TABLESAMPLE keyword. SELECT * FROM employees TABLESAMPLE BERNOULLI 10; misses parentheses around 10.

3. Final Answer:

   SELECT * FROM employees TABLESAMPLE BERNOULLI(10); -> Option A

4. Quick Check:

   Correct TABLESAMPLE syntax = SELECT * FROM employees TABLESAMPLE BERNOULLI(10); [OK]

Hint: Use TABLESAMPLE METHOD(percentage) with parentheses [OK]

Common Mistakes:

• Omitting TABLESAMPLE keyword
• Using wrong method name
• Missing parentheses around percentage

3.

Given a table products with 1000 rows, what is the expected approximate number of rows returned by this query?

SELECT * FROM products TABLESAMPLE SYSTEM(5);

medium

A. Exactly 5 rows

B. About 50 rows

C. About 500 rows

D. All 1000 rows

Solution

1. Step 1: Understand SYSTEM sampling percentage

   SYSTEM(5) means approximately 5% of rows are sampled.

2. Step 2: Calculate expected rows from 1000 total

   5% of 1000 is 0.05 * 1000 = 50 rows approximately.

3. Final Answer:

   About 50 rows -> Option B

4. Quick Check:

   5% of 1000 = 50 [OK]

Hint: Percentage in TABLESAMPLE is approximate row count percent [OK]

Common Mistakes:

• Confusing percentage with exact row count
• Assuming SYSTEM returns fewer rows than BERNOULLI always
• Thinking TABLESAMPLE returns all rows

4.

Identify the error in this query that tries to sample 20% of rows using BERNOULLI:

SELECT * FROM orders TABLESAMPLE BERNOULLI 20;

medium

A. TABLESAMPLE keyword is misspelled

B. BERNOULLI method cannot be used with TABLESAMPLE

C. Percentage value must be between 0 and 1

D. Missing parentheses around the percentage value

Solution

1. Step 1: Check TABLESAMPLE BERNOULLI syntax

   The percentage must be inside parentheses, like BERNOULLI(20).

2. Step 2: Identify the error in the query

   The query misses parentheses around 20, causing syntax error.

3. Final Answer:

   Missing parentheses around the percentage value -> Option D

4. Quick Check:

   Percentage needs parentheses in TABLESAMPLE [OK]

Hint: Always put percentage in parentheses after method name [OK]

Common Mistakes:

• Omitting parentheses around percentage
• Using invalid percentage range
• Misspelling TABLESAMPLE keyword

5.

You want to quickly test a query on a large table sales but need a random sample that is more uniformly random than SYSTEM sampling. Which query is best?

Options:
A) SELECT * FROM sales TABLESAMPLE SYSTEM(10);
B) SELECT * FROM sales TABLESAMPLE BERNOULLI(10);
C) SELECT * FROM sales ORDER BY RANDOM() LIMIT 100;
D) SELECT * FROM sales TABLESAMPLE SYSTEM(100);

hard

A. Use SYSTEM sampling for speed but less randomness

B. Use ORDER BY RANDOM() for perfect randomness but slower

C. Use BERNOULLI sampling for better randomness

D. Use SYSTEM sampling with 100% to get all rows

Solution

1. Step 1: Compare randomness and speed of methods

   SYSTEM is faster but less random; BERNOULLI is slower but more uniformly random.

2. Step 2: Choose best method for uniform randomness and speed

   BERNOULLI(10) gives a good balance of randomness and reasonable speed for sampling.

3. Final Answer:

   Use BERNOULLI sampling for better randomness -> Option C

4. Quick Check:

   BERNOULLI = better randomness than SYSTEM [OK]

Hint: BERNOULLI is more random; SYSTEM is faster [OK]

Common Mistakes:

• Choosing SYSTEM when better randomness is needed
• Using ORDER BY RANDOM() on large tables (slow)
• Misunderstanding 100% SYSTEM returns all rows