Parker's INFO 4407 – Database Design & Implementation

Query List

  1. List the customer name, customer number, purchase agreement number, and purchase total for each purchase agreement. Give the purchase total a meaningful name.
  2. List the model description and manufacturer for all products whose manufacturer name starts with an "A" and has a third letter "r". (Your answer should include Aeros, Airwave, and Airush.)
  3. List all salesman information, including employee#, name, sales count, and sales total (SalesAmount) and commission earned in descending order by commission earned.
  4. List the customer name, serial number, model number, model description, and purchase agreement date for all items having a purchase agreement date of October 6, 2010.
  5. List all orders that were placed over 1 month ago and have not been received. Include order number, order date placed, order date received, supplier number, supplier name, supplier contact, and supplier phone. List by order date placed from oldest to most recent. INTERVAL is useful here.
  6. For each product category list the model number, description, and the total number of sales from that category.
    As a continuation from the previous query (6a), [using a nested query] list the model number, description, and the total number of sales for the product category that has the highest number of sales. SOMEWHAT HARD
  7. List the model number, description, and number of suppliers for those product categories that were supplied by multiple suppliers. HARD
  8. Given a customer name, list all credit cards associated with that customer. Include card number, card type (Visa, Mastercard, etc.), and expiration year and month, in the format yyyy/mm. (Arbitrarily select a customer name.)
  9. List all products in inventory that were purchased more than 18 months ago. Display the serial number, purchase date, supplier ID, supplier name, supplier phone number.
  10. List supplier number and supplier name for all suppliers for which there are no current orders. Sort the list in ascending order by supplier name.

Include data in your tables to test these queries.