Write a stored procedure for the Youth League Database.
SQL Script containing the Stored Procedure for the Youth LeagueDatabase and the statement to Call to the Stored Procedure
——————————————————————————————————————————–
DROP DATABASE YouthLeague;
CREATE DATABASE YouthLeague;
USE YouthLeague;
CREATE TABLE Parent
(
Parent_ID CHAR (8),
Parent_LName VARCHAR (50) NOT NULL,
Parent_FName VARCHAR (50) NOT NULL,
Parent_Phone CHAR (11),
Parent_Street VARCHAR (50) NOT NULL,
Parent_City VARCHAR (50) NOT NULL,
Parent_State VARCHAR (50) NOT NULL,
Parent_Zip CHAR (5),
CONSTRAINT Parent_PK PRIMARY KEY (Parent_ID)
);
CREATE TABLE Team
(
Team_ID CHAR (8),
Team_Name VARCHAR (50)NOT NULL,
Team_Colors VARCHAR (50) NOT NULL,
CONSTRAINT Team_PK PRIMARY KEY (Team_ID)
);
CREATE TABLE Player
(
Player_ID CHAR (8),
Player_FName VARCHAR (50),
Player_LName VARCHAR (50),
Player_Age NUMERIC NOT NULL,
Team_ID_FK CHAR (8),
CONSTRAINT Player_PK PRIMARY KEY (Player_ID),
CONSTRAINT Player_FK1 FOREIGN KEY (Team_ID_FK)
REFERENCES Team(Team_ID)
);
CREATE TABLE Coach
(
Coach_ID CHAR (8),
Coach_FName VARCHAR (50) NOT NULL,
Coach_LName VARCHAR (50) NOT NULL,
Coach_Phone CHAR (11),
Team_ID_FK CHAR (8),
CONSTRAINT Coach_PK PRIMARY KEY (Coach_ID),
CONSTRAINT Coach_FK1 FOREIGN KEY (Team_ID_FK)
REFERENCES Team(Team_ID)
);
CREATE TABLE Registers
(
Parent_ID_FK CHAR (8),
Player_ID_FK CHAR (8),
CONSTRAINT Registers_PK PRIMARY KEY (Parent_ID_FK,Player_ID_FK),
CONSTRAINT Registers_FK1 FOREIGN KEY (Parent_ID_FK)REFERENCES Parent(Parent_ID),
CONSTRAINT Registers_FK2 FOREIGN KEY (Player_ID_FK)REFERENCES Player(Player_ID)
);
—————————————————————————————————————————
INSERT INTO Parent (Parent_ID, Parent_LName, Parent_FName,Parent_Phone, Parent_Street, Parent_City, Parent_State,Parent_Zip)
VALUES (“PA101″,”Dunn”,”Hector “,”18033158451″,”2665 MooreAvenue”,”Ward”,”South Carolina”,”29166″);
INSERT INTO Parent (Parent_ID, Parent_LName, Parent_FName,Parent_Phone, Parent_Street, Parent_City, Parent_State,Parent_Zip)
VALUES (“PA102″,”Riddle”,”Kyla “,”14147795088″,”4407 JohnnyLane”,”Milwaukee”,”Wisconsin”,”53202″);
INSERT INTO Parent (Parent_ID, Parent_LName, Parent_FName,Parent_Phone, Parent_Street, Parent_City, Parent_State,Parent_Zip)
VALUES (“PA103″,”Pennington”,”Andy “,”16512192382″,”1590 PostAvenue”,”Minneapolis”,”Minnesota”,”55402″);
INSERT INTO Parent (Parent_ID, Parent_LName, Parent_FName,Parent_Phone, Parent_Street, Parent_City, Parent_State,Parent_Zip)
VALUES (“PA104″,”Bush”,”Lauren “,”18104743326″,”1862 FrontStreet”,”Southfield”,”Michigan”,”48075″);
INSERT INTO Parent (Parent_ID, Parent_LName, Parent_FName,Parent_Phone, Parent_Street, Parent_City, Parent_State,Parent_Zip)
VALUES (“PA105″,”Klein”,”Krish “,”13139377743″,”3920 StateStreet”,”Livonia”,”Michigan”,”48150″);
INSERT INTO Parent (Parent_ID, Parent_LName, Parent_FName,Parent_Phone, Parent_Street, Parent_City, Parent_State,Parent_Zip)
VALUES (“PA106″,”Anthony”,”Pascale”,”15734070220″,”3739 AdamsDrive”,”Grover”,”Missouri”,”63040″);
INSERT INTO Parent (Parent_ID, Parent_LName, Parent_FName,Parent_Phone, Parent_Street, Parent_City, Parent_State,Parent_Zip)
VALUES (“PA107″,”Stanley”,”Letchworth”,”12158054833″,”121 LostCreek Road”,”Sinking Spring”,”Pennsylvania”,”19608″);
INSERT INTO Team (Team_ID, Team_Name, Team_Colors)
VALUES (“T101″,”Rebels”,”Blue”);
INSERT INTO Team (Team_ID, Team_Name, Team_Colors)
VALUES (“T102″,”Red Devils”,”Red”);
INSERT INTO Team (Team_ID, Team_Name, Team_Colors)
VALUES (“T103″,”Predators”,”Yellow”);
INSERT INTO Team (Team_ID, Team_Name, Team_Colors)
VALUES (“T104″,”Aggies”,”Orange”);
INSERT INTO Team (Team_ID, Team_Name, Team_Colors)
VALUES (“T105″,”Privateers”,”Green”);
INSERT INTO Team (Team_ID, Team_Name, Team_Colors)
VALUES (“T106″,”Pride”,”Brown”);
INSERT INTO Team (Team_ID, Team_Name, Team_Colors)
VALUES (“T107″,”Aces”,”White”);
INSERT INTO Player (Player_ID, Player_FName, Player_LName,Player_Age, Team_ID_FK)
VALUES (“PL101″,”Aaron”,”Rodgers”,”15″,”T101″);
INSERT INTO Player (Player_ID, Player_FName, Player_LName,Player_Age, Team_ID_FK)
VALUES (“PL102″,”Drew”,”Brees”,”10″,”T102″);
INSERT INTO Player (Player_ID, Player_FName, Player_LName,Player_Age, Team_ID_FK)
VALUES (“PL103″,”Tom”,”Brady”,”12″,”T103″);
INSERT INTO Player (Player_ID, Player_FName, Player_LName,Player_Age, Team_ID_FK)
VALUES (“PL104″,”Todd”,”Gurley”,”14″,”T104″);
INSERT INTO Player (Player_ID, Player_FName, Player_LName,Player_Age, Team_ID_FK)
VALUES (“PL105″,”Antonio”,”Brown”,”11″,”T105″);
INSERT INTO Player (Player_ID, Player_FName, Player_LName,Player_Age, Team_ID_FK)
VALUES (“PL106″,”David”,”Silva”,”13″,”T106″);
INSERT INTO Player (Player_ID, Player_FName, Player_LName,Player_Age, Team_ID_FK)
VALUES (“PL107″,”Sergio”,”Ramos”,”12″,”T107″);
INSERT INTO Coach (Coach_ID, Coach_FName, Coach_LName,Coach_Phone, Team_ID_FK)
VALUES (“C101″,”Gregg”,”Berhalter”,”18137922300″,”T101″);
INSERT INTO Coach (Coach_ID, Coach_FName, Coach_LName,Coach_Phone, Team_ID_FK)
VALUES (“C102″,”Bruce”,”Arena”,”17862512401″,”T102″);
INSERT INTO Coach (Coach_ID, Coach_FName, Coach_LName,Coach_Phone, Team_ID_FK)
VALUES (“C103″,”Dave”,”Sarachan”,”14348262888″,”T103″);
INSERT INTO Coach (Coach_ID, Coach_FName, Coach_LName,Coach_Phone, Team_ID_FK)
VALUES (“C104″,”Steve”,”Sampson”,”15596317376″,”T104″);
INSERT INTO Coach (Coach_ID, Coach_FName, Coach_LName,Coach_Phone, Team_ID_FK)
VALUES (“C105″,”Bob”,”Gansler”,”13256033157″,”T105″);
INSERT INTO Coach (Coach_ID, Coach_FName, Coach_LName,Coach_Phone, Team_ID_FK)
VALUES (“C106″,”Cobi”,”Jones”,”17087849071″,”T106″);
INSERT INTO Coach (Coach_ID, Coach_FName, Coach_LName,Coach_Phone, Team_ID_FK)
VALUES (“C107″,”John”,”Hackworth”,”15305388387″,”T107″);
INSERT INTO Registers (Parent_ID_FK, Player_ID_FK)
VALUES (“PA101″,”PL101”);
INSERT INTO Registers (Parent_ID_FK, Player_ID_FK)
VALUES (“PA102″,”PL102”);
INSERT INTO Registers (Parent_ID_FK, Player_ID_FK)
VALUES (“PA103″,”PL103”);
INSERT INTO Registers (Parent_ID_FK, Player_ID_FK)
VALUES (“PA104″,”PL104”);
INSERT INTO Registers (Parent_ID_FK, Player_ID_FK)
VALUES (“PA105″,”PL105”);
INSERT INTO Registers (Parent_ID_FK, Player_ID_FK)
VALUES (“PA106″,”PL106”);
INSERT INTO Registers (Parent_ID_FK, Player_ID_FK)
VALUES (“PA107″,”PL107”);
PARENT has Parent_ID hasH PLAYER Player_10 Parent_LName Player_ID REGISTERS Parent_FName Player_FName FK Parent_ID Parent_Phone Player_LName Player_ID Parent_Street Player_Age Parent_City Team_ID Parent_State Parent_zip Includes COACH TEAM Coach_ID Team_ID Coach_FName >O_ Coached By — Team_Name Coach_LName Team_Colors Coach_Phone Team_ID Show transcribed image text PARENT has Parent_ID hasH PLAYER Player_10 Parent_LName Player_ID REGISTERS Parent_FName Player_FName FK Parent_ID Parent_Phone Player_LName Player_ID Parent_Street Player_Age Parent_City Team_ID Parent_State Parent_zip Includes COACH TEAM Coach_ID Team_ID Coach_FName >O_ Coached By — Team_Name Coach_LName Team_Colors Coach_Phone Team_ID
Expert Answer
Answer to Write a stored procedure for the Youth League Database. SQL Script containing the Stored Procedure for the Youth League …