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

# Setting Up Your SQLite Practice Database in VS Code

> Install the SQLite VS Code extension, create an employee database, populate it with 25 sample records, and verify everything is working.

Before you can practice SQL queries, you need a real database with real data. This guide walks you through installing the SQLite extension for Visual Studio Code, creating a `department` and `employee` table, and loading 25 sample employees across five departments. One of those employees deliberately has no department assigned — you'll use that record later to practice `LEFT JOIN` and `IS NULL` queries.

## Prerequisites

You need **Visual Studio Code** and the following extension:

* **SQLite** by Alex Covizzi (search for `alexcovizzi.vscode-sqlite` in the Extensions panel)

The extension lets you:

* Create and open `.db` database files
* Execute SQL scripts directly from the editor
* Browse tables and their schemas
* View and edit records with a graphical interface

<Steps>
  ### Install the SQLite Extension

  1. Open Visual Studio Code.
  2. Click the **Extensions** icon in the left sidebar (or press `Ctrl+Shift+X` / `Cmd+Shift+X`).
  3. Search for **SQLite**.
  4. Install the extension published by **Alex Covizzi**.

  ### Create a New Database

  1. Open the **Command Palette** (`Ctrl+Shift+P` on Windows/Linux, `Cmd+Shift+P` on macOS).
  2. Type and select **SQLite: Open Database**.
  3. Choose **Create New Database** from the prompt.
  4. Save it as `employee.db` in your project folder.

  The database file is now created and ready to receive tables.

  ### Create the Tables

  Open a new SQL file (e.g., `setup.sql`) in VS Code, paste the statements below, then right-click and choose **Run Query**.

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

  CREATE TABLE employee (
      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 department(department_id)
  );
  ```

  ### Populate the Department Table

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

  ### Populate the Employee Table

  ```sql theme={null}
  INSERT INTO employee (
      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>
    Employee **Gopal Krishna** (ID 124) has `NULL` as the `department_id`. This is intentional — you'll use this record when practising `LEFT JOIN` and `IS NULL` queries in later chapters.
  </Note>

  ### Verify the Data

  Run these verification queries to confirm everything loaded correctly.

  ```sql theme={null}
  -- Show all departments
  SELECT * FROM department;

  -- Show all employees
  SELECT * FROM employee;

  -- Count total employees (should return 25)
  SELECT COUNT(*) AS total_employees
  FROM employee;
  ```

  Expected count output:

  ```text theme={null}
  25
  ```
</Steps>

## Database Schema Diagram

```text theme={null}
Department
──────────────────
department_id  (PK)
department_name
       ▲
       │ department_id (FK)
       │
Employee
──────────────────
employee_id    (PK)
employee_name
salary
city
joining_date
department_id  (FK)
```

## Sample Queries to Try Now

Once your database is set up, practice these queries to make sure everything is working:

**Display all employee names:**

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

**Display employees from Hyderabad:**

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

**Display employees earning more than ₹70,000:**

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

**Display employees sorted by salary (highest first):**

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

**Join employees with their department names:**

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

**Show all employees, including those without a department:**

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

<Tip>
  Run the LEFT JOIN query and compare its output to the INNER JOIN. Notice that Gopal Krishna appears in the LEFT JOIN result with `NULL` for `department_name`, but is absent from the INNER JOIN result. This is the key difference between the two join types.
</Tip>

## What You've Accomplished

You have:

* Installed the SQLite extension in Visual Studio Code
* Created a `employee.db` SQLite database file
* Defined the `department` and `employee` tables with appropriate constraints and a foreign key relationship
* Inserted 5 departments and 25 employee records (including one with a NULL department)
* Verified the data with count and selection queries
* Run your first JOIN queries

Your database is now ready for all the SQL topics covered in the next chapters — SELECT, WHERE, GROUP BY, HAVING, JOINs, aggregate functions, and window functions.