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.
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';
SQL Query -
SELECT *
FROM inhabitant
WHERE state = ‘friendly’
AND job = ‘weaponsmith’;
FROM inhabitant
WHERE state = ‘friendly’
AND job = ‘weaponsmith’;
SQL Query -
SELECT *
FROM inhabitant
WHERE state = ‘friendly’
AND job LIKE ‘%smith’;
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’;
FROM INHABITANT
WHERE name = ‘Stranger’;
SQL Query -
SELECT gold
FROM INHABITANT
WHERE name = ‘Stranger’;
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;
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!
SET owner = 20
WHERE owner IS NULL;
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!)
FROM ITEM
WHERE owner = 20
SQL Query -
SELECT *
FROM INHABITANT
WHERE state = ‘friendly’
AND job = ‘dealer’
OR job = ‘merchant’
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’
SET owner = 15
WHERE item = ‘ring’
OR item = ‘teapot’
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.)
SET name = ‘David’
WHERE personid = 20
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.
FROM inhabitant
WHERE job = ‘baker’
ORDER BY gold DESC
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)
FROM inhabitant
WHERE job = ‘pilot’
SQL Query -
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’)
SELECT COUNT(*)
FROM inhabitant
JOIN village ON village.villageid = inhabitant.villageid
WHERE village.name = 'Onionville'
SQL Query -
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?
21) Yeah! Now I release the pilot!
Certificate
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?
FROM inhabitant
WHERE villageid = 3
AND gender = ‘f’
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, …)?
FROM inhabitant
WHERE job = ‘baker’
OR job = ‘dealer’
OR job = ‘merchant’
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?
FROM inhabitant
GROUP BY state
ORDER BY AVG(inhabitant.gold)
SQL Query -
DELETE FROM inhabitant
WHERE name = ‘Dirty Diane’
WHERE name = ‘Dirty Diane’
SQL Query -
UPDATE inhabitant
SET state = 'friendly'
WHERE state = ‘kidnapped’
Update your Name
SET state = 'friendly'
WHERE state = ‘kidnapped’
UPDATE inhabitant
SET name = 'your name'
WHERE state = '20'
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.
Comments
Post a Comment
datapedia24@gmail.com