Question

1. Write a SELECT statement that returns these columns using a JOIN of three tables:
   1. order_date from the orders table
   2. shipped_date from the orders table
   3. order_qty from the order_details table
   4. title from the items table
   5. artist from the items table
   6. unit_price from the items table
   7. Result table should be in order of order_date in ascending order

Answer 1

According to the question the order and items table has m:n cardinality.

Let the primary key of Order table (Order_ID) be treated as the foreign key in Order_item table, and similarly the primary key of Item table (Item_ID) be treated as the foreign key in Order_item table.

The SQL query is as follows:-

SELECT order_date, shipped_date, order_qty, title, artist, unit_price FROM orders o INNER JOIN order_details od on o.Order_ID = od.Order_ID INNER JOIN items i on i.Item_ID = od.Item_ID order by order_date;

The above query will select the column order_date, shipped date from order table, order_qty from order_details and title, artist, and unit_price from items table respectively then first merge the table orders and order_details and filter the records upon condition orders.Order_ID = order_details.Order_ID

Then again merge the intermediate result and the items table and filter the records upon condition items.Item_ID = order_details.Item_ID, at last sort the records in ascending order in terms of order_date.