Answering a SQL Query Question from a RecruiterBy Bill Graziano on 28 March 2001 | Tags: Queries Earlier we published a question about a SQL query that a recruiter asked a job candidate. I presented a couple of different options but didn't really think it could be done . . . until Alexander Netrebchenko sent me the solution. Here's his email and the script . . . and a job if I had one to give him :)
You can download his script and take a look at it. His email follows.
Dear graz, Here is one possible solution for the problem described in the article 'SQL Query asked by a recruiter'. It seemed challenging, so I decided to put some time and brain energy on it. Basically, the solution is based on the "second easiest way", though the task is accomplished in single SELECT without any tables and/or stored procedures. The idea behind this is quite simple:
The trick is how to generate the sequence of dates in SELECT statement. To accomplish this, I first generate the sequence of integers from 0 to N using Cartesian product (see the source code), and then use DATEADD function to generate dates. The source is the complete example on how to do this. Well, the query looks pretty cumbersome, but I hope the recruiter would be happy with that... :) Last point. The source may not be fully optimized (well, it's just a funny homework), so I would be just happy if you or your readers would recommend some improvements there. Sincerely, Alexander Netrebchenko. Wow! One of the tricks that Alexander used is called derived tables. You can replace a table name in a SELECT query with another complete SELECT statement. An example from Books Online looks like this: SELECT ST.stor_id, ST.stor_name FROM stores AS ST, (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count FROM sales GROUP BY stor_id ) AS SA WHERE ST.stor_id = SA.stor_id Alexander used a derived table to build a "table" of possible dates on the fly. We recently linked to this article about derived tables. Neat stuff. He also used a cartesian product or CROSS JOIN to generate those dates. In this SELECT statement SELECT au_fname, au_lname, pub_name FROM authors CROSS JOIN publishers ORDER BY au_lname DESC it will return each all the publishers joined to the first author, then all the publishers joined to the second author, etc. This is a great way to generate test data in a hurry. Thanks Alexander, for the great script and solution and letting me publish it. |
- Advertisement - |