Creating and Modifying Tables: DDL

So far you've only read data. Now let's look at how tables themselves are created and changed — the practice database's own tables were built using exactly these commands.

CREATE TABLE

CREATE TABLE students (
  id INT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE,
  enrolled_on DATE DEFAULT CURRENT_DATE
);

Common data types

Category	Common types
Whole numbers	INT / INTEGER
Decimal numbers	DECIMAL(10,2), NUMERIC, FLOAT
Short text	VARCHAR(n) — PostgreSQL/MySQL, NVARCHAR(n) — SQL Server
Long text	TEXT
Dates	DATE, DATETIME / TIMESTAMP
True/false	BOOLEAN (SQLite has no true boolean — it uses 0/1 integers)

Auto-incrementing primary keys

This is the syntax that differs the most between engines:

-- PostgreSQL
CREATE TABLE students (id SERIAL PRIMARY KEY, full_name TEXT);

-- MySQL
CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(100));

-- SQL Server
CREATE TABLE students (id INT IDENTITY(1,1) PRIMARY KEY, full_name NVARCHAR(100));

-- SQLite (INTEGER PRIMARY KEY auto-increments by default)
CREATE TABLE students (id INTEGER PRIMARY KEY, full_name TEXT);

ALTER TABLE — changing an existing table

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

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

-- Rename a column (PostgreSQL / MySQL 8+ / SQL Server syntax differs slightly)
ALTER TABLE students RENAME COLUMN full_name TO student_name;

DROP TABLE

DROP TABLE students;
DROP TABLE IF EXISTS students; -- won't error if it doesn't exist

DROP TABLE is permanent and deletes both the structure and all the data — there's no undo. Always double-check you're pointed at the right database before running it.

TRUNCATE — emptying a table fast

TRUNCATE TABLE students;

Removes all rows but keeps the table structure — much faster than DELETE FROM students for large tables, though it usually can't be rolled back and may reset auto-increment counters. (SQLite doesn't have TRUNCATE — use DELETE FROM students; instead.)

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;