database:

DROP TABLE OrderLookup; DROP TABLE Orders; DROP TABLE Wines; DROP TABLE Vineyards; DROP TABLE Members; CREATE TABLE Members (memberID char(6) primary key, surname varchar2(30) not null, forename varchar2(30) not null, address varchar2(50) not null, town varchar2(25) not null, postcode varchar2(10) not null, telephone varchar2(15) not null ); CREATE TABLE Vineyards (vineyardID char(6) primary key, name varchar2(40) not null, address varchar2(50) not null, country varchar2(35) not null, contactName varchar2(30) not null, contactTelephone varchar2(20) not null); CREATE TABLE Wines (wineID char(6) primary key, name varchar2(30) not null, countryOfOrigin varchar2(30) not null, vineyardID char(6) not null references vineyards, style varchar2(20) not null, pricePerBottle decimal(6,2) not null, stockLevel integer not null, minStock integer not null ); CREATE TABLE Orders (orderID char(6) primary key, memberID char(6) not null references members, dateOfOrder date not null ); CREATE TABLE OrderLookup (orderID char(6) not null, wineID char(6) not null, qty integer not null, primary key (OrderID,wineID) ); insert into MEMBERS values ('MB0001', 'Smith', 'Peter', '11 Summerfield Road', 'London', 'SW3 7YH', '020 776876'); insert into MEMBERS values ('MB0002', 'Arnold', 'Sarah', '239 Oaktree Lane', 'Coventry', 'CV7 8KL', '024 76987097'); insert into MEMBERS values ('MB0003', 'Ali', 'Mohammed', '71 Wickers Lane', 'Birmingham', 'B67 5FG', '0121 6576384'); insert into MEMBERS values ('MB0004', 'Bent', 'Simon', 'The Old House', 'Wolverhampton', 'WV87 9LK', '01904 8876762'); insert into MEMBERS values ('MB0005', 'Vickers', 'Fiona', '2 Jolly Steep Hill', 'Cambridge', 'CB6 4FG', '01353 4545678'); insert into VINEYARDS values ('VN0001', 'Fetzer Vineyards','California','USA','Fred Fetzer','1-876875876'); insert into VINEYARDS values ('VN0002', 'Sancerre Vineyard','California','USA','Pep','33-48748993'); insert into VINEYARDS values ('VN0003', 'Moet et Chandon','California','USA','Pierre Le Pue','33-43872392'); insert into VINEYARDS values ('VN0004', 'Kruger Rumf','California','USA','Rudolf Meinsmif','49-39483093'); insert into VINEYARDS values ('VN0005', 'Killerby Vineyard','California','USA','Terence Dundee','61-93873293'); insert into VINEYARDS values ('VN0006', 'Colchagua Vineyard','California','USA','Samti El Dino','56-93023001'); insert into VINEYARDS values ('VN0007', 'Sheep Valley Vineyard','California','USA','Joe Smith','64-20002982'); insert into VINEYARDS values ('VN0008', 'Galicia Grande Vineyard','California','USA','Sam El Porte','34-29302932'); insert into VINEYARDS values ('VN0009', 'Lanchester Vineyard','Coventry','UK','Frank Johnson','44-30229343'); insert into VINEYARDS values ('VN0010', 'Chianti','California','USA','Maria Broad','27-30332129'); insert into VINEYARDS values ('VN0011', 'Thelma Mountain Vineyard','California','USA','Wendy Troen','27-39228391'); insert into VINEYARDS values ('VN0012', 'Bergwater','California','USA','Jose Smite','351-302993821'); insert into VINEYARDS values ('VN0013', 'Douro Valley Vineyard','California','USA','John Hamish ','1-928399203'); insert into VINEYARDS values ('VN0014', 'Carr Valley Vineyard','California','USA','William Carr','44-23819202'); insert into WINES values ('WN0001', 'African Legend', 'South Africa','VN0011','Red','7.49',10,25); insert into WINES values ('WN0002', 'Chablis 1998', 'France','VN0003','Dry White','6.99',12,25); insert into WINES values ('WN0003', 'Pinot Noir 1999', 'France','VN0002','Red','5.49',46,50); insert into WINES values ('WN0004', 'Chardonnay 1999', 'Australia','VN0005','Dry White','4.99',53,50); insert into WINES values ('WN0005', 'Beiras Portugal 1998', 'Portugal','VN0013','Red','7.89',15,75); insert into WINES values ('WN0006', 'Cabernet 1995', 'USA','VN0001','Red','11.99',76,25); insert into WINES values ('WN0007', 'Grenache 1998', 'Spain','VN0008','Red','3.99',55,50); insert into WINES values ('WN0008', 'Perrier NV Brut', 'France','VN0003','Champagne','19.99',12,10); insert into WINES values ('WN0009', 'Gewurztraminer 2000', 'Chile','VN0006','Red','4.99',36,30); insert into WINES values ('WN0010', 'Fonseca Bin 27 Port', 'Portugal','VN0013','Port','7.29',110,100); insert into WINES values ('WN0011', 'Merlot 1996', 'USA','VN0001','Red','6.29',87,75); insert into WINES values ('WN0012', 'Muscat 1997', 'Australia','VN0005','Med-Dry white','3.99',45,25); insert into WINES values ('WN0013', 'Shiraz 1998', 'South Africa','VN0012','Red','5.99',27,10); insert into WINES values ('WN0014', 'Chardonnay 2000', 'Chile','VN0006','Dry White','5.99',1,10); insert into WINES values ('WN0015', 'Carbernet Sauvignon 1999', 'France','VN0006','Red','5.49',0,10); insert into WINES values ('WN0016', 'Cotes du Ventoux 1996', 'France','VN0003','Red','6.99',87,100); insert into WINES values ('WN0017', 'Dr Thanish 1996', 'Germany','VN0004','Med-dry white','7.99',33,35); insert into WINES values ('WN0018', 'Lindauer Brut', 'New Zealand','VN0007','Sparkling','5.99',0,15); insert into WINES values ('WN0019', 'Pinot Noir 997', 'France','VN0002','Red','6.99',38,35); insert into WINES values ('WN0020', 'Crianza 1997', 'Spain','VN0008','Red','5.99',10,25); insert into WINES values ('WN0021', 'Martini Asti', 'Italy','VN0010','Sparkling','5.99',17,15); insert into WINES values ('WN0022', 'Cabernet-Merlot 1999', 'New Zealand','VN0007','Red','6.99',78,75); insert into WINES values ('WN0023', 'Navarra Rose 1998', 'Spain','VN0008','Dry Rose','5.99',0,25); insert into WINES values ('WN0024', 'Pedras do Monte Castelao 1998', 'Portugal','VN0013','Red','5.99',11,10); insert into WINES values ('WN0025', 'Chianti 1999', 'Italy','VN0010','Red','6.99',36,35); insert into WINES values ('WN0026', 'Carr Taylor Vintage 1998', 'UK','VN0014','Sparkling','7.99',34,28); insert into ORDERS values ('OD0001', 'MB0001','05-MAR-02'); insert into ORDERS values ('OD0002', 'MB0004','12-DEC-01'); insert into ORDERS values ('OD0003', 'MB0002','19-JAN-02'); insert into ORDERS values ('OD0004', 'MB0005','19-JAN-02'); insert into ORDERLOOKUP values ('OD0003', 'WN0026',7); insert into ORDERLOOKUP values ('OD0003', 'WN0024',11); insert into ORDERLOOKUP values ('OD0004', 'WN0001',4); insert into ORDERLOOKUP values ('OD0003', 'WN0018',12); insert into ORDERLOOKUP values ('OD0001', 'WN0020',1); insert into ORDERLOOKUP values ('OD0002', 'WN0020',4); insert into ORDERLOOKUP values ('OD0004', 'WN0026',1);

---------------------------------------------------------------------------------------------------------------------------------------

Write and execute SQL for the following queries. Note that the commands use the following tables:

Members(memberID, surname, forename, dob, address, town, postcode, telephone)

Vineyards(vineyardID, name, address, country, contactName, contactTelephone)

Wines(wineID, name, vineyardID, countryOfOrigin, style, pricePerBottle, stockLevel, minStock)

Orders(orderID, memberID, dateOfOrder)

OrderLookup(orderID, wineID, qty)

1.Show all wines from 1998.

2.Display the average price for all wines.

3.List the name and wineID for all those wines not on order.

4.Find the total value of all orders.