How to Modify Tables in SQL

-- Create initial table
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department TEXT
);

INSERT INTO employees (name, department) VALUES
  ('Alice', 'Engineering'),
  ('Bob', 'Marketing');

SELECT * FROM employees;

-- Add a new column
ALTER TABLE employees ADD COLUMN salary REAL;

-- Add column with default value
ALTER TABLE employees ADD COLUMN hire_date DATE DEFAULT CURRENT_DATE;

SELECT * FROM employees;

-- Update existing rows with new column data
UPDATE employees SET salary = 75000 WHERE name = 'Alice';
UPDATE employees SET salary = 65000 WHERE name = 'Bob';

SELECT * FROM employees;

-- Note: SQLite has limitations on ALTER TABLE
-- Cannot drop columns directly in older versions
-- Workaround: Create new table, copy data, drop old, rename new

-- Create improved version
CREATE TABLE employees_new (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department TEXT,
  salary REAL,
  hire_date DATE,
  email TEXT
);

-- Copy data
INSERT INTO employees_new (id, name, department, salary, hire_date)
SELECT id, name, department, salary, hire_date FROM employees;

-- Drop old table
DROP TABLE employees;

-- Rename new table
ALTER TABLE employees_new RENAME TO employees;

-- Add email column
ALTER TABLE employees ADD COLUMN email TEXT;

UPDATE employees SET email = 'alice@company.com' WHERE name = 'Alice';
UPDATE employees SET email = 'bob@company.com' WHERE name = 'Bob';

SELECT * FROM employees;