| 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 |
|
|
|
In this
question, candidates should not be repeatedly penalised for the same error. For instance,
a candidate who consistently omits the final semicolon should be penalised for this error
just once. |
|
|
|
|
|
(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] |
|
(a) |
 |
|
|
[3
marks] |
|
|
Such a
view might be useful for maintainability purposes: this view can remain constant, even if
extra fields are added to the SUPPLIER table. |
|
|
[1
mark] |
|
|
Credit should
be given for an alternative credible use for the view. |
|
|
|
|
|
(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] |
|
(b) |
 |
|
|
[4
marks] |
|
|
|
|
|
(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] |
|
(c) |
 |
|
|
[4
marks] |
|
|
|
|
|
(d) Find the part numbers for all
parts which are supplied by more than one supplier. |
[4] |
|
(d) |
 |
|
|
[4
marks] |
|
|
|
|
|
(e) What SQL query will enable the
marketing director to find out which suppliers are in the same country as S2? |
[4] |
|
(e) |
 |
|
|
[4
marks] |
|