8. SQL Programming

Activity 8.01: Building a Stored Procedure

Solution:

1. Execute the following query:

   CREATE DEFINER=`root`@`localhost` PROCEDURE `spFilterOrdersByItemQuantity`(IN orderItemQuantityVal int)
   BEGIN
    SELECT  OI.OrderID, SUM(OI.Quantity)      AS 'Total Order Item Quantity'
    FROM  OrderItems OI
    GROUP BY OI.OrderID
    HAVING SUM(OI.Quantity) <= orderItemQuantityVal
    ORDER BY OI.OrderID;
   END

2. Test the stored procedure using the following query:

   USE packt_online_shop;
   CALL spFilterOrdersByItemQuantity(25);

Activity 8.02: Working with MySQL Functions

Solution:

1. Write the following query:

   CREATE DEFINER =`root`@`localhost` FUNCTION `fnProductTotalOrderQty`(ProductID INT)
   RETURNS INT
   DETERMINISTIC
   BEGIN
   DECLARE retVal INT;
   SET retVal = (
   SELECT
   CASE
       WHEN     SUM(OI.quantity) IS NULL THEN 0
   ELSE
       SUM(OI.quantity)
   END AS 'quantity'
   FROM     OrderItems...