SQL Basics Part 1: DDL, DML, Constraints & Keys

This is Part 1 of my notes on learning SQL properly from the ground up.

When I started, I was confused by all the three-letter acronyms (DDL, DML) and the different kinds of keys. Once I built small tables and broke things on purpose, the concepts clicked.

---

1. DDL vs DML: The Core Difference

Before writing any SQL, commands divide into two main categories:

• DDL (Data Definition Language): commands that define or change table structure (CREATE, ALTER, DROP, TRUNCATE). Think of this as building bookshelves.
• DML (Data Manipulation Language): commands that interact with the data itself (INSERT, UPDATE, DELETE, SELECT). Think of this as putting books on the shelves.

DDL: Build the container
CREATE TABLE stores (store_id INT, store_name VARCHAR(100));

DML: Put data into the container
INSERT INTO stores VALUES (1, 'Store NYC');

---

2. Step-by-Step: Creating and Populating a Table

Question 1: Create a Database and Stores Table

Question

Create a database named retail_db and a table stores with store_id and store_name.

SQL Query

CREATE DATABASE retail_db;
USE retail_db;

CREATE TABLE stores (
    store_id INT,
    store_name VARCHAR(100)
);

How it works

• CREATE DATABASE builds the workspace.
• USE retail_db; tells MySQL to execute all subsequent statements inside that database.
• CREATE TABLE specifies the schema: two columns with their data types.

Remember

If you forget USE retail_db;, MySQL will throw an error or create your table in whichever random database was active.

---

Question 2: Insert Initial Data into Stores

Question

Insert two store locations: Store 1 (‘Downtown Branch’) and Store 2 (‘Uptown Branch’).

SQL Query

INSERT INTO stores (store_id, store_name)
VALUES
(1, 'Downtown Branch'),
(2, 'Uptown Branch');

Expected Output

Table: stores

store_id	store_name
1	Downtown Branch
2	Uptown Branch

How it works

INSERT INTO writes new rows into the table. The table structure did not change, only the data inside it (DML).

---

Question 3: Add a New Column Using ALTER TABLE

Question

Add a city column to stores, then rename it to location.

SQL Query

ALTER TABLE stores
ADD COLUMN city VARCHAR(100);

ALTER TABLE stores
RENAME COLUMN city TO location;

How it works

ALTER TABLE updates table schema without dropping existing rows.

---

3. Constraints: Enforcing Rules on Your Data

Constraints are guardrails. They tell the database to reject bad data before it ever gets saved.

Constraint	What it enforces	Example error if violated
NOT NULL	Column cannot be left blank	Cannot insert NULL into column
UNIQUE	No duplicate values allowed	Duplicate entry ‘a@b.com’ for key
PRIMARY KEY	Unique + Not Null (Row identifier)	Duplicate entry ‘1’ for key ‘PRIMARY’
CHECK	Must satisfy a logical condition	Check constraint ‘chk_age’ violated
DEFAULT	Auto-fills a value if none is provided	Inserts default string or number

Question 4: Create an Employees Table with Guardrail Constraints

Question

Create an employees table where emp_id is the primary key, email must be unique, age must be 18 or older, and country defaults to ‘USA’.

SQL Query

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INT CHECK (age >= 18),
    country VARCHAR(50) DEFAULT 'USA'
);

Expected Table Definition

Column	Type	Constraints
emp_id	INT	PRIMARY KEY (NOT NULL + UNIQUE)
emp_name	VARCHAR(100)	NOT NULL
email	VARCHAR(150)	UNIQUE
age	INT	CHECK (age >= 18)
country	VARCHAR(50)	DEFAULT 'USA'

How it works

If someone tries to insert INSERT INTO employees (emp_id, emp_name, age) VALUES (1, 'Alex', 16);, MySQL blocks the insert because age < 18.

---

4. Understanding Keys Visually

Table: employees (emp_id PK)
+--------+----------+-------------------+
| emp_id | emp_name | email (Candidate) |
+--------+----------+-------------------+
|    101 | Sarah    | sarah@sky.net     |
+--------+----------+-------------------+
    ^
    |
    | Foreign Key Reference
    |
Table: employee_salaries (salary_id PK, emp_id FK)
+-----------+--------+--------+
| salary_id | emp_id | amount |
+-----------+--------+--------+
|         1 |    101 |  85000 |
+-----------+--------+--------+

Key Types Summary

• Primary Key: The unique fingerprint for each row. Only 1 per table. Never NULL.
• Foreign Key: A column that references the Primary Key of another table. It connects related data.
• Candidate Key: Any column that could have been chosen as the Primary Key (e.g., email).
• Composite Key: A Primary Key made of two or more columns together (e.g., PRIMARY KEY (order_id, product_id)).
• Surrogate Key: An artificial auto-incrementing ID (id INT AUTO_INCREMENT PRIMARY KEY) with no real-world meaning, created purely for database indexing.

---

5. TRUNCATE vs DELETE

• TRUNCATE TABLE employees;: (DDL) Fast. Deletes all rows immediately and resets counters. Cannot use WHERE.
• DELETE FROM employees WHERE id = 1;: (DML) Row-by-row. Slower. Allows WHERE conditions.

---

6. Common Mistakes to Avoid

1. Forgetting USE database: Always select the active database before running CREATE TABLE.
2. Confusing Candidate Key and Alternate Key: Candidate keys are all keys eligible to be the Primary Key. The ones you didn’t choose become Alternate Keys.
3. Inserting child rows before parent rows: A Foreign Key requires the referenced parent row to exist first.

---

30-Second Quick Revision

The Core Idea:

• DDL (CREATE, ALTER, DROP, TRUNCATE): modifies table structure.
• DML (SELECT, INSERT, UPDATE, DELETE): modifies data inside tables.
• Primary Key: unique row identifier (NOT NULL + UNIQUE).
• Foreign Key: links to another table’s primary key.
• Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT.
• TRUNCATE: wipes all rows instantly; DELETE: removes rows with WHERE.