13-06-2025 Oracle 04:30PM
______________________________

Sub-Query: 
The Query inside another Query is called a Subquery (also known as Inner Query).

Syntax:
// Outer Query
// Condition
// (Inner Query)

SELECT column1, column2
FROM table1
WHERE columnX OPERATOR (
    SELECT columnY
    FROM table2
    WHERE condition
);

___________________________________________________________________________________________


Note:
- The Inner Query executes first.
- Its result is passed to the Outer Query for further processing.


Where Subqueries Can Be Used:
- In WHERE clause
- In FROM clause (as derived table)
- In SELECT clause (for calculated fields)

Important Points:
- Always enclose subqueries in parentheses `()`.
- Ensure that the subquery returns the appropriate number of values (single or multiple).
- Use operators like =, <, > for single-row subqueries.
- Use IN, EXISTS, ANY, ALL for multi-row subqueries.

___________________________________________________________________________________________

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    salary INT
);

INSERT INTO employees VALUES (1, 'ashokit', 20000);
INSERT INTO employees VALUES (2, 'vijay', 30000);
INSERT INTO employees VALUES (3, 'harsha', 40000);

SELECT * FROM employees;
___________________________________________________________________________________________

-- Subquery Example 1: Who earns more than average salary
SELECT name, salary
FROM employees
WHERE salary > 
(SELECT AVG(salary) FROM employees);


-- Subquery Example 2: Who has the maximum salary
SELECT name, salary
FROM employees
WHERE salary = 
(SELECT MAX(salary) FROM employees);

___________________________________________________________________________________________