Advanced SQL Concepts – Subqueries and Views Tutorial

Advanced SQL Concepts – Subqueries and Views

Advanced SQL features allow you to write more complex queries and simplify data management. Two important concepts are Subqueries and Views.

1. Subqueries

Definition: A subquery is a query nested inside another SQL query. It is used to perform operations in multiple steps or filter data dynamically.

Syntax:

SELECT column1, column2
FROM table_name
WHERE column3 IN (
    SELECT column3
    FROM other_table
    WHERE condition
);

Example:

SELECT name, age
FROM Student
WHERE dept_id IN (
    SELECT dept_id
    FROM Department
    WHERE dept_name = 'CSE'
);

This fetches students who belong to the 'CSE' department.

2. Views

Definition: A view is a virtual table based on the result of a SQL query. It does not store data physically but provides a way to simplify complex queries.

Syntax:

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

Example:

CREATE VIEW CSE_Students AS
SELECT name, age, dept_id
FROM Student
WHERE dept_id = 1;

Now, you can query the view directly:

SELECT * FROM CSE_Students;
---

Lab Practice Tasks

  1. Create a subquery to find students older than the average age.
  2. Create a view for students of a specific department.
  3. Query the view to display student names and department IDs.

✔ Blogger-ready HTML | Inline CSS | Advanced SQL Concepts Tutorial

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: মন্তব্যসমূহ (Atom)