SQLite is a lightweight, serverless, self-contained SQL database engine that stores data in a single file. It's commonly used in mobile applications because:

• Lightweight: Small footprint (~500KB library size)
• Serverless: No separate server process required
• Zero-configuration: No setup or administration needed
• Cross-platform: Works on iOS, Android, and other mobile platforms
• ACID compliant: Ensures data integrity
• Fast: Optimized for local storage and retrieval
• Embedded: Runs directly within the application process

Mobile apps benefit from SQLite's offline capabilities, allowing data persistence without network connectivity.

SQLite supports five storage classes:

1. NULL: Null value
2. INTEGER: Signed integers (1, 2, 3, 4, 6, or 8 bytes)
3. REAL: Floating-point numbers (8-byte IEEE floating point)
4. TEXT: Text strings (UTF-8, UTF-16BE, or UTF-16LE encoding)
5. BLOB: Binary data stored exactly as input

SQLite uses dynamic typing - columns can store any type of data regardless of declared type. However, it's good practice to declare appropriate types for clarity.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    salary REAL,
    profile_picture BLOB
);

Database connection varies by platform but follows similar patterns:

Android (Java/Kotlin):

SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase("/path/to/database.db", null);

iOS (Swift):

var db: OpaquePointer?
sqlite3_open("database.db", &db)

Key considerations for mobile:

• Store database in app's private directory
• Handle connection lifecycle properly
• Close connections when app goes to background
• Use connection pooling for better performance
• Consider using higher-level ORMs like Room (Android) or Core Data (iOS) that handle connections automatically

DELETE:

• Removes specific rows based on WHERE clause
• Can be rolled back in a transaction
• Triggers fire for each deleted row
• Auto-increment counters are not reset

DELETE FROM users WHERE age < 18;

DROP:

• Removes entire table structure and data
• Cannot be rolled back
• Frees all storage space immediately

DROP TABLE users;

TRUNCATE:

• SQLite doesn't support TRUNCATE command
• Use DELETE FROM table_name to remove all rows
• To reset auto-increment: DELETE FROM sqlite_sequence WHERE name='table_name'