Why PostgreSQL over other databases - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why choose PostgreSQL for complex queries?

PostgreSQL is known for its advanced query capabilities. Which feature best explains why it handles complex queries better than many other databases?

A It only supports simple SELECT statements without joins.

B It supports window functions and common table expressions (CTEs) which simplify complex queries.

C It lacks support for subqueries and nested queries.

D It does not allow indexing on columns.

💡 Hint

Think about features that help break down and organize complex data retrieval.

PostgreSQL supports window functions and CTEs, which allow writing readable and efficient complex queries. Other options describe limitations that PostgreSQL does not have.

❓ query_result

intermediate

What is the output of this PostgreSQL query?

Given the table employees(id, name, salary), what will this query return?

SELECT name FROM employees WHERE salary > 50000 ORDER BY salary DESC LIMIT 3;

PostgreSQL

CREATE TABLE employees (id INT, name TEXT, salary INT);
INSERT INTO employees VALUES (1, 'Alice', 60000), (2, 'Bob', 45000), (3, 'Carol', 70000), (4, 'Dave', 55000), (5, 'Eve', 50000);

A Carol, Alice, Dave

B Alice, Dave, Eve

C Bob, Eve, Dave

D Carol, Bob, Alice

💡 Hint

Look for employees with salary above 50000, then order by salary descending and pick top 3.

The employees with salary > 50000 are Carol (70000), Alice (60000), and Dave (55000). Ordered descending, they appear as Carol, Alice, Dave.

📝 Syntax

advanced

Identify the syntax error in this PostgreSQL query

Which option contains a syntax error in the PostgreSQL query?

SELECT * FROM users WHERE age => 30;

A SELECT * FROM users WHERE age => 30;

B SELECT * FROM users WHERE age >= 30;

C SELECT * FROM users WHERE age > 30;

D SELECT * FROM users WHERE age = 30;

💡 Hint

Check the comparison operator syntax carefully.

The operator '=>' is invalid in PostgreSQL for comparisons. The correct operator for 'greater than or equal' is '>='.

❓ optimization

advanced

Which indexing strategy improves query performance in PostgreSQL?

You have a large table with frequent queries filtering by a text column using pattern matching with LIKE 'abc%'. Which index type is best to speed up these queries?

A No index, just rely on sequential scan

B A hash index on the text column

C A GIN index with trigram operators on the text column

D A B-tree index on the text column

💡 Hint

Think about indexes that support fast pattern matching.

B-tree indexes efficiently support pattern matching queries like LIKE 'abc%'. GIN indexes with trigram operators are better for more complex pattern matching but are not necessary for prefix matches. Hash indexes do not support pattern matching.

🔧 Debug

expert

Why does this PostgreSQL transaction cause a deadlock?

Two transactions run concurrently:

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction 2
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Why can this cause a deadlock?

A Because the UPDATE statements have syntax errors.

B Because PostgreSQL does not support transactions.

C Because both transactions try to lock the same rows but in different order, causing a cycle.

D Because the COMMIT statements are missing.

💡 Hint

Think about how locks are acquired on rows during updates.

Transaction 1 locks row with id=1 first, then id=2. Transaction 2 locks id=2 first, then id=1. This opposite locking order can cause a deadlock where each waits for the other to release a lock.