CREATE TABLE, UPDATE & DELETE

Define table structure and safely modify existing data

Knowledge0%

Learn & Drill

Fluency0%

Drill & Speed

Retention0%

Mastery & Review

Confidence0%

All modes

Practice

Free tier: read the explanation here. Upgrade to Pro for Drills, Speed challenges & Mastery badges.

Upgrade

Knowledge

Fluency

Retention

Knowledge Debt detected

You can study this freely — but your score may plateau if these foundations have gaps. The Mastery badge requires them to be solid.

Removing Duplicates with DISTINCT0%

Explanation

CREATE TABLE defines a table's columns, types, and constraints:

sql

CREATE TABLE patients (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  dob DATE,
  insurance_id VARCHAR(50),
  is_active BOOLEAN DEFAULT TRUE
);

Common data types: INTEGER, SERIAL (auto-incrementing integer), VARCHAR(n)/TEXT, DECIMAL(p,s), DATE, BOOLEAN.

Common constraints:

PRIMARY KEY — uniquely identifies each row, automatically indexed and NOT NULL
NOT NULL — column must always have a value
DEFAULT value — used when no value is provided on INSERT
FOREIGN KEY — enforces that a column's value must exist in another table's primary key

sql

CREATE TABLE claims (
  id SERIAL PRIMARY KEY,
  patient_id INT REFERENCES patients(id),
  amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending'
);

ALTER TABLE — change a table's structure after creation: ``sql ALTER TABLE patients ADD COLUMN email TEXT; ALTER TABLE patients DROP COLUMN insurance_id;

UPDATE — modify existing rows. The WHERE clause is critical: ``sql -- Updates ONLY the matching row UPDATE claims SET status = 'denied' WHERE id = 42; -- ⚠️ DANGER: no WHERE = updates EVERY row in the table UPDATE claims SET status = 'denied';

DELETE — remove rows. Same danger applies: ``sql -- Deletes ONLY matching rows DELETE FROM claims WHERE status = 'pending' AND submitted_date < '2025-01-01'; -- ⚠️ DANGER: no WHERE = deletes EVERY row in the table DELETE FROM claims;

DELETE vs TRUNCATE:

DELETE FROM table WHERE ... — removes specific rows, can be rolled back, fires triggers, slower on large tables
TRUNCATE TABLE table — removes ALL rows instantly, resets auto-increment counters, can't be filtered with WHERE

Best practice: before running an UPDATE or DELETE with a WHERE clause, run the same WHERE as a SELECT first to confirm exactly which rows will be affected.

Examples

Create a table with constraints

SERIAL auto-increments. REFERENCES enforces that patient_id exists in patients.id. DEFAULT applies when status is omitted on INSERT.

CREATE TABLE claims (
  id SERIAL PRIMARY KEY,
  patient_id INT REFERENCES patients(id),
  amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  submitted_date DATE NOT NULL
);

Safe UPDATE: check first, then modify

Running the SELECT first shows exactly which rows the UPDATE will touch — before any data changes.

-- Step 1: check what will be affected
SELECT * FROM claims
WHERE status = 'pending' AND submitted_date < CURRENT_DATE - INTERVAL '90 days';

-- Step 2: run the same WHERE as an UPDATE
UPDATE claims SET status = 'denied'
WHERE status = 'pending' AND submitted_date < CURRENT_DATE - INTERVAL '90 days';

How well did you understand this?

Next in SQL & Databases

Window Functions

Continue