PRODUCT/TIMES Query 1: Teams that are present at the same time in the league when Fulham where in the league. league{team} times league{team} where team = 'Fulham' Query 2: Teams that have won the FA cup and league (W/CC) Cup for the times when Fulham where in the league. fa_cup rename team as FAWinners times league_cup rename team as W/CCWinners times league{team} where team = 'Fulham' Note: Reordering the expressions on either side of the 'times' operator produces the same result - only the format (ordering of columns) of the returned table changes. SINCE PRODUCT Query 3: Since Coventry City where in the league, which teams have won the FA Cup league{team} where team = 'Coventry City' since fa_cup rename team as CupWinners Explanation: 1.Coventry in the league from 0 to 7. 2.Arsenal have won it in time 4 (hence 5 to 8 implies have known to won it before this period or time interval). 3.Arsenal have won it in 4, 8 and 9 but last two results neglected as Coventry were not in the league at this time. Query 4: When and what teams have won the FA Cup since Coventry City have been in the league. fa_cup rename team as CupWinners since league{team} where team = 'Coventry City' Explanation: 1.Same as above, Query 3 but this time we get the exact time points that are known for a team to win the FA Cup since Coventry City have been in the league. 2.Coventry in the league from 0 to 7. 3.Arsenal 5 to 5 implies have won it in time 4 Query 5: Teams that have been in the league and where they finished since Fulham have been in the league. league{team pos} since league{team} where team = 'Fulham' Explanation: 1.Fulham in the league from 8 to 9. 2.We do not get the result for all team at time 9 because our limiting / max time is 9. Data existing at only time 9 is known to hold at time 10. 3.Time implies when result is known to hold but actually happened at the time points before. UNTIL PRODUCT Query 6: Teams that have been in the league and where they finished until Fulham have been in the league. league{team pos} until league{team} where team = 'Fulham' Explanation: 1.Same as repeating Query 5 but replace 'Since' operator with 'Until'. 2.Notice that these results give both sets of time data (8 and 9) because they are within the on the boundary of our time and Until operator definition 3.Time implies when result holds but happened year after. JOIN Query 7: Where have teams finished in the league when they have won the FA cup league{team pos} join fa_cup Explanation: Everton finished 15th when they won the cup in time 1. Query 8: Who has done a double wining the FA Cup and the League title. league where pos = 1{team} join fa_cup Query 9: Who has done a treble - won all three competitions league where pos = 1{team} join fa_cup join league_cup Result Empty - No one has. SINCE JOIN Query 10: How have Blackburn performed or been finishing in the league since they won the league last league where pos = 1 and team = 'Blackburn'{team} past sincejoin league{team pos} OR if we switch the feature coalesce off then we use the following query: (league where pos = 1 and team = 'Blackburn'{team} past coalesce sincejoin league{team pos}) coalesce Both above queries (with and without specifying coalesce give the same result) Explanation: 1.Blackburn won the league in time 1. 2.Since then, they have known finished to finish at the following positions and time - therefore, the actual time is before the time given by the results. UNTIL JOIN Query 11: Where have Arsenal finished up to or until the time when they won the league at time 4 (year 1998) league where team = 'Arsenal' and pos = 1 AT 4 future {team} untiljoin league{team pos} Explanation: 1.Arsenal have finished 12, 5 and 3 up to and before winning their first title 2.Time implies when results hold but happened the year after. 3.By placing brackets and PREVIOUS at the end of the query gives actual time of finishing. PAST QUERY 12: All the teams that have taken part in the league championship some time in the past. league{team} past Explanation: Barnsley 5 to 9 implies that they were known to be in the competition for the years 5 to 9, 1995 to 2003 - they were relegated in 1994 and before this time have not taken past in the league championship (they played in division one) ALWAYS IN THE PAST Query 13: All the teams that have always been in the past in the league. league{team} alwayspast Explanation: 1.The query looks at all time points and finds for each time point: which teams have always been in the past from this time point. 2.Coventry 1 to 8 implies they have been known to be in the always in the past before these time points and hence exist from 0 to 7. Query 14: All the teams that have always been in the past from the last snapshot OR teams that have never been relegated and taken part always in the league. Explanation: Same as Query 13 but by simply placing 'at 9' at the end of the query gives us the teams that have always been in the league PREVIOUS Query 15: Which team has won the league title previously from the last snapshot or this year (2003). league WHERE pos = 1{team} PREVIOUS at 9 Explanation: 1.The reason why we get Arsenal is because our max time is 9. We are looking at all events that have taken part in the past from this year (2003) - storing of archives. 2.Man Utd winning the league holds for the current year and not previous year - will hold if we were looking back from the year 2004. FUTURE Query 16: All the teams that have taken part in the league championship some time in the future league{team} future Explanation: 1.Barnsley 0 to 3 implies that they were known to have been in the competition after the years 0 to 3, 1994 to 1997. 2.Similar to doing a past but this query gives us times such that the event will or has taken place in the future. ALWAYS IN THE FUTURE Query 17: All the teams that have always been in the Future in the league from time 0 OR the teams always known to exist in the future, in the league. league{team} alwaysfuture at 0 Explanation: Same as doing 'league always in the past at 9' NEXT Query 18: Which team has won the first league title after the time we started storing data - the year 1994. league WHERE pos = 1{team} NEXT at 0 Explanation: 1.The reason why we get Blackburn is because our min or first time is 0. We are looking at all events that have taken part in the future from and after this year (1994) - storing of archives. 2.Man Utd winning the league holds for the first ever year and after 1993, not after 1994 which is our first time instance or snapshot- will hold if we were considering data after the year 1993 (before 1994). UNION Query 19: Which teams and when have they won 1 of the 3 competitions. league where pos = 1{team} union fa_cup union league_cup SELECT Query 20: An interesting fact is that teams that normally end the season with 80 or more points, win the league title. Which teams have obtained 80 or more league points but did not finish first, won the league. league where pos <> 1 and points >= 80 OTHER INTERESTING QUERIES Query 21: which team has been promoted to the league. previous (next league{team}) minus league{team} Query 22: Which team has been relegated from the league. (previous league{team}) minus league{team} Query 23: which team have been known to be relegated and have managed to win the league title (future ((previous league{team}) minus league{team})) join league where pos = 1{team} Query 24: Since Watford have been in the premier league, which team have been know to be been relegated. league {team} where team = 'Watford' since next ((previous league{team}) minus league{team}) rename team as Relegated Query 25: Which teams have known to have won a competition before they have been relegated. ((previous league{team}) minus league{team}) join league where pos = 1{team} union fa_cup union league_cup