SQL Exercise: Country ID and number of cities in country has
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 | ...... | 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:
Go to:
PREV : Jobs done by two or more for more than 300 days.
NEXT : Find employees managed by the manager.
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Contribute your code and comments through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.