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 plant information for those in the Flower and Rose categories with a specific colour (Pink), the SQL query can be written as:

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

This ensures that we get all plants belonging to either the Rose or Flower category while specifically filtering for the Pink colour.

To calculate the average price for each plant category and format it in currency, you can use the following SQL statement:

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

This query computes the average price for plants grouped by their category.

To display invoice information for the specified number (F2), the appropriate SQL query is:

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

This retrieves details from both tables based on the matching plant code and filters for the invoice number F2.

To update the number of items delivered in the tblOrders table, the SQL statement would be:

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

This modifies the NumberDelivered field to match NumberOrdered where the ItemNum is equal to a certain variable.