Can you provide an example of a SQL database that includes tables, columns, relationships, and data examples for practice purposes?

 Certainly! Below is an example of a SQL database schema that includes tables, columns, relationships, and sample data for practice purposes. We'll build a small e-commerce database with users, products, orders, and order details.

Database: ecommerce_db

1. Users Table

This table stores information about the users who have accounts on the platform.

CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    phone_number VARCHAR(15),
    registration_date DATE
);

Sample Data:

INSERT INTO Users (first_name, last_name, email, phone_number, registration_date) VALUES
('John', 'Doe', 'john.doe@example.com', '123-456-7890', '2023-01-15'),
('Jane', 'Smith', 'jane.smith@example.com', '987-654-3210', '2023-02-20'),
('Alice', 'Johnson', 'alice.johnson@example.com', '555-555-5555', '2023-03-10');

2. Products Table

This table stores information about the products available for purchase.

CREATE TABLE Products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    description TEXT,
    price DECIMAL(10, 2),
    stock_quantity INT
);

Sample Data:

INSERT INTO Products (product_name, description, price, stock_quantity) VALUES
('Laptop', 'A high-performance laptop', 999.99, 50),
('Smartphone', 'Latest model smartphone', 599.99, 100),
('Headphones', 'Noise-canceling headphones', 199.99, 200);

3. Orders Table

This table records each order placed by users.

CREATE TABLE Orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

Sample Data:

INSERT INTO Orders (user_id, order_date, total_amount) VALUES
(1, '2023-12-01', 1599.98),
(2, '2023-12-05', 599.99),
(3, '2023-12-10', 399.98);

4. OrderDetails Table

This table stores the details of the products within each order, linking products to orders.

CREATE TABLE OrderDetails (
    order_detail_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

Sample Data:

INSERT INTO OrderDetails (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 999.99), -- Order 1: 1 Laptop
(1, 2, 1, 599.99), -- Order 1: 1 Smartphone
(2, 2, 1, 599.99), -- Order 2: 1 Smartphone
(3, 3, 2, 199.99); -- Order 3: 2 Headphones

Relationships

• Users ↔ Orders: One-to-many relationship. A user can place many orders, but each order belongs to one user.
• Orders ↔ OrderDetails: One-to-many relationship. An order can have multiple products, but each record in OrderDetails refers to a single order.
• Products ↔ OrderDetails: One-to-many relationship. A product can appear in many orders, but each record in OrderDetails refers to a specific product within a specific order.

Sample Queries

1. Get all orders for a specific user (e.g., user with user_id = 1):

   SELECT o.order_id, o.order_date, o.total_amount
   FROM Orders o
   WHERE o.user_id = 1;
   

2. Get detailed information for a specific order (e.g., order with order_id = 1):

   SELECT p.product_name, od.quantity, od.price, (od.quantity * od.price) AS total
   FROM OrderDetails od
   JOIN Products p ON od.product_id = p.product_id
   WHERE od.order_id = 1;
   

3. Get the total amount spent by each user:

   SELECT u.first_name, u.last_name, SUM(o.total_amount) AS total_spent
   FROM Users u
   JOIN Orders o ON u.user_id = o.user_id
   GROUP BY u.user_id;
   

4. Check product stock availability:

   SELECT product_name, stock_quantity
   FROM Products
   WHERE stock_quantity > 0;
   

This database schema provides a basic structure to practice with relationships, joins, and queries in SQL. You can extend it by adding additional features like shipping information, payments, or product categories.