SQL Coding Challenges: Practice Exercises for Mastery

Retrieve Top Selling Products

Write a query to find the top 5 products by total sales amount.

Expected output: A list of 5 products with their total sales amounts in descending order.

Hint: Use GROUP BY on product_id and ORDER BY total sales DESC.

Find Customers Without Orders

Write a query to list customers who have never placed an order.

Expected output: A list of customers with no associated orders.

Hint: Use LEFT JOIN between customers and orders and filter where order_id is NULL.

Update Product Prices

Increase the price of all products in the 'Electronics' category by 10%.

Expected output: Product prices updated accordingly.

Hint: Use UPDATE with a WHERE clause filtering by category.

I start by understanding the problem requirements, then break the query into smaller parts, test each part individually, and finally combine them to form the complete solution.

Common joins include INNER JOIN (returns matching rows), LEFT JOIN (returns all rows from left table and matched rows from right), RIGHT JOIN, and FULL OUTER JOIN. The choice depends on which data you want to include.

By analyzing query execution plans, avoiding unnecessary columns in SELECT, indexing relevant columns, and simplifying complex joins or subqueries.