Question

MYSQL DATABASE:

1) Use an OUTER JOIN to join the Products and OrderItems tables, returning a sorted list of product names (prod_name) and the order numbers (order_num) associated with each.

2) Modify number 1 statement so that it returns a total of number of orders for each item ( as opposed to the order numbers).

products table:

Columns:

prod_id	char(10) PK
vend_id	char(10)
prod_name	char(255)
prod_price	decimal(8,2)
prod_desc	text

​orderItems table:

Columns:

order_num	int(11) PK
order_item	int(11) PK
prod_id	char(10)
quantity	int(11)
item_price	decimal(8,2)

Answer 1

select prod_name AS A.product names, B.order_num AS order numbers from Products AS A OUTER JOIN OrderItems AS B on A.prod_id=B.prod_id ORDER BY A.prod_name

select prod_name AS A.product names, Count(order_num) AS order Count from Products AS A OUTER JOIN OrderItems AS B on A.prod_id=B.prod_id GROUP BY A.prod_name