PROVIDER - the shared language of structured data
SQL
SQL has been an ANSI standard since 1986, with ISO/IEC 9075:2023 as the current edition, and describes set queries, window calculations and JSON work declaratively across nearly every relational engine; dialect gaps, optimiser-dependent plans and weak procedural logic are its long-standing boundaries.
4.8pts
Detailed Notes
Pros 5 items
Cons 4 items
Service Overview
SQL (Structured Query Language) was proposed by IBM in the 1970s and became an ANSI standard in 1986, with ISO/IEC 9075:2023 as the current edition. It describes what result is wanted rather than how to compute it, leaving the access path to the database optimiser.
In the languages and frameworks category it is the general-purpose language of data: back-end services, analytics and reporting tools all rely on it.
Key Strengths
- Declarative expression: 1 SELECT covers filtering, grouping, aggregation and ordering with far less code than procedural equivalents
- One standard: ISO/IEC 9075 keeps basic syntax consistent across engines, lowering the cost of moving between them
- Continuously extended: window functions, recursive CTEs, JSON and property graph queries are all standardised now
- Widest ecosystem: nearly every database, BI tool and data platform speaks SQL
- Optimiser cooperation: statistics and indexes let one statement pick different plans as data volume changes
Product Ecosystem
Standards
SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2008, SQL:2011, SQL:2016 and SQL:2023, each adding data types and query capability.
Major implementations
Open source covers MySQL, PostgreSQL, SQLite and MariaDB; commercial covers Oracle Database, SQL Server, DB2 and the cloud data warehouses.
Tools
DBeaver, DataGrip and vendor consoles handle querying, while Flyway, Liquibase and dbt cover migrations, modelling and transformation.
Limitations
- Dialect fragmentation: pagination, date functions, generated keys and casts differ per engine
- Optimiser dependence: the same statement can differ by 1 to 2 orders of magnitude in runtime across engines
- Hard to reuse: stored procedures rarely port, and there is no shared package system
- Weak procedural model: row loops and state machines are clumsy and usually move to a host language
Use Cases
- Transactional reads and writes (★★★★★): almost every back end reaches relational data through SQL
- Analytics and reporting (★★★★★): aggregation, window functions and CTEs do the heavy lifting
- Warehouse modelling (★★★★): tools such as dbt turn SQL into a versioned transformation layer
- High-concurrency key lookups (★★★): KV stores or caches fit point reads better
Pricing
| Item | Price | Notes |
|---|---|---|
| ISO/IEC 9075 standard text | Paid per copy | Sold by ISO; the standard page blocks automated access |
| MySQL / PostgreSQL / SQLite | $0 | Open-source engines deploy directly |
| Cloud database instances | Billed per size | Managed services charge for CPU, storage and traffic |
The language is free; cost concentrates in commercial database licences (billed per core or seat) and in tuning effort.
FAQ
- Standard SQL first or a specific engine? Learn the standard syntax and logical evaluation order first, then the dialect and optimiser quirks of your target engine.
- How do window functions differ from GROUP BY?
GROUP BYcollapses rows into one, while window functions keep the original rows and compute ranks or running totals over them. - Why is a query still slow with an index? Common causes are functions wrapped around indexed columns, implicit casts, or stale statistics; read the execution plan to locate them. More data options are listed under languages and frameworks.
Code Examples
Ranking within groups using a window function
SELECT
department,
employee,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
WHERE status = 'active'
ORDER BY department, salary_rank;
OVER with PARTITION BY attaches aggregates without collapsing rows.
Walking a hierarchy with a recursive CTE
WITH RECURSIVE org AS (
SELECT id, manager_id, name, 1 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.manager_id, e.name, org.depth + 1
FROM employees e
JOIN org ON e.manager_id = org.id
)
SELECT * FROM org ORDER BY depth, id;
WITH RECURSIVE pairs an anchor query with a recursive member, and depth tracks the level.
Community & Resources
- ISO/IEC 9075-2:2023: the current SQL standard, best opened in a browser
- GitHub SQL topic: open scripts, exercises and tooling
- GitHub PostgreSQL topic: the most standards-aligned open-source engine
- GitHub MySQL topic: the most widely deployed open-source engine
- GitHub dbt topic: a framework that turns SQL into engineered pipelines
Articles
- 01 Complete LNMP Stack Setup Guide: Linux + Nginx + MySQL + PHP from scratch 07-18
- 02 Complete website security hardening guide: XSS, CSRF, SQL injection prevention 07-12
- 03 Website database selection guide: MySQL, PostgreSQL, SQLite, NoSQL comparison 07-12
- 04 SQL Injection Defense Best Practices: Protecting Database Security 07-18
- 05 MySQL and PostgreSQL in Production: A Basic Tuning Guide 08-06
- 06 ORM and Database Integration: Prisma, SQLAlchemy, Eloquent Compared and in Practice 08-06
- 07 Making PHP 7 Compatible with mysql_connect at the Code Level 04-03
- 08 WAF Web Application Firewall Guide: Rules Configuration and Attack Prevention 07-18
- 09 Docker Compose LAMP Stack 07-13
- 10 Flask REST API Example 07-13