-- Opdracht 1 | Products /* Toon per vend_id het aantal producten vanaf 3. */ -- vend_id num_prods -- BRS01 3 -- DLL01 4 SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id HAVING COUNT(*) >= 3; -- Opdracht 2 | Products /* Breid opdracht 1 uit met een filter op de prod_price vanaf 4. */ -- vend_id num_prods -- BRS01 3 SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 3; -- Opdracht 3 | Products /* Toon per vend_id de totale prijs kleiner dan 20. Sorteer aflopend op de totale prijs. */ -- vend_id total -- FNG01 18.98 -- DLL01 15.46 SELECT vend_id, SUM(prod_price) AS total FROM Products GROUP BY vend_id HAVING SUM(prod_price) [kleinerdanteken] 20 ORDER BY total DESC; -- Opdracht 4 | OrderItems /* Toon per prod_id het percentage van het totaal aantal bestelde artikelen. */ -- prod_id prod_percent -- BNBG01 25.17 % -- BNBG02 25.17 % -- BNBG03 25.17 % -- BR01 8.39 % -- BR02 0.70 % -- BR03 11.54 % -- RGAN01 3.85 % SELECT prod_id, FORMAT (CONVERT(numeric, SUM(quantity)) / CONVERT(numeric,(SELECT SUM(quantity) FROM OrderItems)), 'P') AS prod_percent FROM OrderItems GROUP BY prod_id; -- MySQL SELECT prod_id, SUM(quantity) / (SELECT SUM(quantity) FROM OrderItems) AS prod_percent FROM OrderItems GROUP BY prod_id;
Download hier het bestand.