Advanced SQL

Here's a detailed explanation of Subqueries, Views, Indexes, Triggers, Aggregate Functions, Stored Procedures, and Functions — all essential intermediate-to-advanced SQL concepts:

🔄 1. Subqueries

A subquery is a query inside another query. It helps you fetch results based on the outcome of another SQL statement.

🔹 Types:

In WHERE clause:

SELECT name FROM employees WHERE dept_id = 
  (SELECT id FROM departments WHERE name = 'Sales');

In FROM clause:

SELECT avg_salary FROM 
  (SELECT AVG(salary) as avg_salary FROM employees) as temp;

In SELECT clause:

SELECT name, (SELECT COUNT(*) FROM projects WHERE emp_id = e.id) AS project_count 
FROM employees e;

🔸 Use Cases:

Filtering by calculated values
Creating dynamic conditions
Complex joins replacement

🧾 2. Views

A view is a virtual table based on a SQL query. It does not store data — it just saves a query for reuse.

🔹 Example:

CREATE VIEW active_users AS
SELECT id, name FROM users WHERE status = 'active';

SELECT * FROM active_users;

🔸 Benefits:

Simplifies complex queries
Enhances security by exposing limited columns
Helps in abstraction for reporting

🚀 3. Indexes

An index is like a table of contents — it speeds up data retrieval, especially for large tables.

🔹 Syntax:

CREATE INDEX idx_name ON employees(name);

🔸 Types:

Single-column index
Composite index (on multiple columns)
Unique index (prevents duplicate values)

🔸 Pros:

Faster search and filtering
Improves WHERE, ORDER BY, and JOIN performance

⚠️ Cons:

Slower INSERT, UPDATE, DELETE (because indexes must be updated)
Takes additional storage

⏰ 4. Triggers

A trigger is an automatic action in the database that runs in response to an event on a table.

🔹 Syntax:

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
SET NEW.updated_at = NOW();

🔸 Use Cases:

Auto-update created_at or updated_at fields
Enforcing audit logs
Automatically validate or clean data

🧮 5. Aggregate Functions

Aggregate functions process multiple values and return a single value. Often used with GROUP BY.

Function	Description	Example
`COUNT()`	Total number of rows	`SELECT COUNT(*) FROM orders;`
`SUM()`	Total sum of values	`SELECT SUM(salary) FROM employees;`
`AVG()`	Average value	`SELECT AVG(age) FROM users;`
`MAX()`	Highest value	`SELECT MAX(score) FROM tests;`
`MIN()`	Lowest value	`SELECT MIN(score) FROM tests;`

🧠 6. Stored Procedures and Functions

📌 Stored Procedure

A procedure is a reusable block of SQL logic that can perform operations like insert, update, or calculations. Think of it like a function without return.

Example:

CREATE PROCEDURE AddUser(IN username VARCHAR(100))
BEGIN
  INSERT INTO users(name) VALUES (username);
END;

CALL AddUser('maham');

📌 Function

A function is similar to a procedure, but it returns a value and is used within SQL expressions.

Example:

CREATE FUNCTION TotalSalary(emp_id INT)
RETURNS DECIMAL(10,2)
BEGIN
  DECLARE total DECIMAL(10,2);
  SELECT SUM(salary) INTO total FROM payroll WHERE employee_id = emp_id;
  RETURN total;
END;

SELECT TotalSalary(101);

🔸 Use Cases:

Procedures: batch inserts, reporting jobs, automation
Functions: reusing calculations in queries

🧾 Summary Table

Concept	Purpose	Typical Use Case
Subquery	Query inside another query	Conditional filtering
View	Virtual table	Simplifying joins, securing data
Index	Speed up reads	Large table filtering
Trigger	Auto-action on DB change	Audit logging, auto timestamps
Aggregate Functions	One result from many rows	Reports, summaries
Stored Procedure	Logic reuse (no return)	Admin tasks
Function	Logic reuse (with return)	Inline calculations

Let me know if you want real-life project use cases or implementation in MySQL/PostgreSQL!