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

# SQL Commands: DDL & DML

> Learn about the types of SQL commands (DDL, DML, DQL, TCL) with simple examples.

## SQL Command Categories

SQL (Structured Query Language) contains many commands, which are categorized based on their functionality:

### DDL (Data Definition Language)

**Purpose:** Defines or changes the structure of database objects.

**Commands**

* CREATE
* ALTER
* DROP

**Examples**

* Create a new table
* Add a new column to an existing table
* Delete a table from the database

**Think**

* Which command would you use to create a new table?
* Which command would you use to add a new column?
* Does DDL modify the table structure or the data?

### DML (Data Manipulation Language)

**Purpose:** Adds, updates, or removes data stored in tables.

**Commands**

* INSERT
* UPDATE
* DELETE

**Examples**

* Add a new student record
* Update a student's email address
* Delete an inactive student

**Think**

* Which command adds new records?
* Which command modifies existing records?
* Which command removes records?

### DQL (Data Query Language)

**Purpose:** Retrieves data from one or more tables.

**Command**

* SELECT

**Examples**

* Display all students
* Display students from the CSE department
* Display students whose marks are greater than 80

**Think**

* Which SQL command is used to retrieve data?
* Does `SELECT` change the data stored in the table?

### TCL (Transaction Control Language)

**Purpose:** Controls transactions and ensures data consistency.

**Commands**

* BEGIN
* COMMIT
* ROLLBACK

**Examples**

* Start a transaction
* Save all changes permanently
* Undo changes before they are committed

**Think**

* Which command permanently saves changes?
* Which command cancels uncommitted changes?
* Why are transactions important?

## Creating Tables

### Naming Conventions

> \[!TIP]
> **Plural Table Names:**
> It is a very common industry standard and database convention to name tables in their **plural form** (e.g., `employees`, `departments`, `students`) because a table represents a collection of multiple records. Columns, however, are named in the **singular form** (e.g., `employee_id`, `salary`) as they represent single attributes of a record.

### Syntax

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

```sql theme={null}
CREATE TABLE employees (
    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 departments(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 `students` table with `student_id`, `student_name`, and `email`.

<Accordion title="Solution">
  ```sql theme={null}
  CREATE TABLE students (
      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 employees
ADD COLUMN email TEXT;
```

### Rename a Column

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

### Practice

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

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

## DROP TABLE

Deletes an entire table permanently.

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

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

### Practice

Delete the `students` table.

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

## INSERT Statement

Used to insert new rows into a table.

### Insert a Single Row

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

### Insert Multiple Rows

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

### Insert by Specifying Columns

```sql theme={null}
INSERT INTO employees (
    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 employees (
      employee_id,
      employee_name,
      salary
  )
  VALUES (
      102,
      'Anitha',
      55000
  );
  ```
</Accordion>

## UPDATE Statement

Used to modify existing records.

### Syntax

```sql theme={null}
UPDATE employees
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 employees
  SET salary = 75000
  WHERE employee_name = 'Rahul';
  ```
</Accordion>

## DELETE Statement

Deletes one or more rows.

```sql theme={null}
DELETE FROM employees
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 employees
  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 on **Setting Up SQLite Database**, where you will install the VS Code extension, create your database file, and populate it with practice data.