CricbuzzDDL.version2

DROP TABLE BOWLING_SCORE;
DROP TABLE BATTING_SCORE;
DROP TABLE OUT_TYPE;
DROP TABLE PARTNERSHIP;
DROP TABLE INNINGS;
DROP TABLE MATCH;
DROP TABLE FIXTURE;
DROP TABLE TOUR;
DROP TABLE UMPIRE;
DROP TABLE STADIUM;
DROP TABLE BATSMAN;
DROP TABLE BOWLER;
DROP TABLE WICKET_KEEPER;
DROP TABLE PLAYER;
DROP TABLE MATCH_TYPE;
DROP TABLE TEAM;



DROP SEQUENCE team_seq;
DROP SEQUENCE player_seq;
DROP SEQUENCE matchtype_seq;
DROP SEQUENCE stadium_seq;
DROP SEQUENCE umpire_seq;
DROP SEQUENCE tour_seq;
DROP SEQUENCE fixture_seq;
DROP SEQUENCE match_seq;
DROP SEQUENCE partnership_seq;
DROP SEQUENCE outtype_seq;
DROP SEQUENCE batting_seq;
DROP SEQUENCE bowling_seq;


CREATE TABLE TEAM
(
    TEAM_ID NUMBER(10),
    TEAM_NAME VARCHAR2(50) UNIQUE NOT NULL,
    TEAM_SF VARCHAR2(10) NOT NULL,
    ESTABLISH_DATE DATE,
    MATCHES NUMBER(20),
    WON NUMBER(20),
    LOST NUMBER(20),
    DRAW NUMBER(20),
    TEAM_LOGO BLOB,
    HEAD_COACH VARCHAR2(50) NOT NULL,
    BOARD_PRESIDENT VARCHAR2(50) NOT NULL,

    CONSTRAINT PKteam PRIMARY KEY (TEAM_ID)
);

CREATE SEQUENCE team_seq START WITH 1;
CREATE OR REPLACE TRIGGER team_on_insert
  BEFORE INSERT ON TEAM
  FOR EACH ROW
BEGIN
  SELECT team_seq.NEXTVAL
  INTO :NEW.TEAM_ID
  FROM DUAL;
END;


CREATE TABLE MATCH_TYPE
(
    MATCH_TYPE_ID NUMBER(10),
    MATCH_TITLE VARCHAR2(10),

    CHECK (MATCH_TITLE IN ('T20', 'ODI', 'TEST')),

    CONSTRAINT PKmatchtype PRIMARY KEY (MATCH_TYPE_ID)
);

CREATE SEQUENCE matchtype_seq START WITH 1;
CREATE OR REPLACE TRIGGER matchtype_on_insert
  BEFORE INSERT ON MATCH_TYPE
  FOR EACH ROW
BEGIN
  SELECT matchtype_seq.NEXTVAL
  INTO :NEW.MATCH_TYPE_ID
  FROM DUAL;
END;


CREATE TABLE PLAYER
(
    PLAYER_ID NUMBER(10),
    FIRST_NAME VARCHAR2(30) NOT NULL,
    LAST_NAME VARCHAR2(30) NOT NULL,
    BORN VARCHAR2(50) NOT NULL,
    DOB DATE,
    DEATH DATE,
    ROLE VARCHAR2(30),
    PROFILE_PIC BLOB,
    STATUS VARCHAR2(10) DEFAULT 'Active',
    TEAM_ID NUMBER(10) NOT NULL,
    JERSEY NUMBER(3) NOT NULL,

    CHECK(ROLE IN('Wicket-keeper', 'Batsman', 'Bowler', 'All-rounder', 'Wicketkeeper-batsman')),
    CHECK(STATUS IN('Active', 'Retired', 'Died')),

    CONSTRAINT PKplayer PRIMARY KEY (PLAYER_ID),
    CONSTRAINT FKplayer FOREIGN KEY (TEAM_ID) REFERENCES TEAM
);

CREATE SEQUENCE player_seq START WITH 1;
CREATE OR REPLACE TRIGGER player_on_insert
  BEFORE INSERT ON PLAYER
  FOR EACH ROW
BEGIN
  SELECT player_seq.NEXTVAL
  INTO :NEW.PLAYER_ID
  FROM DUAL;
END;



CREATE TABLE WICKET_KEEPER
(
    PLAYER_ID NUMBER(10),
    MATCH_TYPE_ID NUMBER(10),
    STUMPINGS NUMBER(7),
    CATCHES NUMBER(7),
    NUM_OF_MATCHES NUMBER(5),
    NUM_OF_ING NUMBER(5),

    CONSTRAINT PKwk PRIMARY KEY (PLAYER_ID, MATCH_TYPE_ID),
    CONSTRAINT FKwk FOREIGN KEY (PLAYER_ID) REFERENCES PLAYER,
    CONSTRAINT FKwktwo FOREIGN KEY (MATCH_TYPE_ID) REFERENCES MATCH_TYPE
);


CREATE TABLE BOWLER
(
    PLAYER_ID NUMBER(10),
    MATCH_TYPE_ID NUMBER(10),
    WICKETS NUMBER(7),
    BALLS_BOWLED NUMBER(7),
    FIVE_WICKET_HAUL NUMBER(5),
    TEN_WICKET_HAUL NUMBER(5),
    BEST_BOWLING_MATCH VARCHAR2(6),
    BEST_BOWLING_ING VARCHAR2(6),
    BOWLING_STRIKE_RATE NUMBER(5, 2),
    BOWLING_ECONOMY_RATE NUMBER(5, 2),
    BOWLING_AVG NUMBER(5, 2),
    BOWLING_STYLE VARCHAR2(50),
    NUM_OF_MATCHES NUMBER(5),
    NUM_OF_ING NUMBER(5),

    CHECK ( BOWLING_STYLE IN ('Right-arm fast', 'Right-arm fast-medium',
    'Right-arm medium-fast', 'Right-arm medium', 'Right-arm medium-slow',
    'Right-arm slow-medium', 'Right-arm slow', 'Left-arm fast', 'Left-arm fast-medium',
    'Left-arm medium-fast', 'Left-arm medium', 'Left-arm medium-slow',
    'Left-arm slow-medium', 'Left-arm slow', 'Right-arm off break',
    'Right-arm leg break', 'Right-arm leg break googly', 'Right-arm leg spin',
    'Slow left-arm orthodox', 'Slow left-arm wrist spin', 'Left-arm googly', 'Left-arm leg spin') ),

    CONSTRAINT PKbowler PRIMARY KEY (PLAYER_ID, MATCH_TYPE_ID),
    CONSTRAINT FKbowler FOREIGN KEY (PLAYER_ID) REFERENCES PLAYER,
    CONSTRAINT FKbowlertwo FOREIGN KEY (MATCH_TYPE_ID) REFERENCES MATCH_TYPE
);


CREATE TABLE BATSMAN
(
    PLAYER_ID NUMBER(10),
    MATCH_TYPE_ID NUMBER(10),
    RUNS NUMBER(5),
    CENTURIES NUMBER(3),
    HALF_CENTURIES NUMBER(3),
    TWO_HUNDREDS NUMBER(3),
    THREE_HUNDREDS NUMBER(3),
    FOUR_HUNDREDS NUMBER(3),
    NOT_OUT NUMBER(3),
    TOP_SCORE NUMBER(4),
    BATTING_AVG NUMBER(5, 2),
    BATTING_STRIKE_RATE NUMBER(5, 2),
    BATTING_STYLE VARCHAR2(20) NOT NULL,
    DUCKS NUMBER(4),
    FOURS NUMBER(4),
    SIXES NUMBER(4),
    NUM_OF_MATCHES NUMBER(5),
    NUM_OF_ING NUMBER(5),

    CHECK (BATTING_STYLE IN ('Right-handed', 'Left-handed') ),
    CONSTRAINT PKbatsman PRIMARY KEY (PLAYER_ID, MATCH_TYPE_ID),
    CONSTRAINT FKbatsman FOREIGN KEY (PLAYER_ID) REFERENCES PLAYER,
    CONSTRAINT FKbatsmantwo FOREIGN KEY (MATCH_TYPE_ID) REFERENCES MATCH_TYPE
);





CREATE TABLE STADIUM
(
    STADIUM_ID NUMBER(10),
    STADIUM_NAME VARCHAR2(50) NOT NULL,
    LOCATION VARCHAR2(50) NOT NULL,
    COUNTRY VARCHAR2(50) NOT NULL,
    CAPACITY NUMBER(8) NOT NULL,

    CONSTRAINT PKstadium PRIMARY KEY (STADIUM_ID)
);

CREATE SEQUENCE stadium_seq START WITH 1;
CREATE OR REPLACE TRIGGER stadium_on_insert
  BEFORE INSERT ON STADIUM
  FOR EACH ROW
BEGIN
  SELECT stadium_seq.NEXTVAL
  INTO :NEW.STADIUM_ID
  FROM DUAL;
END;




CREATE TABLE UMPIRE
(
    UMPIRE_ID NUMBER(10),
    STATUS VARCHAR2(10) DEFAULT 'Active',
    FIRST_NAME VARCHAR2(30) NOT NULL,
    LAST_NAME VARCHAR2(30) NOT NULL,
    COUNTRY VARCHAR(50) NOT NULL,
    DOB DATE NOT NULL,
    DEATH DATE,
    CHECK(STATUS IN ('Active', 'Retired', 'Died')),

    CONSTRAINT PKumprie PRIMARY KEY(UMPIRE_ID)
);

CREATE SEQUENCE umpire_seq START WITH 1;
CREATE OR REPLACE TRIGGER umpire_on_insert
  BEFORE INSERT ON UMPIRE
  FOR EACH ROW
BEGIN
  SELECT umpire_seq.NEXTVAL
  INTO :NEW.UMPIRE_ID
  FROM DUAL;
END;




CREATE TABLE TOUR
(
    TOUR_ID NUMBER(10),
    HOST_TEAM NUMBER(10) NOT NULL,
    VISITING_TEAM NUMBER(10) NOT NULL,
    T20 NUMBER(10),
    ODI NUMBER(10),
    TEST NUMBER(10),

    CONSTRAINT PKtour PRIMARY KEY (TOUR_ID),
    CONSTRAINT FKoneTour FOREIGN KEY (HOST_TEAM) REFERENCES TEAM (TEAM_ID),
    CONSTRAINT FKtwoTour FOREIGN KEY (VISITING_TEAM) REFERENCES TEAM (TEAM_ID)
);

CREATE SEQUENCE tour_seq START WITH 1;
CREATE OR REPLACE TRIGGER tour_on_insert
  BEFORE INSERT ON TOUR
  FOR EACH ROW
BEGIN
  SELECT tour_seq.NEXTVAL
  INTO :NEW.TOUR_ID
  FROM DUAL;
END;




CREATE TABLE FIXTURE
(
    FIXTURE_ID  NUMBER(10),
    TOUR_ID NUMBER(10) NOT NULL,
    DATETIME    DATE NOT NULL,
    STADIUM_ID NUMBER(10) NOT NULL,
    MATCH_TYPE_ID NUMBER(10) NOT NULL,
    ORDERING NUMBER(2),

    CONSTRAINT PKfixture PRIMARY KEY (FIXTURE_ID),
    CONSTRAINT FKfixture FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM,
    CONSTRAINT FKtwoFixture FOREIGN KEY (TOUR_ID) REFERENCES TOUR,
    CONSTRAINT FKthreeFixture FOREIGN KEY (MATCH_TYPE_ID) REFERENCES MATCH_TYPE
);

CREATE SEQUENCE fixture_seq START WITH 1;
CREATE OR REPLACE TRIGGER fixture_on_insert
  BEFORE INSERT ON FIXTURE
  FOR EACH ROW
BEGIN
  SELECT fixture_seq.NEXTVAL
  INTO :NEW.FIXTURE_ID
  FROM DUAL;
END;




CREATE TABLE MATCH
(
    MATCH_ID NUMBER(10),
    FIXTURE_ID NUMBER(10) NOT NULL UNIQUE,
    TOUR_ID NUMBER(10) NOT NULL,
    UMPIRE_ONE NUMBER(10),
    UMPIRE_TWO NUMBER(10),
    UMPIRE_THREE NUMBER(10),
    HOST_TEAM NUMBER(10) NOT NULL,
    VISITING_TEAM NUMBER(10) NOT NULL,
    WINNER NUMBER(10),
    MATCH_TYPE_ID NUMBER(10) NOT NULL,
    MATCH_STATUS VARCHAR2(20) NOT NULL,
    MAN_OF_THE_MATCH NUMBER(10),
    RESULT VARCHAR2(60),
    TOS_WINNER NUMBER(10),
    TOS_DECISION VARCHAR2(30),
    HOST_TEAM_CAPTAIN NUMBER(10),
    VISITING_TEAM_CAPTAIN NUMBER(10),
    HOST_TEAM_WK NUMBER(10), /*WICKET_KEEPER OF TEAM ONE*/
    VISITING_TEAM_WK NUMBER(10),

    CHECK (MATCH_STATUS IN ('Live', 'End', 'Upcoming', 'Abandoned') ),
    CHECK (TOS_DECISION IN ('Elected to bat first', 'Elected to bowl first') ),

    CONSTRAINT PKmatch PRIMARY KEY (MATCH_ID),
    CONSTRAINT FKMATCH FOREIGN KEY (TOUR_ID) REFERENCES TOUR (TOUR_ID),
    CONSTRAINT FKoneMATCH FOREIGN KEY (FIXTURE_ID) REFERENCES FIXTURE (FIXTURE_ID),
    CONSTRAINT FKtwoMATCH FOREIGN KEY (UMPIRE_ONE) REFERENCES UMPIRE (UMPIRE_ID),
    CONSTRAINT FKthreeMATCH FOREIGN KEY (UMPIRE_TWO) REFERENCES UMPIRE (UMPIRE_ID),
    CONSTRAINT FKfourMATCH FOREIGN KEY (UMPIRE_THREE) REFERENCES UMPIRE (UMPIRE_ID),
    CONSTRAINT FKthirteenMATCH FOREIGN KEY (HOST_TEAM) REFERENCES TEAM (TEAM_ID),
    CONSTRAINT FKfourteenMATCH FOREIGN KEY (VISITING_TEAM) REFERENCES TEAM (TEAM_ID),
    CONSTRAINT FKfiveMATCH FOREIGN KEY (WINNER) REFERENCES TEAM (TEAM_ID),
    CONSTRAINT FKsixMATCH FOREIGN KEY (MATCH_TYPE_ID) REFERENCES MATCH_TYPE (MATCH_TYPE_ID),
    CONSTRAINT FKsevenMATCH FOREIGN KEY (MAN_OF_THE_MATCH) REFERENCES PLAYER (PLAYER_ID),
    CONSTRAINT FKeightMATCH FOREIGN KEY (TOS_WINNER) REFERENCES TEAM (TEAM_ID),
    CONSTRAINT FKnineMATCH FOREIGN KEY (HOST_TEAM_CAPTAIN) REFERENCES PLAYER (PLAYER_ID),
    CONSTRAINT FKtenMATCH FOREIGN KEY (VISITING_TEAM_CAPTAIN) REFERENCES PLAYER (PLAYER_ID),
    CONSTRAINT FKelevenMATCH FOREIGN KEY (HOST_TEAM_WK) REFERENCES PLAYER (PLAYER_ID),
    CONSTRAINT FKtwelveMATCH FOREIGN KEY (VISITING_TEAM_WK) REFERENCES PLAYER (PLAYER_ID)
);

CREATE SEQUENCE match_seq START WITH 1;
CREATE OR REPLACE TRIGGER match_on_insert
  BEFORE INSERT ON MATCH
  FOR EACH ROW
BEGIN
  SELECT match_seq.NEXTVAL
  INTO :NEW.MATCH_ID
  FROM DUAL;
END;




CREATE TABLE INNINGS
(
    INNINGS_NO NUMBER(1),
    MATCH_ID NUMBER(10),
    BATTING_TEAM NUMBER(10) NOT NULL,
    BOWLING_TEAM NUMBER(10) NOT NULL,
    TOTAL_RUNS NUMBER(4),
    WICKETS NUMBER(2),
    FOURS NUMBER(3),
    SIXES NUMBER(3),
    OVERS NUMBER(4, 1),
    DAY NUMBER(1),
    WIDES NUMBER(3),
    NO_BALLS NUMBER(3),
    BYE_RUNS NUMBER(3),
    LEG_BYES NUMBER(3),

    CHECK (INNINGS_NO IN (1, 2, 3, 4)),
    CONSTRAINT PKinnings PRIMARY KEY (INNINGS_NO, MATCH_ID),
    CONSTRAINT FKoneInnings FOREIGN KEY (MATCH_ID) REFERENCES MATCH,
    CONSTRAINT FKtwoInnings FOREIGN KEY (BATTING_TEAM) REFERENCES TEAM (TEAM_ID),
    CONSTRAINT FKthreeInnings FOREIGN KEY (BOWLING_TEAM) REFERENCES TEAM (TEAM_ID)
);




CREATE TABLE PARTNERSHIP
(
    PARTNERSHIP_ID NUMBER(10),
    PLAYER_ONE NUMBER(10),
    PLAYER_TWO NUMBER(10),
    INNINGS_NO NUMBER(1),
    MATCH_ID NUMBER(10),
    RUNS NUMBER(4),
    BALLS NUMBER(5),

    CONSTRAINT PKpartner PRIMARY KEY(PARTNERSHIP_ID),
    CONSTRAINT FKonePARTNER FOREIGN KEY(PLAYER_ONE) REFERENCES PLAYER(PLAYER_ID),
    CONSTRAINT FKtwoPARTNER FOREIGN KEY(PLAYER_TWO) REFERENCES PLAYER(PLAYER_ID),
    CONSTRAINT FKthreePARTNER FOREIGN KEY(INNINGS_NO, MATCH_ID) REFERENCES INNINGS(INNINGS_NO, MATCH_ID)
);

CREATE SEQUENCE partnership_seq START WITH 1;
CREATE OR REPLACE TRIGGER partnership_on_insert
  BEFORE INSERT ON PARTNERSHIP
  FOR EACH ROW
BEGIN
  SELECT partnership_seq.NEXTVAL
  INTO :NEW.PARTNERSHIP_ID
  FROM DUAL;
END;



CREATE TABLE OUT_TYPE
(
    OUT_ID NUMBER(10),
    OUT_TITLE VARCHAR2(50),
    CHECK (OUT_TITLE IN ('Run out', 'Caught', 'Obstructing the field',
    'LBW', 'Hit wicket', 'Handled the ball', 'Hit the ball twice',
    'stumped', 'Bowled', 'Timed out', 'Retired')),

    CONSTRAINT PKouttype PRIMARY KEY (OUT_ID)
);

CREATE SEQUENCE outtype_seq START WITH 1;
CREATE OR REPLACE TRIGGER outtype_on_insert
  BEFORE INSERT ON OUT_TYPE
  FOR EACH ROW
BEGIN
  SELECT outtype_seq.NEXTVAL
  INTO :NEW.OUT_ID
  FROM DUAL;
END;



CREATE TABLE BATTING_SCORE
(
    BATTING_ID NUMBER(10),
    PLAYER_ID NUMBER(10),
    INNINGS_NO NUMBER(1),
    MATCH_ID NUMBER(10),
    RUNS NUMBER(4),
    BALLS NUMBER(4),
    FOURS NUMBER(3),
    SIXES NUMBER(3),
    OUT_BY NUMBER(10),
    OUT_CONTRIBUTOR NUMBER(10),
    OUT_ID NUMBER(10),
    STATUS VARCHAR2(10),

    CHECK (STATUS IN ('OUT','NOT OUT','YET TO BAT','NEED NOT BATTED')),

    CONSTRAINT PKbattingscore PRIMARY KEY (BATTING_ID),
    CONSTRAINT FKonebattingscore FOREIGN KEY(PLAYER_ID) REFERENCES PLAYER (PLAYER_ID),
    CONSTRAINT FKtwobattingscore FOREIGN KEY(INNINGS_NO, MATCH_ID) REFERENCES INNINGS (INNINGS_NO, MATCH_ID),
    CONSTRAINT FKthreebattingscore FOREIGN KEY(OUT_BY) REFERENCES PLAYER (PLAYER_ID),
    CONSTRAINT FKfourbattingscore FOREIGN KEY(OUT_CONTRIBUTOR) REFERENCES PLAYER (PLAYER_ID),
    CONSTRAINT FKfivebattingscore FOREIGN KEY(OUT_ID) REFERENCES OUT_TYPE
);

CREATE SEQUENCE batting_seq START WITH 1;
CREATE OR REPLACE TRIGGER batting_on_insert
  BEFORE INSERT ON BATTING_SCORE
  FOR EACH ROW
BEGIN
  SELECT batting_seq.NEXTVAL
  INTO :NEW.BATTING_ID
  FROM DUAL;
END;




CREATE TABLE BOWLING_SCORE
(
    BOWLING_ID NUMBER(10),
    PLAYER_ID NUMBER(10),
    INNINGS_NO NUMBER(1),
    MATCH_ID NUMBER(10),
    RUNS NUMBER(4),
    WICKETS NUMBER(2),
    BYE_RUNS NUMBER(4),
    LEG_BYES NUMBER(4),
    WIDES NUMBER(4),
    NO_BALLS NUMBER(4),
    OVERS NUMBER(4, 1),
    MAIDENS NUMBER(3),

    CONSTRAINT PKbowlingscore PRIMARY KEY (BOWLING_ID),
    CONSTRAINT FKonebowlingscore FOREIGN KEY (PLAYER_ID) REFERENCES PLAYER,
    CONSTRAINT FKtwobowlingscore FOREIGN KEY (INNINGS_NO, MATCH_ID) REFERENCES INNINGS(INNINGS_NO, MATCH_ID)
);

CREATE SEQUENCE bowling_seq START WITH 1;
CREATE OR REPLACE TRIGGER bowling_on_insert
  BEFORE INSERT ON BOWLING_SCORE
  FOR EACH ROW
BEGIN
  SELECT bowling_seq.NEXTVAL
  INTO :NEW.BOWLING_ID
  FROM DUAL;
END;



--Functions--


-- calculates age --

CREATE OR REPLACE FUNCTION AGECALCULATOR(d1 IN DATE, d2 IN DATE)
RETURN NUMBER IS
    age NUMBER(3);
BEGIN
    age := FLOOR(MONTHS_BETWEEN(d1, d2)/12);
    RETURN age;
END;


-- batting strike rate = (runs scored / ball faced) * 100
-- bowling strike rate = (balls bowled / wickets taken)

CREATE OR REPLACE FUNCTION STRIKE_RATE (playerid IN NUMBER, battingORbowling IN VARCHAR2, matchtype IN VARCHAR2)
RETURN NUMBER IS
    strike NUMBER := 0;
    runsScored NUMBER := 0;
    ballfaced NUMBER := 0;
    ballsbowled NUMBER := 0;
    wicketstaken NUMBER := 0;
BEGIN
    IF battingORbowling = 'Batting' THEN
            SELECT RUNS INTO runsScored FROM CRICBUZZ.BATSMAN WHERE PLAYER_ID = playerid;
            SELECT SUM(BALLS) INTO ballfaced FROM CRICBUZZ.BATTING_SCORE WHERE PLAYER_ID = playerid
            AND MATCH_ID = ANY (SELECT MATCH_ID FROM CRICBUZZ.MATCH
            WHERE MATCH_TYPE_ID = ANY(SELECT MATCH_TYPE_ID FROM MATCH_TYPE
            WHERE MATCH_TITLE = matchtype));
            strike := ROUND((runsScored/ballfaced)*100, 2);
    ELSIF battingORbowling = 'Bowling' THEN
            SELECT BALLS_BOWLED, WICKETS
            INTO ballsbowled, wicketstaken
            FROM CRICBUZZ.BOWLER
            WHERE PLAYER_ID = playerid AND
            MATCH_TYPE_ID = (SELECT MATCH_TYPE_ID FROM CRICBUZZ.MATCH_TYPE
            WHERE MATCH_TITLE = matchtype);
            strike := ROUND(ballsbowled/wicketstaken, 2);
    END IF;
    RETURN strike;
END;



-- batting average = (runs scored / number of time out)
-- bowling average = (runs conceded / wicket taken)

CREATE OR REPLACE FUNCTION AVERAGE (playerid IN NUMBER, battingORbowling IN VARCHAR2, matchtype IN VARCHAR2)
RETURN NUMBER IS
    aver NUMBER := 0;
    runsScored NUMBER := 0;
    timesOut NUMBER := 0;
    numInnings NUMBER := 0;
    runsConceded NUMBER := 0;
    wickettaken NUMBER := 0;
BEGIN
    IF battingORbowling = 'Batting' THEN
            SELECT RUNS, NOT_OUT, NUM_OF_ING
            INTO runsScored, timesOut, numInnings
            FROM CRICBUZZ.BATSMAN WHERE PLAYER_ID = playerid
            AND MATCH_TYPE_ID = ANY(SELECT MATCH_TYPE_ID
            FROM CRICBUZZ.MATCH_TYPE
            WHERE MATCH_TITLE = matchtype);

            timesOut := (numInnings - timesOut);
            aver := ROUND(runsScored/timesOut, 2);

    ELSIF battingORbowling = 'Bowling' THEN
            SELECT WICKETS
            INTO wickettaken FROM CRICBUZZ.BOWLER
            WHERE PLAYER_ID = playerid AND MATCH_TYPE_ID = ANY(SELECT MATCH_TYPE_ID
            FROM CRICBUZZ.MATCH_TYPE
            WHERE MATCH_TITLE = matchtype);

            SELECT SUM(RUNS) INTO runsConceded FROM CRICBUZZ.BOWLING_SCORE
            WHERE PLAYER_ID = playerid
            AND MATCH_ID = ANY (SELECT MATCH_ID FROM CRICBUZZ.MATCH
            WHERE MATCH_TYPE_ID = ANY(SELECT MATCH_TYPE_ID FROM CRICBUZZ.MATCH_TYPE
            WHERE MATCH_TITLE = matchtype));

            aver := ROUND(runsConceded/wickettaken, 2);
    END IF;
    RETURN aver;
END;



-- Economy = runs conceded / overs bowled

CREATE OR REPLACE FUNCTION ECONOMY(playerid IN NUMBER, matchtype IN VARCHAR2)
RETURN NUMBER IS
    eco NUMBER := 0;
    runsConceded NUMBER := 0;
    oversbowled NUMBER := 0;
BEGIN
    SELECT BALLS_BOWLED
    INTO oversbowled
    FROM CRICBUZZ.BOWLER
    WHERE PLAYER_ID = playerid AND
    MATCH_TYPE_ID = (SELECT MATCH_TYPE_ID FROM CRICBUZZ.MATCH_TYPE
    WHERE MATCH_TITLE = matchtype);

    oversbowled := oversbowled/6;

  SELECT SUM(RUNS) INTO runsConceded FROM CRICBUZZ.BOWLING_SCORE
    WHERE PLAYER_ID = playerid
    AND MATCH_ID = ANY (SELECT MATCH_ID FROM CRICBUZZ.MATCH
    WHERE MATCH_TYPE_ID = ANY(SELECT MATCH_TYPE_ID FROM CRICBUZZ.MATCH_TYPE
    WHERE MATCH_TITLE = matchtype));

    eco := ROUND(runsConceded/oversbowled, 2);
    RETURN eco;
END;


-- checking/verification if the functions work properly:

DECLARE
    strk NUMBER := 0;
        aver NUMBER := 0;
        eco NUMBER := 0;
BEGIN

END;

------------------------------------------------------------------
/* MATCH_TYPE INSERTION */
INSERT INTO MATCH_TYPE (MATCH_TITLE)
VALUES ('T20');

INSERT INTO MATCH_TYPE (MATCH_TITLE)
VALUES ('ODI');

INSERT INTO MATCH_TYPE (MATCH_TITLE)
VALUES ('TEST');



/* OUT_TYPE INSERTION */
INSERT INTO OUT_TYPE (OUT_TITLE)
VALUES ('Run out');

INSERT INTO OUT_TYPE (OUT_TITLE)
VALUES ('Caught');

INSERT INTO OUT_TYPE (OUT_TITLE)
VALUES ('LBW');

INSERT INTO OUT_TYPE (OUT_TITLE)
VALUES ('Hit wicket');

INSERT INTO OUT_TYPE (OUT_TITLE)
VALUES ('Handled the ball');

INSERT INTO OUT_TYPE (OUT_TITLE)
VALUES ('Hit the ball twice');

INSERT INTO OUT_TYPE (OUT_TITLE)
VALUES ('stumped');

INSERT INTO OUT_TYPE (OUT_TITLE)
VALUES ('Bowled');

INSERT INTO OUT_TYPE (OUT_TITLE)
VALUES ('Timed out');

INSERT INTO OUT_TYPE (OUT_TITLE)
VALUES ('Retired');