2 of 19

SUBQUERY

A subquery is a query inside another query. Think of it like a small question inside a bigger question.
The inner query runs first and gives a result.
The outer query uses that result to complete its task.

A subquery in SQL is a query nested inside another SQL query. It allows complex filtering, aggregation and data manipulation by using the result of one query inside another. They are an essential tool when we need to perform operations like:
Filtering: selecting rows based on conditions from another query.
Aggregating: applying functions like SUM, COUNT, AVG with dynamic conditions.
Updating: modifying data using values from other tables.
Deleting: removing rows based on criteria from another query.

3 of 19

WHY USE SUBQUERIES

Subqueries help when you need to:

Get results that depend on other results.
Break complex problems into simpler steps.
Avoid complicated JOIN operations in some cases.

4 of 19

SYNTAX

SELECT column_name

FROM table_name

WHERE column_name OPERATOR

(SELECT column_name

FROM table_name

WHERE condition);

The inner query goes inside parentheses ( ).
It returns a single value, list of values, or table depending on the type of subquery.

5 of 19

SUBQUERIES USING WHERE CLAUSE

SELECT student_name

FROM students

WHERE department_id = (SELECT department_id

FROM department

WHERE department_name='CSE');

6 of 19

SUBQUERY USING FROM CLAUSE

SELECT dept_id, AVG(marks)

FROM (SELECT department_id AS dept_id, marks FROM students) AS temp

GROUP BY dept_id;

7 of 19

TYPES OF SUBQUERIES�

1. Single-Row Subquery

Returns exactly one row as the result.
Commonly used with comparison operators such as =, >, <

Example:

SELECT CustomerName, City

FROM Customer

WHERE ProductID = (

SELECT ProductID

FROM Product

ORDER BY Price DESC

LIMIT 1

);

8 of 19

2. Multi-Row Subquery

Returns multiple rows as the result.
Requires operators that can handle multiple values, such as IN, ANY or ALL

Example:

SELECT ProductName, Price

FROM Product

WHERE ProductID IN (

SELECT ProductID

FROM Customer

WHERE City = 'Delhi'

);

9 of 19

3. Subquery with Aggregation

A dependent subquery: it references columns from the outer query.
Executed once for each row of the outer query, making it slower for large datasets.

Example:

SELECT CustomerName, Email FROM Customer

WHERE ProductID IN (

SELECT ProductID FROM Product

WHERE Price > (

SELECT AVG(Price) FROM Product

)

);

10 of 19

INDEXING

Indexing in MySQL is a technique used to speed up data searching in a database table.
It creates a special data structure (like a sorted list) that helps MySQL jump directly to the required data without scanning the entire table row by row.

Without Index	With Index
MySQL checks every row	MySQL directly jumps to matching row
Slow searching	Faster searching
Table is like a messy heap	Table is like a well-organized catalog

11 of 19

KEY BENEFITS OF INDEXING

✔ Faster searching�✔ Better performance during WHERE, JOIN, ORDER BY, GROUP BY�✔ Useful for columns frequently searched

12 of 19

HOW TO CREATE INDEX

Syntax:

CREATE INDEX index_name

ON table_name(column_name);

13 of 19

TYPES OF INDEXING METHODS

Indexing are of mainly 3 types:
Clustered Indexing
Primary Indexing
Non-clustered or Secondary Indexing

14 of 19

CLUSTERED INDEXING

A Clustered Index is an index where the table data itself is stored in sorted order based on the index key.�So the index and the actual data are in the same structure.

15 of 19

PRIMARY INDEXING

Created automatically on a PRIMARY KEY column.�Values must be unique and not NULL.
Key Features: The data is stored in sequential order, making searches faster and more efficient.

16 of 19

NON-CLUSTERED OR SECONDARY INDEXING

A Non-Clustered Index (also called Secondary Index) is an index where�the index and the actual table data are stored separately.

17 of 19

CLUSTERED VS NON-CLUSTERED INDEX

Feature	Clustered Index	Non-Clustered Index
Data storage	Physically sorted	Stored separately
No. of indexes	Only 1 per table	Many allowed
Speed	Faster for range queries	Slightly slower
Structure	Data + index together	Index + row pointers
Typical creation	On Primary Key	On frequently searched columns

18 of 19

VIEWS

A View is a virtual table created from a SELECT query.
It does not store data permanently; it only displays data from base tables.

Why use Views?

✔ Data security (hide confidential columns)�✔ Simplify complex queries�✔ Easy reusability�✔ Shows updated data automatically

19 of 19

EXAMPLE

CREATE VIEW CustomerProductView AS

SELECT

Customer.CustomerID,

Customer.CustomerName,

Product.ProductName,

Product.Price

FROM

Customer

JOIN

Product ON Customer.ProductID = Product.ProductID;