Lesson 1 of 0
In Progress
Day 3 Tables
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);