| 2. |
Write SQL statements to retrieve data from the
following tables: |
|
|
SUPPLIER |
|
|
| SUPPLIER_NO |
SNAME |
STATUS |
COUNTRY |
| S1 |
JASON |
MEDIUM |
AUSTRALIA |
| S2 |
TEDDY |
LOW |
JAPAN |
| S3 |
LESIE |
HIGH |
CHINA |
| S4 |
BEN |
MEDIUM |
HONG KONG |
| S5 |
WENDY |
HIGH |
JAPAN |
|
|
|
PART |
|
|
| PART_NO |
PNAME |
COLOUR |
WEIGHT |
| P1 |
DINING TABLE |
CHERRY |
15 |
| P2 |
COFFEE TABLE |
WHITE |
20 |
| P3 |
FILE CABINET |
BLUE |
12 |
| P4 |
TV CABINET |
CHERRY |
11 |
| P5 |
BOOKCASE |
BLUE |
16 |
| P6 |
SIDE TABLE |
CHERRY |
18 |
|
|
|
SUPPLIER_PART |
|
|
| SUPPLIER_NO |
PART_NO |
QUANTITY |
| S1 |
P1 |
0 |
| S1 |
P2 |
4 |
| S2 |
P3 |
13 |
| S3 |
P3 |
4 |
| S3 |
P4 |
3 |
| S4 |
P3 |
6 |
| S5 |
P2 |
7 |
| S5 |
P6 |
2 |
|
|
|
|
|
|
(a) Create a view named GOOD_SUP from the
SUPPLIER table. It should contain all the fields from the original table. Why might such a
view be useful? |
[4] |
|
|
|
|
(b) Due to changes in the manufacturing
process, part P3 is to be replaced by a new part (with the same number). The new part will
be coloured black and will weigh 2 units more than the original. What SQL command should
be used to change the tables? |
[4] |
|
|
|
|
(c) The director of marketing is organising a
trip to visit some of his suppliers. In order to minimise costs, however, he will only be
visiting countries where there is more than one supplier. What SQL query should he use to
get all the pairs of supplier names such that the two suppliers are in the same country? |
[4] |
|
|
|
|
(d) Find the part numbers for all parts which
are supplied by more than one supplier. |
[4] |
|
|
|
|
(e) What SQL query will enable the marketing
director to find out which suppliers are in the same country as S2? |
[4] |