SQL (Structured Query Language) is a language for defining, querying and changing tables in a relational database. In quant work, it lets you select a research sample or calculate a position total where the data is stored, before transferring results into an analysis environment. Its scope includes creating tables, inserting records, joining tables, aggregating observations and updating stored values. PostgreSQL's SQL tutorial covers these operations.
A query describes the result you want; the database engine chooses an execution plan. SQL is the language. SQLite and PostgreSQL are database systems with their own SQL dialects, types and transaction behaviour. A query that works in one system may need changes in another.
From positions to a total
Suppose a fictional snapshot holds 2 units of A marked at USD 100 each and 3 units of B marked at USD 50 each. Their values are USD 200 and USD 150, giving USD 350 in total. All prices use the same currency and snapshot; no cash, fees or liabilities are included.
This self-contained example uses Python's standard-library
sqlite3 module and an in-memory database. It requires Python 3.12 or later for the explicit autocommit setting; it was checked with Python 3.14.4 and SQLite 3.53.0.import sqlite3
con = sqlite3.connect(":memory:", autocommit=False)
try:
con.execute("""
CREATE TABLE positions (
symbol TEXT NOT NULL PRIMARY KEY,
qty INTEGER NOT NULL,
price_usd INTEGER NOT NULL
)
""")
con.executemany(
"INSERT INTO positions VALUES (?, ?, ?)",
[("A", 2, 100), ("B", 3, 50)],
)
con.commit()
rows = con.execute("""
SELECT symbol, qty * price_usd AS value_usd
FROM positions
WHERE qty >= ?
ORDER BY symbol
""", (1,)).fetchall()
total = con.execute(
"SELECT SUM(qty * price_usd) FROM positions"
).fetchone()[0]
print(rows)
print(total)
finally:
con.close()
# [('A', 200), ('B', 150)]
# 350SELECT chooses output expressions, FROM names the input table, and WHERE keeps qualifying rows. ORDER BY symbol requests the displayed order; without ORDER BY, SQLite does not promise one. The unique symbol here also removes ordering ties. SUM reduces the position values to one result row. This is a useful way to return a small summary instead of fetching every record. See SQLite's SELECT documentation.
Fictional single-snapshot valuation: multiply within each row, then sum across rows. Units are USD.
The
? placeholders bind Python values separately from SQL syntax. Use them for external values rather than interpolating text into a query. Binding a value does not choose a table or column name; dynamic identifiers need a separately controlled selection. Here autocommit=False and commit() make the write boundary explicit. The in-memory data still disappears when the connection closes. For updates that must succeed together, use a database transaction and an explicit failure policy. These API details follow the Python sqlite3 documentation.A valid query can return a misleading number
Missing values. This schema rejects missing quantities and marks with
NOT NULL. In a nullable table, qty * price_usd is NULL when the price is missing. SQLite's SUM ignores such inputs: the number can look plausible while leaving a holding unvalued. With no non-NULL inputs, SUM returns NULL, which Python receives as None. COUNT(*) counts rows; COUNT(price_usd) counts non-NULL marks. Reconcile them before treating an aggregate as complete. See SQLite's aggregate contracts. Test missingness with IS NULL, rather than = NULL, under SQLite's expression rules.Row meaning and multiplicity. The primary key permits one row per symbol in this deliberately small snapshot. A real positions table may need account, instrument and as-of time in its key. A database join to repeated price keys can multiply holdings before aggregation.
SUM cannot decide which price is economically correct. Establish the intended matches, currency and time policy first.Numeric representation. These small whole-dollar inputs use integer arithmetic. Real feeds need a deliberate price scale, quantity precision and overflow policy; this non-STRICT SQLite table uses type affinity, so an
INTEGER declaration alone does not enforce an integer-only domain. Floating-point calculations can introduce rounding error. Keep units and data-quality checks alongside the query.Continue with a lesson
The SQL lesson builds the filtering and ordering foundation. SQL and Persistent Data develops a keyed price schema and a bounded panel query. Both offer public previews; the full lessons require membership.
Sources
- PostgreSQL 18 — The SQL Language.
- Python 3.14 — sqlite3: binding values, types and transaction control. Current documentation checked separately from the executed Python 3.14.4 runtime.
- SQLite — SELECT, including filtering, join processing and ordering.
- SQLite — Aggregate Functions, especially
SUMandCOUNT. - SQLite — Expressions, especially NULL behaviour.
- SQLite — Datatypes, storage classes and type affinity.