> ## 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.

# Setting Up SQLite Database

> Create a SQLite database and populate it with sample data for SQL practice.

# Working with SQLite & Database Setup

In this guide, you will learn the fundamentals of SQLite, its data types, and how to set up a local database in VS Code with practice data.

## Why SQLite?

SQLite is a software library that provides a lightweight, serverless relational database management system (RDBMS). Unlike traditional databases like PostgreSQL or MySQL, SQLite does not run as a separate server process. Instead, it reads and writes data directly to a single file on your computer's disk.

### Features of SQLite

* **Serverless:** It does not require a separate server background process. The database engine runs inside the application process.
* **Self-Contained:** A single database is stored entirely in a single cross-platform file.
* **Zero Configuration:** There is no setup or administration needed. You just create a file and start using it.
* **Transactional (ACID):** Even though it is lightweight, it fully supports ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure data safety.

### Advantages

* **Simple to use:** Perfect for learning SQL, prototyping applications, and local development.
* **Highly Portable:** You can copy, share, or email the database file easily.
* **Fast and Lightweight:** Highly optimized code that runs fast and takes up very little memory.
* **Widely Adopted:** It is the most deployed database engine in the world, used in web browsers (Chrome, Firefox), mobile phones (Android, iOS), and many desktop applications.

### Limitations

* **Concurrency Issues:** SQLite locks the entire database file during writes, meaning it is not suitable for high-write concurrency applications.
* **Not suited for Big Data:** While it can handle up to 281 TB database sizes in theory, it is not optimized for massive multi-terabyte enterprise datasets.
* **No User Management:** It does not support native user roles, permissions, or access control. Anyone with read/write access to the file can modify the database.

***

## SQLite Data Types

Unlike other relational databases, SQLite has a dynamic type system. It supports the following storage classes (data types):

| Type        | Description                                                              | Example                         |
| ----------- | ------------------------------------------------------------------------ | ------------------------------- |
| **INTEGER** | Whole numbers (signed integers)                                          | `10`, `101`, `-5`               |
| **REAL**    | Floating-point (decimal) numbers                                         | `99.5`, `65000.50`              |
| **TEXT**    | Text strings (stored using UTF-8/UTF-16 encoding)                        | `"Rahul Sharma"`, `"Hyderabad"` |
| **BLOB**    | Binary Large Object (stored exactly as it was input, e.g. images, files) | Image files                     |
| **NULL**    | Represents a missing or unknown value                                    | `NULL`                          |

### Common Examples

| Data Field                                               | Recommended SQLite Type |
| -------------------------------------------------------- | ----------------------- |
| Employee ID / Department ID                              | `INTEGER`               |
| Employee Name / City                                     | `TEXT`                  |
| Salary                                                   | `REAL`                  |
| Joining Date (stored as ISO8601 text strings YYYY-MM-DD) | `TEXT`                  |
| Profile Image                                            | `BLOB`                  |
| Middle Name (if not provided)                            | `NULL`                  |

***

## Prerequisites

To work with SQLite directly inside your editor, install the following Visual Studio Code extension:

* **SQLite** by Alex Covizzi

The extension allows you to:

* Open and view SQLite databases
* Execute SQL scripts
* Browse tables
* View and edit records
* Run SQL queries

## Step 1: Create a Database File

1. Create a new empty file in your workspace directory named:

```text theme={null}
employee.db
```

2. Open the **Command Palette**:
   * **Windows/Linux:** `Ctrl + Shift + P`
   * **macOS:** `Cmd + Shift + P`
3. Search for and select:

```text theme={null}
SQLite: Open Database
```

4. Select the `employee.db` file you just created from the dropdown menu/file picker.

The database is now open and ready.

## Step 2: Create the Tables

1. Create a new file in your workspace directory named:

```text theme={null}
setup.sql
```

2. Open `setup.sql` and paste the following SQL statements into it:

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

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name TEXT NOT NULL,
    salary REAL NOT NULL,
    city TEXT NOT NULL,
    joining_date TEXT NOT NULL,
    department_id INTEGER,
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);
```

3. Right-click anywhere inside the editor of `setup.sql` and select **Run Query**. When prompted, select the database (`employee.db`) to execute the statements and create the tables.

> \[!IMPORTANT]
> **Executing Queries in VS Code:**
>
> * **Run Selected Query:** You can highlight/select a specific SQL query, right-click, and choose **Run Query** to run only that part.
> * **Avoid Errors:** If you run the entire document after some tables or data have already been created/inserted, SQLite will return errors (e.g., "table already exists"). It is best to highlight and run only the new queries you want to execute, or delete/comment out previously executed queries from `setup.sql`.

## Step 3: Populate the Tables

Append the following SQL statements to your `setup.sql` file, then select the new statements, right-click, and choose **Run Query** to populate the tables.

### Department Data

```sql theme={null}
INSERT INTO departments (department_id, department_name)
VALUES
(1, 'Engineering'),
(2, 'Human Resources'),
(3, 'Sales'),
(4, 'Finance'),
(5, 'Marketing');
```

### Employee Data

```sql theme={null}
INSERT INTO employees (
    employee_id,
    employee_name,
    salary,
    city,
    joining_date,
    department_id
)
VALUES
(101, 'Rahul Sharma',   65000, 'Hyderabad',      '2022-01-15', 1),
(102, 'Priya Reddy',    72000, 'Bengaluru',      '2021-08-20', 1),
(103, 'Arjun Kumar',    58000, 'Chennai',        '2023-02-10', 1),
(104, 'Sneha Patel',    81000, 'Pune',           '2020-11-18', 1),
(105, 'Vikram Singh',   69000, 'Mumbai',         '2022-06-12', 1),

(106, 'Anitha Rao',     52000, 'Hyderabad',      '2023-04-08', 2),
(107, 'Meena Iyer',     56000, 'Chennai',        '2022-09-15', 2),
(108, 'Karthik Nair',   61000, 'Kochi',          '2021-12-01', 2),
(109, 'Pooja Sharma',   54000, 'Delhi',          '2023-01-28', 2),
(110, 'Rohit Gupta',    60000, 'Noida',          '2022-05-09', 2),

(111, 'Ajay Verma',     50000, 'Hyderabad',      '2024-01-10', 3),
(112, 'Neha Kapoor',    64000, 'Mumbai',         '2021-10-21', 3),
(113, 'Suresh Babu',    68000, 'Vijayawada',     '2022-07-18', 3),
(114, 'Divya Menon',    55000, 'Bengaluru',      '2023-03-11', 3),
(115, 'Rakesh Yadav',   73000, 'Lucknow',        '2020-08-14', 3),

(116, 'Harsha Vardhan', 76000, 'Hyderabad',      '2021-06-05', 4),
(117, 'Deepika Joshi',  59000, 'Pune',           '2023-09-17', 4),
(118, 'Nikhil Jain',    82000, 'Indore',         '2020-04-22', 4),
(119, 'Asha Rani',      57000, 'Mysuru',         '2022-12-13', 4),
(120, 'Manoj Kumar',    61000, 'Nagpur',         '2021-11-30', 4),

(121, 'Keerthi Reddy',  66000, 'Hyderabad',      '2022-10-07', 5),
(122, 'Amit Mishra',    62000, 'Delhi',          '2023-05-19', 5),
(123, 'Lakshmi Devi',   71000, 'Chennai',        '2021-07-26', 5),
(124, 'Gopal Krishna',  53000, 'Visakhapatnam',  '2024-02-12', NULL),
(125, 'Swathi Rao',     69000, 'Bengaluru',      '2022-08-16', 5);
```

> **Note:** `Gopal Krishna` has a `NULL` department to demonstrate `LEFT JOIN` and `IS NULL` queries.

## Step 4: Verify the Data

Display all departments.

```sql theme={null}
SELECT *
FROM departments;
```

Display all employees.

```sql theme={null}
SELECT *
FROM employees;
```

Count the total number of employees.

```sql theme={null}
SELECT COUNT(*) AS total_employees
FROM employees;
```

**Expected Output**

```text theme={null}
25
```

## Step 5: Execute Sample Queries

Display all employee names.

```sql theme={null}
SELECT employee_name
FROM employees;
```

Display employees from Hyderabad.

```sql theme={null}
SELECT *
FROM employees
WHERE city = 'Hyderabad';
```

Display employees with salaries greater than ₹70,000.

```sql theme={null}
SELECT *
FROM employees
WHERE salary > 70000;
```

Display employees sorted by salary.

```sql theme={null}
SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;
```

Display employee names with department names.

```sql theme={null}
SELECT
    e.employee_name,
    d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
```

Display all employees, including those without a department.

```sql theme={null}
SELECT
    e.employee_name,
    d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;
```

## Database Schema

```text theme={null}
Departments
-----------
department_id (PK)
department_name
       ▲
       │
       │ department_id
       │
Employees
---------
employee_id (PK)
employee_name
salary
city
joining_date
department_id (FK)
```

## Summary

You have successfully:

* Installed the SQLite extension in Visual Studio Code.
* Created a SQLite database.
* Created the `departments` and `employees` tables.
* Inserted 5 departments and 25 employee records.
* Added one employee with a `NULL` department for practicing `LEFT JOIN` and `IS NULL`.
* Verified the data.
* Executed sample SQL queries.

Your database is now ready to practice all SQL topics, including **SELECT**, **WHERE**, **GROUP BY**, **HAVING**, **JOINs**, **Aggregate Functions**, and **Window Functions**.

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