Skip to content

Instantly share code, notes, and snippets.

@Flackus
Last active January 10, 2022 18:01
Show Gist options
  • Save Flackus/661549e2d22bd85db66adb528dea366a to your computer and use it in GitHub Desktop.
Save Flackus/661549e2d22bd85db66adb528dea366a to your computer and use it in GitHub Desktop.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment