Are there classification metrics for preparing SQL queries?

Question

Are there classification metrics for preparing SQL queries?

Navigation

#1 by (0 votes)

2

Doubt

Is there any method for sorting in difficulty levels, an SQL query in easy, medium, or difficult ? This question may be a bit subjective, but speculating may be a good option.

Context

I was responding to some SQL issues on the hackerrank site and came across a " basic" question > "about JOIN considered" easy ", but I took some time to do it and would like to share the experience.

Here's the question: Given a CITY and COUNTRY table, make a query that shows the sum of the population where the continent is "Asia." ask english

Myresolution

Findthecitiesforthecountry"Asia".


SELECT code FROM country WHERE continent = "Asia";
Map the 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;
Add the population of the filtered query


SELECT SUM(population) FROM consulta2;

If you join all sub queries , using ALIAS - AS, we come to the answer.

Response

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

asked by anonymous 21.11.2017 / 21:10

1 answer

Markdown - Links in page How to extract the face of an RG document from a scanned image

score 0 · Answer 1

You tried:

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, do not think this way: first do this and then this one and finally this something else.

You have one more 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';