Interview questions can be tough. I’ve been grilled on technical and personal skills. When it comes to technical tests all I can recommend is to practice before you go to the interview.
There will be things you don’t know. Don’t panic. Make sure you nail the questions you know then come back to the trickier ones. If you really don’t know the answer try to demonstrate some knowledge and try to show how you would go about solving this problem (without Google!).
This is a technical test I was given recently. I was given 30 minutes to HAND write the answers. It is a pure T-SQL test and in my opinion pretty tricky. I’ve given it to several people now, all have struggled, time being one of the biggest factors.
Feel free to use it as a practice.
The told I would be asked 10 questions, the only advice given was:
“The SQL you write should be as efficient as possible. You should avoid using temporary tables and cursors where possible. If
you can make your answers more generic by the addition of variables, please do so. Make as few assumptions in your
answers as possible (e.g. Assumptions about data types, data volumes, etc).
If you complete all 10 questions inside the allotted 30 minutes, for each question, where would you apply indexes to the
tables in order to make the select operation as efficient as possible? What sort of indexes would you apply?”
1) Return the first name, last name, product name, and sale price for all products sold in the month of July 2008.
2) Return the customer ID, first name and last name of those individuals in the customer table who have made no
purchases to date.
3) Return the first name, last name, sale price, recommended sale price and the difference between the sale price and the recommended sale price, expressed as a percentage, for all sales.
4) Delete the customers from the database who’s only purchases have been prior to 2007.
5) Return the average sales price, by product category
6) Return the product category and the average sales price, for those customers who have returned two or more products.
7) Update the sale price to the recommended sale price for those sales occurring between April 2007 and April 2008.
8) Return the number of sales by product category where the average recommended price is £10 or more greater than the average sales price.
9) Return the sales date and the running total for all sales, ordered by sales date in ascending order.
10) For each product, return the hierarchy of products, starting with the top-most product in the hierarchy and then continuing to all level-1 products in the hierarchy, then level-2 etc. Assume there is an indefinite number of
products to the hierarchy.
Let me know how you do.