Introduction to SQLite

🧠 What is SQLite?

SQLite is a lightweight, serverless, and self-contained SQL database engine. Unlike databases like MySQL or PostgreSQL, SQLite doesn't run as a separate server — it stores the database in a single file on your local system.

Key Features:

Zero configuration (no setup or server needed)
Entire database is a single file
Great for mobile apps, embedded systems, and local applications
Cross-platform and open-source

🛠️ How to Create and Use SQLite Databases

🔧 1. Install SQLite

Windows/Mac/Linux: Download the SQLite command-line tools from https://sqlite.org/download.html
You can also use SQLite from:
- Python (sqlite3 module)
- GUI tools like DB Browser for SQLite
- Mobile apps (Android, iOS)

🗂️ 2. Create a Database

sqlite3 my_database.db

This opens the SQLite prompt and creates a file my_database.db.

📁 3. Create a Table

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER
);

➕ 4. Insert Data

INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 25);

📋 5. View Data

SELECT * FROM users;

✍️ Writing Basic SQL Queries

📌 Select Columns

SELECT name, email FROM users;

📌 Filtering Rows

SELECT * FROM users WHERE age > 20;

📌 Updating Records

UPDATE users SET age = 26 WHERE name = 'Alice';

📌 Deleting Records

DELETE FROM users WHERE name = 'Alice';

📌 Sorting Results

SELECT * FROM users ORDER BY age DESC;

✅ Practical Examples and Hands-On Practice

Here are a few exercises to try:

🔍 1. Create a "books" table

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    year_published INTEGER
);

📘 2. Insert 3 books

INSERT INTO books (title, author, year_published)
VALUES ('1984', 'George Orwell', 1949),
       ('The Hobbit', 'J.R.R. Tolkien', 1937),
       ('To Kill a Mockingbird', 'Harper Lee', 1960);

📖 3. Select books published before 1950

SELECT * FROM books WHERE year_published < 1950;

📝 4. Update a book's title

UPDATE books SET title = 'Nineteen Eighty-Four' WHERE title = '1984';

❌ 5. Delete a book

DELETE FROM books WHERE title = 'The Hobbit';

🔗 Extra Tools for Practice

DB Browser for SQLite (GUI tool): https://sqlitebrowser.org/
SQL Fiddle (browser-based SQL playground): http://sqlfiddle.com/
SQLite Online: https://sqliteonline.com/

What is SQLite?

SQLite is a lightweight, serverless, self-contained relational database management system (RDBMS). Unlike traditional databases like MySQL or PostgreSQL, SQLite does not require a separate server process — the entire database engine is embedded in the application.

It stores the entire database in a single file on disk and is implemented in C, making it small, fast, and portable.

Key Differences: SQLite vs. MySQL / PostgreSQL

Feature	SQLite	MySQL / PostgreSQL
Server Requirement	Serverless (embedded in app)	Requires running server daemon
Setup Complexity	Extremely simple (zero-config)	Requires installation, configuration
Performance	Great for low to moderate traffic	Better for high concurrency workloads
Concurrency	Limited (writes are serialized)	Robust support for concurrent access
File Format	Single file	Multiple files and server-managed storage
Use Case	Local, mobile, embedded, testing	Web apps, enterprise systems, analytics
ACID Compliance	Yes	Yes
Feature Richness	Basic SQL support	Full SQL, complex queries, stored procs

Use Cases for SQLite

Mobile applications – Used by Android and iOS for local storage
Embedded systems – Used in IoT devices, routers, TVs, etc.
Desktop apps – Great for offline mode (e.g., Firefox, Skype use it)
Testing environments – Easy setup makes it perfect for unit testing
Prototyping – Quickly set up local storage without heavy infrastructure
Game development – Save state and user data

Key Features of SQLite

✅ Serverless – No separate database server required
✅ Lightweight – Small footprint (~600 KB)
✅ Cross-platform – Works on Windows, Linux, macOS, iOS, Android
✅ Self-contained – Entire DB is a single .sqlite or .db file
✅ Transactional – Fully ACID-compliant
✅ Zero configuration – No setup or admin overhead

Installation Options

1. SQLite CLI Tool

You can download the command-line tool from the official SQLite website.

Install on macOS (via Homebrew):
```
brew install sqlite
```
Install on Linux (Debian/Ubuntu):
```
sudo apt install sqlite3
```
Install on Windows:
- Download the precompiled binaries
- Add to system PATH

Once installed, you can use:

sqlite3 mydatabase.db

2. DB Browser for SQLite (GUI Tool)

Free, open-source GUI for SQLite
Available for Windows, macOS, Linux
Download from: https://sqlitebrowser.org

1. Creating a new SQLite database (.db file)

SQLite databases are just files on your disk. When you connect to a new database file using SQLite, if the file doesn’t exist, it will be created automatically.

Example in Python with sqlite3 module:

import sqlite3

# Connect to a new database file (it creates the file if it doesn't exist)
conn = sqlite3.connect('mydatabase.db')

# Close the connection when done
conn.close()

Or using the SQLite command line:

sqlite3 mydatabase.db

2. Creating tables using `CREATE TABLE`

Once connected, you can create tables with SQL CREATE TABLE statements.

Basic syntax:

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype NOT NULL,
    column3 datatype,
    ...
);

Example:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT,
    age INTEGER,
    balance REAL
);

You execute this SQL command via your connection interface (like Python or the SQLite shell).

3. Data types in SQLite

SQLite is flexible with data types. It uses dynamic typing with type affinity. Here are the main types:

Data Type Description

INTEGER Whole numbers (e.g., 1, 42, -7)

TEXT Text strings (e.g., "hello", "user123")

REAL Floating point numbers (e.g., 3.14, -0.001)

BLOB Binary Large Objects (raw binary data, like images)

NULL Represents a NULL value (no data)

Data Type	Description
INTEGER	Whole numbers (e.g., 1, 42, -7)
TEXT	Text strings (e.g., "hello", "user123")
REAL	Floating point numbers (e.g., 3.14, -0.001)
BLOB	Binary Large Objects (raw binary data, like images)
NULL	Represents a NULL value (no data)

Summary example:

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    price REAL,
    image BLOB
);

1. Inserting Data: `INSERT INTO`

To insert data into a table (say your table is called basics), the syntax is:

INSERT INTO basics (column1, column2, column3)
VALUES (value1, value2, value3);

Example:

INSERT INTO basics (name, age, city)
VALUES ('Alice', 30, 'New York');

If you want to insert multiple rows:

INSERT INTO basics (name, age, city)
VALUES 
  ('Bob', 25, 'Los Angeles'),
  ('Carol', 28, 'Chicago');

2. Writing SELECT Queries

Basic syntax:

SELECT column1, column2 FROM basics;

To select all columns:

SELECT * FROM basics;

3. Filtering with `WHERE`

Use WHERE to filter rows:

SELECT * FROM basics
WHERE age > 25;

You can use comparison operators like =, <, >, <=, >=, <> (not equal) and logical operators AND, OR, NOT.

Example:

SELECT * FROM basics
WHERE city = 'New York' AND age >= 30;

4. Sorting with `ORDER BY`

Sort results ascending (ASC) or descending (DESC):

SELECT * FROM basics
ORDER BY age ASC;

or descending:

SELECT * FROM basics
ORDER BY age DESC;

You can order by multiple columns:

SELECT * FROM basics
ORDER BY city ASC, age DESC;

5. Limiting Results with `LIMIT`

To get a fixed number of rows, use LIMIT:

SELECT * FROM basics
LIMIT 5;

This returns the first 5 rows of the query result.

You can combine LIMIT with ORDER BY to get, say, the top 3 oldest people:

SELECT * FROM basics
ORDER BY age DESC
LIMIT 3;

SQLite: Joins, Indexes & Query Optimization — Simple Version

1. JOIN types in SQLite (INNER, LEFT)

INNER JOIN:
Shows only the rows where there is a match in both tables.
Example: Show customers who have orders.

LEFT JOIN:
Shows all rows from the first table, and matching rows from the second table if there are any. If no match, you still get the first table’s data but with empty (NULL) values for the second table.
Example: Show all customers, even if they don’t have orders.

2. Primary & Foreign Keys

Primary Key:
A special column that uniquely identifies each row in a table (like an ID). No two rows can have the same primary key.

Foreign Key:
A column in one table that points to the primary key in another table. It creates a link between the two tables (like saying which order belongs to which customer).

3. Indexes: When and Why

What is an index?
Like the index in a book — it helps SQLite find data quickly without searching every row.

When to use indexes?

When you often search or join using a column.

On primary key and foreign key columns.

Why use indexes?
To make queries faster.

Be careful:
Too many indexes can slow down adding or updating data.