Tuesday, September 05, 2006

Questions for database geeks

After 3 years of using DB2 on Linux, I'm leaving my current employers to go work for a SQL Server shop instead. In order to find my replacement, they're trying to put together a set of questions to get both some understanding of how wide candidates knowledge is, and how much DB2 specifics they know. Of the questions below, how many do you think are useful in determining if you've got somebody capable of keeping a DB2 instance up, developing new structures as new business problems arrive, and writing useful/performant SQL? (these therefore range from the inane to quite specific - how many do people feel are actually unfair? or should I just get some questions from Celko?)
[I'm not trawling for a job or for other recruitment purposes here, honest...]
1. I've got a bookings table with 20 million rows, broken down by product, region, date and customer number. It's not loading new data or querying very quickly. What should I do?
2. Do you like stored procedures?
3. What's an inline view?
4. How do you do a running sum when GROUP BY just gives you a total per row?
5. Do you prefer star schemas or snowflake schemas? Why?
6. I have a table with three columns: date (date), country (varchar(10)), bookings (int).
Write me a query that gives me a total by country of bookings made in the last 2 weeks, AND that displays the total number of bookings made in the last 2 weeks too.
7. Do you know what an MQT is?
8. If all my customers have been given x-y coordinates that describe the position of their house (ie every line in customer has x_coordinate (int) and y_coordinate (int), and Birmingham is at (35000, 55000), and Manchester is at (25000, 65000) and York is at (33000, 70000), then, assuming Euclidean geometry, give me a WHERE clause against the customer table to return only customers who live north of Birmingham and south of a line that passes through both York and Manchester.
9. I have two sets of customers with the same 15 columns. What's the best way to return only those customers that are in both sets, for whom the data is the same in both sets?
10. Give me a way to get the database to check on a daily basis if something surprising is happening to sales when compared with last year at the same time.
11. DB2 UDB LUW 8.1.4. For the next six months, ten new tables need to be created every day and automated routines set up to load them every day after they're created. You are the only personin the company responsible for administering the database. Do you use a DMS or SMS tablespace? Why?
12. Your manager has written some code that 'should take 5 minutes to run'. It takes 4 hours. What do you do?
13. Cube Views?
14. What SQL that you've written are you most proud of?
15. What are the disadvantages of HADR vis a vis RAC?
16. SQL Server federation vs DB2 Information Integrator - any thoughts?


Post a Comment