FOOTBALL-PLAYER(CodFP, Name,Surname, Address, CityOfBirth) TEAM(CodT, Name, Category,ConstitutionDate) AWARD(CodA, CodFP, DeliveryDate, DeliveryPlace, Type) PLAYER-TEAM(CodFP, CodT, StartingDate,EndingDate, NumPlayedMatches, NumGoals) For each team in which players living in Turin (attribute “City of birth”) have never played, find team name and category and, for each football player, code, surname, and total number of days of militancy in that team (computed as the difference between ending and starting dates) SELECT T.Name, T.Category, FP.CodFT, FP.Surname, FP.Name, SUM(EndingDate - StartingDate) FROM TEAM T, FOOTBALL-PLAYER FP, PLAYER-TEAM PT WHERE PT.CodFP = FP.CodFP AND PT.CodT = T.CodT AND PT.CodT NOT IN ( SELECT PT.CodT FROM PLAYER-TEAM PT, FOOTBALL-PLAYER FP WHERE PT.CodFP = FP.CodFP and CityOfBirth= "Torino" ) GROUP BY T.CodT, T.Name, T.Category, FP.CodFT, FP.Surname, FP.Name; this is the solution but please help me as i am not understanding anything. if there are any tips or tricks u recommend for these type of questions please tell me AND demonstrate them by solving it and reaching to this solution. if there are any youtube videos or websites or links that can extensively explain these type of questions please let me know as i have an exam soon and i urgently have to pass it in a few days ANYTHING would help please and thank you!
FOOTBALL-PLAYER(CodFP, Name,Surname, Address, CityOfBirth)
TEAM(CodT, Name, Category,ConstitutionDate)
AWARD(CodA, CodFP, DeliveryDate, DeliveryPlace, Type)
PLAYER-TEAM(CodFP, CodT, StartingDate,EndingDate, NumPlayedMatches, NumGoals)
For each team in which players living in Turin (attribute “City of birth”) have never played, find team name and category and, for each football player, code, surname, and total number of days of militancy in that team (computed as the difference between ending and starting dates)
SELECT T.Name, T.Category, FP.CodFT, FP.Surname, FP.Name, SUM(EndingDate - StartingDate)
FROM TEAM T, FOOTBALL-PLAYER FP, PLAYER-TEAM PT
WHERE PT.CodFP = FP.CodFP AND PT.CodT = T.CodT
AND PT.CodT NOT IN
(
SELECT PT.CodT
FROM PLAYER-TEAM PT, FOOTBALL-PLAYER FP
WHERE PT.CodFP = FP.CodFP and CityOfBirth= "Torino"
)
GROUP BY T.CodT, T.Name, T.Category, FP.CodFT, FP.Surname, FP.Name;
this is the solution but please help me as i am not understanding anything. if there are any tips or tricks u recommend for these type of questions please tell me AND demonstrate them by solving it and reaching to this solution. if there are any youtube videos or websites or links that can extensively explain these type of questions please let me know as i have an exam soon and i urgently have to pass it in a few days ANYTHING would help please and thank you!
Step by step
Solved in 4 steps
