| Consider the
following four tables. |
|
Customer
| CUSTNUM |
CUSTNAME |
CUSTADDR |
BALANCE |
CREDLIM |
REPNUMB |
| 124 |
Adams, Sally |
481 Oak, Lansing |
418.75 |
500 |
3 |
| 256 |
Samuels, Ann |
215 Pete, Grant |
10.75 |
800 |
6 |
| 311 |
Charles, Don |
48 College, Ira |
200.10 |
300 |
12 |
| 315 |
Daniels, Tom |
914 Cherry, Kent |
320.75 |
300 |
6 |
| 405 |
Wiliams, Al |
519 Watson, Grant |
201.75 |
800 |
12 |
| 412 |
Adams, Sally |
16 Elm, Lansing |
908.75 |
1000 |
3 |
| 522 |
Nelson, Mary |
108 Pine, Ada |
49.50 |
800 |
12 |
| 567 |
Baker, Joe |
808 Ridge, Harper |
201.20 |
300 |
6 |
| 587 |
Rogerts, Judy |
512 Pine, Ada |
57.75 |
500 |
6 |
| 622 |
Martin, Dan |
419 Chip, Grant |
575.50 |
500 |
3 |
Order
| ORDNUMB |
ORDDTE |
CUSTNUMB |
| 12489 |
90291 |
124 |
| 12491 |
90291 |
311 |
| 12494 |
90491 |
315 |
| 12495 |
90491 |
256 |
| 12498 |
90591 |
522 |
| 12500 |
90591 |
124 |
| 12504 |
90591 |
522 |
ORDLNE
| ORDNUMB |
PARTNUMB |
NUMBORD |
QUOTPRCE |
| 12489 |
AX12 |
11 |
14.95 |
| 12491 |
BT04 |
1 |
402.99 |
| 12491 |
BZ66 |
1 |
311.95 |
| 12494 |
CB03 |
4 |
175.00 |
| 12495 |
CS11 |
2 |
57.95 |
| 12498 |
AZ52 |
2 |
22.95 |
| 12498 |
BA74 |
4 |
4.95 |
| 12500 |
BT04 |
1 |
502.99 |
| 12504 |
CZ81 |
2 |
108.99 |
PART
| PARTNUMB |
PARTDESC |
UNONHAND |
ITEMCLSS |
WRHSNUMB |
UNITPRCE |
| AX12 |
IRON |
104 |
HW |
3 |
17.95 |
| AZ52 |
SKATES |
20 |
SG |
2 |
24.9 |
| BA74 |
BASEBALL |
40 |
SG |
1 |
4.95 |
| BH22 |
TOASTER |
95 |
HW |
3 |
34.95 |
| BT04 |
STOVE |
11 |
AP |
2 |
402.99 |
| BZ66 |
WASHER |
52 |
AP |
3 |
311.95 |
| CA14 |
SKILLET |
2 |
HW |
3 |
19.95 |
| CB03 |
BIKE |
44 |
SG |
1 |
187.50 |
| CX11 |
MIXER |
112 |
HW |
3 |
57.95 |
| CZ81 |
WEIGHTS |
208 |
SG |
2 |
108.99 |
|
|
| Based on the tables
above, write SQL statements to carry out the following tasks: |
| (a) |
Create the PART table, using the following
table definition. PART
| Column |
Type |
Length |
Decimal Places |
| PARTNUMB |
Char |
4 |
|
| PARTDESC |
Char |
10 |
|
| UNONHAND |
Numeric |
3 |
0 |
| ITEMCLSS |
Char |
2 |
|
| WRHSNUMB |
Numeric |
1 |
0 |
| UNITPRCE |
Numeric |
7 |
2 |
|
[4] |
| (b) |
From the CUSTOMER table, which records can be
removed without introducing any anomaly? Justify your answer. Give an SQL statement to
remove one of these records. |
[3] |
| (c) |
Find the number, name, current balance and
sales rep number of those customers whose balance is greater than the balance of every
customer of sales rep 12. |
[6] |
| (d) |
Create a view LISTING with headers
ORDER_NUMBER and ORDER_TOTAL to list the order total for those orders whose total is more
than $200. |
[5] |
| (e) |
Give two advantages of using the VIEW command. |
[2] |