📢 Too many exams? Don’t know which one suits you best? Book Your Free Expert 👉 call Now!

    Question

    Consider a table Orders with columns OrderID,

    CustomerID, OrderDate, and TotalAmount. Which SQL query would correctly find the total number of orders and the average TotalAmount for each CustomerID who has placed more than 5 orders?  Option A) SELECT CustomerID, COUNT(OrderID), AVG(TotalAmount) FROM Orders GROUP BY CustomerID WHERE COUNT(OrderID) > 5; Option B) SELECT CustomerID, COUNT(OrderID), AVG(TotalAmount) FROM Orders WHERE COUNT(OrderID) > 5 GROUP BY CustomerID; Option C) SELECT CustomerID, COUNT(OrderID), AVG(TotalAmount) FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) > 5; Option D) SELECT CustomerID, COUNT(OrderID), AVG(TotalAmount) FROM Orders HAVING COUNT(OrderID) > 5 GROUP BY CustomerID; Option E) None of the above
    A Option A Correct Answer Incorrect Answer
    B Option B Correct Answer Incorrect Answer
    C Option C Correct Answer Incorrect Answer
    D Option D Correct Answer Incorrect Answer
    E Option E Correct Answer Incorrect Answer

    Solution

    SELECT CustomerID, COUNT(OrderID), AVG(TotalAmount) FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) > 5;  Explanation: The WHERE clause is used to filter individual rows *before* grouping, and it cannot contain aggregate functions. The HAVING clause is used to filter groups *after* the GROUP BY clause has been applied, and it can use aggregate functions. The correct order of execution in a SELECT statement is FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Therefore, to filter groups based on an aggregate condition (COUNT(OrderID) > 5), the HAVING clause must be used after GROUP BY. 

    Practice Next
    More C Programming Questions
    ask-question