--
-- ### Gate B
-- 
--


create table kids ( primary kid int not null, forename string(30), gender string(10), birthday datetime, gid int);
create table kidtoys ( primary tid int not null, toyname string(30));

create table toy2kid ( primary tid int not null, primary kid int not null);

create table kidgroup ( primary gid int not null, groupname string(20));

create view kidview as select k.forename as forename, kg.groupname as groupname from kids k, kidgroup kg where k.gid = kg.gid;

create view kidtoyview as select k.forename as forename, kt.toyname as toyname from kids k, kidtoys kt, toy2kid t2k
       where k.kid = t2k.kid and t2k.tid = kt.tid;


insert into kidgroup values ( 1, 'LittleGeeks');
insert into kidgroup values ( 2, 'FunnyBunny');

insert into kids values ( 1, 'hugo' , 'boy', date('%d.%m.%Y %H:%M:%S', '12.01.2001 07:53:12'), 1);
insert into kids values ( 2, 'john', 'boy', date('%d.%m.%Y %H:%M:%S', '01.07.2002 07:53:12'), 1);
insert into kids values ( 3, 'kuno', 'boy', date('%d.%m.%Y %H:%M:%S', '19.03.2003 07:53:12'), 1);
insert into kids values ( 4, 'peter', 'boy', date('%d.%m.%Y %H:%M:%S', '23.05.2001 07:53:12'), 1);
insert into kids values ( 5, 'karl', 'boy', date('%d.%m.%Y %H:%M:%S', '25.07.2005 07:53:12'), 2);
insert into kids values ( 6, 'dieter', 'boy', date('%d.%m.%Y %H:%M:%S', '09.08.2003 07:53:12'), 2);
insert into kids values ( 7, 'iwan', 'boy', date('%d.%m.%Y %H:%M:%S', '12.03.2002 07:53:12'), 2);
insert into kids values ( 8, 'eugen', 'boy', date('%d.%m.%Y %H:%M:%S', '11.05.2001 07:53:12'), 2);
insert into kids values ( 9, 'norbert', 'boy', date('%d.%m.%Y %H:%M:%S', '14.08.2002 07:53:12'), 2);
insert into kids values ( 10, 'rudi', 'boy', date('%d.%m.%Y %H:%M:%S', '04.07.2005 07:53:12'), 2);
insert into kids values ( 11, 'detlev', 'boy', date('%d.%m.%Y %H:%M:%S', '02.01.2007 07:53:12'), 2);
insert into kids values ( 12, 'frank', 'boy', date('%d.%m.%Y %H:%M:%S', '12.02.2005 07:53:12'), 2);
insert into kids values ( 13, 'anna', 'girl', date('%d.%m.%Y %H:%M:%S', '14.02.2003 07:53:12'), 1);
insert into kids values ( 14, 'berta', 'girl', date('%d.%m.%Y %H:%M:%S', '14.08.2002 07:53:12'), 1);
insert into kids values ( 15, 'olga', 'girl', date('%d.%m.%Y %H:%M:%S', '14.12.2003 07:53:12'), 2);
insert into kids values ( 16, 'sveta', 'girl', date('%d.%m.%Y %H:%M:%S', '14.11.2004 07:53:12'), 2);
insert into kids values ( 17, 'maria', 'girl', date('%d.%m.%Y %H:%M:%S', '12.01.2002 07:53:12'), 2);
insert into kids values ( 18, 'alma', 'girl', date('%d.%m.%Y %H:%M:%S', '01.02.2008 07:53:12'), 2);


insert into kidtoys values ( 1, 'car');
insert into kidtoys values ( 2, 'train');
insert into kidtoys values ( 3, 'brick');
insert into kidtoys values ( 4, 'skateboard');
insert into kidtoys values ( 5, 'ball');


insert into toy2kid values ( 1, 1);
insert into toy2kid values ( 1, 3);
insert into toy2kid values ( 2, 6);
insert into toy2kid values ( 2, 12);
insert into toy2kid values ( 2, 7);
insert into toy2kid values ( 3, 11);
insert into toy2kid values ( 3, 10);
insert into toy2kid values ( 3, 1);
insert into toy2kid values ( 4, 10);
insert into toy2kid values ( 5, 9);

insert into toy2kid values ( 1, 13);
insert into toy2kid values ( 2, 13);
insert into toy2kid values ( 3, 13);
insert into toy2kid values ( 4, 13);
insert into toy2kid values ( 5, 13);

@
create procedure checkSelect001(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var i int;
	
	cursor xCur as select count(*) from kids where kid > 5;
        if fetch xCur into ( :i ) = true
        then 
	     if :i = 13
	     then
		:res = 'ok';
	     end;
	end;
	close xCur;

	:msg = 'Number of kids where kid > 5';

	return :res;
end;
@

@
create procedure checkSelect002(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var i int;
	
	cursor xCur as select count(*) from kids where kid >= 5;
        if fetch xCur into ( :i ) = true
        then 
	     if :i = 14
	     then
		:res = 'ok';
	     end;
	end;
	close xCur;

	:msg = 'Number of kids where kid >= 5';

	return :res;
end;
@


@
create procedure checkSelect003(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var f string(10);

	cursor xCur as 
	select forename from kids where forename in ( select forename from kidview where groupname = 'LittleGeeks' ) 
	       and gender = 'boy'  or ( date2str(birthday, '%Y') <  '2001' and date2str(birthday, '%m') = '07' );

        if fetch xCur into ( :f ) = true
        then 
	      if :f = 'john'
	      then
	 	:res = 'ok';
	      end;
	end;
	close xCur;

	:msg = 'Kid with special birtday';

	return :res;
end;
@


@
create procedure checkSelect004(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var g1 string(10);
	var g2 string(10);
	var i1 int;
	var i2 int;

	var b bool;

	--
	-- select kids grouped by gender
	--

	cursor xCur as 
	select gender, count(*) from kids group by gender order by gender;
        :b = fetch xCur into ( :g1, :i1 );
        :b = fetch xCur into ( :g2, :i2 );
	close xCur;

	if :g1 = 'boy' and :i1 = 12 and :g2 = 'girl' and :i2 = 6
	then
		:res = 'ok';
	end;

	:msg = 'Grouped kids order by gender';	

	return :res;
end;
@


@
create procedure checkSelect005(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var g1 string(10);
	var g2 string(10);
	var i1 int;
	var i2 int;

	var b bool;

	--
	-- select kids grouped by gender
	--

	cursor xCur as 
	select gender, count(*) from kids group by gender order by count(*) asc;
        :b = fetch xCur into ( :g1, :i1 );
        :b = fetch xCur into ( :g2, :i2 );
	close xCur;

	if :g2 = 'boy' and :i2 = 12 and :g1 = 'girl' and :i1 = 6
	then
		:res = 'ok';
	end;

	:msg = 'Grouped kids order by count(*)';	

	return :res;
end;
@


@
create procedure checkSelect006(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var fn string(10);

	var b bool;

	--
	-- select kids who have all toys  ( allquantor test )
	--

	cursor xCur as 
	select forename from kids k where not exists 
	       	( select * from kidtoys kt where not exists 
	             ( select * from toy2kid t2k where k.kid = t2k.kid and t2k.tid = kt.tid ));


        :b = fetch xCur into ( :fn );

	close xCur;

	if :fn = 'anna' 
	then
		:res = 'ok';
	end;

	:msg = 'Kids with all toys';	
	
	return :res;
end;
@

@
create procedure checkSelect007(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var fn string(10);

	var b bool;

	--
	-- select kids who are girls and name contains 'nn'
	--

	cursor xCur as 
	select forename from kids k where gender = 'girl' and lower(forename) like '%nn%';

        :b = fetch xCur into ( :fn );

	close xCur;

	if :fn = 'anna' 
	then
		:res = 'ok';
	end;

	:msg = 'Girls with name containing nn lc or uc';	
	
	return :res;
end;
@


:r = call checkSelect001(:msg);
insert into checklog values ('B', :msg, :r);
:r = call checkSelect002(:msg);
insert into checklog values ('B', :msg, :r);
:r = call checkSelect003(:msg);
insert into checklog values ('B', :msg, :r);
:r = call checkSelect004(:msg);
insert into checklog values ('B', :msg, :r);
:r = call checkSelect005(:msg);
insert into checklog values ('B', :msg, :r);
:r = call checkSelect006(:msg);
insert into checklog values ('B', :msg, :r);
:r = call checkSelect007(:msg);
insert into checklog values ('B', :msg, :r);


drop procedure checkSelect001;
drop procedure checkSelect002;
drop procedure checkSelect003;
drop procedure checkSelect004;
drop procedure checkSelect005;
drop procedure checkSelect006;
drop procedure checkSelect007;

drop view kidview;
drop view kidtoyview;
drop table kids;
drop table kidtoys;
drop table kidgroup;
drop table toy2kid;

