SQL

SQL (Structured Query Language) is a standardized language used to manage and manipulate relational databases. It allows users to create, read, update, and delete (CRUD) data within a database. SQL is composed of several key components:

SQL is used across various database management systems (DBMS) such as MySQL, PostgreSQL, SQL Server, and SQLite. It supports querying, filtering, sorting, aggregating, and joining data efficiently.

Key Features & Use Cases

Key Features

Data Management – Enables storing, retrieving, updating, and deleting data in relational databases.
Relational Structure – Uses tables with rows and columns to organize data efficiently.
Querying Data – SELECT statements allow fetching and filtering data.
Data Manipulation – Commands like INSERT, UPDATE, and DELETE modify data.
Data Definition – CREATE, ALTER, and DROP define and modify database schemas.
Joins & Relationships – Combines data from multiple tables using JOIN operations.
Aggregations – Functions like SUM(), COUNT(), AVG(), MIN(), and MAX() perform data summarization.
Indexing – Enhances performance by optimizing search and retrieval.
Security & Access Control – Uses GRANT and REVOKE to manage user permissions.
Transactions & Integrity – Ensures data consistency using COMMIT, ROLLBACK, and constraints like PRIMARY KEY and FOREIGN KEY.

Use Cases

Business & Analytics – Retrieve and analyze data for reporting and decision-making.
Web & App Development – Store and manage user data in backend databases.
Data Warehousing – Aggregate large datasets for business intelligence.
Finance & Banking – Manage transactions, customer data, and security controls.
Healthcare & Research – Store and analyze patient records and medical data.
E-Commerce – Handle product inventories, orders, and customer information.

"Read" SQL Concepts

Below are some common SQL statements, purposes and use case examples.

SQL Commands
Statement	Purpose	Use Case Example
SELECT	Retrieves data from a database.	SELECT * FROM employees;
FROM	Specifies the table to query.	SELECT name FROM employees;
WHERE	Filters records.	SELECT * FROM employees WHERE department = 'Sales';
INNER JOIN	Returns rows when there is a match in both tables.	SELECT employees.name, departments.name FROM employees INNER JOIN departments ON employees.dept_id = departments.id;
LEFT JOIN	Returns all rows from the left table and matched rows from the right table. Returns NULL if no match.	SELECT employees.name, departments.name FROM employees LEFT JOIN departments ON employees.dept_id = departments.id;
RIGHT JOIN	Returns all rows from the right table and matched rows from the left table. Returns NULL if no match.	SELECT employees.name, departments.name FROM employees RIGHT JOIN departments ON employees.dept_id = departments.id;
GROUP BY	Aggregates data across specified columns.	SELECT department, COUNT(*) FROM employees GROUP BY department;
ORDER BY	Sorts the result set.	SELECT name FROM employees ORDER BY hire_date DESC;

Data Manipulation Language (DML)

DML stands for Data Manipulation Language in SQL. It refers to the set of commands used to manage and manipulate the actual data stored in a database.

These commands let you work with the underlying data by inserting new records, updating existing ones, or removing unwanted rows. Unlike DDL (Data Definition Language) commands, which change the structure of database objects (tables, indexes, etc.), DML commands specifically focus on modifying and retrieving the data itself.

DML Commands
Statement	Purpose	Use Case Example
INSERT	Adds new data.	INSERT INTO employees (name, department) VALUES ('Jane Doe', 'Marketing');
UPDATE	Modifies existing data.	UPDATE employees SET department = 'HR' WHERE id = 1;
DELETE	CAUTION: Removes data.	DELETE FROM employees WHERE id = 1;

Common Table Expressions

Syntax:

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE another_condition;

Explanation:

A Common Table Expression (CTE) is a temporary, named result set that you can reference within a single SQL statement (such as a SELECT, INSERT, UPDATE, or DELETE statement). CTEs are defined using the WITH clause. They help to break down complex queries into smaller, more manageable, and readable parts.

Example 1: Simple CTE to filter data
Let's say you have a table named Employees with columns EmployeeID, FirstName, LastName, and Department. You want to select employees from the 'Sales' department and then further filter those with a last name starting with 'S'.

WITH SalesEmployees AS (
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE Department = 'Sales'
)
SELECT EmployeeID, FirstName, LastName
FROM SalesEmployees
WHERE LastName LIKE 'S%';

Example 2: CTE with aggregation
Suppose you have an Orders table with OrderID, CustomerID, and OrderAmount. You want to find customers who have placed more than one order and then display their total order amount.

WITH CustomerOrderCounts AS (
    SELECT CustomerID, COUNT(*) AS NumberOfOrders, SUM(OrderAmount) AS TotalAmount
    FROM Orders
    GROUP BY CustomerID
    HAVING COUNT(*) > 1
)
SELECT CustomerID, TotalAmount
FROM CustomerOrderCounts;

Window Functions

Syntax:

SELECT
    column1,
    column2,
    window_function(column) OVER (
       [PARTITION BY partition_column(s)]
       [ORDER BY order_column(s) [ASC|DESC]]
       [ROWS|RANGE frame_clause]
    ) AS window_function_result
FROM
    table_name;

Explanation:
Window functions perform calculations across a set of table rows that are related to the current row. This set of rows is called the "window". Unlike aggregate functions that collapse multiple rows into a single output row, window functions return a value for each row of the query. The OVER() clause defines the window.

PARTITION BY: Divides the rows into partitions (groups) to which the window function is applied separately.
ORDER BY: Specifies the order of rows within each partition. This is often used with ranking and analytical window functions.
ROWS/RANGE frame_clause: Defines the set of rows within the partition that will be used for the calculation (e.g., the current row and the previous row).

Example 1: Using ROW_NUMBER() to assign a rank within a partition
Consider a Scores table with StudentID, Subject, and Score. You want to assign a rank to each student's score within each subject.

SELECT
    StudentID,
    Subject,
    Score,
    ROW_NUMBER() OVER (PARTITION BY Subject ORDER BY Score DESC) AS RankWithinSubject
FROM
    Scores;

Example 2: Using AVG() as a window function to calculate the average score across all students
Using the same Scores table, you want to see each student's score along with the overall average score for each subject.

SELECT
    StudentID,
    Subject,
    Score,
    AVG(Score) OVER (PARTITION BY Subject) AS AverageScore
FROM
    Scores;

Example 3: Using LAG() to access the previous row's value
Suppose you have a SalesData table with SaleDate and Revenue. You want to calculate the revenue difference compared to the previous day.

SELECT
    SaleDate,
    Revenue,
    LAG(Revenue, 1, 0) OVER (ORDER BY SaleDate) AS PreviousDayRevenue,
    Revenue - LAG(Revenue, 1, 0) OVER (ORDER BY SaleDate) AS RevenueDifference
FROM
    SalesData;