Lesson 1 of 0
In Progress

Day 3 Tables

Kedeisha November 23, 2023

Customers Table

  • Table Structure:
    • customerID (INT): A unique identifier for each customer.
    • name (VARCHAR): The name of the customer.
    • email (VARCHAR): The email address of the customer.
    • joinDate (DATE): The date when the customer joined.
CREATE TABLE customers (
  customerID INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  joinDate DATE
)

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

Orders Table

  • Table Structure:
    • orderID (INT): A unique identifier for each order.
    • customerID (INT): The ID of the customer who made the order (foreign key).
    • orderDate (DATE): The date when the order was placed.
    • totalAmount (FLOAT): The total monetary value of the order.
CREATE TABLE orders (
  orderID INT AUTO_INCREMENT PRIMARY KEY,
  customerID INT,
  orderDate DATE,
  totalAmount FLOAT,
  FOREIGN KEY (customerID) REFERENCES customers(customerID)
)

INSERT INTO orders (customerID, orderDate, totalAmount) VALUES
(1, '2023-03-15', 300.00),
(2, '2023-03-16', 450.00),
(3, '2023-03-17', 150.00);

Order Details Table

  • Table Structure:
    • orderDetailID (INT): A unique identifier for each order detail entry.
    • orderID (INT): The ID of the order (foreign key).
    • productID (INT): The ID of the product ordered.
    • quantity (INT): The number of units ordered.
    • price (FLOAT): The price per unit of the product.
CREATE TABLE order_details (
  orderDetailID INT AUTO_INCREMENT PRIMARY KEY,
  orderID INT,
  productID INT,
  quantity INT,
  price FLOAT,
  FOREIGN KEY (orderID) REFERENCES orders(orderID)
)

INSERT INTO order_details (orderID, productID, quantity, price) VALUES
(1, 101, 2, 50.00),
(1, 102, 1, 200.00),
(2, 103, 3, 150.00),
(3, 104, 1, 150.00);