# Tuesday, December 12, 2006

We are hiring a DBA at my company Corzen and instead of looking at resumes/cvs we are looking at how people solve particular SQL problems-not just the solution but the approach they take. (Do you use temp tables, (nolock), CTEs, table alias, etc.)

Here are the five test questions we ask. (If you want to come work here, just email me the answers.)

Here are three TSQL problems that can judge how you approach a SQL problem. There are no real wrong answers, but the more efficient and bulletproof the query is the better. No cheating and no cursors! Don't cheat and don't google the answers (we'll know.)

 

Step 1: run SetupTSQLProblems.sql in any database.

 

Problem 1.

Finding Duplicate Products in the Product table. Write a query to list only the duplicated products in the Products table. Assume that the dupe is the product with the highest Product_ID. The results of your SQL statement should be:

 

Dupe_Product_ID

Dupe_Product_Name

Dupe_Price

Parent_Product_ID

Parent_Product_Name

7

Widgets

88

1

Widgets

 

 

Problem 2.

Assign rooms to classes based on capacity using the Classes and Rooms tables. Rules: each class should have a room (and NULL if a room is not available). No class can be in a room where there are more students than capacity. No room can be used twice.

 

The results of your SQL statement should be:

class

students

room

capacity

Advanced SQL Queries

65

225

70

Introduction to C#

52

224

60

XQuery Deep Dive

35

222

40

 

 

Problem 3.

Make the following static pivot dynamic in AdventureWorks, assume that the orderyear can increment to 2005 and 2006. Assume that order years can skip.

 

SELECT *

FROM (SELECT CustomerID, YEAR(OrderDate) AS orderyear, TotalDue

      FROM Sales.SalesOrderHeader) as Header

PIVOT(SUM(TotalDue) FOR orderyear IN([2002],[2003],[2004])) as Piv

 

Problem 4.

You have a piece of XML:

<office id="NYC">

  <Employees>

    <employee num="1">Steveemployee>

    <employee num="2">Alemployee>

    <employee num="3">Mikeemployee>

    <employee num="4">Malishaemployee>

  Employees>

office>

<office id="Cairo">

  <Employees>

    <employee num="1">Remonemployee>

    <employee num="2">Lameesemployee>

    <employee num="3">Bassmaemployee>

  Employees>

office>

<office id="Pune">

  <Employees>

    <employee num="1">Vinayemployee>

    <employee num="2">Pradeepemployee>

    <employee num="3">Rashmiemployee>

    <employee num="4">Sujataemployee>

    <employee num="5">Ajitemployee>

  Employees>

office>

<office id="Karachi">

  <Employees>

    <employee num="1">Amratemployee>

  Employees>

office>

 

A. Using the XML datatype, write an XQuery to return the 3rd employee in the NYC office. Your results should look like:

 

EmployeeName

Mike

 

B. Using the XML datatype, write an XQuery to return the 3rd employee in each office. Your results should look like:

EmployeeName

Mike

Bassma

Rashmi

 

Problem 5.

Using adventureworks, rank each salesperson by total sales in 2004 with the rank starting over for each country. Do not use temp tables, self joins, or cursors.

 

CountryName

SalesPersonID

TotalSales

SalesRank

Australia

290

955311.5851

1

Australia

288

35006.4385

2

Canada

285

2411105.152

1

Canada

278

755593.2997

2

Canada

268

15655.2241

3

France

286

1832662.467

1

France

284

2278.3269

2

Germany

289

1121896.781

1

Germany

284

74363.4408

2

United Kingdom

282

1648152.541

1

United Kingdom

284

54036.9765

2

United States

276

2534645.3

1

United States

275

2053782.757

2

United States

277

1824701.882

3

United States

279

1446386.797

4

United States

281

1429353.893

5

United States

287

1114278.877

6

United States

283

901142.2512

7

United States

280

871513.6294

8

United States

268

318200.2683

9

 

Enjoy!

posted on Tuesday, December 12, 2006 7:08:14 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback