SQL Interview Questions with detailed answers:

1️⃣2️⃣ What is a window function, and how is it different from GROUP BY?

A window function performs calculations across a set of table rows related to the current row, without collapsing the result set like GROUP BY.

Key Differences Between Window Functions and GROUP BY:

1️⃣ Window functions retain all rows, while GROUP BY collapses data into a smaller result set.

2️⃣ Window functions use aggregate functions like SUM(), AVG(), and RANK(), but they do not group data; instead, they compute results for each row individually within a defined window.

3️⃣ GROUP BY does not allow row-wise calculations, whereas window functions can provide rankings, running totals, and moving averages while keeping the original data intact.

4️⃣ Window functions support partitions, meaning they can reset calculations within groups using PARTITION BY. In contrast, GROUP BY always groups the entire dataset based on specified columns.

Example of a Window Function (SUM() Over a Window)

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

Here, SUM(salary) is calculated for each department separately, but all rows remain in the result.

Example of GROUP BY (Aggregates Data)

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

In this case, the result shows only one row per department, removing individual employee details.

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

Let me start with teaching each topic one by one.

Let's start with SQL first, as it's one of the most important skills.

Topic 1: SQL Basics for Data Analysts

SQL (Structured Query Language) is used to retrieve, manipulate, and analyze data stored in databases.

1️⃣ Understanding Databases & Tables

Databases store structured data in tables.

Tables contain rows (records) and columns (fields).

Each column has a specific data type (INTEGER, VARCHAR, DATE, etc.).

2️⃣ Basic SQL Commands

Let's start with some fundamental queries:

🔹 SELECT – Retrieve Data

SELECT * FROM employees; -- Fetch all columns from 'employees' table SELECT name, salary FROM employees; -- Fetch specific columns 

🔹 WHERE – Filter Data

SELECT * FROM employees WHERE department = 'Sales'; -- Filter by department SELECT * FROM employees WHERE salary > 50000; -- Filter by salary 

🔹 ORDER BY – Sort Data

SELECT * FROM employees ORDER BY salary DESC; -- Sort by salary (highest first) SELECT name, hire_date FROM employees ORDER BY hire_date ASC; -- Sort by hire date (oldest first) 

🔹 LIMIT – Restrict Number of Results

SELECT * FROM employees LIMIT 5; -- Fetch only 5 rows SELECT * FROM employees WHERE department = 'HR' LIMIT 10; -- Fetch first 10 HR employees 

🔹 DISTINCT – Remove Duplicates

SELECT DISTINCT department FROM employees; -- Show unique departments 

Mini Task for You: Try to write an SQL query to fetch the top 3 highest-paid employees from an "employees" 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

SQL Interview Questions with detailed answers

1️⃣3️⃣ How do you detect and remove duplicate records in SQL?

Detecting Duplicate Records:
To find duplicate rows based on specific columns, use GROUP BY with HAVING COUNT(*) > 1:

SELECT employee_id, department_id, COUNT(*) FROM employees GROUP BY employee_id, department_id HAVING COUNT(*) > 1; 

This retrieves records where the same employee_id and department_id appear more than once.

Removing Duplicates Using ROW_NUMBER():
To delete duplicates while keeping only one occurrence, use ROW_NUMBER():

WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY employee_id, department_id ORDER BY employee_id) AS row_num FROM employees ) DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM CTE WHERE row_num > 1); 

Alternative: Deleting Using DISTINCT and a Temp Table
If ROW_NUMBER() is not supported, you can create a temporary table:

CREATE TABLE employees_temp AS SELECT DISTINCT * FROM employees; DROP TABLE employees; ALTER TABLE employees_temp RENAME TO employees; 

This removes duplicates by keeping only distinct records.

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:

1️⃣4️⃣ Explain the difference between EXISTS and IN.

Both EXISTS and IN are used to filter data based on a subquery, but they work differently in terms of performance and execution.

Key Differences Between EXISTS and IN:

1️⃣ EXISTS checks for the existence of rows in a subquery and returns TRUE if at least one row is found. It stops checking once a match is found, making it more efficient for large datasets.
2️⃣ IN checks if a value is present in a list of values returned by a subquery. It evaluates all rows, which can be slower if the subquery returns a large number of results.
3️⃣ EXISTS is preferred for correlated subqueries, where the inner query depends on the outer query.
4️⃣ IN is generally better for small, fixed lists of values but can be inefficient for large subquery results.

Example of EXISTS:

SELECT employee_id, name FROM employees e WHERE EXISTS ( SELECT 1 FROM departments d WHERE d.department_id = e.department_id ); 

Here, EXISTS checks if a matching department_id exists in the departments table and returns TRUE as soon as it finds a match.

Example of IN:

SELECT employee_id, name FROM employees WHERE department_id IN (SELECT department_id FROM departments); 

In this case, IN retrieves all department_id values from the departments table and checks each row in the employees table against this list.

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 Power BI and become a data visualization pro, master these essential features:

• DAX Functions – SUMX(), CALCULATE(), FILTER(), ALL()
• Power Query – Clean & transform data efficiently
• Data Modeling – Relationships, star & snowflake schemas
• Measures vs. Calculated Columns – When & how to use them
• Time Intelligence – TOTALYTD(), DATESINPERIOD(), PREVIOUSMONTH()
• Custom Visuals – Go beyond default charts
• Drill-Through & Drill-Down – Interactive insights
• Row-Level Security (RLS) – Control data access
• Bookmarks & Tooltips – Enhance dashboard storytelling
• Performance Optimization – Speed up slow reports

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

Free Power BI Resources: 👇 https://news.1rj.ru/str/PowerBI_analyst

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

Hope it helps :)

If you want to Excel as a Data Analyst, master these powerful skills:

• SQL Queries – SELECT, JOINs, GROUP BY, CTEs, Window Functions
• Excel Functions – VLOOKUP, XLOOKUP, PIVOT TABLES, POWER QUERY
• Data Cleaning – Handle missing values, duplicates, and inconsistencies
• Python for Data Analysis – Pandas, NumPy, Matplotlib, Seaborn
• Data Visualization – Create dashboards in Power BI/Tableau
• Statistical Analysis – Hypothesis testing, correlation, regression
• ETL Process – Extract, Transform, Load data efficiently
• Business Acumen – Understand industry-specific KPIs
• A/B Testing – Data-driven decision-making
• Storytelling with Data – Present insights effectively

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

SQL Interview Questions with detailed answers:

1️⃣5️⃣ What is the purpose of COALESCE()?

The COALESCE() function is used to return the first non-NULL value from a list of expressions. It is commonly used to handle missing values in SQL queries.

Why Use COALESCE()?

1️⃣ Replaces NULL values with a default or fallback value.
2️⃣ Prevents NULL-related errors in calculations and reports.
3️⃣ Improves data presentation by ensuring meaningful values appear instead of NULLs.

Example: Replacing NULLs in a Column

SELECT employee_id, name, COALESCE(salary, 0) AS salary FROM employees; 

Here, if salary is NULL, it will be replaced with 0.

Example: Selecting the First Non-NULL Value

SELECT employee_id, COALESCE(phone_number, email, 'No Contact Info') AS contact FROM employees; 

This returns phone_number if available; otherwise, it returns email. If both are NULL, it defaults to 'No Contact Info'.

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 Tableau and become a data visualization expert, master these essential features:

• Calculated Fields – Create custom metrics
• LOD Expressions – FIXED, INCLUDE, EXCLUDE for advanced aggregations
• Table Calculations – RANK(), WINDOW_SUM(), RUNNING_TOTAL()
• Data Blending vs. Joins – Combine data efficiently
• Parameters – Create interactive dashboards
• Dual-Axis & Combined Charts – Advanced visual storytelling
• Filters & Context Filters – Optimize performance
• Dashboard Actions – Make reports interactive
• Storytelling with Data – Present insights effectively
• Performance Optimization – Speed up slow dashboards

Free Tableau Resources: 👇 https://whatsapp.com/channel/0029VasYW1V5kg6z4EHOHG1t

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

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

Hope it helps :)

If you want to Excel in Data Science and become an expert, master these essential concepts:

Core Data Science Skills:

• Python for Data Science – Pandas, NumPy, Matplotlib, Seaborn
• SQL for Data Extraction – SELECT, JOIN, GROUP BY, CTEs, Window Functions
• Data Cleaning & Preprocessing – Handling missing data, outliers, duplicates
• Exploratory Data Analysis (EDA) – Visualizing data trends

Machine Learning (ML):

• Supervised Learning – Linear Regression, Decision Trees, Random Forest
• Unsupervised Learning – Clustering, PCA, Anomaly Detection
• Model Evaluation – Cross-validation, Confusion Matrix, ROC-AUC
• Hyperparameter Tuning – Grid Search, Random Search

Deep Learning (DL):

• Neural Networks – TensorFlow, PyTorch, Keras
• CNNs & RNNs – Image & sequential data processing
• Transformers & LLMs – GPT, BERT, Stable Diffusion

Big Data & Cloud Computing:

• Hadoop & Spark – Handling large datasets
• AWS, GCP, Azure – Cloud-based data science solutions
• MLOps – Deploy models using Flask, FastAPI, Docker

Statistics & Mathematics for Data Science:

• Probability & Hypothesis Testing – P-values, T-tests, Chi-square
• Linear Algebra & Calculus – Matrices, Vectors, Derivatives
• Time Series Analysis – ARIMA, Prophet, LSTMs

Real-World Applications:

• Recommendation Systems – Personalized AI suggestions
• NLP (Natural Language Processing) – Sentiment Analysis, Chatbots
• AI-Powered Business Insights – Data-driven decision-making

Like this post if you need a complete tutorial on essential data science topics! 👍❤️

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

Hope it helps :)

SQL Interview Questions with detailed answers:

1️⃣6️⃣ How do you optimize a slow SQL query?

Optimizing SQL queries is essential for improving database performance. Here are key techniques to speed up slow queries:

1️⃣ Use Indexing
Indexes help the database retrieve data faster. Adding an index on frequently used columns can improve performance.

CREATE INDEX idx_employee_id ON employees(employee_id);

2️⃣ Avoid SELECT *
Fetching unnecessary columns slows down queries. Select only required columns instead of using SELECT *.

SELECT employee_id, name FROM employees; 

3️⃣ Use EXISTS Instead of IN
EXISTS is faster than IN when dealing with subqueries because it stops checking once it finds a match.

SELECT name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id); 

4️⃣ Optimize Joins
Use appropriate join types (INNER JOIN, LEFT JOIN, etc.) and ensure the joined columns are indexed.

SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; 

5️⃣ Use LIMIT for Large Datasets
If you only need a subset of data, use LIMIT to fetch fewer rows.

SELECT * FROM employees LIMIT 100; 

6️⃣ Partition Large Tables
Partitioning helps divide large tables into smaller chunks, improving query performance.

CREATE TABLE employees_2024 PARTITION OF employees FOR VALUES FROM ('2024-01-01') TO ('2024-12-31'); 

7️⃣ Analyze and Use Query Execution Plans
Use EXPLAIN ANALYZE to understand how a query is executed and find bottlenecks.

EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 50000; 

Optimizing queries depends on the database structure and data size.


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

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