Query List -- Spring 2006


  1. List the mechanic#, hours worked, salary per hour, and total salary for each mechanic who worked more than 40 hours.  (You may have to add appropriate data to your mechanic table to get any results.)

  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 May 10, 2005.

  5. List all repair details (customer#, customer name, serial number, service date, problem description) for all repairs associated with a specified customer. (The customer name* will be specified in a dialog box. Click here for details on this type of query.)

  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), 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 month and year, in the format xx/xxxx.(The customer name* will be specified in a dialog box. Click here for details on this type of query.)

  9. List the model number, description, total number of repairs in 2005, and the repair year for all product categories on a category-by-category basis.
     

  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.

* You can modify a field so that it is a lookup field by deleting the relationships, changing datatype to Lookup Wizard, and then re-establishing the relationship.  There is no problem if there is data in the table.

Include data in your tables to test these queries.