MySQL : Retrieve order

Database

UNSOLVED

Environment Specifications & Instructions

Type of Database

MySQL

Database Name to be used

OrderDb

Problem Statement

Write a MySQL query to retrieve a list of orders with the following information for each order:

order$\_$id

customer$\_$id

order$\_$date

total$\_$amount $($sum of the total cost of all items in the order$)$

status $($determine if the order is "Complete" or "Pending"$)$

An order is considered "Complete" if all items in the order are available$($i$.$e$.,$ the product$\_$id in order$\_$items exists in products$)$ and sufficient quantities are in stock. Otherwise, the order is "Pending". Retrieve orders where the status is "Complete," and sort the result by order$\_$id in ascending order.

The string value is case$-$sensitive here.

Column Name: order$\_$id$,$ customer$\_$id$,$ order$\_$date, total$\_$amount, status

Table Description:

customers

MySQL : Retrieve order

Database

UNSOLVED

Environment Specifications & Instructions

Type of Database

MySQL

Database Name to be used

OrderDb

Problem Statement

Write a MySQL query to retrieve a list of orders with the following information for each order:

order$\_$id

customer$\_$id

order$\_$date

total$\_$amount $($sum of the total cost of all items in the order$)$

status $($determine if the order is "Complete" or "Pending"$)$

An order is considered "Complete" if all items in the order are available$($i$.$e$.,$ the product$\_$id in order$\_$items exists in products$)$ and sufficient quantities are in stock. Otherwise, the order is "Pending". Retrieve orders where the status is "Complete," and sort the result by order$\_$id in ascending order.

The string value is case$-$sensitive here.

Column Name: order$\_$id$,$ customer$\_$id$,$ order$\_$date, total$\_$amount, status

Table Description:

customers