|
|
Software Engineer - Red Gate Software
-
Posted Monday, January 07, 2008 5:56 PM |
/*
Happy New Year everybody! To help with the January blues, I thought it was time for another puzzle. The inspiration for this one comes from a SQL question posed by my girlfriend. She is a geologist and, on a recent field trip to Iceland, was wandering around with a GPS strapped to her head to make 3D representations of the side of hills. At the same time she was taking pictures of rocks on her digital camera (we have the best holiday snaps!). Her question was this: how could she match the GPS points to the photos so that she had a rough idea of where all the photos were taken.
So, on to the puzzle. We have a whole lot of points from our GPS and a whole lot of photos. Both the photos and the points have a time and date when they were taken. The puzzle is simple. I want to know which is the nearest point to each photo. Since we only have the time to go on, I want the point that is closest in terms time.
So, for example, if we have a photo taken at 13:01 and points taken at 12:55, 13.00 and 13:06 then I want the photo to be at the point taken at 13:00. Any way of doing this in SQL is fine but the simpler your solution the better. If anyone wants to show off and do this in LINQ as well then I would be interested in your answer (as somebody who likes to write stored procedures I am a bit wary of LINQ but I am very interested in learning about it). I had to make up some imaginary data and my results are:
PhotoName x y z
--------- ----------- ----------- -----------
Photo01 1 1 1
Photo02 1 1 1
Photo03 2 1 1
Photo04 4 1 1
Photo05 4 1 1
Photo06 5 1 1
Photo07 8 1 1
Photo08 15 1 1
Photo09 19 1 1
Photo10 20 1 1
Photo11 20 1 1
Photo12 20 1 1
Photo13 20 1 1
Photo14 21 1 1
Photo15 21 1 1
On a final note, I would like to ask a huge favour of anyone reading this. I have been writing a tool to fill a SQL Server database with test data and have recently released a beta version. I think it is a really cool tool and is something I wanted back when I was a developer DBA. If you need a quick and easy way of filling a table with some example data you will almost certainly find this handy! We could really do with some feedback so if you have a spare moment please check it out! You can download it at:
http://www.red-gate.com/messageboard/viewtopic.php?t=6140
Most importantly, if you have any comments then please post them in the forum:
http://www.red-gate.com/messageboard/viewforum.php?f=76
Thanks and have fun,
Lionel
SET NOCOUNT ON
DECLARE @Photos TABLE
(
PhotoName NVARCHAR(7) PRIMARY KEY,
TimeTaken DATETIME
)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo01', '2008-01-02 15:01:10.12')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo02', '2008-01-07 10:21:06.13')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo03', '2008-01-07 11:15:11.42')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo04', '2008-01-07 12:38:53.653')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo05', '2008-01-07 12:38:53.657')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo06', '2008-01-07 13:25:51.12')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo07', '2008-01-07 14:35:00.00')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo08', '2008-01-07 15:04:19.55')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo09', '2008-01-07 16:01:01.17')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo10', '2008-01-07 16:10:32.92')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo11', '2008-01-07 16:10:32.92')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo12', '2008-01-07 16:10:32.92')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo13', '2008-01-07 16:33:18.35')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo14', '2008-10-15 16:15:52.45')
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES ('Photo15', '2010-10-07 17:53:25.26')
DECLARE @Locations TABLE
(
LocationID INT IDENTITY(1,1) PRIMARY KEY,
x INT,
y INT,
z INT,
TimeTaken DATETIME
)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (1,1,1, '2008-01-07 10:00:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (2,1,1, '2008-01-07 11:05:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (3,1,1, '2008-01-07 11:30:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (4,1,1, '2008-01-07 12:30:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (5,1,1, '2008-01-07 13:10:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (6,1,1, '2008-01-07 14:10:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (7,1,1, '2008-01-07 14:20:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (8,1,1, '2008-01-07 14:30:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (9,1,1, '2008-01-07 14:40:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (10,1,1, '2008-01-07 14:50:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (12,1,1, '2008-01-07 15:01:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (13,1,1, '2008-01-07 15:02:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (14,1,1, '2008-01-07 15:03:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (15,1,1, '2008-01-07 15:04:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (16,1,1, '2008-01-07 15:05:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (17,1,1, '2008-01-07 15:06:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (18,1,1, '2008-01-07 15:07:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (19,1,1, '2008-01-07 16:08:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (20,1,1, '2008-01-07 16:09:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (21,1,1, '2008-01-07 17:00:00.00')
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (22,1,1, '2008-01-07 17:00:00.00')
SET NOCOUNT OFF
|
-
Posted Tuesday, November 27, 2007 7:12 PM |
|
/*
So I thought it was just about time for another puzzle but I was having great difficulty in coming up with a new challenge. Luckily Andras has posted a blog entry about how the
POWERSUM function has been removed from SQL Server.
http://www.simple-talk.com/community/blogs/andras/archive/2007/11/22/40021.aspx
This puzzle is a nice simple one and is to write a single select statement that will return the same results as POWERSUM when run on the supplied table variable. The input values will be between 0 and 127 but it would be nice to see solutions that will scale to beyond that. Andras has posted an explanation of what POWERSUM does but if it is still unclear exactly what it does (The output seems to be the reverse of what I expect it to be!) add a comment to the blog and I will add some clarification. Have fun!
Lionel
*/
SET NOCOUNT ON
DECLARE @Data TABLE
(
Col INT
)
INSERT INTO @Data(col)
SELECT 1
INSERT INTO @Data(col)
SELECT 1
INSERT INTO @Data(col)
SELECT 2
INSERT INTO @Data(col)
SELECT 98
INSERT INTO @Data(col)
SELECT 31
INSERT INTO @Data(col)
SELECT 32
INSERT INTO @Data(col)
SELECT 32
INSERT INTO @Data(col)
SELECT 63
INSERT INTO @Data(col)
SELECT 17
INSERT INTO @Data(col)
SELECT 100
INSERT INTO @Data(col)
SELECT 56
INSERT INTO @Data(col)
SELECT 24
INSERT INTO @Data(col)
SELECT 17
INSERT INTO @Data(col)
SELECT 2
INSERT INTO @Data(col)
SELECT 127
INSERT INTO @Data(col)
SELECT 76
INSERT INTO @Data(col)
SELECT 84
INSERT INTO @Data(col)
SELECT 99
INSERT INTO @Data(col)
SELECT 12
INSERT INTO @Data(col)
SELECT 103
SET NOCOUNT OFF
-- This should return the same as
-- SELECT POWERSUM(col) FROM @Data
-- which is 0x0610028101000081001010009C000080
|
-
Posted Friday, October 26, 2007 10:53 AM |
|
/*
Well, it has been a while since I posted up a puzzle. I blame this on the very unflattering picture of myself attached to the blog. This causes me a natural reluctance to put it in a position where people might see it! It is that and ...ahem... the cat ate my homework ...ahem... blog entry. Anyway, this puzzle has a couple of parts. Since I am short on ideas, the first part is the same as the second part just with one extra column.
The puzzle is very simple. You have to move as much of the data as you can from the source tables to the destination tables. There is one restriction though; you can only use one insert statement in each part! Extra points are definitely awarded for a solution that works in both SQL Server 2000 and 2005. So, to summarize, one insert statement to move data from Part1Source -> Part1Dest, and one insert stament to move Part2Source -> Part2Dest.
Have fun!
Lionel
CREATE TABLE Part1Source
(
a INT,
b INT,
PRIMARY KEY (a,b)
)
INSERT INTO Part1Source VALUES (1,1)
INSERT INTO Part1Source VALUES (1,2)
INSERT INTO Part1Source VALUES (2,3)
INSERT INTO Part1Source VALUES (7,2)
INSERT INTO Part1Source VALUES (2,4)
INSERT INTO Part1Source VALUES (5,5)
INSERT INTO Part1Source VALUES (5,1)
INSERT INTO Part1Source VALUES (5,3)
INSERT INTO Part1Source VALUES (9,0)
INSERT INTO Part1Source VALUES (11,2)
CREATE TABLE Part1Dest
(
a INT UNIQUE,
b INT UNIQUE,
FOREIGN KEY (a,b) REFERENCES Part1Source(a,b)
)
------------------------ Part 2 ------------------------
CREATE TABLE Part2Source
(
a INT,
b INT,
c INT,
PRIMARY KEY (a,b,c)
)
INSERT INTO Part2Source VALUES (1,1,2)
INSERT INTO Part2Source VALUES (1,2,2)
INSERT INTO Part2Source VALUES (2,3,1)
INSERT INTO Part2Source VALUES (7,2,1)
INSERT INTO Part2Source VALUES (2,4,7)
INSERT INTO Part2Source VALUES (5,5,6)
INSERT INTO Part2Source VALUES (5,1,4)
INSERT INTO Part2Source VALUES (5,3,4)
INSERT INTO Part2Source VALUES (9,0,1)
INSERT INTO Part2Source VALUES (11,2,9)
CREATE TABLE Part2Dest
(
a INT UNIQUE,
b INT UNIQUE,
c INT UNIQUE,
FOREIGN KEY (a,b,c) REFERENCES Part2Source(a,b,c)
)
*/
|
-
Posted Wednesday, June 28, 2006 8:25 PM |
/* Just because I can't take another day of hassle for not posting up another puzzle here is a world cup themed one. This puzzle is by a friend of mine called Tom Sillence.
1) The first puzzle is to produce the group tables as seen on this page:
http://news.bbc.co.uk/sport1/hi/football/world_cup_2006/tables/default.stm
so we want a table with the following columns:
Group: (e.g. "Angola's Group", "Mexico's Group")
Country: the name of the country
P: The number of games this country has played
W: The number of wins the country has
D: The number of draws this country has
L: The number of losses this country has
F: The number of goals this country has scored
A: The number of goals this country has conceded
GD: Goal difference which is (F - A)
PTS: The total number of points
Q: contains the letter Q if the team qualifies, otherwise empty string
You have to work out which teams are together in each group by looking at who's played whom. You won't know which is group A, group B etc, so in my results I called them e.g. "Angola's group", picking an arbitrary team to represent the group.
A quick reprise of world cup rules will tell us how the results should be sorted and who qualifies for the next round:
- Each team gets 3 points for a win, 1 for a draw and 0 for a loss.
- Teams who are tied on points are sorted by goal difference (GD) and then by goals scored (F)
- The top two teams in each group qualify for the next round
2) Uncomment the lines below the message "UNCOMMENT THESE LINES FOR QUESTION 2 and 3" at the end of this file :)
(a) We've now removed some matches. Write a query that outputs which matches are still to be played. We want these columns:
Group, TeamA, TeamB
(b) Check that you can still output the group tables with these matches missing. For the purposes of working out the groups, you can assume no more than one match has been removed from each group.
3) Write a query that outputs each team who still has a match left to play, and what they must do in order to qualify. This should include both the result (win/lose/draw) and the margin of victory or defeat that is necessary. There are lots of options for how to format this.
In my solution I was trying to simulate what a commentator would say - I have a hardcoded string for each possible requirement. This is my output:
Angola, currently lying third in their group, play Portugal
Topping the group is impossible for them
They must win here or it's curtains for them Portugal, currently lying first in their group, play Angola
The result is immaterial, they have already qualified top
Qualification is already assured
Sweden, currently lying second in their group, play England
They must win in order to top the group
Qualification is already assured
England, currently lying first in their group, play Sweden
They only need a draw to qualify top
Qualification is already assured
France, currently lying third in their group, play Togo
They can still reach first place if they win my a margin of 3 or more
Their world cup hopes depend on a win by two or more goals Togo, currently lying last in their group, play France
err...
They're playing for pride now. Next stop for them is the airport.
(6 row(s) affected)
FOR THE RECORD:
- I made use of a table variable to avoid retyping the same expression many times, but technically all of this can be done with a single query (it would be huge).
- However, my solution to Q3 only takes account of points and goal difference, not goals scored. Can you do better?
*/
set nocount on
DECLARE @Results Table
(
TeamA VARCHAR(20) NOT NULL,
TeamAGoals INT NOT NULL,
TeamBGoals INT NOT NULL,
TeamB VARCHAR(20) NOT NULL
)
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Saudi Arabia',0,1,'Spain')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Switzerland',2,0,'South Korea')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Togo',0,2,'France')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Ukraine',1,0,'Tunisia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Croatia',2,2,'Australia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Czech Republic',0,2,'Italy')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Ghana',2,1,'USA')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Japan',1,4,'Brazil')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Holland',0,0,'Argentina')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Angola',1,1,'Iran')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Ivory Coast',3,2,'Serbia & Montenegro')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Portugal',2,1,'Mexico')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Costa Rica',1,2,'Poland')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Ecuador',0,3,'Germany')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Paraguay',2,0,'Trinidad and Tobago')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Sweden',2,2,'England')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Saudi Arabia',0,4,'Ukraine')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Spain',3,1,'Tunisia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Togo',0,2,'Switzerland')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Brazil',2,0,'Australia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('South Korea',1,1,'France')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Japan',0,0,'Croatia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Czech Republic',0,2,'Ghana')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Italy',1,1,'USA')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Portugal',2,0,'Iran')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Argentina',6,0,'Serbia & Montenegro')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Holland',2,1,'Ivory Coast')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Mexico',0,0,'Angola')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Ecuador',3,0,'Costa Rica')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('England',2,0,'Trinidad and Tobago')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Sweden',1,0,'Paraguay')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Germany',1,0,'Poland')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Spain',4,0,'Ukraine')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Tunisia',2,2,'Saudi Arabia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Brazil',1,0,'Croatia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Switzerland',0,0,'France')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('South Korea',2,1,'Togo')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Australia',3,1,'Japan')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES('Italy',2,0,'Ghana')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('USA',0,3,'Czech Republic')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Angola',0,1,'Portugal')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Mexico',3,1,'Iran')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Serbia & Montenegro',0,1,'Holland')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Argentina',2,1,'Ivory Coast')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('England',1,0,'Paraguay')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Trinidad and Tobago',0,0,'Sweden')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Germany',4,2,'Costa Rica')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
VALUES ('Poland',0,2,'Ecuador')
---- UNCOMMENT THESE LINES FOR QUESTION 2 and 3:
/*
delete from @results where teamA = 'Togo' and teamb = 'France'
delete from @results where teamA = 'Sweden' and teamb = 'England'
delete from @results where teamA = 'Angola' and teamb = 'Portugal'
--make this a draw so the france match is interesting :
update @results set teambgoals = 2
where teamA = 'Switzerland' and teamb = 'South Korea'
*/
|
-
Posted Friday, December 30, 2005 5:27 PM |
/* I hope that everybody had a good Christmas. I apologize for the lack of new puzzles but I have been using up the remains of my Holiday. Since it is the New Year I thought we should start it off with a nice easy but fun puzzle. Recently I have been struggling writing some internal Red Gate reports and while trying to get Reporting Services to draw bar graphs I thought wouldn’t it be much easier if we could just use SQL Server to generate all these reports internally. So this week’s puzzle is simple. Given some sales results generate an ASCII art bar chart representing the results. Whereas previous puzzles had either a right or a wrong answer with this one I would like to encourage a bit of competition so let’s see who can product the best looking ASCII art chart. Since space is limited I am only going to give people 4 columns to put on the chart. One column for each quarter of the year. The sales data is in a table cunningly labeled @SalesResults. This table has a column for which quarter of the year the results belong to which is a number from 1 to 4. There is also an AmountSold column for the amount of things sold in that quarter. I am also giving you a helper table called Sequence which just contains the numbers 0 to 100. A sequence table is the Swiss Army Knife of all database tables and is a very useful thing in any database. As usual with these puzzles you should be done with one top level select statement though you can use as many derived tables subselects and unions as you like. Defiantly no DDL, variables or Transact SQL Control statements like IF and WHILE. My solution is as follows: 20 |
|
|
|
|
| ----
| | |
| | |
| ---- | | ----
| | | | | | |
10 | | | | | | |
| | | ---- | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
| | | | | | | | |
0 | | | | | | | | |
---------------------------------------------
Q1 Q2 Q3 Q4
Have fun. Lionel */ SET NOCOUNT ON DECLARE @Sequence TABLE ( i INT NOT NULL ) DECLARE @i INT SET @i=0 WHILE(@i < 100) BEGIN INSERT INTO @Sequence(i) VALUES (@i) SET @i = @i + 1 END DECLARE @SalesResults TABLE ( [Quarter] INT PRIMARY KEY CHECK([Quarter] BETWEEN 1 AND 4), [AmountSold] INT NOT NULL ) INSERT INTO @SalesResults([Quarter], [AmountSold]) SELECT 1, 12 INSERT INTO @SalesResults([Quarter], [AmountSold]) SELECT 2, 9 INSERT INTO @SalesResults([Quarter], [AmountSold]) SELECT 3, 15 INSERT INTO @SalesResults([Quarter], [AmountSold]) SELECT 4, 12 SET NOCOUNT OFF
|
-
Posted Friday, December 30, 2005 5:23 PM |
There seems
to be computer game written in almost every language out there but I haven’t
yet found one written in Transact SQL. I thought that poor old DBAs should have
some form of game to play so I have written a little 3D maze game in Transact
SQL. Since there is no obvious way of interacting with the game it is designed
to be used from within Query Analyzer.
You can
download the SQL from the following address:
http://www.red-gate.com/downloads/CreateTheMazeGame.txt
Instructions
on how to play:
First run:
EXEC
spNewGame
This
creates a new game. You can then move around the maze using
EXEC
spFowards -- Move you forwards
EXEC
spBackwards -- Move you backwards
EXEC spLeft -- Rotates you Left
EXEC
spRight -- Rotates you Right
All of
these stored procedures return a single column results set that shows the view
from you current position in the maze rendered in text. It works best if you
have Query Analyser in view results in text mode and make sure your results are
displayed in a fixed with font! Unfortunately since the maze is all drawn in
text it takes a little bit of time to work out what is going on. When you run spNewGame
for the first time you will find yourself looking down a corridor with much the
same view you got in the old 3D games like Wolfenstein 3D. So what you are
seeing is a corridor stretching out in front of you and there is a wall at the
end. You should also note that there is
a slight ‘Fish Eye’ Effect as well so if the walls will look a little curved
when you look at them straight on (The reason for this is that I am using a
rather large Field of View which make it easy to see what is going on). The goal of the game is to find the exit. You
can tell where the exit is as the wall texture changes to the letter ‘E’ rather
than ‘-‘ or ‘|’. When you reach the exit you will be given a little message to
tell you that you have finished the maze.
It is very
easy to add levels. The levels themselves are just an Varchar strings. If you
look at the example below The letter ‘S’ is where you start, ‘E’ is where you
end, ‘1’ is a normal wall, ‘2’ is the ‘E’ wall texture use to show where the
exit is an 0 is open space. You will also need to specify the width and height
of your map. The example below adds a
new maze with the name ‘Long’
INSERT INTO
Levels (LevelName, Maze, MazeWidth, MazeHeight)
SELECT 'Long',
'1111111222' +
'1S100012E2' +
'1010101101' +
'1010101101' +
'1010101101' +
'1010101101' +
'1010101101' +
'1010101101' +
'1000100001' +
'1111111111',
10, 10
Will add a
new level called long. To try this level run:
EXEC
spNewGame 'Long'
I hope you
have fun with this very silly piece of code and if you feel the need to make it
better or even make the game fun to play please feel free to use the code in
anyway you like.
Lionel
|
-
Posted Friday, December 16, 2005 11:35 AM |
|
/*
Sorry for the delay putting up this puzzle but I was going to
post a different puzzle for this week but failed to get a solution
for it :)
When queuing for our food at the Christmas party, I noticed that several of us
had already overdone the alcoholic drinks. Whilst we waited, I wondered how
many drunk people were standing next to each other, and what was the longest
unbroken row of these inebriated people in the queue.
This, then is the puzzle. Below is a table that represents a queue of people. The
value in the Position field represents their position in the queue. The FirstName
Field holds the first name of the person. The IsDrunk
Field is 1 if the person is drunk and 0 otherwise. Using the table below,
construct a single query without using variables, other tables and ddl
that returns single rows resultset which holds the most number of drunk
people standing adjacent in a row within the queue. For the example data below
the results should be 4, as Simon, James, Tom and Lionel are standing next to each
other, drunk, in the queue.
Largest Number Of Drunk People In A Row
---------------------------------------
4
*/
SET NOCOUNT ON
DECLARE @Queue TABLE
(
Position INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
IsDrunk INT NOT NULL -- 0 is not Drunk 1 is drunk
)
INSERT INTO @Queue(Position, FirstName, IsDrunk)
SELECT 1, 'Nick', 1
INSERT INTO @Queue(Position, FirstName, IsDrunk)
SELECT 2, 'John', 0
INSERT INTO @Queue(Position, FirstName, IsDrunk)
SELECT 3, 'Simon', 1
INSERT INTO @Queue(Position, FirstName, IsDrunk)
SELECT 4, 'James', 1
INSERT INTO @Queue(Position, FirstName, IsDrunk)
SELECT 5, 'Tom', 1
INSERT INTO @Queue(Position, FirstName, IsDrunk)
SELECT 6, 'Lionel', 1
INSERT INTO @Queue(Position, FirstName, IsDrunk)
SELECT 7, 'Neil', 0
INSERT INTO @Queue(Position, FirstName, IsDrunk)
SELECT 8, 'Andras', 1
INSERT INTO @Queue(Position, FirstName, IsDrunk)
SELECT 9, 'Richard', 1
INSERT INTO @Queue(Position, FirstName, IsDrunk)
SELECT 10, 'Helen', 0
SET NOCOUNT OFF
|
-
Posted Tuesday, November 22, 2005 3:50 PM |
|
/*
This puzzle is simple. Given the table below you have to output the
words of the famous Christmas carol using a single select statement.
Each line must be a separate row of the results set. I am also adding
the restriction that you can not use a union statement. This is to stop
cheating such as having a select statement for each line. The
output should be as follows (A space between each verse would be good).
EDIT Sorry missed the table creation code of the first post DOH!!.
On the first day of Christmas,
my true love sent to me
A partridge in a pear tree.
On the second day of Christmas,
my true love sent to me
Two turtle doves,
And a partridge in a pear tree.
On the third day of Christmas,
my true love sent to me
Three French hens,
Two turtle doves,
And a partridge in a pear tree.
On the fourth day of Christmas,
my true love sent to me
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.
On the fifth day of Christmas,
my true love sent to me
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.
On the sixth day of Christmas,
my true love sent to me
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.
On the seventh day of Christmas,
my true love sent to me
Seven swans a-swimming,
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.
On the eighth day of Christmas,
my true love sent to me
Eight maids a-milking,
Seven swans a-swimming,
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.
On the ninth day of Christmas,
my true love sent to me
Nine ladies dancing,
Eight maids a-milking,
Seven swans a-swimming,
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.
On the tenth day of Christmas,
my true love sent to me
Ten lords a-leaping,
Nine ladies dancing,
Eight maids a-milking,
Seven swans a-swimming,
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.
On the eleventh day of Christmas,
my true love sent to me
Eleven pipers piping,
Ten lords a-leaping,
Nine ladies dancing,
Eight maids a-milking,
Seven swans a-swimming,
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.
On the twelfth day of Christmas,
my true love sent to me
Twelve drummers drumming,
Eleven pipers piping,
Ten lords a-leaping,
Nine ladies dancing,
Eight maids a-milking,
Seven swans a-swimming,
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree!
(114 row(s) affected)
*/
SET NOCOUNT ON
DECLARE @Presents TABLE
(
InitialDayRecieved VARCHAR(50),
Present VARCHAR(50)
)
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'first', 'A partridge in a pear tree'
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'second', 'Two turtle doves'
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'third', 'Three French hens'
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'fourth', 'Four calling birds'
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'fifth', 'Five golden rings'
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'sixth', 'Six geese a-laying'
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'seventh', 'Seven swans a-swimming'
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'eighth', 'Eight maids a-milking'
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'ninth', 'Nine ladies dancing'
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'tenth', 'Ten lords a-leaping'
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'eleventh', 'Eleven pipers piping'
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'twelfth', 'Twelve drummers drumming'
SET NOCOUNT OFF
|
-
Posted Friday, November 04, 2005 4:46 PM |
|
/*
Todays puzzle comes from Andras who incudentally got the best
solution so far to the last puzzle.
We have two tables a letters table and a numbers table. The letters table
is a letter followed by three numbers. The idea is to write a query that
returns all the letters that have all numbers listed in the numbers table.
This is probably best explaind in an example. so the Letters table contains
a,1,2,3
a,4,5,6
a,4,5,6
b,1,2,3
c,1,2,3
c,4,5,3
d,1,9,4
d,2,8,5
d,3,7,6
d,4,6,7
e,1,2,3
e,4,5,6
e,4,5,6
e,4,6,7
The numbers table contains.
1,2,3
4,5,6
4,5,6
So for a letter to be selected it must have three entries in the letters table
One with 1,2,3. A second with 4,5,6. A third with 4,5,6. So if there is a
duplicate in the numbers table there must be a duplicate in the letters table.
In the example above the result will be a and e. A is returned as it has
exactally the three numbers following it that are in the numbers table. E is
also returned as it also has all of the entries in the numbers table and it
doesn't matter if there are extra ones.
The expected result from the data bellow should be:
Letter
------
a
e
Happy coding :)
*/
SET NOCOUNT ON
DECLARE @Letters TABLE
(
Letter Char(1) NOT NULL,
NumberA INT NOT NULL,
NumberB INT NOT NULL,
NumberC INT NOT NULL
)
DECLARE @Numbers TABLE
(
NumberA INT NOT NULL,
NumberB INT NOT NULL,
NumberC INT NOT NULL
)
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'a', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'a', 2, 3, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'a', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'a', 1, 3, 4
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'b', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'b', 2, 3, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'c', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'c', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'c', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'c', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'c', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'd', 6, 5, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'd', 3, 2, 4
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'd', 7, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'd', 1, 4, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'd', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'e', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'e', 2, 3, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'e', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'f', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'f', 2, 3, 8
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'f', 7, 3, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'f', 1, 2, 3
INSERT INTO @Numbers(NumberA, NumberB, NumberC)
SELECT 1, 2, 3
INSERT INTO @Numbers(NumberA, NumberB, NumberC)
SELECT 1, 2, 3
INSERT INTO @Numbers(NumberA, NumberB, NumberC)
SELECT 2, 3, 3
SET NOCOUNT OFF
|
-
Posted Friday, October 28, 2005 2:10 PM |
|
/*
To try and help everyone get a little better at sql I am setting a
little puzzle for you all to do based on a problem that Richard had
with the check for updates site.
The puzzle is simple. We have a version table that holds all the version
of all our products that we release. The version number is represented
with four numbers a,b,c and d where a is the major version and the other
letters represent which sub version it is. So the puzzle is to write a
query to find the latest build for each major version. So in result from
the data bellow should be.
a b c d
----------- ----------- ----------- -----------
1 2 2 4
2 3 1 7
3 2 1 5
You are only alowed to use one SQL statment and definantly no cursers. Also
no solutions that use any strings!!!!!. ALSO a, b, c or d could all potentally be
Int32.MaxValue so make sure your solutions don't overflow!!!!!!!
Email solutions to me.
Here is some example data to get you started
*/
DECLARE @Version TABLE
(
a int,
b int,
c int,
d int
)
INSERT INTO @Version(a, b, c, d)
SELECT 1,1,1,23
INSERT INTO @Version(a, b, c, d)
SELECT 1,2,1,1
INSERT INTO @Version(a, b, c, d)
SELECT 1,2,2,4
INSERT INTO @Version(a, b, c, d)
SELECT 2,1,1,1
INSERT INTO @Version(a, b, c, d)
SELECT 3,1,1,2
INSERT INTO @Version(a, b, c, d)
SELECT 2,1,2,8
INSERT INTO @Version(a, b, c, d)
SELECT 2,1,3,4
INSERT INTO @Version(a, b, c, d)
SELECT 2,2,1,2
INSERT INTO @Version(a, b, c, d)
SELECT 2,2,3,1
INSERT INTO @Version(a, b, c, d)
SELECT 2,2,4,6
INSERT INTO @Version(a, b, c, d)
SELECT 2,3,1,7
INSERT INTO @Version(a, b, c, d)
SELECT 3,2,1,5
|
|
|