Here we go! It’s the first challenge of the contest! http://www.microsoft.com/learning/en/us/certification/bethenext.aspx 

Well, technically that’s not true…

We brought the contestants out to Seattle and put them up in a really nice house on the shoreline, with a view of Mount Rainier. Just getting anything done in that setting is a challenge all by itself. We gave them each a new laptop to use (the kind you can win as well – more on that in a bit). On that laptop we loaded Microsoft Windows 7, Microsoft Office (with Visio – that becomes important later) and Microsoft SQL Server 2012 Developer Edition with all of the defaults (that becomes important later – are you sensing a theme?) – but before we gave them the laptops, I gave the contestants a “pre” challenge.

 As the contestants arrived, I called a phone and had them gather around it. Without letting them know who I was, and in my best “Charlie’s Angel” voice, I gave them a puzzle – they were to install a sample database from a version of SQL Server that came out the same year that Charlize Theron played the lead role of an action cartoon from MTV, one that dealt with bicycles.

 

Think you know which database that is? How about the year of the database? Why choose that one over something more recent? (That becomes important later on.)

 

Challenge Background

Laptops in hand, the contestants reported to the Microsoft campus. Specifically, to the “Commons”, a sort of mall, shopping center, food court, and meeting area. We introduced them to the judges, which included myself, Tim DiMarco from HR, and my friend  Tobias Ternstrom. Tobias is a Lead Program Manager here at Microsoft, and knows a thing or two about Transact-SQL. You can read about him here: http://borntolearn.mslearn.net/btl/b/bethenext/archive/2012/07/23/guest-judge-tobias-ternstr-246-m-fast-as-lightening-and-just-as-frightening.aspx

Meeting in the bicycle shop in the commons, we provided each challenger with a set of books as a gift from Microsoft press – which Tobias was involved with – and then Tobias gave them a USB drive. 

He gave them this challenge:  “Find all products provided by more than one vendor.” He then turned and left.

The challengers were a bit surprised – no more detail than that? Which database? What was on the USB? We gave them no more details, and sent them to a public area to work. A Jazz band played (loudly) as they tried to accomplish the task in 60 minutes with no more information than that.

The USB contained a zip file, and in that file I placed a single database – which was comprised of a single table from AdventureWorks 2005. That single table on the USB was a copy of the Purchasing.ProductVendor table.

Using that USB (as they were instructed), they should have created a self-join – it’s one of the ways to solve the puzzle and one you can quickly find. Of course, to even get that far, you had to mount the USB, open the ZIP, discover the database, restore it, and explore the meta-data graphically or in code. Once that’s done, this is the code you could run to get the answer:

-- Solution

USE PurchaseReports; -- I renamed the table to this

GO

SELECT DISTINCT pv1.ProductID, pv1.VendorID

FROM Purchasing.ProductVendor pv1

    INNER JOIN Purchasing.ProductVendor pv2

    ON pv1.ProductID = pv2.ProductID

        AND pv1.VendorID <> pv2.VendorID

ORDER BY pv1.ProductID

Simple, right? Well, if you aren’t given much information, even the database or the table name, maybe not as simple as you think. However, I thought the contestants would figure it out quickly – because I had tipped them off about that database in the pre-challenge. Remember the year of the database, and the database name? Using that information – and the fact that we were standing in a bicycle shop, and that only one table was involved, a quick Bing search on “AdventureWorks 2005” “self-join” brings up this link: http://msdn.microsoft.com/en-us/library/ms177490(v=SQL.90).aspx

And there’s the answer.

You see, it’s not just the T-SQL we were after. We were looking to see if the contestants had done a little research on the AdventureWorks database, and since the 2005 is so well documented, most of the answers they would need are there. Peruse this link a while: http://msdn.microsoft.com/en-us/library/ms124501(v=sql.90) You’ll find a wealth of samples, data and so on.

We were also testing the contestants under a time pressure, to see what resources they would use, and could they work with distractions. No, we don’t often work in rooms with a Jazz Band blaring in the background, but we do have to work in adverse conditions and be able to focus on the job. And we never have enough time – probably like where you work.

Challenge Recap

So how did they do? Well…

 One contestant focused on the time pressure and finished extremely early – but got the wrong answer. Another contestant didn’t apply all the patches to their machine the night before, and the system did it for them (oops) and rebooted in the middle of the work. Another contestant used the books we gave as a resource, and came up with an extremely complicated solution – which ironically contained the right answer within it, but wouldn’t compile.

The winning contestant got the answer. He did end up joining the AdventureWorks database to the single-table database we provided, but he got the right answer. He won the day.

The stress was the interesting factor. Although you get to see the answer – “Hey, that’s easy!” you think to yourself – it’s not as obvious as it seems, just like the real day-to-day job we’re testing the contestants on. Tim (the HR part of the team) asked an interesting question to one of the candidates. He said “What would you do back at your job if you were presented with this?” The contestant laughed and said “I’d call Bob.” We asked “Why didn’t you call Bob?” Stunned silence. “We can do that?” “Sure – never said you couldn’t.”

Tim was checking to see how the contestant worked. Did they go it alone, seek help, check the web, check their sources, check their work? Was their re-use, such as putting the answer into a View or Stored Procedure?

Your Challenge

OK – now you get a shot at that laptop – and some free ebooks! Terms and conditions are below, but here’s what we want you to do:

Come up with a better challenge than this one. Tell us the setup, the answer, and how it tests each contestant in the following areas:

  • Working with ambiguity
  • Working in a distracting environment
  • Using not only T-SQL skills, but also DBA skills like restoring a database, creating one, setting up a table or something along those lines
  • Using all your resources – even ones you might have in your past – and determining how the contestant uses those resources

Keep in mind, your challenge has to be more about what it is testing than in the difficulty. Anyone can find some T-SQL that nobody can figure out – that’s not the point. The challenge should be hard, but should be solvable, and in 60 minutes. And it has to be a challenge that we could actually set up – no DBA on the moon kind of thing!

Send your set up, answers, and an explanation of how it measures a candidate on the bullets above to this e-mail address: bethenext@microsoft.com (Terms and Conditions are here: http://borntolearn.mslearn.net/btl/b/bethenext/archive/2012/06/10/play-at-home-contest-terms-and-conditions.aspx)

We’ll read through them all and award the ebooks. You’ll then be placed in the larger contest for the laptop. Sounds good? Go!