Practice & Assessment
Test your understanding of Advanced SQL
Multiple Choice Questions
5What is the difference between WHERE and HAVING in SQL?
Which is generally more efficient for large datasets: NOT IN with subquery or NOT EXISTS?
What is a CTE (Common Table Expression)?
What is the key difference between a VIEW and a MATERIALIZED VIEW?
Which SQL clause would you use to find departments with an average salary greater than 60000?
Coding Challenges
1Build a Sales Analytics Report
Using a database with tables: sales_reps (rep_id, name, region, hire_date), customers (customer_id, name, rep_id, industry), deals (deal_id, customer_id, rep_id, amount, close_date, stage). Write SQL to: (1) Using a CTE, calculate each sales rep's total revenue and rank them within their region. (2) Find the top customer (by total deal amount) for each industry. (3) Create a VIEW called rep_performance that shows each rep's name, region, total deals, total revenue, and average deal size. (4) Find reps whose monthly revenue in the last 3 months has been consistently above their 6-month average. The last query should use CTEs for clarity.
Mini Project
E-Commerce Analytics Dashboard Queries
Build the SQL layer for an e-commerce analytics dashboard. Using tables: customers, orders, order_items, products, categories, returns. Create the following: (1) A VIEW called daily_revenue that shows date, total orders, total revenue, and average order value per day. (2) A VIEW called product_performance showing each product's total units sold, revenue, return rate, and current stock value. (3) A stored procedure called generate_monthly_report(year INT, month INT) that returns: top 10 products by revenue, top 10 customers by spend, revenue by category, and day-of-week patterns. (4) SQL queries to detect anomalies: orders with no items (data quality), customers who bought the same product more than 3 times, and products with return rate above 15%.