===== Grand National Sweepstake (PostgreSQL) =====

==== Create Database ====

<code sql>
CREATE DATABASE GrandNational;
</code>

==== Create tables and insert data ====

<code sql>
-- Lists the entrants in the sweepstake
Drop table participants;
Create table if not exists participants (ID serial, 
                                         Name varchar(20) PRIMARY KEY NOT NULL CHECK (length(Name) > 0));
insert into participants (name) values ('Ian');
insert into participants (name) values ('Pat');
insert into participants (name) values ('Graham');
insert into participants (name) values ('Tracy');
insert into participants (name) values ('Michaela');
insert into participants (name) values ('Glenn');

-- List of horses entered into the Grand National
Drop table horses;
Create table if not exists horses (HorseNo smallint PRIMARY KEY NOT NULL CHECK (HorseNo between 1 and 40), 
                                   Name varchar(40) UNIQUE);
insert into horses values (1,'Bristol De Mai');
insert into horses values (2,'Chris Dream');
insert into horses values (3,'Yala Enki');
insert into horses values (4,'Ballyoptic');
insert into horses values (5,'Definitly Red');
insert into horses values (6,'Lake View Lad');
insert into horses values (7,'Burrows Saint');
insert into horses values (8,'Magic Of Light');
insert into horses values (9,'Acapella Bourgeois');
insert into horses values (10,'Talkischeap');
insert into horses values (11,'Tout Est Permis');
insert into horses values (12,'Anibale Fly');
insert into horses values (13,'Mister Malarky');
insert into horses values (14,'Kimberlite Candy');
insert into horses values (15,'Any Second Now');
insert into horses values (16,'Balko Des Flos');
insert into horses values (17,'Alpha Des Obeaux');
insert into horses values (18,'OK Corral');
insert into horses values (19,'Takingrisks');
insert into horses values (20,'Shattered Love');
insert into horses values (21,'Jett');
insert into horses values (22,'Lord Du Mesnil');
insert into horses values (23,'Potters Corner');
insert into horses values (24,'Class Conti');
insert into horses values (25,'Milan Native');
insert into horses values (26,'Discorama');
insert into horses values (27,'Vieux Lion Rouge');
insert into horses values (28,'Cloth Cap');
insert into horses values (29,'Caberet Queen');
insert into horses values (30,'Minellacelebration');
insert into horses values (31,'Canelo');
insert into horses values (32,'The Long Mile');
insert into horses values (33,'Give Me A Copper');
insert into horses values (34,'Farclas');
insert into horses values (35,'Minella Times');
insert into horses values (36,'Sub Lieutenant');
insert into horses values (37,'Hogans Height');
insert into horses values (38,'Double Shuffle');
insert into horses values (39,'Ami Desbois');
insert into horses values (40,'Blaklion');

-- Lists the entrants and the horses that they got
drop table sweepstake;
create table if not exists sweepstake (PlayerID smallint, 
                                       HorseNo smallint PRIMARY KEY NOT NULL CHECK (HorseNo between 1 and 40), 
			               HorseName varchar(40) UNIQUE);
</code>

==== Create procedures ====
<code plsql>
-- Initialise the SWEEPSTAKE table
-- PostgreSQL Version 11 and Above
create or replace procedure init()
LANGUAGE SQL 
as $$
 delete from sweepstake;
 insert into sweepstake (HorseNo, HorseName) (select horseno, name from horses);
$$;

-- PostgreSQL Version 10 and Below
create or replace function init()
RETURNS void AS $$
BEGIN
 delete from sweepstake;
 insert into sweepstake (HorseNo, HorseName) (select horseno, name from horses);
END;
$$ LANGUAGE plpgsql;

-- Performs one round of the draw.
create or replace function OneCycle()
RETURNS void AS $$
DECLARE
  C1 CURSOR FOR select PlayerID, PlayerName, horseno, HorseName from (
	          select row_number() over (order by random() ASC) row_num,
	                 HorseNo, HorseName from sweepstake where playerid is null) as T1,
	         (select id PlayerID, name PlayerName from participants) as T2
	        where row_num = PlayerID;
BEGIN
  FOR a IN C1
  LOOP
    Update sweepstake set PlayerID = a.PlayerID where HorseNo = a.HorseNo;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Generate the sweepstake data
create or replace function GenSweepstake()
RETURNS void AS $$
DECLARE
  C1 CURSOR FOR select name, horseno, horsename
                from participants, sweepstake
                where id = playerid order by horseno;
  NoParticipants real;
  NoHorses       real;
  NoCycles       smallint;
BEGIN
  select count(*) into NoParticipants from participants;
  select count(*) into NoHorses from horses;
  NoCycles := ceil(NoHorses/NoParticipants);
    perform init();
FOR a IN 1..NoCycles
LOOP
  perform OneCycle();
END LOOP;
END;
$$ LANGUAGE plpgsql;
</code>

==== Create Views ====

<code sql>
create or replace view v_sweepstake as
       select name, horseno, horsename
       from participants, sweepstake
       where id = playerid 
       order by horseno;
	   
create or replace view v_check as
       select name, count(name) No_Horses from v_sweepstake
       group by name
       order by 2 DESC;
</code>

==== Generate Sweepstake ====

<code plsql>
SELECT GenSweepstake;
</code>