𝗕𝗘𝗦𝗧 𝗙𝗥𝗘𝗘 𝗣𝗬𝗧𝗛𝗢𝗡 𝗖𝗘𝗥𝗧𝗜𝗙𝗜𝗖𝗔𝗧𝗜𝗢𝗡𝗦 𝗜𝗡 𝟮𝟬𝟮𝟲! 🎓

Discover some of the best FREE Python learning and certification opportunities

✅ Free Python Learning Resources
✅ Beginner-Friendly Courses
✅ Practical Programming Knowledge
✅ Explore Available Certificates & Badges
✅ Strengthen Your Resume & LinkedIn Profile

🔗 𝗟𝗶𝗻𝗸 :- 👇

https://pdlink.in/4iVDpmN

📌 SAVE this post & SHARE with friends preparing for tech careers! 🔥

🚀 Data Science Roadmap 2026

📍 Phase 3: SQL for Data Science

📖 Topic 10: UNION and UNION ALL

In this lesson, we'll learn how to combine data from multiple tables using UNION and UNION ALL.

🔹 1. What is UNION in SQL?

UNION is used to combine the results of two or more SELECT queries into a single result.

It combines rows vertically, meaning one result is added below another.

For example, imagine you have two tables containing customer information.

Table 1: customers_2025

• 101: Alice

• 102: Bob

Table 2: customers_2026

• 103: Charlie

• 102: Bob

You want to combine customers from both tables.

SELECT customer_id, name
FROM customers_2025

UNION

SELECT customer_id, name
FROM customers_2026;

Result:

• 101: Alice

• 102: Bob

• 103: Charlie

Notice that Bob appears only once.

Why?

Because UNION automatically removes duplicate rows.

🔹 2. What is UNION ALL?

UNION ALL also combines results from multiple SELECT queries.

However, it does not remove duplicates.

Example:

UNION ALL

• 101: Alice

• 102: Bob

• 103: Charlie

• 102: Bob

Bob appears twice because UNION ALL keeps duplicate rows.

🔹 3. Difference Between UNION and UNION ALL

• Removes duplicate rows: Keeps duplicate rows

• Performs duplicate elimination: Does not eliminate duplicates

• Can require more processing: Usually faster

• Useful for unique results: Useful when all records are needed

Important: Prefer UNION ALL when you need every record and don't need duplicate removal.

🔹 4. Rules of UNION

There are some important rules you must follow when using UNION.

Rule 1: Same number of columns

Both SELECT statements must return the same number of columns.

Correct:

SELECT customer_id, name
FROM customers_2025

UNION

SELECT customer_id, name
FROM customers_2026;

Incorrect:

SELECT customer_id, name
FROM customers_2025

UNION

SELECT customer_id
FROM customers_2026;

The second query has only one column.

Rule 2: Columns must have compatible data types

For example, combining an integer column with another integer column is valid.

The corresponding columns should have compatible data types.

Rule 3: Column order matters

SQL combines columns based on their position, not their names.

SELECT customer_id, name
FROM customers_2025

UNION

SELECT name, customer_id
FROM customers_2026;

This is incorrect if the corresponding data types are incompatible.

Rule 4: ORDER BY is generally placed at the end

SELECT customer_id, name
FROM customers_2025

UNION

SELECT customer_id, name
FROM customers_2026

ORDER BY customer_id;

This sorts the final combined result.

🔹 5. Using WHERE with UNION

You can filter records separately in each SELECT query.

Get customers from Pune from both tables.

SELECT customer_id, name, city
FROM customers_2025
WHERE city = 'Pune'

UNION 

SELECT customer_id, name, city
FROM customers_2026
WHERE city = 'Pune';

Each SELECT applies its own filtering condition before the results are combined.

🔹 6. UNION with Different Tables

You don't need to combine only tables with the same name or structure.

You can combine data from different tables as long as the number and types of corresponding columns are compatible.

Suppose you have:

• online_orders

• store_orders

Both contain order ID, customer ID, and order amount.

SELECT order_id, customer_id, amount
FROM online_orders

UNION ALL

SELECT order_id, customer_id, amount
FROM store_orders;

This combines online and physical store orders into one result.

🔹 7. UNION vs JOIN

This is a very important SQL interview concept.

UNION combines rows vertically.

JOIN combines columns horizontally by matching related records.

• UNION: Combine customer records from two different tables.

• JOIN: Combine customer details with their order details using customer ID.

🔹 8. Real-World Data Science Use Cases

UNION and UNION ALL are useful in many data analysis tasks.

• Combining monthly sales data from different tables.

• Merging customer records from different regions.

• Combining online and offline transactions.

• Consolidating historical and current records.

• Creating a unified dataset for reporting.

• Combining data collected from different sources.

Example: Combining sales from three regions.

SELECT product_id, sales
FROM north_sales

UNION ALL

SELECT product_id, sales
FROM south_sales

UNION ALL

SELECT product_id, sales
FROM west_sales;

This creates one combined sales dataset.

🔹 9. Common Mistakes to Avoid

• Using different numbers of columns in SELECT statements.

• Combining incompatible data types.

• Forgetting that UNION removes duplicate rows.

• Assuming UNION matches columns by their names.

• Using ORDER BY between individual SELECT statements instead of at the end.

• Using UNION when UNION ALL is sufficient, causing unnecessary duplicate elimination.

🔹 10. Interview Questions

Q1. What is UNION in SQL?

UNION combines the results of multiple SELECT statements and removes duplicate rows.

Q2. What is UNION ALL?

UNION ALL combines results while keeping duplicate rows.

Q3. Which is generally faster: UNION or UNION ALL?

UNION ALL is generally faster because it does not perform duplicate elimination.

Q4. Can UNION combine more than two SELECT statements?

Yes. You can combine multiple SELECT statements using UNION or UNION ALL.

Q5. Does UNION require the same column names?

No. It requires the same number of columns with compatible data types in corresponding positions.

🎯 Practice Questions

• Create a query to combine customer records from two tables using UNION.

• Combine two sales tables using UNION ALL.

• Combine employee records from three different regional tables.

• Find unique product IDs from two product tables using UNION.

• Combine online and offline orders while keeping duplicate records.

🎯 Key Takeaways

• UNION combines results vertically.

• UNION removes duplicate rows.

• UNION ALL keeps duplicate rows.

• Both require the same number of columns.

• Corresponding columns must have compatible data types.

• UNION and JOIN serve different purposes.

• UNION ALL is generally faster when duplicates don't need to be removed.

Double Tap ❤️ For More

𝗙𝘂𝗹𝗹 𝗦𝘁𝗮𝗰𝗸 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗺𝗲𝗻𝘁 𝘄𝗶𝘁𝗵 𝗚𝗲𝗻𝗔𝗜 - 𝗣𝗮𝘆 𝗔𝗳𝘁𝗲𝗿 𝗣𝗹𝗮𝗰𝗲𝗺𝗲𝗻𝘁 😍

💰 Highest Salary: ₹41 LPA
📈 Average Salary: ₹7.4 LPA
🎓 2,000+ Students Placed
🏢 500+ Hiring Partners

𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗡𝗼𝘄 👇 :-

https://pdlink.in/3SuUeuD

🔥 Take the first step towards your high-paying tech career in 2026!

𝗝.𝗣. 𝗠𝗢𝗥𝗚𝗔𝗡 𝗙𝗥𝗘𝗘 𝗩𝗜𝗥𝗧𝗨𝗔𝗟 𝗣𝗥𝗢𝗚𝗥𝗔𝗠𝗦 𝟮𝟬𝟮𝟲! 🎓

Explore J.P. Morgan's FREE Virtual Job Simulation Programs

✅ 100% FREE Virtual Learning
✅ Work on Practical, Industry-Based Tasks
✅ Learn at Your Own Pace
✅ Build Valuable Professional Skills

🎯 Perfect For:- College Students | Freshers | Graduates | Job Seekers

🔗 𝗟𝗶𝗻𝗸 👇:-

https://pdlink.in/3X8KJmE

⚡ Learn New Skills • Gain Practical Exposure • Boost Your Career!

🚀 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 📊

Learn Excel, SQL, Python & Power BI Live Online Data Analytics Program

💰 Highest Salary: ₹12.6 LPA
📈 Average Salary: ₹5.7 LPA
🏢 500+ Partner Companies

𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄👇 :-

https://pdlink.in/45vk5ph

🔥 Turn your interest in data into practical, job-ready skills!

🚀 Data Science Roadmap 2026

📍 Phase 3: SQL for Data Science

📖 Topic 11: Views in SQL

Guys, today we'll learn about one of the useful SQL concepts: Views.

Views help us simplify complex queries, organize data, and create reusable SQL logic.

🔹 1. What is a View in SQL?

A View is a virtual table created using the result of a SQL query.

It does not normally store the actual data separately. Instead, it stores the SQL query definition and retrieves data from the underlying tables when accessed.

Example:

Suppose you have an employees table with thousands of records.

You frequently need only employee names, departments, and salaries.

Instead of writing the same SELECT query repeatedly, you can create a View.

CREATE VIEW employee_details AS
SELECT employee_id,
       employee_name,
       department,
       salary
FROM employees;

Now you can access it like a regular table:

SELECT *
FROM employee_details;

🔹 2. Why Do We Use Views?

Views are useful for several reasons:

• Simplify complex SQL queries.

• Reuse frequently executed queries.

• Restrict access to sensitive columns.

• Organize data for reporting.

• Provide a consistent structure for analysts.

• Make dashboards and reports easier to maintain.

🔹 3. How to Create a View

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Create a View containing employees earning more than 60,000.

CREATE VIEW high_salary_employees AS
SELECT employee_id,
       employee_name,
       salary
FROM employees
WHERE salary > 60000;

Access the View:

SELECT *
FROM high_salary_employees;

The View displays only employees matching the condition.

🔹 4. How to Update a View

If you want to change the query behind an existing View, you can use CREATE OR REPLACE VIEW in databases that support it.

CREATE OR REPLACE VIEW employee_details AS
SELECT employee_id,
       employee_name,
       department,
       salary,
       joining_date
FROM employees;

This replaces the existing View definition.

Note: SQL syntax varies by database. SQL Server commonly uses CREATE OR ALTER VIEW instead.

🔹 5. How to Delete a View

If you no longer need a View, use DROP VIEW.

DROP VIEW employee_details;

This removes the View definition.

Important: Dropping a View does not delete the original table or its records.

🔹 6. Views with JOIN

You can create Views using multiple tables and JOIN operations.

Suppose you have two tables:

• employees

• departments

Create a View combining employee and department details.

CREATE VIEW employee_department AS
SELECT e.employee_id,
       e.employee_name,
       d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

Now you can access the combined information:

SELECT *
FROM employee_department;

This saves you from repeatedly writing the JOIN query.

🔹 7. Views with GROUP BY

You can also create Views using aggregate functions.

Create a View showing average salary by department.

CREATE VIEW department_salary AS
SELECT department,
       AVG(salary) AS average_salary,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Access it:

SELECT *
FROM department_salary;

This is especially useful for business reporting.

🔹 8. What is an Updatable View?

An Updatable View allows certain changes to be made through the View, such as:

• INSERT

• UPDATE

• DELETE

CREATE VIEW pune_employees AS
SELECT employee_id,
       employee_name,
       city
FROM employees
WHERE city = 'Pune';

You may be able to update records through this View:

UPDATE pune_employees
SET city = 'Mumbai'
WHERE employee_id = 101;

Important: The underlying table may also be updated.

Not all Views are updatable. Views containing GROUP BY, aggregate functions, DISTINCT, or complex JOINs often have restrictions. Exact rules depend on the database.

🔹 9. View vs Table vs Materialized View

These are important concepts for interviews.

Table

• Stores actual data.

• Data is physically stored in the database.

• Supports regular data operations.

View

• Stores a query definition.

• Normally retrieves data from underlying tables.

• Useful for simplifying queries and controlling access.

Materialized View

• Stores the result of a query physically.

• Can make repeated analytical queries faster.

• Needs refreshing to reflect changes in underlying data.

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Note: Materialized View syntax and refresh behavior depend on the database. For example, PostgreSQL supports this syntax.

🔹 10. Real-World Data Science Use Cases

Views are commonly used in data analytics and reporting.

• Creating customer summary datasets.

• Preparing data for Power BI dashboards.

• Combining multiple tables for reporting.

• Hiding sensitive employee information.

• Creating reusable sales reports.

• Simplifying complex business queries.

A company wants to create a sales summary for its reporting team.

CREATE VIEW monthly_sales AS
SELECT
    YEAR(order_date) AS sales_year,
    MONTH(order_date) AS sales_month,
    SUM(amount) AS total_sales
FROM orders
GROUP BY
    YEAR(order_date),
    MONTH(order_date);

The reporting team can use this View without rewriting the aggregation query.

🔹 11. Common Mistakes to Avoid

• Assuming a normal View stores data physically.

• Thinking DROP VIEW deletes the original table.

• Assuming every View can be updated.

• Forgetting that changes in underlying tables can affect View results.

• Creating unnecessarily complicated Views.

• Assuming Views automatically improve query performance.

🔹 12. SQL Interview Questions

Q1. What is a View in SQL?

A View is a virtual table based on the result of a SQL query.

Q2. Does a View store actual data?

A normal View generally stores the query definition, not a separate copy of the data.

Q3. Can we create a View using JOIN?

Yes. A View can combine multiple tables using JOIN.

Q4. What is a Materialized View?

A Materialized View physically stores the result of a query and can be refreshed when needed.

Q5. Can we update data through a View?

Yes, if the View satisfies the database's updatability rules.

🎯 Practice Questions

• Create a View displaying employee names and departments.

• Create a View for employees earning more than 50,000.

• Create a View combining customers and orders.

• Create a View showing total sales by product.

• Delete an existing View using DROP VIEW.

🎯 Key Takeaways

• A View is a virtual table based on a SQL query.

• CREATE VIEW creates a View.

• CREATE OR REPLACE VIEW modifies its definition in supported databases.

• DROP VIEW removes a View.

• Views can use JOIN, GROUP BY, and aggregate functions.

• Materialized Views physically store query results.

• Views are useful for reporting, analytics, and data security.

Double Tap ❤️ For More

🚀 𝗔𝗜 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 😍| 𝗕𝗲𝗰𝗼𝗺𝗲 𝗮𝗻 𝗔𝗜 𝗘𝗻𝗴𝗶𝗻𝗲𝗲𝗿

This AI engineering course online is structured around AI fundamentals, full-stack development, real-world AI projects

💫2000+ Students Placed
🎥 Live Classes + Session Recordings
👨‍💻 1:1 Mentorship & Doubt Support
💼 Interview Preparation & Placement Assistance

𝗕𝗼𝗼𝗸 𝗮 𝗙𝗥𝗘𝗘 𝗗𝗲𝗺𝗼👇:-
​
https://pdlink.in/4fWJVID

🔥 Take the next step towards building your career in AI!

🚀 Data Science Roadmap 2026

📍 Phase 3: SQL for Data Science

📖 Topic 12: Subqueries in SQL

Hi guys! Today, let's understand one of the most important SQL concepts: Subqueries.

Subqueries are frequently used in SQL interviews and real-world data analysis. They help us solve complex problems by writing one query inside another query.

🔹 1. What is a Subquery?

A Subquery is a SQL query written inside another SQL query.

It is also called an inner query or nested query.

The outer query uses the result returned by the inner query.

Example:

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Let's understand this step by step:

• The inner query calculates the average salary.
• The outer query retrieves employees whose salaries are greater than that average.

This is the basic idea behind subqueries.

🔹 2. Basic Syntax of a Subquery

SELECT column_name
FROM table_name
WHERE column_name operator (
    SELECT column_name
    FROM another_table
    WHERE condition
);

A subquery is generally enclosed within parentheses.

It can be used in different parts of a SQL statement, depending on the query.

🔹 3. Types of Subqueries

Let's understand the important types.

1. Single-Row Subquery

Returns only one row.

Example: Find employees earning more than 70,000.

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT 70000
);

Another common example uses an aggregate:

2. Multiple-Row Subquery

Returns multiple rows.

Example: Find employees working in departments located in Mumbai.

SELECT employee_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Mumbai'
);

Here, the inner query may return multiple department IDs.

3. Multiple-Column Subquery

Returns more than one column.

SELECT *
FROM employees
WHERE (department_id, job_role) IN (
    SELECT department_id, job_role
    FROM approved_positions
);

This compares combinations of department ID and job role.

Note: Row-value comparison syntax is supported by several databases, including PostgreSQL and MySQL. Syntax varies across database systems.

🔹 4. Subquery Using IN

IN checks whether a value matches any value returned by the subquery.

Example: Find employees belonging to the IT or HR departments.

SELECT employee_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name IN ('IT', 'HR')
);

Use IN when the inner query can return multiple values.

🔹 5. Subquery Using NOT IN

NOT IN returns records whose values do not match the values returned by the inner query.

Example: Find employees who are not assigned to departments listed in the departments table.

SELECT employee_name, department_id
FROM employees
WHERE department_id NOT IN (
    SELECT department_id
    FROM inactive_departments
);

Important: Be careful when the subquery returns NULL. A NULL inside a NOT IN result can cause the condition to evaluate as unknown and return no matching rows. NOT EXISTS is often a safer alternative.

🔹 6. Subquery Using EXISTS

EXISTS checks whether the subquery returns at least one row.

Example: Find customers who have placed at least one order.

SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

If a matching order exists, the customer is included.

Remember: EXISTS checks whether a matching row exists. It does not need to return actual column values.

🔹 7. Subquery Using NOT EXISTS

NOT EXISTS checks whether the subquery returns no matching rows.

Example: Find customers who have never placed an order.

SELECT customer_id, customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

This is a common technique for finding missing relationships between tables.

🔹 8. Subquery in SELECT

You can also use a subquery inside the SELECT statement.

Example: Display each employee's salary along with the company's average salary.

SELECT
    employee_name,
    salary,
    (
        SELECT AVG(salary)
        FROM employees
    ) AS average_salary
FROM employees;

The average salary is calculated by the inner query and displayed alongside each employee.

🔹 9. Subquery in FROM

A subquery can also be used inside the FROM clause.

This is commonly called a derived table.

Example: Find departments where the average salary is greater than 70,000.

SELECT department, average_salary
FROM (
    SELECT
        department,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department
) AS department_summary
WHERE average_salary > 70000;

Here:

• The inner query calculates the average salary by department.
• The outer query filters departments based on that result.

🔹 10. What is a Correlated Subquery?

A Correlated Subquery is a subquery that depends on values from the outer query.

Unlike a regular subquery, it references a column from the outer query.

Example: Find employees earning more than the average salary of their own department.

SELECT e.employee_name,
       e.department,
       e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e.department
);

How does it work?

• The outer query selects an employee.
• The inner query calculates the average salary for that employee's department.
• The outer query checks whether the employee earns more than that average.

This comparison is performed for each outer employee row conceptually.

🔹 11. Subquery vs JOIN

Both Subqueries and JOINs can solve similar problems, but their approaches are different.

• Query inside another query: Combines tables
• Uses results from an inner query: Uses matching conditions
• Useful for existence checks and comparisons: Useful for retrieving related columns
• Can be nested: Can combine multiple tables

Example using a Subquery:

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Mumbai'
);

Equivalent approach using JOIN:

SELECT e.employee_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id
WHERE d.location = 'Mumbai';

Both can return the same employee names, assuming the department matching logic is equivalent.

Important: Neither approach is always faster. Performance depends on the database optimizer, indexes, data size, and query structure.

🔹 12. Real-World Data Science Use Cases

Subqueries are useful in data analysis for:

• Finding customers spending more than average.
• Identifying products with sales above a certain threshold.
• Finding employees earning more than their department average.
• Identifying customers who never placed orders.
• Finding departments with above-average employee counts.
• Comparing individual performance against overall business performance.

🔹 13. Common Mistakes to Avoid

• Forgetting parentheses around subqueries.
• Using

=

when the inner query returns multiple rows.
• Using

NOT IN

without considering NULL values.
• Confusing correlated and non-correlated subqueries.
• Returning multiple columns when the comparison expects one.
• Using unnecessary nested subqueries that make queries difficult to understand.

Double Tap ❤️ For More

𝗚𝗢𝗢𝗚𝗟𝗘 𝟱 𝗙𝗥𝗘𝗘 𝗧𝗘𝗖𝗛 𝗖𝗢𝗨𝗥𝗦𝗘𝗦 𝟮𝟬𝟮𝟲! 🎓💻

Explore 5 FREE Google learning courses to build in-demand technical skills and strengthen your resume! 🚀

✅ FREE Learning Resources
✅ Beginner-Friendly Courses
✅ Learn at Your Own Pace
✅ Explore Hands-On Learning Opportunities
✅ Build Skills for AI, ML & Cloud Careers

🔗 𝗟𝗶𝗻𝗸 :- 👇

https://pdlink.in/4kMcA5X

⚡ Don't wait to upgrade your skills — start learning today!