DROP TABLE player CASCADE CONSTRAINTS;
DROP TABLE leader CASCADE CONSTRAINTS;
DROP TABLE team CASCADE CONSTRAINTS;
DROP TABLE game CASCADE CONSTRAINTS;
DROP TABLE board CASCADE CONSTRAINTS;
DROP TABLE ruleset CASCADE CONSTRAINTS;
DROP TABLE figure CASCADE CONSTRAINTS;
DROP TABLE event CASCADE CONSTRAINTS;
DROP TABLE related_to CASCADE CONSTRAINTS;
DROP VIEW view_example CASCADE CONSTRAINTS;
DROP TABLE team_log CASCADE CONSTRAINTS;

CREATE TABLE ruleset
(
	rule_id VARCHAR2(5) NOT NULL PRIMARY KEY,
	description VARCHAR(500) NOT NULL UNIQUE,
	author VARCHAR2(30) NOT NULL,
	goal VARCHAR(100) NOT NULL
);

CREATE TABLE game
(
	game_name VARCHAR2(30) NOT NULL PRIMARY KEY,
	game_type VARCHAR(15) NOT NULL,
	distributor VARCHAR(30) NOT NULL,
    rule_id VARCHAR2(5) NOT NULL UNIQUE,
	FOREIGN KEY(rule_id) REFERENCES ruleset(rule_id)
);

CREATE TABLE board
(
	board_id VARCHAR2(5) NOT NULL PRIMARY KEY,
	board_name VARCHAR2(30) NOT NULL,
	shape VARCHAR2(10) NOT NULL,
	field_number NUMBER,
    game_name VARCHAR2(30) NOT NULL UNIQUE,
	FOREIGN KEY(game_name) REFERENCES game(game_name)
);

CREATE TABLE event
(
	event_id VARCHAR2(5) NOT NULL PRIMARY KEY,
	event_name VARCHAR(30) NOT NULL,
	description VARCHAR2(300) NOT NULL UNIQUE
);

CREATE TABLE figure
(
	gamepcs_id VARCHAR2(5) NOT NULL PRIMARY KEY,
	gamepcs_name VARCHAR2(30) NOT NULL,
	color VARCHAR2(15) NOT NULL,
	shape VARCHAR2(20) NOT NULL,
    board_id VARCHAR2(5) NOT NULL,
	FOREIGN KEY(board_id) REFERENCES board(board_id)
);

CREATE TABLE related_to
(
    gamepcs_id VARCHAR2(5) NOT NULL,
    event_id VARCHAR2(5) NOT NULL,
	FOREIGN KEY(gamepcs_id) REFERENCES card(gamepcs_id),
	FOREIGN KEY(event_id) REFERENCES event(event_id),
    PRIMARY KEY(gamepcs_id, event_id)
);

CREATE TABLE leader
(
	leader_id VARCHAR2(5) NOT NULL PRIMARY KEY,
	leader_name VARCHAR2(30) NOT NULL,
	gender VARCHAR2(6) NOT NULL,
	age NUMBER
);

CREATE TABLE team
(
	team_name VARCHAR2(30) NOT NULL PRIMARY KEY,
	score NUMBER DEFAULT 0,
    leader_id VARCHAR2(5) NOT NULL UNIQUE,
    game_name VARCHAR2(30) NOT NULL,
    gamepcs_id VARCHAR2(5) NOT NULL,
	FOREIGN KEY(leader_id) REFERENCES leader(leader_id),
	FOREIGN KEY(game_name) REFERENCES game(game_name),
	FOREIGN KEY(gamepcs_id) REFERENCES figure(gamepcs_id)
);

CREATE TABLE player
(
	player_id VARCHAR2(5) NOT NULL PRIMARY KEY,
	name VARCHAR2(30) NOT NULL,
	gender VARCHAR2(6) NOT NULL,
	age NUMBER,
    team_name VARCHAR2(30) NOT NULL,
	FOREIGN KEY(team_name) REFERENCES team(team_name)
);

CREATE TABLE team_log 
(
    team_log_time TIMESTAMP PRIMARY KEY,
    team_name VARCHAR2(30),
    team_score NUMBER,
    FOREIGN KEY(team_name) REFERENCES team(team_name)
);

CREATE OR REPLACE TRIGGER team_log_trigger
    AFTER INSERT OR UPDATE
    ON team
    FOR EACH ROW
    BEGIN
        CASE
            WHEN INSERTING THEN INSERT INTO team_log VALUES(systimestamp, :NEW.team_name, :NEW.score);
            WHEN UPDATING THEN INSERT INTO team_log VALUES(systimestamp, :NEW.team_name, :NEW.score);
        END CASE;
    END;
INSERT INTO ruleset VALUES('R1002', 'Every teams figure starts from a special field, the start field. They alternate roll the dice, and step forward. At special fields
the teams can draw a card.', 'Steve Author', 'The first team, which reached the START field twice wins.');
INSERT INTO ruleset VALUES('R1003', 'The teams roll the dice, and which has rolled the highest value has the right to hug someone, and step forward. If a team
rolled the highest value twice, they have the right to draw a card.', 'Sophie Matrahill', 'The first team, which hugged all his friends win.');
INSERT INTO ruleset VALUES('R1004', 'Every team gets a duckling at the begenning. In each round every team thinks a number, and the team with the highest number
can give a lesson to their duckling. After four lessons the duckling can fly.', 'Edward Snowden', 'The first team which teaches his duckling to fly wins.');
INSERT INTO ruleset VALUES('R1005', 'Every team gets a bunny at the beggining. In each round every team throwsa figure at the table. Which is the closest to the field with number 12 can
comb their bunny or draw a card.', 'Philip Giraffe', 'After ten rounds the game is over. The team, which has the most fluffy bunny win the game.');
INSERT INTO ruleset VALUES('R1006', 'Every teams figure starts from a special field, the start field. They alternate roll the dice, and step forward. The teams have
to make money. The most common way is to steal. Everyone has 500000 euro for start. Most of the fields are related to plus or minus transactions. If you reach the start, you get 500 euro.', 'Chris Brown', 'The first team, whcih has 50000000 euros wins the game.');
INSERT INTO ruleset VALUES('R1007', 'Every team has to catch the squirrel. The catch-time is documented. As many seconds it takes to catch the squirrel, the team can go forward.', 'Kakasi Naruto', 'The first team, which reached the START field, and love the squirrel wins.');
INSERT INTO ruleset VALUES('R1008', 'The teams roll the dice, and which has rolled the highest value has the right to hug someone who is hated by him, and step forward. If a team
rolled the highest value twice, they have the right to draw a card.', 'Sophie Hatred', 'The first team, which hugged all his eneimies win.');
INSERT INTO ruleset VALUES('R1009', 'Every team has to sleep. After every 10 hours of sleeping they can move forward. By drawing cards, they can get more sleep pills.', 'Edward Snowden', 'The first team which slept 10000 hours in sum, and reached the last field wins.');
INSERT INTO ruleset VALUES('R1010', 'Every team gets a pig at the beggining. In each round every team throwsa figure at the table. Which is the closest to the field with number 12 can
make their pig dirtier or draw a card.', 'Philip Hoolank', 'After ten rounds the game is over. The team, which has the dirtiest pig win the game.');

INSERT INTO game VALUES('MiniMonopoly', 'strategy', 'Fat Horse Games Inc.', 'R1001');
INSERT INTO game VALUES('Horse Race', 'race', 'Pear Industries Inc.', 'R1002');
INSERT INTO game VALUES('Hug Your Friend', 'logic', 'Penguin Entertainment Inc.', 'R1003');
INSERT INTO game VALUES('The Duckling Rush', 'race', 'Fat Horse Games Inc.', 'R1004');
INSERT INTO game VALUES('Korean Bonnies', 'luck', 'Giraffe Neck Spots Inc.', 'R1005');
INSERT INTO game VALUES('MegaMonopoly', 'strategy', 'Fat Horse Games Inc.', 'R1006');
INSERT INTO game VALUES('Squirrel Catch', 'race', 'Beaver Industries Inc.', 'R1007');
INSERT INTO game VALUES('Hug Your Enemy', 'logic', 'Penguin Entertainment Inc.', 'R1008');
INSERT INTO game VALUES('Sleep More', 'race', 'Lazy Duck Games Inc.', 'R1009');
INSERT INTO game VALUES('Hungarian Pigs', 'luck', 'Giraffe Neck Spots Inc.', 'R1010');

INSERT INTO board VALUES('B1001', 'Money table', 'round', 20, 'MiniMonopoly');
INSERT INTO board VALUES('B1002', 'Horse platform', 'round', 50, 'Horse Race');
INSERT INTO board VALUES('B1003', 'Hug Land', 'round', 15, 'Hug Your Friend');
INSERT INTO board VALUES('B1004', 'The River', 'rectangle', 40, 'The Duckling Rush');
INSERT INTO board VALUES('B1005', 'Seoul', 'circle', 32, 'Korean Bonnies');
INSERT INTO board VALUES('B1006', 'MegaMoney table', 'round', 50, 'MegaMonopoly');
INSERT INTO board VALUES('B1007', 'Zoo Table', 'round', 11, 'Squirrel Catch');
INSERT INTO board VALUES('B1008', 'Dark Hug Land', 'round', 17, 'Hug Your Enemy');
INSERT INTO board VALUES('B1009', 'Bed', 'rectangle', 43, 'Sleep More');
INSERT INTO board VALUES('B1010', 'Farm table', 'circle', 32, 'Hungarian Pigs');

INSERT INTO event VALUES('E1001', 'Red storm', 'Every enemy figure position change to a random number');
INSERT INTO event VALUES('E1002', 'Drink and play', 'All your money has gone away.');
INSERT INTO event VALUES('E1003', 'Doping', 'Your horse get extra speed for two rounds. You can step twice as you would.');
INSERT INTO event VALUES('E1004', 'Eat', 'Your horse is tired, and stopped to eat. You cant move for one round.');
INSERT INTO event VALUES('E1005', 'Extra hug', 'You can get an extra hug from a chosen player.');
INSERT INTO event VALUES('E1006', 'Draw', 'Draw a card!');
INSERT INTO event VALUES('E1007', 'Fly wing', 'The teams duck wings has upgraded one level.');
INSERT INTO event VALUES('E1008', 'Pass', 'You miss one round.');
INSERT INTO event VALUES('E1009', 'Restart', 'Every team go back to starting position.');
INSERT INTO event VALUES('E1010', 'Comb your bunny', 'You can use a comb on your bunny, and it will give him / her plus 1 fluffy points.');
INSERT INTO event VALUES('E1011', 'Money storm', 'Every team gets random amount of money.');
INSERT INTO event VALUES('E1012', 'Sadness', 'Every team has to act sad.');
INSERT INTO event VALUES('E1013', 'SelfDoping', 'Your squirrel became more powerful. Pretty useless.');
INSERT INTO event VALUES('E1014', 'EatAcorn', 'Your squirrel is tired, and stopped to eat. You cant move for one round.');
INSERT INTO event VALUES('E1015', 'Extra sadhug', 'You can get an extra hug from the player you hate the most.');
INSERT INTO event VALUES('E1016', 'Draw', 'Draw another card!');
INSERT INTO event VALUES('E1017', 'Sweet Home', 'Gets extra sleep time.');
INSERT INTO event VALUES('E1018', 'Pills', 'Your team gets sleep pills.');
INSERT INTO event VALUES('E1019', 'Extra dirt', 'Your pig gets some extra dirt.');
INSERT INTO event VALUES('E1020', 'Clean', 'Your pig gets clean. Sad.');

INSERT INTO figure VALUES('F1001', 'The Scary Blood Horse', 'red', 'horse', 'B1001');
INSERT INTO figure VALUES('F1002', 'The Beautiful Orange Hyppo', 'orange', 'hyppo', 'B1001');
INSERT INTO figure VALUES('F1003', 'The Tree Dog', 'brown', 'dog', 'B1002');
INSERT INTO figure VALUES('F1004', 'The Lemon Car', 'yellow', 'car', 'B1002');
INSERT INTO figure VALUES('F1005', 'The Money Eifell-tower', 'green', 'Eifell-tower', 'B1003');
INSERT INTO figure VALUES('F1006', 'The Sad Bus', 'grey', 'bus', 'B1003');
INSERT INTO figure VALUES('F1007', 'The Clear Pyramid', 'white', 'pyramid', 'B1004');
INSERT INTO figure VALUES('F1008', 'The Oceanhog', 'blue', 'hedgehog', 'B1004');
INSERT INTO figure VALUES('F1009', 'The Blueberry Plane', 'purple', 'plane', 'B1005');
INSERT INTO figure VALUES('F1010', 'The Kind Pig', 'rose', 'pig', 'B1005');
INSERT INTO figure VALUES('F1011', 'The Lovely Hug Horse', 'pink', 'horse', 'B1006');
INSERT INTO figure VALUES('F1012', 'The Super Apple Hyppo', 'red', 'hyppo', 'B1006');
INSERT INTO figure VALUES('F1013', 'The Clean Dog', 'white', 'dog', 'B1007');
INSERT INTO figure VALUES('F1014', 'The Tasty Bed', 'yellow', 'bed', 'B1007');
INSERT INTO figure VALUES('F1015', 'The Poor Gate', 'green', 'gate', 'B1008');
INSERT INTO figure VALUES('F1016', 'The Happy Deer', 'yellow', 'deer', 'B1008');
INSERT INTO figure VALUES('F1017', 'The Free Rose', 'rosered', 'rose', 'B1009');
INSERT INTO figure VALUES('F1018', 'The Oceanpig', 'blue', 'pig', 'B1009');
INSERT INTO figure VALUES('F1019', 'The Strawberry Plane', 'red', 'plane', 'B1010');
INSERT INTO figure VALUES('F1020', 'The Super Kind Piglet', 'rose', 'piglet', 'B1010');


INSERT INTO related_to VALUES('C1001', 'E1001');
INSERT INTO related_to VALUES('C1002', 'E1002');
INSERT INTO related_to VALUES('C1003', 'E1003');
INSERT INTO related_to VALUES('C1004', 'E1004');
INSERT INTO related_to VALUES('C1005', 'E1005');
INSERT INTO related_to VALUES('C1006', 'E1006');
INSERT INTO related_to VALUES('C1007', 'E1007');
INSERT INTO related_to VALUES('C1008', 'E1008');
INSERT INTO related_to VALUES('C1009', 'E1009');
INSERT INTO related_to VALUES('C1010', 'E1010');
INSERT INTO related_to VALUES('C1011', 'E1011');
INSERT INTO related_to VALUES('C1012', 'E1012');
INSERT INTO related_to VALUES('C1013', 'E1013');
INSERT INTO related_to VALUES('C1014', 'E1014');
INSERT INTO related_to VALUES('C1015', 'E1015');
INSERT INTO related_to VALUES('C1016', 'E1016');
INSERT INTO related_to VALUES('C1017', 'E1017');
INSERT INTO related_to VALUES('C1018', 'E1018');
INSERT INTO related_to VALUES('C1019', 'E1019');
INSERT INTO related_to VALUES('C1020', 'E1020');

INSERT INTO leader VALUES('P1027', 'Steve Hedgehog', 'male', 8);
INSERT INTO leader VALUES('P1028', 'Steve Jobs', 'male', 10);
INSERT INTO leader VALUES('P1029', 'Bill Gates', 'male', 11);
INSERT INTO leader VALUES('P1030', 'Jeff Bezos', 'male', 12);
INSERT INTO leader VALUES('P1031', 'Harley Quinn', 'female', 9);
INSERT INTO leader VALUES('P1032', 'Joanne Wayne', 'female', 7);
INSERT INTO leader VALUES('P1033', 'Kate Allen', 'female', 13);
INSERT INTO leader VALUES('P1034', 'Peter Parker', 'male', 11);
INSERT INTO leader VALUES('P1035', 'Dorothy Lance', 'female', 12);
INSERT INTO leader VALUES('P1036', 'Sara Minecraft', 'female', 10);
INSERT INTO leader VALUES('P1037', 'Brad Hedgehog', 'male', 8);
INSERT INTO leader VALUES('P1038', 'Brad Pitt', 'male', 10);
INSERT INTO leader VALUES('P1039', 'Mark Hamil', 'male', 11);
INSERT INTO leader VALUES('P1040', 'Steve McDonald', 'male', 12);
INSERT INTO leader VALUES('P1041', 'Holy Law', 'female', 9);
INSERT INTO leader VALUES('P1042', 'Marques Wayne', 'female', 8);
INSERT INTO leader VALUES('P1043', 'Kate Wisdom', 'female', 14);
INSERT INTO leader VALUES('P1044', 'Mark Zuck', 'male', 10);
INSERT INTO leader VALUES('P1045', 'Laurel Quentin', 'female', 11);
INSERT INTO leader VALUES('P1046', 'Megatrony Adams', 'female', 10);

INSERT INTO team VALUES('The Conscious Donkeys', 4, 'P1036', 'MiniMonopoly', 'F1001');
INSERT INTO team VALUES('The Succinct Buffalos', 3, 'P1028', 'MiniMonopoly', 'F1002');
INSERT INTO team VALUES('The Grumpy Elephants', 1, 'P1029', 'Horse Race', 'F1003');
INSERT INTO team VALUES('The Waggish Squirrels', 2, 'P1030', 'Horse Race', 'F1004');
INSERT INTO team VALUES('The Little Shrews', 6, 'P1031', 'Hug Your Friend', 'F1005');
INSERT INTO team VALUES('The Telling Gazelles', 4, 'P1032', 'Hug Your Friend', 'F1006');
INSERT INTO team VALUES('The Fluffy Hedgehoges', 10, 'P1033', 'The Duckling Rush', 'F1007');
INSERT INTO team VALUES('The Kind Panthers', 10, 'P1034', 'The Duckling Rush', 'F1008');
INSERT INTO team VALUES('The Blind Dinos', 9, 'P1027', 'Korean Bonnies', 'F1009');
INSERT INTO team VALUES('The Spotted Whales', 9, 'P1035', 'Korean Bonnies', 'F1010');
INSERT INTO team VALUES('The Crazy Monkeys', 7, 'P1037', 'MegaMonopoly', 'F1011');
INSERT INTO team VALUES('The Sleepy Beavers', 3, 'P1038', 'MegaMonopoly', 'F1012');
INSERT INTO team VALUES('The Strong Camels', 1, 'P1039', 'Squirrel Catch', 'F1013');
INSERT INTO team VALUES('The Sweet Lames', 2, 'P1040', 'Squirrel Catch', 'F1014');
INSERT INTO team VALUES('The Clear Dachshunds', 6, 'P1041', 'Hug Your Enemy', 'F1015');
INSERT INTO team VALUES('The Clever Fish', 4, 'P1042', 'Hug Your Enemy', 'F1016');
INSERT INTO team VALUES('The Smart Tigers', 10, 'P1043', 'Sleep More', 'F1017');
INSERT INTO team VALUES('The Lawful Lions', 10, 'P1044', 'Sleep More', 'F1018');
INSERT INTO team VALUES('The Chubby Elephants', 9, 'P1045', 'Hungarian Pigs', 'F1019');
INSERT INTO team VALUES('The Neutral Gophers', 9, 'P1046', 'Hungarian Pigs', 'F1020');

INSERT INTO player VALUES('P1001', 'Robert Puente', 'male', 12, 'The Conscious Donkeys');
INSERT INTO player VALUES('P1002', 'Benjamin Reardon', 'male', 13, 'The Conscious Donkeys');
INSERT INTO player VALUES('P1003', 'Kevin Amaya', 'male', 6, 'The Succinct Buffalos');
INSERT INTO player VALUES('P1004', 'Marcus Bates', 'male', 12, 'The Succinct Buffalos');
INSERT INTO player VALUES('P1005', 'Benjamin Belt', 'male', 10, 'The Succinct Buffalos');
INSERT INTO player VALUES('P1006', 'Devon Davis', 'male', 9, 'The Grumpy Elephants');
INSERT INTO player VALUES('P1007', 'Kelvin Tapia', 'male', 12, 'The Grumpy Elephants');
INSERT INTO player VALUES('P1008', 'Vanesa Beckwith	', 'female', 6, 'The Grumpy Elephants');
INSERT INTO player VALUES('P1009', 'Ann Salgado', 'female', 8, 'The Waggish Squirrels');
INSERT INTO player VALUES('P1010', 'Rosalyn Haynes', 'female', 7, 'The Waggish Squirrels');
INSERT INTO player VALUES('P1011', 'Shaneka Gary', 'female', 7, 'The Little Shrews');
INSERT INTO player VALUES('P1012', 'Ann Wehr', 'female', 10, 'The Little Shrews');
INSERT INTO player VALUES('P1013', 'Cynthia Skinner', 'female', 11, 'The Telling Gazelles');
INSERT INTO player VALUES('P1014', 'Dorothy Byers', 'female', 11, 'The Telling Gazelles');
INSERT INTO player VALUES('P1015', 'Annie Martinez	', 'female', 12, 'The Fluffy Hedgehoges');
INSERT INTO player VALUES('P1016', 'Tracey Beus', 'female', 7, 'The Fluffy Hedgehoges');
INSERT INTO player VALUES('P1017', 'Kimberly Sisson', 'female', 13, 'The Fluffy Hedgehoges');
INSERT INTO player VALUES('P1018', 'Rochelle Heidenreich', 'female', 14, 'The Kind Panthers');
INSERT INTO player VALUES('P1019', 'Joyce Frink', 'female', 15, 'The Kind Panthers');
INSERT INTO player VALUES('P1020', 'Connie Marcial', 'female', 15, 'The Kind Panthers');
INSERT INTO player VALUES('P1021', 'Lisa Brannon', 'female', 7, 'The Blind Dinos');
INSERT INTO player VALUES('P1022', 'Margie Sharp', 'female', 17, 'The Blind Dinos');
INSERT INTO player VALUES('P1023', 'Dolores Thompson', 'female', 8, 'The Blind Dinos');
INSERT INTO player VALUES('P1024', 'Angela Austin', 'female', 6, 'The Spotted Whales');
INSERT INTO player VALUES('P1025', 'Linda Butts', 'female', 12, 'The Spotted Whales');
INSERT INTO player VALUES('P1026', 'Steve Wozniak', 'male', 11, 'The Spotted Whales');
INSERT INTO player VALUES('P1047', 'Klyde Puente', 'male', 12, 'The Crazy Monkeys');
INSERT INTO player VALUES('P1048', 'Benjamin Brenton', 'male', 14, 'The Crazy Monkeys');
INSERT INTO player VALUES('P1049', 'Brendon Amaya', 'male', 7, 'The Sleepy Beavers');
INSERT INTO player VALUES('P1050', 'Matt Bates', 'male', 10, 'The Sleepy Beavers');
INSERT INTO player VALUES('P1051', 'Hokage Belt', 'male', 12, 'The Strong Camels');
INSERT INTO player VALUES('P1052', 'Muraki Daraki', 'male', 7, 'The Strong Camels');
INSERT INTO player VALUES('P1053', 'Kim Tapia', 'male', 13, 'The Sweet Lames');
INSERT INTO player VALUES('P1054', 'Katelyn Beckwitch', 'female', 16, 'The Sweet Lames');
INSERT INTO player VALUES('P1055', 'Anna Salvados', 'female', 8, 'The Clear Dachshunds');
INSERT INTO player VALUES('P1056', 'Rosalyn Hook', 'female', 7, 'The Clear Dachshunds');
INSERT INTO player VALUES('P1057', 'Shakira Black', 'female', 7, 'The Clever Fish');
INSERT INTO player VALUES('P1058', 'Lily Wehra', 'female', 11, 'The Clever Fish');
INSERT INTO player VALUES('P1059', 'Cindy Ripper', 'female', 12, 'The Smart Tigers');
INSERT INTO player VALUES('P1060', 'Dora Brian', 'female', 12, 'The Smart Tigers');
INSERT INTO player VALUES('P1061', 'Kelly Martinzo', 'female', 13, 'The Lawful Lions');
INSERT INTO player VALUES('P1062', 'Trudy Beussek', 'female', 5, 'The Lawful Lions');
INSERT INTO player VALUES('P1063', 'Kelly Silson', 'female', 17, 'The Chubby Elephants');
INSERT INTO player VALUES('P1064', 'Rochelle Hindenburg', 'female', 12, 'The Chubby Elephants');
INSERT INTO player VALUES('P1065', 'Joi Makeup', 'female', 15, 'The Neutral Gophers');
INSERT INTO player VALUES('P1066', 'Mara Makeup', 'female', 15, 'The Neutral Gophers');

--What is the average age of the leaders older than 10?
SELECT AVG(AGE)
FROM leader
WHERE age > 10;

--Which figures are related to the groups with minimum 10 points?
SELECT team.team_name, figure.gamepcs_name, figure.shape, figure.color
FROM team INNER JOIN figure ON team.gamepcs_id = figure.gamepcs_id
WHERE team.score > 9;

--Which games are those, which are played on rectangle shaped boards and the board has at least 20 fields?
SELECT game.game_name
FROM game INNER JOIN board ON game.game_name = board.game_name
WHERE board.shape = 'rectangle' AND board.field_number > 20;

--Which game is played on the Hug Land table and who is the author of its ruleset?
SELECT game.game_name, ruleset.author
FROM ruleset
INNER JOIN game ON game.rule_id = ruleset.rule_id
INNER JOIN board ON board.game_name = game.game_name
WHERE board.board_name = 'Hug Land';

DROP VIEW view_example CASCADE CONSTRAINTS;

DROP TABLE player CASCADE CONSTRAINTS;
DROP TABLE leader CASCADE CONSTRAINTS;
DROP TABLE team CASCADE CONSTRAINTS;
DROP TABLE game CASCADE CONSTRAINTS;
DROP TABLE board CASCADE CONSTRAINTS;
DROP TABLE ruleset CASCADE CONSTRAINTS;
DROP TABLE figure CASCADE CONSTRAINTS;
DROP TABLE event CASCADE CONSTRAINTS;
DROP TABLE related_to CASCADE CONSTRAINTS;
DROP TABLE team_log CASCADE CONSTRAINTS;


