SQL Lab 9

# 1. STORE PROCEDURE: list_animals
# Display the list of all the animals with a name and a race
CREATE PROCEDURE list_animals()
BEGIN

SELECT animal.name, race.name as race
FROM animal join race on race.id = animal.race_id
where animal.name is not null and race_id is not null;

END|

call list_animals|

# 2. STORE PROCEDURE: fetch_animal_age
# IN: Animal id
# OUT: Animal age in numbers
CREATE PROCEDURE count_race_by_species (IN p_species_id INT, OUT p_num_races INT) 
BEGIN
    SELECT COUNT(*) INTO p_num_races
    FROM race
    WHERE species_id= p_species_id ;                             
END |

SELECT id, name INTO @var1, @var2
FROM animal
WHERE id = 7;
SELECT @var1, @var2|

CALL count_race_by_species(2, @num_cat_race)|

SELECT @num_cat_race|

DROP PROCEDURE animal_age|

CREATE PROCEDURE animal_age (IN p_animal_id INT, OUT p_num_age INT) 
BEGIN
    SELECT TIMESTAMPDIFF(YEAR, dob, NOW()) INTO p_num_age
    FROM animal
    where animal.id = p_animal_id;                             
END |

SELECT id, TIMESTAMPDIFF(YEAR, dob, NOW()) INTO @var3, @var4
FROM animal
WHERE id = 7;
SELECT @var3, @var4|


CALL animal_age(2, @num_age)|

SELECT @num_age|

# 3. STORE PROCEDURE: price_of_pets
# IN: 3 Animal ids
# OUT: Price of the 3 animals

CREATE PROCEDURE price_of_pets(IN p_animal_id INT, INOUT p_price DECIMAL(7,2))
BEGIN
    SELECT COALESCE(race.price, Species.price) INTO p_price
    FROM animal
    INNER JOIN species ON species.id = animal.species_id
    LEFT JOIN race ON race.id = animal.race_id
    WHERE animal.id = p_animal_id;
END |

SET @price = 0|
CALL price_of_pets (13, @price)|
SELECT @price AS first_price|

CALL price_of_pets (24, @price)|
SELECT @price AS second_price|

CALL price_of_pets (42, @price)|
SELECT @price AS third_price|

drop procedure price_of_pets|

CREATE PROCEDURE price_of_pets(IN p1_animal_id INT, IN p2_animal_id INT, IN p3_animal_id INT, OUT p1_num_price INT, OUT p2_num_price INT, OUT p3_num_price INT) 
BEGIN
    SELECT species.price, species.price, species.price INTO p1_num_price, p2_num_price, p3_num_price
    from animal join species on species.id = animal.species_id
    where animal.id = p1_animal_id or animal.id = p2_animal_id or animal.id = p3_animal_id;                             
END |

SELECT animal.id, species.price into @var5, @var6
FROM animal join species on species.id = animal.species_id
WHERE animal.id = (8)|
SELECT @var5, @var6|

CALL price_of_pets(5, @num_price)|

SELECT @num_age|

# 4. STORE PROCEDURE: total_age_of_animal
# IN: 1 Animal id
# INOUT: Cummulative age of the animals
# Do it for 5 animals
CREATE PROCEDURE total_age_of_animal (IN p_animal_id INT, INOUT p_num_age INT) 
BEGIN
    SELECT TIMESTAMPDIFF(YEAR, dob, NOW()) INTO p_num_age
    FROM animal
    where animal.id = p_animal_id;                       
END |

SELECT id, TIMESTAMPDIFF(YEAR, dob, NOW()) INTO @var11, @var12
FROM animal
WHERE id = 7|
SELECT @var11, @var12|


CALL total_age_of_animal(2, @p_num_age)|

SELECT @num_age|

drop procedure total_age_of_animal|

set @age = 'select TIMESTAMPDIFF(YEAR, dob, NOW()) from animal'|
prepare age
from @age|


CREATE PROCEDURE total_age_of_animal(IN p_animal_id INT, INOUT p_age datetime)
BEGIN
    SELECT sum(concat('select TIMESTAMPDIFF(YEAR, dob, NOW()) from animal')) INTO p_age
    FROM animal
    INNER JOIN species ON species.id = animal.species_id
    LEFT JOIN race ON race.id = animal.race_id
    WHERE animal.id = p_animal_id;
   
    set @age = concat('select TIMESTAMPDIFF(YEAR, dob, NOW()) from animal');
prepare age
from @age;
execute age;
   
END |

select @age|

set @age = concat('select TIMESTAMPDIFF(YEAR, dob, NOW()) from animal')|


CALL total_age_of_animal (13, age)|
SELECT age AS first_total|

CALL total_age_of_animal (24, @age)|
SELECT @age AS second_total|

CALL total_age_of_animal (42, @age)|
SELECT @age AS third_total|

CALL total_age_of_animal (54, @age)|
SELECT @age AS fourth_total|

CALL total_age_of_animal (62, @age)|
SELECT @age AS grand_total|



CREATE PROCEDURE age_tot(IN a_id1 INT, IN a_id2 INT, IN a_id3 INT, IN a_id4 INT, IN a_id5 INT, OUT sum_age INT)
BEGIN
    SELECT id,TIMESTAMPDIFF(YEAR, dob, NOW()) AS Age, sum(TIMESTAMPDIFF(YEAR, dob, NOW())) as tot into sum_age
    FROM animal
    where a_id1 = @a_id1 or a_id2 = @a_id2 or a_id3 = @a_id3 or a_id4 = @a_id4 or a_id5 = @a_id5 and sum_age = @sum_age;
END|

set @a_id1 = 1, @a_id2 = 2, @a_id3 = 3, @a_id4 = 4, @a_id5 = 5|
set sum_age = 'select sum(TIMESTAMPDIFF(YEAR, dob, NOW())) from animal'|
call age_tot (1,2,3,5,6,@sum_age)|

drop procedure age_tot|

create procedure addn(@a float, @b float)
as
begin
declare @sum varchar(4)
set @sum=cast((@a+@b) as VARCHAR)
print 'the sum of '+cast(@a as varchar) + ' and ' + cast(@b as varchar) + ' is ' + cast((@a+@b) as VARCHAR)
print @sum
END|



CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))
BEGIN
 SET count = count + inc;
END|

SET @counter = 1|
CALL set_counter(@counter,1)| -- 2
CALL set_counter(@counter,1)| -- 3
CALL set_counter(@counter,5)| -- 8
SELECT @counter| -- 8

drop procedure set_counter|


CREATE PROCEDURE sum_of_two(IN num1 INT,IN num2 INT,OUT sot INT)
BEGIN
    SET sot := num1 + num2;
END|

-- Calling procedure here
CALL sum_of_two(2,4,@output)|

-- Checking or getting output value
SELECT @output|



/* 5. STORE PROCEDURE: top_x_animals
IN: How many animals to display
Display a list of x number of animals for the following:
-Youngest
-Oldest
-Cheapest
-Most Expensive
-Male
-Female
*/

CREATE PROCEDURE top_x_animals (IN n INT)
BEGIN

set @limit = n;

SET @youngest := 'SELECT dob FROM animal order by dob desc limit ?';
PREPARE youngest
FROM @youngest;

SET @female = 'SELECT sex FROM animal where sex = "F" limit ?';
PREPARE female
FROM @female;
       
        SET @oldest = 'SELECT dob FROM animal order by dob asc limit ?';
PREPARE oldest
FROM @oldest;
       
        SET @cheapest = 'SELECT price FROM species order by price desc limit ?';
PREPARE cheapest
FROM @cheapest;
       
        SET @MostExpensive = 'SELECT price FROM species order by price asc limit ?';
PREPARE MostExpensive
FROM @MostExpensive;
       
        SET @male = 'SELECT sex FROM animal where sex = "M" limit ?';
PREPARE male
FROM @male;
       
        execute youngest using @limit;
        execute oldest using @limit;
        execute cheapest using @limit;
        execute MostExpensive using @limit;
        execute male using @limit;
        execute female using @limit;


SET @x = 1; 
REPEAT 
IF mod(@x, 2) = 0
THEN 
SET @sum = @sum + @x; 
END IF; 
SET @x = @x + 1; 
UNTIL @x > n
END REPEAT;
END|

call top_x_animals(3)|

drop procedure top_x_animals|

CREATE PROCEDURE total_age(
   in a1 int,
   in a2 int,
   in a3 int,
   in a4 int,
   in a5 int,
   inOUT ta int
)
BEGIN
   SELECT TIMESTAMPDIFF(YEAR, dob, NOW()) AS Age,animal.id
   INTO a1
   FROM animal;
 
   SELECT TIMESTAMPDIFF(YEAR, dob, NOW()) AS Age,animal.id
   INTO a2
   FROM animal;
 
   SELECT TIMESTAMPDIFF(YEAR, dob, NOW()) AS Age,animal.id
   INTO a3
   FROM animal;
 
   SELECT TIMESTAMPDIFF(YEAR, dob, NOW()) AS Age,animal.id
   INTO a4
   FROM animal;
 
   SELECT TIMESTAMPDIFF(YEAR, dob, NOW()) AS Age,animal.id
   INTO a5
   FROM animal;
 
   SELECT sum
   INTO ph
   FROM products;
   SELECT Avg(prod_price)
   INTO pa
   FROM products;
END;