Question

Create a stored procedure that allows a user to select a bat’s manufacturer and (optionally) serial number using a stored procedure. The output should display all of the players who use the bat’s manufacturer. If the serial number is also provided, only display the players who use that bat’s manufacturer and serial number. Make sure you use a CREATE PROCEDURE call and insert this procedure into the existing database.

Submit a document that includes:

1. Commented code for the stored procedure to assist future users in understanding the steps/logic you followed

2. A paragraph describing how it could be embedded into a non-SQL program (e.g., a Windows application, a web app, a mobile app, etc.). Why would it be useful to be able to execute a stored procedure from outside an SQL editor?

Tables for the database:

TABLE TEAM - TEAMNUM             VARCHAR(4), TEAMNAME            VARCHAR(15), CITY               VARCHAR(30), MANAGER             VARCHAR(20), CONSTRAINT pk_team PRIMARY KEY (TEAMNUM)

TABLE BATS - SERIALNUM           VARCHAR(3), TEAMNUM             VARCHAR(4), MANUF              VARCHAR(20), CONSTRAINT pk_serialnum           PRIMARY KEY (SERIALNUM), CONSTRAINT fk1_teamnum          FOREIGN KEY (TEAMNUM)REFERENCES TEAM (TEAMNUM)

TABLE COACH - COACHNAME           VARCHAR(20), TEAMNUM             VARCHAR(4), TELEPHONE           VARCHAR(8), CONSTRAINT pk_coachname        PRIMARY KEY (COACHNAME), CONSTRAINT fk1_teamnum          FOREIGN KEY (TEAMNUM)REFERENCES TEAM (TEAMNUM)

TABLE PLAYER - PLAYERNUM           VARCHAR(4), PLAYERNAME          VARCHAR(20), AGE                SMALLINT, CONSTRAINT pk_playernum           PRIMARY KEY (PLAYERNUM)

TABLE AFFILIATION - PLAYERNUM           VARCHAR(4), TEAMNUM             VARCHAR(4), YEARS              SMALLINT, BATTINGAVE          FLOAT, CONSTRAINT pk1_playernum   PRIMARY KEY (PLAYERNUM), CONSTRAINT pk2_teamnum   PRIMARY KEY (TEAMNUM), CONSTRAINT fk1_playernum FOREIGN KEY (PLAYERNUM)REFERENCES PLAYER(PLAYERNUM), CONSTRAINT fk2_teamnum   FOREIGN KEY (TEAMNUM)REFERENCES TEAM (TEAMNUM)

TABLE WORK_EXPERIENCE - TEAMNUM             VARCHAR(4), COACHNAME           VARCHAR(20), EXPERIENCETYPE      VARCHAR(50), YEARSEXPERIENCE     SMALLINT, CONSTRAINT pk1_teamnum           PRIMARY KEY (TEAMRNUM), CONSTRAINT pk2_coachname    PRIMARY KEY (COACHNAME),      CONSTRAINT fk1_teamnum     FOREIGN KEY (TEAMNUM)REFERENCES TEAM (TEAMNUM), CONSTRAINT fk2_coachname         FOREIGN KEY (COACHNAME)REFERENCES COACH (COACHNAME)