database: ----------------------------------------------------------------------

Question

database:

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

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.

Explanation / Answer

Solution:

1)

SELECT wineID FROM Wines WHERE name LIKE 1998%

2)

SELECT AVG(pricePerBottle) FROM Wines

3)

SELECT wineID, name FROM OrderLookup WHERE wineID is NULL

4)

SELECT SUM(pricePerBottle * qty) FROM OrderLookup INNER JOIN Wines.wineID= OrderLookup.wineID.

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.