Reference no: EM13932922
Premiere Products Database (figure 1.2)
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
|
CUSTOMER_NUM
|
CUSTOMER_NAME
|
STREET
|
CITY
|
STATE
|
ZIP
|
BALANCE
|
CREDIT_LIMIT
|
REP_NUM
|
|
148
|
Al's Appliance and Sport
|
2837 Greenway
|
Fillmore
|
FL
|
33336
|
$6,550.00
|
$7,500.00
|
20
|
|
282
|
Brookings Direct
|
3827 Devon
|
Grove
|
FL
|
33321
|
$431.50
|
$10,000.00
|
35
|
|
356
|
Ferguson's
|
382 Wildwood
|
Northfield
|
FL
|
33146
|
$5,785.00
|
$7,500.00
|
65
|
|
408
|
The Everything Shop
|
1828 Raven
|
Crystal
|
FL
|
33503
|
$5,285.25
|
$5,000,00
|
35
|
|
462
|
Bargains Galore
|
3829 Central
|
Grove
|
FL
|
33321
|
$3,412.00
|
$10,000.00
|
65
|
|
524
|
Kline's
|
838 Ridgeland
|
Fillmore
|
FL
|
33336
|
$12,762.00
|
$15,000.00
|
20
|
|
608
|
Johnson's Department Store
|
372 Oxford
|
Sheldon
|
FL
|
33553
|
$2,106.00
|
$10,000,00
|
65
|
|
687
|
Lee's Sport and Appliance
|
282 Evergreen
|
Altonville
|
FL
|
32543
|
$2,851.00
|
$5,000.00
|
35
|
|
725
|
Deerfield's Four Seasons
|
282 Columbia
|
Sheldon
|
FL
|
33553
|
$248.00
|
$7,500.00
|
35
|
|
842
|
All Season
|
28 Lakeview
|
Grove
|
FL
|
33321
|
$8,221.00
|
$7,500.00
|
20
|
Table: ORDERS
|
ORDER_NUM
|
ORDER_DATE
|
CUSTOMER_NUM
|
|
21608
|
10/20/2003
|
148
|
|
21610
|
10/20/2003
|
356
|
|
21613
|
10/21/2003
|
408
|
|
21614
|
10/21/2003
|
282
|
|
21617
|
10/23/2003
|
608
|
|
21619
|
10/23/2003
|
148
|
|
21623
|
10/23/2003
|
608
|
Table: ORDER_LINE
|
ORDER_NUM
|
PART_NUM
|
NUM_ORDERED
|
QUOTED_PRICE
|
|
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
|
|
21617
|
BV06
|
2
|
$794.95
|
|
21617
|
CD52
|
4
|
$150.00
|
|
21619
|
DR93
|
1
|
$495.00
|
|
21623
|
KV29
|
2
|
$1,290.00
|
Table: PART
|
PART_NUM
|
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
|
$129.95
|
|
DR93
|
Gas Range
|
8
|
AP
|
2
|
$495.00
|
|
DW11
|
Washer
|
12
|
AP
|
3
|
$399.99
|
|
FD21
|
Stand Mixer
|
22
|
HW
|
3
|
$159.95
|
|
KL62
|
Dryer
|
12
|
AP
|
1
|
$349.95
|
|
KT03
|
Dishwasher
|
8
|
AP
|
3
|
$595.00
|
|
KV29
|
Treadmill
|
9
|
SG
|
2
|
$1,390.00
|
Use SQL, and the Premiere Products database (see Figure 1.2) to complete the following exercises.
1. For each order placed on October 20, 2003, list the order number along with the number and name of the customer that
placed the order.
2. Use the IN operator to find the number and name of each customer that placed an order on October 20, 2003.
3. Find the number and name of each customer that did not place an order on October 20, 2003.
4. For each order, list the order number, order date, part number, part description, and item class for each part that makes up
the order but order the rows by item class and then by order number.
5. Find the rep number, last name, and first name of each sales rep who represents at least one customer with a credit limit of
$10,000. List each sales rep only once in the results. Do not use a subquery.
6. List the part number, part description, and item class for each pair of parts that are in the same item class.
7. List the order number and order date for each order that contains an order line for a Gas Range.
8. List the order number and order date for each order that was placed by Ferguline for a Gas Range.
9. List the part number, part description, unit price, and item class for each part that has a unit price greater that the unit price
of every part in item class AP. Use either the ALL or ANY operator in your query. (Hint: Make sure you select the correct
operator.)
10. For each part, list the part number, description, units on hand, order number, and number of units ordered. All parts
should be included in the results. For those parts that are currently not on order, the order number and number of units
ordered should be left blank. Order the results by part number.
|
Can a stock have a zero or negative beta
: Can a stock have a zero or negative beta? If so, what would that mean and what type of stock (industry sector) would have a zero or negative beta? As an investor, is there any time that you would want to include a stock with a zero or negative bet..
|
|
Difference between a dnp and a phd in nursing
: What is the difference between a DNP and a PhD in nursing? Which of these would you choose to pursue if you decide to continue your education to the doctoral level ?
|
|
Overview of just war theory and its tenets
: Examine Ethics: Ethics of War for a full overview of Just War theory and its tenets to include in your analysis. Identify at least one strength and at least one weakness of Just War theory
|
|
Financial risk management using derivagem software
: Answer this question using the data on daily closing prices provided in the spreadsheet Assignment Data.xlsx for Securities A, B, and C, for the time period from July 2, 2012 to June 30, 2014.1
|
|
List the items from the table
: List the part number, part description, and item class for each pair of parts that are in the same item class.
|
|
What does result suggest about cell phone as cause of cancer
: Is the result very different from the probability of 0.000340 that was found for the general population? What does the result suggest about cell phones as a cause of such cancers, as has been claimed?
|
|
Describe the procurement cycle
: Describe the Procurement cycle. Explain how it is different than Purchasing. How does Procurement fit into the overall strategy of the organization?
|
|
What regions in the cerebral cortex are known to be involved
: What regions in the cerebral cortex are known to be involved in movement? How do these areas contribute to the production of motor behavior?
|
|
Calculate the minimum and maximum for a a pay grade
: Calculate the minimum and maximum for a a pay grade with a midpoint of $35,000 per year and a range spread of 30%. Assume a 40% range spread, what is the midpoint for a pay grade with a minimum of $22,750 annually
|