assignment3/ass3.sql — — COMP9311 Database Systems
— — Assignment 3
— — Written By: YOUR NAME
— — ID: YOUR ID
— — Date:

set serveroutput on

— Q1 create a function that returns the number of times a player has played in
— his favorite stadiums. Return -2 if the player doesn’t exist in our database.
— Return -1 if the player doesn’t have any favorite stadium

create or replace function favS(pname varchar2) return number
as
— Write your declarations here
begin
— Write your statements here
return 0; — CHANGE THIS LINE TO RETURN APPROPRIATE VALUE
end;
/

— Q2 create a procedure that inserts in Q2 table match ID and name of the stadium
— for each player where he was declared man of the match but his team had lost.
create or replace procedure manofthematch
is
— Write your declarations here
begin
delete from Q2; — DON’T REMOVE THIS LINE (it makes the table empty at start of the procedure)
— Write your statements here
end;
/

— Q3 create a procedure that inserts the league results into a table Q3

create or replace procedure league_results
as
— Write you declarations here
begin
delete from Q3; — DON’T REMOVE THIS LINE (it makes the table empty at start of the procedure)
— Write your statements here
end;
/

— Q4 create your triggers on AUTOGRAPHS table after this line

assignment3/create.sql
set feedback on
prompt creating tables …
set feedback on

create table players (
name varchar(20) primary key,
birthdate date,
shirtNumber number(2),
team varchar(20) not null
);

create table officials (
staffID NUMBER(2) primary key,
name varchar(20),
address varchar(20),
birthdate date,
staysAt varchar(20) not null,
checkIn date,
checkOut date
);

create table hotels(
name varchar(20) primary key,
rooms integer check (rooms > 0),
address varchar(20),
manager number(2) not null
references officials(staffID)
initially deferred deferrable
);

create table teams (
name varchar(20) primary key,
staysAt…