SELECT with PostgreSQL-specific features - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query using PostgreSQL's DISTINCT ON?

Consider the table employees with columns department and salary. What rows does this query return?

SELECT DISTINCT ON (department) department, salary FROM employees ORDER BY department, salary DESC;

PostgreSQL

CREATE TABLE employees (department TEXT, salary INT);
INSERT INTO employees VALUES
('Sales', 5000),
('Sales', 6000),
('HR', 4500),
('HR', 4700),
('IT', 7000);

SELECT DISTINCT ON (department) department, salary FROM employees ORDER BY department, salary DESC;

A [{'Sales', 5000}, {'HR', 4500}, {'IT', 7000}]

B [{'Sales', 6000}, {'HR', 4700}, {'IT', 7000}]

C [{'Sales', 6000}, {'HR', 4500}, {'IT', 7000}]

D [{'Sales', 5000}, {'HR', 4700}, {'IT', 7000}]

💡 Hint

DISTINCT ON returns the first row of each group based on the ORDER BY clause.

DISTINCT ON (department) picks the first row per department ordered by salary descending, so highest salary per department is returned.

❓ query_result

intermediate

What does this query using PostgreSQL's RETURNING clause output?

Given the table products with columns id and price, what is the output of this query?

UPDATE products SET price = price * 1.1 WHERE id = 2 RETURNING id, price;

PostgreSQL

CREATE TABLE products (id INT PRIMARY KEY, price NUMERIC);
INSERT INTO products VALUES (1, 100), (2, 200);

UPDATE products SET price = price * 1.1 WHERE id = 2 RETURNING id, price;

A [{id: 2, price: 220}]

B [{id: 1, price: 100}, {id: 2, price: 220}]

C []

D [{id: 2, price: 200}]

💡 Hint

RETURNING shows the rows affected by the UPDATE.

Only the row with id=2 is updated, price multiplied by 1.1, so price becomes 220.

📝 Syntax

advanced

Which option correctly uses PostgreSQL's JSONB operators to extract a value?

Given a table data with a JSONB column info, which query correctly extracts the text value of the key name?

PostgreSQL

SELECT info->>'name' FROM data;

A SELECT info->>name FROM data;

B SELECT info->'name' FROM data;

C SELECT info->>'name' FROM data;

D SELECT info->name FROM data;

💡 Hint

Use ->> with quotes around the key to get text.

The operator ->> extracts the JSON value as text. The key must be a string literal in quotes.

❓ optimization

advanced

Which index type is best for optimizing full-text search in PostgreSQL?

You want to speed up searches on a text column using PostgreSQL's full-text search. Which index type should you create?

A CREATE INDEX idx ON table USING gin (to_tsvector('english', column));

B CREATE INDEX idx ON table USING btree (column);

C CREATE INDEX idx ON table USING hash (column);

D CREATE INDEX idx ON table USING gist (column);

💡 Hint

Full-text search uses tsvector and GIN indexes.

GIN indexes on to_tsvector expressions are optimized for full-text search queries.

🧠 Conceptual

expert

What is the effect of the PostgreSQL-specific clause FOR UPDATE SKIP LOCKED in a SELECT query?

Consider a SELECT query with FOR UPDATE SKIP LOCKED. What does this clause do?

A It causes the query to wait until all rows are unlocked before returning.

B It skips locking any rows and returns all rows immediately.

C It locks all rows in the table regardless of selection.

D It locks selected rows and skips rows already locked by other transactions.

💡 Hint

SKIP LOCKED avoids waiting on locked rows.

FOR UPDATE locks rows selected, but SKIP LOCKED skips rows locked by others, allowing non-blocking row selection.