A Guide to MySQL Chapter 4 Premiere
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
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
References: Pratt, P. J., & Last, M. Z. (2006). A Guide to MySQL . Boston, Massachusetts: Course Technology.