SQL is a declarative language for interacting with relational database management systems. Instead of describing each retrieval step, you state the result you want, letting the database determine how to produce it. Standardized SQL also lets different applications share database functionality through database libraries. section 10.3.4
A practical SQL query can progressively add selection, ordering, aggregation, and relationships:
- Retrieve every column and row with SELECT * FROM customer;. section 10.3.4
- Select particular columns and sort them with SELECT name FROM customer ORDER BY name DESC;. section 10.3.4
- Summarize results with functions such as COUNT(*), assigning a readable alias to the output. section 10.3.4
- Combine normalized tables with JOIN, then use GROUP BY and ORDER BY to produce related, organized results. section 10.3.4
From customers to orders
A useful report can connect orders to customers, addresses, items, and products. Joining those tables returns each customer's address, order identifier, product name, and quantity, turning normalized relational data into an application-ready result. section 10.3.4
SQL also belongs in the maintenance side of application architecture. Creating a database from models is convenient for a new SQLite database, but changing an existing schema requires SQL statements or migration tools such as Alembic rather than simply recreating the database. section 10.7
In web applications, database queries sit behind handlers: a handler can build a query, add filters for search or permissions, execute it, and render the results. Single-record handlers can join related user data, while update handlers retrieve, validate, modify, and save records. section 11.4.1 section 11.6.1
