SQL Interview Questions with detailed answers

1️⃣7️⃣ What is indexing in SQL, and how does it improve performance?

An index in SQL is a data structure that improves query performance by allowing faster data retrieval. It works like an index in a book, helping the database find records quickly instead of scanning the entire table.

How Indexing Improves Performance

1️⃣ Speeds Up Searches – Instead of scanning every row, the database uses the index to locate data faster.
2️⃣ Optimizes Joins – Indexed columns in JOIN conditions improve performance.
3️⃣ Enhances Filtering – WHERE clauses execute faster when filtering by an indexed column.
4️⃣ Reduces Sorting Overhead – Indexing helps when using ORDER BY or GROUP BY.

Creating an Index

CREATE INDEX idx_employee_name ON employees(name);

This creates an index on the name column, making searches like WHERE name = 'John' much faster.

Types of Indexes

✅ Primary Index – Automatically created for PRIMARY KEY.
✅ Unique Index – Ensures uniqueness of values in a column.
✅ Composite Index – Index on multiple columns.
✅ Full-Text Index – Optimized for searching text data.

When Not to Use Indexes

❌ On small tables – Scanning is often faster than using an index.
❌ On frequently updated columns – Index maintenance can slow down INSERT, UPDATE, and DELETE operations.
❌ If the query retrieves most rows – Indexing works best for selective queries, not full table scans.

Top 20 SQL Interview Questions

Like this post if you want me to continue this SQL Interview Series♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Topic 2: Filtering & Advanced WHERE Clause in SQL

Filtering data efficiently is crucial in data analysis. The WHERE clause helps filter rows based on conditions. Let’s explore some advanced filtering techniques.

1️⃣ Using Comparison Operators in WHERE Clause
= → Equal to → Example: WHERE department = 'Sales'
!= or <> → Not equal to → Example: WHERE salary <> 50000
> and < → Greater than / Less than → Example: WHERE age > 30
>= and <= → Greater than or equal to / Less than or equal to → Example: WHERE experience >= 5

🔹 Example: Get all employees who earn more than $50,000

SELECT * FROM employees WHERE salary > 50000;


2️⃣ Using Logical Operators (AND, OR, NOT)

AND → Returns results when both conditions are TRUE SELECT * FROM employees WHERE department = 'IT' AND salary > 70000;
OR → Returns results when at least one condition is TRUE SELECT * FROM employees WHERE department = 'IT' OR department = 'HR';
NOT → Excludes results that match the condition SELECT * FROM employees WHERE NOT department = 'Finance';


3️⃣ Using BETWEEN for Range Filtering

BETWEEN → Selects values within a specific range

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;


BETWEEN can also be used for dates

SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2023-12-31';


4️⃣ Using IN for Multiple Matches

IN is used when filtering data that matches multiple values

SELECT * FROM employees WHERE department IN ('IT', 'HR', 'Sales');

Example: Find employees whose job noscript is either ‘Manager’ or ‘Analyst’

SELECT * FROM employees WHERE job_noscript IN ('Manager', 'Analyst');


5️⃣ Using LIKE & Wildcards for Pattern Matching

% → Represents zero or more characters
_ → Represents exactly one character

🔹 Find employees whose name starts with ‘J’
SELECT * FROM employees WHERE name LIKE 'J%';

🔹 Find employees whose name ends with ‘son’

SELECT * FROM employees WHERE name LIKE '%son';

🔹 Find employees with ‘an’ anywhere in their name

SELECT * FROM employees WHERE name LIKE '%an%';


Mini Task for You:
Write an SQL query to find employees who work in either "Marketing" or "Sales" and earn more than $60,000.

You can find free SQL Resources here
👇👇
https://news.1rj.ru/str/mysqldata

Like this post if you want me to continue covering all the topics! ❤️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

#sql

SQL Interview Questions with detailed answers

1️⃣8️⃣ Write an SQL query to find customers who have placed more than 3 orders.

To find customers who have placed more than 3 orders, we can use the GROUP BY and HAVING clauses to count the number of orders per customer.

SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 3;

Explanation:

1️⃣ GROUP BY customer_id groups all orders by each customer.
2️⃣ COUNT(order_id) counts the number of orders per customer.
3️⃣ HAVING COUNT(order_id) > 3 filters only those customers who have placed more than 3 orders.

If you also want customer names, you can join this with a customers table:

SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) > 3;

Top 20 SQL Interview Questions

Like this post if you want me to continue this SQL Interview Series♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Aggregation Functions in SQL

Aggregation functions help summarize data by performing calculations like sum, average, count, and more. These functions are commonly used in data analysis.

1️⃣ Common Aggregation Functions

COUNT() → Counts the number of rows
SUM() → Calculates the total sum of a numeric column
AVG() → Finds the average value
MIN() → Returns the smallest value
MAX() → Returns the largest value

2️⃣ Using COUNT() to Count Records

🔹 Find the total number of employees

SELECT COUNT(*) FROM employees;

🔹 Find the number of employees in the ‘Sales’ department

SELECT COUNT(*) FROM employees WHERE department = 'Sales';

3️⃣ Using SUM() to Calculate Totals

🔹 Find the total salary of all employees

SELECT SUM(salary) FROM employees;

🔹 Find the total salary paid to employees in the ‘IT’ department

SELECT SUM(salary) FROM employees WHERE department = 'IT';

4️⃣ Using AVG() to Calculate Averages

🔹 Find the average salary of all employees

SELECT AVG(salary) FROM employees;

🔹 Find the average salary of employees in the ‘HR’ department

SELECT AVG(salary) FROM employees WHERE department = 'HR';

5️⃣ Using MIN() and MAX() to Find Extremes

🔹 Find the lowest salary in the company

SELECT MIN(salary) FROM employees;

🔹 Find the highest salary in the company

SELECT MAX(salary) FROM employees;

🔹 Find the most recently hired employee (latest hire date)

SELECT MAX(hire_date) FROM employees;

6️⃣ Using Aggregation Functions with GROUP BY

Aggregation functions are often used with GROUP BY to analyze data by categories.

🔹 Find the total salary for each department

SELECT department, SUM(salary) FROM employees GROUP BY department;

🔹 Find the average salary for each job noscript

SELECT job_noscript, AVG(salary) FROM employees GROUP BY job_noscript;

Mini Task for You:
Write an SQL query to find the highest salary in each department.

You can find free SQL Resources here
👇👇
https://news.1rj.ru/str/mysqldata

Like this post if you want me to continue covering all the topics! ❤️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

#sql

SQL Interview Questions with detailed answers

1️⃣9️⃣ How do you calculate the percentage of total sales for each category?

To calculate the percentage of total sales for each category, we use SUM() and window functions or subqueries.
Using Window Functions (Recommended for Modern SQL)

SELECT category_id, SUM(sales_amount) AS category_sales, (SUM(sales_amount) * 100.0) / SUM(SUM(sales_amount)) OVER () AS sales_percentage FROM sales GROUP BY category_id; 

Explanation:

1️⃣ SUM(sales_amount) OVER () calculates the total sales across all categories.
2️⃣ SUM(sales_amount) * 100.0 / total_sales computes the percentage for each category.
3️⃣ GROUP BY category_id ensures aggregation at the category level.

Using a Subquery (Compatible with Older SQL Versions):

SELECT category_id, SUM(sales_amount) AS category_sales, (SUM(sales_amount) * 100.0) / (SELECT SUM(sales_amount) FROM sales) AS sales_percentage FROM sales GROUP BY category_id; 

This works the same way but calculates total sales in a subquery.

Top 20 SQL Interview Questions

Like this post if you want me to continue this SQL Interview Series♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

SQL Interview Questions with detailed answers

2️⃣0️⃣ What is the use of CASE statements in SQL?

The CASE statement in SQL is used for conditional logic within queries, similar to an IF-ELSE statement in programming. It allows you to return different values based on conditions.

Use Cases of CASE Statement:

1️⃣ Creating custom categories based on conditions.
2️⃣ Handling NULL values with default replacements.
3️⃣ Applying conditional aggregations in reports.

Example 1: Categorizing Sales Amount

SELECT order_id, customer_id, sales_amount, CASE WHEN sales_amount > 1000 THEN 'High' WHEN sales_amount BETWEEN 500 AND 1000 THEN 'Medium' ELSE 'Low' END AS sales_category FROM sales; 

✅ This classifies each sale as High, Medium, or Low based on sales_amount.

Example 2: Handling NULL Values

SELECT employee_id, CASE WHEN department IS NULL THEN 'Not Assigned' ELSE department END AS department_status FROM employees; 

✅ This replaces NULL values in the department column with "Not Assigned".

Example 3: Conditional Aggregation in Reports

SELECT SUM(CASE WHEN order_status = 'Completed' THEN total_amount ELSE 0 END) AS completed_sales, SUM(CASE WHEN order_status = 'Pending' THEN total_amount ELSE 0 END) AS pending_sales FROM orders; 

✅ This calculates total sales separately for "Completed" and "Pending" orders.

Top 20 SQL Interview Questions

React with ❤️ if you want similar Interview Series for other data analytics topics

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

GROUP BY & HAVING in SQL

The GROUP BY clause is used to group rows that have the same values in specified columns. It’s commonly used with aggregation functions (SUM(), AVG(), COUNT(), etc.) to perform calculations on each group.
The HAVING clause filters groups after aggregation, similar to how WHERE filters individual rows.

1️⃣ Basic GROUP BY Usage
🔹 Find the total number of employees in each department

SELECT department, COUNT(*) FROM employees GROUP BY department; 

This groups employees by department and counts the number of employees in each department.

🔹 Find the total salary per department

SELECT department, SUM(salary) FROM employees GROUP BY department; 

2️⃣ GROUP BY with Multiple Columns
You can group by multiple columns to analyze data more deeply.

🔹 Find the total salary for each job noscript within each department

SELECT department, job_noscript, SUM(salary) FROM employees GROUP BY department, job_noscript;

3️⃣ Using HAVING to Filter Groups
Unlike WHERE, which filters before aggregation, HAVING filters after aggregation.

🔹 Find departments with more than 5 employees

SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 5; 

🔹 Find departments where the total salary is greater than $500,000

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department HAVING SUM(salary) > 500000;

🔹 Find job noscripts where the average salary is above $70,000

SELECT job_noscript, AVG(salary) AS avg_salary FROM employees GROUP BY job_noscript HAVING AVG(salary) > 70000; 

4️⃣ GROUP BY with ORDER BY
To sort grouped results, use ORDER BY.

🔹 Find the total salary per department, sorted in descending order

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department ORDER BY total_salary DESC;

Mini Task for You:

Write an SQL query to find departments where the average salary is more than $80,000.
Let me know when you’re ready to move to the next topic! 🚀

You can find free SQL Resources here
👇👇
https://news.1rj.ru/str/mysqldata

Like this post if you want me to continue covering all the topics! ❤️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

#sql

JOINS in SQL

Joins allow you to combine data from multiple tables based on related columns. They are essential for working with relational databases.

1️⃣ Types of JOINS

INNER JOIN → Returns only matching rows from both tables
LEFT JOIN → Returns all rows from the left table + matching rows from the right table
RIGHT JOIN → Returns all rows from the right table + matching rows from the left table
FULL JOIN → Returns all rows from both tables (matching + non-matching)
SELF JOIN → Joins a table with itself
CROSS JOIN → Returns all possible combinations of rows

2️⃣ INNER JOIN (Most Common Join)

🔹 Find employees and their department names

SELECT employees.name, employees.salary, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id; 

✔ Returns only employees who have a matching department.

3️⃣ LEFT JOIN (Includes Unmatched Rows from Left Table)

🔹 Find all employees, including those without a department

SELECT employees.name, employees.salary, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id; 

✔ Includes employees even if they don’t have a department (NULL if no match).

4️⃣ RIGHT JOIN (Includes Unmatched Rows from Right Table)

🔹 Find all departments, including those without employees

SELECT employees.name, employees.salary, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id; 

✔ Includes all departments, even if no employees are assigned.

5️⃣ FULL JOIN (Includes Unmatched Rows from Both Tables)

🔹 Get a complete list of employees and departments (matched + unmatched rows)

SELECT employees.name, employees.salary, departments.department_name FROM employees FULL JOIN departments ON employees.department_id = departments.department_id; 

✔ Includes all employees and departments even if there’s no match.


You can find free SQL Resources here
👇👇
https://news.1rj.ru/str/mysqldata

Like this post if you want me to continue covering all the topics! ❤️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

#sql

Common Table Expressions (CTEs) in SQL 👇👇

CTEs (WITH statement) help write cleaner and more readable SQL queries. They are like temporary result sets that can be referenced within the main query.

1️⃣ Basic Syntax of CTE

WITH cte_name AS ( SELECT column1, column2 FROM table_name WHERE condition ) SELECT * FROM cte_name;

✔ The CTE cte_name is defined and then used in the main SELECT query.

2️⃣ Simple CTE Example

🔹 Find employees earning more than $70,000

WITH high_earners AS ( SELECT name, salary, department_id FROM employees WHERE salary > 70000 ) SELECT * FROM high_earners; 

✔ The CTE high_earners filters employees with high salaries before selecting all columns from it.

3️⃣ CTE with Aggregation

🔹 Find departments where the average salary is above $80,000

WITH department_salary AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) SELECT department_id, avg_salary FROM department_salary WHERE avg_salary > 80000; 

✔ The CTE department_salary calculates the average salary per department and filters out low-paying ones.

4️⃣ CTE for Recursive Queries (Hierarchy Example)

🔹 Find an employee hierarchy (who reports to whom)

WITH RECURSIVE employee_hierarchy AS ( SELECT employee_id, name, manager_id FROM employees WHERE manager_id IS NULL -- Start with top-level manager UNION ALL SELECT e.employee_id, e.name, e.manager_id FROM employees e INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id ) SELECT * FROM employee_hierarchy; 

✔ This recursive CTE finds an employee hierarchy starting from the top-level manager.

5️⃣ Why Use CTEs Instead of Subqueries?

✅ Better Readability – Makes complex queries easier to understand
✅ Reusability – Can be referenced multiple times in the main query
✅ Performance – Some databases optimize CTEs better than nested subqueries

Mini Task for You: Write an SQL query using a CTE to find departments with more than 5 employees.

You can find free SQL Resources here
👇👇
https://news.1rj.ru/str/mysqldata

Like this post if you want me to continue covering all the topics! ❤️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

#sql

Window Functions in SQL

Window functions perform calculations across a set of table rows related to the current row. Unlike aggregation functions, they do not collapse rows but retain all rows while providing additional insights.

1️⃣ Common Window Functions

ROW_NUMBER() → Assigns a unique rank to each row within a partition
RANK() → Similar to ROW_NUMBER(), but gives same rank to duplicates
DENSE_RANK() → Similar to RANK(), but without skipping numbers
NTILE(n) → Divides the result into n equal parts
SUM() OVER() → Running total (cumulative sum)
AVG() OVER() → Moving average
LAG() → Gets the previous row’s value
LEAD() → Gets the next row’s value

2️⃣ Basic Syntax

SELECT column1, column2, window_function() OVER (PARTITION BY column ORDER BY column) AS alias FROM table_name;

✔ PARTITION BY groups rows before applying the function
✔ ORDER BY determines the ranking or sequence

3️⃣ Using ROW_NUMBER()

🔹 Assign a unique row number to each employee based on salary (highest first)

SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num FROM employees;



✔ Each employee gets a unique row number within their department.

4️⃣ Using RANK() and DENSE_RANK()

🔹 Rank employees by salary within each department

SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank FROM employees;



✔ RANK() skips numbers when there’s a tie
✔ DENSE_RANK() does not skip numbers

5️⃣ Using NTILE() for Distribution

🔹 Divide employees into 4 salary groups per department

SELECT name, department, salary, NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS salary_quartile FROM employees;



✔ Useful for dividing salaries into percentiles (e.g., top 25%, bottom 25%)

6️⃣ Running Total with SUM() OVER()

🔹 Calculate cumulative salary per department

SELECT name, department, salary, SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS running_total FROM employees;



✔ Useful for tracking cumulative totals

7️⃣ Using LAG() and LEAD()

🔹 Compare an employee’s salary with the previous and next employee’s salary


SELECT name, department, salary, LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS previous_salary, LEAD(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS next_salary FROM employees; 

✔ LAG() gets the previous row’s value
✔ LEAD() gets the next row’s value

Mini Task for You: Write an SQL query to assign a unique rank to employees based on their salary within each department using RANK().

You can find free SQL Resources here
👇👇
https://news.1rj.ru/str/mysqldata

Like this post if you want me to continue covering all the topics! ❤️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

#sql

Indexing in SQL

Indexes improve the speed of data retrieval by optimizing how queries access tables. They work like a book’s index—allowing you to find information faster instead of scanning every page.

1️⃣ Types of Indexes in SQL:

Primary Index → Automatically created on the primary key
Unique Index → Ensures all values in a column are unique
Composite Index → Created on multiple columns
Clustered Index → Determines the physical order of data storage
Non-Clustered Index → Creates a separate structure for faster lookups
Full-Text Index → Optimized for text searches

2️⃣ Creating an Index

🔹 Create an index on the "email" column in the "users" table

CREATE INDEX idx_email ON users(email); 

✔ Speeds up searches for users by email

3️⃣ Creating a Unique Index
🔹 Ensure that no two users have the same email

CREATE UNIQUE INDEX idx_unique_email ON users(email); 

✔ Prevents duplicate emails from being inserted

4️⃣ Composite Index for Multiple Columns

🔹 Optimize queries that filter by first name and last name

CREATE INDEX idx_name ON users(first_name, last_name); 

✔ Faster lookups when filtering by both first name and last name

5️⃣ Clustered vs. Non-Clustered Index

Clustered Index → Physically rearranges table data (only one per table)
Non-Clustered Index → Stores a separate lookup table for faster access

🔹 Create a clustered index on the "id" column

CREATE CLUSTERED INDEX idx_id ON users(id); 

🔹 Create a non-clustered index on the "email" column

CREATE NONCLUSTERED INDEX idx_email ON users(email); 

✔ Clustered indexes speed up searches when retrieving all columns
✔ Non-clustered indexes speed up searches for specific columns

6️⃣ Checking Indexes on a Table

🔹 Find all indexes on the "users" table

SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('users'); 

7️⃣ When to Use Indexes?

✅ Columns frequently used in WHERE, JOIN, ORDER BY
✅ Large tables that need faster searches
✅ Unique columns that should not allow duplicates
❌ Avoid indexing on columns with highly repetitive values (e.g., boolean columns)
❌ Avoid too many indexes, as they slow down INSERT, UPDATE, DELETE operations

Mini Task for You: Write an SQL query to create a unique index on the "phone_number" column in the "customers" table.

You can find free SQL Resources here
👇👇
https://news.1rj.ru/str/mysqldata

Like this post if you want me to continue covering all the topics! ❤️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

#sql