GROUP BY and HAVING Practice

By /

GROUP BY and HAVING – Understanding & Practice

SQL Study Notes – AdventureWorksDW2017

This page covers GROUP BY, HAVING, LEFT JOIN + IS NULL, EXISTS, IN, and comparison between IN and LIKE.

Key Concepts

WHERE vs HAVING

  • WHERE filters rows before grouping.
  • HAVING filters groups after grouping and aggregation.

IN vs LIKE

  • IN → Used when you have a list of exact values (e.g., specific categories or years).
  • LIKE → Used for partial text matching with wildcards (% and _).

SQL Order of Operations

  1. FROM + JOINs
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

Practice Questions with Solutions

Question 1 – GROUP BY + Date

List all employees and the most recent sales date. Show full name in one column. Format date as mm/dd/yyyy.

Solution:

SELECT 
    CONCAT(E.FirstName, ' ', ISNULL(E.MiddleName, ''), ' ', E.LastName) AS FULLNAME,
    CONVERT(VARCHAR, MAX(FIS.OrderDate), 101) AS MOSTRECENTORDERDATE
FROM DimEmployee E
LEFT JOIN FactInternetSales FIS ON E.EmployeeKey = FIS.SalesTerritoryKey
GROUP BY E.FirstName, E.MiddleName, E.LastName
ORDER BY MOSTRECENTORDERDATE DESC;

Question 2 – GROUP BY + HAVING

For each sales territory, show number of transactions and total sales. Only show territories with more than 1000 transactions.

Solution:

SELECT 
    ST.SalesTerritoryRegion,
    COUNT(*) AS NUMBEROFTRANSACTIONS,
    SUM(FIS.SalesAmount) AS TOTALSALES
FROM FactInternetSales FIS
JOIN DimSalesTerritory ST ON FIS.SalesTerritoryKey = ST.SalesTerritoryKey
GROUP BY ST.SalesTerritoryRegion
HAVING COUNT(*) > 1000
ORDER BY TOTALSALES DESC;

Question 3 – LEFT JOIN + IS NULL

Find customers who have never placed an order.

Solution:

SELECT 
    DC.CustomerKey,
    DC.FirstName,
    DC.LastName
FROM DimCustomer DC
LEFT JOIN FactInternetSales FIS ON DC.CustomerKey = FIS.CustomerKey
WHERE FIS.CustomerKey IS NULL;

Question 4 – EXISTS

Find customers who have placed at least one order.

Solution:

SELECT 
    DC.CustomerKey,
    DC.FirstName,
    DC.LastName
FROM DimCustomer DC
WHERE EXISTS (
    SELECT 1 
    FROM FactInternetSales FIS 
    WHERE FIS.CustomerKey = DC.CustomerKey
);

Question 5 – IN Operator

Show total sales for products in categories ‘Bikes’ or ‘Clothing’.

Solution:

SELECT 
    C.EnglishProductCategoryName,
    SUM(FIS.SalesAmount) AS TOTALSALES
FROM FactInternetSales FIS
JOIN DimProduct P ON FIS.ProductKey = P.ProductKey
JOIN DimProductSubcategory PS ON P.ProductSubcategoryKey = PS.ProductSubcategoryKey
JOIN DimProductCategory C ON PS.ProductCategoryKey = C.ProductCategoryKey
WHERE C.EnglishProductCategoryName IN ('Bikes', 'Clothing')
GROUP BY C.EnglishProductCategoryName;

Question 6 – LIKE Operator

Find all customers whose last name starts with ‘S’.

Solution:

SELECT 
    FirstName,
    LastName
FROM DimCustomer
WHERE LastName LIKE 'S%';

IN vs LIKE – Quick Comparison

Operator When to Use Example
IN Exact match from a list EnglishProductCategoryName IN (‘Bikes’, ‘Clothing’)
LIKE Pattern / partial match LastName LIKE ‘S%’ (starts with S)

Practice these patterns well. They appear often on the final exam.

Scroll to Top