Introduction to the Join

1. List all customers by ID and name along with all the ORDID’s for the orders that those customers have made.
Answer:
SELECT CUSTOMER.CUSTOMER_ID, CUSTOMER.NAME, ITEM.ORDER_ID
FROM CUSTOMER INNER JOIN ITEM ON CUSTOMER.CUSTOMER_ID = ITEM.ORDER_ID 2. For each product, list the PRODID, description, standard price and start date.
Answer:

SELECT PRODUCT.PRODUCT_ID, PRODUCT.DESCRIPTION, PRICE.LIST_PRICE, PRICE.START_DATE
FROM PRODUCT INNER JOIN PRICE ON PRODUCT.PRODUCT_ID = PRICE.PRODUCT_ID;

3. For each product, list the PRODID, description, standard price and start date for current prices only. Hint: the end date will be NULL.
Answer:

SELECT PRODUCT.PRODUCT_ID, PRODUCT.DESCRIPTION, PRICE.LIST_PRICE, PRICE.START_DATE, PRICE.END_DATE
FROM PRODUCT INNER JOIN PRICE ON PRODUCT.PRODUCT_ID = PRICE.PRODUCT_ID
WHERE (((PRICE.END_DATE)=Null));

4. For each item sold, list the ORDID, ITEMID, PRODID and description.
Answer:

SELECT ITEM.ORDER_ID, ITEM.ITEM_ID, PRODUCT.PRODUCT_ID, PRODUCT.DESCRIPTION
FROM PRODUCT INNER JOIN ITEM ON PRODUCT.PRODUCT_ID = ITEM.PRODUCT_ID;

5. The Marketing department wants to know which customers are buying which products. List each customer (CUSTID and name) and the products purchased by that customer (PRODID and description). Products purchased by a given customer should be listed only once. SELECT CUSTOMER.CUSTOMER_ID, CUSTOMER.NAME, PRODUCT.PRODUCT_ID, PRODUCT.DESCRIPTION FROM CUSTOMER INNER JOIN PRODUCT ON CUSTOMER.CUSTOMER_ID = PRODUCT.PRODUCT_ID;

6. List the name of those customers who paid no more than 10% over the minimum price (at the time of purchase) of a product. Include in the listing, the product id, description, actual price, minimum price and order date.