The Art of SQL Practice: 12 Dimensions of Real‑World Data Analysis
Filtering is the foundation of . Every ETL pipeline, report, and transformation starts with selecting the right rows.
1. Filter rows with simple conditions
What it is
Use WHERE to return only rows that match a condition.
Syntax
SELECT columns FROM table WHERE condition;
Example
Customers from USA:
SELECT * FROM Sales.Customers WHERE Country = 'USA';
Practice
Get all orders where Sales > 500.
2. Filter with AND
What it is
Combine multiple conditions that must all be true.
Example
Orders with high sales AND completed status:
SELECT * FROM Sales.Orders WHERE Sales > 500 AND OrderStatus = 'Completed';
Practice
Find customers from India with Score > 80.
3. Filter with OR
What it is
Return rows where any condition is true.
Example
Orders that are either Pending or Cancelled:
SELECT * FROM Sales.Orders WHERE OrderStatus = 'Pending' OR OrderStatus = 'Cancelled';
Practice
Find customers from USA or Canada.
4. Filter with BETWEEN
What it is
Filter values within a range (inclusive).
Example
Orders placed in May 2023:
SELECT * FROM Sales.Orders WHERE OrderDate BETWEEN '2023-05-01' AND '2023-05-31';
Practice
Find customers created between two dates.
5. Filter with IN
What it is
Match against a list of values.
Example
Orders with specific statuses:
SELECT * FROM Sales.Orders WHERE OrderStatus IN ('Completed', 'Pending');
Practice
Find customers from a list of countries.
6. Filter with LIKE
What it is
Pattern matching for strings.
Example
Customers whose first name starts with “A”:
SELECT * FROM Sales.Customers WHERE FirstName LIKE 'A%';
Practice
Find customers whose email ends with “.com”.
7. Filter NULL values
What it is
Use IS NULL or IS NOT NULL.
Example
Orders missing a ShipDate:
SELECT * FROM Sales.Orders WHERE ShipDate IS NULL;
Practice
Find customers with no phone number.
8. Filter NOT conditions
What it is
Exclude values.
Example
Orders not shipped to USA:
SELECT * FROM Sales.Orders WHERE Country <> 'USA';
(If you add Country to Orders later.)
Practice
Find customers not from India.
9. Filter using expressions
What it is
Use calculations inside WHERE.
Example
Orders where total revenue (Sales × Quantity) > 1000:
SELECT * FROM Sales.Orders WHERE (Sales * Quantity) > 1000;
Practice
Find orders where Quantity × Sales < 500.
10. Combine multiple filters
What it is
Use parentheses to control logic.
Example
Orders that are Completed OR (Pending AND high value):
SELECT * FROM Sales.Orders WHERE OrderStatus = 'Completed' OR (OrderStatus = 'Pending' AND Amount > 500);
Practice
Find customers from USA with Score > 50 OR from India with Score > 80.
⭐ SECTION 2 — JOINS (Topics 11–20)
Joins combine rows from multiple tables based on related columns.
This is the backbone of analytics, reporting, and ETL.
11. INNER JOIN — match rows in both tables
What it is
Returns rows where the join condition matches in both tables.
Syntax
SELECT ... FROM A INNER JOIN B ON A.key = B.key;
Example
List orders with customer names:
SELECT o.OrderID, o.Amount, c.FirstName, c.LastName FROM Sales.Orders o INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID;
Practice
Show OrderID, OrderDate, FirstName for all matching orders.
12. LEFT JOIN — keep all customers, even without orders
What it is
Returns all rows from the left table, and matching rows from the right.
Example
Customers with their orders (including those with none):
SELECT c.CustomerID, c.FirstName, o.OrderID, o.Amount FROM Sales.Customers c LEFT JOIN Sales.Orders o ON c.CustomerID = o.CustomerID;
Practice
Find customers who have no orders.
13. RIGHT JOIN — keep all orders, even without customers
(Less common, but useful)
Example
Orders with customer info (even if customer missing):
SELECT o.OrderID, o.Amount, c.FirstName FROM Sales.Orders o RIGHT JOIN Sales.Customers c ON o.CustomerID = c.CustomerID;
Practice
Show all orders and customer names, even if customer is missing.
14. FULL OUTER JOIN — keep everything
What it is
Returns all rows from both tables.
Example
Customers and orders, even unmatched:
SELECT c.CustomerID, o.OrderID, c.FirstName, o.Amount FROM Sales.Customers c FULL OUTER JOIN Sales.Orders o ON c.CustomerID = o.CustomerID;
Practice
Find all customers and orders, including unmatched.
15. JOIN with additional filters
Example
Completed orders with customer names:
SELECT o.OrderID, o.Amount, c.FirstName FROM Sales.Orders o JOIN Sales.Customers c ON o.CustomerID = c.CustomerIDWHERE o.OrderStatus = 'Completed';
Practice
Find all Pending orders with customer names.
16. JOIN using expressions
Example
Orders where customer Score > 80:
SELECT o.OrderID, o.Amount, c.Score
FROM Sales.Orders o
JOIN Sales.Customers c
ON o.CustomerID = c.CustomerID
WHERE c.Score > 80;
Practice
Find orders where Quantity × Sales > 1000.
17. JOIN + GROUP BY
Example
Total sales per customer:
SELECT c.CustomerID, c.FirstName,
SUM(o.Amount) AS TotalSpent
FROM Sales.Customers c
LEFT JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.FirstName;
Practice
Count orders per customer.
18. JOIN with multiple tables
Example
Orders with customer and salesperson:
SELECT o.OrderID, c.FirstName, o.SalesPersonID
FROM Sales.Orders o
JOIN Sales.Customers c
ON o.CustomerID = c.CustomerID;
(You can later add a SalesPerson table.)
Practice
Show OrderID, CustomerName, and SalesPersonID.
19. Anti‑join (LEFT JOIN + NULL)
What it is
Find rows in one table with no match in another.
Example
Customers with no orders:
SELECT c.*
FROM Sales.Customers c
LEFT JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;
Practice
Find orders with no matching customer.
20. Semi‑join (EXISTS)
What it is
Check if a match exists, but don’t return the joined rows.
Example
Customers who have placed at least one order:
SELECT *
FROM Sales.Customers c
WHERE EXISTS (
SELECT 1
FROM Sales.Orders o
WHERE o.CustomerID = c.CustomerID
);
Practice
Find customers who placed orders after 2024.
SECTION 3 — Advanced joins (Topics 21–30)
We’ll keep using only your real tables:
Sales.Customers
Sales.Orders
21. Customers with orders in a specific month
Idea: Use EXTRACT‑like logic via MONTH() and YEAR() in Server.
Example — customers with orders in June 2023
SELECT DISTINCT c.CustomerID, c.FirstName, c.LastName
FROM Sales.Customers c
JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
WHERE YEAR(o.OrderDate) = 2023
AND MONTH(o.OrderDate) = 6;
Practice: Get customers who ordered in January 2024.
22. Customers with orders in multiple months
Idea: Group by customer and count distinct months.
Example
SELECT c.CustomerID, c.FirstName, c.LastName,
COUNT(DISTINCT (CONVERT(char(7), o.OrderDate, 120))) AS DistinctMonths
FROM Sales.Customers c
JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.LastName
HAVING COUNT(DISTINCT (CONVERT(char(7), o.OrderDate, 120))) > 1;
Practice: Find customers who ordered in at least 3 different months.
23. Customers whose first order had high Amount
Idea: Use window functions to find the first order per customer.
Example — first order Amount > 200
WITH OrderedOrders AS (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY o.CustomerID
ORDER BY o.OrderDate
) AS rn
FROM Sales.Orders o
)
SELECT c.CustomerID, c.FirstName, c.LastName
FROM Sales.Customers c
JOIN OrderedOrders o
ON c.CustomerID = o.CustomerID
WHERE o.rn = 1
AND o.Amount > 200;
Practice: Find customers whose first order status was Cancelled.
24. Customers whose last order was Cancelled
Idea: Same pattern, but order by date descending.
Example
WITH OrderedOrders AS (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY o.CustomerID
ORDER BY o.OrderDate DESC
) AS rn
FROM Sales.Orders o
)
SELECT c.CustomerID, c.FirstName, c.LastName
FROM Sales.Customers c
JOIN OrderedOrders o
ON c.CustomerID = o.CustomerID
WHERE o.rn = 1
AND o.OrderStatus = 'Cancelled';
Practice: Find customers whose last order was Completed.
25. Customers with more than one PaymentMethod
Idea: Count distinct payment methods per customer.
Example
SELECT c.CustomerID, c.FirstName, c.LastName,
COUNT(DISTINCT o.PaymentMethod) AS DistinctPaymentMethods
FROM Sales.Customers c
JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.LastName
HAVING COUNT(DISTINCT o.PaymentMethod) > 1;
Practice: Find customers who used exactly one payment method.
26. Customers with both Completed and Pending orders
Idea: Use conditional aggregation.
Example
SELECT c.CustomerID, c.FirstName, c.LastName
FROM Sales.Customers c
JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.LastName
HAVING SUM(CASE WHEN o.OrderStatus = 'Completed' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN o.OrderStatus = 'Pending' THEN 1 ELSE 0 END) > 0;
Practice: Find customers who have both Completed and Cancelled orders.
27. Customers whose order Amount increased over time
Idea: Use LAG to compare each order to the previous one.
Example
WITH Lagged AS (
SELECT o.*,
LAG(o.Amount) OVER (
PARTITION BY o.CustomerID
ORDER BY o.OrderDate
) AS PrevAmount
FROM Sales.Orders o
)
SELECT DISTINCT CustomerID
FROM Lagged
WHERE PrevAmount IS NOT NULL
AND Amount > PrevAmount;
Practice: Find customers whose Amount decreased compared to the previous order.
28. Customers whose order Amount decreased over time
Example
WITH Lagged AS (
SELECT o.*,
LAG(o.Amount) OVER (
PARTITION BY o.CustomerID
ORDER BY o.OrderDate
) AS PrevAmount
FROM Sales.Orders o
)
SELECT DISTINCT CustomerID
FROM Lagged
WHERE PrevAmount IS NOT NULL
AND Amount < PrevAmount;
Practice: Find customers whose Amount stayed the same as previous order.
29. Orders updated on the same day they were created
Idea: Compare CreationTime and UpdatedAt dates.
Example
SELECT *
FROM Sales.Orders
WHERE CONVERT(date, CreationTime) = CONVERT(date, UpdatedAt);
Practice: Find orders updated at least 3 days after CreationTime.
30. Customers with orders in 2023 but not in 2024
Idea: Use two sets and anti‑join logic.
Example
WITH Orders2023 AS (
SELECT DISTINCT CustomerID
FROM Sales.Orders
WHERE YEAR(OrderDate) = 2023
),
Orders2024 AS (
SELECT DISTINCT CustomerID
FROM Sales.Orders
WHERE YEAR(OrderDate) = 2024
)
SELECT c.CustomerID, c.FirstName, c.LastName
FROM Sales.Customers c
JOIN Orders2023 y23
ON c.CustomerID = y23.CustomerID
LEFT JOIN Orders2024 y24
ON c.CustomerID = y24.CustomerID
WHERE y24.CustomerID IS NULL;
Practice: Find customers who ordered in 2024 but not in 2023.
✅ SECTION 4 — Set Operators (31–35)
31. UNION
Combine rows from two queries.
SELECT Country FROM Sales.Customers
UNION
SELECT PaymentMethod FROM Sales.Orders;
32. INTERSECT
Common rows.
SELECT CustomerID FROM Sales.Customers
INTERSECT
SELECT CustomerID FROM Sales.Orders;
33. EXCEPT
Rows in first query but not second.
SELECT CustomerID FROM Sales.Customers
EXCEPT
SELECT CustomerID FROM Sales.Orders;
34. UNION ALL
Keeps duplicates.
SELECT Country FROM Sales.Customers
UNION ALL
SELECT Country FROM Sales.Customers;
35. EXCEPT for date ranges
SELECT CustomerID FROM Sales.Orders WHERE YEAR(OrderDate)=2023
EXCEPT
SELECT CustomerID FROM Sales.Orders WHERE YEAR(OrderDate)=2024;
✅ SECTION 5 — Functions (36–45)
36. ROUND
SELECT OrderID, ROUND(Amount,2) FROM Sales.Orders;
37. Math expression
SELECT OrderID, Amount * 1.10 AS WithTax FROM Sales.Orders;
38. CONCAT
SELECT CONCAT(FirstName,' ',LastName) AS FullName FROM Sales.Customers;
39. SUBSTRING
SELECT SUBSTRING(Email, CHARINDEX('@',Email)+1, 100) AS Domain
FROM Sales.Customers;
40. REPLACE
SELECT REPLACE(ShipAddress,'Street','St') FROM Sales.Orders;
41. UPPER/LOWER
SELECT UPPER(FirstName) FROM Sales.Customers;
42. LEN
SELECT Email FROM Sales.Customers WHERE LEN(Email)>15;
43. TRIM
SELECT LTRIM(RTRIM(Phone)) FROM Sales.Customers;
44. ABS
SELECT ABS(Sales - Amount) FROM Sales.Orders;
45. CEILING/FLOOR
SELECT CEILING(Amount), FLOOR(Amount) FROM Sales.Orders;
✅ SECTION 6 — Date Functions (46–55)
46. YEAR/MONTH/DAY
SELECT YEAR(OrderDate), MONTH(OrderDate) FROM Sales.Orders;
47. DATEADD
SELECT DATEADD(DAY,7,OrderDate) FROM Sales.Orders;
48. DATEDIFF
SELECT DATEDIFF(DAY,OrderDate,ShipDate) FROM Sales.Orders;
49. GETDATE
SELECT * FROM Sales.Customers WHERE CreatedAt > DATEADD(DAY,-30,GETDATE());
50. Orders on weekends
SELECT * FROM Sales.Orders WHERE DATEPART(WEEKDAY,OrderDate) IN (1,7);
51. NULLIF
SELECT NULLIF(Amount,0) FROM Sales.Orders;
52. COALESCE
SELECT COALESCE(Phone,'Unknown') FROM Sales.Customers;
53. ISNULL
SELECT ISNULL(Amount,0) FROM Sales.Orders;
54. CAST/CONVERT
SELECT CONVERT(varchar,OrderDate,23) FROM Sales.Orders;
55. DATEFROMPARTS
SELECT DATEFROMPARTS(2024,1,1);
✅ SECTION 7 — CASE (56–65)
56. Categorize Amount
SELECT CASE WHEN Amount>500 THEN 'High' ELSE 'Low' END FROM Sales.Orders;
57. Categorize Score
SELECT CASE WHEN Score>=80 THEN 'Gold' ELSE 'Regular' END FROM Sales.Customers;
58. Categorize OrderStatus
SELECT CASE OrderStatus WHEN 'Completed' THEN 'Done' ELSE 'Pending' END FROM Sales.Orders;
59. Categorize by Country
SELECT CASE WHEN Country='USA' THEN 'North America' ELSE 'Other' END FROM Sales.Customers;
60. Categorize by Quantity
SELECT CASE WHEN Quantity>10 THEN 'Bulk' ELSE 'Normal' END FROM Sales.Orders;
61–65 similar patterns
(You now know the structure.)
✅ SECTION 8 — Aggregates (66–75)
66. COUNT
SELECT COUNT(*) FROM Sales.Customers;
67. SUM
SELECT SUM(Amount) FROM Sales.Orders;
68. AVG
SELECT AVG(Amount) FROM Sales.Orders;
69. MIN/MAX
SELECT MIN(Amount), MAX(Amount) FROM Sales.Orders;
70. GROUP BY Country
SELECT Country, COUNT(*) FROM Sales.Customers GROUP BY Country;
71. Orders per customer
SELECT CustomerID, COUNT(*) FROM Sales.Orders GROUP BY CustomerID;
72. Revenue per customer
SELECT CustomerID, SUM(Amount) FROM Sales.Orders GROUP BY CustomerID;
73. Orders per status
SELECT OrderStatus, COUNT(*) FROM Sales.Orders GROUP BY OrderStatus;
74. Average Quantity
SELECT AVG(Quantity) FROM Sales.Orders;
75. Monthly orders
SELECT YEAR(OrderDate), MONTH(OrderDate), COUNT(*)
FROM Sales.Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate);
✅ SECTION 9 — Window Functions (76–95)
76. Running total
SELECT OrderID, Amount,
SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Sales.Orders;
77. Total per customer
SELECT OrderID, CustomerID,
SUM(Amount) OVER (PARTITION BY CustomerID)
FROM Sales.Orders;
78. Avg per customer
SELECT OrderID, AVG(Amount) OVER (PARTITION BY CustomerID)
FROM Sales.Orders;
79. Count per customer
SELECT OrderID, COUNT(*) OVER (PARTITION BY CustomerID)
FROM Sales.Orders;
80. Percent of total
SELECT OrderID, Amount,
Amount * 1.0 / SUM(Amount) OVER ()
FROM Sales.Orders;
81. Max per customer
SELECT OrderID, MAX(Amount) OVER (PARTITION BY CustomerID)
FROM Sales.Orders;
82. Min per customer
SELECT OrderID, MIN(Amount) OVER (PARTITION BY CustomerID)
FROM Sales.Orders;
83. First order amount
SELECT OrderID,
FIRST_VALUE(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate)
FROM Sales.Orders;
84. Last order amount
SELECT OrderID,
LAST_VALUE(Amount) OVER (
PARTITION BY CustomerID ORDER BY OrderDate
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
FROM Sales.Orders;
85. LAG
SELECT OrderID, Amount,
LAG(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate)
FROM Sales.Orders;
86. LEAD
SELECT OrderID, Amount,
LEAD(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate)
FROM Sales.Orders;
87. ROW_NUMBER
SELECT OrderID,
ROW_NUMBER() OVER (ORDER BY Amount DESC)
FROM Sales.Orders;
88. RANK
SELECT OrderID,
RANK() OVER (ORDER BY Amount DESC)
FROM Sales.Orders;
89. DENSE_RANK
SELECT OrderID,
DENSE_RANK() OVER (ORDER BY Amount DESC)
FROM Sales.Orders;
90–95 similar patterns
(Top N per customer, differences, etc.)
✅ SECTION 10 — Subqueries & CTEs (96–105)
96. Subquery filter
SELECT * FROM Sales.Customers
WHERE CustomerID IN (SELECT CustomerID FROM Sales.Orders WHERE Amount>500);
97. Total spend > 1000
SELECT * FROM Sales.Customers
WHERE CustomerID IN (
SELECT CustomerID FROM Sales.Orders GROUP BY CustomerID HAVING SUM(Amount)>1000
);
98. More than 3 orders
SELECT CustomerID FROM Sales.Orders
GROUP BY CustomerID HAVING COUNT(*)>3;
99. First order cancelled
WITH x AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) rn
FROM Sales.Orders
)
SELECT * FROM x WHERE rn=1 AND OrderStatus='Cancelled';
100. Only ordered in 2023
WITH y AS (
SELECT CustomerID, YEAR(OrderDate) yr FROM Sales.Orders GROUP BY CustomerID,YEAR(OrderDate)
)
SELECT CustomerID FROM y GROUP BY CustomerID HAVING COUNT(*)=1 AND MIN(yr)=2023;
101–105 CTE patterns
(monthly revenue, repeat customers, etc.)
✅ SECTION 11 — Views, CTAS, Temp Tables (106–115)
106. View
CREATE VIEW Sales.CustomerSummary AS
SELECT c.CustomerID, c.FirstName, COUNT(o.OrderID) AS TotalOrders
FROM Sales.Customers c
LEFT JOIN Sales.Orders o ON c.CustomerID=o.CustomerID
GROUP BY c.CustomerID, c.FirstName;
107. High value orders
CREATE VIEW Sales.HighValueOrders AS
SELECT * FROM Sales.Orders WHERE Amount>500;
108. Monthly revenue
CREATE VIEW Sales.MonthlyRevenue AS
SELECT YEAR(OrderDate) yr, MONTH(OrderDate) mn, SUM(Amount) total
FROM Sales.Orders GROUP BY YEAR(OrderDate),MONTH(OrderDate);
109. CTAS
SELECT * INTO Sales.TempHighOrders
FROM Sales.Orders WHERE Amount>500;
110–115 similar patterns
(temp tables, CTAS, etc.)
✅ SECTION 12 — Stored Procedures (116–120)
116. Update order status
CREATE PROCEDURE Sales.UpdateStatus @OrderID int, @Status varchar(50)
AS
UPDATE Sales.Orders SET OrderStatus=@Status WHERE OrderID=@OrderID;
117. Delete cancelled
CREATE PROCEDURE Sales.DeleteCancelled AS
DELETE FROM Sales.Orders WHERE OrderStatus='Cancelled';
118–120 similar patterns
(insert customer, archive orders, etc.)
✅ SECTION 13 — Triggers (121–125)
121. Auto-update UpdatedAt
CREATE TRIGGER trg_UpdateTime ON Sales.Orders
AFTER UPDATE AS
UPDATE o SET UpdatedAt=GETDATE()
FROM Sales.Orders o
JOIN inserted i ON o.OrderID=i.OrderID;
122. Log deletes
CREATE TRIGGER trg_LogDelete ON Sales.Orders
AFTER DELETE AS
INSERT INTO Sales.OrderDeleteLog(OrderID,Amount,DeletedAt)
SELECT OrderID,Amount,GETDATE() FROM deleted;
123–125 similar patterns
(prevent negative, track status changes, etc.)
✅ SECTION 14 — Indexes & Execution Plans (126–135)
126. Index
CREATE INDEX idx_orders_customer ON Sales.Orders(CustomerID);
127. Index on Email
CREATE INDEX idx_customers_email ON Sales.Customers(Email);
128. Composite index
CREATE INDEX idx_orders_customer_date ON Sales.Orders(CustomerID,OrderDate);
129–135
Use:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXPLAIN...
✅ SECTION 15 — Partitioning & Performance (136–150)
Server partitioning example:
136. Create partition function
CREATE PARTITION FUNCTION pfOrders (date)
AS RANGE RIGHT FOR VALUES ('2023-01-01','2024-01-01');
137. Create partition scheme
CREATE PARTITION SCHEME psOrders AS PARTITION pfOrders ALL TO ([PRIMARY]);
138. Create partitioned table
CREATE TABLE Sales.OrdersPartitioned (...)
ON psOrders(OrderDate);
139–150
Performance rewrites:
Replace OR with IN
Use EXISTS instead of IN
Avoid SELECT *
Use covering indexes
Use window functions instead of correlated subqueries
✅ SECTION 16 — AI + (151–155)
151. Convert English →
You already saw many examples.
152. Explain in English
You can ask me anytime.
153. Optimize
Rewrite slow queries using:
indexes
window functions
CTEs
predicate pushdown
154. Generate test data
Use loops or random functions.
155. Convert business question →
Example:
“Top 5 customers by revenue”
SELECT TOP 5 CustomerID, SUM(Amount) AS TotalSpent
FROM Sales.Orders
GROUP BY CustomerID
ORDER BY TotalSpent DESC;
SELECT ... FROM A INNER JOIN B ON A.key = B.key;
Example
List orders with customer names:
SELECT o.OrderID, o.Amount, c.FirstName, c.LastName FROM Sales.Orders o INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID;
Practice
Show OrderID, OrderDate, FirstName for all matching orders.
12. LEFT JOIN — keep all customers, even without orders
What it is
Returns all rows from the left table, and matching rows from the right.
Example
Customers with their orders (including those with none):
SELECT c.CustomerID, c.FirstName, o.OrderID, o.Amount FROM Sales.Customers c LEFT JOIN Sales.Orders o ON c.CustomerID = o.CustomerID;
Practice
Find customers who have no orders.
13. RIGHT JOIN — keep all orders, even without customers
(Less common, but useful)
Example
Orders with customer info (even if customer missing):
SELECT o.OrderID, o.Amount, c.FirstName FROM Sales.Orders o RIGHT JOIN Sales.Customers c ON o.CustomerID = c.CustomerID;
Practice
Show all orders and customer names, even if customer is missing.
14. FULL OUTER JOIN — keep everything
What it is
Returns all rows from both tables.
Example
Customers and orders, even unmatched:
SELECT c.CustomerID, o.OrderID, c.FirstName, o.Amount FROM Sales.Customers c FULL OUTER JOIN Sales.Orders o ON c.CustomerID = o.CustomerID;
Practice
Find all customers and orders, including unmatched.
15. JOIN with additional filters
Example
Completed orders with customer names:
SELECT o.OrderID, o.Amount, c.FirstName FROM Sales.Orders o JOIN Sales.Customers c ON o.CustomerID = c.CustomerIDWHERE o.OrderStatus = 'Completed';
Practice
Find all Pending orders with customer names.
16. JOIN using expressions
Example
Orders where customer Score > 80:
SELECT o.OrderID, o.Amount, c.Score
FROM Sales.Orders o
JOIN Sales.Customers c
ON o.CustomerID = c.CustomerID
WHERE c.Score > 80;
Practice
Find orders where Quantity × Sales > 1000.
17. JOIN + GROUP BY
Example
Total sales per customer:
SELECT c.CustomerID, c.FirstName,
SUM(o.Amount) AS TotalSpent
FROM Sales.Customers c
LEFT JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.FirstName;
Practice
Count orders per customer.
18. JOIN with multiple tables
Example
Orders with customer and salesperson:
SELECT o.OrderID, c.FirstName, o.SalesPersonID
FROM Sales.Orders o
JOIN Sales.Customers c
ON o.CustomerID = c.CustomerID;
(You can later add a SalesPerson table.)
Practice
Show OrderID, CustomerName, and SalesPersonID.
19. Anti‑join (LEFT JOIN + NULL)
What it is
Find rows in one table with no match in another.
Example
Customers with no orders:
SELECT c.*
FROM Sales.Customers c
LEFT JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;
Practice
Find orders with no matching customer.
20. Semi‑join (EXISTS)
What it is
Check if a match exists, but don’t return the joined rows.
Example
Customers who have placed at least one order:
SELECT *
FROM Sales.Customers c
WHERE EXISTS (
SELECT 1
FROM Sales.Orders o
WHERE o.CustomerID = c.CustomerID
);
Practice
Find customers who placed orders after 2024.
SECTION 3 — Advanced joins (Topics 21–30)
We’ll keep using only your real tables:
Sales.Customers
Sales.Orders
21. Customers with orders in a specific month
Idea: Use EXTRACT‑like logic via MONTH() and YEAR() in Server.
Example — customers with orders in June 2023
SELECT DISTINCT c.CustomerID, c.FirstName, c.LastName
FROM Sales.Customers c
JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
WHERE YEAR(o.OrderDate) = 2023
AND MONTH(o.OrderDate) = 6;
Practice: Get customers who ordered in January 2024.
22. Customers with orders in multiple months
Idea: Group by customer and count distinct months.
Example
SELECT c.CustomerID, c.FirstName, c.LastName,
COUNT(DISTINCT (CONVERT(char(7), o.OrderDate, 120))) AS DistinctMonths
FROM Sales.Customers c
JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.LastName
HAVING COUNT(DISTINCT (CONVERT(char(7), o.OrderDate, 120))) > 1;
Practice: Find customers who ordered in at least 3 different months.
23. Customers whose first order had high Amount
Idea: Use window functions to find the first order per customer.
Example — first order Amount > 200
WITH OrderedOrders AS (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY o.CustomerID
ORDER BY o.OrderDate
) AS rn
FROM Sales.Orders o
)
SELECT c.CustomerID, c.FirstName, c.LastName
FROM Sales.Customers c
JOIN OrderedOrders o
ON c.CustomerID = o.CustomerID
WHERE o.rn = 1
AND o.Amount > 200;
Practice: Find customers whose first order status was Cancelled.
24. Customers whose last order was Cancelled
Idea: Same pattern, but order by date descending.
Example
WITH OrderedOrders AS (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY o.CustomerID
ORDER BY o.OrderDate DESC
) AS rn
FROM Sales.Orders o
)
SELECT c.CustomerID, c.FirstName, c.LastName
FROM Sales.Customers c
JOIN OrderedOrders o
ON c.CustomerID = o.CustomerID
WHERE o.rn = 1
AND o.OrderStatus = 'Cancelled';
Practice: Find customers whose last order was Completed.
25. Customers with more than one PaymentMethod
Idea: Count distinct payment methods per customer.
Example
SELECT c.CustomerID, c.FirstName, c.LastName,
COUNT(DISTINCT o.PaymentMethod) AS DistinctPaymentMethods
FROM Sales.Customers c
JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.LastName
HAVING COUNT(DISTINCT o.PaymentMethod) > 1;
Practice: Find customers who used exactly one payment method.
26. Customers with both Completed and Pending orders
Idea: Use conditional aggregation.
Example
SELECT c.CustomerID, c.FirstName, c.LastName
FROM Sales.Customers c
JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.LastName
HAVING SUM(CASE WHEN o.OrderStatus = 'Completed' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN o.OrderStatus = 'Pending' THEN 1 ELSE 0 END) > 0;
Practice: Find customers who have both Completed and Cancelled orders.
27. Customers whose order Amount increased over time
Idea: Use LAG to compare each order to the previous one.
Example
WITH Lagged AS (
SELECT o.*,
LAG(o.Amount) OVER (
PARTITION BY o.CustomerID
ORDER BY o.OrderDate
) AS PrevAmount
FROM Sales.Orders o
)
SELECT DISTINCT CustomerID
FROM Lagged
WHERE PrevAmount IS NOT NULL
AND Amount > PrevAmount;
Practice: Find customers whose Amount decreased compared to the previous order.
28. Customers whose order Amount decreased over time
Example
WITH Lagged AS (
SELECT o.*,
LAG(o.Amount) OVER (
PARTITION BY o.CustomerID
ORDER BY o.OrderDate
) AS PrevAmount
FROM Sales.Orders o
)
SELECT DISTINCT CustomerID
FROM Lagged
WHERE PrevAmount IS NOT NULL
AND Amount < PrevAmount;
Practice: Find customers whose Amount stayed the same as previous order.
29. Orders updated on the same day they were created
Idea: Compare CreationTime and UpdatedAt dates.
Example
SELECT *
FROM Sales.Orders
WHERE CONVERT(date, CreationTime) = CONVERT(date, UpdatedAt);
Practice: Find orders updated at least 3 days after CreationTime.
30. Customers with orders in 2023 but not in 2024
Idea: Use two sets and anti‑join logic.
Example
WITH Orders2023 AS (
SELECT DISTINCT CustomerID
FROM Sales.Orders
WHERE YEAR(OrderDate) = 2023
),
Orders2024 AS (
SELECT DISTINCT CustomerID
FROM Sales.Orders
WHERE YEAR(OrderDate) = 2024
)
SELECT c.CustomerID, c.FirstName, c.LastName
FROM Sales.Customers c
JOIN Orders2023 y23
ON c.CustomerID = y23.CustomerID
LEFT JOIN Orders2024 y24
ON c.CustomerID = y24.CustomerID
WHERE y24.CustomerID IS NULL;
Practice: Find customers who ordered in 2024 but not in 2023.
✅ SECTION 4 — Set Operators (31–35)
31. UNION
Combine rows from two queries.
SELECT Country FROM Sales.Customers
UNION
SELECT PaymentMethod FROM Sales.Orders;
32. INTERSECT
Common rows.
SELECT CustomerID FROM Sales.Customers
INTERSECT
SELECT CustomerID FROM Sales.Orders;
33. EXCEPT
Rows in first query but not second.
SELECT CustomerID FROM Sales.Customers
EXCEPT
SELECT CustomerID FROM Sales.Orders;
34. UNION ALL
Keeps duplicates.
SELECT Country FROM Sales.Customers
UNION ALL
SELECT Country FROM Sales.Customers;
35. EXCEPT for date ranges
SELECT CustomerID FROM Sales.Orders WHERE YEAR(OrderDate)=2023
EXCEPT
SELECT CustomerID FROM Sales.Orders WHERE YEAR(OrderDate)=2024;
✅ SECTION 5 — Functions (36–45)
36. ROUND
SELECT OrderID, ROUND(Amount,2) FROM Sales.Orders;
37. Math expression
SELECT OrderID, Amount * 1.10 AS WithTax FROM Sales.Orders;
38. CONCAT
SELECT CONCAT(FirstName,' ',LastName) AS FullName FROM Sales.Customers;
39. SUBSTRING
SELECT SUBSTRING(Email, CHARINDEX('@',Email)+1, 100) AS Domain
FROM Sales.Customers;
40. REPLACE
SELECT REPLACE(ShipAddress,'Street','St') FROM Sales.Orders;
41. UPPER/LOWER
SELECT UPPER(FirstName) FROM Sales.Customers;
42. LEN
SELECT Email FROM Sales.Customers WHERE LEN(Email)>15;
43. TRIM
SELECT LTRIM(RTRIM(Phone)) FROM Sales.Customers;
44. ABS
SELECT ABS(Sales - Amount) FROM Sales.Orders;
45. CEILING/FLOOR
SELECT CEILING(Amount), FLOOR(Amount) FROM Sales.Orders;
✅ SECTION 6 — Date Functions (46–55)
46. YEAR/MONTH/DAY
SELECT YEAR(OrderDate), MONTH(OrderDate) FROM Sales.Orders;
47. DATEADD
SELECT DATEADD(DAY,7,OrderDate) FROM Sales.Orders;
48. DATEDIFF
SELECT DATEDIFF(DAY,OrderDate,ShipDate) FROM Sales.Orders;
49. GETDATE
SELECT * FROM Sales.Customers WHERE CreatedAt > DATEADD(DAY,-30,GETDATE());
50. Orders on weekends
SELECT * FROM Sales.Orders WHERE DATEPART(WEEKDAY,OrderDate) IN (1,7);
51. NULLIF
SELECT NULLIF(Amount,0) FROM Sales.Orders;
52. COALESCE
SELECT COALESCE(Phone,'Unknown') FROM Sales.Customers;
53. ISNULL
SELECT ISNULL(Amount,0) FROM Sales.Orders;
54. CAST/CONVERT
SELECT CONVERT(varchar,OrderDate,23) FROM Sales.Orders;
55. DATEFROMPARTS
SELECT DATEFROMPARTS(2024,1,1);
✅ SECTION 7 — CASE (56–65)
56. Categorize Amount
SELECT CASE WHEN Amount>500 THEN 'High' ELSE 'Low' END FROM Sales.Orders;
57. Categorize Score
SELECT CASE WHEN Score>=80 THEN 'Gold' ELSE 'Regular' END FROM Sales.Customers;
58. Categorize OrderStatus
SELECT CASE OrderStatus WHEN 'Completed' THEN 'Done' ELSE 'Pending' END FROM Sales.Orders;
59. Categorize by Country
SELECT CASE WHEN Country='USA' THEN 'North America' ELSE 'Other' END FROM Sales.Customers;
60. Categorize by Quantity
SELECT CASE WHEN Quantity>10 THEN 'Bulk' ELSE 'Normal' END FROM Sales.Orders;
61–65 similar patterns
(You now know the structure.)
✅ SECTION 8 — Aggregates (66–75)
66. COUNT
SELECT COUNT(*) FROM Sales.Customers;
67. SUM
SELECT SUM(Amount) FROM Sales.Orders;
68. AVG
SELECT AVG(Amount) FROM Sales.Orders;
69. MIN/MAX
SELECT MIN(Amount), MAX(Amount) FROM Sales.Orders;
70. GROUP BY Country
SELECT Country, COUNT(*) FROM Sales.Customers GROUP BY Country;
71. Orders per customer
SELECT CustomerID, COUNT(*) FROM Sales.Orders GROUP BY CustomerID;
72. Revenue per customer
SELECT CustomerID, SUM(Amount) FROM Sales.Orders GROUP BY CustomerID;
73. Orders per status
SELECT OrderStatus, COUNT(*) FROM Sales.Orders GROUP BY OrderStatus;
74. Average Quantity
SELECT AVG(Quantity) FROM Sales.Orders;
75. Monthly orders
SELECT YEAR(OrderDate), MONTH(OrderDate), COUNT(*)
FROM Sales.Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate);
✅ SECTION 9 — Window Functions (76–95)
76. Running total
SELECT OrderID, Amount,
SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Sales.Orders;
77. Total per customer
SELECT OrderID, CustomerID,
SUM(Amount) OVER (PARTITION BY CustomerID)
FROM Sales.Orders;
78. Avg per customer
SELECT OrderID, AVG(Amount) OVER (PARTITION BY CustomerID)
FROM Sales.Orders;
79. Count per customer
SELECT OrderID, COUNT(*) OVER (PARTITION BY CustomerID)
FROM Sales.Orders;
80. Percent of total
SELECT OrderID, Amount,
Amount * 1.0 / SUM(Amount) OVER ()
FROM Sales.Orders;
81. Max per customer
SELECT OrderID, MAX(Amount) OVER (PARTITION BY CustomerID)
FROM Sales.Orders;
82. Min per customer
SELECT OrderID, MIN(Amount) OVER (PARTITION BY CustomerID)
FROM Sales.Orders;
83. First order amount
SELECT OrderID,
FIRST_VALUE(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate)
FROM Sales.Orders;
84. Last order amount
SELECT OrderID,
LAST_VALUE(Amount) OVER (
PARTITION BY CustomerID ORDER BY OrderDate
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
FROM Sales.Orders;
85. LAG
SELECT OrderID, Amount,
LAG(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate)
FROM Sales.Orders;
86. LEAD
SELECT OrderID, Amount,
LEAD(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate)
FROM Sales.Orders;
87. ROW_NUMBER
SELECT OrderID,
ROW_NUMBER() OVER (ORDER BY Amount DESC)
FROM Sales.Orders;
88. RANK
SELECT OrderID,
RANK() OVER (ORDER BY Amount DESC)
FROM Sales.Orders;
89. DENSE_RANK
SELECT OrderID,
DENSE_RANK() OVER (ORDER BY Amount DESC)
FROM Sales.Orders;
90–95 similar patterns
(Top N per customer, differences, etc.)
✅ SECTION 10 — Subqueries & CTEs (96–105)
96. Subquery filter
SELECT * FROM Sales.Customers
WHERE CustomerID IN (SELECT CustomerID FROM Sales.Orders WHERE Amount>500);
97. Total spend > 1000
SELECT * FROM Sales.Customers
WHERE CustomerID IN (
SELECT CustomerID FROM Sales.Orders GROUP BY CustomerID HAVING SUM(Amount)>1000
);
98. More than 3 orders
SELECT CustomerID FROM Sales.Orders
GROUP BY CustomerID HAVING COUNT(*)>3;
99. First order cancelled
WITH x AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) rn
FROM Sales.Orders
)
SELECT * FROM x WHERE rn=1 AND OrderStatus='Cancelled';
100. Only ordered in 2023
WITH y AS (
SELECT CustomerID, YEAR(OrderDate) yr FROM Sales.Orders GROUP BY CustomerID,YEAR(OrderDate)
)
SELECT CustomerID FROM y GROUP BY CustomerID HAVING COUNT(*)=1 AND MIN(yr)=2023;
101–105 CTE patterns
(monthly revenue, repeat customers, etc.)
✅ SECTION 11 — Views, CTAS, Temp Tables (106–115)
106. View
CREATE VIEW Sales.CustomerSummary AS
SELECT c.CustomerID, c.FirstName, COUNT(o.OrderID) AS TotalOrders
FROM Sales.Customers c
LEFT JOIN Sales.Orders o ON c.CustomerID=o.CustomerID
GROUP BY c.CustomerID, c.FirstName;
107. High value orders
CREATE VIEW Sales.HighValueOrders AS
SELECT * FROM Sales.Orders WHERE Amount>500;
108. Monthly revenue
CREATE VIEW Sales.MonthlyRevenue AS
SELECT YEAR(OrderDate) yr, MONTH(OrderDate) mn, SUM(Amount) total
FROM Sales.Orders GROUP BY YEAR(OrderDate),MONTH(OrderDate);
109. CTAS
SELECT * INTO Sales.TempHighOrders
FROM Sales.Orders WHERE Amount>500;
110–115 similar patterns
(temp tables, CTAS, etc.)
✅ SECTION 12 — Stored Procedures (116–120)
116. Update order status
CREATE PROCEDURE Sales.UpdateStatus @OrderID int, @Status varchar(50)
AS
UPDATE Sales.Orders SET OrderStatus=@Status WHERE OrderID=@OrderID;
117. Delete cancelled
CREATE PROCEDURE Sales.DeleteCancelled AS
DELETE FROM Sales.Orders WHERE OrderStatus='Cancelled';
118–120 similar patterns
(insert customer, archive orders, etc.)
✅ SECTION 13 — Triggers (121–125)
121. Auto-update UpdatedAt
CREATE TRIGGER trg_UpdateTime ON Sales.Orders
AFTER UPDATE AS
UPDATE o SET UpdatedAt=GETDATE()
FROM Sales.Orders o
JOIN inserted i ON o.OrderID=i.OrderID;
122. Log deletes
CREATE TRIGGER trg_LogDelete ON Sales.Orders
AFTER DELETE AS
INSERT INTO Sales.OrderDeleteLog(OrderID,Amount,DeletedAt)
SELECT OrderID,Amount,GETDATE() FROM deleted;
123–125 similar patterns
(prevent negative, track status changes, etc.)
✅ SECTION 14 — Indexes & Execution Plans (126–135)
126. Index
CREATE INDEX idx_orders_customer ON Sales.Orders(CustomerID);
127. Index on Email
CREATE INDEX idx_customers_email ON Sales.Customers(Email);
128. Composite index
CREATE INDEX idx_orders_customer_date ON Sales.Orders(CustomerID,OrderDate);
129–135
Use:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXPLAIN...
✅ SECTION 15 — Partitioning & Performance (136–150)
Server partitioning example:
136. Create partition function
CREATE PARTITION FUNCTION pfOrders (date)
AS RANGE RIGHT FOR VALUES ('2023-01-01','2024-01-01');
137. Create partition scheme
CREATE PARTITION SCHEME psOrders AS PARTITION pfOrders ALL TO ([PRIMARY]);
138. Create partitioned table
CREATE TABLE Sales.OrdersPartitioned (...)
ON psOrders(OrderDate);
139–150
Performance rewrites:
Replace OR with IN
Use EXISTS instead of IN
Avoid SELECT *
Use covering indexes
Use window functions instead of correlated subqueries
✅ SECTION 16 — AI + (151–155)
151. Convert English →
You already saw many examples.
152. Explain in English
You can ask me anytime.
153. Optimize
Rewrite slow queries using:
indexes
window functions
CTEs
predicate pushdown
154. Generate test data
Use loops or random functions.
155. Convert business question →
Example:
“Top 5 customers by revenue”
SELECT TOP 5 CustomerID, SUM(Amount) AS TotalSpent
FROM Sales.Orders
GROUP BY CustomerID
ORDER BY TotalSpent DESC;
No comments:
Post a Comment