During my school days, I used to play this game that I would call as "word mutation". The problem statement gives the starting and ending 4-letter words.
The game is to find the path from the starting to the ending word by changing one alphabet at a time and ,of course, each of the words in the path should be a valid word.
I guess, by now, most of you are familiar with the SQL Server 2005 sample database - AdventureWorks. We will use one of the tables from this database to create our scenario - Production.BillOfMaterials. To simplify our requirements, we will be selecting only those records in this table, that has the EndDate as NULL.
Lots of things happened since the last post I made in this blog. I got married. Didn't do much justice to my blog followers since then. My wife couldn't stand me being interested in ONLY her :) So, she pushed me to get this blog started again and I hope she keeps pushing me to continue this blog.
Now, Here is the scenario.
I had been playing Chicktionary (google it if you had not heard of it) in Club Bing. There are some other versions of the same game - Word Warp, Text Twist, Jumble, etc. In a gist, its a game where you are given a chain of letters and you need to come up with as many words as you can with these letters in a given time. It's a pretty addictive game.
Last February, I had come over to Atlanta, Georgia from India for a new project and had been quite overwhelmed by the new way of life, the work and the country that I wasn't able to do justice to this blog. I apologize for those who had been following this mini series of scenarios. Hope I can get some time now to revive this.
Here goes the scenario:
Its been a long time since I had used this medium of communication. Without getting into the excuses part of the dormancy I will get on with the question...
Here is the scenario. Say, I am in a business consulting company and I am given a table EmployeeCustomerOrders.
The table can be generated from the orders table in the Northwind database using this query.
This time it's a pretty interesting scenario to solve. I have a table called the distance_tbl holding distance between different cities.
Here is the table definition:
create table #distance_tbl (city1 char(1), city2 char(1), distance int)
And the sample data:
Say,I have a requirement. My business demands an auto-generated key (or an identity) for a table called CharTbl. But, it needs to be a character identity column. The sequence generation should be as given in the figure below (partial result displayed).
How would I go about creating the table definition?
This scenario is a slightly modifed (simplified) version of a question posted in a discussion forum.
Lets say I have a SQL Server 2000 box. And I have the following table.
create table #runningavg(id int identity(1,1), curval decimal(16,10))
And the following data:
This time its an interesting question. You will have to come up with a seemingly impossible query, which is pretty simple to implement in SQL Server 2005 :)
Lets say I have a table like this in a SQL Server 2000 box:
create table #answers (val1 char(1), val2 char(1))
And the following rows
A totally hypothetical and insane requirement, incontestably prone to excessive criticism (to put it mildly) from Joe Celko, if he ever happens to visit my blog :)
But, lets just assume that the requirement is warranted and we need a solution in SQL Server 2000.
I have a table with this definition.
CREATE TABLE BAD_ENCRYPTION(VAL_ID INT, VALUE VARCHAR(100))
Here is a question which I find to be pretty interesting. Can anyone come-up with a query or a batch which, when executed, will print itself? I would like to see the lengthiest batch anyone can come up with. The solution can be in SQL Server 2000 or 2005. For example, here is a no-brainer solution that works for SQL Server 2005
SELECT (select text from sys.dm_exec_sql_text(sql_handle))
FROM sys.dm_exec_requests
WHERE session_id = @@spid
This should be quite simple. But, I would like to find the best approach to this problem.
The question is to get the binary values for positive integers.
Lets take this table called numbers that has one column called id holding positive integer values. Now, I need to write a query (or set of queries) to select from the table "numbers" and return me the id and the binary value of that id.
This scenario doesn't (and hopefully shouldn't) relate to any requirements, you will ever see, to be implemented at the database tier. But just for the fun of solving it, I am putting it here.
Please have a look at the image. Its a sine curve, that is generated from a query selecting 2 columns (both computed, may be from number table). One column gives the angle in degrees and the other column is the corresponding sine value, graphically represented.
Now this is pretty similar to the previous scenario (#2) , but a bit more interesting, I hope.
Here is the question.
I have a table maxnum with the following data:
create table maxnum (a int, b int, c int)
insert into maxnum values (1,2,3)
insert into maxnum values (4,3,5)
insert into maxnum values (23,44,23)
insert into maxnum values (23,45,45)
Now I need to get the result with 4 columns. a,b,c and the intermediate (neither max nor min) value of a,b,c. So, the result will be this way:
a b c intermed
1 2 3 2
4 3 5 4
23 44 23 23
23 45 45 45
How do I get the result?
Take it, that all three values (a,b,c) in the row cannot be the same.
Unlike the previous scenario, I am not tying this up with any SQL Server version. Here is the question.
I have a table maxnum with the following data:
create table maxnum (a int, b int, c int)
insert into maxnum values (1,2,3)
insert into maxnum values (4,3,5)
insert into maxnum values (23,44,23)
insert into maxnum values (23,45,45)
Now I need to get the result with 4 columns. a,b,c and the max value of a,b,c. So, the result will be this way:
For all those using SQL Server 2005, here is a scenario, see if you can come up with a solution:
I thought I will check out the different implementations possible and find the best out of it.
Say you have a table:
Customers (CustomerID int primary key, CustomerName varchar(50))
A pretty simple table structure. And it has 1000 rows.
Now, I am conducting a contest for the customers where I will randomly pick up 5 to 20 customers every week and give away prizes.
How will I go about doing it?
I need to create a stored procedure/query/function that will accept no parameters but will return random list of customers and random number of customers (between 5 and 20)
Posts
All Comments
