[Solved] Which of the following demonstrates a valid correlated subquery structure in PostgreSQL? — Ans: SELECT c.name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE... | PostgreSQL

PostgreSQL - Subqueries in PostgreSQL

Which of the following demonstrates a valid correlated subquery structure in PostgreSQL?

ASELECT c.name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

BSELECT * FROM orders WHERE order_id IN (SELECT order_id FROM customers WHERE customer_id = orders.customer_id);

CSELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

DSELECT name FROM departments WHERE id = (SELECT department_id FROM employees);

Step-by-Step Solution

Solution:

Step 1: Identify the outer query and inner subquery
The outer query is selecting from customers c, and the subquery references c.customer_id, which is from the outer query.
Step 2: Check for correlation
The subquery uses o.customer_id = c.customer_id, linking the inner query to the outer query, making it a correlated subquery.
Final Answer:
SELECT c.name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id); correctly shows a correlated subquery.
Quick Check:
Subquery references outer query alias [OK]

Quick Trick: Correlated subqueries reference outer query columns [OK]

Common Mistakes:

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Which of the following demonstrates a valid correlated subquery structure in PostgreSQL?