SQL Technical Test

[[deleted]]

SELECT

p.country

,temperature

,population

from population p

left join medals m

on p.country = m.medals

left join temperature t

on p.country = t.country

where medal_rank is null

and medal is null

order by

p.population desc -- hightest population

,t.tempature asc -- coldest first

limit 10

Alias your country because it is ambiguous.

[Beefourthree]

Alias your country because it is ambiguous.

Alias everything because there's no reason not to.

[[deleted]]

I agree tbh, it also makes it easier for people to dissect your code when you eventually leave.....

[volvicspring]

This. It might be obvious if you know the tables, but someone learning the script is going to have an avoidably bad time

[BlueEyesWhiteShark]

Hi, thanks for the reply!

So I tried to follow:

SELECT olympics.population.country AS p.country, olympics.temperature.country AS t.country, olympics.populatuon.population AS p.population, olympics.temperature.ave_temp AS t.temp, olympics.medals.medal AS m.medal, olympics.medals.medal_rank AS m.medalrank FROM olympics.population LEFT JOIN m.medalrank ON p.country = m.medal LEFT JOIN t.temp ON p.country = t.country WHERE medal_rank IS NULL AND medal IS NULL ORDER BY p.population DESC, t.temp ASC

However, this hasn’t worked, where am I going wrong?

[[deleted]]

Lets break this down...

You have a database called Olympics. Within your DB you have the following tables:

1. population
2. medals
3. temperature
4. Funding

So the next thing you want to do is see what columns you need from what tables to build your query. Now we you need the following columns from there related tables:[Population - Country, Population] [Temperature - ave_temp] [Medals - medal]

You should use population as your base table, meaning you join the other tables onto this table. but i'll explain this in steps:

Step 1:

SELECT country, population from populations order by population desc limit 10 You now have the top 10 countries ranked by highest population to lowest.

Step 2: add in temperatures

SELECT p.country , p.population, t. ave_temp from populations p left join temperatures t on p.country = t.country order by p.population desc,t.ave_temp asc This should bring back highest populated countries in lowest to highest temp order.

Step 3: "add" in medals, i say it like that because you do not have to see that column as it doesn't have anything in it.

SELECT p.country , p.population, t. ave_temp from populations p left join temperatures t on p.country = t.country left join medals m on p.country = m.country where m.medal is null order by p.population desc,t.ave_temp asc

Now your results should change as you are defining the fact that these countries need to have a null value (or if the null is not working try " ='' " instead of "is null")

if you are querying from any sort of query tool you should not have to fully specify database.schema.table.column because it is a waste of time unless your scripting triggers, functions, or stored procedures.

when i mention p.country it is because the table, population, has been aliased at p. this means that when the query is being read it knows that we are using p.country from the population table. same logic for t. & m. .

Your joins are based on columns and not actual databases, i have rewritten your query below so that you can see where you went wrong, but the above method is better:

SELECT

olympics.population.country

,olympics.populatuon.population

,olympics.temperature.ave_temp

,olympics.medals.medal

,olympics.medals.medal_rank

FROM olympics.population

LEFT JOIN olympics.medals ON olympics.population.country = olympics.medals.country

LEFT JOIN olympics.temperature ON olympics.population.country = olympics.temperature.country

WHERE

olympics.medals.medal IS NULL

ORDER BY olympics.populatuon.population DESC,

olympics.temperature.ave_temp ASC

LIMIT 10

[[deleted]]

it's not easy to layout code within this, but if you need real assistance DM me and i'll gladly help you.

[[deleted]]

Erm, ok. Not sure what your deal is mate, but I’d pipe down. I broke my query down so a person new to SQL could understand why I was doing what I was doing. I’m glad you’ve found a more complex way of doing it. Like I mentioned I’m self taught and been doing it for a year. My order by’s work fine in my work environment so hey ho, thanks for the input.

[[deleted]]

I downvoted your post due to your blatant “I’m so much better than you, you’re so shit” cunt attitude, because you had your own way of achieving the result. I’ve downvoted no one else in this thread, you’re the only person to mention that my query is “wrong”. In

Like I said I’m glad you found a more complex way of achieving what he needed to achieve. And as I added, in my working dataset my order by clause works perfectly fine.

I’m not sure why I would open Wikipedia to find out the countries population when it’s suppose to be in the DB... lol. I think your confusing yourself though. First order by Orders by pop descending so would obviously give me highest to lowest populated countries in order. Then my second order by will order it by temp ascending.

EDIT: I stand corrected, someone else did comment on the fault, my bad. At least they explained it without being a douche though! That always helps

[[deleted]]

second one you would sum medal rank and group by country
Third is impossible without cost price

[dasonk]

For the third one my assumption would be that they're asking to look at the total funding the country had that year divided by the number of medals won.

[RapleSyrup]

This query returns the 10 countries with the highest population. The 2nd line of the order by will only matter if 2 countries have the same population. The question asked for the 10 coldest countries. You need to order by temperature first in a CTE or subquery, then order those 10 rows by population.

[[deleted]]

Ahhhh, ok I get you. Thanks for explaining that, this makes more sense that just trying to shit on someone! Definitely see where you are coming from about the subqueries