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 display the required fields for plants in both the Rose and Flower categories, you can use:

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

This query selects the relevant columns where the category is either 'ROSE' or 'FLOWER' and the colour contains 'Pink'.

To calculate the average price of all plants grouped by category, the following SQL statement can be used:

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

This query computes the average price for each category and formats the result in currency.

To display invoice information for invoice number F2, the SQL query will look like this:

SELECT InvoiceNum, Description, NumberOrdered
FROM tblOrders
WHERE InvoiceNum = 'F2';

This retrieves the specified fields from the tblOrders table for the invoice number F2.

To update the NumDelivered field to match NumOrdered, you can use the following SQL statement:

UPDATE tblOrders
SET NumberDelivered = NumberOrdered
WHERE ItemNum = ?;

In this case, ? should be replaced with the actual item number variable from the user input.