Button [2.1.1 - List of roses] Display all the details of the plants in the Rose category in the tblPlants table - NSC Information Technology - Question 2 - 2019 - Paper 1

To retrieve the PlantCode, Category, Colour, and SizeOfPot fields for all plants in the Flower and Rose categories, use:

SELECT PlantCode, Category, Colour, SizeOfPot FROM tblPlants WHERE (Category = 'ROSE' OR Category = 'FLOWER') AND Colour LIKE '%Pink%';

This ensures that we get records from both categories that include the colour Pink.

To calculate the average price of plants per category, the following SQL can be used:

SELECT Category, FORMAT(AVG(Price), 'Currency') AS AveragePrice FROM tblPlants GROUP BY Category;

This retrieves the average price formatted as currency for each category.

To display information for invoice number F2, execute:

SELECT InvoiceNum, Description, NumberOrdered FROM tblPlants, tblOrders WHERE tblOrders.PlantCode = tblPlants.PlantCode AND InvoiceNum = 'F2';

This SQL query joins both tables based on the PlantCode and filters by InvoiceNum.

To update the NumberDelivered field to match the NumberOrdered for a specific item, use:

UPDATE tblOrders SET NumberDelivered = NumberOrdered WHERE ItemNum = 'QuotedStr(ItemNum)';

This SQL command correctly updates the records according to the provided ItemNum.