SQL — Create Table

Basic Syntax

CREATE TABLE table_name (
  column1 datatype constraints,
  column2 datatype constraints,
  ...
);

Simple Example

CREATE TABLE employees (
  id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  salary DECIMAL(10, 2)
);

Common Data Types

Type	Description	Example
INT	Whole numbers	42
VARCHAR(n)	Variable-length text	'Alice'
TEXT	Unlimited text	Long descriptions
DECIMAL(p, s)	Exact decimal	99.99
FLOAT	Approximate decimal	3.14
DATE	Date only	'2025-01-15'
DATETIME	Date and time	'2025-01-15 09:30:00'
BOOLEAN	True/false	TRUE
BLOB	Binary data	Images, files

Adding Constraints

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
  category VARCHAR(50) DEFAULT 'Uncategorized',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Common Constraints

• PRIMARY KEY — uniquely identifies each row
• NOT NULL — value cannot be empty
• UNIQUE — no duplicate values allowed
• DEFAULT — sets a value when none is provided
• CHECK — validates data against a condition
• AUTO_INCREMENT — automatically generates the next number (MySQL)

Creating a Table with a Foreign Key

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT DEFAULT 1,
  order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Creating Table If Not Exists

Prevents errors when the table already exists:

CREATE TABLE IF NOT EXISTS users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(50) NOT NULL
);

Copying Table Structure

-- Copy structure only (no data)
CREATE TABLE employees_backup LIKE employees;

-- Copy structure and data
CREATE TABLE employees_archive AS
SELECT * FROM employees;

Modifying After Creation

-- Add a column
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- Remove a column
ALTER TABLE employees DROP COLUMN phone;

-- Change column type
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12, 2);

Dropping a Table

DROP TABLE employees_backup;

-- Safe version: won't error if it doesn't exist
DROP TABLE IF EXISTS employees_backup;

Best Practices

• Always use PRIMARY KEY for the id column
• Use NOT NULL unless a column genuinely needs to be empty
• Choose the smallest data type that fits your data
• Add DEFAULT values for columns that commonly have a standard value
• Use IF NOT EXISTS in scripts to avoid errors on repeated runs

Practice

1. Create a students table with id, name, email, and enrollment_date
2. Create a courses table with id, title, credits, and a default of 3 credits
3. Create an enrollments table linking students and courses with foreign keys