procedures_solution.sql

procedures_solution.sql

use test;

drop table if exists words;

create table words (id int primary key auto_increment, adjective varchar(100) not null, noun varchar(100) not null);
insert into words (adjective, noun) values
("hot", "dog"),
("fast", "banana"),
("orange", "cloud"),
("charming", "curtain"),
("distinguished", "gentleman"),
("standing", "desk"),
("reasonable", "person"),
("white", "snow"),
("black", "mud"),
("green", "box");

-- select * from words;

-- Write a procedure that returns data in two "out" variables. One will contain
-- all the nouns joined with commas, the other will contain all the adjectives
-- joined by commas.

-- Optional: modify the procedure so that there is no stray extra comma before or after
-- the start of the lists.

delimiter $$

drop procedure if exists joinWords$$

create procedure joinWords(inout nouns varchar(1000), inout adjectives varchar(1000))
begin
    declare cur_noun varchar(100);
    declare cur_adj varchar(100);
    declare finished boolean default false;

    declare cur cursor for select adjective, noun from words;
    declare continue handler for not found set finished = true;

    open cur;

    the_loop: loop
        fetch cur into cur_adj, cur_noun;
        if finished then
            leave the_loop;
        end if;

        if nouns = "" then
            set nouns = cur_noun;
        else
            set nouns = concat(nouns, ", ", cur_noun);
        end if;

        if adjectives = "" then
            set adjectives = cur_adj;
        else
            set adjectives = concat(adjectives, ", ", cur_adj);
        end if;
    end loop;

    close cur;
end$$

delimiter ;

set @the_nouns = "";
set @the_adjectives = "";

call joinWords(@the_nouns, @the_adjectives);

select @the_nouns, @the_adjectives;

-- 2. Write a stored procedure that creates a table called "stars" containing a column of type text.
-- Make the procedure populate the table by selecting an adjective at random and
-- a noun at random and joining them together with a space between them.

-- Hot Cloud
-- Charming Dog
-- etc.

-- Optional: upper case the first letters of the two words.

-- 3. Modify this procedure so that it create the table with a birth date and death date
-- column. Make the procedure fill in the birth and death dates in the following way.

-- The birth date should be random between 20 and 80 years ago.
-- The death date should be random between 19 years after the birth date and today.

-- 4. Modify this procedure so that the death date is only filled in 40% of the time, at random.

drop table if exists stars;

create table stars (
    id int primary key auto_increment,
    name text,
    birth date,
    death date
);

delimiter $$

drop procedure if exists createStars$$

create procedure createStars()
not deterministic
begin
    declare the_noun varchar(100);
    declare the_adj varchar(100);
    declare star text;

    declare the_birth date;
    declare the_death date;
    declare min_died date;
    declare max_died date;
    declare days_timespan int;

    declare finished boolean default false;

    declare curs_noun cursor for select noun from words order by rand();
    declare curs_adj cursor for select adjective from words order by rand();
    declare continue handler for not found set finished = true;

    open curs_noun;
    open curs_adj;

    the_loop: loop
        fetch curs_noun into the_noun;
        fetch curs_adj into the_adj;
        if finished then
            leave the_loop;
        end if;

        set the_adj = concat(ucase(left(the_adj, 1)), substring(the_adj, 2));
        set the_noun = concat(ucase(left(the_noun, 1)), substring(the_noun, 2));

        select date(now() - interval 20 year - interval rand() * 365 * 60 day) into the_birth;

        set the_death = null;
        if rand() <= 0.4 then
            select the_birth + interval 19 year into min_died;
            select date(now()) into max_died;
            select datediff(max_died, min_died) * rand() into days_timespan;
            select the_birth + interval 19 year + interval days_timespan day into the_death;
        end if;

        insert into stars (name, birth, death) values (
            concat(the_adj, " ", the_noun),
            the_birth,
            the_death
        );
    end loop;

    close curs_noun;
    close curs_adj;
end$$

delimiter ;

call createStars();

select id, name, birth, death, from_days(datediff(death, birth)) from stars;