Introduction to Databases and SQL

So far, when you close your app, your data disappears. The posts you downloaded in lesson 29 are fetched again every time, and a to-do item you typed in lesson 24 vanishes when the process dies. To keep data on the phone you need a database.

Android has a database built in, called SQLite, and you talk to it using a language called SQL. In lesson 34 you will use a library named Room that writes much of the plumbing for you, but Room's annotations are built on SQL. If you do not understand tables, keys and queries, Room feels like magic. If you do, it feels like a convenience.

This lesson is deliberately pure SQL, with no Android code until the very end, so you can practise on any computer. It covers the two Unit 6 items of Pathway 1 in the official Android Basics with Compose course: Introduction to Databases and SQL and Use SQL to read and write to a database.

By the end you will be able to:

• Explain what a relational database, a table, a row, a column and a key are.
• Choose column types and constraints.
• Write SELECT queries with WHERE, ORDER BY and LIMIT.
• Add, change and remove rows with INSERT, UPDATE and DELETE.
• Count and summarise with aggregate functions and GROUP BY / HAVING.
• Combine tables with JOIN, and explain a one-to-many relationship with a foreign key.
• Explain what an index is and when normalisation helps.
• Map SQL ideas to Room annotations.

Big idea: a database is a set of tables, and SQL is how you ask questions about them. You describe what you want, not how to loop for it.

• Data classes (lesson 11). A row in a table will become a data class.
• Why persistence matters (lessons 23 and 24). The ViewModel does not survive process death, and rememberSaveable is only for small values.
• Where to practise. You need an SQLite tool. Any of these works: the free DB Browser for SQLite desktop app, the sqlite3 command-line program (already installed on many computers), or an online SQLite playground. Android Studio itself does not give you a standalone SQL console; its Database Inspector (lesson 44) lets you run queries against your running app's database.

Think of a school register. It is a grid. Each column is one kind of fact (name, age, state). Each row is one student. That grid is a table. A relational database is a collection of tables that can refer to each other.

students                          <- table name
+----+-------+-----+---------+
| id | name  | age | state   |     <- column names (the table's "schema")
+----+-------+-----+---------+
|  1 | Ada   |  15 | Oyo     |     <- one row = one student
|  2 | Tunde |  16 | Lagos   |
|  3 | Chika |  15 | Enugu   |
+----+-------+-----+---------+

Primary keys

Every row needs a way to be identified uniquely. Two students could both be named Ada, so we add an id column that is different for every row. This is the primary key. It must be unique and never NULL. Most apps let the database generate it automatically.

Column types

SQLite uses a flexible type system with five storage classes:

Dates are usually stored as INTEGER (milliseconds since 1970) or TEXT in ISO format. Money is best stored as an integer in the smallest unit (kobo or cents) so you never meet floating-point rounding errors.

Constraints

Constraints are rules the database enforces for you:

Foreign keys make relationships possible. In the next section a results table points at students through student_id. One student has many results (a one-to-many relationship). Note: SQLite does not enforce foreign keys unless you turn them on with PRAGMA foreign_keys = ON; on each connection. Room turns this on for you when you declare foreign keys.

Everything in this lesson uses one small database of students and exam results. Open your SQLite tool and run this script. Read it line by line first.

CREATE TABLE students (
 id INTEGER PRIMARY KEY,
 name TEXT NOT NULL,
 age INTEGER NOT NULL,
 state TEXT NOT NULL
);
CREATE TABLE results (
 id INTEGER PRIMARY KEY,
 student_id INTEGER NOT NULL REFERENCES students(id),
 subject TEXT NOT NULL,
 score INTEGER NOT NULL
);
INSERT INTO students (id, name, age, state) VALUES
 (1, 'Ada', 15, 'Oyo'),
 (2, 'Tunde', 16, 'Lagos'),
 (3, 'Chika', 15, 'Enugu'),
 (4, 'Bola', 17, 'Oyo'),
 (5, 'Emeka', 16, 'Anambra');
INSERT INTO results (id, student_id, subject, score) VALUES
 (1, 1, 'Maths', 78),
 (2, 1, 'English', 64),
 (3, 2, 'Maths', 55),
 (4, 2, 'English', 70),
 (5, 3, 'Maths', 91),
 (6, 4, 'English', 48),
 (7, 4, 'Maths', 60);

Here is the data you just created:

students                              results
id | name  | age | state              id | student_id | subject | score
 1 | Ada   | 15  | Oyo                 1 |     1      | Maths   |  78
 2 | Tunde | 16  | Lagos               2 |     1      | English |  64
 3 | Chika | 15  | Enugu               3 |     2      | Maths   |  55
 4 | Bola  | 17  | Oyo                 4 |     2      | English |  70
 5 | Emeka | 16  | Anambra             5 |     3      | Maths   |  91
                                       6 |     4      | English |  48
                                       7 |     4      | Maths   |  60

Observe: Emeka (id 5) has no results. Keep that in mind; it matters when we reach joins. SQL keywords are not case sensitive (select equals SELECT), but writing them in capitals makes queries easier to read. Every statement ends with a semicolon.

CREATE TABLE defines the schema once. INSERT INTO ... VALUES adds rows. Text values are in single quotes.

SELECT asks a question and returns a result table. Its general shape:

SELECT columns
FROM table
WHERE condition
ORDER BY column
LIMIT count;

All columns, specific columns

SELECT * FROM students;

returns every column and every row. The * means "all columns". Avoid * in app code, because it breaks when columns are added; name the columns you need:

SELECT name, age FROM students;

Filtering with WHERE

SELECT name, age FROM students WHERE state = 'Oyo';

name | age
Ada | 15
Bola | 17

Comparison operators: =, <> (not equal), <, >, <=, >=. Combine conditions with AND, OR and NOT, and group with parentheses:

SELECT name FROM students WHERE name LIKE 'A%' OR age > 16 ORDER BY name;

name
Ada
Bola

LIKE matches patterns: % stands for any run of characters, _ for exactly one. 'A%' means "starts with A". Other useful tests are BETWEEN 15 AND 16, IN ('Oyo','Lagos'), and IS NULL (never = NULL, which is never true).

Sorting and limiting

SELECT name, age FROM students ORDER BY age DESC, name LIMIT 2;

name | age
Bola | 17
Emeka | 16

ORDER BY age DESC sorts oldest first; ties on age (Tunde and Emeka are both 16) are broken by name alphabetically, so Emeka comes before Tunde. LIMIT 2 keeps only the first two rows. Without ORDER BY, the order of rows is not guaranteed, so always sort when order matters. LIMIT 10 OFFSET 20 skips the first 20 rows, which is how paging works.

INSERT INTO students (id, name, age, state) VALUES (6, 'Funke', 16, 'Ogun');

If you leave out id on an INTEGER PRIMARY KEY column, SQLite picks the next free number for you. That is how apps normally add rows.

UPDATE results SET score = score + 5 WHERE student_id = 4 AND subject = 'English';

After this, Bola's English score changes from 48 to 53. Always include a WHERE on UPDATE.

DELETE FROM results WHERE id = 3;

This removes Tunde's Maths result. Always include a WHERE on DELETE.

Danger: UPDATE results SET score = 0; with no WHERE changes every row. DELETE FROM results; empties the table. Many real outages started with a missing WHERE. Run the matching SELECT first, check the rows, then change the verb.

Make changes to a copy of the data while you practise. You can rebuild the sample database at any time by re-running the script from Build 1 on a fresh database.

An aggregate function squeezes many rows into one value:

Using the original data (before the update and delete above):

SELECT COUNT(*), SUM(score), AVG(score), MIN(score), MAX(score) FROM results;

COUNT | SUM | AVG | MIN | MAX
7 | 466 | 66.571... | 48 | 91

Check by hand: 78 + 64 + 55 + 70 + 91 + 48 + 60 = 466, and 466 / 7 = 66.57.

GROUP BY makes one group per value and applies the aggregate to each group:

SELECT subject, ROUND(AVG(score), 2) AS average, COUNT(*) AS entries
FROM results
GROUP BY subject
ORDER BY subject;

subject | average | entries
English | 60.67 | 3
Maths | 71.0 | 4

Check: English is (64 + 70 + 48) / 3 = 60.67; Maths is (78 + 55 + 91 + 60) / 4 = 71. AS gives a column a friendlier name.

WHERE filters rows before grouping. HAVING filters groups after grouping:

SELECT student_id, AVG(score) AS average
FROM results
GROUP BY student_id
HAVING AVG(score) >= 65;

student_id | average
1 | 71.0
3 | 91.0

Students 2 (62.5) and 4 (54) are excluded.

The results table shows student_id numbers, which are not friendly. A JOIN lines up rows from two tables using the key relationship:

SELECT s.name, r.subject, r.score
FROM students AS s
INNER JOIN results AS r ON r.student_id = s.id
WHERE r.score >= 70
ORDER BY r.score DESC;

name | subject | score
Chika | Maths | 91
Ada | Maths | 78
Tunde | English | 70

How to read it: for each student, find the results whose student_id matches the student's id, keep only scores of 70 or more, and sort them highest first. s and r are aliases (short nicknames) so you do not type the long table names.

INNER JOIN versus LEFT JOIN

INNER JOIN  = only rows that have a match on both sides
LEFT JOIN   = every row from the left table, with NULLs where no match exists

Who has no results? An inner join silently drops Emeka. A left join keeps him:

SELECT s.name, COUNT(r.id) AS results_count
FROM students AS s
LEFT JOIN results AS r ON r.student_id = s.id
GROUP BY s.id
ORDER BY s.id;

name | results_count
Ada | 2
Tunde | 2
Chika | 1
Bola | 2
Emeka | 0

COUNT(r.id) counts only non-NULL values, so Emeka gets 0. (COUNT(*) would wrongly say 1 for him, because his joined row exists with NULLs.) Another way to find students with no results is a subquery:

SELECT name FROM students WHERE id NOT IN (SELECT student_id FROM results);

name
Emeka

You could have stored everything in one wide table: name, age, state, subject, score, repeating Ada's name, age and state on each of her results. That causes three kinds of trouble:

Splitting data so that each fact is stored once, and linking tables with keys, is called normalisation. A good rule for beginners: one table per kind of thing; refer to other things by their ID. Do not take normalisation to extremes on a phone app; sometimes a little duplication keeps queries simple and fast, but start normalised.

Indexes

If a table has a million rows and you search WHERE state = 'Oyo', SQLite must read every row. An index is like the index at the back of a book: a sorted list that lets the database jump to the right rows.

CREATE INDEX idx_students_state ON students(state);

Indexes make reads faster but writes slower and use more space, so add them for columns you search or join on often, not everywhere. Primary keys are indexed automatically. Room lets you declare indexes in annotations.

SQLite is a small database engine that lives inside every Android phone. Each app gets its own database file in its private storage, which other apps cannot read. Room is a Jetpack library that sits on top. Here is how the ideas you just learned map over (preview of lesson 34):

A taste of what is coming:

import androidx.room.Dao
import androidx.room.Entity
import androidx.room.PrimaryKey
import androidx.room.Query
import kotlinx.coroutines.flow.Flow
@Entity(tableName = "students")
data class Student(
 @PrimaryKey(autoGenerate = true) val id: Int = 0,
 val name: String,
 val age: Int,
 val state: String
)
@Dao
interface StudentDao {
 @Query("SELECT * FROM students WHERE state = :state ORDER BY name")
 fun studentsIn(state: String): Flow<List<Student>>
}

The SQL you wrote today goes inside that annotation, character for character. Room checks it at build time, so a typo in a column name stops the build instead of crashing the app on a user's phone.

Never build SQL with string concatenation

If you ever run raw SQL, never assemble it by gluing user input into the string:

// DANGEROUS: SQL injection
val sql = "SELECT * FROM students WHERE name = '" + userInput + "'"

If the user types x' OR '1'='1, the condition becomes always true and returns everything; worse input can delete data. Always use parameters (:state above, or ? placeholders), which the database treats strictly as values, never as SQL code. Room's :name syntax is parameterised by design.

Use the original sample data for these (re-create the database if you changed it). The answers show the exact output so you can check yourself.

-- 1
SELECT name FROM students WHERE state <> 'Oyo';
-- Tunde, Chika, Emeka
-- 2
SELECT name FROM students WHERE age = 16 ORDER BY name;
-- Emeka, Tunde
-- 3
SELECT state, COUNT(*) AS total
FROM students
GROUP BY state
ORDER BY total DESC, state;

-- 1
SELECT s.name, SUM(r.score) AS total
FROM students AS s
JOIN results AS r ON r.student_id = s.id
GROUP BY s.id
ORDER BY total DESC;

-- 2
SELECT s.name, r.subject, r.score
FROM students AS s JOIN results AS r ON r.student_id = s.id
WHERE r.score < 60;
-- Tunde Maths 55, Bola English 48
-- 3
SELECT subject, AVG(score) AS average
FROM results
GROUP BY subject
ORDER BY average DESC
LIMIT 1;
-- Maths | 71.0

-- 1
UPDATE results
SET score = MIN(score + 3, 100)
WHERE subject = 'Maths';
-- 2 (children first, then the parent)
DELETE FROM results WHERE student_id = 4;
DELETE FROM students WHERE id = 4;

Mistake #1: UPDATE or DELETE without WHERE. What happens: every row is changed or removed. The fix: write the SELECT with the same WHERE first.

Mistake #2: = NULL. What happens: returns nothing. The fix: IS NULL and IS NOT NULL.

Mistake #3: Forgetting ORDER BY. What happens: rows come back in an unpredictable order that happens to look right until it does not. The fix: always sort when order matters.

Mistake #4: Double quotes for text. What happens: "Oyo" may be treated as a column name. The fix: single quotes for string values.

Mistake #5: Using WHERE for an aggregate. The wrong code: WHERE AVG(score) > 65. What happens: an error. The fix: HAVING.

Mistake #6: Inner join drops rows with no match. What happens: Emeka disappears from a report. The fix: LEFT JOIN, and count a column from the right table.

Mistake #7: Counting with COUNT(*) after a left join. The fix: COUNT(right_table.id).

Mistake #8: Building SQL from user text. What happens: SQL injection. The fix: parameters.

Mistake #9: Floating-point money. What happens: 0.1 + 0.2 rounding oddities. The fix: integers in kobo or cents.

Mistake #10: Storing lists in one column. The wrong design: subjects = 'Maths,English'. What happens: you cannot query or join it properly. The fix: a separate table with one row per item.

Question 1. What is a primary key?

Question 2. What is the difference between WHERE and HAVING?

Question 3. Why use a foreign key?

Question 4. When does an INNER JOIN return fewer rows than a LEFT JOIN?

Question 5. Why must queries never be built by concatenating user input?

Go deeper: official resources

• Android Basics with Compose, Unit 6, Pathway 1: Introduction to databases and SQL: the official pathway for this lesson.
• Android Basics with Compose course page
• Data layer guide