Structured Query Language (SQL) is the standard declarative implementation language for database query operations. This section covers production-grade queries, syntax, and structures.

DDL & DML Syntax

SQL Joins Deep-Dive

Consider two tables: Employees (ID, Name, ManagerID) and Departments (ID, DeptName).

Self Join Query

Cross Join Query

Aggregations & Group By

SQL

-- Get departments with more than 5 employees and an average salary above 60000
SELECT DeptID, COUNT(*) AS EmpCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DeptID
HAVING COUNT(*) > 5 AND AVG(Salary) > 60000;

Nested & Subqueries

Window Functions

Compute values across a set of table rows related to the current row, without collapsing the results (unlike GROUP BY):

Function	Mechanics	Syntax / Usage
ROW_NUMBER()	Assigns a sequential integer to rows starting at 1.	SELECT Name, Salary, DeptID, DENSE_RANK() OVER ( PARTITION BY DeptID ORDER BY Salary DESC ) AS RankInDept FROM Employees;
RANK()	Assigns rank values. Skips rank integers if duplicate values are found (e.g. 1, 2, 2, 4).
DENSE_RANK()	Assigns rank values consecutively. Does not skip rank integers (e.g. 1, 2, 2, 3).
LEAD() / LAG()	Accesses data columns from rows directly ahead of (LEAD) or behind (LAG) the current row.	-- Compare employee salary to predecessor LAG(Salary, 1) OVER (ORDER BY Salary)

SQL Masterclass