Skip to content

Commit dc33334

Browse files
smpetersgithubsmpetersgithub
authored
Update Advanced SQL Puzzles DDL.sql
1 parent e912e80 commit dc33334

1 file changed

Lines changed: 117 additions & 1 deletion

File tree

‎Advanced SQL Puzzles/Advanced SQL Puzzles DDL.sql‎

Lines changed: 117 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
Scott Peters
33
DDL for Advanced SQL Puzzles
44
https://advancedsqlpuzzles.com
5-
Last Updated 12/29/2023
5+
Last Updated 02/18/2024
66
Microsoft SQL Server T-SQL
77
88
*/----------------------------------------------------
@@ -1785,6 +1785,122 @@ INSERT INTO #Repairs (RepairID, CustomerID, RepairDate) VALUES
17851785
(8008,'B','2023-09-01');
17861786
GO
17871787

1788+
/*----------------------------------------------------
1789+
DDL for Puzzle #73
1790+
Distinct Statuses
1791+
*/----------------------------------------------------
1792+
DROP TABLE IF EXISTS #WorkflowSteps;
1793+
GO
1794+
1795+
CREATE TABLE #WorkflowSteps
1796+
(
1797+
StepID INTEGER PRIMARY KEY,
1798+
Workflow VARCHAR(50),
1799+
[Status] VARCHAR(50)
1800+
);
1801+
GO
1802+
1803+
INSERT INTO #WorkflowSteps (StepID, Workflow, [Status]) VALUES
1804+
(1, 'Alpha', 'Open'),
1805+
(2, 'Alpha', 'Open'),
1806+
(3, 'Alpha', 'Inactive'),
1807+
(4, 'Alpha', 'Open'),
1808+
(5, 'Bravo', 'Closed'),
1809+
(6, 'Bravo', 'Closed'),
1810+
(7, 'Bravo', 'Open'),
1811+
(8, 'Bravo', 'Inactive');
1812+
GO
1813+
1814+
/*----------------------------------------------------
1815+
DDL for Puzzle #74
1816+
Bowling League
1817+
*/----------------------------------------------------
1818+
DROP TABLE IF EXISTS ##BowlingResults;
1819+
GO
1820+
1821+
CREATE TABLE ##BowlingResults
1822+
(
1823+
GameID INTEGER,
1824+
Bowler VARCHAR(50),
1825+
Score INTEGER,
1826+
PRIMARY KEY (GameID, Bowler)
1827+
);
1828+
GO
1829+
1830+
INSERT INTO ##BowlingResults (GameID, Bowler, Score) VALUES
1831+
(1, 'John', 167),
1832+
(1, 'Susan', 139),
1833+
(1, 'Ralph', 95),
1834+
(1, 'Mary', 90),
1835+
(2, 'Susan', 187),
1836+
(2, 'John', 155),
1837+
(2, 'Dennis', 100),
1838+
(2, 'Anthony', 78);
1839+
GO
1840+
1841+
/*----------------------------------------------------
1842+
DDL for Puzzle #75
1843+
Symmetric Matches
1844+
*/----------------------------------------------------
1845+
DROP TABLE IF EXISTS ##Boxes;
1846+
GO
1847+
1848+
CREATE TABLE ##Boxes
1849+
(
1850+
Box CHAR(1),
1851+
[Length] INTEGER,
1852+
Width INTEGER,
1853+
Height INTEGER
1854+
);
1855+
GO
1856+
1857+
INSERT INTO ##Boxes (Box, [Length], Width, Height) VALUES
1858+
('A', 10, 25, 15),
1859+
('B', 15, 10, 25),
1860+
('C', 10, 15, 25),
1861+
('D', 20, 30, 30),
1862+
('E', 30, 30, 20);
1863+
GO
1864+
1865+
/*----------------------------------------------------
1866+
DDL for Puzzle #76
1867+
Determine Batches
1868+
*/----------------------------------------------------
1869+
DROP TABLE IF EXISTS ##BatchStarts;
1870+
DROP TABLE IF EXISTS ##BatchLines;
1871+
GO
1872+
1873+
CREATE TABLE ##BatchStarts
1874+
(
1875+
Batch CHAR(1),
1876+
BatchStart INTEGER,
1877+
PRIMARY KEY (Batch, BatchStart)
1878+
);
1879+
GO
1880+
1881+
CREATE TABLE ##BatchLines
1882+
(
1883+
Batch CHAR(1),
1884+
Line INTEGER,
1885+
Syntax VARCHAR(MAX),
1886+
PRIMARY KEY (Batch, Line)
1887+
);
1888+
GO
1889+
1890+
INSERT INTO ##BatchStarts (Batch, BatchStart) VALUES
1891+
('A', 1),
1892+
('A', 5);
1893+
GO
1894+
1895+
INSERT INTO ##BatchLines (Batch, Line, Syntax) VALUES
1896+
('A', 1, 'SELECT *'),
1897+
('A', 2, 'FROM Account;'),
1898+
('A', 3, 'GO'),
1899+
('A', 4, ''),
1900+
('A', 5, 'TRUNCATE TABLE Accounts;'),
1901+
('A', 6, 'GO');
1902+
GO
1903+
17881904
/*----------------------------------------------------
17891905
The End
17901906
*/----------------------------------------------------

0 commit comments

Comments
 (0)