SQL / RELATIONAL FOUNDATIONS
Installing a practice database and running queries
Build a throwaway SQLite practice database from a seed script you can rerun at will, then verify the load with row counts before trusting any query.
What you will learn
- Create a one-file SQLite practice database and load a seed script into it
- Write seed scripts that start with DROP TABLE IF EXISTS so reruns stay idempotent
- Verify a load with row counts instead of assuming the script worked
- Recognise sqlite3 dot-commands as client features that are not SQL
Understanding Installing a practice database and running queries
For practice you want a database you are happy to destroy. SQLite is the easiest choice because the whole database is one ordinary file: sqlite3 practice.db creates it as soon as something is written, copying the file backs it up, and deleting the file deletes the database. With Postgres or MySQL the tables live inside a running server's own storage, so installing a practice database there means creating a named database inside that server first, but the seed SQL you feed it is nearly identical, so practising on SQLite is not a dead end.
The artefact worth keeping is the script, not the file. Put every CREATE TABLE and INSERT in a text file, because within a couple of lessons you will damage the data on purpose and you want returning to a known state to be one command. A rerunnable script has to undo itself, which is why each CREATE is preceded by DROP TABLE IF EXISTS: the second run then starts from empty tables. INSERT has no memory of having run before, it appends unconditionally, so a script that only creates and inserts doubles your data on the second run and quietly makes every count, sum and average wrong.
Finish a load by checking it rather than assuming it. Count the rows in each table and compare against what the script inserts; a wrong number is far cheaper to find now than inside a query you are also unsure about. Be aware too that .read, .tables and .schema are handled by the sqlite3 shell itself, never reach the SQL engine, take no semicolon, and produce a syntax error if pasted into a browser editor or a program. The portable way to inspect what was loaded is to query the catalog the database keeps about itself.
DROP TABLE IF EXISTS enrollment;
DROP TABLE IF EXISTS course;
CREATE TABLE course (
course_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
credits INTEGER NOT NULL
);
CREATE TABLE enrollment (
course_id INTEGER NOT NULL REFERENCES course(course_id),
student TEXT NOT NULL,
grade TEXT
);
INSERT INTO course (course_id, title, credits) VALUES
(1, 'Databases', 4),
(2, 'Statistics', 3),
(3, 'Compilers', 4);
INSERT INTO enrollment (course_id, student, grade) VALUES
(1, 'Ada', 'A'),
(1, 'Grace', 'B'),
(2, 'Ada', NULL),
(3, 'Linus', 'A');
-- verification step: the numbers must match the script
SELECT 'course' AS table_name, COUNT(*) AS row_count FROM course
UNION ALL
SELECT 'enrollment', COUNT(*) FROM enrollment
ORDER BY table_name;A practice database is disposable; the seed script that rebuilds it from nothing is the thing you keep, and it must be safe to run twice.
Worked examples
Why IF NOT EXISTS turns a rerun into duplicate data
Shows what a non-idempotent seed script does the second time it is loaded.
CREATE TABLE IF NOT EXISTS city (name TEXT);
INSERT INTO city (name) VALUES ('Oslo'), ('Lima');
INSERT INTO city (name) VALUES ('Oslo'), ('Lima');
SELECT name, COUNT(*) AS copies
FROM city
GROUP BY name
ORDER BY name;Example explained
Line 1The second INSERT stands in for a second run of the same seed file; nothing in it checks whether those rows are already present.
Line 2CREATE TABLE IF NOT EXISTS succeeds silently on a rerun, and that silence is exactly what lets the old rows survive into the new load.
Line 3Counting per name is how you catch a doubled load; a plain SELECT returning four rows looks perfectly plausible until you count.
Line 4Replacing the first line with DROP TABLE IF EXISTS city; followed by CREATE TABLE city (name TEXT); makes the duplication impossible.
Asking the database what it just loaded
Reads the schema catalog in plain SQL, which is what the shell's .tables shortcut does underneath.
CREATE TABLE note (note_id INTEGER PRIMARY KEY, body TEXT);
CREATE INDEX note_body_idx ON note(body);
SELECT type, name
FROM sqlite_master
WHERE name NOT LIKE 'sqlite_%'
ORDER BY type, name;Example explained
Line 1sqlite_master is a normal table SQLite maintains about itself, so it can be queried with the same SELECT you use on your own data.
Line 2The index appears as its own row: the catalog lists every object the script created, not only tables.
Line 3The NOT LIKE 'sqlite_%' filter hides internal objects such as sqlite_sequence, which is what .tables also does for you.
Line 4Because this is real SQL, it still works from a GUI client or from program code, where .tables would be rejected.
Loading a seed file from the command line
Runs a seed file and checks the result without ever opening an interactive session; seed.sql here holds only the CREATE and INSERT statements from the main example.
sqlite3 practice.db < seed.sql
sqlite3 practice.db "SELECT COUNT(*) FROM enrollment;"Example explained
Line 1The < redirection feeds the file to sqlite3 on standard input, which does the same job as typing .read seed.sql at the prompt.
Line 2practice.db is created by the first command if it is missing; SQLite has no separate create-database step.
Line 3Passing SQL as the second argument runs it and exits, so this line is a reload check you can repeat cheaply.
Line 4Column headers are off by default in the shell, which is why the result prints as a bare 4 with no column name above it.
Important notes
A seed script that drops tables is fine for a practice file and destructive anywhere shared, so keep it in its own clearly named file and never point it at a database you care about.
SQLite ignores REFERENCES unless the connection has run PRAGMA foreign_keys = ON, so a script that inserts child rows before parent rows can pass here and fail on Postgres or MySQL.
Common mistakes
Using CREATE TABLE IF NOT EXISTS with plain INSERTs and then rerunning the script: the tables are left alone, the rows pile up, and later counts and averages are wrong while your queries look correct.
Starting the shell as sqlite3 with no filename: you get a temporary in-memory database, every statement appears to work, and the entire schema disappears at .quit with nothing written to disk.
Pasting .read seed.sql or .tables into a browser editor or GUI query box: those are shell commands, so the SQL parser rejects them with a syntax error near the dot.
Try it yourself
Change, predict, then run
Paste the seed script into a browser SQLite editor and run it twice, confirming the verification counts stay 3 and 4. Then remove the two DROP TABLE lines, change both CREATE TABLE to CREATE TABLE IF NOT EXISTS, run it twice more, and watch the counts climb to 6 and 8.
Open the SQL workspaceCheck your understanding
A seed script uses CREATE TABLE IF NOT EXISTS followed by INSERT statements. A reader loads it, accidentally deletes two rows, then runs the same script again to reset the data. What is the resulting state?
- The tables are recreated empty and refilled, so the data is back to exactly what the script describes
- Nothing is cleared: the INSERTs run again, so the two deleted rows return once and every other row now exists twice
- The script fails with an error because the tables already exist, leaving the data as it was
- The tables end up empty, because rerunning a script always starts from scratch
Show answer
IF NOT EXISTS makes each CREATE a no-op when the table is already there, so nothing is emptied, and INSERT is unconditional and simply appends. The rows that survived the delete get a second copy while the two deleted rows come back once. Option 3 is tempting, but IF NOT EXISTS is precisely the clause that suppresses the table-already-exists error; without it you would get that error rather than a reset, and only DROP TABLE IF EXISTS before each CREATE gives the clean slate described in option 1.