Day 6 Tables

Kedeisha November 23, 2023

Customers Table

Table Structure:
- customerID (INT): Unique identifier for each customer.
- name (VARCHAR): Name of the customer.
- joinDate (DATE): Date when the customer joined or made the first purchase.
- email (VARCHAR): Email address of the customer.

CREATE TABLE customers (
  customerID INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  joinDate DATE,
  email VARCHAR(100)
)

INSERT INTO customers (customerID, name, joinDate, email) VALUES
(1, 'John Doe', '2020-01-15', 'johndoe@example.com'),
(2, 'Jane Smith', '2020-03-22', 'janesmith@example.com'),
(3, 'Emily Johnson', '2020-05-30', 'emilyj@example.com');

Sales Table

Table Structure:
- saleID (INT): Unique identifier for each sale.
- customerID (INT): The ID of the customer who made the purchase.
- saleDate (DATE): Date of the sale.
- amount (FLOAT): Total amount of the sale.

CREATE TABLE sales (
  saleID INT AUTO_INCREMENT PRIMARY KEY,
  customerID INT,
  saleDate DATE,
  amount FLOAT,
  FOREIGN KEY (customerID) REFERENCES customers(customerID)
)

INSERT INTO sales (saleID, customerID, saleDate, amount) VALUES
(1, 1, '2021-03-15', 300.00),
(2, 2, '2021-03-16', 450.00),
(3, 3, '2021-03-17', 150.00);

Marketing Campaigns Table

Table Structure:
- campaignID (INT): Unique identifier for each marketing campaign.
- campaignName (VARCHAR): Name of the campaign.
- startDate (DATE): Start date of the campaign.
- endDate (DATE): End date of the campaign.

CREATE TABLE marketing_campaigns (
  campaignID INT AUTO_INCREMENT PRIMARY KEY,
  campaignName VARCHAR(100),
  startDate DATE,
  endDate DATE
)

INSERT INTO marketing_campaigns (campaignID, campaignName, startDate, endDate) VALUES
(1, 'Spring Sale', '2021-03-01', '2021-03-31'),
(2, 'Summer Bonanza', '2021-06-01', '2021-06-30');

Customer Interactions Table

Table Structure:
- interactionID (INT): Unique identifier for each interaction.
- customerID (INT): The ID of the customer involved in the interaction.
- interactionDate (DATE): Date of the interaction.
- campaignID (INT): ID of the campaign during which the interaction occurred.
- interactionType (VARCHAR): Type of interaction (e.g., ‘Email’, ‘Call’, ‘Visit’).

CREATE TABLE customer_interactions (
  interactionID INT AUTO_INCREMENT PRIMARY KEY,
  customerID INT,
  interactionDate DATE,
  campaignID INT,
  interactionType VARCHAR(50),
  FOREIGN KEY (customerID) REFERENCES customers(customerID),
  FOREIGN KEY (campaignID) REFERENCES marketing_campaigns(campaignID)
)

INSERT INTO customer_interactions (interactionID, customerID, interactionDate, campaignID, interactionType) VALUES
(1, 1, '2021-03-05', 1, 'Email'),
(2, 2, '2021-03-10', 1, 'Email'),
(3, 3, '2021-06-15', 2, 'Call'),
(4, 1, '2021-06-20', 2, 'Visit');