3. UPDATE, DELETE, NULL, and ALTER TABLE

UPDATE, DELETE, NULL, and ALTER TABLE

After you can create and insert data, the next step is changing existing data. This lesson covers:

NULL values.
UPDATE statements.
DELETE statements.
ALTER TABLE for schema changes.

Create the practice database and table

sql

CREATE DATABASE companydb;

USE companydb;

CREATE TABLE employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(25),
  LastName VARCHAR(50),
  Email VARCHAR(100),
  HireDate DATE,
  Salary DECIMAL(10, 2)
);

This table already has one constraint: EmployeeID is the primary key, so each employee must have a unique ID.

Insert sample employees

sql

INSERT INTO employees (EmployeeID, FirstName, LastName, Email, HireDate, Salary)
VALUES
  (1, "John", "Doe", "john.doe@example.com", "2020-01-15", 60000.00),
  (2, "Jane", "Smith", "jane.smith@example.com", "2019-03-22", 75000.00),
  (3, "Alice", "Johnson", "alice.johnson@example.com", "2021-07-30", 50000.00),
  (4, "Bob", "Brown", "bob.brown@example.com", "2018-11-12", 65000.00);

Read the table:

sql

SELECT *
FROM employees;

Work with NULL values

NULL means the value is missing or unknown. It is not the same as an empty string and it is not the same as zero.

sql

INSERT INTO employees
VALUES
  (5, "Krish", NULL, "krishnaik06@gmail.com", NULL, 55000.00);

Use IS NULL and IS NOT NULL. Do not use = NULL.

sql

SELECT *
FROM employees
WHERE LastName IS NULL;

SELECT *
FROM employees
WHERE LastName IS NULL
   OR HireDate IS NULL;

Update rows

Always include a WHERE clause when you intend to update specific records.

sql

UPDATE employees
SET LastName = "Naik"
WHERE EmployeeID = 5;

Update a numeric column using its existing value:

sql

UPDATE employees
SET Salary = Salary + 10000
WHERE EmployeeID = 5;

Check the result:

sql

SELECT *
FROM employees;

Delete rows

Delete a single employee:

sql

DELETE FROM employees
WHERE EmployeeID = 1;

Delete multiple employees with a condition:

sql

DELETE FROM employees
WHERE Salary < 66000;

Before running a destructive DELETE, preview the matching rows with SELECT.

sql

SELECT *
FROM employees
WHERE Salary < 66000;

Add columns with ALTER TABLE

ALTER TABLE changes the structure of an existing table.

Add one column:

sql

ALTER TABLE employees
ADD COLUMN phone_number VARCHAR(25);

Add multiple columns:

sql

ALTER TABLE employees
ADD COLUMN middle_name VARCHAR(25),
ADD COLUMN date_of_birth DATE;

Modify column types

Change a column definition with MODIFY COLUMN.

sql

ALTER TABLE employees
MODIFY COLUMN phone_number VARCHAR(20);

ALTER TABLE employees
MODIFY COLUMN Salary INT;

After adding the new columns, inserts need values for the new table shape if you omit the column list.

sql

INSERT INTO employees
VALUES
  (6, "Krish", NULL, "krishnaik06@gmail.com", NULL, 55000, "989373733", "C", "1989-09-09");

In production code, prefer an explicit column list for inserts. It makes schema changes safer.

Rename and drop columns

Rename middle_name to middleName:

sql

ALTER TABLE employees
CHANGE COLUMN middle_name middleName VARCHAR(25);

Drop one column:

sql

ALTER TABLE employees
DROP COLUMN date_of_birth;

Drop multiple columns:

sql

ALTER TABLE employees
DROP COLUMN phone_number,
DROP COLUMN middleName;

Schema changes can affect application code, dashboards, and downstream pipelines. Treat ALTER TABLE as a planned change, not a casual cleanup command.