Question

Step 1: Write SQL to List the information delivered (PO number, order date, vendor ID & name, product ID & name, Product quantity) of transaction records, sorted by order date?

Step 2: Write SQL For orders that were placed between 9/1/2016 and 9/30/2016, list (product IDs, product names, order dates, PO numbers) sort by order date

Step 3: Write SQL and find

In the month of August, which vendor received the smallest order for a product in terms of dollars?

Hint the date returned should have these headings:

vendor_id, vendor_name, order_date, po_number, line_number, Product_ID, total_cost

Answer 1

ANSWERS -

(NOTE : Product name, Product quantity column name is not specified so taking a random name as Product_Name, Product_Quantity . If you have the details pls put in a comment, I will update the answer asap)

STEP 1 : To list all the details of the delivered product from the transaction record in a sorted table with respect to the order date. We use here ORDER BY clause to retrieve a sorted list.

Table Name - transaction

Column Names - po_number, order_date, vendor_id, vendor_name, Product_ID, Product_Name, Product_Quantity

The query to form is,

SELECT po_number, order_date, vendor_id, vendor_name, Product_ID, Product_Name, Product_Quantity FROM transaction ORDER BY order_date ;

The resultant output will be,

po_number

order_date

vendor_id

vendor_name

Product_ID

Product_Name

Product_Quantity

The output will list the details in sorted order.

STEP 2 : To list the orders that were placed between certain dates , we use WHERE condition along with BETWEEN operator and ORDER BY to sort.

Table name - orders

Column name - Product_ID, Product_Name, order_date, po_number

The query to form is,

SELECT Product_ID, Product_Name, order_date, po_number FROM orders WHERE order_date BETWEEN '2016/9/1' AND '2016/9/30' ORDER BY order_date ;

This will give the sorted output of order details which were placed between the dates.

Product_ID

Product_Name

order_date

po_number

Step 3 : To list the details of vendor who has smallest order for product in the month of august. We use here MIN function to retrieve the smallest order and WHERE condition.

Table name - product

Column name - total_cost

The query to form is,

SELECT vendor_id, vendor_name, total_cost FROM product WHERE total_cost = ( SELECT MIN(total_cost) FROM product WHERE MONTH(order_date)= 8) ;

This will give the output,

vendor_id

vendor_name

total_cost

This will list the output of vendor who has smallest order done in month of august.

=================================END================================

Please comment if u need any other info and DO LEAVE A LIKE, it would mean a lot. Thanks :)