MySQL interview questions

Table orders(id, customer_id, amount). amount can be NULL when the price has not been settled yet. For every customer that appears in the table return the number of orders they placed and the sum of their known amounts. An order with a NULL amount

Table reviews(id, product_id, rating) where rating is an integer from 1 to 5. Return the products that have at least 3 reviews and an average rating of 4 or higher. Columns: product_id, review_count, avg_rating (the average rounded to 2 decimal places).

Table payments(id, paid_on, amount) with paid_on a DATE and amount possibly NULL. Produce a monthly summary: the month as a string in the form YYYY-MM (zero-padded month), the number of payments in that month and the sum of the non-NULL amounts. Only include months whose sum is

What is the difference between WHERE and HAVING in a query that uses GROUP BY?

MySQL runs with ONLY_FULL_GROUP_BY enabled by default (the default since MySQL 5.7). What does this mode require of a query that uses GROUP BY, and what error do you get if you violate it?

Explain the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column). What role do NULL values play in each?