SQL Exercise: Country ID and number of cities in country has
SQL SORTING and FILTERING on HR Database: Exercise-25 with Solution
25. From the following table, write a SQL query to count the number of cities in each country. Return country ID and number of cities.
Sample table : locations+-------------+------------------------------------------+-------------+---------------------+-------------------+------------+ | LOCATION_ID | STREET_ADDRESS | POSTAL_CODE | CITY | STATE_PROVINCE | COUNTRY_ID | +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+ | 1000 | 1297 Via Cola di Rie | 989 | Roma | | IT | | 1100 | 93091 Calle della Testa | 10934 | Venice | | IT | | 1200 | 2017 Shinjuku-ku | 1689 | Tokyo | Tokyo Prefecture | JP | | 1300 | 9450 Kamiya-cho | 6823 | Hiroshima | | JP | | 1400 | 2014 Jabberwocky Rd | 26192 | Southlake | Texas | US | | 1500 | 2011 Interiors Blvd | 99236 | South San Francisco | California | US | | 1600 | 2007 Zagora St | 50090 | South Brunswick | New Jersey | US | | 1700 | 2004 Charade Rd | 98199 | Seattle | Washington | US | | 1800 | 147 Spadina Ave | M5V 2L7 | Toronto | Ontario | CA | | 1900 | 6092 Boxwood St | YSW 9T2 | Whitehorse | Yukon | CA | | 2000 | 40-5-12 Laogianggen | 190518 | Beijing | | CN | | 2100 | 1298 Vileparle (E) | 490231 | Bombay | Maharashtra | IN | | 2200 | 12-98 Victoria Street | 2901 | Sydney | New South Wales | AU | | 2300 | 198 Clementi North | 540198 | Singapore | | SG | | 2400 | 8204 Arthur St | | London | | UK | | 2500 | Magdalen Centre, The Oxford Science Park | OX9 9ZB | Oxford | Oxford | UK | | 2600 | 9702 Chester Road | 9629850293 | Stretford | Manchester | UK | | 2700 | Schwanthalerstr. 7031 | 80925 | Munich | Bavaria | DE | | 2800 | Rua Frei Caneca 1360 | 01307-002 | Sao Paulo | Sao Paulo | BR | | 2900 | 20 Rue des Corps-Saints | 1730 | Geneva | Geneve | CH | | 3000 | Murtenstrasse 921 | 3095 | Bern | BE | CH | | 3100 | Pieter Breughelstraat 837 | 3029SK | Utrecht | Utrecht | NL | | 3200 | Mariano Escobedo 9991 | 11932 | Mexico City | Distrito Federal, | MX | +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+
Sample Solution:
-- Selecting 'country_id' and counting the number of records for each 'country_id' group
SELECT country_id, COUNT(*)
-- Specifying the table to retrieve data from ('locations')
FROM locations
-- Grouping the results by 'country_id'
GROUP BY country_id;
Sample Output:
country_id | count ------------+------- CH | 2 MX | 1 US | 4 AU | 1 IT | 2 Ox | 1 JP | 2 CA | 2 DE | 1 NL | 1 SG | 1 CN | 1 UK | 2 IN | 1 BR | 1 (15 rows)
Code Explanation:
The said query in SQL which aggregates data from the 'locations' table, grouping by the "country_id" column. It returns the values for each group of locations in the same country as below:
country_id: the country id of the locations
COUNT(*): the number of locations in the group/country.
Therefore the final result will be a list of country_id values and the count of locations for each country.
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Contribute your code and comments through Disqus.
Previous SQL Exercise: Jobs done by two or more for more than 300 days.
Next SQL Exercise: Find employees managed by the manager.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics