(Ex-Facebook & Best-Selling Data Science Author)
September 9, 2023
The game SQL Island is an entertaining way to practice SQL. Before we dive into SQL Island's answers and explanation, let's first cover how to play SQL Island in English.
To play SQL Island in English (rather than the original language German), you can use this special link which should make SQL Island automatically switch to English. Alternatively, you play SQL Island in English by clicking the menu icon and hitting the 4th button "Sprache wechsein" which means switch language:
Now that we've got the game in English, let's dive into a full-walkthrough of SQL Island's answers.
The first step in SQL Island is to use the statement to view a list of all the inhabitants:
Make sure to submit this code in the text-box at the bottom to progress the game.
Next, your asked to "see who is friendly on SQL Island". We'll use the SQL WHERE clause to filter the list of inhabitants based on the value to check whose friendly:
The above SQL query helps us avoid all "evil" inhabitants:
If for some reason you got an "Error: no such column: ‘friendly’" message in SQL Island when running the code, try re-running the SQL command with a double quotes like this: "friendly".
You can learn more about using to select only certain rows in this SQL filtering with WHERE tutorial.
Next in SQL Island, you need to find a friendly weaponsmith to forge me you a sword. We'll use the clause with to combine two conditions:
This yields us just one result: Ernest Perry.
FYI: If you've never seen boolean operators like in SQL before, be sure to complete this SQL AND tutorial with practice exercises.
To get results from other types of smiths – not just weaponsmiths – we'll use the SQL LIKE with % wildcard. This allows us to find all jobs ending with smith, because is a wildcard
Running the above query, you get 3 results – a weaponsmith, smith ,and blacksmith, thanks to the wildcard operator!
In SQL, the stands for SELECT all columns. Instead of the star, you can also address one or more columns (separated by a comma) and you will only get the columns you need.
This will return that our personid is 20.
To check how much gold we have in SQL Island, we can use the following SQL query:
You'll see that sadly we have zero gold 😔.
To collect ownerless items, we'll make a list of all items that don’t belong to anyone. These are represented as null values (ie. blank/missing values) in the owner column.
To accomplish this, we'll use the command:
Running the above query will give us the following output:
If you want to learn more about handling , do the practice exercises in this SQL NULL tutorial.
To collect all ownerless items, we can use the command:
This one's a bit more straightforward:
We can use both and statements to find all SQL Island inhabitants who are friendly dealers OR friendly merchants.
This should give us 3 results:
To give the ring and the teapot to personid 15, run this SQL query:
This will trigger the following command to run, getting you 120 more gold!
Unfortunately, that's still won't be enough gold to buy a sword, so we'll go to step #12.
Here's the code I'll run:
I'm Nick Singh – feel free to replace this code snippet with your own real name so the game recognizes you!
To find a baker to work for, we'll look at all SQL Island inhabitants with the job of baker. We'll list them all out, and use ‘ORDER BY gold’ to sort the results.
We used because this way the richest baker is on top! Running the query gives you Paul Bakerman as the baker giving the most gold.
If you want to learn more about , check out this ORDER BY tutorial.
After you've baked your bread, and used that gold to buy a sword, it's time to find a pilot to fly out of SQL Island.
To find a pilot is simple, simply query the inhabitants and filter on jobs equal to pilot:
After running this code, you'll be introduced to the concept, and the game will execute the following query on your behalf:
I don't think SQL Island does a great job at all of explaining the tricky concept of a SQL Join, so for a more detailed introduction on the [types of joins] read the article below.
You'll find that he's kidnapped, and that we need to find the chief of village Onionville.
To do this we'll join the village and inhabitant tables:
This will tell you that the name of the village chief is Fred Dix.
To count how many women there are in Onionville, we'll use the aggregate function. To find only women, we'll use .
Here's that query:
This should give you only one result.
Next, we'll cover the aggregate function to find the sum of gold of all bakers, dealers and merchants together:
Running this yields a sum of 4130.
To find much gold do different inhabitants have on average, depending on their state, we'll use the aggregate function, along with .
Here's the solution:
Here's the results:
Clearly, the only way to escape SQL Island is to mug the villains for their gold
If this is confusing to you, you can think of as similar to creating pivot tables in Excel or Google Sheets. More info on grouping by categories in SQL in this GROUP BY tutorial.
Not sure what's Diane's problem, but time to Thanos snap her out of existince lol.
Now, we'll foucs on the pilot.
To release the pilot, run this SQL query:
By running this script, we're finally free:
Why couldn't we just run this snippet in the beginning???! No freaking idea 🤷. But at least we've escaped, and gotten a neat certificate too:
The SQL learning doesn't have to stop here though! Check out some more SQL Island alternatives below!
If you're looking for more fun ways to practice SQL, check out this blog on 4 SQL games.
If you're looking for interactive SQL exercises that are more difficult than SQL Island, check out these 200+ SQL Interview Questions.
And of course, to build those SQL foundations checkout the 100% free SQL tutorial: