Write a query which will return all cities more customers than the average number of customer of all cities. For each such city, return the country name, the city name and the number of customers. Order the result by country name ascending.
Table definitions and a data sample are given below.
Table: country
column name | column type | key / NULL |
id | int | PK |
country_name | varchar(128) |
Table: city
column name | column type | key / NULL |
id | int | PK |
city_name | varchar(128) | |
postal_code | varchar(16) | |
country_id | int | FK |
city.country_id references country.id
Table: customer
column name | column type | key / NULL |
id | int | PK |
customer_name | varchar(255) | |
city_id | int | FK |
customer_address | varchar(255) | FK |
contact_person | varchar(255) | N |
varchar(128) | ||
phone | varchar(128) |
customer.city_id references city.id
Table: country
id | country_name |
1 | Austria |
2 | Germany |
3 | United Kingdom |
Table: City
id | city_name | postal_code | country_id |
1 | Wien | 1010 | 1 |
2 | Berlin | 10115 | 2 |
3 | Hamburg | 20095 | 2 |
4 | London | EC4V 4AD | 3 |
Table: Customer
id | customer_name | city_id | customer_address | contact_person | Phone | |
1 | Drogerie Wien | 1 | Deckergasse 15A | Emil Steinbach | emil@drogeriewein.com | 094234234 |
2 | Costemitc Store | 4 | Watling Street 347 | Jeremy Corbyn | jeremy@c-store.org | 093923923 |
3 | Kosmetikstudio | 3 | Rothenbaumchaussee 53 | Willy Brandt | willy@kosmetikstudio.com | 0941562222 |
4 | Neue Kosmetik | 1 | Karlspatz 2 | NULL | info@neuekosmetik.com | 094109253 |
5 | Bio Kosmetik | 2 | Motzrabe 23 | Clara Zetkin | clara@biokomsetik.org | 093825825 |
6 | K-Wien | 1 | Karnter Strabe 204 | Maria Rauch-Kallat | maria@kwien.org | 093427002 |
7 | Natural Cosmetics | 4 | Clerkenwell Road 14B | Glenda Jackson | glen.j@natural-cosmetics.com | 093555123 |
8 | Kosmetik Plus | 2 | Unter den Linden 1 | Angela Merkel | angela@k-plus.com | 094727727 |
9 | New Line Cosmetics | 4 | Devonshire Street 92 | Oliver Cromwell | oliver@nlc.org | 093202404 |
The first line of the result should be:
counry_name city_name count
-> Austria Wien 3