Top 10 SQL Interview Queries: Master Your Next Data Interview

Introduction

In today‘s data-driven world, SQL (Structured Query Language) has become an essential skill for anyone aspiring to work with data. Whether you‘re a data analyst, data scientist, or software developer, having a strong grasp of SQL is crucial to succeed in your job interviews. Gone are the days when interviewers focused solely on theoretical knowledge; now, they want to see how you can apply your SQL skills to solve real-world problems.

In this blog post, we‘ll explore the top 10 SQL interview queries that you‘re likely to encounter in your next data interview. We‘ll provide clear explanations, example tables, and best practices to help you master these queries and impress your interviewers. So, let‘s dive in!

1. Finding the nth Highest Salary

One of the most common SQL interview queries is finding the nth highest salary from an employee table. Let‘s consider the following "Employee_Salary" table:

EmpId | NetPay   | Variable
------+----------+----------
121   | 85000    | 2000
321   | 100000   | 5000
421   | 70000    | 3000

To find the second highest salary, you can use two approaches:

a. Using the N-1 Rule

SELECT DISTINCT NetPay
FROM Employee_Salary e1
WHERE 1 = (
  SELECT COUNT(DISTINCT NetPay)
  FROM Employee_Salary e2
  WHERE e2.NetPay > e1.NetPay
);

Explanation: The N-1 rule states that to find the nth highest salary, you need to count the number of distinct salaries greater than the current salary (e1.NetPay) and compare it with N-1. In this case, we‘re finding the second highest salary, so N-1 equals 1.

b. Using the MAX() Aggregate Function

SELECT MAX(NetPay)
FROM Employee_Salary
WHERE NetPay < (
  SELECT MAX(NetPay)
  FROM Employee_Salary
);

Explanation: This approach first finds the highest salary using the MAX() function in the subquery. Then, it finds the maximum salary that is less than the highest salary, effectively giving us the second highest salary.

2. Fetching Records within a Specific Salary Range

Another common SQL interview query is to fetch employee records whose salary falls within a specific range. Let‘s say you need to find employees with a salary between 60,000 and 95,000.

a. Using Comparison Operators

SELECT *
FROM Employee_Salary
WHERE NetPay >= 60000 AND NetPay <= 95000;

b. Using the BETWEEN Operator

SELECT *
FROM Employee_Salary
WHERE NetPay BETWEEN 60000 AND 95000;

Explanation: Both approaches use comparison operators to filter the records based on the specified salary range. The BETWEEN operator provides a more concise way to express the range condition.

3. Filtering Records Based on Name Patterns

In this query, you‘ll learn how to filter employee records based on specific name patterns. Let‘s consider the "Employee_Detail" table:

Row_num | EmpId | FullName      | Designation | DateOfJoining | City
--------+-------+---------------+-------------+---------------+----------
1       | 121   | Rachit Tomar  | Apprentice  | 01/31/2014    | Paris
2       | 321   | Jag Reddy     | Developer   | 01/30/2015    | New Delhi
3       | 421   | Vasa Trisha   | Team Lead   | 27/11/2016    | Chennai

To fetch the details of employees whose names do not start with "V", "R", or "S", you can use the following approaches:

a. Using the LIKE Operator with Wildcards

SELECT *
FROM Employee_Detail
WHERE FullName LIKE ‘[^VRS]%‘;

b. Using the NOT LIKE Operator

SELECT *
FROM Employee_Detail
WHERE FullName NOT LIKE ‘[VRS]%‘;

Explanation: The LIKE operator allows you to match patterns using wildcards. In this case, the [^VRS] pattern matches any character that is not "V", "R", or "S". The NOT LIKE operator inverts the pattern matching condition.

4. Creating Tables with the Same Structure

Sometimes, you may need to create a new table with the same structure as an existing table. Here‘s how you can do it:

a. Creating a Table without Data

CREATE TABLE Employee_Detail_New AS
SELECT *
FROM Employee_Detail
WHERE 1 = 2;

b. Creating a Table with Data

CREATE TABLE Employee_Detail_New AS
SELECT *
FROM Employee_Detail;

Explanation: To create a table with the same structure but without data, you can use a SELECT statement with a false condition (1 = 2). To create a table with both the structure and data, simply omit the false condition.

5. Displaying Specific Portions of Records

In some cases, you may need to display specific portions of records, such as the first 50% or the last 50%. Here‘s how you can achieve that:

a. Displaying the First 50% of Records

SELECT *
FROM (
  SELECT Row_num, e.*,
         ROW_NUMBER() OVER (ORDER BY Row_num) AS rn
  FROM Employee_Detail e
)
WHERE rn <= (SELECT COUNT(*) FROM Employee_Detail) / 2;

b. Displaying the Last 50% of Records

SELECT *
FROM (
  SELECT Row_num, e.*,
         ROW_NUMBER() OVER (ORDER BY Row_num DESC) AS rn
  FROM Employee_Detail e
)
WHERE rn <= (SELECT COUNT(*) FROM Employee_Detail) / 2;

Explanation: To display specific portions of records, you can use a subquery with the ROW_NUMBER() function to assign row numbers to each record. Then, you can filter the records based on the row numbers and the total count of records.

6. Fetching Distinct/Unique Records

In SQL, you can fetch distinct or unique records using two approaches:

a. Using the DISTINCT Keyword

SELECT DISTINCT *
FROM Employee_Detail;

b. Using the UNION Operator

SELECT *
FROM Employee_Detail
UNION
SELECT *
FROM Employee_Detail;

Explanation: The DISTINCT keyword eliminates duplicate rows from the result set. The UNION operator combines the result sets of two or more SELECT statements and removes duplicates.

7. Splitting a String into Multiple Columns

Interviewers may ask you to split a string into multiple columns. Here‘s an example of how to split the string "SHIKHA" into individual characters:

SELECT SUBSTR(‘SHIKHA‘, LEVEL, 1) AS character
FROM DUAL
CONNECT BY LEVEL <= LENGTH('SHIKHA');

Explanation: The SUBSTR() function extracts a substring from the given string. LEVEL is a pseudo-column that represents the current level in the hierarchical query. CONNECT BY defines the hierarchical relationship, and LENGTH() returns the length of the string.

8. Email Validation using Regular Expressions

Validating email addresses is a common task in SQL interviews. You can use regular expressions to perform email validation:

SELECT *
FROM Employee_Detail
WHERE NOT REGEXP_LIKE(Email_id, ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z]{2,4}$‘, ‘i‘);

Explanation: The REGEXP_LIKE function matches the email address against the specified regular expression pattern. The pattern ensures that the email address contains a username, domain name, and top-level domain.

9. Counting and Removing Duplicate Rows

Sometimes, you may need to count and remove duplicate rows from a table. Here‘s how you can achieve that:

a. Counting Duplicate Rows

SELECT EmpId, COUNT(EmpId) AS count
FROM Employee_Detail
GROUP BY EmpId
HAVING COUNT(EmpId) > 1;

b. Removing Duplicate Rows

DELETE FROM Employee_Detail
WHERE EmpId IN (
  SELECT EmpId
  FROM (
    SELECT EmpId,
           ROW_NUMBER() OVER (PARTITION BY EmpId ORDER BY EmpId) AS rn
    FROM Employee_Detail
  )
  WHERE rn > 1
);

Explanation: To count duplicate rows, you can use the COUNT() aggregate function with the GROUP BY clause and a HAVING condition. To remove duplicate rows, you can use a subquery with the ROW_NUMBER() function to identify duplicate rows and then delete them using the DELETE statement.

10. Finding Even and Odd Rows

Interviewers may ask you to find even and odd rows from a table. Here‘s how you can do it:

a. Finding Even Rows

SELECT *
FROM (
  SELECT Row_num, e.*, MOD(Row_num, 2) AS remainder
  FROM Employee_Detail e
)
WHERE remainder = 0;

b. Finding Odd Rows

SELECT *
FROM (
  SELECT Row_num, e.*, MOD(Row_num, 2) AS remainder
  FROM Employee_Detail e
)
WHERE remainder = 1;

Explanation: To find even or odd rows, you can use the MOD() function to calculate the remainder of the row number divided by 2. If the remainder is 0, it‘s an even row; if the remainder is 1, it‘s an odd row.

Conclusion

Congratulations! You‘ve now learned the top 10 SQL interview queries that will help you ace your next data interview. Remember, practice is key to mastering these concepts and applying them effectively in real-world scenarios.

SQL is a powerful language that enables you to extract insights from data and solve complex problems. By understanding and applying these interview queries, you‘ll demonstrate your proficiency in SQL and impress your interviewers.

To further enhance your SQL skills, consider exploring more advanced topics such as window functions, recursive queries, and performance optimization techniques. The more you practice and challenge yourself, the better prepared you‘ll be for your data interviews.

Happy coding, and best of luck in your data career!