Advanced SQL concepts

📌 Introduction

As a Business Analyst, mastering basic SQL is important, but knowing advanced SQL concepts allows you to handle complex business queries, perform deep analysis, and collaborate efficiently with technical teams. Below are key advanced SQL concepts that go beyond basic CRUD operations.

---

🔑 Key Advanced SQL Concepts

Concept	Purpose
Joins (INNER, LEFT, RIGHT, FULL)	Combine rows from two or more tables based on related columns.
Subqueries (Nested Queries)	Query within another query to filter or compute data.
Common Table Expressions (CTE)	Create temporary result sets for complex queries.
Window (Analytic) Functions	Perform calculations across a set of rows related to the current row.
Aggregate Functions with GROUP BY and HAVING	Summarize and filter grouped data.
UNION and UNION ALL	Combine results of multiple SELECT statements.
CASE Statements	Conditional logic in SQL queries.
Indexes	Improve query performance on large datasets.
Stored Procedures & Functions	Reusable SQL code blocks for automation and business logic.

---

✅ 1. Joins: Combining Data from Multiple Tables

Types of Joins:

Type	Description
INNER JOIN	Returns records that have matching values in both tables.
LEFT JOIN	Returns all records from the left table, matched records from the right table.
RIGHT JOIN	Returns all records from the right table, matched records from the left table.
FULL JOIN	Returns all records when there is a match in either table.

Example:

sqlCopyEditSELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

---

✅ 2. Subqueries (Nested Queries)

• Query inside another query, used for filtering, calculation, or fetching related data.

Example:

sqlCopyEditSELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

---

✅ 3. Common Table Expressions (CTEs)

• Temporary named result set that improves query readability and reusability.

Syntax:

sqlCopyEditWITH HighSalaryEmployees AS (
    SELECT name, salary FROM employees WHERE salary > 60000
)
SELECT * FROM HighSalaryEmployees;

---

✅ 4. Window (Analytic) Functions

• Perform aggregate-like functions without collapsing rows (e.g., running totals, ranking).

Common Window Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), SUM() OVER(), AVG() OVER()

Example:

sqlCopyEditSELECT name, department_id, 
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS SalaryRank
FROM employees;

---

✅ 5. Aggregate Functions with GROUP BY and HAVING

• GROUP BY groups rows that share a property.

• HAVING filters groups.

Example:

sqlCopyEditSELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

---

✅ 6. UNION and UNION ALL

• Combine result sets of multiple SELECT statements.

Example:

sqlCopyEditSELECT name FROM employees
UNION
SELECT name FROM clients;

🔑 Note: UNION removes duplicates, UNION ALL keeps duplicates.

---

✅ 7. CASE Statements (Conditional Logic)

Example:

sqlCopyEditSELECT name,
       CASE 
           WHEN salary > 70000 THEN 'High'
           WHEN salary BETWEEN 50000 AND 70000 THEN 'Medium'
           ELSE 'Low'
       END AS SalaryCategory
FROM employees;

---

✅ 8. Indexes (Performance Tuning)

• Indexes improve query performance by allowing faster data retrieval.

Example:

sqlCopyEditCREATE INDEX idx_salary ON employees (salary);

⚙️ Note: Indexes speed up read queries but slow down write operations (INSERT, UPDATE, DELETE).

---

✅ 9. Stored Procedures & Functions (Reusable Logic)

• Stored Procedures: Blocks of SQL code stored in the database and executed as needed.

• Functions: Return a value and can be used in SQL expressions.

Stored Procedure Example:

sqlCopyEditCREATE PROCEDURE GetHighSalaryEmployees()
BEGIN
    SELECT * FROM employees WHERE salary > 60000;
END;

Function Example:

sqlCopyEditCREATE FUNCTION GetTotalSalary()
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(salary) INTO total FROM employees;
    RETURN total;
END;

---

🎯 Importance of Advanced SQL for Business Analysts

Reason	Explanation
Handle Complex Data Scenarios	Join multiple tables, filter, and aggregate large datasets.
Perform Deep Analysis	Window functions and CTEs help analyze trends and ranks.
Improve Performance Awareness	Understand indexes and optimization for better query performance.
Communicate Effectively with Developers/DBAs	Collaborate on designing efficient data solutions.

---

🚀 Conclusion

Concept	Purpose
Joins	Combine data from multiple sources.
Subqueries & CTEs	Modular and reusable query components.
Window Functions	Row-wise calculations and analytics.
Aggregate & CASE	Summarization and conditional logic.
Indexes & Performance	Optimize data retrieval.
Stored Procedures	Automate repetitive data tasks.