FREE AI-Enhanced Online MySQL Compiler - For learning & practice

Untitled*

You must execute the code again to save those values in the database.

-- Create the Products table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(100),
    Category VARCHAR(50),
    Price DECIMAL(10, 2),
    StockQuantity INT
);

-- Create the Customers table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    Phone VARCHAR(20)
);

-- Create the Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Create the OrderDetails table
CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY AUTO_INCREMENT,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    Price DECIMAL(10, 2),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Insert data into the Products table
INSERT INTO Products (ProductName, Category, Price, StockQuantity)
VALUES 
('Laptop', 'Electronics', 799.99, 50),
('Smartphone', 'Electronics', 599.99, 100),
('Desk Chair', 'Furniture', 89.99, 30),
('Electric Kettle', 'Home Appliances', 29.99, 75);

-- Insert data into the Customers table
INSERT INTO Customers (FirstName, LastName, Email, Phone)
VALUES 
('John', 'Doe', 'john.doe@example.com', '123-456-7890'),
('Jane', 'Smith', 'jane.smith@example.com', '234-567-8901'),
('Alice', 'Johnson', 'alice.johnson@example.com', '345-678-9012');

-- Insert data into the Orders table
INSERT INTO Orders (CustomerID, OrderDate)
VALUES 
(1, '2024-06-01'),
(2, '2024-06-02'),
(1, '2024-06-03');

-- Insert data into the OrderDetails table
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price)
VALUES 
(1, 1, 1, 799.99),
(1, 4, 2, 29.99),
(2, 2, 1, 599.99),
(3, 3, 1, 89.99);

SELECT * FROM Products;

SELECT* FROM Orders;

SELECT OrderDetails.OrderID, Products.ProductName, OrderDetails.Quantity, OrderDetails.Price
FROM OrderDetails
JOIN Products ON OrderDetails.ProductID = Products.ProductID;

ProductID	ProductName	Category	Price	StockQuantity
1	Laptop	Electronics	799.99	50
2	Smartphone	Electronics	599.99	100
3	Desk Chair	Furniture	89.99	30
4	Electric Kettle	Home Appliances	29.99	75

OrderID	CustomerID	OrderDate
1	1	2024-06-01
2	2	2024-06-02
3	1	2024-06-03

OrderID	ProductName	Quantity	Price
1	Laptop	1	799.99
1	Electric Kettle	2	29.99
2	Smartphone	1	599.99
3	Desk Chair	1	89.99