Ferguson's Department Store Handbook

Complete PPE QBE exercises 1-13 on page 68, Pratt book. You can use the PPE database file located in the resource section of the online classroom. Provide your responses in a word document.
1. List the number and name of all customers.
Customer Number
Customer Name
148
Al's Appliance and Sport
282
Brookings Direct
356
Ferguson's
408
The Everything Shop
462
Bargains Galore
524
Kline's
608
Johnson's Department Store
687
Lee's Sport and Appliance
725
Deerfield's Four Seasons
842
All Season

SELECT CustomerNum,CustomerName
FROM Customer

2. List the complete Part table.

Part Number
Description
On Hand
Class
Warehouse
Price
AT94
Iron
50
HW
3
$24.95
BV06
Home Gym
45
SG
2
$794.95
CD52
Microwave Oven
32
AP
1
$165.00
DL71
Cordless Drill
21
HW
3
List the number and name of all customers represented by sales rep 35.

Name
Number
Brookings Direct
282
The Everything Shop
408
Lee's Sport and Appliance
687
Deerfield's Four Seasons
725

SELECT CustomerNum, CustomerName
FROM Customer
WHERE RepNum = '35';

4. List the number and name of all customers that are represented by sales rep 35 and that have a credit limit of $10,000.

Name
Number
Brookings Direct
282

SELECT CustomerNum,CustomerName
FROM Customer
WHERE ([RepNum]="35") AND
([CreditLimit]=10000);

5. List the number and name of all customers that are represented by sales rep 35 or that have a credit limit of $10,000.

Name
Number
Brookings Direct (35)
282
Bargains Galore
462
Johnson's Department Store
608

SELECT CustomerNum,CustomerName
FROM Customer
WHERE ([RepNum]="35") OR
([CreditLimit]=10000);

6. For each order, list the order number, order date, number of the customer that placed the order, and name of the customer that placed the order.

Order Number
Order Date
Customer Number
Customer Name
21608
10/20/2013
148
Al's Appliance and Sport
21619
10/23/2013
148
Al's Appliance and
List the number and name of all customers represented by Juan Perez.

Juan Perez Customers
Ferguson's
Bargains Galore
Johnson's Department Store

SELECT CustomerNum,CustomerName
FROM Customer
WHERE ([RepNum]="65")

8. How many customers have a credit limit of $10,000?

Three

Customers
Brookings Direct
Bargains Galore
Johnson's Department Store

SELECT CustomerNum,CustomerName
FROM Customer
WHERE ([CreditLimit]=10000);
9. Find the total of the balances for all customers represented by sales rep 35.

Sum of Balances
$8,815.75

SELECT SUM(Balance)
FROM Customer
WHERE RepNum = '35';

10. Give the part number, description, and on-hand value (OnHand * Price) for each part in item class HW.

Part Number
Description
On-Hand Value
AT94
Iron
50 ($1,247.50)
DL71
Cordless Drill
21 ($2,728.95)
FD21
Stand Mixer
22 ($3,518.90)

SELECT PartNum, Description, OnHand * Price
FROM