Knoppie [2.1.1 - Lys van rose] Vertoon die besonderehede van die plante in die Roos-kategorie in die tblPlante-tafel - NSC Information Technology - Question 2 - 2019 - Paper 1

To display the fields [PlantKode, Kategoreien, Kleur, and GrootteVanPot] for all the plants in the Blom and Roos categories that have a specific type of pink color, we can use:

SELECT PlantKode, Kategorie, Kleur, GrootteVanPot 
FROM tblPlante 
WHERE (Kategorie = 'Roos' OR Kategorie = 'Blom') 
AND Kleur LIKE '%Pienk%';

This SQL query filters the plants based on their categories and ensures the color contains 'Pienk'.

To calculate the average price per category from the tblPlante table and display it in currency format, the SQL query will be:

SELECT Kategorie, FORMAT(AVG(Prys), 'Currency') AS [GemiddeldePrys] 
FROM tblPlante 
GROUP BY Kategorie;

This statement selects the category and formats the average price, grouping the results by category.

To retrieve the FaktuurNom, Beskrywing, and GetalBestel fields from the tblBestellings table for the specific invoice number F2, we should execute:

SELECT FaktuurNom, Beschrywing, GetalBestel 
FROM tblBestellings 
WHERE Plantkode = (SELECT Plantkode FROM tblPlante WHERE Kategoreien = 'F2');

This ensures we get results specifically for invoice F2.

To update the tblBestellings table by setting the GetalAfgelewer to the GetalBestel value where the ItemNom matches the specified value, the SQL command will be:

UPDATE tblBestellings SET GetalAfgelewer = GetalBestel 
WHERE ItemNom = QuotedStr(ItemNom);

This effectively updates the delivered quantity based on the item number.

To place an order into the tblBestellings table, we can use the INSERT statement:

INSERT INTO tblBestellings (FaktuurNom, Plantkode, GetalBestel, GetalAfgelewer) 
VALUES ('F2', 'DIP002#M', 125, 0);

This adds a new order entry with the specified details.