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

💰 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!