Looking for SQL Optimization Interview Questions

I'm on a project where I was asked to take a quick peek at some reporting SQL (in a SQL Server 2K5 environment) and was surprised at what I found: 4 to 5 levels of subquerys, distinct clauses, unions, and NoLock hints (which were needed because the SQL was running so long it was blocking standard processing) - all in the same set!.

Because I (foolishly :) mentioned that I thought the SQL was 开发者_如何学运维inefficient I've been labeled the "expert" and have been tasked with creating a test for a couple of interviewees to do that will assess their SQL optimizing abilities. I'm hoping someone can point me to some URLS, or maybe provide a list that I can use to help weed out the good from the bad.

Since you mentioned the SQL Server 2005 environment:

More SQL Server interview questions than you possibly could have imagined:

The classic set. Most interviewees will probably have studied these...maybe a good way to gauge who has prepared.

Another classic

Questions from one of the original Stack Overflow DBAs

Another link for best SQL questions and answers

I would give them a Query plan (via EXPLAIN, or whatever your flavor of SQL uses as a keyword) and see if they can decipher what it means, what the weak points are, and how to improve the query.

Look at MySQL's Explain Documentation for help using MySQL's explain and what it means.