Week 2 SQL Demo | GMBA 612

1

Remove Duplicates

DISTINCT

💼 BUSINESS QUESTION

"What countries do our customers come from? Just the unique list, no repeats."

SELECT DISTINCT Country FROM Customers ORDER BY Country;

🎯 KEY TAKEAWAY

DISTINCT removes duplicate values. Without it, you'd see "Germany" 11 times. With it, you see each country once.

✓ EXPECTED RESULT

21 unique countries (Argentina, Austria, Belgium, Brazil, Canada, Denmark, Finland, France, Germany, Ireland, Italy, Mexico, Norway, Poland, Portugal, Spain, Sweden, Switzerland, UK, USA, Venezuela)

2

Filter a Range

BETWEEN

💼 BUSINESS QUESTION

"Show me mid-priced products — between $20 and $50."

SELECT ProductName, Price FROM Products WHERE Price BETWEEN 20 AND 50 ORDER BY Price;

🎯 KEY TAKEAWAY

BETWEEN is inclusive — it includes both 20 and 50. It's cleaner than writing "Price >= 20 AND Price <= 50".

✓ EXPECTED RESULT

37 products in the $20-$50 range

3

Match a List

IN

💼 BUSINESS QUESTION

"Find customers in our key European markets: Germany, France, and UK."

SELECT CustomerName, City, Country FROM Customers WHERE Country IN ('Germany', 'France', 'UK') ORDER BY Country, City;

🎯 KEY TAKEAWAY

IN is shorthand for multiple OR conditions. Much cleaner than "Country = 'Germany' OR Country = 'France' OR Country = 'UK'".

✓ EXPECTED RESULT

28 customers (11 Germany + 11 France + 7 UK)

🧪 STUDENT CHALLENGE

Find customers in North America (USA, Canada, Mexico). How many are there?

4

Pattern Matching

LIKE

💼 BUSINESS QUESTION

"Find all products with 'Sauce' in the name."

SELECT ProductName, Price FROM Products WHERE ProductName LIKE '%Sauce%';

🎯 KEY TAKEAWAY

The % is a wildcard meaning "any characters." So '%Sauce%' finds Sauce anywhere in the name. Use 'Sauce%' for "starts with" or '%Sauce' for "ends with."

✓ EXPECTED RESULT

4 products (Genen Shouyu, Gula Malacca, Northwoods Cranberry Sauce, etc.)

🧪 STUDENT CHALLENGE

Find all customers whose name starts with 'A'. How many are there?

5

Summarize Data

COUNT / AVG / SUM

💼 BUSINESS QUESTION

"Give me a quick summary of our product catalog — how many products, average price, and total inventory value."

SELECT COUNT(*) AS TotalProducts, ROUND(AVG(Price), 2) AS AveragePrice, ROUND(SUM(Price), 2) AS TotalCatalogValue FROM Products;

🎯 KEY TAKEAWAY

Aggregate functions collapse many rows into one summary. COUNT counts rows, AVG averages values, SUM adds them up. ROUND keeps decimals tidy. AS creates a column alias (nickname).

✓ EXPECTED RESULT

77 products | Average: $28.87 | Total: $2222.71

6

Find Extremes

MIN / MAX

💼 BUSINESS QUESTION

"What's our cheapest and most expensive product?"

SELECT MIN(Price) AS CheapestPrice, MAX(Price) AS HighestPrice FROM Products;

🎯 KEY TAKEAWAY

MIN and MAX find the smallest and largest values. But notice — this only gives you the prices, not the product names! We'll fix that with subqueries later.

✓ EXPECTED RESULT

Cheapest: $2.50 | Highest: $263.50

7

Count by Category

GROUP BY

💼 BUSINESS QUESTION

"How many customers do we have in each country?"

SELECT Country, COUNT(*) AS CustomerCount FROM Customers GROUP BY Country ORDER BY CustomerCount DESC;

🎯 KEY TAKEAWAY

GROUP BY creates "buckets" and counts items in each bucket. Without GROUP BY, COUNT gives you one total. With GROUP BY, you get a count per group.

✓ EXPECTED RESULT

USA leads with 13 customers, followed by Germany and France with 11 each

8

Analyze by Category

GROUP BY + AVG

💼 BUSINESS QUESTION

"What's the average price and product count for each category?"

SELECT CategoryID, COUNT(*) AS ProductCount, ROUND(AVG(Price), 2) AS AvgPrice FROM Products GROUP BY CategoryID ORDER BY AvgPrice DESC;

🎯 KEY TAKEAWAY

You can use multiple aggregate functions with GROUP BY. This shows which categories have premium pricing (Category 6 - Meat/Poultry is highest).

✓ EXPECTED RESULT

8 categories. Category 6 (Meat/Poultry) has highest avg price at $54.01

9

Filter Groups

HAVING

💼 BUSINESS QUESTION

"Which countries have more than 5 customers? Those are our strong markets."

SELECT Country, COUNT(*) AS CustomerCount FROM Customers GROUP BY Country HAVING COUNT(*) > 5 ORDER BY CustomerCount DESC;

🎯 KEY TAKEAWAY

HAVING filters groups (after GROUP BY). WHERE filters rows (before GROUP BY). Rule: use WHERE for raw data, HAVING for aggregated results.

✓ EXPECTED RESULT

5 countries: USA (13), Germany (11), France (11), Brazil (9), UK (7)

🧪 STUDENT CHALLENGE

Find categories with an average price over $30. Which ones qualify?

10

Group by Multiple Columns

GROUP BY (multi)

💼 BUSINESS QUESTION

"How many orders did each employee process per shipper?"

SELECT EmployeeID, ShipperID, COUNT(*) AS OrderCount FROM Orders GROUP BY EmployeeID, ShipperID ORDER BY EmployeeID, ShipperID;

🎯 KEY TAKEAWAY

GROUP BY can use multiple columns — creating smaller, more specific buckets. This shows each employee's order count broken down by shipping company.

✓ EXPECTED RESULT

30 rows showing every employee-shipper combination

11

Query Inside a Query

Subquery (WHERE)

💼 BUSINESS QUESTION

"Which products cost more than the average price? Those are our premium items."

SELECT ProductName, Price FROM Products WHERE Price > (SELECT AVG(Price) FROM Products) ORDER BY Price DESC;

🎯 KEY TAKEAWAY

A subquery is a query inside another query. The inner query runs first (calculates avg = $28.87), then the outer query uses that result. This is how you answer "compared to what?" questions.

✓ EXPECTED RESULT

25 products priced above the $28.87 average

12

Find the Record with MAX

Subquery (MAX)

💼 BUSINESS QUESTION

"What IS our most expensive product? Give me the name, not just the price."

SELECT ProductName, Price FROM Products WHERE Price = (SELECT MAX(Price) FROM Products);

🎯 KEY TAKEAWAY

Remember Demo 6? MAX only gave us the price. Now with a subquery, we get the full record. The subquery finds the max ($263.50), then the outer query finds the product at that price.

✓ EXPECTED RESULT

Côte de Blaye at $263.50

🧪 STUDENT CHALLENGE

Find the cheapest product name using the same technique. What is it?

13

Subquery Returns a List

Subquery (IN)

💼 BUSINESS QUESTION

"Which customers have placed orders? Show only customers who actually bought something."

SELECT CustomerName, Country FROM Customers WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders) ORDER BY Country;

🎯 KEY TAKEAWAY

When a subquery returns multiple values, use IN (not =). The inner query gets all customer IDs from Orders, then the outer query finds those customers. This connects two tables without a JOIN!

✓ EXPECTED RESULT

74 customers have placed at least one order

14

Find What's Missing

Subquery (NOT IN)

💼 BUSINESS QUESTION

"Which customers have NEVER placed an order? We need to reach out to them!"

SELECT CustomerName, Country FROM Customers WHERE CustomerID NOT IN (SELECT DISTINCT CustomerID FROM Orders) ORDER BY Country;

🎯 KEY TAKEAWAY

NOT IN finds the opposite — customers who are NOT in the orders list. This is a powerful business query: finding inactive customers, products never sold, employees with no sales, etc.

✓ EXPECTED RESULT

17 customers have never placed an order — great leads for sales outreach!

✓

Week 2 Summary

-- Simple SELECT enhancements DISTINCT -- Remove duplicates BETWEEN -- Filter a range IN -- Match a list LIKE -- Pattern matching (%) -- Aggregate functions COUNT SUM AVG MIN MAX ROUND -- Grouping GROUP BY -- Create buckets HAVING -- Filter groups -- Subqueries WHERE x = (SELECT...) -- Single value WHERE x IN (SELECT...) -- Multiple values WHERE x NOT IN (SELECT...) -- Exclusion

🎯 KEY TAKEAWAY

Today you went from simple filtering to answering complex business questions with subqueries. Next week: JOINs — connecting tables directly to get even more powerful results!

📊 Week 2 SQL Demo

📋 Available Tables (8 total)