​

Working with SQLite & Database Setup

​

Why SQLite?

​

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

​

Common Examples

​

Prerequisites

• SQLite by Alex Covizzi

• 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:

employee.db

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

SQLite: Open Database

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

​

Step 2: Create the Tables

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

setup.sql

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

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

​

Department Data

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'Engineering'),
(2, 'Human Resources'),
(3, 'Sales'),
(4, 'Finance'),
(5, 'Marketing');

​

Employee Data

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

SELECT *
FROM departments;

SELECT *
FROM employees;

SELECT COUNT(*) AS total_employees
FROM employees;

25

​

Step 5: Execute Sample Queries

SELECT employee_name
FROM employees;

SELECT *
FROM employees
WHERE city = 'Hyderabad';

SELECT *
FROM employees
WHERE salary > 70000;

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

SELECT
    e.employee_name,
    d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

SELECT
    e.employee_name,
    d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

​

Database Schema

Departments
-----------
department_id (PK)
department_name
       ▲
       │
       │ department_id
       │
Employees
---------
employee_id (PK)
employee_name
salary
city
joining_date
department_id (FK)

​

Summary

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