SQL fundamentals interview questions

Table products(id, category, price) where price is never NULL. Return one row per category with category, product_count (how many products it has) and total_price (the sum of their prices). Categories are compared exactly as stored.

Table orders(id, customer_id), customer_id never NULL. Return customer_id and order_count for every customer who has placed three or more orders. Customers below the threshold must not appear at all.

Walk me through the difference between COUNT(*), COUNT(price) and COUNT(DISTINCT price) on the same table. On a table of five rows where two prices are missing and the other three are all 9.99, what does each one return?

What does GROUP BY actually do to a result set? And why does the database reject SELECT category, name, SUM(price) FROM products GROUP BY category?

When do you put a condition in WHERE and when does it belong in HAVING? Give one condition that only works in HAVING and one that would be a mistake to put there.

Before adding a UNIQUE constraint on users.email you need to know what is in the way. Table users(id, email); emails are compared exactly as stored. Return email and copies for every address that appears on more than one row.