Databases and SQL Basics

Tables, rows, keys and how relational databases organize data.

A relational database stores data in tables, like spreadsheets with strict rules. Each row is one record and each column is one attribute with a fixed type. SQL (Structured Query Language) is the standard language for asking these databases questions and changing their data. It works, with small dialect differences, in PostgreSQL, MySQL, SQLite, SQL Server and Oracle.

A sample table

Throughout this course we use two tables. customers holds people, and orders holds purchases, each pointing at a customer.

-- customers
-- id | name  | country | age
--  1 | Ada   | UK      | 36
--  2 | Linus | FI      | 28
--  3 | Grace | US      | 45

-- orders
-- id | customer_id | total | status
-- 10 |           1 | 120.0 | paid
-- 11 |           1 |  35.5 | paid
-- 12 |           3 |  80.0 | refunded

Keys

• A primary key uniquely identifies each row (customers.id). It can never be duplicated or null.
• A foreign key is a column that points at another table's primary key (orders.customer_id). This is how tables relate.

SELECT name, country
FROM customers;

name   | country
Ada    | UK
Linus  | FI
Grace  | US

A query names what you want (SELECT) and where it lives (FROM). SQL is declarative: you describe the result, and the database engine works out the fastest way to produce it. Keywords are case-insensitive, but writing them in capitals and ending statements with a semicolon is the convention.

Trying it

The easiest way to practice is SQLite, which needs no server: install it, run sqlite3 practice.db and paste the statements. Online playgrounds such as SQLite Fiddle or DB Fiddle work too.

sqlite3 practice.db
sqlite> CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT, age INTEGER);
sqlite> .tables

SELECT name, age
FROM customers;