• Why SQLite?
• SQLite Data Types
• Prerequisites
• Connecting Database Client to Any Database
• Step 1: Create Database and Connect in Database Client
• Step 2: Create Tables Using .sql Script
• Step 3: Populate Tables Using .sql Script
• Step 4: Verify the Data
• Step 5: Execute Sample Practice Queries
• Option 2: Using SQLite Extension Plugin
• Running SQL in Jupyter Notebooks
• Database Schema
• Summary

​

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

1. Database Client (by cweijan) — ⭐ Primary / Recommended Universal Tool
   • Universal database manager for SQLite, PostgreSQL, MySQL, MariaDB, SQL Server, Redis, MongoDB, Snowflake, DuckDB, etc.
   • Built-in visual data grid, SQL query editor with auto-completion, ER diagram generator, mock data generator, and export tools (CSV/JSON/SQL).
   • Manages a dedicated Query folder in your project structure for your .sql files.
2. SQLite (by Alex Covizzi) — Alternative Lightweight Option
   • Simple extension specifically for opening .db files and running queries.

​

Connecting Database Client to Any Database

​

1. Supported Databases

​

2. The Query Folder in Project Structure

your-project-workspace/
├── employee.db          <-- SQLite database file
├── Query/               <-- Managed SQL Scripts Folder
│   ├── setup.sql        <-- DDL & DML setup statements
│   ├── queries.sql      <-- Practice SELECT queries
│   └── analysis.sql     <-- Advanced JOIN & Aggregation queries

​

3. Connecting to External Databases (PostgreSQL, MySQL, etc.)

1. Click the Database (cylinder) icon in the VS Code Activity Bar (left sidebar).
2. Click + (Create Connection) $\rightarrow$ select your database engine (e.g. PostgreSQL or MySQL).
3. Enter your connection credentials:
   • Host: localhost (or cloud endpoint)
   • Port: 5432 (PostgreSQL) / 3306 (MySQL)
   • Username: postgres / root
   • Password: <your-password>
   • Database: <database_name>
4. Click Test Connection, then click Connect.

​

Step 1: Create Database and Connect in Database Client

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

employee.db

2. Open the Database Client panel by clicking the Database (cylinder) icon in the VS Code Activity Bar (left sidebar).
3. Click the + (Create Connection) button at the top of the panel.
4. Select SQLite from the database list.
5. Enter the connection settings:
   • Connection Name: Employee DB
   • Database Path / File: Click the Browse / File Picker icon and select employee.db (or type ./employee.db).
6. Click Connect (or Save).

Database Client Panel
└── 🔌 Employee DB (SQLite)
    ├── 📁 main
    │   ├── 📁 Tables
    │   └── 📁 Views

​

Step 2: Create Tables Using .sql Script

1. Create a new SQL script file in your workspace directory (or inside your Query/ folder) named:

setup.sql

2. Open setup.sql and paste the table definition DDL statements:

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. Execute in Database Client:
   • In VS Code with setup.sql open, make sure Employee DB is selected in the Database Client toolbar or status bar.
   • Highlight the CREATE TABLE statements.
   • Press Cmd + Enter (macOS) or Ctrl + Enter (Windows/Linux), or click the green Run / Execute button in the query toolbar.
4. Click the Refresh icon in the Database Client panel. You will now see departments and employees listed under the Tables folder!

[!IMPORTANT] Executing Selected Queries: Always highlight/select the specific SQL statements you want to run before executing (Cmd+Enter / Ctrl+Enter). Running the whole script again after tables are created will result in a “table already exists” error.

​

Step 3: Populate Tables Using .sql Script

​

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.

​

Execute in Database Client:

1. Highlight the INSERT INTO statements.
2. Press Cmd + Enter (macOS) or Ctrl + Enter (Windows/Linux) to run the insertion.
3. In the Database Client sidebar, double-click the employees table to open the interactive data grid and view all 25 rows populated live!

​

Step 4: Verify the Data

​

Method 1: Using Database Client Interactive Data Grid (Fastest)

​

Method 2: Running Verification Queries in SQL Console

SELECT *
FROM departments;

SELECT *
FROM employees;

SELECT COUNT(*) AS total_employees
FROM employees;

25

​

Step 5: Execute Sample Practice Queries

​

1. Display all employee names

SELECT employee_name
FROM employees;

​

2. Display employees from Hyderabad

SELECT *
FROM employees
WHERE city = 'Hyderabad';

​

3. Display employees with salaries greater than ₹70,000

SELECT *
FROM employees
WHERE salary > 70000;

​

4. Display employees sorted by salary descending

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

​

5. Display employee names with their department names (INNER JOIN)

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

​

6. Display all employees including those without a department (LEFT JOIN)

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

​

Advanced Database Client Features:

• 📊 Column Sorting & Filtering: Click on any column header in the result grid to sort ascending/descending or filter by values.
• 🗺️ Visual ER Diagram (ERD): Right-click Employee DB in the sidebar and choose Visual Relationship (ERD) to view relationships between departments and employees.
• 📤 Export Results: Right-click the result table or any table in the sidebar to export data into CSV, JSON, or executable SQL INSERT scripts.

​

Option 2: Using SQLite Extension Plugin

​

Step 1: Install the Extension

1. Open the Extensions view (Ctrl + Shift + X on Windows/Linux or Cmd + Shift + X on macOS).
2. Search for SQLite by Alex Covizzi and click Install.

​

Step 2: Open Your Database

1. Open the Command Palette:
   • Windows/Linux: Ctrl + Shift + P
   • macOS: Cmd + Shift + P
2. Search for and select: SQLite: Open Database.
3. Choose employee.db from your workspace directory.
4. An SQLITE EXPLORER section will appear in your VS Code Explorer sidebar showing the database and its tables.

​

Step 3: Run Queries from .sql Files

1. Open setup.sql in VS Code.
2. Select/highlight any query (or block of queries) you wish to run.
3. Right-click anywhere in the editor and choose Run Query (or press Cmd + Shift + Q / Ctrl + Shift + Q).
4. When prompted, select employee.db.
5. The query results will appear in a separate SQLite Results panel in VS Code.

​

Step 4: Browse Tables & Records

• In the SQLITE EXPLORER panel in the Explorer sidebar, expand employee.db to see all tables (departments, employees).
• Click the play / show table icon next to any table to view all its records instantly.

​

Running SQL in Jupyter Notebooks

​

Approach 1: Using %sql Cell Magics (ipython-sql)

​

Step 1: Install Required Packages & Versions

pip install "ipykernel>=7.3.0" "ipython-sql>=0.5.0" "prettytable<3.12" pandas

[!NOTE] prettytable<3.12 prevents compatibility issues when rendering tabular SQL results with ipython-sql.

​

Step 2: Load Extension & Connect to Database

%load_ext sql
%sql sqlite:///employee.db

​

Step 3: Run Single-Line SQL Queries (%sql)

%sql SELECT * FROM employees WHERE city = 'Hyderabad';

​

Step 4: Run Multi-Line SQL Queries (%%sql)

%%sql
SELECT 
    d.department_name,
    COUNT(e.employee_id) AS total_employees,
    ROUND(AVG(e.salary), 2) AS average_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY average_salary DESC;

​

Step 5: Convert Query Result to Pandas DataFrame

result = %sql SELECT * FROM employees WHERE salary > 70000
df = result.DataFrame()
df.head()

​

Approach 2: Using Python sqlite3 + pandas

import sqlite3
import pandas as pd

# 1. Connect to SQLite database
conn = sqlite3.connect("employee.db")

# 2. Execute SQL query and return a Pandas DataFrame
query = """
SELECT 
    employee_name,
    salary,
    city
FROM employees
WHERE salary > 60000
ORDER BY salary DESC;
"""

df = pd.read_sql_query(query, conn)
display(df)

# 3. Always close the connection when finished
conn.close()

[!TIP] Which approach should you use?

• Use Approach 1 (%%sql magic) for quick, interactive SQL exploration directly in notebook cells.
• Use Approach 2 (sqlite3 + pandas) when building data analysis pipelines, creating visualizations (Matplotlib/Seaborn), or writing production Python scripts.

​

Database Schema

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

​

Summary

• Connected and managed databases using Database Client in Visual Studio Code as the primary universal method.
• Explored Option 2 using the lightweight SQLite plugin by Alex Covizzi.
• Created the employee.db database and managed .sql scripts.
• Created the departments and employees tables using DDL scripts.
• Populated 5 departments and 25 employee records using DML scripts.
• Verified data visually via the interactive data grid and through verification queries.
• Executed sample SQL queries and explored ER Diagram visualization.