SQL Advanced Interview Questions

 

Question: How to get accurate age of an employee using SQL?

Answer: The word accurate is crucial here. The short answer is you have to play with several functions. For more comprehensive answer see the following link SQL Age Function. Calculate accurate age using SQL Server

Question: This is SQL Server interview question. You have three fields ID, Date and Total. Your table contains multiple rows for the same day which is valid data however for reporting purpose you need to show only one row per day. The row with the highest ID per day should be returned the rest should be hidden from users (not returned).
To better picture the question below is sample data and sample output:
ID, Date, Total
1, 2011-12-22, 50
2, 2011-12-22, 150

The correct result is:
2, 2012-12-22, 150
The correct output is single row for 2011-12-22 date and this row was chosen because it has the highest ID (2>1)

Answer: Usually Group By and aggregate function are used (MAX/MIN) but in this case that will not work (unless you use joins as Anton mentioned in his comment, although from readability and potentially performance point of view I would prefer not to do that.)
Removing duplications with this kind of rules is not so easy however SQL Server provides ranking functions and the candidate can use dense_rank function partition by Date and order by id (desc) and then use cte/from query and filter it using rank = 1. There are several other ways to solve that but I found this way to be most efficient and simple.

Question: How to return truly random data from a table? Let say top 100 random rows?
I must admit I didn't answer correctly this sql interview question a few years back.

Answer: Again this is more SQL Server answer and you can do that using new_id() function in order by clause and using top 100 in select. There is also table sample function but it is not truly random as it operates on pages not rows and it might not also return the number of rows you wanted.

Question: How to create recursive query in SQL Server?

Answer: The first question is actually what is a recursive query? The most common example is parent child hierarchy for instance employee hierarchy where employee can have only one manager and manager can have none or many employees reporting to it. Recursive query can be create in sql using stored procedure but you can also use CTE (Common table expression) for more information visit SQL Interview question - recursive query (microsoft). It might be also worth asking about performance as CTE is not always very fast but in this case I don't know which one is would perform betters.