SQLite fundamentals
You will learn from scratch to create and query SQLite, the single-file database found in phones, browsers and apps.
Level 101 · 17 lessons · 15 practice labs
Lessons
Getting started
- What SQLite is and where it runs: You will understand why SQLite, a serverless single-file database, is so common in phones, browsers and apps.
- The sqlite3 command line: install and first connection: You will install the sqlite3 tool, open a new database file and exit it.
- Dot commands: .tables, .schema and .mode: You will list tables, view structure and change output format using SQLite’s dot commands, which are not SQL.
- Short task: explore a ready-made database
Tables and data
- Creating tables and SQLite’s flexible data types: You will write CREATE TABLE and understand why SQLite loosely checks types (type affinity) when designing tables.
- Constraints, primary keys and AUTOINCREMENT: You will set table rules with NOT NULL, UNIQUE and primary keys and use an auto-numbering id column.
- Inserting, updating and deleting data in SQLite: You will manage records with INSERT, UPDATE and DELETE and see what happens when you forget WHERE.
- SQLite: retrieving data with SELECT and WHERE: You will pick columns, filter rows by condition and combine several conditions with AND and OR.
- Sorting, LIKE and LIMIT: You will sort results, search for patterns in text and take only the first few rows.
- Aggregates, GROUP BY and HAVING: You will count and sum records, group them by department and filter the group result with HAVING.
- Joining tables in SQLite: You will combine two tables on a shared id with INNER and LEFT JOIN and see related data in one result.
- Date and text functions in SQLite
- Short task: queries on a recipe database
Working with the file, and security
- CSV import and export, backup with .dump: You will import a CSV into a table, export results and create a text backup of the database with .dump.
- Views and indexes: You will turn a frequent query into a view and speed up lookups with an index.
- Securing the database file: permissions and risky locations: You will protect data knowing SQLite is just a file: use file permissions, keep it out of the web root and protect backups.
- Capstone: a contacts database in SQLite: You will combine tables, constraints, queries, backup and file permissions into a complete contacts database.
Practice labs
- List the tables of an unfamiliar database: Open the .db file sent by Defne Bookshop with sqlite3 and list its tables and row counts.
- Turn the output into a readable table: Use .mode, .headers and .width to make messy query output fit for a report.
- Work out what an app does from its schema: From the .schema output of an unlabelled app database, determine what data it keeps and how the tables relate.
- Create a members table with rules: Build a members table for Kumsal Sports Club where e-mail is unique and name cannot be empty, then test the rules.
- Find mixed data caused by flexible typing: In a table whose price column also received text, find the bad rows with typeof() and fix them.
- Repair a schema that leaves orphan rows: Clean the orphan rows in an orders table with no foreign key and rebuild it with constraints.
- Sort the stationery items running out: In the Çınar Stationery database, list products with stock under 10, sorted by price.
- Filter orders stored with text dates: Select last month's records from orders whose dates are stored as text, using date() and strftime().
- Fix a LIKE search that matches too much: Fix a customer search that wrongly matches names containing percent and underscore by using ESCAPE and a correct pattern.
- Total sales per category: From a bakery chain's sales table, compute total revenue and product count per category.
- Find members who never bought anything in SQLite: Join customers and orders with LEFT JOIN to list customers who have never bought anything.
- Fix a double-counted total: Find why a revenue report inflates after joining two tables and write a query that gives the correct total.
- Import a CSV file into a table: Import Lale Nursery's product list with .import and compare the row count with the source.
- Move one table to another file with .dump: Export a table with .dump, load it into a new database and confirm that every row arrived.
- Protect a world-readable database file: Move a world-readable .db file out of the web directory, tighten its permissions and test access.
About this course
You will learn from scratch to create and query SQLite, the single-file database found in phones, browsers and apps.
What you will learn
- Getting started
- Tables and data
- Working with the file, and security
Prerequisites
No prior knowledge is needed; the course starts from zero.
Lab environment
Lessons run in your own Kali Linux terminal that opens in the browser. Nothing to install; the lab starts clean in every lesson.
Frequently asked questions
- Is this course right for my level?
- The course is at beginner level; every term is explained where it first appears.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.