One of my favorite technical interview question on SQL Server is “What query would you write to find the second highest column value in a table?” The question seems easy, however most developers fail to answer to this. If we had a table named Employee which had a column named Salary and we had to find the second highest Salary in the Employee table, the query for the same would be:

SELECT TOP 1 Salary FROM (SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) AS E ORDER BY Salary ASC

Here’s a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:

SELECT email,
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

You could also use this technique to find rows that occur exactly once:

A Visual Explanation of SQL Joins

I thought Ligaya Turmelle’s post on SQL joins was a great primer for novice developers. Since SQL joins appear to be set-based, the use of Venn diagrams to explain them seems, at first blush, to be a natural fit. However, like the commenters
to her post, I found that the Venn diagrams didn’t quite match the
SQL join syntax

reality in my testing.

I love the concept, though, so let’s see if we can make it work. Assume we have the following two tables. Table A is on the left, and
Table B is on the right. We’ll populate them with four records each.