Advanced SQL Puzzles DDL.sql

/*----------------------------------------------------
Scott Peters
Advanced SQL Puzzles
https://advancedsqlpuzzles.com
Last Updated: 06/11/2026
Microsoft SQL Server T-SQL

*/----------------------------------------------------
SET NOCOUNT ON;
GO

/*----------------------------------------------------
Puzzle #1 - Shopping Carts
*/----------------------------------------------------

DROP TABLE IF EXISTS #Cart1;
DROP TABLE IF EXISTS #Cart2;
GO

CREATE TABLE #Cart1
(
Item  VARCHAR(100)
);
GO

CREATE TABLE #Cart2
(
Item  VARCHAR(100)
);
GO

INSERT INTO #Cart1 (Item) VALUES
('Sugar'),('Bread'),('Juice'),('Soda'),('Flour');
GO

INSERT INTO #Cart2 (Item) VALUES
('Sugar'),('Bread'),('Butter'),('Cheese'),('Fruit');
GO

/*----------------------------------------------------
Puzzle #2 - Managers and Employees
*/----------------------------------------------------

DROP TABLE IF EXISTS #Employees;
GO

CREATE TABLE #Employees
(
EmployeeID  INTEGER,
ManagerID   INTEGER,
JobTitle    VARCHAR(100)
);
GO

INSERT INTO #Employees (EmployeeID, ManagerID, JobTitle) VALUES
(1001,NULL,'CEO'),(2002,1001,'Director'),
(3003,1001,'Office Manager'),(4004,2002,'Engineer'),
(5005,2002,'Engineer'),(6006,2002,'Engineer');
GO

/*----------------------------------------------------
Puzzle #3 - Fiscal Year Table Constraints
*/----------------------------------------------------

DROP TABLE IF EXISTS #EmployeePayRecords;
GO

CREATE TABLE #EmployeePayRecords
(
EmployeeID  INTEGER,
FiscalYear  INTEGER,
StartDate   DATE,
EndDate     DATE,
PayRate     MONEY
);
GO

/*----------------------------------------------------
Puzzle #4 - Two Predicates
*/----------------------------------------------------

DROP TABLE IF EXISTS #Orders;
GO

CREATE TABLE #Orders
(
CustomerID     INTEGER,
OrderID        INTEGER,
DeliveryState  VARCHAR(100),
Amount         MONEY
);
GO

INSERT INTO #Orders (CustomerID, OrderID, DeliveryState, Amount) VALUES
(1001,1,'CA',340),(1001,2,'TX',950),(1001,3,'TX',670),
(1001,4,'TX',860),(2002,5,'WA',320),(3003,6,'CA',650),
(3003,7,'CA',830),(4004,8,'TX',120);
GO

/*----------------------------------------------------
Puzzle #5 - Phone Directory
*/----------------------------------------------------

DROP TABLE IF EXISTS #PhoneDirectory;
GO

CREATE TABLE #PhoneDirectory
(
CustomerID   INTEGER,
[Type]       VARCHAR(100),
PhoneNumber  VARCHAR(12)
);
GO

INSERT INTO #PhoneDirectory (CustomerID, [Type], PhoneNumber) VALUES
(1001,'Cellular','555-897-5421'),
(1001,'Work','555-897-6542'),
(1001,'Home','555-698-9874'),
(2002,'Cellular','555-963-6544'),
(2002,'Work','555-812-9856'),
(3003,'Cellular','555-987-6541');
GO

/*----------------------------------------------------
Puzzle #6 - Workflow Steps
*/----------------------------------------------------

DROP TABLE IF EXISTS #WorkflowSteps;
GO

CREATE TABLE #WorkflowSteps
(
Workflow        VARCHAR(100),
StepNumber      INTEGER,
CompletionDate  DATE
);
GO

INSERT INTO #WorkflowSteps (Workflow, StepNumber, CompletionDate) VALUES
('Alpha',1,'7/2/2018'),('Alpha',2,'7/2/2018'),('Alpha',3,'7/1/2018'),
('Bravo',1,'6/25/2018'),('Bravo',2,NULL),('Bravo',3,'6/27/2018'),
('Charlie',1,NULL),('Charlie',2,'7/1/2018'),
('Delta',1,NULL),('Delta',2,NULL);
GO

/*----------------------------------------------------
Puzzle #7 - Mission to Mars
*/----------------------------------------------------

DROP TABLE IF EXISTS #Candidates;
DROP TABLE IF EXISTS #Requirements;
GO

CREATE TABLE #Candidates
(
CandidateID  INTEGER,
Occupation   VARCHAR(100)
);
GO
       
INSERT INTO #Candidates (CandidateID, Occupation) VALUES
(1001,'Geologist'),(1001,'Astrogator'),(1001,'Biochemist'),
(1001,'Technician'),(2002,'Surgeon'),(2002,'Machinist'),(2002,'Geologist'),
(3003,'Geologist'),(3003,'Astrogator'),(4004,'Selenologist');
GO

CREATE TABLE #Requirements
(
Requirement  VARCHAR(100)
);
GO

INSERT INTO #Requirements (Requirement) VALUES
('Geologist'),('Astrogator'),('Technician');
GO

/*----------------------------------------------------
Puzzle #8 - Workflow Cases
*/----------------------------------------------------

DROP TABLE IF EXISTS #WorkflowCases;
GO

CREATE TABLE #WorkflowCases
(
Workflow  VARCHAR(100),
Case1     INTEGER,
Case2     INTEGER,
Case3     INTEGER,
);
GO

INSERT INTO #WorkflowCases (Workflow, Case1, Case2, Case3) VALUES
('Alpha',0,0,0),('Bravo',0,1,1),('Charlie',1,0,0),('Delta',0,0,0);
GO

/*----------------------------------------------------
Puzzle #9 - Matching Sets
*/----------------------------------------------------

DROP TABLE IF EXISTS #Employees;
GO

CREATE TABLE #Employees
(
EmployeeID  INTEGER,
License     VARCHAR(100)
);
GO

INSERT INTO #Employees (EmployeeID, License) VALUES
(1001,'Class A'),(1001,'Class B'),(1001,'Class C'),
(2002,'Class A'),(2002,'Class B'),(2002,'Class C'),
(3003,'Class A'),(3003,'Class D'),
(4004,'Class A'),(4004,'Class B'),(4004,'Class D'),
(5005,'Class A'),(5005,'Class B'),(5005,'Class D');
GO

/*----------------------------------------------------
Puzzle #10 - Mean, Median, Mode, and Range
*/----------------------------------------------------

DROP TABLE IF EXISTS #SampleData;
GO

CREATE TABLE #SampleData
(
IntegerValue  INTEGER
);
GO

INSERT INTO #SampleData (IntegerValue) VALUES
(5),(6),(10),(10),(13),(14),(17),(20),(81),(90),(76);
GO

/*----------------------------------------------------
Puzzle #11 - Permutations
*/----------------------------------------------------

DROP TABLE IF EXISTS #TestCases;
GO

CREATE TABLE #TestCases
(
TestCase  VARCHAR(1)
);
GO

INSERT INTO #TestCases (TestCase) VALUES
('A'),('B'),('C');
GO

/*----------------------------------------------------
Puzzle #12 - Average Days
*/----------------------------------------------------

DROP TABLE IF EXISTS #ProcessLog;
GO

CREATE TABLE #ProcessLog
(
WorkFlow       VARCHAR(100),
ExecutionDate  DATE
);
GO

INSERT INTO #ProcessLog (WorkFlow, ExecutionDate) VALUES
('Alpha','6/01/2018'),('Alpha','6/14/2018'),('Alpha','6/15/2018'),
('Bravo','6/1/2018'),('Bravo','6/2/2018'),('Bravo','6/19/2018'),
('Charlie','6/1/2018'),('Charlie','6/15/2018'),('Charlie','6/30/2018');
GO

/*----------------------------------------------------
Puzzle #13 - Inventory Tracking
*/----------------------------------------------------

DROP TABLE IF EXISTS #Inventory;
GO

CREATE TABLE #Inventory
(
InventoryDate       DATE,
QuantityAdjustment  INTEGER
);
GO

INSERT INTO #Inventory (InventoryDate, QuantityAdjustment) VALUES
('7/1/2018',100),('7/2/2018',75),('7/3/2018',-150),
('7/4/2018',50),('7/5/2018',-100);
GO

/*----------------------------------------------------
Puzzle #14 - Indeterminate Process Log
*/----------------------------------------------------

DROP TABLE IF EXISTS #ProcessLog;
GO

CREATE TABLE #ProcessLog
(
Workflow    VARCHAR(100),
StepNumber  INTEGER,
RunStatus   VARCHAR(100),
);
GO

INSERT INTO #ProcessLog (Workflow, StepNumber, RunStatus) VALUES
('Alpha',1,'Error'),('Alpha',2,'Complete'),('Alpha',3,'Running'),
('Bravo',1,'Complete'),('Bravo',2,'Complete'),
('Charlie',1,'Running'),('Charlie',2,'Running'),
('Delta',1,'Error'),('Delta',2,'Error'),
('Echo',1,'Running'),('Echo',2,'Complete');
GO

/*----------------------------------------------------
Puzzle #15 - Group Concatenation
*/----------------------------------------------------

DROP TABLE IF EXISTS #DMLTable;
GO

CREATE TABLE #DMLTable
(
SequenceNumber  INTEGER,
String          VARCHAR(100)
);
GO

INSERT INTO #DMLTable (SequenceNumber, String) VALUES
(1,'SELECT'),
(2,'Product,'),
(3,'UnitPrice,'),
(4,'EffectiveDate'),
(5,'FROM'),
(6,'Products'),
(7,'WHERE'),
(8,'UnitPrice'),
(9,'> 100');
GO

/*----------------------------------------------------
Puzzle #16 - Reciprocals
*/----------------------------------------------------

DROP TABLE IF EXISTS #PlayerScores;
GO

CREATE TABLE #PlayerScores
(
PlayerA  INTEGER,
PlayerB  INTEGER,
Score    INTEGER
);
GO

INSERT INTO #PlayerScores (PlayerA, PlayerB, Score) VALUES
(1001,2002,150),(3003,4004,15),(4004,3003,125);
GO

/*----------------------------------------------------
Puzzle #17 - De-Grouping
*/----------------------------------------------------

DROP TABLE IF EXISTS #Ungroup;
GO

CREATE TABLE #Ungroup
(
ProductDescription  VARCHAR(100),
Quantity            INTEGER
);
GO

INSERT INTO #Ungroup (ProductDescription, Quantity) VALUES
('Pencil',3),('Eraser',4),('Notebook',2);
GO

/*----------------------------------------------------
Puzzle #18 - Seating Chart
*/----------------------------------------------------

DROP TABLE IF EXISTS #SeatingChart;
GO

CREATE TABLE #SeatingChart
(
SeatNumber  INTEGER
);
GO

INSERT INTO #SeatingChart (SeatNumber) VALUES
(7),(13),(14),(15),(27),(28),(29),(30),(31),(32),(33),(34),(35),(52),(53),(54);
GO

/*----------------------------------------------------
Puzzle #19 - Back to the Future
*/----------------------------------------------------

DROP TABLE IF EXISTS #TimePeriods;
GO

CREATE TABLE #TimePeriods
(
StartDate  DATE,
EndDate    DATE
);
GO

INSERT INTO #TimePeriods (StartDate, EndDate) VALUES
('1/1/2018','1/5/2018'),
('1/3/2018','1/9/2018'),
('1/10/2018','1/11/2018'),
('1/12/2018','1/16/2018'),
('1/15/2018','1/19/2018');
GO

/*----------------------------------------------------
Puzzle #20 - Price Points
*/----------------------------------------------------

DROP TABLE IF EXISTS #ValidPrices;
GO

CREATE TABLE #ValidPrices
(
ProductID      INTEGER,
UnitPrice      MONEY,
EffectiveDate  DATE
);
GO

INSERT INTO #ValidPrices (ProductID, UnitPrice, EffectiveDate) VALUES
(1001,1.99,'1/01/2018'),
(1001,2.99,'4/15/2018'),
(1001,3.99,'6/8/2018'),
(2002,1.99,'4/17/2018'),
(2002,2.99,'5/19/2018');
GO

/*----------------------------------------------------
Puzzle #21 - Average Monthly Sales
*/----------------------------------------------------

DROP TABLE IF EXISTS #Orders;
GO

CREATE TABLE #Orders
(
OrderID     INTEGER,
CustomerID  INTEGER,
OrderDate   DATE,
Amount      MONEY,
[State]     VARCHAR(2)
);
GO

INSERT INTO #Orders (OrderID, CustomerID, OrderDate, Amount, [State]) VALUES
(1,1001,'1/1/2018',100,'TX'),
(2,1001,'1/1/2018',150,'TX'),
(3,1001,'1/1/2018',75,'TX'),
(4,1001,'2/1/2018',100,'TX'),
(5,1001,'3/1/2018',100,'TX'),
(6,2002,'2/1/2018',75,'TX'),
(7,2002,'2/1/2018',150,'TX'),
(8,3003,'1/1/2018',100,'IA'),
(9,3003,'2/1/2018',100,'IA'),
(10,3003,'3/1/2018',100,'IA'),
(11,4004,'4/1/2018',100,'IA'),
(12,4004,'5/1/2018',50,'IA'),
(13,4004,'5/1/2018',100,'IA');
GO

/*----------------------------------------------------
Puzzle #22 - Occurrences
*/----------------------------------------------------

DROP TABLE IF EXISTS #ProcessLog;
GO

CREATE TABLE #ProcessLog
(
Workflow     VARCHAR(100),
LogMessage   VARCHAR(100),
Occurrences  INTEGER
);
GO

INSERT INTO #ProcessLog (Workflow, LogMessage, Occurrences) VALUES
('Alpha','Error: Conversion Failed',5),
('Alpha','Status Complete',8),
('Alpha','Error: Unidentified error occurred',9),
('Bravo','Error: Cannot Divide by 0',3),
('Bravo','Error: Unidentified error occurred',1),
('Charlie','Error: Unidentified error occurred',10),
('Charlie','Error: Conversion Failed',7),
('Charlie','Status Complete',6);
GO

/*----------------------------------------------------
Puzzle #23 - Divide in Half
*/----------------------------------------------------

DROP TABLE IF EXISTS #PlayerScores;
GO

CREATE TABLE #PlayerScores
(
PlayerID  INTEGER,
Score     INTEGER
);
GO

INSERT INTO #PlayerScores (PlayerID, Score) VALUES
(1001,2343),(2002,9432),
(3003,6548),(4004,1054),
(5005,6832);
GO

/*----------------------------------------------------
Puzzle #24 - Page Views
*/----------------------------------------------------

DROP TABLE IF EXISTS #Orders;
GO

CREATE TABLE #Orders
(
OrderID     INTEGER,
CustomerID  INTEGER,
OrderDate   DATE,
Amount      MONEY,
[State]     VARCHAR(2)
);
GO

INSERT INTO #Orders (OrderID, CustomerID, OrderDate, Amount, [State]) VALUES
(1, 1001, '2018-01-01', 100, 'TX'),
(2, 3003, '2018-01-01', 100, 'IA'),
(3, 1001, '2018-03-01', 100, 'TX'),
(4, 2002, '2018-02-01', 150, 'TX'),
(5, 1001, '2018-02-01', 100, 'TX'),
(6, 4004, '2018-05-01', 50,  'IA'),
(7, 1001, '2018-01-01', 150, 'TX'),
(8, 3003, '2018-03-01', 100, 'IA'),
(9, 4004, '2018-04-01', 100, 'IA'),
(10, 1001, '2018-01-01', 75,  'TX'),
(11, 2002, '2018-02-01', 75,  'TX'),
(12, 3003, '2018-02-01', 100, 'IA'),
(13, 4004, '2018-05-01', 100, 'IA');
GO

/*----------------------------------------------------
Puzzle #25 - Top Vendors
*/----------------------------------------------------

DROP TABLE IF EXISTS #Orders;
GO

CREATE TABLE #Orders
(
OrderID     INTEGER,
CustomerID  INTEGER,
[Count]     INTEGER,
Vendor      VARCHAR(100)
);
GO

INSERT INTO #Orders (OrderID, CustomerID, [Count], Vendor)
VALUES
-- Customer 1001
(1,1001,35,'Direct Parts'),
(2,1001,35,'Direct Parts'),
(3,1001,50,'ACME'),

-- Customer 2002
(4,2002,10,'ACME'),
(5,2002,10,'ACME'),
(6,2002,15,'Direct Parts');
GO

/*----------------------------------------------------
Puzzle #26 - Previous Year's Sales
*/----------------------------------------------------

DROP TABLE IF EXISTS #Sales;
GO

CREATE TABLE #Sales
(
[Year]  INTEGER,
Amount  INTEGER
);
GO

INSERT INTO #Sales ([Year], Amount) VALUES
(YEAR(GETDATE()),352645),
(YEAR(DATEADD(YEAR,-1,GETDATE())),165565),
(YEAR(DATEADD(YEAR,-1,GETDATE())),254654),
(YEAR(DATEADD(YEAR,-2,GETDATE())),159521),
(YEAR(DATEADD(YEAR,-2,GETDATE())),251696),
(YEAR(DATEADD(YEAR,-3,GETDATE())),111894);
GO

/*----------------------------------------------------
Puzzle #27 - Delete the Duplicates
*/----------------------------------------------------

DROP TABLE IF EXISTS #SampleData;
GO

CREATE TABLE #SampleData
(
IntegerValue  INTEGER
);
GO

INSERT INTO #SampleData (IntegerValue) VALUES
(1),(1),(2),(3),(3),(4);
GO

/*----------------------------------------------------
Puzzle #28 - Fill the Gaps
*/----------------------------------------------------

DROP TABLE IF EXISTS #Gaps;
GO

CREATE TABLE #Gaps
(
RowNumber   INTEGER,
TestCase    VARCHAR(100)
);
GO

INSERT INTO #Gaps (RowNumber, TestCase) VALUES
(1,'Alpha'),(2,NULL),(3,NULL),(4,NULL),
(5,'Bravo'),(6,NULL),(7,'Charlie'),(8,NULL),(9,NULL);
GO

/*----------------------------------------------------
Puzzle #29 - Count the Groupings
*/----------------------------------------------------
DROP TABLE IF EXISTS #Groupings;
GO

CREATE TABLE #Groupings
(
StepNumber  INTEGER,
TestCase    VARCHAR(100),
[Status]    VARCHAR(100)
);
GO

INSERT INTO #Groupings (StepNumber, TestCase, [Status]) VALUES
(1,'Test Case 1','Passed'),
(2,'Test Case 2','Passed'),
(3,'Test Case 3','Passed'),
(4,'Test Case 4','Passed'),
(5,'Test Case 5','Failed'),
(6,'Test Case 6','Failed'),
(7,'Test Case 7','Failed'),
(8,'Test Case 8','Failed'),
(9,'Test Case 9','Failed'),
(10,'Test Case 10','Passed'),
(11,'Test Case 11','Passed'),
(12,'Test Case 12','Passed');
GO

/*----------------------------------------------------
Puzzle #30 - Select Star
*/----------------------------------------------------

DROP TABLE IF EXISTS #Products;
GO

CREATE TABLE #Products
(
ProductID    INTEGER,
ProductName  VARCHAR(100)
);
GO

/*----------------------------------------------------
Puzzle #31 - Second Highest
*/----------------------------------------------------

DROP TABLE IF EXISTS #SampleData;
GO

CREATE TABLE #SampleData
(
IntegerValue  INTEGER
);
GO

INSERT INTO #SampleData (IntegerValue) VALUES
(3759),(3760),(3761),(3762),(3763);
GO

/*----------------------------------------------------
Puzzle #32 - First and Last
*/----------------------------------------------------

DROP TABLE IF EXISTS #Personal;
GO

CREATE TABLE #Personal
(
SpacemanID      INTEGER,
JobDescription  VARCHAR(100),
MissionCount    INTEGER
);
GO

INSERT INTO #Personal (SpacemanID, JobDescription, MissionCount) VALUES
(1001,'Astrogator',6),(2002,'Astrogator',12),(3003,'Astrogator',17),
(4004,'Geologist',21),(5005,'Geologist',9),(6006,'Geologist',8),
(7007,'Technician',13),(8008,'Technician',2),(9009,'Technician',7);
GO

/*----------------------------------------------------
Puzzle #33 - Deadlines
*/----------------------------------------------------

DROP TABLE IF EXISTS #Orders;
DROP TABLE IF EXISTS #ManufacturingTimes;
GO

CREATE TABLE #Orders
(
OrderID        INTEGER,
Product        VARCHAR(100),
DaysToDeliver  INTEGER
);
GO

CREATE TABLE #ManufacturingTimes
(
Product            VARCHAR(100),
Component          VARCHAR(100),
DaysToManufacture  INTEGER
);
GO

INSERT INTO #Orders (OrderID, Product, DaysToDeliver) VALUES
(1, 'Aurora', 7),
(2, 'Twilight', 3),
(3, 'SunRay', 9);
GO

INSERT INTO #ManufacturingTimes (Product, Component, DaysToManufacture) VALUES
('Aurora', 'Photon Coil', 7),
('Aurora', 'Filament', 2),
('Aurora', 'Shine Capacitor', 3),
('Aurora', 'Glow Sphere', 1),
('Twilight', 'Photon Coil', 7),
('Twilight', 'Filament', 2),
('SunRay', 'Shine Capacitor', 3),
('SunRay', 'Photon Coil', 1);
GO

/*----------------------------------------------------
Puzzle #34 - Specific Exclusion
*/----------------------------------------------------

DROP TABLE IF EXISTS #Orders;
GO

CREATE TABLE #Orders
(
OrderID     INTEGER,
CustomerID  INTEGER,
Amount      MONEY
);
GO

INSERT INTO #Orders (OrderID, CustomerID, Amount) VALUES
(1,1001,25),(2,1001,50),(3,2002,65),(4,3003,50);
GO

/*----------------------------------------------------
Puzzle #35 - International vs Domestic Sales
*/----------------------------------------------------

DROP TABLE IF EXISTS #Orders;
GO

CREATE TABLE #Orders
(
InvoiceID   INTEGER,
SalesRepID  INTEGER,
Amount      MONEY,
SalesType   VARCHAR(100)
);
GO

INSERT INTO #Orders (InvoiceId, SalesRepID, Amount, SalesType) VALUES
(1,1001,13454,'International'),
(2,2002,3434,'International'),
(3,4004,54645,'International'),
(4,5005,234345,'International'),
(5,1001,4564,'Domestic'),
(6,2002,34534,'Domestic'),
(7,3003,345,'Domestic'),
(8,6006,6543,'Domestic');
GO

/*----------------------------------------------------
Puzzle #36 - Traveling Salesman
*/----------------------------------------------------

DROP TABLE IF EXISTS #TravelingSalesman;
DROP TABLE IF EXISTS #Routes;
GO

CREATE TABLE #Routes
(
RouteID        INTEGER,
DepartureCity  VARCHAR(30),
ArrivalCity    VARCHAR(30),
Cost           MONEY
);
GO

INSERT INTO #Routes (RouteID, DepartureCity, ArrivalCity, Cost) VALUES
(1,'Austin','Dallas',100),
(2,'Dallas','Memphis',200),
(3,'Memphis','Des Moines',300),
(4,'Dallas','Des Moines',400);
GO

/*----------------------------------------------------
Puzzle #37 - Group Criteria Keys
*/----------------------------------------------------

DROP TABLE IF EXISTS #GroupCriteria;
GO

CREATE TABLE #GroupCriteria
(
OrderID      INTEGER,
Distributor  VARCHAR(100),
Facility     INTEGER,
[Zone]       VARCHAR(100),
Amount       MONEY
);
GO

INSERT INTO #GroupCriteria (OrderID, Distributor, Facility, [Zone], Amount) VALUES
(1,'ACME',123,'ABC',100),
(2,'ACME',123,'ABC',75),
(3,'Direct Parts',789,'XYZ',150),
(4,'Direct Parts',789,'XYZ',125);
GO

/*----------------------------------------------------
Puzzle #38 - Reporting Elements
*/----------------------------------------------------

DROP TABLE IF EXISTS #RegionSales;
GO

CREATE TABLE #RegionSales
(
Region       VARCHAR(100),
Distributor  VARCHAR(100),
Sales        INTEGER
);
GO

INSERT INTO #RegionSales (Region, Distributor, Sales) VALUES
('North','ACE',10),
('South','ACE',67),
('East','ACE',54),
('North','ACME',65),
('South','ACME',9),
('East','ACME',1),
('West','ACME',7),
('North','Direct Parts',8),
('South','Direct Parts',7),
('West','Direct Parts',12);
GO

/*----------------------------------------------------
Puzzle #39 - Prime Numbers
*/----------------------------------------------------

DROP TABLE IF EXISTS #PrimeNumbers;
GO

CREATE TABLE #PrimeNumbers
(
IntegerValue  INTEGER
);
GO

INSERT INTO #PrimeNumbers (IntegerValue) VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
GO

/*----------------------------------------------------
Puzzle #40 - Sort Order
*/----------------------------------------------------

DROP TABLE IF EXISTS #SortOrder;
GO

CREATE TABLE #SortOrder
(
City  VARCHAR(100)
);
GO

INSERT INTO #SortOrder (City) VALUES
('Atlanta'),('Baltimore'),('Chicago'),('Denver');
GO

/*----------------------------------------------------
Puzzle #41 - Associate IDs
*/----------------------------------------------------

DROP TABLE IF EXISTS #Associates;
DROP TABLE IF EXISTS #Associates2;
DROP TABLE IF EXISTS #Associates3;
GO

CREATE TABLE #Associates
(
Associate1  VARCHAR(100),
Associate2  VARCHAR(100)
);
GO

INSERT INTO #Associates (Associate1, Associate2) VALUES
('Anne','Betty'),('Anne','Charles'),('Betty','Dan'),('Charles','Emma'),
('Francis','George'),('George','Harriet');
GO

/*----------------------------------------------------
Puzzle #42 - Mutual Friends
*/----------------------------------------------------

DROP TABLE IF EXISTS #Friends;
GO

CREATE TABLE #Friends
(
Friend1  VARCHAR(100),
Friend2  VARCHAR(100)
);
GO

INSERT INTO #Friends (Friend1, Friend2) VALUES
('Jason','Mary'),('Mike','Mary'),('Mike','Jason'),
('Susan','Jason'),('John','Mary'),('Susan','Mary');
GO

/*----------------------------------------------------
Puzzle #43 - Unbounded Preceding
*/----------------------------------------------------

DROP TABLE IF EXISTS #CustomerOrders;
GO

CREATE TABLE #CustomerOrders
(
OrderID     INTEGER,
CustomerID  INTEGER,
Quantity    INTEGER
);
GO

INSERT INTO #CustomerOrders (OrderID, CustomerID, Quantity) VALUES 
(1,1001,5),(2,1001,8),(3,1001,3),(4,1001,7),
(1,2002,4),(2,2002,9);
GO

/*----------------------------------------------------
Puzzle #44 - Slowly Changing Dimension Part I
*/----------------------------------------------------

DROP TABLE IF EXISTS #Balances;
GO

CREATE TABLE #Balances
(
CustomerID   INTEGER,
BalanceDate  DATE,
Amount       MONEY
);
GO

INSERT INTO #Balances (CustomerID, BalanceDate, Amount) VALUES
(1001,'10/11/2021',54.32),
(1001,'10/10/2021',17.65),
(1001,'9/18/2021',65.56),
(1001,'9/12/2021',56.23),
(1001,'9/1/2021',42.12),
(2002,'10/15/2021',46.52),
(2002,'10/13/2021',7.65),