Day 5: Practice Exercises

Hands-On SQL Assignments

Scenario: You are the lead database analyst for an E-commerce platform. You need to pull reports using three tables: Customers (CustomerID, CustomerName), Orders (OrderID, CustomerID, OrderDate, TotalAmount), and a separate Employees table (EmployeeID, Name, ManagerID).

Task 1: The INNER JOIN

Write a query using an INNER JOIN to display the CustomerName, OrderID, and TotalAmount for every order placed. Use table aliases (C for Customers, O for Orders).
Modify the query from the previous step to only show orders where the TotalAmount is greater than 500.

Task 2: Finding Missing Data (LEFT JOIN)

The marketing team wants a list of all customers, regardless of whether they have made a purchase or not. Write a query using a LEFT JOIN to show CustomerName and OrderID.
Challenge: Find the customers who registered but have never bought anything. Write a LEFT JOIN query, but add a WHERE clause to filter for records where the OrderID IS NULL.

Task 3: The Cartesian Product (CROSS JOIN)

Assume you have two small tables: Promotions (containing '10% Off' and 'Free Shipping') and MembershipTiers (containing 'Gold', 'Silver', 'Bronze'). Write a CROSS JOIN query to generate every possible combination of promotions and membership tiers.

Task 4: Advanced SELF JOIN

Using the Employees table, write a query that joins the table to itself. Your final output should have two columns: EmployeeName and their corresponding ManagerName. (Hint: Link the Employee's ManagerID to the Manager's EmployeeID).