SQLite DDL and DML: Define and Populate Your Database Tables

Before you can query data you need somewhere to put it. This page teaches you the two halves of SQL that build and fill a database: DDL (Data Definition Language) shapes the structure of your tables, and DML (Data Manipulation Language) manages the rows inside them. You will build a complete Employee Management schema and populate it with realistic data by the end.

SQL Command Categories at a Glance

DDL
DML
DQL (preview)
TCL (preview)

DDL statements define and modify the structure of your database. They affect tables and columns, not rows.

Command	What it does
`CREATE`	Creates a new table (or index, view)
`ALTER`	Adds or renames columns on an existing table
`DROP`	Permanently deletes a table and all its data

DML statements operate on the data (rows) stored inside tables.

Command	What it does
`INSERT`	Adds new rows
`UPDATE`	Modifies values in existing rows
`DELETE`	Removes rows

DQL has one command — SELECT — covered in full in the next page. It reads data without changing it.

TCL manages transaction boundaries: BEGIN, COMMIT, ROLLBACK. Covered when you connect Python to SQLite.

SQLite Data Types

SQLite uses a flexible type affinity system. Every column has an affinity — a preferred type — but SQLite will store any value in any column. Understanding the five native types keeps your schema intentional and your data clean.

Type	Stores	Example value
`INTEGER`	Whole numbers (signed 1–8 bytes)	`101`, `-5`, `0`
`REAL`	Floating-point numbers (8-byte IEEE)	`65000.50`, `3.14`
`TEXT`	UTF-8 / UTF-16 strings	`'Rahul'`, `'Hyderabad'`
`BLOB`	Raw binary data stored exactly as provided	Image bytes, encrypted data
`NULL`	Missing or unknown value	`NULL`

Use TEXT for dates and times in SQLite (stored as 'YYYY-MM-DD'). SQLite has date functions that work on text in that format, and it avoids time-zone headaches.

DDL — Creating Tables

The CREATE TABLE statement defines your table’s name, its columns, each column’s type, and any constraints.

Basic Syntax

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Constraints

Constraints enforce data rules at the database level so bad data never gets in.

Constraint	Meaning
`PRIMARY KEY`	Uniquely identifies each row; value must be unique and non-null
`NOT NULL`	Column must always have a value
`UNIQUE`	All values in the column must be distinct
`DEFAULT value`	Supplies a value when `INSERT` omits the column
`FOREIGN KEY`	Value must match a primary key in another table

Creating the Department Table

CREATE TABLE department (
    department_id   INTEGER PRIMARY KEY,
    department_name TEXT    NOT NULL
);

department_id is the primary key. department_name is required — you cannot insert a department without a name.

Creating the Employee Table

CREATE TABLE employee (
    employee_id   INTEGER PRIMARY KEY,
    employee_name TEXT    NOT NULL,
    salary        REAL    NOT NULL,
    city          TEXT    NOT NULL DEFAULT 'Unknown',
    joining_date  TEXT    NOT NULL,
    department_id INTEGER,
    FOREIGN KEY (department_id)
        REFERENCES department(department_id)
);

Why is department_id nullable here?

An employee might not yet be assigned to a department — perhaps they are newly hired. The FOREIGN KEY constraint still ensures that if a value is provided, it must exist in the department table. NULL means “not yet known”, which is a valid real-world state.

What does DEFAULT 'Unknown' do?

If you INSERT a row and omit the city column, SQLite automatically stores 'Unknown' instead of NULL. This gives you a non-null fallback without forcing every INSERT to supply the value.

How does INTEGER PRIMARY KEY work in SQLite?

In SQLite, INTEGER PRIMARY KEY is a special alias for the internal rowid. If you insert a row without supplying employee_id, SQLite auto-increments it. This is SQLite’s version of AUTO_INCREMENT.

Practice: Create a student table

Create a student table with a required student_id primary key, a required student_name, and an optional email.

Solution

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

email has no NOT NULL constraint, so it accepts NULL when the email is not yet known.

DDL — Altering Tables

Use ALTER TABLE when you need to change a table’s structure after it has been created.

Add a Column

ALTER TABLE employee
ADD COLUMN email TEXT;

The new email column is added to every existing row with a value of NULL.

Rename a Column

ALTER TABLE employee
RENAME COLUMN joining_date TO start_date;

SQLite supports ADD COLUMN and RENAME COLUMN. It does not support DROP COLUMN in older versions (prior to 3.35.0). For complex schema changes on older SQLite, the recommended approach is to create a new table, copy data, and drop the old one.

Practice: Add a phone column

Add an optional phone column of type TEXT to the employee table.

Solution

ALTER TABLE employee
ADD COLUMN phone TEXT;

DDL — Dropping Tables

DROP TABLE permanently deletes the table and every row inside it. There is no undo.

DROP TABLE student;

Use DROP TABLE IF EXISTS to avoid an error when the table might not exist:

DROP TABLE IF EXISTS student;

Always double-check your target before running DROP TABLE. If foreign keys reference the table you are dropping, SQLite will raise an error to protect data integrity.

DML — INSERT

INSERT INTO adds 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, 'Human Resources'),
    (3, 'Sales'),
    (4, 'Finance'),
    (5, 'Marketing');

Batching inserts is much faster than running separate INSERT statements for each row.

Insert by Naming Columns (recommended)

Naming columns explicitly makes your SQL resilient to future schema changes and instantly readable.

INSERT INTO employee (employee_id, employee_name, salary, city, joining_date, department_id)
VALUES (101, 'Rahul Sharma', 65000, 'Hyderabad', '2022-01-15', 1);

Insert Multiple Employees

INSERT INTO employee (employee_id, employee_name, salary, city, joining_date, department_id)
VALUES
    (102, 'Priya Reddy',   72000, 'Bengaluru', '2021-08-20', 1),
    (103, 'Anitha Rao',    52000, 'Hyderabad', '2023-04-08', 2),
    (104, 'Sneha Patel',   81000, 'Pune',      '2020-11-18', 1),
    (105, 'Ajay Verma',    50000, 'Hyderabad', '2024-01-10', 3);

Practice: Insert a new employee

Insert an employee named Kiran Kumar with employee ID 106, salary 68000, city Chennai, joining date '2022-07-01', and department ID 2.

Solution

INSERT INTO employee (employee_id, employee_name, salary, city, joining_date, department_id)
VALUES (106, 'Kiran Kumar', 68000, 'Chennai', '2022-07-01', 2);

DML — UPDATE

UPDATE modifies existing rows. Always pair it with a WHERE clause — without one, every row in the table changes.

Syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

Update a Single Employee’s Salary

UPDATE employee
SET salary = 70000
WHERE employee_id = 101;

Update Multiple Columns at Once

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

Update All Rows in a Department

UPDATE employee
SET salary = salary * 1.10
WHERE department_id = 1;

This gives every Engineering employee a 10% pay rise.

Forgetting the WHERE clause in UPDATE changes every row in the table. Always verify your condition with a SELECT query first before running the UPDATE.

Practice: Correct a salary

Anitha Rao’s salary should be 56000, not 52000. Write the UPDATE statement to fix it.

Solution

UPDATE employee
SET salary = 56000
WHERE employee_name = 'Anitha Rao';

Using employee_id is safer in production since names are not guaranteed to be unique:

UPDATE employee
SET salary = 56000
WHERE employee_id = 103;

DML — DELETE

DELETE removes rows from a table. Like UPDATE, always include a WHERE clause unless you intentionally want to clear the entire table.

Delete a Specific Row

DELETE FROM employee
WHERE employee_id = 105;

Delete Based on a Condition

DELETE FROM employee
WHERE salary < 30000;

Delete All Rows (keep the table structure)

DELETE FROM employee;

This removes every row but keeps the employee table itself intact, ready for new data.

DELETE vs. DROP

	`DELETE FROM table`	`DROP TABLE table`
Removes rows	✅ Yes	✅ Yes (all of them)
Removes table structure	❌ No	✅ Yes
Can use `WHERE`	✅ Yes	❌ No
Recoverable with `ROLLBACK`	✅ Yes (inside a transaction)	❌ No

Practice: Remove an employee

Delete the employee with employee_id = 106 (Kiran Kumar, added in the INSERT practice above).

Solution

DELETE FROM employee
WHERE employee_id = 106;

Putting It All Together

Here is the full sequence to create and populate a minimal Employee Management database from scratch:

-- 1. Create tables
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 DEFAULT 'Unknown',
    joining_date  TEXT NOT NULL,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES department(department_id)
);

-- 2. Populate departments
INSERT INTO department VALUES
    (1, 'Engineering'),
    (2, 'Human Resources'),
    (3, 'Sales'),
    (4, 'Finance'),
    (5, 'Marketing');

-- 3. Populate employees
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, 'Anitha Rao',    56000, 'Hyderabad', '2023-04-08', 2),
    (104, 'Sneha Patel',   81000, 'Pune',      '2020-11-18', 1),
    (105, 'Ajay Verma',    50000, 'Hyderabad', '2024-01-10', 3);

-- 4. Verify
SELECT * FROM department;
SELECT * FROM employee;

In the next page you will run all of these statements from Python using sqlite3, so you can automate database creation as part of your application’s startup code.

Next: SQLite Setup

Connect Python to SQLite using the built-in sqlite3 module and run your DDL and DML statements from a script.

Project Development

SQL & Databases

Data Analysis

SQLite DDL and DML: Define and Populate Your Database Tables

SQL Command Categories at a Glance

SQLite Data Types

DDL — Creating Tables

Basic Syntax

Constraints

Creating the Department Table

Creating the Employee Table

Practice: Create a student table

DDL — Altering Tables

Add a Column

Rename a Column

Practice: Add a phone column

DDL — Dropping Tables

DML — INSERT

Insert a Single Row (all columns in order)

Insert Multiple Rows at Once

Insert by Naming Columns (recommended)

Insert Multiple Employees

Practice: Insert a new employee

DML — UPDATE

Syntax

Update a Single Employee’s Salary

Update Multiple Columns at Once

Update All Rows in a Department

Practice: Correct a salary

DML — DELETE

Delete a Specific Row

Delete Based on a Condition

Delete All Rows (keep the table structure)

DELETE vs. DROP

Practice: Remove an employee

Putting It All Together

Next: SQLite Setup

​SQL Command Categories at a Glance

​SQLite Data Types

​DDL — Creating Tables

​Basic Syntax

​Constraints

​Creating the Department Table

​Creating the Employee Table

​Practice: Create a student table

​DDL — Altering Tables

​Add a Column

​Rename a Column

​Practice: Add a phone column

​DDL — Dropping Tables

​DML — INSERT

​Insert a Single Row (all columns in order)

​Insert Multiple Rows at Once

​Insert by Naming Columns (recommended)

​Insert Multiple Employees

​Practice: Insert a new employee

​DML — UPDATE

​Syntax

​Update a Single Employee’s Salary

​Update Multiple Columns at Once

​Update All Rows in a Department

​Practice: Correct a salary

​DML — DELETE

​Delete a Specific Row

​Delete Based on a Condition

​Delete All Rows (keep the table structure)

​DELETE vs. DROP

​Practice: Remove an employee

​Putting It All Together

Next: SQLite Setup

SQL Command Categories at a Glance

SQLite Data Types

DDL — Creating Tables

Basic Syntax

Constraints

Creating the Department Table

Creating the Employee Table

Practice: Create a student table

DDL — Altering Tables

Add a Column

Rename a Column

Practice: Add a phone column

DDL — Dropping Tables

DML — INSERT

Insert a Single Row (all columns in order)

Insert Multiple Rows at Once

Insert by Naming Columns (recommended)

Insert Multiple Employees

Practice: Insert a new employee

DML — UPDATE

Syntax

Update a Single Employee’s Salary

Update Multiple Columns at Once

Update All Rows in a Department

Practice: Correct a salary

DML — DELETE

Delete a Specific Row

Delete Based on a Condition

Delete All Rows (keep the table structure)

DELETE vs. DROP

Practice: Remove an employee

Putting It All Together