SQL Projects: Online Store Database Management System

Online Store Database Management System:

An Online Store Database is designed to store and manage product information, customer details, orders, inventory, and payments. This system ensures efficient management of e-commerce operations, enabling businesses to track product availability, manage customer orders, process payments, and generate reports.

Creating the Database in MySQL or PostgreSQL:

Create the Database:

-- Create the database for the Online Store Database Management System
CREATE DATABASE OnlineStoreDB;
USE OnlineStoreDB;

Create the Tables:

Products Table:

The Products table stores information about the products available in the online store, including the product ID, name, description, price, and category, helping manage the product catalog effectively..

Structure:

Code:

CREATE TABLE Products (
    product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,   -- Product ID is the primary key
    name VARCHAR(25) NOT NULL,                           -- Product name
    description TEXT,                                     -- Product description
    price DECIMAL(10, 2) NOT NULL,                        -- Product price
    category VARCHAR(50)                                 -- Product category
);

Customers Table:

The Customers table stores customer details such as customer ID, name, email, phone number, and address, providing essential information for managing customer interactions and transactions within the online store.

Structure:

Code:

CREATE TABLE Customers (
    customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- Customer ID
    name VARCHAR(25) NOT NULL,                           -- Customer name
    email VARCHAR(25) UNIQUE NOT NULL,                   -- Unique email
    phone_number VARCHAR(15),                             -- Phone number
    address VARCHAR(55)                                  -- Address
);

Orders Table:

The Orders table records customer orders, tracking order ID, customer ID, order date, and status (such as 'Pending', 'Completed', or 'Cancelled'). It links orders to customers and helps manage the order lifecycle within the system.

Structure:

Code:

CREATE TABLE Orders (
    order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,     -- Order ID
    customer_id INT,                                      -- Foreign key to Customers table
    order_date DATE NOT NULL,                             -- Order date
    status ENUM('Pending', 'Completed', 'Cancelled') DEFAULT 'Pending', -- Order status
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) -- Relating orders to customers
); 

Payments Table:

The Payments table stores payment records associated with customer orders. It tracks the payment ID, order ID, payment date, amount, and payment status (e.g., 'Paid' or 'Unpaid'), linking payments to their respective orders to manage transaction processing.

Structure:

Code:

CREATE TABLE Payments (
    payment_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,   -- Payment ID
    order_id INT,                                         -- Foreign key to Orders table
    payment_date DATE NOT NULL,                           -- Payment date
    amount DECIMAL(10, 2) NOT NULL,                       -- Payment amount
    payment_status ENUM('Paid', 'Unpaid') DEFAULT 'Unpaid', -- Payment status
	FOREIGN KEY (order_id) REFERENCES Orders(order_id)    -- Relating payments to orders
);

Inventory Table:

The Inventory table tracks the stock levels of products in the system. It includes the inventory ID, product ID (linked to the Products table), the quantity of stock available, and the last updated date, helping manage and monitor product availability.

Structure:

Code:

CREATE TABLE Inventory (
    inventory_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- Inventory ID
    product_id INT,                                       -- Foreign key to Products table
    stock_quantity INT DEFAULT 0,                         -- Quantity in stock
    last_updated DATE DEFAULT (CURRENT_DATE),               -- Last updated date
    FOREIGN KEY (product_id) REFERENCES Products(product_id) -- Relating inventory to products
);

Inserting Data:

Add some sample data to the tables.

Inserting Data into Products Table:

-- Insert Products
INSERT INTO Products (name, description, price, category) VALUES 
('Laptop', 'A high-performance laptop', 1200.00, 'Electronics'),
('Headphones', 'Wireless noise-cancelling headphones', 200.00, 'Electronics'),
('Book', 'Inspirational novel', 15.00, 'Books'); 

Inserting Data into Customers Table:

-- Insert Customers
INSERT INTO Customers (name, email, phone_number, address) VALUES 
('John Doe', '[email protected]', '1234567890', '123 Main St'),
('Jane Smith', '[email protected]', '0987654321', '456 Elm St'); Inserting 

Inserting Data into Orders Table:

-- Insert Orders
INSERT INTO Orders (customer_id, order_date, status) VALUES 
(1, '2024-10-21', 'Pending'),
(2, '2024-10-20', 'Completed');

Inserting Data into Payments Table:

-- Insert Payments
INSERT INTO Payments (order_id, payment_date, amount, payment_status) VALUES 
(1, '2024-10-21', 1200.00, 'Paid'),
(2, '2024-10-20', 200.00, 'Paid');

Inserting Data into Inventory Table:

-- Insert Inventory
INSERT INTO Inventory (product_id, stock_quantity, last_updated) VALUES 
(1, 50, '2024-10-20'),
(2, 100, '2024-10-19'),
(3, 200, '2024-10-18');

Basic Functionalities of Employee Payroll System:

• Add New Products:
• Allow users to add new products to the system.
• Update Product Information:
• Update product details, including name, price, and category.
• Delete Products:
• Remove products from the system when no longer available.
• Track Customer Orders:
• View the status of customer orders.
• Process Payments:
• Update payment status when customers complete a transaction.
• Manage Inventory:
• Adjust stock levels based on new shipments and sales.

Writing Queries for Functionality:

Query-1: View All Products

SELECT * FROM Products;  -- Retrieves all products listed in the store's database

Explanation:

This query selects all columns from the `Products` table, displaying the complete list of products available in the store's database, including their details such as name, price, and description.

Output:

product_id	name	description	price	category
1	Laptop	A high-performance laptop	1200.00	Electronics
2	Headphones	Wireless noise-cancelling headphones	200.00	Electronics
3	Book	Inspirational novel	15.00	Books

Query-2: Check Product Availability

SELECT name, stock_quantity                 -- Selects the product name and stock quantity
FROM Products p                             -- Retrieves product data from the 'Products' table
JOIN Inventory i ON p.product_id = i.product_id   -- Joins the 'Inventory' table to get stock details
WHERE stock_quantity > 0;                   -- Filters only products with stock available

Explanation:

This query retrieves the names of products and their corresponding stock quantities by joining the Products table with the Inventory table based on the product_id. It then filters the results to show only products that have stock available, meaning the stock_quantity is greater than zero.

Output:

name	stock_quantity
Laptop	50
Headphones	100
Book	200

Query-3: Get Customer Details

SELECT * FROM Customers WHERE customer_id = 1;   -- Retrieves customer details for a specific customer

Explanation:

This query retrieves all details (like name, address, etc.) of the customer with the `customer_id` of 1 from the `Customers` table.

Output:

customer_id	name	email	phone_number	address
1	John Doe	[email protected]	1234567890	123 Main St

Query-4: Orders with Payment Status

SELECT o.order_id, o.order_date, p.payment_status  -- Selects order ID, order date, and payment status
FROM Orders o                                      -- Retrieves order details from the 'Orders' table
JOIN Payments p ON o.order_id = p.order_id;        -- Joins the 'Payments' table to get the payment status  

Explanation:

This query retrieves the order ID, order date, and corresponding payment status for each order. It selects these details by joining the Orders table with the Payments table using the common order_id column. The result set will show each order's ID, the date it was placed, and its current payment status (such as "Paid" or "Unpaid").

Output:

order_id	order_date	payment_status
1	2024-10-21	Paid
2	2024-10-20	Paid

Query-5: Pending Payments

	SELECT * FROM Payments WHERE payment_status = 'Unpaid';   -- Retrieves all unpaid payments

Explanation:

This query lists all payments with a status of 'Unpaid' from the `Payments` table, helping identify pending payments.

Output:

Output not generated for insufficient data

Query-6: Total Sales per Product

SELECT p.name, SUM(pay.amount) AS total_sales    -- Selects product name and calculates total sales
FROM Products p                                  -- Retrieves product data from the 'Products' table
JOIN Orders o ON p.product_id = o.order_id       -- Joins the 'Orders' table to link product and orders
JOIN Payments pay ON o.order_id = pay.order_id   -- Joins the 'Payments' table to get payment amounts
GROUP BY p.product_id;                           -- Groups by product to sum total sales per product

Explanation:

This query calculates the total sales for each product. It selects the product's name from the Products table and sums the payment amounts from the Payments table for orders associated with each product. The JOIN operations connect the Products table with Orders (to link products to orders) and the Payments table (to get the corresponding payment amounts). The results are grouped by product_id, ensuring that the total sales are calculated for each individual product.

Output:

name	total_sales
Laptop	1200.00
Headphones	200.00

Query-7: Orders Completed in Last Month

SELECT * FROM Orders                              -- Selects all columns from the 'Orders' table
WHERE status = 'Completed'                        -- Filters only orders that are completed
AND order_date > CURDATE() - INTERVAL 1 MONTH;    -- Limits results to orders completed in the last month

Explanation:

This query retrieves all orders that were marked as 'Completed' within the last month. It selects all fields from the Orders table and filters the results to include only orders with a status of 'Completed'. The condition order_date > CURDATE() - INTERVAL 1 MONTH ensures that only orders completed in the past month (relative to the current date) are returned, which helps track recent activity.

Output:

order_id	customer_id	order_date	status
2	2	2024-10-20	Completed

Query-8: Inventory Restocking

SELECT name, stock_quantity                  -- Selects product name and stock quantity
FROM Products p                              -- Retrieves product data from the 'Products' table
JOIN Inventory i ON p.product_id = i.product_id  -- Joins the 'Inventory' table for stock information
WHERE stock_quantity < 20;                   -- Filters products with low stock that need restocking

Explanation:

This query identifies products that need restocking by selecting the name and stock_quantity of products from the Products table. It joins the Inventory table to retrieve stock details using the product's ID. The WHERE clause filters products with a stock_quantity of less than 20, signaling that these items are running low and may require replenishment. This helps in managing inventory levels efficiently.

Output:

Output not generated for insufficient data

Query-9: Customers with Multiple Orders

SELECT c.name, COUNT(o.order_id) AS order_count    -- Selects customer name and counts their orders
FROM Customers c                                   -- Retrieves customer data from the 'Customers' table
JOIN Orders o ON c.customer_id = o.customer_id     -- Joins the 'Orders' table to get order data
GROUP BY c.customer_id                             -- Groups by customer to count their orders
HAVING order_count > 1;                            -- Filters customers who placed more than one order

Explanation:

This query identifies customers who have placed more than one order. It selects each customer's name and counts the total number of orders (order_count) they have placed by joining the Customers and Orders tables. The results are grouped by customer_id to tally the orders for each customer, and the HAVING clause ensures only customers with more than one order are included in the output. This query is useful for recognizing repeat customers.

Output:

Output not generated for insufficient data

Query-10: Average Order Value

SELECT AVG(amount) AS avg_order_value  -- Calculates the average amount from payments
FROM Payments;                         -- Retrieves payment data from the 'Payments' table

Explanation:

This query calculates the average value of all payments made in the system by using the AVG() function on the amount column from the Payments table. The result is labeled as avg_order_value. It helps assess the average revenue generated per order, which can be valuable for analyzing business performance and customer spending habits.

Output:

avg_order_value
700.000000

Query-11: Highest-Paying Customer

SELECT c.name, SUM(p.amount) AS total_spent    -- Selects customer name and calculates total amount spent
FROM Customers c                               -- Retrieves customer data from the 'Customers' table
JOIN Orders o ON c.customer_id = o.customer_id -- Joins 'Orders' table to get customer orders
JOIN Payments p ON o.order_id = p.order_id     -- Joins 'Payments' table to get payment amounts
GROUP BY c.customer_id                         -- Groups by customer ID to sum total spent
ORDER BY total_spent DESC                      -- Sorts by total spent in descending order
LIMIT 1;                                       -- Limits results to the top customer

Explanation:

This query identifies the customer who has spent the most by calculating the total amount they paid across all orders. It selects each customer's name and the sum of their payments (SUM(p.amount)), grouped by customer_id to aggregate the total spent per customer. The results are then sorted in descending order (ORDER BY total_spent DESC), and the LIMIT 1 ensures that only the highest-paying customer is returned. This is useful for identifying VIP or high-value customers.

Output:

name	total_spent
John Doe	1200.00

Query-12: Total Sales per Month

SELECT MONTHNAME(payment_date) AS month, SUM(amount) AS total_sales   -- Selects month name and calculates total sales for each month
FROM Payments                                                      -- Retrieves payment data from the 'Payments' table
GROUP BY MONTH(payment_date);                                      -- Groups by month to sum total sales per month

Explanation:

This query calculates the total sales for each month based on payment records. The MONTHNAME(payment_date) function extracts the month name from the payment_date, and SUM(amount) computes the total sales for that month. The GROUP BY MONTH(payment_date) ensures that the results are grouped by month, summarizing the sales for each distinct month. This is useful for generating monthly sales reports.

Output:

month	total_sales
October	1400.00

Query-13: Orders by Date Range

SELECT * FROM Orders  -- Selects all columns from the 'Orders' table
WHERE order_date BETWEEN '2024-10-01' AND '2024-10-31';  -- Filters orders placed between October 1 and October 31, 2024

Explanation:

This query retrieves all orders that were placed within a specified date range, from October 1, 2024, to October 31, 2024. The BETWEEN clause ensures that only those records with an order_date within this period are selected. This is useful for analyzing orders made within a specific timeframe, such as a monthly report.

Output:

order_id	customer_id	order_date	status
1	1	2024-10-21	Pending
2	2	2024-10-20	Completed

Query-14: Find All Canceled Orders

SELECT * FROM Orders WHERE status = 'Cancelled';  -- Retrieves all orders with a status of 'Cancelled'

Explanation:

This query retrieves all the orders from the Orders table that have been canceled. By using the WHERE clause to filter by the status 'Cancelled', it ensures that only the relevant records are returned. This helps in quickly identifying all orders that were not fulfilled or processed due to cancellation.

Output:

Output not generated for insufficient data

Query-15: Customers with Unpaid Orders

SELECT c.name, p.payment_status  -- Selects customer name and payment status
FROM Customers c                 -- Retrieves customer data from the 'Customers' table
JOIN Orders o ON c.customer_id = o.customer_id   -- Joins 'Orders' table to link customers and orders
JOIN Payments p ON o.order_id = p.order_id       -- Joins 'Payments' table to get payment statuses
WHERE p.payment_status = 'Unpaid';               -- Filters for orders with unpaid payments

Explanation:

This query retrieves a list of customers who have unpaid orders. It selects the customer's name and the payment status from the Customers table, Orders table, and Payments table. By joining these three tables, the query ensures that only customers with orders that have a payment status of 'Unpaid' are returned. This is useful for identifying customers who have outstanding balances in the system..

Output:

Output not generated for insufficient data

Query-16: Top 3 Most Sold Products

-- 16. Top 3 Most Sold Products

-- Select the name of the product and the total quantity sold (summed from inventory)
SELECT p.name, SUM(i.stock_quantity) AS total_sold 
-- Join the Orders table with the Products table based on the product ID
FROM Orders o 
JOIN Products p ON o.order_id = p.product_id
-- Join the Products table with the Inventory table to get stock quantities
JOIN Inventory i ON p.product_id = i.product_id 
-- Filter only completed orders
WHERE o.status = 'Completed' 
-- Group results by product ID to calculate total sales per product
GROUP BY p.product_id 
-- Order the results by the total quantity sold in descending order
ORDER BY total_sold DESC 
-- Limit the result to the top 3 products with the highest sales
LIMIT 3;

Explanation:

This query retrieves the top 3 products with the highest sales based on completed orders. It starts by selecting the product names and summing their sold quantities from the Inventory table using SUM(). The JOIN statements connect the Orders, Products, and Inventory tables, establishing the relationships needed to gather information about completed orders and the corresponding stock quantities. The WHERE clause filters only orders marked as 'Completed'. The GROUP BY clause ensures that the sales are calculated per product, and finally, the ORDER BY clause sorts the products by total sales in descending order, showing the most sold products first. The LIMIT 3 ensures that only the top three products are displayed.

Output:

name	total_sold
Headphones	100

Query-17: Customers Who Haven't Ordered in the Last 6 Months

-- Select the name of the customer and the latest order date
SELECT c.name, MAX(o.order_date) AS last_order_date 
-- Perform a LEFT JOIN between Customers and Orders, so we include customers without any orders
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
-- Group by customer ID to get each customer's latest order
GROUP BY c.customer_id
-- Use HAVING to filter customers whose latest order date was more than 6 months ago or who have no orders at all
HAVING MAX(o.order_date) < CURDATE() - INTERVAL 6 MONTH
   OR MAX(o.order_date) IS NULL;

Explanation:

This query retrieves customers who either haven't placed any orders in the last six months or have never placed an order. It starts by selecting the customer names and their most recent order date using the MAX() function on the order_date field. A LEFT JOIN ensures that customers who have not placed any orders are still included in the results (unlike an INNER JOIN, which would exclude them). The GROUP BY clause groups the results by customer ID, so we can evaluate each customer's latest order. The HAVING clause filters the results to include only customers whose last order was more than six months ago (< CURDATE() - INTERVAL 6 MONTH) or those who have no orders at all (indicated by MAX(o.order_date) IS NULL).

Output:

Output not generated for insufficient data

Query-18: Products Without Any Orders

-- Select the name of the products
SELECT p.name
-- Perform a LEFT JOIN between Products and Orders, matching product IDs with order IDs
FROM Products p
LEFT JOIN Orders o ON p.product_id = o.order_id
-- Filter the results to only show products that have no matching order (i.e., have never been ordered)
WHERE o.order_id IS NULL;

Explanation:

This query retrieves a list of products that have never been ordered. It uses a LEFT JOIN to connect the Products table with the Orders table based on matching product_id and order_id. The LEFT JOIN includes all products, even if they don't have any corresponding records in the Orders table. The WHERE o.order_id IS NULL condition filters the results to show only those products that do not appear in any orders, meaning they haven't been purchased by any customer. This is useful for identifying unpurchased or inactive products.

Output:

name
Book

Query-19: Total Revenue by Product Category

-- Select the product category and the sum of payment amounts as total revenue
SELECT p.category, SUM(pay.amount) AS total_revenue
-- Join the Products table with the Orders table using product_id and order_id
FROM Products p
JOIN Orders o ON p.product_id = o.order_id
-- Join the Payments table to retrieve payment details
JOIN Payments pay ON o.order_id = pay.order_id
-- Group the results by product category to calculate total revenue per category
GROUP BY p.category
-- Order the results by total revenue in descending order
ORDER BY total_revenue DESC;

Explanation:

This query calculates the total revenue generated from each product category by summing the payment amounts (pay.amount) associated with products in the Products table. It uses JOIN operations to connect the Products, Orders, and Payments tables based on matching product_id and order_id. The query then groups the results by product category, ensuring that the total revenue is calculated for each category. Finally, the results are ordered in descending order of total revenue, allowing the highest-earning categories to appear at the top. This is helpful for understanding which categories contribute the most to the store's revenue.

Output:

category	total_revenue
Electronics	1400.00

Query-20: Identify High-Risk Customers (with Multiple Canceled Orders)

-- Select the customer name and the count of canceled orders for each customer
SELECT c.name, COUNT(o.order_id) AS canceled_orders
-- Join the Customers table with the Orders table using customer_id
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
-- Filter to include only orders that have been canceled
WHERE o.status = 'Cancelled'
-- Group the results by customer_id to count the number of canceled orders per customer
GROUP BY c.customer_id
-- Filter the results to show only customers with more than 2 canceled orders
HAVING COUNT(o.order_id) > 2;

Explanation:

This query retrieves the names of customers who have had more than 2 canceled orders. It starts by joining the Customers and Orders tables using the customer_id. The WHERE clause filters the orders to include only those with a Cancelled status. After grouping the results by customer_id, the query counts the number of canceled orders for each customer. The HAVING clause further refines the results to include only customers with more than 2 canceled orders. This query is useful for identifying customers who frequently cancel their orders.

Output:

Output not generated for insufficient data

Query-21: Calculate Average Payment Time Per Customer

-- Select the customer name and the average time between order date and payment date
SELECT c.name, AVG(DATEDIFF(pay.payment_date, o.order_date)) AS avg_payment_time
FROM Customers c
-- Join the Customers table with the Orders table using customer_id
JOIN Orders o ON c.customer_id = o.customer_id
-- Join the Orders table with the Payments table using order_id
JOIN Payments pay ON o.order_id = pay.order_id
-- Group the results by customer_id to calculate the average payment time for each customer
GROUP BY c.customer_id;

Explanation:

This query calculates the average time it takes for customers to make payments after placing an order. The query first joins the Customers, Orders, and Payments tables to connect customer information with their corresponding orders and payments. The DATEDIFF() function computes the number of days between the order date and payment date for each order. By using AVG(), the query then calculates the average number of days it takes for each customer to complete payment. Finally, the results are grouped by customer_id to show the average payment time for each customer.

Output:

name	avg_payment_time
John Doe	0.0000
Jane Smith	0.0000

Query-22: Find Products Ordered by More Than 5 Different Customers

-- Select the product name and the count of distinct customers who ordered each product
SELECT p.name, COUNT(DISTINCT o.customer_id) AS unique_customers
FROM Products p
-- Join the Products table with the Orders table using product_id and order_id
JOIN Orders o ON p.product_id = o.order_id
-- Group the results by product_id to count unique customers for each product
GROUP BY p.product_id
-- Only include products that have more than 5 unique customers
HAVING COUNT(DISTINCT o.customer_id) > 5;

Explanation:

This query retrieves products that have been purchased by more than 5 unique customers. The query joins the Products and Orders tables, and for each product, it counts the number of distinct customers who placed orders for that product using COUNT(DISTINCT o.customer_id). The GROUP BY p.product_id groups the results by product to calculate customer counts for each product. The HAVING clause filters the results to include only products that have been purchased by more than 5 unique customers. This helps identify popular products based on the number of different customers who have bought them.

Output:

Output not generated for insufficient data

Query-23: Monthly Sales Growth Rate

-- Select the month name from the payment_date for grouping sales by month
SELECT 
    MONTHNAME(payment_date) AS month, 
    -- Calculate the total sales for each month
    SUM(amount) AS total_sales,
    -- Use the LAG() function to get the total sales from the previous month
    LAG(SUM(amount), 1) OVER (ORDER BY MONTH(payment_date)) AS previous_month_sales,
    -- Calculate the percentage growth rate compared to the previous month
    (SUM(amount) - LAG(SUM(amount), 1) OVER (ORDER BY MONTH(payment_date))) / 
    LAG(SUM(amount), 1) OVER (ORDER BY MONTH(payment_date)) * 100 AS growth_rate
-- Specify the table to calculate the sales from
FROM Payments
-- Group the results by the month of the payment date
GROUP BY MONTH(payment_date);

Explanation:

This query calculates the monthly sales growth rate by comparing the total sales of each month with the previous month. It uses the LAG() window function to access the sales of the previous month.

Output:

month	total_sales	previous_month_sales	growth_rate
October	1400.00	NULL	NULL

Query-24: Customer Order Retention (Orders Placed in Consecutive Months)

-- Select the customer's name from the Customers table
SELECT c.name, 
    -- Count the number of distinct months in which the customer has placed orders
    COUNT(DISTINCT MONTH(o.order_date)) AS active_months 
-- Join the Customers table with the Orders table based on the customer_id
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
-- Group the results by customer_id to aggregate order data for each customer
GROUP BY c.customer_id
-- Filter the results to only include customers with orders in more than one month
HAVING active_months > 1;

Explanation:

This query calculates customer retention by finding customers who placed orders in more than one distinct month. The COUNT(DISTINCT MONTH(o.order_date)) counts the number of unique months each customer placed an order.

Output:

Output not generated for insufficient data

Query-25: Detect Overdue Payments

-- Select the order ID, customer name, payment status, and days since the order was placed
SELECT o.order_id, 
       c.name, 
       pay.payment_status, 
       -- Calculate the number of days since the order was placed
       DATEDIFF(CURDATE(), o.order_date) AS days_since_order 
-- Join the Orders table with the Customers table based on customer_id
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
-- Join the Orders table with the Payments table based on order_id
JOIN Payments pay ON o.order_id = pay.order_id
-- Filter the results for unpaid payments and orders placed more than 30 days ago
WHERE pay.payment_status = 'Unpaid' 
AND DATEDIFF(CURDATE(), o.order_date) > 30;

Explanation:

This query finds orders with unpaid payments that are overdue by more than 30 days. It uses DATEDIFF() to calculate the number of days since the order date and filters for unpaid orders that are overdue.

Output:

Output not generated for insufficient data

SQL Code Editor: