SQL Island: Dive into the Exciting World of SQL with an Engaging Adventure

SQL Island is an interactive educational game designed to teach SQL (Structured Query Language) in a fun and engaging way. Players embark on a virtual adventure where they solve puzzles, complete challenges, and master SQL concepts to progress through the game. It offers a hands-on experience to learn and practice SQL skills while exploring an exciting island-themed environment.

SQL Island Link - https://sql-island.informatik.uni-kl.de/ 

Questions and Answers

Tap Continue

1) It seems there are a few people living in these villages. How can I see a list of all inhabitants?

SQL Query - 

SELECT *
FROM inhabitant;

2) Thank you, Edward! Okay, let’s see who is friendly on this island…

SQL Query - 

SELECT *
FROM inhabitant
WHERE state = 'friendly';

3) There is no way around getting a sword for myself. I will now try to find a friendly weaponsmith to forge me one. (Hint: You can combine predicates in the WHERE clause with AND)

SQL Query - 

SELECT *
FROM inhabitant
WHERE state = ‘friendly’
AND job = ‘weaponsmith’;

4) Oh, that does not look good. Maybe other friendly smiths can help you out, e.g. a blacksmith. Try out: job LIKE ‘%smith’ to find all inhabitants whose job ends with ‘smith’ (% is a wildcard for any number of characters).

SQL Query - 

SELECT *
FROM inhabitant
WHERE state = ‘friendly’
AND job LIKE ‘%smith’;

5) No need to call me stranger! What’s my personid? (Hint: In former queries, the * stands for: all columns. Instead of the star, you can also address one or more columns (seperated by a comma) and you will only get the columns you need.)

SQL Query - 

SELECT personid
FROM INHABITANT
WHERE name = ‘Stranger’;

6) I can offer to make you a sword for 150 gold. That’s the cheapest you will find! How much gold do you have?

SQL Query - 

SELECT gold
FROM INHABITANT
WHERE name = ‘Stranger’;

7) Damn! No mon, no fun. There has to be another option to earn gold other than going to work. Maybe I could collect ownerless items and sell them! Can I make a list of all items that don’t belong to anyone? (Hint: You can recognize ownerless items by: WHERE owner IS NULL)

SQL Query - 

SELECT *
FROM ITEM
WHERE owner IS null;

8) Do you know a trick how to collect all the ownerless items?

SQL Query - 

UPDATE item
SET owner = 20
WHERE owner IS NULL;

9) Now list all of the items I have!

SQL Query - 

SELECT *
FROM ITEM
WHERE owner = 20

10) Find a friendly inhabitant who is either a dealer or a merchant. Maybe they want to buy some of my items. (Hint: When you use both AND and OR, don’t forget to put brackets correctly!)

SQL Query - 

SELECT *
FROM INHABITANT
WHERE state = ‘friendly’
AND job = ‘dealer’
OR job = ‘merchant’

11) I’d like to get the ring and the teapot. The rest is nothing but scrap. Please give me the two items. My personid is 15.

SQL Query - 

UPDATE item
SET owner = 15
WHERE item = ‘ring’
OR item = ‘teapot’

12) Unfortunately, that’s not enough gold to buy a sword. Seems like I do have to work after all. Maybe it’s not a bad idea to change my name from Stranger to my real name before I will apply for a job.

SQL Query - 

UPDATE inhabitant
SET name = ‘David’
WHERE personid = 20

13) Since baking is one of my hobbies, why not find a baker who I can work for? (Hint: List all bakers and use ‘ORDER BY gold’ to sort the results. ‘ORDER BY gold DESC’ is even better because then the richest baker is on top.)

SQL Query - 

SELECT *
FROM inhabitant
WHERE job = ‘baker’
ORDER BY gold DESC

14) Is there a pilot on this island by any chance? He could fly me home.

SQL Query - 

SELECT *
FROM inhabitant
WHERE job = ‘pilot’

15) Thanks for the hint! I can use the join to find out the chief’s name of the village Onionville. (Hint: In the column ‘chief’ in the village table, the personid of the chief is stored)

SQL Query - 

SELECT i.name AS chief_name
FROM village v
JOIN individual i ON v.chief = i.personid

WHERE v.village_name = 'Onionville';

16) Hello David, the pilot is held captive by Dirty Dieter in his sister’s house. Shall I tell you how many women there are in Onionville? Nah, you can figure it out by yourself! (Hint: Women show up as gender = ‘f’)

SQL Query - 

SELECT COUNT(*)
FROM inhabitant
JOIN village ON village.villageid = inhabitant.villageid
WHERE village.name = 'Onionville'

AND gender = 'f';

17) Oh, only one woman. What’s her name?

SQL Query - 

SELECT name
FROM inhabitant
WHERE villageid = 3
AND gender = ‘f’

18) Oh no, baking bread alone can’t solve my problems. If I continue working and selling items though, I could earn more gold than the worth of gold inventories of all bakers, dealers and merchants together. How much gold is that?

SQL Query - 

SELECT SUM(inhabitant.gold)
FROM inhabitant
WHERE job = ‘baker’
OR job = ‘dealer’
OR job = ‘merchant’

19) Very interesting: For some reason, butchers own the most gold. How much gold do different inhabitants have on average, depending on their state (friendly, …)?

SQL Query - 

SELECT state, AVG(inhabitant.gold)
FROM inhabitant
GROUP BY state
ORDER BY AVG(inhabitant.gold)

20) Heeeey! Now I’m very angry! What will you do next, David?

SQL Query - 

DELETE FROM inhabitant
WHERE name = ‘Dirty Diane’

21) Yeah! Now I release the pilot!

SQL Query - 

UPDATE inhabitant
SET state = 'friendly'
WHERE state = ‘kidnapped’

Update your Name

UPDATE inhabitant 

SET name = 'your name' 

WHERE state = '20'

Certificate

Wrap-up

Congratulations! You have completed SQL island!!!! Now, try it on your own, print your certificate and display it above the mantel in a prominent position.

For real practice and to become more comfortable with SQL I would suggest running through SQL island until you do not need a walk-through like this. Sleep. Then pass it one more time without help.