Theory
A database with no database server
In BCA105 you queried tables and imagined some big system answering: a database server, always running, waiting for connections.
Now a surprise: your project this semester, ResultDesk, a tool to analyse college results, will use a full SQL database that is... one ordinary file on disk: college.db.
No installation. No server to start. No username, no password. Copy the file to a pen drive and the entire database travels with it. That engine is SQLite, and it is already the most deployed database on Earth.
Theory
A restaurant vs a tiffin box
MySQL and Oracle are restaurants: a building (server), staff (processes), a counter that takes orders over the network, built to serve hundreds at once.
SQLite is a tiffin box: complete, self-contained, carried inside the very bag (application) that needs it. No staff, no building. Your phone's contacts app, WhatsApp's chat history, your browser's bookmarks: all tiffin boxes, all SQLite files.
Theory
The feature list exams ask for
SQLite is:
- Serverless: no separate server process; the library reads and writes the file directly.
- Zero-configuration: nothing to install or administer.
- Self-contained, single-file: one cross-platform file holds tables, indexes, everything.
- Transactional: changes are all-or-nothing (next lesson).
- Lightweight and free (public domain).
When NOT to choose it: many users writing at the same moment over a network. That is restaurant work: MySQL territory.
At a glance
The five storage classes
| Storage class | Holds | Example |
|---|---|---|
| NULL | Missing / unknown value | score of an absent student |
| INTEGER | Whole numbers | roll 101, score 78 |
| REAL | Decimals (8-byte float) | percentage 81.67 |
| TEXT | Character data | name 'Riya', city 'Surat' |
| BLOB | Raw bytes, stored as-is | a photo, a PDF of a certificate |
Theory
Dynamic typing: the type lives on the value
In BCA105-style databases, a column's type is a law: an INT column refuses 'hello'.
SQLite flips this: it uses manifest (dynamic) typing, where the type belongs to the value, not the column. Any column can, in principle, hold any storage class.
Declared column types instead set a type affinity: a preference. A column declared INTEGER has INTEGER affinity: given the text '42', SQLite converts it to the integer 42. Given 'Riya', it simply stores text. Declared VARCHAR(50)? That maps to TEXT affinity.
Practical
college.db: the tables this whole subject uses
CREATE TABLE students (
roll INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT
);
CREATE TABLE marks (
roll INTEGER,
subject TEXT,
score INTEGER
);
INSERT INTO students VALUES (101, 'Riya', 'Surat');
INSERT INTO students VALUES (102, 'Aman', 'Navsari');
INSERT INTO marks VALUES (101, 'DBMS', 78);
INSERT INTO marks VALUES (101, 'Maths', 91);
INSERT INTO marks VALUES (102, 'DBMS', 55);
This example runs in Gri-Learn on the web, where you can edit it and see the output.
Think first
Predict SQLite's reaction
The marks table declares score INTEGER. Someone runs:
INSERT INTO marks VALUES (103, 'DBMS', 'absent');
In a strict BCA105-style database this errors. Before tapping: what does SQLite do?
Show the answer
SQLite accepts it and stores the TEXT value 'absent' in the score column. INTEGER is only an affinity (a preference): 'absent' cannot be converted to a number, so it is kept as text. No error. This flexibility is SQLite's signature and its trap: your queries must be ready for a text value where numbers are expected. (SQLite does offer STRICT tables to forbid this, worth one line in an exam answer.)
Quiz
Which of these is a real SQLite storage class?
- BLOB
- BOOLEAN
- DATE
- VARCHAR
Show the answer
BLOB
The five storage classes are NULL, INTEGER, REAL, TEXT, BLOB (raw bytes, stored exactly as given). BOOLEAN and DATE do not exist as storage classes: booleans are stored as INTEGER 0/1, dates as TEXT, REAL or INTEGER by convention. VARCHAR is a declared type name that SQLite maps to TEXT affinity, not a storage class itself. Exams love asking for the odd one out of this list.
Watch out
Two traps from stricter worlds
"SQLite will reject the wrong type": it usually will not; affinity converts when possible and stores as-is when not. Never rely on the column to police your data.
"There must be a DATE type": there is not. Store dates as TEXT ('2026-07-04'), and say so in exams: SQLite has no dedicated date/boolean storage class; conventions over INTEGER/TEXT/REAL are used.
Theory
Why Python and SQLite are this subject's pair
Python ships with SQLite built in (the sqlite3 module, Unit 3): no install, no server, one import away. That makes SQLite the perfect classroom and prototyping database, and the reason this subject can take you from raw SQL to pandas charts using one small file: the same college.db travels through all five units.
Summary
Key takeaways
- SQLite = serverless, zero-configuration, self-contained, transactional engine; the whole database is one portable file.
- Choose it for embedded/local work; choose client-server (MySQL) for many concurrent network writers.
- Five storage classes: NULL, INTEGER, REAL, TEXT, BLOB; no BOOLEAN or DATE classes.
- Manifest (dynamic) typing: the type belongs to the value, not the column.
- Declared column types set an affinity, a conversion preference, not a law.
- college.db (students + marks) is this subject's running database.
- Memory hook: a tiffin box, not a restaurant.