SQL Basics

Wikipedia · SQL · CC BY-SA 4.0

SQL (Structured Query Language) is the standard language for relational databases. It maps directly to relational algebra: SELECT is projection, WHERE is selection, JOIN is join, UNION is union. SQL adds aggregation (GROUP BY, COUNT, SUM) and ordering (ORDER BY) on top of the algebra.

CREATE and INSERT — defining and populating tables

CREATE TABLE defines a schema: column names, types, and constraints. INSERT adds tuples. We simulate this in Python with sqlite3, which runs entirely in memory.

Python

import sqlite3

conn = sqlite3.connect(":memory:")
c = conn.cursor()

# CREATE TABLE
c.execute("""
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER
    )
""")

# INSERT
c.execute("INSERT INTO users VALUES (1, 'Alice', 30)")
c.execute("INSERT INTO users VALUES (2, 'Bob', 22)")
c.execute("INSERT INTO users VALUES (3, 'Carol', 28)")
c.execute("INSERT INTO users VALUES (4, 'Dave', 35)")
conn.commit()

# Verify
for row in c.execute("SELECT * FROM users"):
    print(row)

SELECT and WHERE — querying data

SELECT chooses which columns to return (projection). WHERE filters which rows to return (selection). Together they implement the two most basic relational algebra operations.

Python

import sqlite3

conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
for row in [(1,'Alice',30),(2,'Bob',22),(3,'Carol',28),(4,'Dave',35)]:
    c.execute("INSERT INTO users VALUES (?,?,?)", row)

# SELECT with WHERE
print("Users over 25:")
for row in c.execute("SELECT name, age FROM users WHERE age > 25"):
    print("  " + str(row[0]) + ", age " + str(row[1]))

print()
print("All names (projection only):")
for row in c.execute("SELECT name FROM users"):
    print("  " + str(row[0]))

JOIN — combining tables

JOIN combines rows from two tables based on a related column. INNER JOIN returns only matching rows. LEFT JOIN returns all rows from the left table, with NULLs where the right table has no match.

Python

import sqlite3

conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
c.execute("CREATE TABLE orders (oid INTEGER PRIMARY KEY, user_id INTEGER, item TEXT)")

for u in [(1,'Alice'),(2,'Bob'),(3,'Carol')]:
    c.execute("INSERT INTO users VALUES (?,?)", u)
for o in [(101,1,'book'),(102,2,'pen'),(103,1,'lamp')]:
    c.execute("INSERT INTO orders VALUES (?,?,?)", o)

# INNER JOIN
print("Inner join:")
for row in c.execute("""
    SELECT users.name, orders.item
    FROM users
    INNER JOIN orders ON users.id = orders.user_id
"""):
    print("  " + str(row[0]) + " ordered " + str(row[1]))

# LEFT JOIN (Carol has no orders)
print()
print("Left join:")
for row in c.execute("""
    SELECT users.name, orders.item
    FROM users
    LEFT JOIN orders ON users.id = orders.user_id
"""):
    print("  " + str(row[0]) + " -> " + str(row[1]))

GROUP BY and aggregate functions

GROUP BY partitions rows into groups. Aggregate functions (COUNT, SUM, AVG, MIN, MAX) compute a single value per group. HAVING filters groups after aggregation, the way WHERE filters rows before aggregation.

Python

import sqlite3

conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute("CREATE TABLE orders (oid INTEGER, user_id INTEGER, amount REAL)")
for o in [(1,1,10.0),(2,1,20.0),(3,2,15.0),(4,1,5.0),(5,2,30.0),(6,3,50.0)]:
    c.execute("INSERT INTO orders VALUES (?,?,?)", o)

# COUNT and SUM per user
print("Orders per user:")
for row in c.execute("""
    SELECT user_id, COUNT(*) as cnt, SUM(amount) as total
    FROM orders
    GROUP BY user_id
"""):
    print("  User " + str(row[0]) + ": " + str(row[1]) + " orders, total $" + str(round(row[2],2)))

# HAVING: only users with total > 20
print()
print("Users with total > 20:")
for row in c.execute("""
    SELECT user_id, SUM(amount) as total
    FROM orders
    GROUP BY user_id
    HAVING total > 20
"""):
    print("  User " + str(row[0]) + ": $" + str(round(row[1],2)))

ORDER BY — sorting results

ORDER BY sorts the output. It is not a relational algebra operation (relations are unordered sets), but SQL adds it as a convenience. ASC for ascending (default), DESC for descending.

Scheme

; ORDER BY is sorting after the relational operations.
; Relations are sets (unordered), but output needs an order.

(define data
  (list
    (list (cons "name" "Carol") (cons "score" 88))
    (list (cons "name" "Alice") (cons "score" 95))
    (list (cons "name" "Bob")   (cons "score" 72))))

(define (get-attr t a) (cdr (assoc a t)))

; Sort by score descending
(define sorted
  (sort data (lambda (a b) (> (get-attr a "score") (get-attr b "score")))))

(display "Sorted by score DESC:") (newline)
(for-each
  (lambda (t)
    (display "  ")
    (display (get-attr t "name"))
    (display ": ")
    (display (get-attr t "score"))
    (newline))
  sorted)

Neighbors

Foundations (Wikipedia)

← Relational Algebra by june.kim Normalization →