SQLite, DDL & DML

Why SQLite?

What is SQLite?
Features of SQLite
Advantages
Limitations
SQLite Data Types

Practice

Solution

Explain why SQLite is called a serverless database.

SQL Command Categories

DDL (Data Definition Language)

Purpose: Defines and modifies the structure of database objects. Commands

CREATE
ALTER
DROP

Practice

Solution

DDL commands change the database schema by creating, modifying, or deleting database objects.

DML (Data Manipulation Language)

Purpose: Inserts, updates, and deletes data stored in tables. Commands

INSERT
UPDATE
DELETE

Practice

Solution

DML commands work with the data inside existing tables.

DQL (Brief Introduction)

SELECT
Covered in detail in the next chapter.

TCL (Brief Introduction)

BEGIN
COMMIT
ROLLBACK

SQLite Data Types

Type	Description	Example
INTEGER	Whole numbers	10
REAL	Decimal numbers	99.5
TEXT	Strings	”Rahul”
BLOB	Binary data	Images
NULL	Missing value	NULL

Practice

Solution

Choose the appropriate SQLite data type for:

Age → INTEGER
Salary → REAL
Name → TEXT

Creating Tables

Syntax

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,
    joining_date TEXT,
    department_id INTEGER,
    FOREIGN KEY (department_id)
        REFERENCES department(department_id)
);

Explanation

PRIMARY KEY — Uniquely identifies each row.
NOT NULL — Prevents NULL values.
FOREIGN KEY — Creates a relationship with another table.

Practice

Create a student table with student_id, student_name, and email.

Solution

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

ALTER TABLE

Used to modify an existing table.

Add a Column

ALTER TABLE employee
ADD COLUMN email TEXT;

Rename a Column

ALTER TABLE employee
RENAME COLUMN joining_date TO start_date;

Practice

Add a phone column to the employee table.

Solution

ALTER TABLE employee
ADD COLUMN phone TEXT;

DROP TABLE

Deletes an entire table permanently.

DROP TABLE employee;

Note: All data in the table is permanently deleted.

Practice

Delete the student table.

Solution

DROP TABLE student;

INSERT Statement

Used to insert new rows into a table.

Insert a Single Row

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

Insert Multiple Rows

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

Insert by Specifying Columns

INSERT INTO employee (
    employee_id,
    employee_name,
    salary
)
VALUES (
    101,
    'Rahul',
    65000
);

Practice

Insert an employee named Anitha with a salary of 55000.

Solution

INSERT INTO employee (
    employee_id,
    employee_name,
    salary
)
VALUES (
    102,
    'Anitha',
    55000
);

UPDATE Statement

Used to modify existing records.

Syntax

UPDATE employee
SET salary = 70000
WHERE employee_id = 101;

Important: Always use a WHERE clause unless you intend to update every row.

Practice

Increase Rahul’s salary to ₹75,000.

Solution

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

DELETE Statement

Deletes one or more rows.

DELETE FROM employee
WHERE employee_id = 101;

Important: Omitting the WHERE clause deletes all rows.

Practice

Delete the employee named Sneha.

Solution

DELETE FROM employee
WHERE employee_name = 'Sneha';

Summary

In this chapter, you learned:

SQLite basics
SQL command categories
SQLite data types
CREATE TABLE
ALTER TABLE
DROP TABLE
INSERT
UPDATE
DELETE

The next chapter focuses entirely on DQL (SELECT), where you’ll learn how to retrieve and analyze data from the database.

SQL & Databases

Libraries

Why SQLite?

Practice

SQL Command Categories

DDL (Data Definition Language)

Practice

DML (Data Manipulation Language)

Practice

DQL (Brief Introduction)

TCL (Brief Introduction)

SQLite Data Types

Practice

Creating Tables

Syntax

Explanation

Practice

ALTER TABLE

Add a Column

Rename a Column

Practice

DROP TABLE

Practice

INSERT Statement

Insert a Single Row

Insert Multiple Rows

Insert by Specifying Columns

Practice

UPDATE Statement

Syntax

Practice

DELETE Statement

Practice

Summary

​Why SQLite?

​Practice

​SQL Command Categories

​DDL (Data Definition Language)

​Practice

​DML (Data Manipulation Language)

​Practice

​DQL (Brief Introduction)

​TCL (Brief Introduction)

​SQLite Data Types

​Practice

​Creating Tables

​Syntax

​Explanation

​Practice

​ALTER TABLE

​Add a Column

​Rename a Column

​Practice

​DROP TABLE

​Practice

​INSERT Statement

​Insert a Single Row

​Insert Multiple Rows

​Insert by Specifying Columns

​Practice

​UPDATE Statement

​Syntax

​Practice

​DELETE Statement

​Practice

​Summary

Why SQLite?

Practice

SQL Command Categories

DDL (Data Definition Language)

Practice

DML (Data Manipulation Language)

Practice

DQL (Brief Introduction)

TCL (Brief Introduction)

SQLite Data Types

Practice

Creating Tables

Syntax

Explanation

Practice

ALTER TABLE

Add a Column

Rename a Column

Practice

DROP TABLE

Practice

INSERT Statement

Insert a Single Row

Insert Multiple Rows

Insert by Specifying Columns

Practice

UPDATE Statement

Syntax

Practice

DELETE Statement

Practice

Summary