rem http://www.wowwiki.com/Main_Page
rem 

create user wow identified by wow;
grant dba to wow;

connect wow/wow

purge recyclebin;

drop table character;
drop table quest;
drop table race;
drop table faction;
drop table class;
drop table zone;
drop table experience;
drop table skill_level;

create table faction
(
  faction varchar2(30)
);

create table quest
(
  quest varchar2(30),
  text varchar2(30),
  zone varchar2(30),
  experience number
);

create table class
(
  class varchar2(30)
);

create table race
(
  race varchar2(30),
  faction varchar2(30)
);

create table zone
(
  zone varchar2(30),
  low_level number,
  high_level number
);

create table character
(
  name varchar2(30),
  experience number,
  race varchar2(30),
  class varchar2(30),
  location varchar2(30)
);

create table experience
(
  char_level number,
  experience number
);

create table skill_level
(
  skill_level varchar2(30)
);

create unique index ixpk_faction on faction (faction);
create unique index ixpk_quest on quest (quest);
create unique index ixpk_class on class (class);
create unique index ixpk_race on race (race);
create unique index ixpk_zone on zone (name);
create unique index ixpk_character on character (name);
create unique index ixpk_experience on experience (char_level);
create unique index ixpk_skill_level on skill_level (skill_level);

alter table faction add primary key (faction);
alter table quest add primary key (quest);
alter table class add primary key (class);
alter table race add primary key (race);
alter table zone add primary key (zone);
alter table character add primary key (name);
alter table experience add primary key (char_level);
alter table skill_level add primary key (skill_level);

alter table character add (constraint fk_character_race foreign key (race) references race);
alter table character add (constraint fk_character_class foreign key (class) references class);
alter table character add (constraint fk_character_zone foreign key (name) references zone);
alter table race add (constraint fk_race_faction foreign key (faction) references faction);
alter table quest add (constraint fk_quest_zone foreign key (zone) references zone);

insert into faction values('Alliance');
insert into faction values('Horde');

insert into class values('Druid');
insert into class values('Hunter');
insert into class values('Mage');
insert into class values('Paladin');
insert into class values('Priest');
insert into class values('Rogue');
insert into class values('Shaman');
insert into class values('Warlock');
insert into class values('Warrior');

insert into race values('Human', 'Alliance');
insert into race values('Dwarf', 'Alliance');
insert into race values('Night Elf', 'Alliance');
insert into race values('Gnome', 'Alliance');
insert into race values('Draenei', 'Alliance');
insert into race values('Orc', 'Horde');
insert into race values('Forsaken', 'Horde');
insert into race values('Tauren', 'Horde');
insert into race values('Darkspear Troll', 'Horde');
insert into race values('Blood Elf', 'Horde');

insert into skill_level values('Apprentice');
insert into skill_level values('Journeyman');
insert into skill_level values('Expert');
insert into skill_level values('Artisan');
insert into skill_level values('Master');
rem Eastern Kingdoms
rem Human Stormwind
insert into zone values('Elwynn Forest', 1, 10);
insert into zone values('Westfall', 10, 20);
insert into zone values('Redridge Mountains', 15, 25);
insert into zone values('Duskwood', 18, 30);
insert into zone values('Stranglethorn Vale', 30, 45);
insert into zone values('Swamp of Sorrows', 35, 45);
rem Dwarf Ironforge
insert into zone values('Dun Morogh', 1, 10);
insert into zone values('Loch Modan', 10, 20);
insert into zone values('Wetlands', 20, 30);
insert into zone values('Arathi Highlands', 30, 40);
rem Undercity
insert into zone values('Tirisfal Glades', 1, 10);
insert into zone values('Silverpine Forest', 10, 20);
insert into zone values('Hillsbrad Foothills', 20, 30);
insert into zone values('Alterac Mountains', 30, 40);
rem Kalimdor 
rem Orgrimmar
insert into zone values('Durotar', 1, 10);
insert into zone values('The Barrens', 10, 25);
insert into zone values('Darksore', 10, 20);
insert into zone values('Ashenvale Forest', 18, 30);
insert into zone values('Dustwallow Marsh', 35, 45);
rem Thunderbluff

insert into zone values('Mulgore', 1, 10);

insert into character('Rhorlyn', 40, 'Human', 'Mage', 'Elwynn Forest');

insert into experience values(1, 400);
insert into experience values(2, 1300);
insert into experience values(3, 2700);
insert into experience values(4, 4800);
insert into experience values(5, 7600);
insert into experience values(6, 11200);
insert into experience values(7, 15700);
insert into experience values(8, 21100);
insert into experience values(9, 27600);
insert into experience values(10, 35200);
insert into experience values(11, 44000);
insert into experience values(12, 54100);
insert into experience values(13, 65500);
insert into experience values(14, 78400);
insert into experience values(15, 92800);
insert into experience values(16, 108800);
insert into experience values(17, 126500);
insert into experience values(18, 145900);
insert into experience values(19, 167200);
insert into experience values(20, 190400);
insert into experience values(21, 215600);
insert into experience values(22, 242900);
insert into experience values(23, 272300);
insert into experience values(24, 304000);
insert into experience values(25, 338000);
insert into experience values(26, 374400);
insert into experience values(27, 413300);
insert into experience values(28, 454700);
insert into experience values(29, 499000);
insert into experience values(30, 546400);
insert into experience values(31, 597200);
insert into experience values(32, 651900);
insert into experience values(33, 710500);
insert into experience values(34, 773300);
insert into experience values(35, 840300);
insert into experience values(36, 911900);
insert into experience values(37, 988000);
insert into experience values(38, 1068800);
insert into experience values(39, 1154500);
insert into experience values(40, 1245200);
insert into experience values(41, 1341000);
insert into experience values(42, 1442000);
insert into experience values(43, 1548300);
insert into experience values(44, 1660100);
insert into experience values(45, 1777500);
insert into experience values(46, 1900700);
insert into experience values(47, 2029800);
insert into experience values(48, 2164900);
insert into experience values(49, 2306100);
insert into experience values(50, 2453600);
insert into experience values(51, 2607500);
insert into experience values(52, 2767900);
insert into experience values(53, 2935000);
insert into experience values(54, 3108900);
insert into experience values(55, 3289700);
insert into experience values(56, 3477600);
insert into experience values(57, 3672600);
insert into experience values(58, 3874900);
insert into experience values(59, 4084700);
insert into experience values(60, 4940000);
insert into experience values(61, 5747000);
insert into experience values(62, 6144000);
insert into experience values(63, 6503000);
insert into experience values(64, 6823000);
insert into experience values(65, 7012000);
insert into experience values(66, 7341000);
insert into experience values(67, 7537000);
insert into experience values(68, 7689000);
insert into experience values(69, 7797000);
insert into experience values(70, 8150000);