SQL Most Asked Interview Questions – 2024

SQL Most Asked Interview Questions

SQL (Structured Query Language) is the cornerstone of database management, used extensively for managing and manipulating relational databases. Proficiency in SQL is crucial for roles like Database Administrators, Data Analysts, Data Scientists, and Software Engineers. Consequently, SQL interview questions are a staple in technical interviews for these positions.

Interviewers often focus on SQL to assess a candidate’s ability to perform data retrieval, aggregation, transformation, and manipulation tasks efficiently. Common interview questions range from basic operations like SELECT, INSERT, UPDATE, and DELETE to more complex tasks involving joins, subqueries, window functions, and indexing. They also cover optimization techniques and best practices for writing clean, efficient, and scalable SQL queries.

Understanding and practicing these frequently asked questions helps candidates demonstrate their analytical thinking, problem-solving skills, and proficiency in handling real-world data challenges. Preparing for SQL interview questions not only enhances one’s SQL skills but also ensures readiness for practical database-related tasks encountered in professional environments

SQL Most Asked Interview Questions

Day 1 – 5 Most Asked SQL Interview Questions

Find the Nth Highest Salary

Problem: Write a SQL query to get the Nth highest salary from the employees table.
Table Schema:
CREATE TABLE employees ( id INT, name VARCHAR(50), salary INT );

Solution –

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1
OFFSET N-1;

Question 2: Find Duplicate Rows

Problem: Write a SQL query to find all duplicate rows based on email in a users table.

SELECT email, COUNT() as a
FROM users
GROUP BY email
HAVING COUNT() > 1;

Question 3: Find Employees with Highest Salary in Each Department

Problem: Write a SQL query to find employees with the highest salary in each department.

SELECT department_id, name, salary
FROM employees e
WHERE salary = ( SELECT MAX(salary) FROM employees WHERE department_id = e.department_id );

Question 4: Find Consecutive Days with Logins

Problem: Write a SQL query to find all users who have logged in for three consecutive days.

SELECT DISTINCT l1.user_id
FROM logins l1 JOIN
logins l2 ON l1.user_id = l2.user_id AND l2.login_date = DATE_ADD(l1.login_date, INTERVAL 1 DAY)
JOIN logins l3 ON l1.user_id = l3.user_id AND l3.login_date = DATE_ADD(l1.login_date, INTERVAL 2 DAY);

Question 5: Find Missing Numbers in a Sequence

Problem: Write a SQL query to find all missing numbers from a sequence in the numbers table.

SELECT n1.num + 1 AS missing_num
FROM numbers n1
LEFT JOIN numbers n2 ON n1.num + 1 = n2.num
WHERE n2.num IS NULL;

Day 2 – Cross join and Self join Questions

Question: Generate all possible pairs of employees from different departments and calculate the difference in their salaries.
Table Schema:

CREATE TABLE employees ( id INT, name VARCHAR(50), department_id INT, salary INT );

Solution

SELECT e1.name AS employee1,
e1.department_id AS dept1,
e2.name AS employee2,
e2.department_id AS dept2,
ABS(e1.salary – e2.salary) AS salary_difference
FROM employees e1
CROSS JOIN employees e2
WHERE e1.department_id <> e2.department_id;

Self Join Hard Interview Question

Question: Find all employees who earn more than the average salary of their respective departments.
Table Schema:
CREATE TABLE employees ( id INT, name VARCHAR(50), department_id INT, salary INT );

SELECT e1.name,
e1.department_id,
e1.salary
FROM employees e1
JOIN (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) e2 ON e1.department_id = e2.department_id
WHERE e1.salary > e2.avg_salary;

Explanation

Cross Join:

• A CROSS JOIN produces the Cartesian product of the two tables involved, meaning it pairs each row from the first table with every row from the second table.
• In the given question, the CROSS JOIN pairs every employee with every other employee from different departments, resulting in all possible department pairs.

Self Join:

• A SELF JOIN is a regular join, but the table is joined with itself.
• In the given question, the subquery calculates the average salary for each department. Then, the main query joins the original table with this subquery to find employees who earn more than the department’s average salary.

Day 3 – JSON and Date Concepts

JSON Concepts

1. JSON Data Type:
   • SQL databases like PostgreSQL, MySQL, and SQL Server support JSON data types, allowing storage and querying of JSON documents directly within a table.
2. JSON Functions and Operators:
   • Functions to parse, extract, and manipulate JSON data. For example, JSON_EXTRACT, JSON_QUERY, JSON_VALUE, JSON_OBJECT, JSON_ARRAY, etc.
3. Indexing JSON:
   • Indexing JSON columns to improve query performance, such as GIN indexes in PostgreSQL.

Date Concepts

1. Date Data Types:
   • Various date and time data types like DATE, TIME, TIMESTAMP, DATETIME, etc.
2. Date Functions:
   • Functions for date manipulation and calculation, such as DATE_ADD, DATE_SUB, DATEDIFF, DATE_TRUNC, CURRENT_DATE, CURRENT_TIMESTAMP, etc.
3. Date Formatting:
   • Formatting and parsing dates using functions like DATE_FORMAT, STR_TO_DATE, etc.

JSON – Functions in Presto

Parses a JSON-formatted string into a JSON object.
SELECT json_parse(‘{“name”: “John”, “age”: 30}’)

Extracts a JSON value from a JSON object using a JSON path.
SELECT json_extract(‘{“name”: “John”, “age”: 30}’, ‘$.name’);

Extracts a scalar value (string, number, boolean) from a JSON object.
SELECT json_extract_scalar(‘{“name”: “John”, “age”: 30}’, ‘$.name’);

Returns the length of a JSON array.
SELECT json_array_length(‘[1, 2, 3, 4]’);

Converts a SQL value to a JSON-formatted string.
SELECT json_format(CAST(ROW(1, 2) AS ROW(x INT, y INT)));

Date functions in Presto

Returns the current date.
SELECT current_date;

Returns the current timestamp.
SELECT current_timestamp;

Adds a specified number of days, months, or years to a date.
SELECT date_add(‘day’, 5, current_date);

Calculates the difference between two dates.
SELECT date_diff(‘day’, date ‘2024-07-20’, current_date);

Formats a date or timestamp using a specified format.
SELECT date_format(current_timestamp, ‘%Y-%m-%d %H:%i:%s’);

Parses a date string into a date or timestamp.
SELECT date_parse(‘2024-07-29’, ‘%Y-%m-%d’);

Extracts the year, month, or day from a date.
SELECT year(current_date), month(current_date), day(current_date);


Extract Name and Age from details JSON column where skill part is Presto

SELECT json_extract_scalar(details, ‘$.name’) AS name,
json_extract_scalar(details, ‘$.age’) AS age
FROM employees
WHERE json_extract(details, ‘$.skills’) LIKE ‘%Presto%’;

Date Interview Question

Write a query to find the total number of employees who have joined in each month of the current year.

SELECT date_format(join_date, ‘%Y-%m’) AS month,
COUNT(*) AS total_employees
FROM employees
WHERE year(join_date) = year(current_date)
GROUP BY date_format(join_date, ‘%Y-%m’)
ORDER BY month;

Day 4 – Unnesting and Pivot Concepts in Presto

Unnesting in Presto

Unnesting is the process of converting an array or map into a set of rows. It is particularly useful when you have nested data structures and you need to flatten them for further analysis.

Example:

Given a table orders with a column items storing arrays of items ordered, we want to unnest these arrays.

Table Schema:

CREATE TABLE orders ( order_id INT, customer_id INT, items ARRAY<VARCHAR> );

Sample Data:

INSERT INTO orders (order_id, customer_id, items)
VALUES
(1, 101, ARRAY[‘item1’, ‘item2’, ‘item3’]),
(2, 102, ARRAY[‘item4’, ‘item5’]);

Unnest Query:

SELECT order_id, customer_id, item
FROM orders CROSS JOIN UNNEST(items) AS t (item);

Explanation:

• CROSS JOIN UNNEST(items) AS t (item) flattens the array items into individual rows.
• Each element of the array is transformed into a separate row, duplicating the order_id and customer_id for each item.

Output

Pivoting in Presto

Pivoting is the process of transforming rows into columns. It is useful when you want to summarize data in a cross-tabulated form.

Example:

Given a table sales with columns year, quarter, and revenue, we want to pivot the quarters into columns for each year.

Table Schema:

CREATE TABLE sales ( year INT, quarter VARCHAR, revenue DECIMAL );

Sample Data:
INSERT INTO sales (year, quarter, revenue)
VALUES
(2021, ‘Q1’, 1000.00),
(2021, ‘Q2’, 1500.00),
(2021, ‘Q3’, 2000.00),
(2021, ‘Q4’, 2500.00),
(2022, ‘Q1’, 1100.00),
(2022, ‘Q2’, 1600.00),
(2022, ‘Q3’, 2100.00),
(2022, ‘Q4’, 2600.00);

Pivot Query:

SELECT year,
MAX(CASE WHEN quarter = ‘Q1’ THEN revenue END) AS Q1,
MAX(CASE WHEN quarter = ‘Q2’ THEN revenue END) AS Q2,
MAX(CASE WHEN quarter = ‘Q3’ THEN revenue END) AS Q3,
MAX(CASE WHEN quarter = ‘Q4’ THEN revenue END) AS Q4
FROM sales
GROUP BY year
ORDER BY year;

Explanation:

• CASE WHEN quarter = 'Q1' THEN revenue END selects the revenue for Q1, and similar cases for Q2, Q3, and Q4.
• MAX is used to aggregate the revenue values for each quarter, as pivoting typically requires an aggregation function.
• GROUP BY year groups the data by year to perform the pivot operation.

Output

Unnesting and pivoting are powerful data transformation techniques in Presto:

• Unnesting: Converts nested data structures (arrays, maps) into a flat set of rows, enabling detailed analysis of individual elements.
• Pivoting: Transforms rows into columns, allowing for the summarization and cross-tabulation of data.

Mastering these concepts is essential for effectively handling complex data transformations and creating comprehensive reports and analyses.

Day 5 – Advance sql interview topics with simple explanation

Window Functions

• Explanation: Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike regular aggregate functions, window functions do not cause rows to become grouped into a single output row; the rows retain their separate identities.
• Example Use Case: Calculate a running total, rank rows, or compute moving averages.

SELECT employee_id, salary,
SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM employees;

Common Table Expressions (CTEs)

• Explanation: CTEs are temporary result sets that are defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. They make complex queries more readable and easier to manage.
• Example Use Case: Simplify complex joins and subqueries, and create recursive queries.

WITH SalesCTE AS
( SELECT employee_id, SUM(sales) AS total_sales
FROM sales
GROUP BY employee_id )

SELECT e.name, s.total_sales
FROM employees e JOIN SalesCTE s ON e.id = s.employee_id;

Recursive Queries

• Explanation: Recursive queries are a type of CTE that refers to itself to achieve iteration. They are useful for querying hierarchical data such as organizational charts or folder structures.
• Example Use Case: Traverse hierarchies, like finding all subordinates of a manager.
• Example:

WITH RECURSIVE EmployeeHierarchy AS
( SELECT employee_id,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL

UNION ALL

SELECT e.employee_id,
e.manager_id,
eh.level + 1
FROM employees e
JOIN EmployeeHierarchy eh
ON e.manager_id = eh.employee_id )

SELECT * FROM EmployeeHierarchy;

Indexing

• Explanation: Indexes are database objects that improve the speed of data retrieval operations at the cost of additional storage and potential slower write operations. Understanding when and how to use indexes is crucial for optimizing query performance.
• Example Use Case: Speed up searches, joins, and filter operations on large tables.
• Example:

CREATE INDEX idx_employee_name ON employees(name);
SELECT * FROM employees WHERE name = ‘John Doe’;

Transaction Management

• Explanation: Transactions are sequences of SQL statements that are executed as a single unit of work. They ensure data integrity and consistency, using the properties of ACID (Atomicity, Consistency, Isolation, Durability).
• Example Use Case: Ensure that multiple related updates to the database either all succeed or all fail.
• Example:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance – 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

Window Functions: Calculate running totals, ranks, and moving averages without collapsing rows.
Common Table Expressions (CTEs): Simplify complex queries and enable recursive querying.
Recursive Queries: Handle hierarchical data structures using self-referencing CTEs.
Indexing: Improve query performance by creating indexes on frequently queried columns.
Transaction Management: Ensure data integrity and consistency using transactions with ACID properties.

Our services

1. YouTube channel covering all the interview-related important topics in SQL, Python, MS Excel, Machine Learning Algorithm, Statistics, and Direct Interview Questions
   Link – The Data Monk Youtube Channel
   
2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
   Link – The Data Monk website
   
3. E-book shop – We have 70+ e-books available on our website and 3 bundles covering 2000+ solved interview questions
   Link – The Data E-shop Page
   
4. Instagram Page – It covers only Most asked Questions and concepts (100+ posts)
   Link – The Data Monk Instagram page
   
5. Mock Interviews
   Book a slot on Top Mate
   
6. Career Guidance/Mentorship
   Book a slot on Top Mate
   
7. Resume-making and review
   Book a slot on Top Mate 

The Data Monk e-books

We know that each domain requires a different type of preparation, so we have divided our books in the same way:

✅ Data Analyst and Product Analyst -> 1100+ Most Asked Interview Questions

✅ Business Analyst -> 1250+ Most Asked Interview Questions

✅ Data Scientist and Machine Learning Engineer -> 23 e-books covering all the ML Algorithms Interview Questions

✅ Full Stack Analytics Professional – 2200 Most Asked Interview Questions

The Data Monk – 30 Days Mentorship program

We are a group of 30+ people with ~8 years of Analytics experience in product-based companies. We take interviews on a daily basis for our organization and we very well know what is asked in the interviews.
Other skill enhancer websites charge 2lakh+ GST for courses ranging from 10 to 15 months.

We only focus on making you a clear interview with ease. We have released our Become a Full Stack Analytics Professional for anyone in 2nd year of graduation to 8-10 YOE. This book contains 23 topics and each topic is divided into 50/100/200/250 questions and answers. Pick the book and read
it thrice, learn it, and appear in the interview.

We also have a complete Analytics interview package
– 2200 questions ebook (Rs.1999) + 23 ebook bundle for Data Science and Analyst role (Rs.1999)
– 4 one-hour mock interviews, every Saturday (top mate – Rs.1000 per interview)
– 4 career guidance sessions, 30 mins each on every Sunday (top mate – Rs.500 per session)
– Resume review and improvement (Top mate – Rs.500 per review)

Total cost – Rs.10500
Discounted price – Rs. 9000