SQL Interview Questions with detailed answers:

5️⃣ Difference between RANK(), DENSE_RANK(), and ROW_NUMBER()

1️⃣ RANK() assigns a rank to each row based on the specified order. If two rows have the same value, they get the same rank, but the next rank is skipped.
Example: If two employees have the same salary and rank as 2, the next rank will be 4 (skipping 3).

SELECT employee_id, salary, 
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

2️⃣ DENSE_RANK() is similar to RANK(), but it does not skip ranks when there are ties.
Example: If two employees share rank 2, the next rank will be 3 instead of skipping it.

SELECT employee_id, salary, 
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

3️⃣ ROW_NUMBER() assigns a unique number to each row, even if the values are the same. No ties occur, and every row gets a unique sequential number.

SELECT employee_id, salary, 
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

⬇️ Key Differences:

RANK() skips numbers when there are duplicates.

DENSE_RANK() does not skip numbers and assigns the next rank sequentially.

ROW_NUMBER() does not allow ties and gives every row a unique number.

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 :)

A simple way to remember which I use for the example given above:

RANK -> 1224 (skip)
DENSE_RANK-> 1223 (no skip)
ROW_NUMBER -> 1234 (sequence)

Hope it helps you as well :)

Here is the list of most widely used window functions in SQL:

ROW_NUMBER(): Assigns consecutive numbers starting from 1 to all rows in the table
RANK: Assigns a rank value to each row within each ordered partition of a result set
NTILE(): Returns the group number for each of the rows in the partition
LEAD() and LAG(): Compares the rows with their previous or next rows
PERCENTILE_CONT: Compares each employee's salary with the average salary in his or her department

And SORT() is not even a valid command in SQL. For sorting, we use ORDER BY clause in SQL.

Hope it helps :)

SQL Interview Questions with detailed answers:

6️⃣ How do you find the second highest salary from an Employee table?

There are multiple ways to find the second highest salary in SQL. Here are three common approaches:

1️⃣ Using LIMIT and OFFSET (MySQL, PostgreSQL, etc.)

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

Explanation:
ORDER BY salary DESC sorts salaries in descending order.
LIMIT 1 OFFSET 1 skips the highest salary (OFFSET 1) and retrieves the next highest.


2️⃣ Using RANK() (Works in SQL Server, PostgreSQL, MySQL 8+)

SELECT salary FROM ( SELECT salary, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) ranked_salaries WHERE rnk = 2; 

Explanation:
The inner query assigns a RANK() to each salary.
The outer query filters for rnk = 2 to get the second highest salary.


3️⃣ Using MAX() and NOT IN (Works in all SQL versions)

SELECT MAX(salary) FROM employees WHERE salary NOT IN (SELECT MAX(salary) FROM employees); 

Explanation:
The subquery finds the highest salary.
The main query finds the maximum salary excluding the highest one.
Each approach depends on the database system you are using.

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:

7️⃣ What is a Common Table Expression (CTE), and when should you use it?

A Common Table Expression (CTE) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It improves code readability and allows recursive queries.

Syntax of a CTE

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

Example: Using CTE to Find Employees with High Salaries

WITH HighSalaryEmployees AS ( SELECT employee_id, first_name, salary FROM employees WHERE salary > 70000 ) SELECT * FROM HighSalaryEmployees; 

When to Use CTEs?

1️⃣ Improve Readability – Makes complex queries easier to understand.
2️⃣ Avoid Subquery Repetition – Instead of repeating subqueries, define them once in a CTE.
3️⃣ Enable Recursion – Useful for hierarchical data like employee-manager relationships.

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:

8️⃣ How do you identify missing values in a dataset using SQL?

In SQL, missing values are usually represented as NULL. You can detect them using the IS NULL condition.

Basic Query to Find NULL Values in a Column

SELECT * FROM employees WHERE salary IS NULL; 

This retrieves all employees where the salary is missing.

Find Missing Values in Multiple Columns

SELECT * FROM employees WHERE salary IS NULL OR department_id IS NULL; 

This checks for NULL values in both the salary and department_id columns.

Count Missing Values in Each Column

SELECT COUNT(*) AS total_rows, COUNT(salary) AS non_null_salaries, COUNT(department_id) AS non_null_departments FROM employees; 

Since COUNT(column_name) ignores NULL values, subtracting it from COUNT(*) gives the number of missing values.

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 :)

Here are some most popular Python libraries for data visualization:

Matplotlib – The most fundamental library for static charts. Best for basic visualizations like line, bar, and scatter plots. Highly customizable but requires more coding.

Seaborn – Built on Matplotlib, it simplifies statistical data visualization with beautiful defaults. Ideal for correlation heatmaps, categorical plots, and distribution analysis.

Plotly – Best for interactive visualizations with zooming, hovering, and real-time updates. Great for dashboards, web applications, and 3D plotting.

Bokeh – Designed for interactive and web-based visualizations. Excellent for handling large datasets, streaming data, and integrating with Flask/Django.

Altair – A declarative library that makes complex statistical plots easy with minimal code. Best for quick and clean data exploration.

For static charts, start with Matplotlib or Seaborn. If you need interactivity, use Plotly or Bokeh. For quick EDA, Altair is a great choice.

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

Hope it helps :)

#python

SQL Interview Questions with detailed answers:

9️⃣ What is the difference between UNION and UNION ALL?

Both UNION and UNION ALL are used to combine the results of two or more SELECT queries, but they handle duplicate records differently.

1️⃣ UNION (Removes Duplicates)
Combines result sets and removes duplicate rows automatically.

It performs an implicit DISTINCT operation, which may affect performance.

SELECT employee_id, department_id FROM employees UNION SELECT employee_id, department_id FROM managers; 

2️⃣ UNION ALL (Keeps Duplicates)

Combines result sets without removing duplicates.
Faster than UNION because it doesn’t perform duplicate elimination.

SELECT employee_id, department_id FROM employees UNION ALL SELECT employee_id, department_id FROM managers; 

Key Differences:

UNION removes duplicates, which may cause performance overhead.

UNION ALL keeps all records, making it more efficient.

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 :)

Many people pay too much to learn Excel, but my mission is to break down barriers. I have shared complete learning series to learn Excel from scratch.

Here are the links to the Excel series

Complete Excel Topics for Data Analyst: https://news.1rj.ru/str/sqlspecialist/547

Part-1: https://news.1rj.ru/str/sqlspecialist/617

Part-2: https://news.1rj.ru/str/sqlspecialist/620

Part-3: https://news.1rj.ru/str/sqlspecialist/623

Part-4: https://news.1rj.ru/str/sqlspecialist/624

Part-5: https://news.1rj.ru/str/sqlspecialist/628

Part-6: https://news.1rj.ru/str/sqlspecialist/633

Part-7: https://news.1rj.ru/str/sqlspecialist/634

Part-8: https://news.1rj.ru/str/sqlspecialist/635

Part-9: https://news.1rj.ru/str/sqlspecialist/640

Part-10: https://news.1rj.ru/str/sqlspecialist/641

Part-11: https://news.1rj.ru/str/sqlspecialist/644

Part-12:
https://news.1rj.ru/str/sqlspecialist/646

Part-13: https://news.1rj.ru/str/sqlspecialist/650

Part-14: https://news.1rj.ru/str/sqlspecialist/651

Part-15: https://news.1rj.ru/str/sqlspecialist/654

Part-16: https://news.1rj.ru/str/sqlspecialist/655

Part-17: https://news.1rj.ru/str/sqlspecialist/658

Part-18: https://news.1rj.ru/str/sqlspecialist/660

Part-19: https://news.1rj.ru/str/sqlspecialist/661

Part-20: https://news.1rj.ru/str/sqlspecialist/662

Bonus: https://news.1rj.ru/str/sqlspecialist/663

I saw a lot of big influencers copy pasting my content after removing the credits. It's absolutely fine for me as more people are getting free education because of my content.

But I will really appreciate if you share credits for the time and efforts I put in to create such valuable content. I hope you can understand.

You can join this telegram channel for more Excel Resources: https://news.1rj.ru/str/excel_data

Python Learning Series: https://news.1rj.ru/str/sqlspecialist/615

Complete SQL Topics for Data Analysts: https://news.1rj.ru/str/sqlspecialist/523

Complete Power BI Topics for Data Analysts: https://news.1rj.ru/str/sqlspecialist/588

I'll now start with learning series on SQL Interviews & Tableau.

Thanks to all who support our channel and share the content with proper credits. You guys are really amazing.

Hope it helps :)

SQL Interview Questions with detailed answers:

🔟 How do you calculate a running total in SQL?

A running total (also known as a cumulative sum) is the sum of values up to the current row. You can calculate it using window functions like SUM() OVER().

Using SUM() with OVER() (Best Approach)

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

Explanation:
SUM(salary) OVER (ORDER BY employee_id) calculates a cumulative sum.
The ORDER BY employee_id ensures the total is calculated sequentially.

Running Total Partitioned by a Category

To calculate the running total within groups (e.g., per department): 👇

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

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 :)

If you want to Excel at using the most used database language in the world, learn these powerful SQL features:

• Wildcards (%, _) – Flexible pattern matching
• Window Functions – ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG()
• Common Table Expressions (CTEs) – WITH for better readability
• Recursive Queries – Handle hierarchical data
• STRING Functions – LEFT(), RIGHT(), LEN(), TRIM(), UPPER(), LOWER()
• Date Functions – DATEDIFF(), DATEADD(), FORMAT()
• Pivot & Unpivot – Transform row data into columns
• Aggregate Functions – SUM(), AVG(), COUNT(), MIN(), MAX()
• Joins & Self Joins – Master INNER, LEFT, RIGHT, FULL, SELF JOIN
• Indexing – Speed up queries with CREATE INDEX

Like it if you need a complete tutorial on all these topics! 👍❤️

#sql

Changed the channel name from "Data Analysts" to "Data Analytics" as moving further I've decided to also teach Data Science, AI, and the latest industry trends to help you stay ahead!

If you support this change, react with a like ❤️

SQL Interview Questions with detailed answers:

1️⃣1️⃣ How does a self-join work? Give an example.

A self-join is when a table joins with itself. It is useful for comparing rows within the same table, such as finding employees and their managers.

Example:

Find Employee-Manager Relationships

SELECT e1.employee_id AS Employee, e1.name AS Employee_Name, e2.employee_id AS Manager, e2.name AS Manager_Name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.employee_id; 

Explanation:
e1 represents employees.
e2 represents managers.
The join condition e1.manager_id = e2.employee_id matches employees to their managers.

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 :)