Premiere Products Database
Table: Rep
|
Rep_Num
|
Last_Name
|
First_Name
|
Street
|
City
|
State
|
Zip
|
Commission
|
Rate
|
|
20
|
Kaiser
|
Valerie
|
624 Randall
|
Grove
|
FL
|
33321
|
$20,542.50
|
0.05
|
|
35
|
Hull
|
Richard
|
532 Jackson
|
Sheldon
|
FL
|
33553
|
$39,216.00
|
0.07
|
|
65
|
Perez
|
Juan
|
1626 Taylor
|
Fillmore
|
FL
|
33336
|
$23,487.00
|
0.05
|
Table: Customer
|
CustomerNum
|
CustomerName
|
Street
|
City
|
State
|
Zip
|
Balance
|
CreditLimit
|
|
148
|
Al's Appliance and Sport
|
2837 Greenway
|
Filmore
|
FL
|
33336
|
$6,550.00
|
$7,500.00
|
|
282
|
Brookings Direct
|
3827 Devon
|
Grove
|
FL
|
33321
|
$431.50
|
$10,000.00
|
Table: Orders
|
OrderNum
|
OrderDate
|
CustomerNum
|
|
21608
|
10/20/2003
|
148
|
|
21610
|
10/20/2003
|
356
|
|
21613
|
10/21/2003
|
408
|
|
21614
|
10/21/2003
|
282
|
Table: OrderLine
|
OrderNum
|
PartNum
|
NumOrdered
|
QuotedPrice
|
|
21608
|
AT94
|
11
|
$21.95
|
|
21610
|
DR93
|
1
|
$495.00
|
|
21610
|
DW11
|
1
|
$399.99
|
|
21613
|
KL62
|
4
|
$329.95
|
|
21614
|
KT03
|
2
|
$595.00
|
Table: Part
|
PartNum
|
Description
|
OnHand
|
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
|
$129.95
|
1. Define a view named CustOrder. It consists of the customer number, name, balance, order number, and order date for all
orders currently on file. Explain how you would use SQL to obtain the desired results. Display the data in the view.
2. Create a view named OrdTot. It consists of the order number and order total for each order currently on file. (The order
total is the sum of the number ordered multiplied by the quoted price on each order line for each order.) Explain how you
would use SQL to obtain the desired results. Display the data in the view.