Question

Given the following view that has been created, how would you query it to find the customers that have ordered more than $30 over their lifetime as a customer?

CREATE VIEW customer_order
AS SELECT invoice.customer_id, first_name, last_name, SUM(total) as total
FROM invoice
INNER JOIN customer
ON invoice.customer_id = customer customer_id
GROUP BY invoice.customer_id, first name, lest_name;

a. SELECT FROM order WHERE SUM(total) > 30;
b. SELECT FROM customer_order WHERE lifetime_total > 30,
c. SELECT FROM customer_order WHERE total > 30;
d. SELECT FROM customer order WHERE total < 30,

Answer 1

Option C is answer. The correct query to find customers who have ordered more than $30 over their lifetime is c. 'SELECT * FROM customer_order WHERE total > 30;'.

Step-by-step explanation:

To find the customers who have ordered more than $30 over their lifetime as a customer from the provided customer_order view, you can run the following SQL query:

SELECT * FROM customer_order WHERE total > 30;

This query selects all columns from the customer_order view for customers whose lifetime total, represented by the total column, exceeds $30. The correct answer to the student's question is option c. It is important to note that in SQL, an aggregate function like SUM() can only be used in a GROUP BY clause or in the selection of aggregated columns, and not in the WHERE clause directly unless it is a part of a subquery or having been used in a VIEW like in this case.