🗃️

SQL Basics — DDL & DML with Constraints

Build the structure, then furnish it. CREATE, ALTER, DROP and TRUNCATE define tables; INSERT, UPDATE and DELETE manage data; constraints keep it clean.

DDL DML Constraints Practice Problems

Press Next → or use ← → arrow keys

Section 01

The Story — Build the House, Then Furnish It

🏗️ REAL-WORLD ANALOGY

Architecture vs interior design

Architects build the physical structure — walls, rooms, doorways — before the movers arrive with furniture. SQL works in the same two phases: DDL is the architecture that constructs tables and their structural rules; DML is the moving company that fills and rearranges the data inside; and constraints are the building codes that keep everything sound.

⚠️

The Core Distinction

DDL changes the structure (the table itself); DML changes the data (the rows). DDL commands auto-commit and usually cannot be rolled back — verify carefully before you run them.

Section 02

The SQL Command Families

Family	Purpose	Commands
DDL — Data Definition	Define / change structure	CREATE · ALTER · DROP · TRUNCATE
DML — Data Manipulation	Manipulate data	INSERT · UPDATE · DELETE
DQL — Data Query	Query data	SELECT
DCL — Data Control	Control access	GRANT · REVOKE
TCL — Transaction Control	Manage transactions	COMMIT · ROLLBACK · SAVEPOINT

🏷️

Where Does SELECT Belong?

Modern SQL classifies SELECT as DQL (Data Query Language), though older texts group it with DML. Either way, it reads data without changing structure.

Section 03 · DDL

CREATE TABLE — Building the Structure

CREATE TABLE DEPARTMENT (
    Dept_ID  INT          PRIMARY KEY,
    Dname    VARCHAR(40)  NOT NULL
);
CREATE TABLE EMPLOYEE (
    Emp_ID   INT          PRIMARY KEY,
    Name     VARCHAR(50)  NOT NULL,
    Age      INT          CHECK (Age >= 18),
    Salary   INT          DEFAULT 30000,
    Email    VARCHAR(80)  UNIQUE,
    Dept_ID  INT,
    FOREIGN KEY (Dept_ID) REFERENCES DEPARTMENT(Dept_ID)
);

Section 04

Constraints — The Guardrails of Your Data

Constraint	Enforces	Example
NOT NULL	Column cannot be empty	`Name VARCHAR(50) NOT NULL`
UNIQUE	No duplicates (one NULL allowed)	`Email VARCHAR(80) UNIQUE`
PRIMARY KEY	UNIQUE + NOT NULL; identifies each row	`Emp_ID INT PRIMARY KEY`
FOREIGN KEY	Value must exist in parent's PK	`REFERENCES DEPARTMENT(Dept_ID)`
CHECK	Value must satisfy a condition	`CHECK (Age >= 18)`
DEFAULT	Value used when none is given	`Salary INT DEFAULT 30000`

🔑

Primary Key vs Unique

A table has exactly one PRIMARY KEY (which forbids NULLs), but it can have many UNIQUE columns — and a UNIQUE column may hold a single NULL.

Section 05 · DDL

ALTER TABLE — Changing the Structure

ALTER TABLE EMPLOYEE ADD Joining_Date DATE;              -- add a column
ALTER TABLE EMPLOYEE MODIFY Name VARCHAR(80);            -- change a type
ALTER TABLE EMPLOYEE ADD CONSTRAINT chk_sal CHECK (Salary > 0);  -- add a rule
ALTER TABLE EMPLOYEE DROP COLUMN Joining_Date;          -- remove a column

🧱

Structure Changes, Data Survives

ALTER modifies a table without losing the rows already in it — existing rows just get NULL in any new column.

Section 06

DELETE vs TRUNCATE vs DROP

Feature	DELETE	TRUNCATE	DROP
Family	DML	DDL	DDL
Removes	Chosen rows	All rows	Whole table
WHERE clause	Yes	No	No
Can roll back	Yes	Usually no	No
Structure after	Remains	Remains	Gone
Speed	Slower (row by row)	Fast	Fast

Section 07 · DML

INSERT — Adding Rows

-- Parent (DEPARTMENT) first, for the foreign key
INSERT INTO DEPARTMENT VALUES (10, 'Sales'), (20, 'Tech');

INSERT INTO EMPLOYEE VALUES (1, 'Raj', 30, 50000, 'raj@co.com', 10);  -- full row

-- Named columns: Salary defaults to 30000, Email becomes NULL
INSERT INTO EMPLOYEE (Emp_ID, Name, Age, Dept_ID) VALUES (3, 'Amit', 19, 10);

EMPLOYEE after inserts

1 Raj 30 50000 raj@co.com 10 2 Sara 25 40000 sara@co.com 20 3 Amit 19 30000 NULL 10 ← Salary from DEFAULT, Email NULL

Section 08 · DML

UPDATE — Changing Existing Rows

-- Give everyone in department 10 a 10% raise
UPDATE EMPLOYEE SET Salary = Salary * 1.10 WHERE Dept_ID = 10;

🚨

The Missing WHERE

UPDATE EMPLOYEE SET Salary = 0; with no WHERE zeroes out every salary in the table. There's no undo on an auto-committed change.

Section 09 · DML

DELETE — Removing Rows

DELETE FROM EMPLOYEE WHERE Age < 21;   -- removes matching rows only (Amit)
DELETE FROM EMPLOYEE;                 -- removes ALL rows; prefer TRUNCATE for speed

↩️

DELETE Is DML — It Can Be Rolled Back

Unlike TRUNCATE, DELETE works row by row, supports a WHERE clause, and can be rolled back inside a transaction.

Section 10 · Practice

Practice — Create & Insert

-- Problem 1: create the tables (5 constraint types)
CREATE TABLE COURSE ( Course_ID INT PRIMARY KEY, Title VARCHAR(50) NOT NULL );
CREATE TABLE STUDENT (
    Roll      INT          PRIMARY KEY,
    Name      VARCHAR(50)  NOT NULL,
    Marks     INT          CHECK (Marks >= 0 AND Marks <= 100),
    Grade     CHAR(2)      DEFAULT 'NA',
    Course_ID INT,
    FOREIGN KEY (Course_ID) REFERENCES COURSE(Course_ID)
);

-- Problem 2: insert data (one Grade left unset to test DEFAULT)
INSERT INTO COURSE VALUES (101, 'DBMS');
INSERT INTO STUDENT VALUES (1, 'Raj', 82, 'A', 101);
INSERT INTO STUDENT (Roll, Name, Marks, Course_ID) VALUES (3, 'Amit', 91, 101);

STUDENT after inserts

1 Raj 82 A 101 2 Sara 55 C 101 3 Amit 91 NA 101 ← Grade from DEFAULT 'NA'

Section 10 · Practice

Practice — Alter, Update, Clean Up

-- Problem 3: add a column, then a CHECK constraint
ALTER TABLE STUDENT ADD Age INT;
ALTER TABLE STUDENT ADD CONSTRAINT chk_age CHECK (Age >= 17);

-- Problem 4: promote high scorers
UPDATE STUDENT SET Grade = 'A' WHERE Marks > 90;   -- Amit (91) → A

-- Problem 5: delete, then clean up
DELETE FROM STUDENT WHERE Marks < 60;   -- removes Sara (55)
TRUNCATE TABLE STUDENT;                  -- (a) empty rows, keep table
DROP TABLE STUDENT;                      -- (b) remove the table entirely

🎓

One Workflow, the Entire Core

You used CREATE with five constraint types, INSERT (with a DEFAULT), ALTER (add column + constraint), UPDATE with a condition, and DELETE / TRUNCATE / DROP — the whole DDL + DML core.

Section 11 · Cheat Sheet

Command Cheat Sheet

Command	Family	Job	Skeleton
CREATE TABLE	DDL	Build a table	`CREATE TABLE t (col type constraint, …)`
ALTER TABLE	DDL	Change structure	`ALTER TABLE t ADD / MODIFY / DROP …`
DROP TABLE	DDL	Delete table + data	`DROP TABLE t`
TRUNCATE TABLE	DDL	Empty all rows	`TRUNCATE TABLE t`
INSERT	DML	Add rows	`INSERT INTO t VALUES (…)`
UPDATE	DML	Change rows	`UPDATE t SET col = v WHERE …`
DELETE	DML	Remove rows	`DELETE FROM t WHERE …`

Section 12

Three Common Mistakes to Avoid

🚨

UPDATE / DELETE without WHERE

No WHERE means every row is affected. Confirm the condition first — there is no undo on an auto-committed change.

🔀

DELETE vs TRUNCATE vs DROP

DELETE removes chosen rows (DML, reversible); TRUNCATE empties the table (DDL, fast); DROP destroys the table itself (DDL, permanent).

🔗

Child before parent

A foreign-key value must already exist in the parent. Insert DEPARTMENT / COURSE rows before the rows that reference them.

🧭

The Safety Habit

Before any UPDATE or DELETE, run the same WHERE as a SELECT first — see exactly which rows you're about to change.

Section 13

Golden Rules of DDL & DML

🏆 NON-NEGOTIABLE RULES

1

DDL changes structure; DML changes data. Know which family a command belongs to before you run it.

2

Define constraints at CREATE time. They are the guardrails that keep invalid data out permanently.

3

Every table needs exactly one primary key. Add UNIQUE for other no-duplicate columns.

4

Never run UPDATE or DELETE without a WHERE unless you truly mean "all rows."

5

DDL auto-commits — there is no undo. Double-check DROP and TRUNCATE on real databases.

6

Respect foreign keys. Insert parents before children, and delete children before parents.

FINAL

Structure First, Then Data

5Command families

4DDL commands

3DML commands

6Constraint types

WHEREYour safety net

🎯

The Foundation Is Set

You can now build tables with the right constraints, populate and reshape them safely, and tell DELETE, TRUNCATE and DROP apart. Next comes reading data in depth — SELECT with aggregate functions, GROUP BY, HAVING and nested subqueries.

🧠

One Sentence to Remember

DDL builds and reshapes the table, DML fills and edits the rows, constraints reject bad data automatically — and a missing WHERE hits every row.

🗃️ End of tutorial · Press ← to review, or click Restart