5. Customers Table Assignment

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

sql

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:

sql

customer_id INT AUTO_INCREMENT PRIMARY KEY

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

Insert five customers

sql

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:

sql

SELECT *
FROM customers;

Practice updates

Update a single customer's phone number:

sql

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

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

sql

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

Practice NULL values

Insert a row with missing optional fields:

sql

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:

sql

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

Practice DELETE

Preview before deleting:

sql

SELECT *
FROM customers
WHERE customer_id = 6;

Delete the practice row:

sql

DELETE FROM customers
WHERE customer_id = 6;

Practice ALTER TABLE

Add a loyalty tier:

sql

ALTER TABLE customers
ADD COLUMN loyalty_tier VARCHAR(25);

Increase the phone number size:

sql

ALTER TABLE customers
MODIFY COLUMN phone_number VARCHAR(20);

Drop a column you no longer need:

sql

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.