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

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