===== Grand National Sweepstake (Oracle) =====

==== Create User ====

<code sql>
CREATE USER GRANDNATIONAL IDENTIFIED BY horses;
GRANT CREATE SESSION TO GRANDNATIONAL;
GRANT CREATE TABLE TO GRANDNATIONAL;
GRANT CREATE PROCEDURE TO GRANDNATIONAL;
GRANT CREATE VIEW TO GRANDNATIONAL;
GRANT CREATE SEQUENCE TO GRANDNATIONAL;
GRANT UNLIMITED TABLESPACE TO GRANDNATIONAL;
</code>

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

<code sql>
-- Lists the entrants in the sweepstake
Drop table participants;
Create table participants (ID number(2) generated by default on null as identity, 
                           Name varchar2(20), primary key(ID), 
		           constraint unique_name unique (name));
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 horses (HorseNo number(2), 
                     Name varchar2(40), primary key(HorseNo));
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 sweepstake (PlayerID number(2), 
                         HorseNo number(2), 
			 HorseName varchar2(40), primary key (HorseNo));
</code>

==== Create procedures ====
<code plsql>
-- Initialise the SWEEPSTAKE table
create or replace procedure init as
begin
  EXECUTE IMMEDIATE 'delete from sweepstake';
  EXECUTE IMMEDIATE 'insert into sweepstake (HorseNo, HorseName) (select horseno, name from horses)';
end;
/

-- Performs one round of the draw.
create or replace procedure OneCycle as
Cursor C1 is select PlayerID, PlayerName, horseno, HorseName from (
	       select row_number() over (order by dbms_random.value ASC) row_num,
	              HorseNo, HorseName from sweepstake where playerid is null),
	      (select id PlayerID, name PlayerName from participants)
	     where row_num = PlayerID;
BEGIN
FOR a IN C1
LOOP
  EXECUTE IMMEDIATE 'Update sweepstake set PlayerID = :pid where HorseNo = :horseno' 
                     using a.PlayerID, a.HorseNo;
END LOOP;
END;
/

-- Generate the sweepstake data
create or replace procedure GenSweepstake as
Cursor C1 is select name, horseno, horsename
             from participants, sweepstake
             where id = playerid order by horseno;
NoParticipants number(2);
NoHorses       number(3);
NoCycles       number(2);
begin
select count(*) into NoParticipants from participants;
select count(*) into NoHorses from horses;
NoCycles := ceil(NoHorses/NoParticipants);
  init;
FOR a IN 1..NoCycles
LOOP
  OneCycle;
END LOOP;

dbms_output.put_line(' ');
dbms_output.put_line('PLAYER    NUMBER  HORSE NAME');
dbms_output.put_line('------    ------  ------------------');
FOR b in C1
LOOP
   dbms_output.put_line(rpad(b.name, 10) || rpad(b.horseno, 8) || rpad(b.horsename, 30));
END LOOP;
end;
/
</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>
set serveroutput on
BEGIN
  GenSweepstake();
END;
/
</code>