w3resource

SQL SORTING and FILTERING on HR Database: Display the country ID and number of cities in that country we have

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 has. Return country ID and number of cities.

Sample table : locations


Sample Solution:

SELECT country_id,  COUNT(*)  
	FROM locations 
		GROUP BY country_id;

Sample Output:

 country_id | count
------------+-------
 CH         |     2
 "          |     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)

Relational Algebra Expression:

Relational Algebra Expression: Display the country ID and number of cities in that country we have.

Relational Algebra Tree:

Relational Algebra Tree: Display the country ID and number of cities in that country we have.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the country ID and number of cities in that country we have - Duration

Rows:

Query visualization of Display the country ID and number of cities in that country we have - Rows

Cost:

Query visualization of Display the country ID and number of cities in that country we have - Cost

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find each job ids where two or more employees worked for more than 300 days. Return job id.
Next:From the following table, write a SQL query to count the number of employees worked under each manager. Return manager ID and number of employees.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

Ref: https://bit.ly/3xzB9dS