I\'m having issues completing these 4 steps in my sql database. The database que

Question

I'm having issues completing these 4 steps in my sql database. The database query is below the 4 steps. MONEY is currency in this database

1.Insert data into each of your database tables. Be sure to insert at least 5 golfers, at least 3 teams and at least 10 donors.   Also be sure to insert at least 1 donor who is sponsoring multiple golfers for different pledge amounts.

2.Write a query to return the total amount pledged for each sports team. Remember, the pledge amount is per hole played and the goal is to play 100 holes of golf. Show the sports team name, level, and gender. Order the results by total pledged amount from highest to lowest amount.

3.Write a query to return the total amount pledged for each golfer. List the golfer name, spots team that he/she is playing for, and the total amount pledged.   Order the results to show the golfers with the most to the least amount pledged.

4.Write a query to list the sponsors for each sports team. List the team sport, team level, and team gender. List the sponsor name and total amount pledged for the team.   Order the results by Team ID.

CREATE TABLE TTeams

(

intTeamID INTEGER NOT NULL

,intSportID INTEGER NOT NULL

,intTeamLevelID INTEGER NOT NULL

,intTeamGenderID INTEGER NOT NULL

,strTeamName VARCHAR(50) NOT NULL

,CONSTRAINT TTeams_PK PRIMARY KEY ( intTeamID )

)

CREATE TABLE TSports

(

intSportID INTEGER NOT NULL

,strSportName VARCHAR(50) NOT NULL

,CONSTRAINT TSports_PK PRIMARY KEY ( intSportID )

)

CREATE TABLE TPlayers

(

intPlayerID INTEGER NOT NULL

,intTeamID INTEGER NOT NULL

,intShirtSizeID INTEGER NOT NULL

,strFirstName VARCHAR(50) NOT NULL

,strLastName VARCHAR(50) NOT NULL

,strEmail VARCHAR(50) NOT NULL

,intDonorID INTEGER NOT NULL

,CONSTRAINT TPlayers_PK PRIMARY KEY ( intPlayerID )

)

CREATE TABLE TDonors

(

intDonorID INTEGER NOT NULL

,strFirstName VARCHAR(50) NOT NULL

,strLastName VARCHAR(50) NOT NULL

,strEmail VARCHAR(50) NOT NULL

,CONSTRAINT TDonors_PK PRIMARY KEY ( intDonorID )

)

CREATE TABLE TDonorPlayers

(

intDonorPlayerID INTEGER NOT NULL

,intDonationID INTEGER NOT NULL

,intPlayerID INTEGER NOT NULL

,intDonorID INTEGER NOT NULL

,dtmPlayDate DATETIME NOT NULL

,CONSTRAINT TDonorPlayers_PK PRIMARY KEY ( intDonorPlayerID)

)

CREATE TABLE TDonations

(

intDonationID INTEGER NOT NULL

,intDonorPlayerID INTEGER NOT NULL

,monPledgeRecieved Money NOT NULL

,intPaymentMethodID INTEGER NOT NULL

,monPricePerHole Money NOT NULL

,dtmDonationDate DATETIME NOT NULL

,CONSTRAINT TDonations_PK PRIMARY KEY ( intDonationID,intDonorPlayerID )

)

CREATE TABLE TPaymentMethod

(

intPaymentMethodID INTEGER NOT NULL

,strPaymentType VARCHAR(500) NOT NULL

,CONSTRAINT TPaymentMethod_PK PRIMARY KEY ( intPaymentMethodID )

)

CREATE TABLE TTeamLevel

(

intTeamLevelID INTEGER NOT NULL

,strTeamLevel VARCHAR(500) NOT NULL

,CONSTRAINT TTeamLevel_PK PRIMARY KEY ( intTeamLevelID )

)

CREATE TABLE TTeamGender

(

intTeamGenderID INTEGER NOT NULL

,strTeamGender VARCHAR(500) NOT NULL

,CONSTRAINT TTeamGender_PK PRIMARY KEY ( intTeamGenderID )

)

CREATE TABLE TShirtSize

(

intShirtSizeID INTEGER NOT NULL

,strShirtSize VARCHAR(500) NOT NULL

,CONSTRAINT TShirtSize_PK PRIMARY KEY ( intShirtSizeID )

)

-- --------------------------------------------------------------------------------------------------------------------------

-- Step #1.2: Identify and Create Foreign Keys

-- --------------------------------------------------------------------------------------------------------------------------

-- # Child Parent Column(s)

-- ------ ------- ---------

--1

ALTER TABLE TTeams ADD CONSTRAINT TTeams_TSports_FK

FOREIGN KEY (intSportID) REFERENCES TSports (intSportID)

--2

ALTER TABLE TTeams ADD CONSTRAINT TTeams_TTeamLevel_FK

FOREIGN KEY (intTeamLevelID) REFERENCES TTeamLevel (intTeamLevelID)

--3

ALTER TABLE TTeams ADD CONSTRAINT TTeams_TTeamGender_FK

FOREIGN KEY (intTeamGenderID) REFERENCES TTeamGender (intTeamGenderID)

--4

ALTER TABLE TDonations ADD CONSTRAINT TDonations_TDonorPlayers_FK

FOREIGN KEY (intDonorPlayerID) REFERENCES TDonorPlayers (intDonorPlayerID)

Explanation / Answer

Hi, please find the sqls below. Please provide feedback and upvote the answer. Thanks

insert into TDonors values
(1,'John','Hart','John.Hart@abc.com')
insert into TDonors values
(2,'John2','Hart2','John.Hart2@abc.com');
insert into TDonors values
(3,'John3','Hart3','John.Hart3@abc.com');
insert into TDonors values
(4,'John4','Hart4','John.Hart4@abc.com');
insert into TDonors values
(5,'John5','Hart5','John.Hart5@abc.com');
insert into TDonors values
(6,'John6','Hart6','John.Hart6@abc.com');
insert into TDonors values
(7,'John7','Hart7','John.Hart7@abc.com');
insert into TDonors values
(8,'John8','Hart8','John.Hart8@abc.com');
insert into TDonors values
(9,'John9','Hart9','John.Hart9@abc.com');
insert into TDonors values
(10,'John10','Hart10','John.Hart10@abc.com');

insert into TSports values
(1,'Golf');
insert into TSports values
(2,'Cricket');
insert into TSports values
(3,'Football');

insert into TPlayers values
(1,1,4,'ABC','XYZ','abc.xyz@com',1);
insert into TPlayers values
(2,1,4,'ABC1','XYZ1','abc.xyz1@com',1);
insert into TPlayers values
(3,2,3,'ABC3','XYZ3','abc.xyz3@com',1);
insert into TPlayers values
(4,2,3,'ABC4','XYZ4','abc.xyz4@com',1);
insert into TPlayers values
(5,2,3,'ABC5','XYZ5','abc.xyz5@com',4);

insert into TTeams values
(1,1,1,1,'Team1');
insert into TTeams values
(2,1,1,1,'Team2');

insert into TDonorPlayers values
(2,1,2,1,'03-AUG-2017');
insert into TDonorPlayers values
(3,2,3,1,'01-AUG-2017');

insert into TDonations values
(1,1,'10000',1,'1000','1-AUG-2017');
insert into TDonations values
(2,2,'250000',2,'2000','2-AUG-2017');
insert into TDonations values
(3,3,'50000',3,'2500','5-AUG-2017');

insert into TTeamLevel values
(1,'1');
insert into TTeamLevel values
(2,'2');
insert into TTeamGender values
(1,'M');
insert into TTeamGender values
(2,'F');
insert into TShirtSize values
(1,'Small');
insert into TShirtSize values
(2,'Medium');
insert into TShirtSize values
(3,'Large');
insert into TShirtSize values
(4,'XL');
insert into TShirtSize values
(5,'XXL');

2. select amt,b.intteamid,tt.strTeamName,tl.strTeamLevel,tg.strTeamGender from (
select sum(amount) as amt,a.intteamid from (
select (td.MONPRICEPERHOLE*100) as amount,tp.intteamid from tdonations td,tdonorplayers tdp,tplayers tp
where td.intdonorplayerid=tdp.intDonorPlayerID and tdp.intPlayerID = tp.intPlayerID) a
group by a.intteamid order by sum(amount) desc
) b, TTeams tt,TTeamLevel tl, TTeamGender tg
where b.intTeamID =tt.intTeamID and tt.intTeamLevelID = tl.intTeamLevelID and tt.intTeamGenderID =tg.intTeamGenderID ;

3.select tp.strFirstName ,tp.strLastName ,tt.strTeamName,(td.MONPRICEPERHOLE*100) as amount from tdonations td,tdonorplayers tdp,tplayers tp,TTeams tt
where td.intdonorplayerid=tdp.intDonorPlayerID and tdp.intPlayerID = tp.intPlayerID and tp.intteamid=tt.intteamid and tt.intsportid=1
order by amount asc;
4. select amt,b.intteamid,tl.strTeamLevel,tg.strTeamGender,td.strFirstName, td.strLastName from (
select sum(amount) as amt,a.intteamid,a.intdonorid from (
select (td.MONPRICEPERHOLE*100) as amount,tp.intteamid,tdp.intdonorid from tdonations td,tdonorplayers tdp,tplayers tp
where td.intdonorplayerid=tdp.intDonorPlayerID and tdp.intPlayerID = tp.intPlayerID) a
group by a.intteamid,a.intdonorid order by sum(amount) desc
) b, TTeams tt,TTeamLevel tl, TTeamGender tg, TDonors td
where b.intTeamID =tt.intTeamID and tt.intTeamLevelID = tl.intTeamLevelID and tt.intTeamGenderID =tg.intTeamGenderID
and b.intdonorid=td.intdonorid order by b.intteamid;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.