Catalog
concept#Data#Platform#Architecture#Software Engineering

Structured Query Language

SQL is a standardized declarative language for defining, querying, and manipulating relational data in database systems.

Structured Query Language (SQL) is a declarative language for defining, querying, and manipulating relational data.
Established
Medium

Classification

  • Medium
  • Technical
  • Architectural
  • Intermediate

Technical context

Application backend (ORMs or DB clients)Data warehouse and ETL pipelinesMonitoring and observability tools

Principles & goals

Declarative queries over imperative stepsSchema-first design and normalizationSet-oriented processing instead of row-by-row loops
Build
Enterprise, Domain, Team

Use cases & scenarios

Compromises

  • Missing indexing leads to slow queries
  • Unsafe dynamic SQL generation can allow SQL injection
  • Monolithic schemas hinder scaling and development
  • Use prepared statements instead of dynamic SQL for security
  • Regularly review and adapt indexing strategies
  • Profile and refactor long-running queries

I/O & resources

  • Relational schema data sources
  • Business requirements for queries
  • Access and authorization models
  • Query results and reports
  • Transactional state changes
  • Metrics and monitoring data

Description

Structured Query Language (SQL) is a declarative language for defining, querying, and manipulating relational data. It standardizes operations such as SELECT, INSERT, UPDATE, DELETE, and supports transactions, joins and aggregation. SQL underpins relational database systems and shapes data modeling, integrity constraints and performance considerations.

✔

  • Broad standardization and portability between systems
  • Expressive query and aggregation capabilities
  • Support for transactions and integrity constraints

✖

  • Not ideal for very flexible schemaless data models
  • Differences between dialects cause porting effort
  • Complex queries can cause performance bottlenecks

  • Average query latency

    Average execution time of typical queries as an indicator of performance.

  • Transactions per second

    Number of completed transactions per time unit to measure throughput.

  • Cache hit rate

    Proportion of queries served from cache to evaluate efficiency.

Analytical query with JOIN and aggregation

Uses multiple tables, performs joins and GROUP BY to calculate revenue by product category.

Transactional update with rollback

Example of an ACID-compliant transaction that updates multiple tables and rolls back on error.

Schema migration with migration script

Illustration of a schema change including migration and backout scripts for safe rollout.

1

Capture requirements and data model

2

Design and normalize schemas

3

Plan indexes and partitioning

4

Implement queries and analyze with EXPLAIN

⚠️ Technical debt & bottlenecks

  • Legacy schemas lacking normalization
  • Ad-hoc indexes without monitoring
  • Outdated dialect-specific queries
IndexingJoins on large datasetsLock contention
  • Dynamic SQL with unsafe parameters causing injection
  • Missing indexes on large joins causing timeouts
  • Using SQL for highly schemaless, document-based data
  • Underestimating dialect differences during porting
  • Incorrect use of transaction isolations
  • Excessive indexing without query analysis
Relational data modelingSQL query optimizationTransaction and lock management
Data consistency and integrityQuery performance and latencyPortability between database dialects
  • • Dialect differences between implementations
  • • Hardware and I/O limits of the database instance
  • • Regulatory requirements for data storage