database: ----------------------------------------------------------------------
ID: 3699660 • Letter: D
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.Change all wines with a style of ‘Dry White’ to’Dry-White’.
2.Show the telephone number of all customers with an outstanding order.
3.Find all wines produced by South Africa.
4.Find number of French wines handled by each vineyard.
Explanation / Answer
1..
Update Wines set where;
2.
Select telephone from Members inner join Orders on Members.memberID = Orders.memberID inner join OrderLookup on OrderLookup.orderID = Orders.orderID;
Order is outstanding if it is present is OrderLookup table
3.
Select name from Wines where countryOfOrigin = 'South Africa';
4.
Select vineyardID,count(wineID) from Wines group by vineyardID;
Do ask if any doubt. Please upvote.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.