November 12, 2015 at 10:43 am
I'm looking for a number of problems that I can ask people to solve in T-SQL to try and drive some learning. I'm wondering what problems people would recommend. I'm thinking to make T-SQL as of SQL Server 2012 as a base version.
I think about things along the lines of
1. Find duplicates rows, given a PK in the table.
2. Find sums of sales for a customer, with positive and negative amounts
3. Match up events for scheduling data, such as in and out times for a car in a lot.
If you can provide 2-10 rows of setup data and results with a description, I'd appreciate it.
November 13, 2015 at 4:47 am
Hi
I want to return two character from a column. But now the column itself returns 0 -9, then double digits. before I concatenated "0", then the problem I had was when I get 13, plus my "0" I would get 013. how do I return just two characters?
November 16, 2015 at 2:53 am
Heres a good real life example I came across.
This takes annual Yield curves and then calculates a Cumulative End of Month position.
The data values provided are at End of year, so Year 1 Month 12 = 1.89, it is assumed that Year 0 Month 12 is 0, such that the Year 1 Month 1 is calculated as being 0 + 1/12 of the difference between Y1 and the previous year, and so on.
Sample data
DECLARE @Curves TABLE
(
YearNumber SMALLINT
,DataHeader char(4)
,Percentage DECIMAL(9,2)
);
INSERT INTO @Curves
VALUES
(1,'Test',1.89)
,(2,'Test',1.85)
,(3,'Test',1.92)
,(4,'Test',2.16)
,(5,'Test',2.29)
,(6,'Test',2.43)
,(7,'Test',2.55)
,(8,'Test',2.66)
,(9,'Test',2.75)
,(10,'Test',2.83);
The expected output is
YMDHYcurveMcurve
11Test1.890.157500
12Test1.890.315000
13Test1.890.472500
14Test1.890.630000
15Test1.890.787500
16Test1.890.945000
17Test1.891.102500
18Test1.891.260000
19Test1.891.417500
110Test1.891.575000
111Test1.891.732500
112Test1.891.890000
21Test1.851.886667
22Test1.851.883334
One solutions is
SELECT
YearNumber
, MonthNumber
, DataHeader
, Percentage
, LAG(Percentage, 12, 0) OVER (ORDER BY YearNUMBER) PreviousYearPercent
, LAG(Percentage, 12, 0) OVER (ORDER BY YearNUMBER) + (Percentage-(LAG(Percentage, 12, 0) OVER (ORDER BY YearNUMBER)) ) * ((m.MonthNumber) % 13) /12.0 MonthlyCurve
FROM @Curves
CROSS APPLY
(
SELECT TOP 12 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) MonthNumber
FROM sys.objects
) M
The code teaches the use of windowing functions and especially the Lag, though could easily be changed to use Lead.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 16, 2015 at 4:29 pm
I thought more people would jump on this. :Whistling:
Anyhow I took a few minutes to throw some stuff together for you. Perhaps this will be helpful.
USE tempdb
GO
/****************************************************************************************
(1) Create Sample Data
100 rows, 5 salespeopleID (1,2,3,4,5), 5 days of sales (20100101 - 20100115)
****************************************************************************************/;
-- Create the and populate #sales
IF OBJECT_ID('tempdb..#sales') IS NOT NULL DROP TABLE #sales;
SELECT * INTO #sales FROM (VALUES(1, '2010-01-01', 1, 249.78),(2, '2010-01-01', 2, 263.33),(4, '2010-01-01', 3, 214.81),(8, '2010-01-01', 3, 113.15),(9, '2010-01-01', 3, 357.79),(10, '2010-01-01', 3, 172.43),(3, '2010-01-01', 4, 443.78),(6, '2010-01-01', 4, 495.78),(5, '2010-01-01', 5, 472.86),(7, '2010-01-01', 5, 402.68),(12, '2010-01-02', 1, 481.43),(13, '2010-01-02', 1, 3.37),(17, '2010-01-02', 1, 379.94),(11, '2010-01-02', 3, 302.97),(14, '2010-01-02', 4, 256.72),(16, '2010-01-02', 4, 228.56),(15, '2010-01-02', 5, 315.78),(23, '2010-01-03', 2, 458.38),(21, '2010-01-03', 3, 57.30),(22, '2010-01-03', 3, 115.15),(18, '2010-01-03', 4, 54.44),(19, '2010-01-03', 4, 440.74),(20, '2010-01-03', 5, 287.39),(25, '2010-01-04', 1, 67.85),(26, '2010-01-04', 2, 200.36),(24, '2010-01-04', 4, 28.60),(27, '2010-01-04', 4, 483.84),(28, '2010-01-04', 4, 195.57),(32, '2010-01-05', 2, 3.18),(30, '2010-01-05', 3, 292.26),(29, '2010-01-05', 4, 186.73),(31, '2010-01-05', 5, 401.70),(35, '2010-01-06', 2, 131.32),(34, '2010-01-06', 3, 403.64),(33, '2010-01-06', 4, 420.73),(36, '2010-01-06', 4, 391.77),(37, '2010-01-07', 1, 488.61),(38, '2010-01-07', 1, 155.20),(39, '2010-01-07', 1, 381.21),(41, '2010-01-07', 1, 336.99),(40, '2010-01-07', 4, 435.03),(42, '2010-01-07', 5, 231.36),(49, '2010-01-08', 2, 475.14),(44, '2010-01-08', 3, 264.74),(48, '2010-01-08', 4, 259.12),(43, '2010-01-08', 5, 219.64),(45, '2010-01-08', 5, 398.55),(46, '2010-01-08', 5, 287.93),(47, '2010-01-08', 5, 194.30),(53, '2010-01-09', 2, 365.93),(56, '2010-01-09', 2, 177.95),(50, '2010-01-09', 4, 288.12),(51, '2010-01-09', 4, 161.99),(52, '2010-01-09', 4, 303.21),(54, '2010-01-09', 5, 69.83),(55, '2010-01-09', 5, 289.72),(58, '2010-01-10', 2, 416.94),(61, '2010-01-10', 2, 217.19),(63, '2010-01-10', 2, 148.39),(60, '2010-01-10', 3, 485.84),(62, '2010-01-10', 3, 418.11),(64, '2010-01-10', 3, 104.27),(57, '2010-01-10', 4, 293.33),(59, '2010-01-10', 4, 8.25),(65, '2010-01-11', 1, 272.23),(67, '2010-01-11', 2, 175.90),(66, '2010-01-11', 3, 225.69),(68, '2010-01-11', 5, 485.11),(69, '2010-01-11', 5, 385.25),(71, '2010-01-12', 1, 492.58),(72, '2010-01-12', 1, 427.46),(76, '2010-01-12', 1, 152.25),(70, '2010-01-12', 2, 76.46),(73, '2010-01-12', 3, 493.72),(74, '2010-01-12', 4, 155.74),(75, '2010-01-12', 4, 100.66),(77, '2010-01-13', 1, 198.16),(79, '2010-01-13', 2, 115.50),(85, '2010-01-13', 2, 173.18),(78, '2010-01-13', 3, 334.13),(82, '2010-01-13', 3, 184.04),(84, '2010-01-13', 3, 39.77),(86, '2010-01-13', 3, 201.02),(87, '2010-01-13', 3, 460.27),(80, '2010-01-13', 4, 474.78),(83, '2010-01-13', 4, 22.18),(81, '2010-01-13', 5, 437.23),(91, '2010-01-14', 1, 376.48),(90, '2010-01-14', 2, 257.18),(92, '2010-01-14', 2, 185.33),(94, '2010-01-14', 4, 162.56),(88, '2010-01-14', 5, 414.67),(89, '2010-01-14', 5, 117.04),(93, '2010-01-14', 5, 421.37),(97, '2010-01-15', 1, 289.55),(98, '2010-01-15', 3, 204.32),(100, '2010-01-15', 4, 26.95),(95, '2010-01-15', 5, 465.83),(96, '2010-01-15', 5, 2.74),(99, '2010-01-15', 5, 327.97)) T(SaleID, SaleDate,SalesPersonID, SaleAmount);
-- Add Primary key and a Nonclustered index (optional)
CREATE CLUSTERED INDEX pk_sales_SaleID ON #sales(SaleID);
CREATE NONCLUSTERED INDEX nc_sales_SaleDate ON #sales(SaleDate, SalesPersonID) INCLUDE (SaleAmount);
-- Output: SELECT * FROM #sales ORDER BY SaleDate;
/****************************************************************************************
(2) Questions with answers (Basic)
****************************************************************************************/;
-- 1. What is the Average Sales per day?
SELECT SaleDate, AvgSales = AVG(SaleAmount)
FROM #sales
GROUP BY SaleDate
ORDER BY SaleDate -- not needed, for presentation only
-- 2. For SalesPersonID 4 only, what day did they have the highest sales?
SELECT TOP (1) SalesPersonID, SaleDate
FROM #sales
WHERE SalesPersonID = 4
GROUP BY SalesPersonID, SaleDate
ORDER BY SUM(SaleAmount) DESC
-- 3. For SalesPersonID 4 only, What was the most sales they had in a single day?
SELECT TOP (1) MostSales = COUNT(SaleAmount)
FROM #sales
WHERE SalesPersonID = 4
GROUP BY SalesPersonID, SaleDate
ORDER BY COUNT(SaleAmount) DESC;
-- 4. What day had the highest sales? What was the amount?
SELECT TOP (1) SaleDate, MaxSales = MAX(SaleAmount)
FROM #sales
GROUP BY SaleDate
ORDER BY MAX(SaleAmount) DESC;
-- 5. For all employees, who had the most sales in one day? What date? Make sure to inlcude ties
SELECT TOP (1) WITH TIES SaleDate, SalesPersonID, Sales = COUNT(SaleAmount)
FROM #sales
GROUP BY SaleDate, SalesPersonID
ORDER BY COUNT(SaleAmount) DESC; -- not needed, for presentation only
-- Note the WITH TIES...
-- 6. For all employees, get me their total daily sales count and sales amount for that day
SELECT
SaleDate,
SalesPersonID,
TotalSalesForMonth = COUNT(SaleAmount),
SalesAmtForMonth = SUM(SaleAmount)
FROM #sales
GROUP BY SaleDate, SalesPersonID
ORDER BY SaleDate, SalesPersonID -- not needed, for presentation only
/****************************************************************************************
(3) Questions with answers (medium difficulty)
****************************************************************************************/;
-- 7. For each employee, get me the Total Sales Per day and how much higher or lower
-- that amount is compared to the average sales for the day.
SELECT
SaleDate,
SalesPersonID,
DailySPTotal,
OverUnderAvg = DailySPTotal - DailyAvg
FROM
(
SELECT
s.SaleDate,
SalesPersonID,
DailySPTotal = SUM(SaleAmount)
FROM #sales s
GROUP BY s.SaleDate, SalesPersonID
) DailySP CROSS JOIN
(
SELECT
SaleDay = SaleDate,
DailyAvg = AVG(SaleAmount)
FROM #sales
GROUP BY SaleDate
) DailyAvg
WHERE SaleDay = SaleDate;
-- 8. For each day, who had the highests sales total for the day
;
-- Solution #1
SELECT SaleDate, SalesPersonID, SaleAmount
FROM
(
SELECT
SaleDate,
SalesPersonID,
HighestDailySales = RANK() OVER (PARTITION BY SaleDate ORDER BY SaleAmount DESC),
SaleAmount
FROM #sales
) highestSalesPerDay
WHERE HighestDailySales = 1;
-- Solution #2 (better)
SELECT s.SaleDate, s.SalesPersonID, mx
FROM #sales s
INNER JOIN
(
SELECT SaleDate, mx = MAX(SaleAmount)
FROM #sales
GROUP BY SaleDate
) x
ON x.mx = s.SaleAmount AND s.SaleDate = x.SaleDate AND s.SaleDate = x.SaleDate;
/****************************************************************************************
(4) Questions with answers (medium+ difficulty)
****************************************************************************************/;
-- 9. Who had the most sales. Get the answer without using TOP
WITH SalesBySP AS
(
SELECT SalesPersonID, SalesSum = SUM(SaleAmount)
FROM #sales
GROUP BY SalesPersonID
),
MaxSales AS
(
SELECT MaxSales = MAX(SalesSum)
FROM SalesBySP
)
SELECT SalesPersonID, SalesSum
FROM SalesBySp
CROSS JOIN MaxSales
WHERE SalesSum = MaxSales;
-- 10. For all employees and all days, get me sales amount per month
-- INCLUDING people without any sales on a particular day
-- (for them SalesAmtForMonth should be 0.00)
;
-- Solution #1
SELECT
spsd.SaleDate,
spsd.SalesPersonID,
SalesAmtForMonth = ISNULL(SalesAmtForMonth,0)
FROM
(
SELECT DISTINCT sd.SaleDate, sp.SalesPersonID
FROM #sales sd
CROSS JOIN
(
SELECT SalesPersonID
FROM #sales
) sp
) spsd
LEFT JOIN
(
SELECT SaleDate, SalesPersonID, SalesAmtForMonth = SUM(SaleAmount)
FROM #sales
GROUP BY SaleDate, SalesPersonID
) sm ON spsd.SaleDate = sm.SaleDate AND spsd.SalesPersonID = sm.SalesPersonID;
-- Solution #2 (perhaps better)
WITH iTally(N) AS
(
SELECT TOP(15) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N)
),
EveryoneByDate(sd,spN) AS
(
SELECT CAST(DATEADD(D,sd.N-1,'1/1/2010') AS date), sp.N
FROM iTally sp CROSS JOIN iTally sd
WHERE sd.N <= 16 AND sp.N < 6
),
SumSalesByMonth AS
(
SELECT SaleDate, SalesPersonID, SalesAmtForMonth = SUM(SaleAmount)
FROM #sales
GROUP BY SaleDate, SalesPersonID
)
SELECT
SalesDate = sd,
SalesPersonID = spN,
SalesAmtForMonth = ISNULL(SalesAmtForMonth,0)
FROM EveryoneByDate E
LEFT JOIN SumSalesByMonth S ON E.sd = S.SaleDate AND E.spN = S.SalesPersonID
ORDER BY sd, spN; -- not needed, presenation only.
FYI:
/****************************************************************************************
(5) High I generated the sample data
****************************************************************************************/;
-- CREATE THE SAMPLE DATA (Note there are 5 employees total 1 through 5)
SELECT X =
'IF OBJECT_ID(''tempdb..#sales'') IS NOT NULL DROP TABLE #sales;'+CHAR(13)+
'SELECT * INTO #sales FROM (VALUES'+
STUFF
((
SELECT
CONCAT
(',',
CONCAT('(',SaleID,', '),
CONCAT('''',SaleDate,''', '),
CONCAT('',SalesPersonID,', '),
CONCAT('',SaleAmount,')')
)
FROM #sales
FOR XML PATH(''), TYPE
).value('.', 'varchar(8000)'),1,1,'')+
') T(SaleID, SaleDate,SalesPersonID, SaleAmount);'
-- Itzik Ben-Gan 2001
November 16, 2015 at 7:36 pm
Steve Jones - SSC Editor (11/12/2015)
I'm looking for a number of problems that I can ask people to solve in T-SQL to try and drive some learning. I'm wondering what problems people would recommend. I'm thinking to make T-SQL as of SQL Server 2012 as a base version.I think about things along the lines of
1. Find duplicates rows, given a PK in the table.
2. Find sums of sales for a customer, with positive and negative amounts
3. Match up events for scheduling data, such as in and out times for a car in a lot.
If you can provide 2-10 rows of setup data and results with a description, I'd appreciate it.
On #1... is that a trick question? If you have a PK, you shouldn't have duplicates, right? Can you provide more details on that particular problem, please?
As to other problems, perhaps a couple of the following will do. Teaching how to actually loop (without RBAR, of course) and how to count are things done taught in all other languages right after the classic "Hello World" demo but not in programming courses for T-SQL. As you're seen on these forums over the years, a huge number of seemingly impossible problems have been solved through the use of "Pseudo-Cursors", Tally Tables and their inline kin (including ROW_NUMBER()), and some simple mathematics.
JBM1. Count from 1 to a million without using any form of RBAR, which means no rCTEs, no Cursors, no While Loops, etc. The purpose of this is a primer to introduce the concept of "Pseudo-Cursors", which is nothing more than SQL Server looping at the machine language level behind the scenes. It also teaches the concept of thinking in columns rather than rows. Obviously, no test data is required for this problem because it's also the basis for making test data.
JBM2. In preparation for JBM3 below, make a million row test table with per/minute dates and times along with a random machine state of 1 for "on" and 0 for "off". Again... no test data required here because you're building it. This will introduce the concept that RAND() isn't and that NEWID() is actually a random number that can be used as a seed for integers and for RAND(). Ironically, RAND() requires a random seed to produce multiple random values for any given multi-row set. It will also introduce the very important usage of MODULO and could segue into some of the highly valuable integer math concepts.
JBM3. The mate of your problem #3 would be the traditional "find the state change" problem where you have a machine that reports once per minute and you need to find things like...
1. The longest and shortest durations of a machine being on or off and what the dates/times and durations of those events where.
2. The average up time period and the average down time period.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2015 at 7:40 pm
hoseam (11/13/2015)
HiI want to return two character from a column. But now the column itself returns 0 -9, then double digits. before I concatenated "0", then the problem I had was when I get 13, plus my "0" I would get 013. how do I return just two characters?
You've happened across a totally different thread. I recommend that you start a new topic all your own. You'll get a lot more people looking at it that way.
I also strong urge you to read the first link in my signature line below under "Helpful Links" so that you stand the best chance of getting your question answered.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 17, 2015 at 11:33 am
Jeff Moden (11/16/2015)
On #1... is that a trick question? If you have a PK, you shouldn't have duplicates, right? Can you provide more details on that particular problem, please?
No, if I have customers, and there's a PK, do I have duplicate customer names? I can think of a few scenarios here, but basically looking for some progressive problems to help people get better. Solve some exercises and improve their skills.
November 17, 2015 at 8:26 pm
Steve Jones - SSC Editor (11/17/2015)
Jeff Moden (11/16/2015)
On #1... is that a trick question? If you have a PK, you shouldn't have duplicates, right? Can you provide more details on that particular problem, please?
No, if I have customers, and there's a PK, do I have duplicate customer names? I can think of a few scenarios here, but basically looking for some progressive problems to help people get better. Solve some exercises and improve their skills.
Ah. Understood.
In the meantime, someone asked a question and provided some test data. It's a good simple "joined aggregation" problem. http://www.sqlservercentral.com/Forums/Topic1733470-2799-1.aspx
EDIT... sorry... used the wrong IFCode. The link should be visible now.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2015 at 5:37 pm
Thanks, everyone. Trying to get some learning material for people together.
November 18, 2015 at 6:01 pm
Steve Jones - SSC Editor (11/18/2015)
Thanks, everyone. Trying to get some learning material for people together.
I have to ask... what is this all for? Where will this learning take place?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2015 at 8:30 am
Here, though not sure how/why/when. I am trying to get some POC together that could be used to help people walk a path, but be easy to evolve and grow over time.
November 19, 2015 at 9:11 am
Steve Jones - SSC Editor (11/19/2015)
Here, though not sure how/why/when. I am trying to get some POC together that could be used to help people walk a path, but be easy to evolve and grow over time.
Sounds like the beginning of an interesting project.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2015 at 10:14 am
Jeff Moden (11/16/2015)
hoseam (11/13/2015)
HiI want to return two character from a column. But now the column itself returns 0 -9, then double digits. before I concatenated "0", then the problem I had was when I get 13, plus my "0" I would get 013. how do I return just two characters?
You've happened across a totally different thread. I recommend that you start a new topic all your own. You'll get a lot more people looking at it that way.
I also strong urge you to read the first link in my signature line below under "Helpful Links" so that you stand the best chance of getting your question answered.
But this would be a good exercise in the vein of a good basic exercise. 😀 It is shocking how many people request this kind of thing and can't figure out a solution for it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2015 at 11:10 am
Jeff Moden (11/19/2015)
Steve Jones - SSC Editor (11/19/2015)
Here, though not sure how/why/when. I am trying to get some POC together that could be used to help people walk a path, but be easy to evolve and grow over time.Sounds like the beginning of an interesting project.
This does sound like it could be interesting. Do you mean something like a stairway series of miniature articles that get deeper and deeper as they go?
November 19, 2015 at 11:14 am
Ed Wagner (11/19/2015)
Jeff Moden (11/19/2015)
Steve Jones - SSC Editor (11/19/2015)
Here, though not sure how/why/when. I am trying to get some POC together that could be used to help people walk a path, but be easy to evolve and grow over time.Sounds like the beginning of an interesting project.
This does sound like it could be interesting. Do you mean something like a stairway series of miniature articles that get deeper and deeper as they go?
Yes and no. Each article could stand alone or build on previous articles but all in a very "SQL Spackle" fashion.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply