SQL Island In English: Answers & Solutions

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.

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.

SQL Island Answers With Step-by-Step Explanations

Step #1 in SQL Island: View All Inhabitants

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.

Step #2 in SQL Island: Finding Friendly Inhabitants

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.

Step #3 in SQL Island: Finding Friendly Weaponsmith with

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.

Step #4 in SQL Island: Finding Friendly Smiths with LIKE '%smith'

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!

Step #5 in SQL Island: Get My personid

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.

Step #6 in SQL Island: Checking How Much Gold You Have?

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 😔.

Step #7 in SQL Island: List of All Items That Don’t Belong to Anyone

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.

Step #8 in SQL Island: Collect All Ownerless Items

To collect all ownerless items, we can use the command:

Step #9 in SQL Island: List All Items You Have

This one's a bit more straightforward:

Step #10 in SQL Island: Find Friendly Dealer or Merchant

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:

Step #11 in SQL Island: Get the Ring and Teapot

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.

Step #12 in SQL Island: Change Name from Stranger to Real Name

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!

Step #13 in SQL Island: Baking Job That Pays the Most Gold

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.

Step #14 in SQL Island: Finding A Pilot

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.

Step #15 in SQL Island: Find Chief’s Name of Village Onionville Using

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.

Step #16 in SQL Island: Number of Women in Onionville

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.

Step #17 in SQL Island: Find The of all Bakers, Dealers and Merchants's Gold

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.

Step #18 in SQL Island: Average Gold By Different Inhabitant States

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.

Step #19 in SQL Island: DELETE Dirty Diane

Not sure what's Diane's problem, but time to Thanos snap her out of existince lol.

Now, we'll foucs on the pilot.

Step #20 in SQL Island: Release The Pilot

To release the pilot, run this SQL query:

Step #21 in SQL Island: We've Escaped SQL Island

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!

SQL Island Alternatives And More SQL Games

If you're looking for more fun ways to practice SQL, check out this blog on 4 SQL games.

The best SQL game to play in our opinion is SQL Murder Mystery, which we conveniently also solved for you step-by-step!

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: