Unveiling SQL Interview Essentials: 15 Crucial Questions for Success

Elevate your SQL interview preparation with our comprehensive guide, offering an in-depth compilation of the most frequently asked questions. Anticipate encountering 2-3 questions from this curated list during your interview, ensuring you're well-equipped to showcase your SQL proficiency. Get ready to stand out and excel in your SQL interviews.

Let's delve into the questions with a sample table script:

-- Creating the 'emp' table

CREATE TABLE emp (

emp_id INT,

emp_name VARCHAR(20),

department_id INT,

salary INT,

manager_id INT,

emp_age INT

);

-- Inserting data into the 'emp' table

INSERT INTO emp VALUES (1, 'Ankit', 100, 10000, 4, 39);

INSERT INTO emp VALUES (2, 'Mohit', 100, 15000, 5, 48);

INSERT INTO emp VALUES (3, 'Vikas', 100, 10000, 4, 37);

INSERT INTO emp VALUES (4, 'Rohit', 100, 5000, 2, 16);

-- More INSERT statements...

-- Creating the 'department' table

CREATE TABLE department(

dept_id INT,

dept_name VARCHAR(10)

);

-- Inserting data into the 'department' table

INSERT INTO department values(100,'Analytics');

INSERT INTO department values(300,'IT');

-- More INSERT statements...

-- Creating the 'orders' table

CREATE TABLE orders(

customer_name VARCHAR(10),

order_date DATE,

order_amount INT,

customer_gender VARCHAR(6)

);

-- Inserting data into the 'orders' table

INSERT INTO orders values('Shilpa','2020-01-01',10000,'Female');

INSERT INTO orders values('Rahul','2020-01-02',12000,'Male');

-- More INSERT statements...

-- Creating the 'employees' table

CREATE TABLE employees (

emp_id INT,

emp_name VARCHAR(50),

department_id INT,

salary INT,

manager_id INT,

gender VARCHAR(10)

);

-- Inserting data into the 'employees' table

INSERT INTO employees VALUES (1, 'John Doe', 100, 50000, 4, 'Male');

INSERT INTO employees VALUES (2, 'Jane Smith', 100, 60000, 5, 'Female');

INSERT INTO employees VALUES (3, 'Alice Johnson', 200, 55000, 4, 'Female');

INSERT INTO employees VALUES (4, 'Bob Brown', 200, 52000, 2, 'Male');

-- More INSERT statements...

-- Creating the 'customers' table

CREATE TABLE customers (

customer_id INT,

customer_name VARCHAR(50),

gender VARCHAR(10),

email VARCHAR(50),

phone VARCHAR(15)

);

-- Inserting data into the 'customers' table

INSERT INTO customers VALUES (1, 'Rakesh Kumar', 'Male', '[email protected]', '123-456-7890');

INSERT INTO customers VALUES (2, 'Sunita Sharma', 'Female', '[email protected]', '987-654-3210');

INSERT INTO customers VALUES (3, 'Amit Singh', 'Male', '[email protected]', '456-789-0123');

INSERT INTO customers VALUES (4, 'Anita Gupta', 'Female', '[email protected]', '321-654-9870');

-- More INSERT statements...

Let's dive into the questions:

Question 1 — How to find duplicates in a given table?

SELECT

emp_id, COUNT(*)

FROM

emp

GROUP BY emp_id

HAVING COUNT(*) > 1;

Explanation:

• In this SQL query, we're selecting the emp_id column from the employees table and counting the occurrences of each emp_id.
• Using the GROUP BY clause, we group the rows in the employees table by the emp_id column, so we get one row for each unique emp_id.
• The HAVING clause filters the grouped rows to only include rows where the count of occurrences (COUNT(*)) is greater than 1. This means we're selecting only those emp_id values that appear more than once in the employees table, indicating duplicates.
• The result of this query will be a list of emp_id values that appear more than once in the employees table along with their respective counts. This query helps identify duplicates in the employees table based on the emp_id column, which can be important for data quality assurance and cleanup.

Question 2 — How to delete duplicates?

WITH cte AS (

SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY emp_id) AS rn

FROM emp

)

DELETE FROM cte WHERE rn > 1;

Explanation:

• In this SQL query, we're using a Common Table Expression (CTE) named cte to select all columns from the employees table and add a new column rn using the ROW_NUMBER() window function.
• The PARTITION BY clause in the ROW_NUMBER() function ensures that row numbers are assigned separately for each unique emp_id.
• The ORDER BY clause orders the rows within each partition by emp_id.
• The CTE now contains a row number for each row in the employees table.
• The DELETE statement is applied to the CTE, deleting rows where the row number (rn) is greater than 1. This effectively deletes all but one instance of each duplicate emp_id.
• After running this SQL code, the employees table will be left with only one instance of each unique emp_id, and duplicates will be removed. This query is useful for maintaining data integrity by removing duplicate records from the employees table based on the emp_id column.

Question 3 — Difference between UNION and UNION ALL? Assuming we have two tables employees and customers, and we want to combine the results of two queries:

-- UNION Example

SELECT emp_name FROM employees

UNION

SELECT customer_name FROM customers;

Explanation: The UNION operator combines the results of two SELECT statements and removes duplicate rows from the combined result set. In this example, the query selects names from emp_name in the employees table and customer_name in the customers table, and any duplicate names are removed from the final result set.

-- UNION ALL Example

SELECT emp_name FROM employees

UNION ALL

SELECT customer_name FROM customers;

Explanation: The UNION ALL operator also combines the results of two SELECT statements but does not remove duplicate rows from the combined result set. In this example, all rows from both emp_name in employees and customer_name in customers are included in the final result set, including duplicates.

Question 4 — Difference between RANK, ROW_NUMBER, and DENSE_RANK? Code Example:

-- RANK Example

SELECT

emp_id,

emp_name,

salary,

RANK() OVER(ORDER BY salary DESC) AS rnk

FROM

employees;

-- ROW_NUMBER Example

SELECT

emp_id,

emp_name,

salary,

ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn

FROM

employees;

-- DENSE_RANK Example

SELECT

emp_id,

emp_name,

salary,

DENSE_RANK() OVER(ORDER BY salary DESC) AS rn

FROM

employees;

Explanation:

1. RANK():The RANK() function assigns a unique rank to each distinct row in the result set.If multiple rows have the same values and are assigned the same rank, the next rank will skip the number of tied rows and continue from there. In other words, it leaves gaps in the ranking sequence for tied rows.It assigns the same rank to tied rows but doesn’t provide unique row numbers.
2. ROW_NUMBER():The ROW_NUMBER() function assigns a unique row number to each row in the result set.It doesn’t consider ties. If multiple rows have the same values, they will still receive unique row numbers.It doesn’t leave gaps in the row numbering sequence.
3. DENSE_RANK():The DENSE_RANK() function assigns a unique rank to each distinct row in the result set.If multiple rows have the same values, they are assigned the same rank, and the next rank does not skip any values. It provides a dense ranking without gaps for tied rows.It assigns the same rank to tied rows but doesn’t provide unique row numbers. These window functions are useful for assigning ranks or row numbers to rows in a result set based on the specified ordering, and understanding their differences is crucial for performing analytical tasks efficiently in SQL.

Question 5 — How to find employees who are not present in the department table? Code Example:

SELECT

*

FROM

employees AS e

LEFT JOIN

departments AS d ON e.department_id = d.dept_id

WHERE d.dept_id IS NULL;

Explanation:

• In this SQL query, we're using a LEFT JOIN between the employees table (aliased as e) and the departments table (aliased as d) based on the department_id.
• The LEFT JOIN ensures that all rows from the employees table are returned, along with matching rows from the departments table. If there is no match, NULL values are returned for the columns from the departments table.
• The WHERE clause filters the result set to only include rows where the dept_id column from the departments table is NULL. This indicates that the employee is not associated with any department.
• By using LEFT JOIN and checking for NULL values in the department table, we can find employees who are not present in the department table. This query is useful for identifying employees who have not been assigned to any department, which can be important for organizational management and analysis.

Question 6 — How to find the second highest salary in each department? Code Example:

SELECT *

FROM (

SELECT

emp_name,

salary,

department_id,

DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn

FROM employees

) AS a

WHERE rn = 2;

Explanation:

• In this SQL query, we're using a window function DENSE_RANK() over a specific partition defined by the department_id column to assign ranks to salaries within each department.
• The ORDER BY clause orders the salaries within each department in descending order.
• The DENSE_RANK() function assigns a dense rank to each salary within its respective department, ensuring that tied salaries receive the same rank without any gaps.
• By filtering the results to WHERE rn = 2, we're selecting rows where the rank (rn) is equal to 2. This effectively gives us the employees with the second-highest salary in each department.
• The outer query then selects all columns from the derived table a, which contains the employees with the second-highest salary in each department. This query is useful for identifying employees with the second-highest salary within their respective departments, which can be valuable for various HR and management purposes.

Question 7 — Find all the transactions done by customers named "RAKESH". Code Example:

SELECT *

FROM orders

WHERE UPPER(customer_name) = 'RAKESH';

Explanation:

• In this SQL query, we're selecting all columns from the orders table.
• The WHERE clause filters the result set to include only rows where the customer_name column matches 'RAKESH' after converting it to uppercase using the UPPER() function.
• Using UPPER() ensures that the comparison is case-insensitive, allowing us to match variations in the case of the customer name. This query retrieves all transactions made by customers named "RAKESH" from the orders table.

Question 8 — How to perform a self join to find cases where the manager's salary is greater than the employee's salary?

Code Example:

SELECT

e1.emp_id,

e1.emp_name,

e2.emp_name AS manager_name,

e1.salary AS employee_salary,

e2.salary AS manager_salary

FROM

employees AS e1

INNER JOIN

employees AS e2 ON e1.manager_id = e2.emp_id

WHERE e2.salary > e1.salary;

Explanation:

• In this SQL query, we perform a self join on the employees table.
• We join the table on the condition that the manager_id of an employee (e1) matches the emp_id of another employee (e2). This joins an employee with their manager.
• The SELECT statement retrieves the emp_id, emp_name, and salary of the employee (e1), along with the emp_name and salary of the manager (e2).
• The WHERE clause filters the result set to only include rows where the salary of the manager (e2.salary) is greater than the salary of the employee (e1.salary).
• This query helps identify cases where the manager earns more than the employee, which might be useful for performance evaluation or salary adjustment purposes.

Question 9 — How to find all transactions done by female customers?

Code Example:

SELECT *

FROM orders

WHERE UPPER(customer_gender) = 'FEMALE';

Explanation:

• In this SQL query, we're selecting all columns from the orders table.
• The WHERE clause filters the result set to include only rows where the customer_gender column matches 'FEMALE' after converting it to uppercase using the UPPER() function.
• Using UPPER() ensures that the comparison is case-insensitive, allowing us to match variations in the case of the gender. This query retrieves all transactions made by female customers from the orders table, which can be useful for analyzing sales patterns or targeting specific customer segments.

Question 10 — How to find employees who are not assigned to any department?

Code Example:

SELECT *

FROM employees AS e

LEFT JOIN departments AS d ON e.department_id = d.dept_id

WHERE d.dept_id IS NULL;

Explanation:

• In this SQL query, we're performing a LEFT JOIN between the employees table (aliased as e) and the departments table (aliased as d) based on the department_id.
• The LEFT JOIN ensures that all rows from the employees table are returned, along with matching rows from the departments table. If there is no match, NULL values are returned for the columns from the departments table.
• The WHERE clause filters the result set to only include rows where the dept_id column from the departments table is NULL. This indicates that the employee is not associated with any department.
• This query helps identify employees who have not been assigned to any department, which can be important for organizational management and analysis.

Question 11 — How to calculate the total number of orders placed on each order date?

Code Example:

SELECT

order_date,

COUNT(*) AS total_orders

FROM orders

GROUP BY order_date;

Explanation:

• In this SQL query, we're selecting the order_date column from the orders table along with the count of orders placed on each date.
• The COUNT(*) function is used to count the number of rows for each distinct order_date.
• Using the GROUP BY clause, we group the rows in the orders table by the order_date column. This groups the orders based on their order dates.
• As a result, we get one row for each unique order date along with the total number of orders placed on that date.
• This query helps to analyze the distribution of orders over different dates, which can be useful for understanding trends and patterns in customer behavior or business operations.

Question 12 — How to identify employees with the highest salary in each department?

Code Example:

SELECT *

FROM (

SELECT

emp_name,

salary,

department_id,

ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn

FROM employees

) AS a

WHERE rn = 1;

Explanation:

• In this SQL query, we're using a window function ROW_NUMBER() over a specific partition defined by the department_id column to assign row numbers based on the salary in descending order within each department.
• The PARTITION BY clause divides the result set into partitions, one for each unique department_id.
• The ORDER BY clause orders the rows within each partition by salary in descending order.
• The ROW_NUMBER() function assigns a unique row number to each row within its partition.
• By filtering the results to WHERE rn = 1, we're selecting rows where the row number (rn) is equal to 1. This effectively gives us the employees with the highest salary in each department.
• The outer query then selects all columns from the derived table a, which contains the employees with the highest salary in each department. This query is useful for identifying top earners in each department, which can be valuable for various HR and management purposes.

Question 13 — How to find the total salary expenditure for each department?

Code Example:

SELECT

d.dept_name,

SUM(e.salary) AS total_salary_expenditure

FROM

employees e

JOIN

departments d ON e.department_id = d.dept_id

GROUP BY

d.dept_name;

Explanation:

• In this SQL query, we're joining the employees table (e) with the departments table (d) based on the department_id and dept_id columns, respectively.
• The JOIN operation ensures that we have access to the department names (dept_name) from the departments table.
• The SUM() function is used to calculate the total salary expenditure for each department by summing up the salaries of all employees within the department.
• Using the GROUP BY clause, we group the result set by department names (dept_name). This allows us to calculate the total salary expenditure for each department.
• As a result, we obtain one row for each department, containing the department name and the total salary expenditure for that department.
• This query helps organizations understand and manage their payroll expenses across different departments, facilitating budgeting and financial analysis.

Question 14 — How to identify employees who have the same salary as their managers?

Code Example:

SELECT

e.emp_id,

e.emp_name,

e.salary AS employee_salary,

m.emp_name AS manager_name,

m.salary AS manager_salary

FROM

employees e

JOIN

employees m ON e.manager_id = m.emp_id

WHERE

e.salary = m.salary;

Explanation:

• In this SQL query, we're joining the employees table (e) with itself (aliased as m) based on the manager_id and emp_id columns to establish a self-join.
• The JOIN operation ensures that we pair each employee with their corresponding manager.
• The WHERE clause filters the result set to include only rows where the salary of the employee (e.salary) is equal to the salary of their manager (m.salary).
• As a result, we obtain rows where employees have the same salary as their managers, allowing organizations to identify such cases for review or analysis.
• This query can help in assessing the fairness and consistency of salary structures within the organization.

Question 15 — Update query to swap gender?

Code Example:

UPDATE employees

SET gender =

CASE

WHEN gender = 'Male' THEN 'Female'

WHEN gender = 'Female' THEN 'Male'

ELSE gender

END;

Explanation:

• The UPDATE statement is used to modify existing records in the employees table.
• The SET clause specifies the column to be updated, which is gender in this case.
• The CASE statement evaluates each row's current gender value:If the gender is 'Male', it updates it to 'Female'.If the gender is 'Female', it updates it to 'Male'.For any other gender values, it leaves the gender unchanged.
• The UPDATE statement applies the changes to all records in the employees table.
• This query efficiently swaps the gender values for all employees in the table, ensuring data consistency.

In conclusion, mastering SQL interview questions is crucial for individuals aiming to excel in their career endeavors. By delving into the core concepts and practicing key scenarios, you're not only preparing for interviews but also laying a strong foundation for success in real-world projects.

We've explored 15 essential SQL interview questions, ranging from data manipulation to analytical queries, providing you with a comprehensive toolkit for success. Remember, preparation is the key to unlocking your full potential during interviews.

As you continue to refine your SQL skills and deepen your understanding of database management, leverage these questions as springboards for further exploration and practice. Stay curious, stay persistent, and keep honing your expertise.

With dedication and strategic preparation, you can navigate SQL interviews with ease, leaving a lasting impression on prospective employers and positioning yourself as a valuable asset in the competitive landscape of data management and analysis.

Embrace the challenge, embrace the opportunity, and let your SQL prowess shine bright in every interview scenario.