SQL for Java Devs · interview question

Given tables 'customers' and 'orders', which SQL query correctly retrieves customers with their latest order?

Multiple choiceHardchapter-8-curatedpremiumsql-for-java-devsjoins

SELECT c.*, o.* FROM customers c LEFT JOIN orders o ON c.id = o.customer_id ORDER BY o.date DESCSELECT c.*, o.* FROM customers c RIGHT JOIN orders o ON c.id = o.customer_idSELECT c.*, o.* FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.date = (SELECT MAX(date) FROM orders WHERE customer_id = c.id)SELECT c.*, o.* FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.id

Answer and explanation

Correct answer

C. SELECT c.*, o.* FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.date = (SELECT MAX(date) FROM orders WHERE customer_id = c.id)

Why

The query uses a subquery to find the maximum date for each customer's orders, ensuring that only the latest order per customer is retrieved.

Test yourself on SQL for Java Devs

A free test with instant, explained results. No account needed.

Start free test

All SQL for Java Devs questions →

Given tables 'customers' and 'orders', which SQL query correctly retrieves customers with their latest order?

Correct answer

Why

More SQL for Java Devs questions