SQLite, DDL & DML: Define and Manipulate Your Data

Explore SQLite’s features and data types, then master DDL commands (CREATE, ALTER, DROP) and DML commands (INSERT, UPDATE, DELETE).

Before you can query data, you need somewhere to store it. This page introduces SQLite — the lightweight, serverless database engine you’ll use throughout this course — and then covers the two families of SQL commands that let you build and populate that database: Data Definition Language (DDL) for shaping the structure of your tables, and Data Manipulation Language (DML) for adding, changing, and removing the rows inside them.

Why SQLite?

SQLite is called a serverless database because it doesn’t require a separate server process. The entire database lives in a single .db file on your filesystem, making it perfect for development, learning, and embedded use cases.

Key characteristics:

• Zero configuration — no installation server required
• Cross-platform — the same .db file works on Windows, macOS, and Linux
• ACID-compliant — your data is safe even if the app crashes
• Fully supports standard SQL syntax

Limitations to keep in mind:

• Not designed for high-concurrency production workloads
• Limited support for ALTER TABLE compared with MySQL or PostgreSQL
• No built-in user authentication

For learning SQL and building FastAPI backends, SQLite is an ideal choice. When you move to production you can swap it for PostgreSQL or MySQL with minimal code changes.

SQL Command Categories

SQL commands are organized into four groups based on their purpose.

Category	Purpose	Commands
DDL — Data Definition Language	Define and modify database structure	CREATE, ALTER, DROP
DML — Data Manipulation Language	Insert, update, and delete data	INSERT, UPDATE, DELETE
DQL — Data Query Language	Retrieve data	SELECT
TCL — Transaction Control Language	Manage transactions	BEGIN, COMMIT, ROLLBACK

This page covers DDL and DML. DQL (SELECT) is covered in full on the next page.

SQLite Data Types

SQLite uses a flexible, dynamic type system. The five core storage classes are:

Type	Description	Example
INTEGER	Whole numbers	10, 101
REAL	Decimal (floating-point) numbers	99.5, 65000.0
TEXT	String values	'Rahul', 'Hyderabad'
BLOB	Raw binary data	Images, files
NULL	Missing or unknown value	NULL

Practice: Choosing Data Types

Choose the appropriate SQLite data type for each attribute:

Attribute	Correct Type
Age	INTEGER
Salary	REAL
Employee Name	TEXT
Profile Photo	BLOB
Unknown value	NULL

DDL: Defining Your Schema

CREATE TABLE

Use CREATE TABLE to define a new table and its columns. You specify each column’s name, data type, and any constraints.

-- Create the department table first (referenced by employee)
CREATE TABLE department (
    department_id   INTEGER PRIMARY KEY,
    department_name TEXT    NOT NULL
);

-- Create the employee table with a foreign key reference
CREATE TABLE employee (
    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 department(department_id)
);

Key constraints explained:

Constraint	Meaning
PRIMARY KEY	Uniquely identifies each row; automatically NOT NULL
NOT NULL	Prevents inserting a NULL value into this column
FOREIGN KEY … REFERENCES	Enforces referential integrity between two tables

Practice: Create a student table

Write a CREATE TABLE statement for a student table with student_id, student_name, and email.

CREATE TABLE student (
    student_id   INTEGER PRIMARY KEY,
    student_name TEXT    NOT NULL,
    email        TEXT
);

ALTER TABLE

Use ALTER TABLE to modify an existing table without dropping and recreating it.

Add a new column:

ALTER TABLE employee
ADD COLUMN email TEXT;

Rename an existing column:

ALTER TABLE employee
RENAME COLUMN joining_date TO start_date;

SQLite has limited ALTER TABLE support compared to other databases. You cannot drop columns or change data types directly in older SQLite versions. In SQLite 3.35+ you can use DROP COLUMN.

Practice: Add a phone column

Add a phone column of type TEXT to the employee table.

ALTER TABLE employee
ADD COLUMN phone TEXT;

DROP TABLE

DROP TABLE permanently deletes a table and all of its data.

DROP TABLE employee;

DROP TABLE is irreversible. All rows in the table are permanently deleted. Always back up your data or use a test database when experimenting with destructive commands.

Practice: Drop the student table

DROP TABLE student;

DML: Manipulating Your Data

INSERT Statement

Use INSERT INTO to add new rows to a table.

Insert a single row (all columns, in order):

INSERT INTO department
VALUES (1, 'Engineering');

Insert multiple rows at once:

INSERT INTO department
VALUES
    (2, 'HR'),
    (3, 'Sales');

Insert by explicitly naming columns (recommended practice):

INSERT INTO employee (
    employee_id,
    employee_name,
    salary,
    city,
    department_id
)
VALUES (
    101,
    'Rahul',
    65000,
    'Hyderabad',
    1
);

Always list column names explicitly in your INSERT statements. This makes your code resilient to future schema changes and easier to read.

Practice: Insert employee Anitha

Insert an employee named Anitha with employee_id 102 and a salary of ₹55,000 in Bengaluru, assigned to department 2.

INSERT INTO employee (
    employee_id,
    employee_name,
    salary,
    city,
    department_id
)
VALUES (
    102,
    'Anitha',
    55000,
    'Bengaluru',
    2
);

UPDATE Statement

Use UPDATE to modify existing rows.

-- Increase employee 101's salary to ₹70,000
UPDATE employee
SET salary = 70000
WHERE employee_id = 101;

You can update multiple columns in one statement:

UPDATE employee
SET salary = 75000,
    city   = 'Mumbai'
WHERE employee_id = 101;

Always include a WHERE clause with UPDATE unless you genuinely intend to update every row in the table. Running UPDATE employee SET salary = 0; without a filter will zero out every employee’s salary.

Practice: Raise Rahul's salary

Increase the salary of the employee named Rahul to ₹75,000.

UPDATE employee
SET salary = 75000
WHERE employee_name = 'Rahul';

DELETE Statement

Use DELETE FROM to remove rows that match a condition.

-- Remove the employee with ID 101
DELETE FROM employee
WHERE employee_id = 101;

Omitting the WHERE clause deletes all rows from the table while leaving the table structure intact. This is different from DROP TABLE, which removes the table itself.

Practice: Delete employee Sneha

DELETE FROM employee
WHERE employee_name = 'Sneha';

Summary

In this chapter you learned:

• Why SQLite is a great choice for learning and development
• The four SQL command categories: DDL, DML, DQL, and TCL
• SQLite’s five data types: INTEGER, REAL, TEXT, BLOB, NULL
• CREATE TABLE — define new tables with constraints
• ALTER TABLE — add or rename columns
• DROP TABLE — permanently delete a table
• INSERT — add single or multiple rows
• UPDATE — modify existing rows (always use WHERE!)
• DELETE — remove rows (always use WHERE!)

The next chapter focuses entirely on DQL (SELECT) — retrieving, filtering, sorting, grouping, and analyzing the data you’ve stored.