Actual Exam

--- Q1 ---
create table Match (
	id int,
	venueID int,
	teamID int,
	opponentID int,
	teamPoints int,
	primary key (id),
	foreign key (venueID) references Venue(id),
	foreign key (teamID) references Team(id),
	foreign key (opponentID) references Team(id)
);

--- Q2 ---
select Team.name
from Team, Match
where Match.teamID = Team.id
and Match.teamPoints = 3;

--- Q3 ---
delete from Match
where opponentID = 12 
or teamID = 12;

--- Q4 ---
select Venue.name, count(Match.venueID) as "number of matches"
from Venue, Match
where Venue.id = Match.venueID
group by Match.venueID
having avg(Match.teamPoints) > 1;


--- Q5 ---
create procedure MatchDetails (alocation Venue.name%type) as
cursor list is 
select teamID, opponentID
from Match, Venue
where alocation = Venue.name 
and Venue.id = Match.venueID;
aTeam Match.teamID%type;
aOpp Match.opponentID%type;
begin
open list;
loop
	fetch list into aTeam, aOpp;
	exit when list%notfound;
	dbms_output.put_line('team: '||aTeam||' Opponent: 'aOpp);
end loop;
close list;
end;
/

--- Q7 ---
create type VenueDetails as object (
	address varchar(25),
	totalMatches int,
	member function addOneMatch return int
) not final;
/

create type body VenueDetails as
	member function addOneMatch return int is
	begin 
		totalMatches := totalMatches + 1;
		return totalMatches;
	end addOneMatch;
end;
/

create type BookableVenueDetails under VenueDetails (
	totalTicketsSold int
) final;
/

--- Q8 ---
100 // teamID > 20
ans + 20 + (40 * 20) // venueID = Venue.id
ans + (40 * 20) // project rows

// FINAL ANSWER
ans = 1720

--- Q9 ---
create view userView as 
select matchPoints, location
from Match, Venue
where venueID = Venue.id
and teamID > 20;

grant select on userView to userName;