HOMEWORK # 5: Premier Products
Practice with SQL, Forms

Here is the CREATE script and the INSERT script for the tables.

PART I. PRACTICE - READING TABLES. Without using a computer, use the provided tables to answer the following questions:

1. Find the names of all the customers who have a credit limit of at least $1500.
2. Give the order numbers of those orders placed by customer 124 on Sept.5, 1998.
3. Give the part number, description, and on-hand value (units on hand * price) for each part in item class AP.
4. Find the number and name of each customer whose last name is Nelson.
5. How many customers have a credit limit of $1000.
6. Find the total balance for all the customers represented by sales rep 12.
7. For each order, list the order number, order date, customer number and customer name.
8. For each order placed on Sept 5,1998 list the order number, order date, customer number and customer name.
9. Find the number and name of each sales rep who represents any customer with a credit limit of $1000.
10. For each order, list the order number, order date, customer number, customer name, along with the number and name of the sales rep who represents the customer.

PART II. PRACTICE - SQL SYNTAX. Specify the correct SQL query for each of the following:

1. For every order, list the order number and order date along with the customer number, last name, and first name of the customer who placed the order.
2. For evey order, list the order number, order date, part number, number of unit ordered, and quoted price for each order line that makes up the order
3. List the part description, part number, order number, and order date for everyorder placed by Mary Nelson that contains an order line for a treadmill.
4. For every order, list the order number, order date, part number, part descriptionand item class for each part that makes up the order.
5. Use a subquery to find the sales rep number, last name, and first name for everysales rep who represents at least one customer with a credit limit of $2000
6. Find the customer number, last name, and first name for every customer who currently has an order on file for an iron.

PART III. PRACTICE SQL SYNTAX

For each question in PART I, specify the correct SQL query.

PART IV. CREATING FORMS.

1. Create a form for data entry into the sales_rep table.
2. Using your form, insert 2 new records into the sales_rep table.
3. Create a form for populating the customer table.
4. Using your form, insert 3 new records into the customer table.
5. Create a form to display the contents of the orders table.
6. Create a form to display the contents of the part table.
7. Create a form to display the contents of the order_line table.

Submission Instructions

• Submit a printed copy of each query.
  
• Submit a printed copy of the query results.
  
• Submit a printed copy of each form.
  
• If assignments are printed on perforated paper, please separate each sheet prior to submission.
  
• Do not use any fancy folders. Simply staple the papers together. Please use one staple in the upper left hand corner to bind all sheets together.
  
• Make sure your name is clearly printed on the first page of the assignment.