This is my very first question on Quora. Thanks for any recommendations , solutions and remarks. I was not provided with many specifics and data, this is a assignments where one must use his or her imagination to fill in particular data and variables. What counts is the correct logic, approach and covering possibilities. Your help is highly appreciated! Thank you!

Country and Continent table

Required Visual Result for Question 3

Questions:

1. Write a query that would select all countries with GDP of more than 1 000 000 000 USD
2. Write a query that would return all countries in Europe (specifically) with GDP of more than 1 000 000 000 USD
3. Write a query that lists all continents with GDP per continent (as the sum of the GDP of all countries). Each country belong to one continent only. For what result should look like - please resort to "Required Visual Result for Question 3" image.

My Solutions:

1. select * from countries where GDP > 1000000000
2. select * from countries where continent_id = 2 and GDP>1000000000;
3. select sum(GDP) from countries where continent_id = 4;

However, here in 3) I can only have the GDP sum displayed, and do not know how to have the continent's name on the left side as well. Please, if possible, assist with having the continent's name displayed and then right next to it and on the right handside - the relevant GDP sum.

CodePudding user response：

Welcome!

Your image shows the tables as country and continent but your queries refer to countries? So which is it please?

On the basis your image is correct but the queries are wrong then number 3 would be as below:

With no 3 your currently only going to get the some for the continent with the ID of 4.

select sum(GDP) from country where continent_id = 4;

So what you want to do is remove the WHERE and the GROUP BY continent_id to give you 1 result per continent.

select sum(GDP) from country where 1 GROUP BY continent_id

No to get the continent name included in your results you can use the JOIN syntax.

In this instance you want all your records from country and just the records from continent that match your join condition which will be the continent_id from country and the id from the continent table.

SELECT 
`continent`.`name`, sum(`GDP`) 
FROM `country` 
LEFT JOIN `continent` 
ON `country`.`continent_id` = `continent`.`id` 
GROUP BY `continent_id` 
ORDER BY `continent`.`name` ASC;

That should give you the results 1 per continent as required.

I've specified the table names as its clearer how to target specific columns from each table.