> ## Documentation Index
> Fetch the complete documentation index at: https://fastapi2day.codewithsiva.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# SQLite, DDL & DML: Define and Manipulate Your Data

> Explore SQLite's features and data types, then master DDL commands (CREATE, ALTER, DROP) and DML commands (INSERT, UPDATE, DELETE).

Before you can query data, you need somewhere to store it. This page introduces **SQLite** — the lightweight, serverless database engine you'll use throughout this course — and then covers the two families of SQL commands that let you build and populate that database: **Data Definition Language (DDL)** for shaping the structure of your tables, and **Data Manipulation Language (DML)** for adding, changing, and removing the rows inside them.

## Why SQLite?

SQLite is called a *serverless* database because it doesn't require a separate server process. The entire database lives in a single `.db` file on your filesystem, making it perfect for development, learning, and embedded use cases.

**Key characteristics:**

* Zero configuration — no installation server required
* Cross-platform — the same `.db` file works on Windows, macOS, and Linux
* ACID-compliant — your data is safe even if the app crashes
* Fully supports standard SQL syntax

**Limitations to keep in mind:**

* Not designed for high-concurrency production workloads
* Limited support for `ALTER TABLE` compared with MySQL or PostgreSQL
* No built-in user authentication

<Note>
  For learning SQL and building FastAPI backends, SQLite is an ideal choice. When you move to production you can swap it for PostgreSQL or MySQL with minimal code changes.
</Note>

## SQL Command Categories

SQL commands are organized into four groups based on their purpose.

| Category                               | Purpose                              | Commands                      |
| -------------------------------------- | ------------------------------------ | ----------------------------- |
| **DDL** — Data Definition Language     | Define and modify database structure | `CREATE`, `ALTER`, `DROP`     |
| **DML** — Data Manipulation Language   | Insert, update, and delete data      | `INSERT`, `UPDATE`, `DELETE`  |
| **DQL** — Data Query Language          | Retrieve data                        | `SELECT`                      |
| **TCL** — Transaction Control Language | Manage transactions                  | `BEGIN`, `COMMIT`, `ROLLBACK` |

This page covers DDL and DML. DQL (`SELECT`) is covered in full on the next page.

## SQLite Data Types

SQLite uses a flexible, dynamic type system. The five core storage classes are:

| Type      | Description                      | Example                  |
| --------- | -------------------------------- | ------------------------ |
| `INTEGER` | Whole numbers                    | `10`, `101`              |
| `REAL`    | Decimal (floating-point) numbers | `99.5`, `65000.0`        |
| `TEXT`    | String values                    | `'Rahul'`, `'Hyderabad'` |
| `BLOB`    | Raw binary data                  | Images, files            |
| `NULL`    | Missing or unknown value         | `NULL`                   |

<Accordion title="Practice: Choosing Data Types">
  Choose the appropriate SQLite data type for each attribute:

  | Attribute     | Correct Type |
  | ------------- | ------------ |
  | Age           | `INTEGER`    |
  | Salary        | `REAL`       |
  | Employee Name | `TEXT`       |
  | Profile Photo | `BLOB`       |
  | Unknown value | `NULL`       |
</Accordion>

## DDL: Defining Your Schema

### CREATE TABLE

Use `CREATE TABLE` to define a new table and its columns. You specify each column's name, data type, and any constraints.

```sql theme={null}
-- Create the department table first (referenced by employee)
CREATE TABLE department (
    department_id   INTEGER PRIMARY KEY,
    department_name TEXT    NOT NULL
);

-- Create the employee table with a foreign key reference
CREATE TABLE employee (
    employee_id   INTEGER PRIMARY KEY,
    employee_name TEXT    NOT NULL,
    salary        REAL    NOT NULL,
    city          TEXT,
    joining_date  TEXT,
    department_id INTEGER,
    FOREIGN KEY (department_id)
        REFERENCES department(department_id)
);
```

**Key constraints explained:**

| Constraint                 | Meaning                                              |
| -------------------------- | ---------------------------------------------------- |
| `PRIMARY KEY`              | Uniquely identifies each row; automatically NOT NULL |
| `NOT NULL`                 | Prevents inserting a NULL value into this column     |
| `FOREIGN KEY … REFERENCES` | Enforces referential integrity between two tables    |

<Accordion title="Practice: Create a student table">
  Write a `CREATE TABLE` statement for a `student` table with `student_id`, `student_name`, and `email`.

  ```sql theme={null}
  CREATE TABLE student (
      student_id   INTEGER PRIMARY KEY,
      student_name TEXT    NOT NULL,
      email        TEXT
  );
  ```
</Accordion>

### ALTER TABLE

Use `ALTER TABLE` to modify an existing table without dropping and recreating it.

**Add a new column:**

```sql theme={null}
ALTER TABLE employee
ADD COLUMN email TEXT;
```

**Rename an existing column:**

```sql theme={null}
ALTER TABLE employee
RENAME COLUMN joining_date TO start_date;
```

<Warning>
  SQLite has limited `ALTER TABLE` support compared to other databases. You cannot drop columns or change data types directly in older SQLite versions. In SQLite 3.35+ you can use `DROP COLUMN`.
</Warning>

<Accordion title="Practice: Add a phone column">
  Add a `phone` column of type `TEXT` to the `employee` table.

  ```sql theme={null}
  ALTER TABLE employee
  ADD COLUMN phone TEXT;
  ```
</Accordion>

### DROP TABLE

`DROP TABLE` permanently deletes a table and all of its data.

```sql theme={null}
DROP TABLE employee;
```

<Warning>
  `DROP TABLE` is irreversible. All rows in the table are permanently deleted. Always back up your data or use a test database when experimenting with destructive commands.
</Warning>

<Accordion title="Practice: Drop the student table">
  ```sql theme={null}
  DROP TABLE student;
  ```
</Accordion>

## DML: Manipulating Your Data

### INSERT Statement

Use `INSERT INTO` to add new rows to a table.

**Insert a single row (all columns, in order):**

```sql theme={null}
INSERT INTO department
VALUES (1, 'Engineering');
```

**Insert multiple rows at once:**

```sql theme={null}
INSERT INTO department
VALUES
    (2, 'HR'),
    (3, 'Sales');
```

**Insert by explicitly naming columns (recommended practice):**

```sql theme={null}
INSERT INTO employee (
    employee_id,
    employee_name,
    salary,
    city,
    department_id
)
VALUES (
    101,
    'Rahul',
    65000,
    'Hyderabad',
    1
);
```

<Tip>
  Always list column names explicitly in your `INSERT` statements. This makes your code resilient to future schema changes and easier to read.
</Tip>

<Accordion title="Practice: Insert employee Anitha">
  Insert an employee named **Anitha** with `employee_id` 102 and a salary of ₹55,000 in Bengaluru, assigned to department 2.

  ```sql theme={null}
  INSERT INTO employee (
      employee_id,
      employee_name,
      salary,
      city,
      department_id
  )
  VALUES (
      102,
      'Anitha',
      55000,
      'Bengaluru',
      2
  );
  ```
</Accordion>

### UPDATE Statement

Use `UPDATE` to modify existing rows.

```sql theme={null}
-- Increase employee 101's salary to ₹70,000
UPDATE employee
SET salary = 70000
WHERE employee_id = 101;
```

You can update multiple columns in one statement:

```sql theme={null}
UPDATE employee
SET salary = 75000,
    city   = 'Mumbai'
WHERE employee_id = 101;
```

<Warning>
  Always include a `WHERE` clause with `UPDATE` unless you genuinely intend to update **every row** in the table. Running `UPDATE employee SET salary = 0;` without a filter will zero out every employee's salary.
</Warning>

<Accordion title="Practice: Raise Rahul's salary">
  Increase the salary of the employee named **Rahul** to ₹75,000.

  ```sql theme={null}
  UPDATE employee
  SET salary = 75000
  WHERE employee_name = 'Rahul';
  ```
</Accordion>

### DELETE Statement

Use `DELETE FROM` to remove rows that match a condition.

```sql theme={null}
-- Remove the employee with ID 101
DELETE FROM employee
WHERE employee_id = 101;
```

<Warning>
  Omitting the `WHERE` clause deletes **all rows** from the table while leaving the table structure intact. This is different from `DROP TABLE`, which removes the table itself.
</Warning>

<Accordion title="Practice: Delete employee Sneha">
  ```sql theme={null}
  DELETE FROM employee
  WHERE employee_name = 'Sneha';
  ```
</Accordion>

## Summary

In this chapter you learned:

* Why SQLite is a great choice for learning and development
* The four SQL command categories: DDL, DML, DQL, and TCL
* SQLite's five data types: `INTEGER`, `REAL`, `TEXT`, `BLOB`, `NULL`
* `CREATE TABLE` — define new tables with constraints
* `ALTER TABLE` — add or rename columns
* `DROP TABLE` — permanently delete a table
* `INSERT` — add single or multiple rows
* `UPDATE` — modify existing rows (always use `WHERE`!)
* `DELETE` — remove rows (always use `WHERE`!)

The next chapter focuses entirely on **DQL (SELECT)** — retrieving, filtering, sorting, grouping, and analyzing the data you've stored.