DataBolt

All Lessons

Introduction to SQL

SQL Lesson 1: Your Sample Database

SQL Lesson 2: SELECT — Reading data

SQL Lesson 3: WHERE — Filtering rows

SQL Lesson 4: AND, OR, NOT

SQL Lesson 5: BETWEEN, IN, LIKE

SQL Lesson 6: NULL — The Mystery Value

SQL Lesson 7: ORDER BY

SQL Lesson 8: LIMIT & OFFSET

SQL Lesson 9: Aggregate Functions

SQL Lesson 10: GROUP BY

SQL Lesson 11: HAVING

SQL Lesson 12: INNER JOINs

SQL Lesson 13: LEFT JOINs

SQL Lesson 14: RIGHT JOINs

SQL Lesson 15: SELF JOINs

SQL Lesson 16: UNION JOINs

SQL Lesson 17: Joining Multiple Tables

SQL Lesson 18: Subqueries

SQL Lesson 19: CTEs (WITH)

SQL Lesson 20: CASE Statements

SQL Lesson 21: Window Functions

SQL Lesson 22: String Functions

SQL Lesson 23: Date & Time Functions

SQL Lesson 24: INSERT, UPDATE, DELETE

SQL Lesson 25: CREATE TABLE & DDL

SQL Lesson 26: Indexes & Performance

SQL Lesson 27: Transactions & ACID

SQL Lesson 28: SQL Execution Order

SQL Lesson 29: 50 Practice Problems

CHAPTER 26

CREATE TABLE & DDL

Designing and modifying database structure.

IN THIS CHAPTER

CREATE TABLE with all constraint types
PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, DEFAULT, CHECK
ALTER TABLE — adding, modifying, dropping columns
DROP TABLE — remove a table entirely

Creating our employees table from scratch

CREATE TABLE employees (
    emp_id        INT PRIMARY KEY AUTO_INCREMENT,
    name          VARCHAR(100)    NOT NULL,
    department    VARCHAR(50)     NOT NULL,
    salary        DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    manager_id    INT,
    hire_date     DATE            NOT NULL,
    CONSTRAINT chk_salary CHECK (salary >= 0),
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

Constraints Reference

Constraint	Purpose
PRIMARY KEY	Unique + NOT NULL identifier for every row
NOT NULL	Column cannot be omitted or set to NULL
UNIQUE	All values in this column must be different across rows
DEFAULT	Value used automatically when INSERT omits this column
CHECK(condition)	INSERT/UPDATE fails if condition evaluates to false
FOREIGN KEY	Value must exist in the referenced table (referential integrity)
AUTO_INCREMENT	Database automatically assigns the next integer value

ALTER TABLE

SQL

ALTER TABLE employees ADD COLUMN phone VARCHAR(15);
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2) NOT NULL;
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees RENAME COLUMN department TO dept;