Question

Figure 3-4 Sample Schema co CO PRODUCTS CUSTOMERS product_id customer_id product_name email_address unit_price full_name product_details product_image image_mime_type image_charset image_filename image_last_updated ORDER_ITEMS order_id line_item_id product_id unit_price quantity ORDERS order_id order_datetime customer_id order_status store_id STORES store_id store_name web_address physical address latitude longitude logo logo_mime_type logo_charset logo_filename logo_last_updated | 3. Write one SELECT statement to display ten most popular products (only ten rows) in 2018. The most popular means the highest quantity ordered on COMPLETED orders. Display the product id, product name, and the total quantity ordered (from the highest to the lowest). The metadata (column names) should be as follows: Product ID Product Name QTY

Answer 1

To my opinion query should be like:

"SELECT PRODUCTS.product_id,PRODUCTS.product_name,ORDER_ITEMS.quantity FROM PRODUCTS,ORDER_ITEMS,ORDERS WHERE ORDERS.order_datetime BETWEEN '2018-01-01' AND '2018-12-31' AND ORDERS.order_status='COMPLETED' ORDER BY ORDER_ITEMS.quantity DESC LIMIT 10 " ;

explanation:

here,datetime clause will select date from 1 jan 2018 till 31 dec 2108 .and order by will display the most value in desc order and limit will used to display only 10 limited rows metadata