Tech Qu: Some SQL Server Questions

The Idiot ones

I have gathered a few from some interview sites, and the ones below are so trivial that it’s shocking that they were asked!

 

WriteTSSL to find all the duplicate email address in a table which contains only one column "email"

Code Snippet

1. select [email]
2. from [sometable]
3. where [email] is not null OR len([email])=0
4. group by [email]
5. having count([email]) > 1

 

Many answers failed to exclude no email (could be recorded as null or an empty string), or return the count with it (which was not asked for).

 

Given a table: CustomerOrders: (Customer ID | Order ID | Order Date)

1. Write a SQL to find all customers who has placed an order today.

2. Write a SQL to find all customers who has placed an order today AND yesterday.

Again, a trivial one

Code Snippet

1. Select CustomerId from CustomerOrders
2. Where OrderDate >= Cast(GetDate as Date)
3. Select CustomerId from CustomerOrders
4. Where OrderDate >= Cast(DateAdd(dd,-1, GetDate()) as Date)

 

“Write an SQL query to select the nth row from a table. I asked the interviewer that, can I assume say the nth record is based on a primary/unique key column or any column, he said no, the question is just select the nth row.”

The interviewer is ignorant (that is the polite way to say it!). Unless we are talking the nth row according to a clustered index on the table, it’s as meaningful as asking for the nth byte in memory of the OS. The order of rows in a table without a clustered index is volatile. Two queries 1 second apart of the same code may give different results. Never the less, a simple implementation is just:

Code Snippet

1. SELECT TOP 21 * FROM SalesLt.Product
2. EXCEPT
3. SELECT TOP 20 * FROM SalesLt.Product

However, there is a possibility of more than 1 row being returned…

Retrieve second highest salary and the corresponding Employee Id from a Employee_Salary table.

Many ways of doing this one -- with many of them failing on boundary conditions. The following simple solution is suggested.

Code Snippet

1. SELECT TOP 2 Employee_ID,Salary FROM Employee order by salary desc
2. EXCEPT
3. SELECT TOP 1 Employee_ID,Salary FROM Employee order by salary desc

 

A boundary condition that could happen is two employees with the highest salary,  many solutions that I have seen will actually give the 3rd highest salary in this case! Oooopps. The above code will not. .

A table has only one column. Retrieve all the data which are different from their previous data.

Another idiot question – likely a C# developer trying to cast a C# question (which it bears a striking resemblance to) into a database context and literally blowing it. In RDBMS theory, there is NO NATURAL ROW ORDER (something that some C++ and C# and Java types just do not comprehend!).

 

Well, here’s a solution:  Put it into a working table adding an identity column and then just compare to prior record. The very first row is always included (it is different than no row!) which is a boundary condition that may be missed. Again, two executions of this query may return different results.

 

Code Snippet

1. Declare @workingspace table(id int Identity(1,1), data varchar(max))
2. INSERT INTO @workingspace (data)
3.     SELECT LastName from SalesLt.Customer
4. Select top 1 data from @Workingspace
5. UNION
6. Select A.data from @workingspace A
7. JOIN @workingspace B ON A.ID=B.Id-1

 

Any questions that deal with difference in versions of SQL Server are IMHO, bad questions. You are not evaluating their ability/skills to do the job, rather a quasi-rote factor.

 

FYI: Some questioners excludes the use of TOP (not standard sql), this makes the question unrealistic and thus inappropriate. If TOP is excluded then use the above pattern to number the rows and run with it.

 

Some better Basic questions

• Give some code that uses cursors and ask for it to be rewritten without using cursors
• Explain the difference between using:
  • Create Table #temp
  • Declare @Temp Table
• Give some XML and ask them to perform an upsert of a SQL Table from it.
• Ask them to give an example of CROSS APPLY. Or given them a question where cross apply is the easiest solution.

For some good questions (31 days of them!)

See http://blog.sqlauthority.com/2011/07/01/sql-server-interview-questions-and-answers-frequently-asked-questions-complete-downloadable-list-day-0-of-31/