Write a script that creates and calls a stored procedure named test. This stored

Question

Write a script that creates and calls a stored procedure named test. This stored procedure should declare a variable and set it to the count of all products in the Products table. If the count is greater than or equal to 7, the stored procedure should display a message that says, “The number of products is greater than or equal to 7”. Otherwise, it should say, “The number of products is less than 7”.

2. Write a script that creates and calls a stored procedure named test. This stored procedure should use two variables to store (1) the count of all of the products in the Products table and (2) the average list price for those products. If the product count is greater than or equal to 7, the stored procedure should display a result set that displays the values of both variables. Otherwise, the procedure should display a result set that displays a message that says, “The number of products is less than 7”.

3. Write a script that creates and calls a stored procedure named test. This procedure should calculate the common factors between 10 and 20. To find a common factor, you can use the modulo operator (%) to check whether a number can be evenly divided into both numbers. Then, this procedure should display a string that displays the common factors like this: Common factors of 10 and 20: 1 2 5 10

4. Write a script that creates and calls a stored procedure named test. This procedure should attempt to insert a new category named “Guitars” into the Categories table. If the insert is successful, the procedure should display this message:

Using MySQL workbench

Explanation / Answer

1

DROP PROCEDURE IF EXISTS test;//drop if already test name procedure avilable

DELIMITER //

CREATE PROCEDURE test()

BEGIN

DECLARE countprod int;

   SELECT count(*) into countprod //taking total count into variable countprod

   FROM product ;

   if countprod>=7 then    //checking fro condition is greater than 7 or not

   select 'The number of products is greater than or equal to 7' as col1;

   else

   select 'The number of products is less than 7' as col1;

   end if;

END //

DELIMITER ;

call test()

2.

SELECT avg(price) FROM product

DROP PROCEDURE IF EXISTS test;                ;//drop if already test name procedure avilable

DELIMITER //

CREATE PROCEDURE test()

BEGIN

DECLARE countprod int;                                   //declaring two variable countprod and avgprod

DECLARE avgprod decimal(10,8);

   SELECT count(*) into countprod //put total count in countprod

   FROM product ;

   SELECT avg(price) into avgprod         //put average in avgprod

   FROM product ;

   if countprod>=7 then   

   select countprod ,avgprod as col1;    //col1 is name for new column

   else

   select 'The number of products is less than 7';

   end if;

END //

DELIMITER ;

call test()

3.

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test ()

BEGIN

    DECLARE counter INT;

    DECLARE num1 INT;

    DECLARE num2 INT;

    DECLARE factors varchar (100);

    SET num1 = 10;

    SET num2 = 20;

    SET counter = 1;

    SET factors = 'Factors of 10 and 20: '; //declare num1 and num2 for 10 and 20 then factor for string concatination

WHILE( counter <= 20/2) //always check 20/2 is 10 and counter is 1 so 1 is < than 10 true till condition false like counter will increase from 1 ,2,3,4,5 till 10

do                           

                                    IF ( num1 % counter = 0 AND num2 % counter = 0) THEN

//if 10%1=0 true and 20 %1 =0 true then factor will have value concat( 'Factors of 10 and 20: ',1,’’) so on

Bt 10%3 Is not equal to zero and 20%3 is also not equal to 0 so condition false ended if then counter increment by 1 .. and so on checking.. till all value satisfied //

                                                SET factors = CONCAT (factors, counter, ' ');

                                    END IF;

            SET counter = counter + 1;

                        END WHILE;

        SELECT factors;

   END

call test()

4.

DROP PROCEDURE IF EXISTS test;

DELIMITER //

CREATE PROCEDURE test (in id int(5),IN name1 varchar(50))// taking two param from procedure

BEGIN

    insert into Categories (id,`name`) values (id,name1);

    select 'record inserted successfully' as col1;

END;

call test(7,’Guitars’)

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.