SQL Commands: DDL & DML

​

SQL Command Categories

​

DDL (Data Definition Language)

• CREATE
• ALTER
• DROP

• Create a new table
• Add a new column to an existing table
• Delete a table from the database

• Which command would you use to create a new table?
• Which command would you use to add a new column?
• Does DDL modify the table structure or the data?

​

DML (Data Manipulation Language)

• INSERT
• UPDATE
• DELETE

• Add a new student record
• Update a student’s email address
• Delete an inactive student

• Which command adds new records?
• Which command modifies existing records?
• Which command removes records?

​

DQL (Data Query Language)

• SELECT

• Display all students
• Display students from the CSE department
• Display students whose marks are greater than 80

• Which SQL command is used to retrieve data?
• Does SELECT change the data stored in the table?

​

TCL (Transaction Control Language)

• BEGIN
• COMMIT
• ROLLBACK

• Start a transaction
• Save all changes permanently
• Undo changes before they are committed

• Which command permanently saves changes?
• Which command cancels uncommitted changes?
• Why are transactions important?

​

Creating Tables

​

Naming Conventions

[!TIP] Plural Table Names: It is a very common industry standard and database convention to name tables in their plural form (e.g., employees, departments, students) because a table represents a collection of multiple records. Columns, however, are named in the singular form (e.g., employee_id, salary) as they represent single attributes of a record.

​

Syntax

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

​

Explanation

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

​

Practice

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

​

ALTER TABLE

​

Add a Column

ALTER TABLE employees
ADD COLUMN email TEXT;

​

Rename a Column

ALTER TABLE employees
RENAME COLUMN joining_date TO start_date;

​

Practice

ALTER TABLE employees
ADD COLUMN phone TEXT;

​

DROP TABLE

DROP TABLE employees;

Note: All data in the table is permanently deleted.

​

Practice

DROP TABLE students;

​

INSERT Statement

​

Insert a Single Row

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

​

Insert Multiple Rows

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

​

Insert by Specifying Columns

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

​

Practice

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

​

UPDATE Statement

​

Syntax

UPDATE employees
SET salary = 70000
WHERE employee_id = 101;

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

​

Practice

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

​

DELETE Statement

DELETE FROM employees
WHERE employee_id = 101;

Important: Omitting the WHERE clause deletes all rows.

​

Practice

DELETE FROM employees
WHERE employee_name = 'Sneha';

​

Summary

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