SQL PLSQL | Most Important Concepts

Here’s an overview of these SQL concepts:

1. Self-Join

• Definition: A self-join is a join operation in which a table is joined with itself. This is useful when you want to compare rows within the same table.
• Use Case: Commonly used for hierarchical data (e.g., employees with managers) or comparing rows within the same table.

Example:

SELECT A.employee_id, A.employee_name, B.employee_name AS manager_name
FROM employees A
JOIN employees B ON A.manager_id = B.employee_id;

This example assumes that each employee has a manager_id pointing to another employee_id.

2. Inner Join

• Definition: An inner join combines rows from two tables based on a specified condition. It returns only rows where there is a match in both tables.
• Use Case: Useful for retrieving related data across multiple tables (e.g., customers and their orders).

Example:

SELECT customers.customer_name, orders.order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

This returns customers who have placed orders, excluding customers without orders.

3. Clustered Index

• Definition: A clustered index determines the physical order of data in a table. Only one clustered index can exist per table since data rows are stored in that order.
• Use Case: Useful for primary key columns or columns frequently queried for range-based searches.

Example:

CREATE CLUSTERED INDEX idx_customer_id ON customers(customer_id);

This sorts the customers table by customer_id, making queries faster for this column.

4. Non-Clustered Index

• Definition: A non-clustered index creates a separate structure that holds pointers to the physical data in the table. Multiple non-clustered indexes can exist on a table.
• Use Case: Useful for columns frequently used in search or filter conditions, especially when multiple columns need indexing.

Example:

CREATE NONCLUSTERED INDEX idx_customer_name ON customers(customer_name);

This improves search performance on customer_name without altering the table's physical order.

5. Analytical Functions

Analytical functions perform calculations across a set of table rows that are related to the current row.

RANK()

Definition: Assigns a unique rank number to each row within a partition, with gaps for ties (if two rows have the same value, they get the same rank, and the next rank is skipped).

Example:

SELECT employee_name, department_id, 
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

This ranks employees within each department by salary.

ROW_NUMBER()

Definition: Assigns a unique sequential integer to rows within a partition, with no gaps.

Example:

SELECT employee_name, department_id, 
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) 
AS row_num
FROM employees;

This assigns a row number to each employee within each department, ordered by salary.

DENSE_RANK()

Definition: Similar to RANK(), but without gaps for ties (if two rows have the same value, they get the same rank, and the next rank is sequential).

Example:

SELECT employee_name, department_id, 
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) 
AS dense_rank
FROM employees;

This ranks employees without leaving gaps for ties.

6. Common Table Expressions (CTEs)

• Definition: A common table expression (CTE) is a temporary result set defined within a SQL query. It is used to simplify complex queries or create reusable subqueries.
• Use Case: Useful for making queries more readable, especially for recursive or multi-step calculations.

Example:

WITH Sales_CTE AS (
  SELECT product_id, SUM(sales_amount) AS total_sales
  FROM sales
  GROUP BY product_id
)
SELECT product_id, total_sales
FROM Sales_CTE
WHERE total_sales > 1000;

This creates a temporary table (Sales_CTE) with aggregated sales data for further querying.

Each of these concepts helps in optimizing SQL performance, simplifying queries, and structuring data. Let me know if you need more specific examples or further details.