Question

Structure of the ORDERS table / Structure of the CUSTOMERS table

Which of the following SQL statements will display all customers who have not recently placed an order?

a) SELECT firstname, lastname
FROM customers
WHERE NOT EXISTS (SELECT customer# FROM orders WHERE orders.customer# = customers.customer#);

b) SELECT firstname, lastname
FROM customers
WHERE customer# NOT IN (SELECT customer# FROM orders WHERE order_date > '2023-01-01');

c) SELECT firstname, lastname
FROM customers
LEFT JOIN orders ON customers.customer# = orders.customer#
WHERE orders.customer# IS NULL;

d) SELECT firstname, lastname
FROM customers
RIGHT JOIN orders ON customers.customer# = orders.customer#
WHERE customers.customer# IS NULL;

Answer 1

The correct SQL statement to display all customers who have not recently placed an order is option c using LEFT JOIN and WHERE orders.customer# IS NULL.

Step-by-step explanation:

The correct SQL statement that will display all customers who have not recently placed an order is option c) SELECT firstname, lastname FROM customers LEFT JOIN orders ON customers.customer# = orders.customer# WHERE orders.customer# IS NULL;

This SQL statement uses a LEFT JOIN to join the customers table with the orders table, and then filters the result by selecting only the customers where orders.customer# IS NULL. This condition ensures that only the customers who have not placed an order recently are displayed.

For example, if the orders table has a customer with ID 100, and no corresponding entry exists in the customers table, then this customer will be included in the result because orders.customer# IS NULL will evaluate to true.