Practice & Assessment
Test your understanding of Core SQL: Querying and Manipulating Data
Multiple Choice Questions
5What does the RETURNING clause do in a DELETE statement?
You want employees who have NO matching department. Which query is correct?
Which clause filters rows AFTER aggregation in a GROUP BY query?
What is the correct way to check for NULL in a WHERE clause?
What is the difference between EXISTS and IN when used with a subquery on a large table?
Coding Challenges
1Sales Report Query
Given tables: orders (id, customer_id, total, status, created_at) and customers (id, name, country). Write a single SELECT query that returns: customer name, country, number of completed orders, total revenue (sum of total where status='completed'), and average order value — grouped by customer, filtered to customers with more than 2 completed orders, sorted by total revenue descending. Use a CTE for the aggregation step. Expected columns: name, country, order_count, total_revenue, avg_order_value. Time estimate: 25 minutes.
Mini Project
E-commerce Query Suite
Using the tables products (id, name, category, price, stock_qty), customers (id, name, email, country), orders (id, customer_id, created_at, status), and order_items (order_id, product_id, qty, unit_price): Write five SQL queries. (1) List all customers who have never placed an order (anti-join). (2) Find the top 5 best-selling products by total units sold. (3) Calculate monthly revenue for the last 12 months using date_trunc. (4) Using a CTE, find customers whose total spend exceeds the company-wide average. (5) Upsert a product: if the product name already exists update its price and stock, otherwise insert it. Use RETURNING in at least two queries. Comment each query with its purpose.