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

# SQLite, DDL & DML

> Learn SQLite basics, SQL command categories, table creation, and data manipulation.

## Why SQLite?

* What is SQLite?
* Features of SQLite
* Advantages
* Limitations
* SQLite Data Types

### Practice

<Question />

<Accordion title="Solution">
  Explain why SQLite is called a **serverless database**.
</Accordion>

## SQL Command Categories

### DDL (Data Definition Language)

**Purpose:** Defines and modifies the structure of database objects.

**Commands**

* CREATE
* ALTER
* DROP

#### Practice

<Question />

<Accordion title="Solution">
  DDL commands change the database schema by creating, modifying, or deleting database objects.
</Accordion>

### DML (Data Manipulation Language)

**Purpose:** Inserts, updates, and deletes data stored in tables.

**Commands**

* INSERT
* UPDATE
* DELETE

#### Practice

<Question />

<Accordion title="Solution">
  DML commands work with the data inside existing tables.
</Accordion>

### DQL (Brief Introduction)

* SELECT
* Covered in detail in the next chapter.

### TCL (Brief Introduction)

* BEGIN
* COMMIT
* ROLLBACK

## SQLite Data Types

| Type    | Description     | Example |
| ------- | --------------- | ------- |
| INTEGER | Whole numbers   | 10      |
| REAL    | Decimal numbers | 99.5    |
| TEXT    | Strings         | "Rahul" |
| BLOB    | Binary data     | Images  |
| NULL    | Missing value   | NULL    |

### Practice

<Question />

<Accordion title="Solution">
  Choose the appropriate SQLite data type for:

  * Age → INTEGER
  * Salary → REAL
  * Name → TEXT
</Accordion>

## Creating Tables

### Syntax

```sql theme={null}
CREATE TABLE department (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT NOT NULL
);
```

```sql theme={null}
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)
);
```

### Explanation

* **PRIMARY KEY** — Uniquely identifies each row.
* **NOT NULL** — Prevents NULL values.
* **FOREIGN KEY** — Creates a relationship with another table.

### Practice

Create a `student` table with `student_id`, `student_name`, and `email`.

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

## ALTER TABLE

Used to modify an existing table.

### Add a Column

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

### Rename a Column

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

### Practice

Add a `phone` column to the `employee` table.

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

## DROP TABLE

Deletes an entire table permanently.

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

> **Note:** All data in the table is permanently deleted.

### Practice

Delete the `student` table.

<Accordion title="Solution">
  ```sql theme={null}
  DROP TABLE student;
  ```
</Accordion>

## INSERT Statement

Used to insert new rows into a table.

### Insert a Single Row

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

### Insert Multiple Rows

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

### Insert by Specifying Columns

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

### Practice

Insert an employee named **Anitha** with a salary of **55000**.

<Accordion title="Solution">
  ```sql theme={null}
  INSERT INTO employee (
      employee_id,
      employee_name,
      salary
  )
  VALUES (
      102,
      'Anitha',
      55000
  );
  ```
</Accordion>

## UPDATE Statement

Used to modify existing records.

### Syntax

```sql theme={null}
UPDATE employee
SET salary = 70000
WHERE employee_id = 101;
```

> **Important:** Always use a `WHERE` clause unless you intend to update every row.

### Practice

Increase Rahul's salary to ₹75,000.

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

## DELETE Statement

Deletes one or more rows.

```sql theme={null}
DELETE FROM employee
WHERE employee_id = 101;
```

> **Important:** Omitting the `WHERE` clause deletes all rows.

### Practice

Delete the employee named **Sneha**.

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

## Summary

In this chapter, you learned:

* SQLite basics
* SQL command categories
* SQLite data types
* CREATE TABLE
* ALTER TABLE
* DROP TABLE
* INSERT
* UPDATE
* DELETE

The next chapter focuses entirely on **DQL (SELECT)**, where you'll learn how to retrieve and analyze data from the database.