Why PostgreSQL advanced features matter - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query using JSONB operators?

Given a table products with a details column of type jsonb, what will this query return?

SELECT details->>'color' AS color FROM products WHERE details @> '{"size": "M"}';

PostgreSQL

SELECT details->>'color' AS color FROM products WHERE details @> '{"size": "M"}';

A Syntax error due to JSON syntax

B All colors of products where size is 'M'

C All products with any size and color 'M'

D All sizes of products where color is 'M'

💡 Hint

The operator @> checks if the JSONB column contains the specified JSON.

The query filters products whose details JSONB contains the key-value pair size='M'. It then selects the color value from details.

🧠 Conceptual

intermediate

Why use PostgreSQL's CTEs (WITH clauses)?

Which of the following best explains why Common Table Expressions (CTEs) are useful in PostgreSQL?

A They replace the need for transactions in multi-step operations.

B They automatically create indexes on tables used in the query.

C They allow breaking complex queries into simpler parts and can improve readability and maintainability.

D They are used only for creating temporary tables.

💡 Hint

Think about how CTEs help organize queries.

CTEs let you define temporary named result sets that can be referenced within a query, making complex queries easier to read and maintain.

📝 Syntax

advanced

Which query correctly uses the UPSERT feature in PostgreSQL?

You want to insert a new user or update their email if the username already exists. Which query is correct?

PostgreSQL

Table: users(username TEXT PRIMARY KEY, email TEXT)

A INSERT INTO users(username, email) VALUES('alice', '[e-mail]') ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;

B INSERT INTO users(username, email) VALUES('alice', '[e-mail]') ON DUPLICATE KEY UPDATE email = VALUES(email);

C INSERT INTO users(username, email) VALUES('alice', '[e-mail]') ON CONFLICT DO NOTHING;

D UPDATE users SET email = '[e-mail]' WHERE username = 'alice' ELSE INSERT INTO users(username, email) VALUES('alice', '[e-mail]');

💡 Hint

PostgreSQL uses ON CONFLICT syntax for UPSERT.

Option A uses the correct PostgreSQL syntax for UPSERT with ON CONFLICT and EXCLUDED keyword.

❓ optimization

advanced

How does PostgreSQL's partial index improve query performance?

Consider a table orders with a status column. You create a partial index on status = 'pending'. What is the main benefit?

A The index duplicates the entire table for faster access.

B The index automatically updates all rows regardless of status.

C The index prevents insertion of rows with status other than 'pending'.

D The index is smaller and faster because it only includes rows where status is 'pending'.

💡 Hint

Think about how indexing fewer rows affects speed and size.

Partial indexes include only rows matching the condition, making them smaller and faster for queries filtering on that condition.

🔧 Debug

expert

Why does this PostgreSQL query raise an error?

Given the query:

SELECT * FROM employees WHERE department = 'Sales' AND salary > (SELECT AVG(salary) FROM employees);

Does it raise an error about subquery returning more than one row? Why?

A The subquery returns a single value, so the error must be from elsewhere.

B The subquery returns multiple rows because it lacks a GROUP BY clause and is correlated incorrectly.

C The subquery returns multiple rows because AVG(salary) is not aggregated properly.

D The subquery returns multiple rows because the table has duplicate salaries.

💡 Hint

Check if the subquery is correlated or not and what it returns.

The subquery uses AVG(), an aggregate function that always returns a single scalar value regardless of the number of rows. It is not correlated and uses proper aggregation, so it does not return multiple rows.