Are there classification metrics for the elaboration of SQL queries?

Asked

Viewed 150 times

2

Doubt

Have some method to sort in difficulty levels, an SQL query in easy, medium or difficult? This question may be a little subjective, but speculating may be a good option.

Context

I was answering some SQL questions on the hackerrank and I came across a question "basic" about JOIN considered "easy", but it took me a while to do and I would like to share the experience.

Here is the question: Given a table CITY and COUNTRY, make a query that shows the sum of the population where the continent is "Asia". Ask English inserir a descrição da imagem aqui

My resolution

  1. Find cities related to the country "Asia".

    SELECT code FROM country WHERE continent = "Asia";

  2. Map filtered cities in the COUNTRY table with the CITY table using JOIN.

    SELECT code, countrycode, population 
  FROM consulta1 JOIN city ON consulta1.code = city.countrycode;

  3. Add the population of the filtered query

    SELECT SUM(population) FROM consulta2;

Join all the sub-queries, using ALIAS - AS, we arrived at the answer of the question.

Answer

SELECT SUM(POPULATION) FROM 
    (SELECT tab1.CODE,CITY.COUNTRYCODE,CITY.POPULATION FROM 
        ( SELECT CODE FROM COUNTRY WHERE CONTINENT = "Asia") AS tab1 
            JOIN CITY ON tab1.CODE = CITY.COUNTRYCODE) AS tab2;
mysql sql database
  • 1

    What exactly are you wondering? If the way you did it is correct? If there are alternatives? The difficulty level depends on your knowledge of the SQL commands to get the desired result.

  • If you were to rate the difficulty of this question what would it be? Would your answer be based on what arguments? Imagine you had to make a sort of classification like this for a test

  • That is exactly what I said in the previous comment: the level will depend on your knowledge. For you, as you reported, it was a little more complicated and may not be basic level. For me, it is basic. Technically it can be said that this question is easy by using the basic commands to agregamento and junção that would be the commands SUM() and JOIN, which in that case was used subquerys in place of junctions.

  • That would be a good candidate for answer =]

  • @Jonathasb.C. , I think talking about difficulty connecting the syntax emphasis would be topical to the goal

  • @Jeffersonquesado to specify the difficulty to this SQL example demonstrated in the question is coherent to the scope of Sopt ?

  • @Jonathasb.C. I believe that yes, perfect for the goal, with the tag "support". I was thinking for a while to open a formatting question like "proposal-to-Faq", because I didn’t see anything in the FAQ specifically talking about formatting, and this "error", believe me, is very common and I spend a lot for it in many-language answers

  • According to the above comment, it is difficult to objectively answer this type of question. That said, the problem can be reduced to a sum and a JOIN simple, without GROUP BY, HAVING, Subqueries, analytical functions, UNION, PIVOT, recursive queries or more complicated constructions. Personally I would also classify the problem as easy. It is perfectly natural but that a first attempt takes longer; it is equally natural that you end up arriving at a more complicated solution than expected. It’s part of the learning process.

Show 3 more comments

1 answer

0

You tried to:

SELECT SUM(CITY.POPULATION) FROM
    CITY INNER JOIN COUNTRY ON (CITY.COUNTRYCODE = COUNTRY.CODE)
        WHERE COUNTRY.CONTINENT = "Asia");

Remember that SQL is a declarative language, don’t think like that: first does it and then this other and finally this other thing.

You have an ) extra at the end of the command. Try:

SELECT SUM(CITY.POPULATION) FROM
    CITY INNER JOIN COUNTRY ON (CITY.COUNTRYCODE = COUNTRY.CODE)
        WHERE COUNTRY.CONTINENT = 'Asia';

  • I tried your solution on the hackerrank site and says it is wrong =(, take a look at the link I left in the question and let us know if you find a simpler solution.

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.