Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE MatchupEntries(
- id_matchupEntries NUMBER(5) PRIMARY KEY,
- id_matchup NUMBER(5) NOT NULL,
- id_ParentMatchup NUMBER(5) NULL,
- id_TeamCompeting NUMBER(5) NULL,
- Score NUMBER(5,2) NULL
- );
- CREATE TABLE Matchups(
- id_Matchups NUMBER(5) NOT NULL PRIMARY KEY,
- id_Tournament NUMBER(5) NOT NULL,
- id_Winner NUMBER(5) NULL,
- MatchupRound NUMBER(5) NOT NULL
- );
- CREATE TABLE People(
- id_Person NUMBER(5) NOT NULL PRIMARY KEY,
- FirstName VARCHAR2(20) NOT NULL,
- LastName VARCHAR2(20) NOT NULL,
- Email VARCHAR2(50) NOT NULL,
- PhoneNr VARCHAR2(20) NOT NULL
- );
- CREATE TABLE Prizes(
- id_Prizes NUMBER(5) NOT NULL PRIMARY KEY,
- PlaceNumber NUMBER(5) NOT NULL,
- PlaceName VARCHAR2(20) NOT NULL,
- PrizeAmount NUMBER(20) NOT NULL,
- PrizePercentage NUMBER(20) NOT NULL
- );
- CREATE TABLE TeamMembers(
- id_teamMembers NUMBER(5) NOT NULL PRIMARY KEY,
- id_team NUMBER(5) NOT NULL,
- id_person NUMBER(5) NOT NULL
- );
- CREATE TABLE Teams (
- id_teams NUMBER(5) NOT NULL primary key,
- teamName VARCHAR2(200) NOT NULL
- );
- CREATE TABLE TournamentEntries(
- id_Entries NUMBER(5) not null primary key,
- id_tournament NUMBER(5) NOT NULL,
- id_team NUMBER(5) NOT NULL
- );
- CREATE TABLE TournamentPrizes(
- id_TournamentPrizes NUMBER(5) not null primary key,
- id_Tournament NUMBER(5) NOT NULL,
- id_Prize NUMBER(5) not null
- );
- CREATE TABLE Tournaments(
- id_tournaments NUMBER(5) not null primary key,
- TournamentName VARCHAR2(200) NOT NULL,
- EntryFee NUMBER(5) NOT NULL,
- Active number(1) NOT NULL
- );
- ALTER TABLE MatchupEntries
- ADD FOREIGN KEY (id_matchup) REFERENCES
- Matchups (id_Matchups);
- ALTER TABLE MatchupEntries
- ADD FOREIGN KEY (id_TeamCompeting) REFERENCES
- Teams (id_teams);
- ALTER TABLE Matchups
- ADD FOREIGN KEY (id_Tournament) REFERENCES
- Tournaments (id_tournaments);
- ALTER TABLE TeamMembers
- ADD FOREIGN KEY (id_team) REFERENCES
- Teams (id_teams);
- ALTER TABLE TeamMembers
- ADD FOREIGN KEY (id_person) REFERENCES
- People (id_person);
- ALTER TABLE TournamentEntries
- ADD Foreign key (id_tournament) REFERENCES
- Tournaments (id_tournaments);
- ALTER TABLE TournamentEntries
- ADD Foreign key (id_team) REFERENCES
- Teams (id_teams);
- ALTER TABLE TournamentPrizes
- ADD Foreign key (id_tournament) REFERENCES
- Tournaments (id_tournaments);
- ALTER TABLE TournamentPrizes
- ADD Foreign key (id_Prize) REFERENCES
- Prizes (id_prizes);
- --INSERT
- INSERT INTO Tournaments VALUES(1,'Turniej', 50,1);
- INSERT INTO Tournaments VALUES(2,'Turniej', 100,1);
- INSERT INTO Tournaments VALUES(3,'Turniej', 0,0);
- INSERT INTO Tournaments VALUES(4,'Turniej', 0,0);
- INSERT INTO Tournaments VALUES(5,'Turniej', 0,0);
- INSERT INTO Tournaments VALUES(6,'Turniej', 0,0);
- INSERT INTO Tournaments VALUES(7,'Turniej', 0,0);
- INSERT INTO Tournaments VALUES(8,'Turniej', 0,0);
- INSERT INTO Tournaments VALUES(9,'Turniej', 0,0);
- INSERT INTO Tournaments VALUES(10,'Turniej', 0,0);
- INSERT INTO Tournaments VALUES(11,'Turniej', 0,0);
- INSERT INTO Tournaments VALUES(12,'Turniej', 0,0);
- INSERT INTO Tournaments VALUES(13,'Turniej', 0,0);
- INSERT INTO Tournaments VALUES(14,'Turniej', 0,0);
- INSERT INTO Tournaments VALUES(15,'Turniej', 0,0);
- INSERT INTO Prizes VALUES(1,1,'winner',150,0);
- INSERT INTO Prizes VALUES(2,2,'2nd place',50,0);
- INSERT INTO Prizes VALUES(3,1,'champion',300,0);
- INSERT INTO Prizes VALUES(4,2,'silver',100,0);
- INSERT INTO Prizes VALUES(5,1,'win',100,0);
- INSERT INTO Prizes VALUES(6,1,'win',100,0);
- INSERT INTO Prizes VALUES(7,1,'win',100,0);
- INSERT INTO Prizes VALUES(8,1,'win',100,0);
- INSERT INTO Prizes VALUES(9,1,'win',100,0);
- INSERT INTO Prizes VALUES(10,1,'win',100,0);
- INSERT INTO Prizes VALUES(11,1,'win',100,0);
- INSERT INTO Prizes VALUES(12,1,'win',100,0);
- INSERT INTO Prizes VALUES(13,1,'win',100,0);
- INSERT INTO Prizes VALUES(14,1,'win',100,0);
- INSERT INTO Prizes VALUES(15,1,'win',100,0);
- INSERT INTO TournamentPrizes VALUES(1,1,1);
- INSERT INTO TournamentPrizes VALUES(2,1,2);
- INSERT INTO TournamentPrizes VALUES(3,1,1);
- INSERT INTO TournamentPrizes VALUES(4,1,2);
- INSERT INTO TournamentPrizes VALUES(5,3,3);
- INSERT INTO TournamentPrizes VALUES(6,4,4);
- INSERT INTO TournamentPrizes VALUES(7,5,5);
- INSERT INTO TournamentPrizes VALUES(8,6,6);
- INSERT INTO TournamentPrizes VALUES(9,7,7);
- INSERT INTO TournamentPrizes VALUES(10,8,8);
- INSERT INTO TournamentPrizes VALUES(11,9,9);
- INSERT INTO TournamentPrizes VALUES(12,10,10);
- INSERT INTO TournamentPrizes VALUES(13,11,11);
- INSERT INTO TournamentPrizes VALUES(14,12,12);
- INSERT INTO TournamentPrizes VALUES(15,13,13);
- INSERT INTO People VALUES(1,'Anna','Ciosk','email1@gmail.com','81050854796');
- INSERT INTO People VALUES(2,'Krzysztof','Kowalski','email2@gmail.com','73082823846');
- INSERT INTO People VALUES(3,'Mariusz','Piotrowski','email3@gmail.com','69121868745');
- INSERT INTO People VALUES(4,'Tadeusz','Maliniak','email4@gmail.com','78100864875');
- INSERT INTO People VALUES(5,'Karol','Wojciechowski','email5@gmail.com','59010565847');
- INSERT INTO People VALUES(6,'Maciej','Radecki','email6@gmail.com','82112554796');
- INSERT INTO People VALUES(7,'Paweł','Gruszczyński','email7@gmail.com','80031854796');
- INSERT INTO People VALUES(8,'Paweł','Laprus','email8@gmail.com','75101979621');
- INSERT INTO People VALUES(9,'Robert','Słoma','email9@gmail.com','64072959621');
- INSERT INTO People VALUES(10,'Kamila','Baran','email10@gmail.com','74062979621');
- INSERT INTO People VALUES(11,'Albert','Drozdowski','email11@gmail.com','80090156812');
- INSERT INTO People VALUES(12,'Henryk','Bista','email12@gmail.com','77040368154');
- INSERT INTO People VALUES(13,'Marek','Makuszyński','email13@gmail.com','79010187769');
- INSERT INTO People VALUES(14,'Zbigniew','Michta','email14@gmail.com','83060650012');
- INSERT INTO People VALUES(15,'Katarzyna','Michalska','email15@gmail.com','55111597532');
- INSERT INTO Teams VALUES(1,'team1');
- INSERT INTO Teams VALUES(2,'team2');
- INSERT INTO Teams VALUES(3,'team3');
- INSERT INTO Teams VALUES(4,'team4');
- INSERT INTO Teams VALUES(5,'team5');
- INSERT INTO Teams VALUES(6,'team6');
- INSERT INTO Teams VALUES(7,'team7');
- INSERT INTO Teams VALUES(8,'team8');
- INSERT INTO Teams VALUES(9,'team9');
- INSERT INTO Teams VALUES(10,'team10');
- INSERT INTO Teams VALUES(11,'team11');
- INSERT INTO Teams VALUES(12,'team12');
- INSERT INTO Teams VALUES(13,'team13');
- INSERT INTO Teams VALUES(14,'team14');
- INSERT INTO Teams VALUES(15,'team15');
- INSERT INTO TeamMembers VALUES(1,1,1);
- INSERT INTO TeamMembers VALUES(2,1,2);
- INSERT INTO TeamMembers VALUES(3,2,3);
- INSERT INTO TeamMembers VALUES(4,2,4);
- INSERT INTO TeamMembers VALUES(5,3,5);
- INSERT INTO TeamMembers VALUES(6,3,6);
- INSERT INTO TeamMembers VALUES(7,4,7);
- INSERT INTO TeamMembers VALUES(8,4,8);
- INSERT INTO TeamMembers VALUES(9,5,9);
- INSERT INTO TeamMembers VALUES(10,6,10);
- INSERT INTO TeamMembers VALUES(11,7,11);
- INSERT INTO TeamMembers VALUES(12,8,12);
- INSERT INTO TeamMembers VALUES(13,9,13);
- INSERT INTO TeamMembers VALUES(14,10,14);
- INSERT INTO TeamMembers VALUES(15,11,15);
- INSERT INTO TournamentEntries VALUES(1,1,1);
- INSERT INTO TournamentEntries VALUES(2,1,2);
- INSERT INTO TournamentEntries VALUES(3,1,3);
- INSERT INTO TournamentEntries VALUES(4,1,4);
- INSERT INTO TournamentEntries VALUES(5,2,5);
- INSERT INTO TournamentEntries VALUES(6,2,6);
- INSERT INTO TournamentEntries VALUES(7,2,7);
- INSERT INTO TournamentEntries VALUES(8,2,8);
- INSERT INTO TournamentEntries VALUES(9,3,9);
- INSERT INTO TournamentEntries VALUES(10,3,10);
- INSERT INTO TournamentEntries VALUES(11,3,11);
- INSERT INTO TournamentEntries VALUES(12,3,12);
- INSERT INTO TournamentEntries VALUES(13,4,13);
- INSERT INTO TournamentEntries VALUES(14,4,14);
- INSERT INTO TournamentEntries VALUES(15,4,15);
- INSERT INTO Matchups VALUES(1,1,1,1);
- INSERT INTO Matchups VALUES(2,1,3,1);
- INSERT INTO Matchups VALUES(3,1,1,2);
- INSERT INTO Matchups VALUES(4,2,5,1);
- INSERT INTO Matchups VALUES(5,2,8,1);
- INSERT INTO Matchups VALUES(6,2,5,2);
- INSERT INTO Matchups VALUES(7,3,NULL,1);
- INSERT INTO Matchups VALUES(8,4,NULL,1);
- INSERT INTO Matchups VALUES(9,5,NULL,1);
- INSERT INTO Matchups VALUES(10,6,NULL,1);
- INSERT INTO Matchups VALUES(11,7,NULL,1);
- INSERT INTO Matchups VALUES(12,8,NULL,1);
- INSERT INTO Matchups VALUES(13,9,NULL,1);
- INSERT INTO Matchups VALUES(14,10,NULL,1);
- INSERT INTO Matchups VALUES(15,11,NULL,1);
- INSERT INTO MatchupEntries VALUES(1,1,NULL,1,100);
- INSERT INTO MatchupEntries VALUES(2,1,NULL,2,50);
- INSERT INTO MatchupEntries VALUES(3,2,NULL,3,100);
- INSERT INTO MatchupEntries VALUES(4,2,NULL,4,50);
- INSERT INTO MatchupEntries VALUES(5,3,1,1,100);
- INSERT INTO MatchupEntries VALUES(6,3,2,3,50);
- INSERT INTO MatchupEntries VALUES(7,4,NULL,5,100);
- INSERT INTO MatchupEntries VALUES(8,4,NULL,6,50);
- INSERT INTO MatchupEntries VALUES(9,5,NULL,7,50);
- INSERT INTO MatchupEntries VALUES(10,5,NULL,8,100);
- INSERT INTO MatchupEntries VALUES(11,6,4,5,100);
- INSERT INTO MatchupEntries VALUES(12,6,5,8,50);
- INSERT INTO MatchupEntries VALUES(13,7,NULL,9,NULL);
- INSERT INTO MatchupEntries VALUES(14,7,NULL,10,NULL);
- INSERT INTO MatchupEntries VALUES(15,8,NULL,11,NULL);
- INSERT INTO MatchupEntries VALUES(16,8,NULL,12,NULL);
- --- Views
- Create View AS Teammates
Add Comment
Please, Sign In to add comment