I don't know How to do this query , I only could find the volume of carton 10

Product table

This is a sample product table , I have given you the relevant columns.This actual table has 60 rows of information like this.

Order_items table

This is a sample order_items table where product quantity is no of items sold for that particular order_id.

Carton table

The carton_id 10 has the actual values of length, width and height.

The query is: Write a query to display order_id and volume of the biggest order (in terms of volume) that can fit in carton id 10 . Expected 1 row in final output.

I am not able to do more than this, it's too confusing. Please help

select (c.len*c.width*c.height) as Max_volume 
from carton c 
where c.carton_id = 10;

If there always is a single product in each order, as shown in your sample data, then one option is to join and filter.

select o.order_id, p.len * p.width * p.height volume
from order_items o
inner join products p on p.product_id = o.product_id
inner join cartons c on c.len >= p.len and c.width >= p.width and c.height >= p.height
where c.carton_id = 10
order by volume desc limit 1

The query filters orders on products that fit in a carton 10 (meaning that the three dimensions of the product are smaller than those of the carton), then sorts by descending volume and retains the first row only.