{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"a-place-to-practise","title":"Getting a database onto the machine you have","url":"https://addaly.com/learn/data-and-sql/a-place-to-practise","position":2,"of":66,"minutes":8,"answer":"You do not need a server to practise SQL: SQLite is one file, DuckDB queries a CSV where it lies, and Postgres is what to install when a job advert names it.","keyIdea":"You do not need a server to practise SQL: SQLite is one file, DuckDB queries a CSV where it lies, and Postgres is what to install when a job advert names it."},"body":{"format":"markdown","text":"## You do not need a server, a cloud account or a credit card\n\nMost SQL courses assume you already have a database. Most people do not, and the setup step is where a lot of learners stop. So here are four honest options, in order of how little they ask of you. All are free, all run offline, and none of them expire.\n\n## SQLite: a database that is one file\n\nSQLite is the most widely deployed database in the world. It is inside your phone, your browser and your car. A whole database is a single file you can email to somebody.\n\nIf you have Python, you already have it:\n\n```bash\npython3 -c \"import sqlite3; sqlite3.connect('school.db').close()\"\nsqlite3 school.db\n```\n\nIf the `sqlite3` command is missing, install it (`apt install sqlite3`, `brew install sqlite`), or use **DB Browser for SQLite**: a free graphical tool with a table view, an import-CSV button and a query tab. Job ads say Microsoft Access or Excel; DB Browser does the same teaching job for nothing, on Windows, macOS and Linux.\n\n```sql\nCREATE TABLE students (id integer primary key, full_name text, class_level int);\nINSERT INTO students (full_name, class_level) VALUES ('Rukmini R', 9), ('Ade O', 10);\nSELECT * FROM students;\n```\n\nThe honest limitation, and it matters for this course: **SQLite does not really enforce types.** Its columns have type *affinity*, not type constraints, so `INSERT INTO students (full_name, class_level) VALUES ('Ade', 'Grade Nine')` succeeds. Everything else you learn transfers; that one protection does not exist until you turn on strict tables (`CREATE TABLE ... STRICT`, SQLite 3.37 or later). Do turn it on.\n\n## DuckDB: SQLite's analytical cousin\n\nDuckDB is a single binary that queries CSV and Parquet files directly, without loading them first.\n\n```bash\nduckdb -c \"SELECT city, count(*) FROM read_csv_auto('payments.csv') GROUP BY city\"\n```\n\nThat one line is the fastest route from a downloaded export to a real answer, and it is what most of this course's cleaning work is comfortable in. It is built for reading large tables fast, not for many people writing at once, so it is a superb analysis tool and the wrong choice for an application's live data.\n\n## PostgreSQL: the one the jobs ask for\n\nPostgres is the reference implementation for almost everything in this course. Install it locally:\n\n```bash\n# Debian / Ubuntu\nsudo apt install postgresql\nsudo -u postgres createdb school\npsql school\n\n# macOS\nbrew install postgresql@17 && brew services start postgresql@17\ncreatedb school && psql school\n```\n\nOr run it in Docker without installing anything permanent:\n\n```bash\ndocker run --name pg -e POSTGRES_PASSWORD=dev -p 5432:5432 -d postgres:17\npsql \"postgres://postgres:dev@localhost:5432/postgres\"\n```\n\nIf your laptop cannot run either, several providers give a small Postgres database on a free tier that never asks for a card. That is enough for every query in this course.\n\n## If all you have is a phone\n\nThis is a real constraint for a lot of readers, so it gets a real answer rather than an apology. On Android, Termux installs `sqlite3` and `python` from a package manager and gives you a proper shell. On any phone, a browser-based SQL sandbox will run SQLite compiled to WebAssembly entirely on the device. Neither is comfortable for eight-hour days. Both are entirely sufficient for learning to think in tables, which is the part that transfers.\n\n## Get some data in\n\nEmpty databases teach nothing. Two moves:\n\n```sql\n-- Postgres, from a file on the server's disk\nCOPY payments FROM '/tmp/payments.csv' WITH (FORMAT csv, HEADER true);\n\n-- Postgres, from a file on your own machine, via psql\n\\copy payments FROM 'payments.csv' WITH (FORMAT csv, HEADER true)\n```\n\nThat backslash matters. `COPY` runs as the database server and looks on the server's disk. `\\copy` runs in your client and reads your file. Almost every \"permission denied\" on a first import is this distinction.\n\nIn SQLite: `.mode csv` then `.import payments.csv payments`. In DuckDB you can skip importing entirely.\n\nGood public data to practise on: a national statistics office's open data portal, your city's transport authority, or the World Bank's open datasets. Prefer data you have some feel for: you will spot a wrong answer in data about your own city and you will not spot it in a synthetic sales table.\n\n## What to actually do now\n\nCreate one database. Load one CSV of at least a few thousand rows. Run `SELECT * FROM yourtable LIMIT 20` and read it. That is the whole exercise, and doing it once removes the friction that otherwise sits between you and every remaining lesson in this course."},"check":{"question":"A colleague sends you a 3-million-row CSV export and asks for the number of rows per branch by tomorrow morning. You have an ordinary laptop and no database installed. Which route gets a correct answer with the least ceremony?","options":[{"text":"Set up Postgres, create a table with the right column types, import the CSV with COPY, then run the count against the indexed table.","correct":false,"tempting":"It is not wrong, but it front-loads schema design onto a question you have not yet decided is worth answering; the import alone costs more than the answer."},{"text":"Query the CSV directly with DuckDB, since it reads the file in place and is built for scanning large tables.","correct":true},{"text":"Open the file in a spreadsheet and use a pivot table.","correct":false,"tempting":"Most spreadsheets stop near a million rows and will either refuse the file or become unusable, and the operation is not repeatable next month."},{"text":"Load it into SQLite, because SQLite is the most widely deployed database and works everywhere.","correct":false,"tempting":"SQLite will do it, but it is optimised for small transactional reads and writes rather than scanning a whole column, and you still pay the import step first."}]},"prev":{"slug":"what-a-database-is","title":"When a spreadsheet stops working","url":"https://addaly.com/learn/data-and-sql/what-a-database-is"},"next":{"slug":"rows-columns-and-types","title":"What a column type actually buys you","url":"https://addaly.com/learn/data-and-sql/rows-columns-and-types"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Getting a database onto the machine you have', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/a-place-to-practise (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Getting a database onto the machine you have","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":1,"slug":"tables-types-and-the-question","title":"Tables, types and the question you are actually asking"},"answer":null,"keyIdea":"You do not need a server to practise SQL: SQLite is one file, DuckDB queries a CSV where it lies, and Postgres is what to install when a job advert names it.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"you-do-not-need-a-server-a-cloud-account-or-a-credit-card","runs":[{"text":"You do not need a server, a cloud account or a credit card"}]},{"t":"p","runs":[{"text":"Most SQL courses assume you already have a database. Most people do not, and the setup step is where a lot of learners stop. So here are four honest options, in order of how little they ask of you. All are free, all run offline, and none of them expire."}]},{"t":"h2","id":"sqlite-a-database-that-is-one-file","runs":[{"text":"SQLite: a database that is one file"}]},{"t":"p","runs":[{"text":"SQLite is the most widely deployed database in the world. It is inside your phone, your browser and your car. A whole database is a single file you can email to somebody."}]},{"t":"p","runs":[{"text":"If you have Python, you already have it:"}]},{"t":"code","lang":"bash","label":"Bash","text":"python3 -c \"import sqlite3; sqlite3.connect('school.db').close()\"\nsqlite3 school.db"},{"t":"p","runs":[{"text":"If the "},{"text":"sqlite3","code":true},{"text":" command is missing, install it ("},{"text":"apt install sqlite3","code":true},{"text":", "},{"text":"brew install sqlite","code":true},{"text":"), or use "},{"text":"DB Browser for SQLite","b":true},{"text":": a free graphical tool with a table view, an import-CSV button and a query tab. Job ads say Microsoft Access or Excel; DB Browser does the same teaching job for nothing, on Windows, macOS and Linux."}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE TABLE students (id integer primary key, full_name text, class_level int);\nINSERT INTO students (full_name, class_level) VALUES ('Rukmini R', 9), ('Ade O', 10);\nSELECT * FROM students;"},{"t":"p","runs":[{"text":"The honest limitation, and it matters for this course: "},{"text":"SQLite does not really enforce types.","b":true},{"text":" Its columns have type "},{"text":"affinity","i":true},{"text":", not type constraints, so "},{"text":"INSERT INTO students (full_name, class_level) VALUES ('Ade', 'Grade Nine')","code":true},{"text":" succeeds. Everything else you learn transfers; that one protection does not exist until you turn on strict tables ("},{"text":"CREATE TABLE ... STRICT","code":true},{"text":", SQLite 3.37 or later). Do turn it on."}]},{"t":"h2","id":"duckdb-sqlite-s-analytical-cousin","runs":[{"text":"DuckDB: SQLite’s analytical cousin"}]},{"t":"p","runs":[{"text":"DuckDB is a single binary that queries CSV and Parquet files directly, without loading them first."}]},{"t":"code","lang":"bash","label":"Bash","text":"duckdb -c \"SELECT city, count(*) FROM read_csv_auto('payments.csv') GROUP BY city\""},{"t":"p","runs":[{"text":"That one line is the fastest route from a downloaded export to a real answer, and it is what most of this course’s cleaning work is comfortable in. It is built for reading large tables fast, not for many people writing at once, so it is a superb analysis tool and the wrong choice for an application’s live data."}]},{"t":"h2","id":"postgresql-the-one-the-jobs-ask-for","runs":[{"text":"PostgreSQL: the one the jobs ask for"}]},{"t":"p","runs":[{"text":"Postgres is the reference implementation for almost everything in this course. Install it locally:"}]},{"t":"code","lang":"bash","label":"Bash","text":"# Debian / Ubuntu\nsudo apt install postgresql\nsudo -u postgres createdb school\npsql school\n\n# macOS\nbrew install postgresql@17 && brew services start postgresql@17\ncreatedb school && psql school"},{"t":"p","runs":[{"text":"Or run it in Docker without installing anything permanent:"}]},{"t":"code","lang":"bash","label":"Bash","text":"docker run --name pg -e POSTGRES_PASSWORD=dev -p 5432:5432 -d postgres:17\npsql \"postgres://postgres:dev@localhost:5432/postgres\""},{"t":"p","runs":[{"text":"If your laptop cannot run either, several providers give a small Postgres database on a free tier that never asks for a card. That is enough for every query in this course."}]},{"t":"h2","id":"if-all-you-have-is-a-phone","runs":[{"text":"If all you have is a phone"}]},{"t":"p","runs":[{"text":"This is a real constraint for a lot of readers, so it gets a real answer rather than an apology. On Android, Termux installs "},{"text":"sqlite3","code":true},{"text":" and "},{"text":"python","code":true},{"text":" from a package manager and gives you a proper shell. On any phone, a browser-based SQL sandbox will run SQLite compiled to WebAssembly entirely on the device. Neither is comfortable for eight-hour days. Both are entirely sufficient for learning to think in tables, which is the part that transfers."}]},{"t":"h2","id":"get-some-data-in","runs":[{"text":"Get some data in"}]},{"t":"p","runs":[{"text":"Empty databases teach nothing. Two moves:"}]},{"t":"code","lang":"sql","label":"SQL","text":"-- Postgres, from a file on the server's disk\nCOPY payments FROM '/tmp/payments.csv' WITH (FORMAT csv, HEADER true);\n\n-- Postgres, from a file on your own machine, via psql\n\\copy payments FROM 'payments.csv' WITH (FORMAT csv, HEADER true)"},{"t":"p","runs":[{"text":"That backslash matters. "},{"text":"COPY","code":true},{"text":" runs as the database server and looks on the server’s disk. "},{"text":"\\copy","code":true},{"text":" runs in your client and reads your file. Almost every “permission denied” on a first import is this distinction."}]},{"t":"p","runs":[{"text":"In SQLite: "},{"text":".mode csv","code":true},{"text":" then "},{"text":".import payments.csv payments","code":true},{"text":". In DuckDB you can skip importing entirely."}]},{"t":"p","runs":[{"text":"Good public data to practise on: a national statistics office’s open data portal, your city’s transport authority, or the World Bank’s open datasets. Prefer data you have some feel for: you will spot a wrong answer in data about your own city and you will not spot it in a synthetic sales table."}]},{"t":"h2","id":"what-to-actually-do-now","runs":[{"text":"What to actually do now"}]},{"t":"p","runs":[{"text":"Create one database. Load one CSV of at least a few thousand rows. Run "},{"text":"SELECT * FROM yourtable LIMIT 20","code":true},{"text":" and read it. That is the whole exercise, and doing it once removes the friction that otherwise sits between you and every remaining lesson in this course."}]}],"check":{"question":[{"text":"A colleague sends you a 3-million-row CSV export and asks for the number of rows per branch by tomorrow morning. You have an ordinary laptop and no database installed. Which route gets a correct answer with the least ceremony?"}],"options":[{"text":[{"text":"Set up Postgres, create a table with the right column types, import the CSV with COPY, then run the count against the indexed table."}],"correct":false,"tempting":[{"text":"It is not wrong, but it front-loads schema design onto a question you have not yet decided is worth answering; the import alone costs more than the answer."}]},{"text":[{"text":"Query the CSV directly with DuckDB, since it reads the file in place and is built for scanning large tables."}],"correct":true,"tempting":null},{"text":[{"text":"Open the file in a spreadsheet and use a pivot table."}],"correct":false,"tempting":[{"text":"Most spreadsheets stop near a million rows and will either refuse the file or become unusable, and the operation is not repeatable next month."}]},{"text":[{"text":"Load it into SQLite, because SQLite is the most widely deployed database and works everywhere."}],"correct":false,"tempting":[{"text":"SQLite will do it, but it is optimised for small transactional reads and writes rather than scanning a whole column, and you still pay the import step first."}]}],"order":[0,3,2,1],"why":null},"prev":{"slug":"what-a-database-is","title":"When a spreadsheet stops working","minutes":7},"next":{"slug":"rows-columns-and-types","title":"What a column type actually buys you","minutes":8}}}