SELECT with PostgreSQL-specific features - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the PostgreSQL-specific keyword RETURNING do?

The RETURNING clause in PostgreSQL allows you to return values from rows that are inserted, updated, or deleted, without needing a separate SELECT query. It is often used with INSERT, UPDATE, or DELETE statements to get the affected rows immediately.

intermediate

How does the SELECT DISTINCT ON clause work in PostgreSQL?

SELECT DISTINCT ON (expression) returns the first row for each unique value of the expression. It is useful to get one row per group, unlike standard DISTINCT which removes duplicate rows entirely.

intermediate

What is the purpose of the FILTER clause in aggregate functions in PostgreSQL?

The FILTER clause lets you apply a condition inside an aggregate function to include only rows that meet that condition. For example, COUNT(*) FILTER (WHERE status = 'active') counts only rows where status is 'active'.

advanced

Explain the use of the WITH ORDINALITY clause in PostgreSQL.

WITH ORDINALITY adds a column that numbers the rows returned by a set-returning function or a table expression. This is useful when you want to keep track of the order of rows generated by functions like unnest().

advanced

What does the TABLESAMPLE clause do in a PostgreSQL SELECT query?

TABLESAMPLE allows you to retrieve a random sample of rows from a table. It is useful for testing or analysis when you don't need the full dataset. Different sampling methods like BERNOULLI or SYSTEM can be specified.

What does SELECT DISTINCT ON (column) return in PostgreSQL?

A The first row for each unique value in the specified column

B All rows with duplicates removed from the entire result

C Only rows where the column is not null

D Rows sorted by the specified column

SELECT DISTINCT ON (column) returns the first row for each unique value in that column, unlike standard DISTINCT which removes all duplicate rows.

Which clause allows you to get the affected rows immediately after an INSERT in PostgreSQL?

A WITH ORDINALITY

B RETURNING

C FILTER

D TABLESAMPLE

The RETURNING clause returns rows affected by INSERT, UPDATE, or DELETE statements immediately.

What does the FILTER clause do inside an aggregate function?

A Filters columns to include in aggregation

B Filters the final result set after aggregation

C Filters rows before aggregation based on a condition

D Filters the query plan for optimization

FILTER applies a condition inside an aggregate function to include only rows that meet that condition during aggregation.

What is the purpose of WITH ORDINALITY in PostgreSQL?

A Adds a row number column to rows returned by a set-returning function

B Filters rows based on ordinal values

C Sorts rows in ascending order

D Limits the number of rows returned

WITH ORDINALITY adds a column numbering the rows from set-returning functions, helping track their order.

Which sampling methods can be used with TABLESAMPLE in PostgreSQL?

A RANDOM and SEQUENTIAL

B HASH and RANGE

C FULL and PARTIAL

D BERNOULLI and SYSTEM

TABLESAMPLE supports sampling methods like BERNOULLI and SYSTEM to retrieve random rows.

Describe how you would use the RETURNING clause in PostgreSQL and why it is useful.

Think about how to get data from changed rows without extra queries.

Explain the difference between DISTINCT and DISTINCT ON in PostgreSQL SELECT queries.

Focus on how each handles duplicates and grouping.

Practice

1. What does the LIMIT clause do in a PostgreSQL SELECT query?

easy

A. It combines rows from two tables.

B. It restricts the number of rows returned by the query.

C. It removes duplicate rows from the result.

D. It sorts the rows in ascending order.

Solution

1. Step 1: Understand the purpose of LIMIT

   The LIMIT clause is used to specify the maximum number of rows to return from a query result.

2. Step 2: Compare with other options

   Sorting is done by ORDER BY, removing duplicates by DISTINCT, and combining tables by JOIN, so these are not correct.

3. Final Answer:

   It restricts the number of rows returned by the query. -> Option B

4. Quick Check:

   LIMIT = restrict rows [OK]

Hint: LIMIT caps rows returned, like a page size [OK]

Common Mistakes:

• Confusing LIMIT with ORDER BY
• Thinking LIMIT removes duplicates
• Mixing LIMIT with JOIN functionality

2. Which of the following is the correct syntax to select distinct city names from a table called customers?

easy

A. SELECT city FROM DISTINCT customers;

B. SELECT city DISTINCT FROM customers;

C. SELECT DISTINCT city FROM customers;

D. SELECT city FROM customers DISTINCT;

Solution

1. Step 1: Recall correct DISTINCT syntax

   In PostgreSQL, DISTINCT comes immediately after SELECT and before the column list.

2. Step 2: Validate each option

   SELECT DISTINCT city FROM customers; correctly places DISTINCT after SELECT. Options A, B, and D misuse DISTINCT placement causing syntax errors.

3. Final Answer:

   SELECT DISTINCT city FROM customers; -> Option C

4. Quick Check:

   SELECT DISTINCT = correct syntax [OK]

Hint: DISTINCT always follows SELECT keyword [OK]

Common Mistakes:

• Placing DISTINCT after column names
• Using DISTINCT after FROM clause
• Misordering keywords causing syntax errors

3. Given the table products with columns id, name, and price, what will this query return?

SELECT name FROM products ORDER BY price DESC LIMIT 2;

medium

A. The two products with the lowest prices.

B. An error because LIMIT cannot be used with ORDER BY.

C. All products sorted by price descending.

D. The two products with the highest prices.

Solution

1. Step 1: Understand ORDER BY with DESC

   ORDER BY price DESC sorts products from highest to lowest price.

2. Step 2: Apply LIMIT 2

   LIMIT 2 restricts the output to only the first two rows after sorting, so the two most expensive products.

3. Final Answer:

   The two products with the highest prices. -> Option D

4. Quick Check:

   ORDER BY DESC + LIMIT = top rows [OK]

Hint: ORDER BY DESC + LIMIT picks top items [OK]

Common Mistakes:

• Thinking LIMIT returns lowest prices
• Believing LIMIT causes syntax error with ORDER BY
• Ignoring DESC means descending order

4. Identify the error in this PostgreSQL query:

SELECT DISTINCT name, price FROM products LIMIT;

medium

A. LIMIT requires a number to specify how many rows to return.

B. DISTINCT cannot be used with multiple columns.

C. SELECT must include WHERE clause when using LIMIT.

D. The table name is missing.

Solution

1. Step 1: Check LIMIT usage

   LIMIT must be followed by a number indicating how many rows to return; here it is missing.

2. Step 2: Validate other parts

   DISTINCT can be used with multiple columns, WHERE is optional, and table name is present as products.

3. Final Answer:

   LIMIT requires a number to specify how many rows to return. -> Option A

4. Quick Check:

   LIMIT needs a number [OK]

Hint: Always put a number after LIMIT [OK]

Common Mistakes:

• Omitting number after LIMIT
• Thinking DISTINCT can't use multiple columns
• Assuming WHERE is mandatory with LIMIT

5. You want to get the third and fourth highest salaries from the employees table with column salary. Which query correctly achieves this in PostgreSQL?

hard

A. SELECT salary FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 2;

B. SELECT salary FROM employees ORDER BY salary ASC LIMIT 2 OFFSET 3;

C. SELECT DISTINCT salary FROM employees LIMIT 2 OFFSET 3 ORDER BY salary DESC;

D. SELECT salary FROM employees LIMIT 2 OFFSET 2 ORDER BY salary DESC;

Solution

1. Step 1: Understand OFFSET and LIMIT

   OFFSET 2 skips the first two highest salaries, LIMIT 2 returns the next two rows.

2. Step 2: Check ORDER BY direction

   ORDER BY salary DESC sorts salaries from highest to lowest, so OFFSET 2 skips top two highest.

3. Step 3: Validate options

   SELECT salary FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 2; correctly orders DESC, uses LIMIT 2 and OFFSET 2. SELECT salary FROM employees ORDER BY salary ASC LIMIT 2 OFFSET 3; orders ASC (wrong direction), C misplaces ORDER BY, D places ORDER BY after LIMIT (syntax error).

4. Final Answer:

   SELECT salary FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 2; -> Option A

5. Quick Check:

   OFFSET skips rows, LIMIT picks next [OK]

Hint: Use OFFSET to skip, LIMIT to pick rows [OK]

Common Mistakes:

• Using ASC instead of DESC for highest values
• Placing ORDER BY after LIMIT
• Confusing OFFSET number with row count