Top SQL Queries for Interview

In this article, I will take you through the most common SQL queries asked in interviews with answers.

1. Nth Highest Salary

Get the 3rd highest salary from employee table.

Dense_Rank():Returns rank of each row within a result set partition, with no gaps in resulting values.

You can use subqueries to get the same result.

2. Find Duplicate records

Find duplicate records in a table using a unique identifier. Here I am using Firstname as an identifier but you can use any other based on the table info.

3. Print numbers from 1 to 100 without using the loop

4. Generate Running total

Given products and order details, we have to generate running total by each product. (example: running total of 3rd row will be the sum of first 3 rows)

a. Write a subquery to generate the total amount of each product by grouping the product name.

b. Calculate running total from the above query.

5. Query to generate unique combinations

Consider a scenario you have 5 teams and we have to plan a league tournament between the teams such that all the team pairs should be unique. I will be using temporary tables and inner joins to achieve the result.

a. Create a temporary table with unique teams

Below is the result set for the above query !!

Teams

b. Generate all possible combinations with the above result.

Below is the result for the above query.

All combinations

Here we could see all possible combinations and which are duplicate like ( India, Australia; Australia, India)

c. Let's generate only the unique combinations

We can see that if fromid and toid matches, it represents the same combination. There are two types of combinations that are possible either fromId will be greater than toid or toid will be greater than fromid . Let us fetch only one of the combination.

Final result

Hope you enjoyed it !!! Please do comment if you have any queries !!

Share if you faced any toughest/complex queries, I will try to solve them and append here.