Computer Science · Oct / Nov 2020 · Paper 2 HL · Question 3
Adapt for 2027Queries, 3NF and composite keys are on the course, but non-persistent derived fields are not named; write the query in SQL.
IBCS
As printed in the paper
The question as text
3. WineForAll is a retailer that sells wine in its stores. Each store sells wine from a number of vineyards. The following extract from the Wine file contains unnormalized data. NameOfWine Vineyard Description UnitPrice Region StoreID StockQty Chardonnay Stormy Bay 2015, Dry, 13 % $19.99 Gisborne 1 45 Sauvignon Blanc Stormy Bay 2017, Dry, 12 % $29.99 Gisborne 2 20 Sauvignon Blanc Stormy Bay 2017, Dry, 12 % $29.99 Gisborne 1 45 Pelorus Stormy Bay 2016, Brut, 14 % $34.99 Gisborne 1 10 Pinot Noir Pecan Block 2016, Fruity, 14 % $29.99 Gisborne 3 32 Sauvignon Blanc Pecan Block 2017, Dry,13 % $29.99 Gisborne 4 25 Shiraz James Tree 2013, Fruity, 13.5 % $25.99 Hawkes Bay 3 15 Shiraz James Tree 2013, Fruity, 13.5 % $25.99 Hawkes Bay 2 40 Merlot James Tree 2014, Fruity, 14 % $29.99 Hawkes Bay 1 25 Sauvignon Blanc John Glad 2016, Dry, 12.5 % $19.99 Gladstone 4 30 Chardonnay John Glad 2017, Dry, 13.5 % $15.99 Gladstone 2 28 (Option A continues on the following page) (Option A, question 3 continued)
Read automatically from the paper; the printed page above is exact.
Show the answer and mark scheme
3(a)[1]
3(b)[3]
3(c)(i)[2]
3(c)(ii)[2]
3(d)[6]
3(e)[2]
Make a free account to save your answers and see your weak topics and ask the AI why a wrong answer is wrong.