Question

I have this answer:

SELECT c.Code, count(*) FROM country c JOIN countrylanguage cl ON c.Code = cl.CountryCode GROUP BY cl.CountryCode HAVING COUNT(*) > 1 LIMIT 10;

From a previous question I asked which was:

Using the database you installed from the link below, provide an example query using both a group by clause and a having clause. Show no more than ten rows of your query result. Discuss if the query you wrote can be rewritten without those clauses.

The sample database that this is based off of can be found at https://dev.mysql.com/doc/index-other.html under example databases, world_x database.

******************************

What I need Now is:

Could you please explain the query that is written above as well as if it can be re-written without the clauses and why?

Thank you

Answer 1

This query returns the country and the number of languages spoken for each country if and only there are atleast 2 different languages spoken. The LIMIT 10 limits the result to 10 rows.

No, the query cannot be written without these clauses since we want to group similar countries together in order to sum up the number of languages spoken in each country. Without the GROUP BY clause, it is impossible to group the countries together.