Loading W Code...
Comprehensive Deep-Dive into Joins, Window Functions, CTEs & DML Operations
SQL Reference: Complete guide to SQL syntax, execution pipelines, window functions, and recursive query CTEs.
FROM $\\to$ JOIN $\\to$ WHERE $\\to$ GROUP BY $\\to$ HAVING $\\to$ SELECT $\\to$ DISTINCT $\\to$ ORDER BY $\\to$ LIMIT / OFFSET
The SELECT statement reads specified attributes from one or more database tables without mutating underlying disk storage.
SELECT: Specifies projection attributes or expressions.FROM: Identifies target table relations.WHERE: Filters candidate tuples before row aggregation.DISTINCT: Filters duplicate result rows.ORDER BY: Sorts output rows by designated attributes (ASC / DESC).-- Basic Projection with Filtering
SELECT employee_id, first_name, salary
FROM Employees
WHERE salary >= 75000.00
ORDER BY salary DESC;
-- Pattern Matching with Wildcards
SELECT username, email
FROM Users
WHERE email LIKE '%@wcode.edu'
LIMIT 20 OFFSET 0;The INSERT statement populates new records into target relations, validating column constraint rules (NOT NULL, CHECK, FOREIGN KEY).
-- Batch Insertion Syntax
INSERT INTO Employees (first_name, last_name, email, dept_id, salary)
VALUES
('Arjun', 'Sharma', 'arjun@wcode.edu', 10, 95000.00),
('Priya', 'Verma', 'priya@wcode.edu', 20, 88000.00);
-- PostgreSQL Returning Clause
INSERT INTO Orders (customer_id, total_amount)
VALUES (402, 299.50)
RETURNING order_id, created_at;The UPDATE statement alters attribute values in existing rows matching a WHERE clause predicate.
Always verify update predicates with a SELECT query prior to execution. Omitting the WHERE clause updates every row in the target table indiscriminately.
-- Conditional Bulk Salary Adjustment
UPDATE Employees
SET salary = salary * 1.08,
last_reviewed_at = CURRENT_TIMESTAMP
WHERE dept_id = 10 AND performance_rating >= 4;It is vital to distinguish between DML deletion and DDL table dropping:
Removes specified rows while logging individual deletion operations. Can be rolled back inside transactions.
Deallocates data pages directly, instantly wiping all rows while preserving table structure. Resets auto-increment counters.
Completely removes the table definition, indexes, constraints, and data pages from the database dictionary.
-- Deleting Specific Inactive Accounts
DELETE FROM UserSessions
WHERE last_active_at < NOW() - INTERVAL '90 DAYS';
-- Instant Page Deallocation
TRUNCATE TABLE TemporaryStagingBuffer;JOIN operations correlate tuples across tables based on shared attribute relationships.
INNER JOIN: Returns tuples only where the join predicate evaluates to true in both relations.LEFT OUTER JOIN: Preserves all tuples from the left relation, filling missing right attributes with NULL.RIGHT OUTER JOIN: Preserves all tuples from the right relation.FULL OUTER JOIN: Preserves all tuples from both relations.CROSS JOIN: Produces the Cartesian Product ($M \times N$ rows).-- Inner & Left Join Mechanics
SELECT
e.emp_id,
e.first_name,
d.dept_name
FROM Employees e
INNER JOIN Departments d ON e.dept_id = d.dept_id
LEFT JOIN Managers m ON d.manager_id = m.manager_id;Aggregate functions (COUNT, SUM, AVG, MIN, MAX) compute summary statistics over attribute sets.
WHERE: Filters individual rows before grouping occurs.HAVING: Filters grouped aggregate buckets after grouping completes.-- Departmental Average Salary Breakdown
SELECT
dept_id,
COUNT(*) AS total_staff,
AVG(salary) AS avg_dept_salary
FROM Employees
WHERE status = 'ACTIVE'
GROUP BY dept_id
HAVING AVG(salary) > 70000.00;A Subquery is an embedded SQL query nested inside a parent query.
IN, ANY, ALL).EXISTS, NOT EXISTS).-- Correlated Subquery: Find Employees Earning Above Department Average
SELECT e1.first_name, e1.salary, e1.dept_id
FROM Employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM Employees e2
WHERE e2.dept_id = e1.dept_id
);Unlike GROUP BY, Window Functions perform calculations across sliding row frames without collapsing individual rows.
ROW_NUMBER(): Assigns sequential unique integers.RANK(): Assigns rank numbers (leaves gaps for ties).DENSE_RANK(): Assigns consecutive ranks (no gaps).LAG() / LEAD(): Accesses data from previous or next rows without self-joins.-- Finding Top 2 Earners Per Department via DENSE_RANK()
WITH RankedStaff AS (
SELECT
first_name,
dept_id,
salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as salary_rank
FROM Employees
)
SELECT * FROM RankedStaff WHERE salary_rank <= 2;DDL statements define the physical storage structures and database object definitions.
CREATE: Instantiates tables, indexes, views, or schemas.ALTER: Adds, drops, or alters table columns and constraint rules.DROP: Destroys existing database objects.-- DDL Schema Definition
CREATE TABLE Accounts (
account_id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
status VARCHAR(20) DEFAULT 'ACTIVE',
CONSTRAINT chk_positive_balance CHECK (balance >= 0.00),
CONSTRAINT fk_user_acc FOREIGN KEY (user_id) REFERENCES Users(user_id)
);A Common Table Expression (CTE) defines a temporary named result set using the WITH clause, improving query readability and enabling recursive graph queries.
-- Recursive CTE: Traversing Organizational Hierarchy Tree
WITH RECURSIVE OrgChart AS (
-- Anchor Member: Top-level CEO
SELECT emp_id, first_name, manager_id, 1 AS depth
FROM Employees WHERE manager_id IS NULL
UNION ALL
-- Recursive Member: Subordinate Employees
SELECT e.emp_id, e.first_name, e.manager_id, o.depth + 1
FROM Employees e
INNER JOIN OrgChart o ON e.manager_id = o.emp_id
)
SELECT * FROM OrgChart ORDER BY depth, emp_id;Logical Execution Order: FROM $\\to$ WHERE $\\to$ GROUP BY $\\to$ HAVING $\\to$ SELECT $\\to$ ORDER BY.
WHERE vs HAVING: WHERE filters rows prior to aggregation; HAVING filters aggregated row buckets after GROUP BY.
Window Functions: ROW_NUMBER(), DENSE_RANK(), and LAG() compute analytics across partitions without collapsing individual dataset rows.