Write a note on Sub Query.

Q.5 Write a note on Sub Query.

Answer :- 

Subqueries with INSERT, UPDATE, and DELETE in Oracle.

• A subquery in Oracle is a query nested within another SQL statement.

• It is used to retrieve intermediate data that can be used by the main query for various operations like INSERT, UPDATE, or DELETE.

• Subqueries can enhance the flexibility and functionality of SQL commands.

1. Subqueries with INSERT

• Subqueries can be used with the INSERT statement to populate a table with data retrieved from another table.

Syntax:

• INSERT INTO target_table (column1, column2, …)

  SELECT column1, column2, …

  FROM source_table

  WHERE condition;

Example:

• INSERT INTO archived_employees (employee_id, name, department_id)

  SELECT employee_id, name, department_id

  FROM employees

  WHERE status = ‘inactive’;

2. Subqueries with UPDATE

• Subqueries can be used in the UPDATE statement to dynamically calculate or fetch new values for updating columns in a table.

Syntax:

• UPDATE target_table

  SET column1 = (SELECT value_column

  FROM source_table

  WHERE condition)

  WHERE condition;

Example:

• UPDATE employees

  SET salary = (SELECT AVG(salary)

  FROM employees

  WHERE department_id = 10)

  WHERE department_id = 10;

3. Subqueries with DELETE

• Subqueries can be used in the DELETE statement to determine which rows to remove based on conditions involving data from another table.

Syntax:

• DELETE FROM target_table

  WHERE column IN (SELECT column

  FROM source_table

  WHERE condition);

Example:

• DELETE FROM employees

  WHERE department_id IN (SELECT department_id

  FROM departments

  WHERE location_id = 100);

Types of Subqueries

• Single-Row Subquery: Returns a single value, typically used with operators like =, <, or >.

• Multiple-Row Subquery: Returns multiple values, typically used with operators like IN or ANY.

• Correlated Subquery: Refers to columns in the outer query and is evaluated for each row processed by the main query.