# SQLite Basics: A Complete Beginner's Guide

Welcome to the world of SQLite! This guide will teach you everything you need to know about SQLite from scratch. By the end, you'll be able to create databases, store data, and retrieve information like a pro.

## What is SQLite?

SQLite is a **lightweight database** that stores all your data in a single file. Unlike other databases that require a separate server program, SQLite runs directly on your computer inside your application. It's perfect for:

- Mobile apps
- Desktop applications
- Small websites
- Learning and prototyping

## Why Use SQLite?

- **Simple**: No complex setup required
- **Portable**: One file = one database
- **Reliable**: Used by millions of applications worldwide
- **Free**: No license costs
- **Fast**: Great performance for small to medium applications

## Getting Started with SQLite

### 1. Install SQLite (if needed)

Most operating systems come with SQLite pre-installed:
```bash
# Check if SQLite is installed
sqlite3 --version
```

If not, download it from [sqlite.org](https://www.sqlite.org/download.html)

### 2. Open the SQLite Command Line

```bash
# Start SQLite (creates a new database file)
sqlite3 mydatabase.db
```

You'll see a prompt like:
```
SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>
```

## Basic SQL Commands

### Creating Tables

A **table** is where you store your data. Think of it as a spreadsheet.

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

Let's break this down:
- `CREATE TABLE users` - Creates a table named "users"
- `id INTEGER PRIMARY KEY AUTOINCREMENT` - Unique number that automatically increases
- `name TEXT NOT NULL` - Text field that can't be empty
- `email TEXT UNIQUE` - Email must be unique across all records
- `age INTEGER` - Whole numbers only

### Adding Data (INSERT)

```sql
INSERT INTO users (name, email, age) VALUES ('Alice Johnson', 'alice@email.com', 28);
INSERT INTO users (name, email, age) VALUES ('Bob Smith', 'bob@email.com', 35);
```

### Viewing Data (SELECT)

```sql
-- See all data in the table
SELECT * FROM users;

-- See specific columns
SELECT name, email FROM users;

-- See only people over 30
SELECT * FROM users WHERE age > 30;
```

## Common SQL Operations

### SELECT - Get Data

```sql
-- All records
SELECT * FROM users;

-- Specific fields with conditions
SELECT name, age FROM users WHERE age >= 25;

-- Order results
SELECT * FROM users ORDER BY age DESC;

-- Limit results
SELECT * FROM users LIMIT 5;
```

### INSERT - Add New Data

```sql
INSERT INTO users (name, email, age) 
VALUES ('Charlie Brown', 'charlie@email.com', 22);
```

### UPDATE - Change Existing Data

```sql
UPDATE users SET age = 29 WHERE name = 'Alice Johnson';
```

### DELETE - Remove Records

```sql
DELETE FROM users WHERE name = 'Bob Smith';
```

## SQLite Data Types

SQLite uses a flexible type system:

- **TEXT**: Text strings (like names, descriptions)
- **INTEGER**: Whole numbers (1, 2, 3, etc.)
- **REAL**: Decimal numbers (1.5, 3.14, etc.)
- **BLOB**: Binary data (images, files)
- **NULL**: Empty value

## Useful SQLite Commands

### Database Management

```sql
-- See all tables in database
.tables

-- See table structure
.schema users

-- Exit SQLite
.quit
```

### Working with Data

```sql
-- Count records
SELECT COUNT(*) FROM users;

-- Find unique values
SELECT DISTINCT age FROM users;

-- Search for text (case-insensitive)
SELECT * FROM users WHERE name LIKE '%john%';
```

## Practical Example

Let's build a simple contacts app:

```sql
-- Create table
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT,
    phone TEXT,
    email TEXT UNIQUE
);

-- Add some contacts
INSERT INTO contacts (first_name, last_name, phone, email) 
VALUES ('John', 'Doe', '555-1234', 'john.doe@email.com');

INSERT INTO contacts (first_name, last_name, phone, email) 
VALUES ('Jane', 'Smith', '555-5678', 'jane.smith@email.com');

-- View all contacts
SELECT * FROM contacts;

-- Find someone by name
SELECT * FROM contacts WHERE first_name = 'John';
```

## Best Practices

1. **Always create a primary key** - Makes records unique and easier to reference
2. **Use meaningful names** - Table names like "user_profiles" are clearer than "t1"
3. **Plan your data structure first** - Think about what information you need before creating tables
4. **Use constraints** - `NOT NULL`, `UNIQUE` help maintain data quality

## Common Mistakes for Beginners

1. **Forgetting semicolons** - Always end SQL statements with `;`
2. **Not using quotes around text values**
3. **Mixing up table and column names** - SQLite is case-sensitive in some contexts
4. **Forgetting to commit changes** - In some applications, you need to explicitly save changes

## Next Steps

Once you're comfortable with these basics:
- Learn about **JOINs** to connect related data from multiple tables
- Explore **indexes** for faster searches
- Try using SQLite in programming languages like Python or JavaScript
- Practice with more complex queries and data relationships

## Quick Reference Sheet

```sql
-- CREATE TABLE
CREATE TABLE table_name (column1 datatype, column2 datatype);

-- INSERT DATA
INSERT INTO table_name (col1, col2) VALUES (val1, val2);

-- VIEW DATA
SELECT * FROM table_name;
SELECT col1, col2 FROM table_name WHERE condition;

-- UPDATE DATA
UPDATE table_name SET col1 = new_value WHERE condition;

-- DELETE DATA
DELETE FROM table_name WHERE condition;

-- QUICK COMMANDS
.tables         -- List tables
.schema         -- Show table structure  
.quit           -- Exit SQLite
```

Congratulations! You now know the fundamentals of SQLite. Practice with different data sets, and soon you'll be managing databases like a pro!