Question No 1:
A German publication recently broke news that Plans for top European clubs to form a breakaway league of their own starting from 2021 are under consideration, this has made concept of an elite European 'Super League' becoming more achievable. Based on speculations participants are Real Madrid, Barcelona, Manchester United, Chelsea, Arsenal, Manchester City, Liverpool, Paris Saint-Germain, Juventus, AC Milan and Bayern Munich. The first two clubs are Spanish. Next 5 clubs are English. Paris Saint-Germain belongs to France, Juventus and AC Milan are Italian clubs
while Bayern Munich is a German club based in Munich. The individual details of clubs are as follows:
Real Madrid plays at Santiago Bernabeu. The capacity of stadium is 81,044. The club was founded in 1902. The club is managed by Zinedine Zidane.
Barcelona plays at Camp Nou. The capacity of stadium is 99,354. The club was founded in 1899. Quique Setién is manager of Barcelona.
Manchester United plays at Old Trafford. The capacity of stadium is 75,731. The club was founded in 1878. Jose Mourinho manages Manchester United.
Chelsea plays at Stamford Bridge. The capacity of stadium is 41,841. The club was founded in 1905. Maurizio Sarri is manager of Chelsea.
Arsenal plays at Emirates Stadium. The capacity of stadium is 59,867. The club was founded in 1886. Unai Emery is manager of Arsenal.
Manchester City plays at Etihad Stadium. The capacity of stadium is 55,097. The club was founded in 1880. Pep Guardiola is manager of Manchester City.
Liverpool plays at Anfield. The capacity of stadium is 54,074. The club was founded in 1892. Jürgen Klopp manages Liverpool.
Paris Saint-Germain plays at Parc des Princes. The capacity of stadium is 47,929. The club was founded in 1970. Thomas Tuchel is manager of Paris Saint-Germain.
Juventus plays at Santiago Allianz Stadium. The capacity of stadium is 41,507. The club was founded in 1897. The club is managed by Andrea Pirlo
Page 2 of 3
Ac Milan plays at Santiago San Siro. The capacity of stadium is 80,018. The club was founded in 1899. Gennaro Gattuso manages the club.
Bayern Munich plays at Allianz Arena. The capacity of stadium is 75,000. The club was founded in 1900. The club is managed by Niko Kovač
Write down queries to perform following tasks
1. Create a Table named “European_Super_League”. Give each club a unique number and not null constraint.
2. Insert the data of each club. All details mentioned above need to be properly placed with in club. The names of fields should be Club_name, Stadium, Established_date, Stadium_capacity, Manger.
3. Find the biggest stadium with respect to capacity.
4. Which stadium has the smallest capacity?
5. Which is the oldest established team?
6. How many teams have a capacity between 50,000 and 65,000?
7. How many teams have a stadium capacity of at least 55,000?
8. How many clubs were founded in the 19th century?
9. Find average Stadium capacity.
10. Following changes have been made at managerial level of club:
Ronald Koeman has been appointed as Barcelona manager
Unai Emery is replaced by Mikel Arteta
Stefano Piolo is the new manager of AC Milan
Jose Mourinho has been sacked as is replaced by Ole Gunnar Solskajer
Hans-Dieter Flick has replaced Niko Kovac as manager of Bayern Munich
Upgrade these changes in your table. Write down queries.
11. Add field to your table named as City. The records are given as follows
Arsenal FC and Chelsea FC play in London. Manchester United FC and Manchester City FC play in Manchester. Real Madrid CF and FC Barcelona are Spanish club, they play in Madrid and Barcelona respectively. Paris Saint Germain is based in Paris, While Juventus FC and Ac Milan play in Turin and Milan respectively.
12. Delete clubs belonging from Manchester.
13. Add a team named Borussia Dortmund. Team plays at Signal Iduna Park. Capacity of their stadium is 81,365. The club was founded in 1909. Erdin Terzic is the manager of club. The club is based in Dortmund.
14. Find the total number of teams.
15. Create a view named as Clubs_formed_after_1880 which includes all teams founded
after 1880. View must show club name, manager name, established date as its field and corresponding records as well
16. Display names of stadiums but cities can belong from London or Liverpool.
17. Display names of clubs that belong from cites of England and Germany.