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
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">Steve< SPAN>employee>
<employee num="2">Al< SPAN>employee>
<employee num="3">Mike< SPAN>employee>
<employee num="4">Malisha< SPAN>employee>
< SPAN>Employees>
< SPAN>office>
<office id="Cairo">
<employee num="1">Remon< SPAN>employee>
<employee num="2">Lamees< SPAN>employee>
<employee num="3">Bassma< SPAN>employee>
<office id="Pune">
<employee num="1">Vinay< SPAN>employee>
<employee num="2">Pradeep< SPAN>employee>
<employee num="3">Rashmi< SPAN>employee>
<employee num="4">Sujata< SPAN>employee>
<employee num="5">Ajit< SPAN>employee>
<office id="Karachi">
<employee num="1">Amrat< SPAN>employee>
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:
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
288
35006.4385
2
Canada
285
2411105.152
278
755593.2997
268
15655.2241
3
France
286
1832662.467
284
2278.3269
Germany
289
1121896.781
74363.4408
United Kingdom
282
1648152.541
54036.9765
United States
276
2534645.3
275
2053782.757
277
1824701.882
279
1446386.797
4
281
1429353.893
5
287
1114278.877
6
283
901142.2512
280
871513.6294
8
318200.2683
9
Enjoy!
Page rendered at Tuesday, August 9, 2022 12:50:06 AM (Eastern Daylight Time, UTC-04:00)
Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.