A Guide to MySQL Chapter 4 Premiere

Topics: Customer service, Microwave oven, Consultative selling Pages: 10 (677 words) Published: February 3, 2014


A Guide to MySQL Chapter 4 Premiere

S3 Written Assignment

1. List the part number, description, and price for all parts SELECT PART_NUM, DESCRIPTION,PRICE FROM premiere.part;
PART_NUM
DESCRIPTION
PRICE
AT94
Iron
24.95
BV06
Home Gym
794.95
CD52
Microwave Oven
165.00
DL71
Cordless Drill
129.95
DR93
Gas Range
495.00
DW11
Washer
399.99
FD21
Stand Mixer
159.95
KL62
Dryer
349.95
KT03
Dishwasher
595.00
KV29
Treadmill
1390.00

2. List all rows and columns for the complete ORDERS table.
SELECT * FROM premiere.orders;
ORDER_NUM
ORDER_DATE
CUSTOMER_NUM
21608
2007-10-20
148
21610
2007-10-20
356
21613
2007-10-21
408
21614
2007-10-21
282
21617
2007-10-23
608
21619
2007-10-23
148
21623
2007-10-23
608

3. List the names of customers with credit limits of $7,500 or less. SELECT CUSTOMER_NAME,CREDIT_LIMIT FROM premiere.customer WHERE CREDIT_LIMIT =10 AND ON_HAND=7500; PART_NUM
DESCRIPTION
ON_HAND_VALUE
BV06
Home Gym
35772.75
KV29
Treadmill
12510.00

10. Use the IN operator to list the part number and part description of each part in item class AP or SG. SELECT PART_NUM,DESCRIPTION FROM part WHERE CLASS IN(‘AP’,’SG’); PART_NUM
DESCRIPTION
BV06
Home Gym
CD52
Microwave Oven
DR93
Gas Range
DW11
Washer
KL62
Dryer
KT03
Dishwasher
KV29
Treadmill

11. Find the number and name of each customer whose name begins with the letter “K”. SELECT CUSTOMER_NUM,CUSTOMER_NAME FROM customer WHERE CUSTOMER_NAME LIKE ‘K%’; CUSTOMER_NUM
CUSTOMER_NAME
524
Kline's

12. List all details about all parts. Order the output by part description. SELECT * FROM part ORDER by DESCRIPTION;
PART_NUM
DESCRIPTION
ON_HAND
CLASS
WAREHOUSE
PRICE
DL71
Cordless Drill
21
HW
3
129.95
KT03
Dishwasher
8
AP
3
595.00
KL62
Dryer
12
AP
1
349.95
DR93
Gas Range
8
AP
2
495.00
BV06
Home Gym
45
SG
2
794.95
AT94
Iron
50
HW
3
24.95
CD52
Microwave Oven
32
AP
1
165.00
FD21
Stand Mixer
22
HW
3
159.95
KV29
Treadmill
9
SG
2
1390.00
DW11
Washer
12
AP
3
399.99

13. List all details about all parts. Order the output by part number within item class. (That is, order the output by item class and then by part number.) SELECT * FROM part ORDER by CLASS,PART_NUM;

PART_NUM
DESCRIPTION
ON_HAND
CLASS
WAREHOUSE
PRICE
CD52
Microwave Oven
32
AP
1
165.00
DR93
Gas Range
8
AP
2
495.00
DW11
Washer
12
AP
3
399.99
KL62
Dryer
12
AP
1
349.95
KT03
Dishwasher
8
AP
3
595.00
AT94
Iron
50
HW
3
24.95
DL71
Cordless Drill
21
HW
3
129.95
FD21
Stand Mixer
22
HW
3
159.95
BV06
Home Gym
45
SG
2
794.95
KV29
Treadmill
9
SG
2
1390.00

14. How many customers have balances that are more than their credit limits? SELECT CUSTOMER_NUM,CUSTOMER_NAME,BALANCE,CREDIT_LIMIT FROM customer WHERE BALANCE>CREDIT_LIMIT; CUSTOMER_NUM
CUSTOMER_NAME
BALANCE
CREDIT_LIMIT
408
The Everything Shop
5285.25
5000.00
842
All Season
8221.00
7500.00

15. Find the total of the balances for all customers represented by sales rep 65 with balances that are less than their credit limits. SELECT CUSTOMER_NUM,CUSTOMER_NAME,BALANCE,CREDIT_LIMIT FROM customer WHERE BALANCE AND REP_NUM>=’65’; CUSTOMER_NUM

CUSTOMER_NAME
BALANCE
CREDIT_LIMIT
356
Ferguson's
5785.00
7500.00
462
Bargains Galore
3412.00
10000.00
608
Johnson's Department Store
2106.00
10000.00

16. List the part number, part description, and on-hand value of each part whose number of units on hand is more than the average number of units on hand for all parts. (Hint: Use a subquery.) SELECT PART_NUM,DESCRIPTION,(ON_HAND*PRICE) AS ON_HAND_VALUE FROM part WHERE ON_HAND>(SELECT AVG(ON_HAND) FROM part); PART_NUM

DESCRIPTION
ON_HAND_VALUE
AT94
Iron
1247.50
BV06
Home Gym
35772.75
CD52
Microwave Oven
5280.00
FD21
Stand Mixer
3518.90

17. What is the price of the most expensive part in the database? SELECT MAX(PRICE) FROM...

References: Pratt, P. J., & Last, M. Z. (2006). A Guide to MySQL . Boston, Massachusetts: Course Technology.
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Chapter 4 Study Guide Research Paper
  • Chapter 4 Study Guide Essay
  • a guide to mysql ch 7 Essay
  • Chapter 4 & 5 Study Guide Essay
  • Chapter 4 Psychology Study Guide Essay
  • History chapter 4 study guide Essay
  • Essay about CHAPTER 3 AND 4 STUDY GUIDE
  • Chapter 4 Study Guide Essay

Become a StudyMode Member

Sign Up - It's Free