++++

Data Engineering

May 2026×9 min read

Build a simple customers table for an online store, insert five sample customers, and practice update, delete, null, and alter operations.

5. Customers Table Assignment

Driptanil DattaSoftware Developer

Customers Table Assignment

The assignment is to model basic customer information for an online store.

Requirement

Create a customers table with these fields:

Column	Type
`customer_id`	Integer customer identifier
`first_name`	Up to 50 characters, required
`last_name`	Up to 50 characters, required
`email`	Up to 100 characters
`phone_number`	Up to 15 characters
`address`	Up to 255 characters
`city`	Up to 100 characters
`postal_code`	Up to 10 characters
`country`	Up to 50 characters

The original prompt asks for an automatically incrementing customer_id but also says not to include primary keys. In MySQL, an AUTO_INCREMENT column must be indexed. To keep this assignment constraint-free, use a plain integer ID and insert the values explicitly.

Create the table

CREATE TABLE customers (
  customer_id INT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100),
  phone_number VARCHAR(15),
  address VARCHAR(255),
  city VARCHAR(100),
  postal_code VARCHAR(10),
  country VARCHAR(50)
);

For a real ecommerce database, this column would normally be defined as:

customer_id INT AUTO_INCREMENT PRIMARY KEY

That version is better for production, but it intentionally adds a primary key constraint.

Insert five customers

INSERT INTO customers (
  customer_id,
  first_name,
  last_name,
  email,
  phone_number,
  address,
  city,
  postal_code,
  country
) VALUES
  (1, "John", "Doe", "john.doe@example.com", "123-456-7890", "123 Elm Street", "Springfield", "62704", "USA"),
  (2, "Jane", "Smith", "jane.smith@example.com", "098-765-4321", "456 Oak Avenue", "Metropolis", "62960", "USA"),
  (3, "Emily", "Davis", "emily.davis@example.com", "555-123-4567", "789 Pine Road", "Gotham", "10001", "USA"),
  (4, "Michael", "Brown", "michael.brown@example.com", "444-555-6666", "321 Maple Lane", "Star City", "60614", "USA"),
  (5, "Sarah", "Wilson", "sarah.wilson@example.com", "777-888-9999", "654 Cedar Boulevard", "Central City", "94105", "USA");

Read the inserted rows:

SELECT *
FROM customers;

Practice updates

Update a single customer's phone number:

UPDATE customers
SET phone_number = "111-222-3333"
WHERE customer_id = 1;

Update every customer in the USA to use a normalized country value:

UPDATE customers
SET country = "United States"
WHERE country = "USA";

Practice NULL values

Insert a row with missing optional fields:

INSERT INTO customers (
  customer_id,
  first_name,
  last_name,
  email,
  phone_number,
  address,
  city,
  postal_code,
  country
) VALUES
  (6, "Ava", "Patel", NULL, NULL, "88 Lake Road", "Austin", "73301", "United States");

Find customers with missing contact details:

SELECT *
FROM customers
WHERE email IS NULL
   OR phone_number IS NULL;

Practice DELETE

Preview before deleting:

SELECT *
FROM customers
WHERE customer_id = 6;

Delete the practice row:

DELETE FROM customers
WHERE customer_id = 6;

Practice ALTER TABLE

Add a loyalty tier:

ALTER TABLE customers
ADD COLUMN loyalty_tier VARCHAR(25);

Increase the phone number size:

ALTER TABLE customers
MODIFY COLUMN phone_number VARCHAR(20);

Drop a column you no longer need:

ALTER TABLE customers
DROP COLUMN loyalty_tier;

This assignment combines the core beginner skills: schema design, sample data, row changes, missing values, and schema changes.

4. DML Practice with customer_info 6. MySQL Constraints