Sequel. SQL Zoo basics
SQL injections are a very common class of attack. To get a basic foundation we lets quickly refresh some of the essentials of SQL by solving some tasks in SQL Zoo.
0 - SELECT BASICS
The first tasks wants us to modify the query to show the population of Germany. In this case it is as simple as changing SELECT population FROM world WHERE name = 'France' to SELECT population FROM world WHERE name = 'Germany' If we did everything correctly we should be given a population of 80716000.
The second task wants us to show the population for Sweden, Norway and Denmark. This is again pretty simple, we just change the country-list from SELECT name, population FROM world WHERE name IN ('Brazil', 'Russia', 'India', 'China'); to SELECT name, population FROM world WHERE name IN ('Sweden', 'Norway', 'Denmark');
The third task asks us to show the countries which have a population that matches a certain range. This is again a rather straight forward modification where we change SELECT name, area FROM world WHERE area BETWEEN 250000 AND 300000 to SELECT name, area FROM world WHERE area BETWEEN 200000 AND 250000
2 - SELECT from World
The first task in this chapter is to simply observe the output of the following SQL command SELECT name, continent, population FROM world which shows the name, continent and population of all countries.
The second task asks us to show the name of countries which have a population of at least 200 million. We can simply change the operator and value in the existing query from SELECT name FROM world WHERE population = 64105700 to SELECT name FROM world WHERE population >= 200000000
In the third task we are asked to show name and per capita GDP of the countries with a population of at least 200 million. We can utilize the previous query and modify field names after the SELECT keyword. We will have to divide GDP by Population to output the desired value. The final query will look like this SELECT name, (gdp/population) AS 'per capita GDP' FROM world WHERE population >= 200000000
Task four wants us to show the name and population in millions for all countries on the continent of South America. We can take a similar approach as in the previous task and create a custom field. The final result should look something like this SELECT name, (population/1000000) AS 'population (M)' FROM world WHERE continent = 'South America'
Task five requires the name and population of France, Germany, and Italy. This requires a similar query as an earlier task, the solution will look like this SELECT name, population FROM world WHERE name IN ('France', 'Germany', 'Italy')
Task six needs us to show all countries which contain the word "United" in their name. We can do this with the following query SELECT name from world WHERE name LIKE '%United%'.
in task seven we need to list the countries that are larger than 3M sq km or have a population of over 250M. Lets use an OR statement in our query to easily check for either of those parameters. The solution will look like this SELECT name, population, area FROM world WHERE population > 250000000 OR area > 3000000
Task eight requires an exclusive OR structure to show countries of more than 3M sq km or a population of over 250M, but not both. We can solve this with same statement as the previous task but we have to change out the OR for XOR SELECT name, population, area FROM world WHERE population > 250000000 XOR area > 3000000.
In task nine we have to apply rounding to 2 decimal places for the GDP and population in millions (for countries in South America). We can use some of the earlier queries as a starting point and make use of the ROUND function for the rounding. The solution will look something like this SELECT name, ROUND(population/1000000,2) AS 'population (M)', ROUND(gdp/1000000000, 2) AS 'GDP (B)' FROM world WHERE continent='South America'.
Task ten wants the name and per-capita GDP for countries with a GDP of at least one trillion, but the GDP has to be rounded to the nearest 1000. We can do this with the following statement SELECT name, ROUND(gdp/population,-3) AS 'per-capita GDP' FROM world WHERE gdp >= 1000000000000. This is not very different from previous solutions, especially since we can use a negative parameter in the ROUND function.
Task 11 requires for us to show the name and capital where the name and the capital have the same number of characters. With the help of the LENGTH function we can create the following query to solve this problem SELECT name, capital FROM world WHERE LENGTH(name) = LENGTH(capital)
Task 12 wants us to show the name and the capital where the first letters of each match. We shall not include countries where the name and the capital are the same word. With the help of LEFT we can easily grab the first letter, and the <> operator can be used as NOT EQUALS. The final statement will look like this SELECT name, capital FROM world WHERE LEFT(name,1) = LEFT(capital,1) AND name <> capital
Task 13 is the final task and asks us to find the countries which have all the vowels (a e i o u) and no spaces in their name. The previous tasks provided us with all the knowledge and tools we need. The solution can be achieved with the following statement SELECT name FROM world WHERE name LIKE '%a%' AND name LIKE '%e%' AND name LIKE '%i%' AND name LIKE '%o%' AND name LIKE '%u%' AND name NOT LIKE '% %'. We could also use REGEX to solve this.
