This repository contains my step-by-step solutions for the SQL Island interactive SQL learning game.

How to read this document:

• The queries in this format are game system auto-generated and executed by the game.

• Output for each query is provided.
• Conversations are also included for easy navigation of that story point.

• My solutions are in this format and can be directly copied to clipboard.

Schema:

• VILLAGE (villageid, name, chief)
• INHABITANT (personid, name, villageid, gender, job, gold, state)
• ITEM (item, owner)

💬 You: Oh dear, what happened? It seems that I am the only survivor of the air crash. Wow, there are some villages on this island.

SELECT * FROM village
Output:

villageid	name	chief
1	Monkeycity	1
2	Cucumbertown	6
3	Onionville	13

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

SELECT * FROM INHABITANT;

Output:

personid	name	villageid	gender	job	gold	state
1	Paul Bakerman	1	m	baker	850	friendly
2	Ernest Perry	3	m	weaponsmith	280	friendly
3	Rita Ox	1	f	baker	350	friendly
4	Carl Ox	1	m	merchant	250	friendly
5	Dirty Dieter	3	m	smith	650	evil
6	Gerry Slaughterer	2	m	butcher	4850	evil
7	Peter Slaughterer	3	m	butcher	3250	evil
8	Arthur Tailor	2	m	pilot	490	kidnapped
9	Tiffany Drummer	1	f	baker	550	evil
10	Peter Drummer	1	m	smith	600	friendly
11	Dirty Diane	3	f	farmer	10	evil
12	Otto Alexander	2	m	dealer	680	friendly
13	Fred Dix	3	m	author	420	friendly
14	Enrico Carpenter	3	m	weaponsmith	510	evil
15	Helen Grasshead	2	f	dealer	680	friendly
16	Ivy Hatter	1	f	dealer	770	evil
17	Edward Grasshead	3	m	butcher	990	friendly
18	Ryan Horse	3	m	blacksmith	390	friendly
19	Ann Meaty	2	f	butcher	2280	friendly

💬You: Woah, so many people!

💬You: Man! I'm hungry. I will go and find a butcher to ask for some free sausages.

SELECT * FROM inhabitant WHERE job = 'butcher'
Output:

personid	name	villageid	gender	job	gold	state
6	Gerry Slaughterer	2	m	butcher	4850	evil
7	Peter Slaughterer	3	m	butcher	3250	evil
17	Edward Grasshead	3	m	butcher	990	friendly
19	Ann Meaty	2	f	butcher	2280	friendly

💬Edward Grasshead(Butcher): There you are! Enjoy your meal! But take care of yourself. As long as you are unarmed, stay away from villains. Not everyone on this island is friendly.

💬You: Thank you, Edward! Okay, let's see who is friendly on this island...

SELECT * FROM INHABITANT WHERE state = 'friendly';

Output:

personid	name	villageid	gender	job	gold	state
1	Paul Bakerman	1	m	baker	850	friendly
2	Ernest Perry	3	m	weaponsmith	280	friendly
3	Rita Ox	1	f	baker	350	friendly
4	Carl Ox	1	m	merchant	250	friendly
10	Peter Drummer	1	m	smith	600	friendly
12	Otto Alexander	2	m	dealer	680	friendly
13	Fred Dix	3	m	author	420	friendly
15	Helen Grasshead	2	f	dealer	680	friendly
17	Edward Grasshead	3	m	butcher	990	friendly
18	Ryan Horse	3	m	blacksmith	390	friendly
19	Ann Meaty	2	f	butcher	2280	friendly

💬You: 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)

SELECT * FROM INHABITANT WHERE job = 'weaponsmith' AND state = 'friendly';

Output:

personid	name	villageid	gender	job	gold	state
2	Ernest Perry	3	m	weaponsmith	280	friendly

💬You: 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).

SELECT * FROM INHABITANT WHERE job LIKE '%smith' AND state = 'friendly';

Output:

personid	name	villageid	gender	job	gold	state
2	Ernest Perry	3	m	weaponsmith	280	friendly
10	Peter Drummer	1	m	smith	600	friendly
18	Ryan Horse	3	m	blacksmith	390	friendly

💬You: That looks better! I will go and visit those smiths.

💬Paul: Hi stranger! Where are you going? I'm Paul, I'm the major of Monkeycity. I will go ahead and register you as a citizen.

INSERT INTO inhabitant (name, villageid, gender, job, gold, state) VALUES ('Stranger', 1, '?', '?', 0, '?')

💬You: No need to call me stranger! What's my personid? (Hint: Use a SELECT query without an asterisk. 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.)

SELECT personid FROM INHABITANT WHERE name = 'Stranger';

Output:

personid
20

💬You: Hi Ernest! How much is a sword?

💬Ernest: I can offer to make you a sword for 150 gold. That's the cheapest you will find! How much gold do you have?

SELECT gold FROM INHABITANT WHERE personid = 20;

Output:

gold
0

💬You: 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)

SELECT * FROM ITEM WHERE owner IS NULL;

item	owner
teapot	null
ring	null
coffee cup	null
bucket	null
carton	null
lightbulb	null

💬You: So much cool stuff!

💬You: Yay, a coffee cup. Let's collect it!

UPDATE item SET owner = 20 WHERE item = 'coffee cup'

💬You: Do you know a trick how to collect all the ownerless items?

UPDATE ITEM SET owner = 20 WHERE owner IS NULL;

💬You: Now list all of the items I have!

SELECT * FROM ITEM WHERE owner = 20;

Output:

item	owner
teapot	20
ring	20
coffee cup	20
bucket	20
carton	20
lightbulb	20

💬You: 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!)

SELECT * FROM INHABITANT WHERE job IN ('dealer', 'merchant') AND state = 'friendly';

Output:

personid	name	villageid	gender	job	gold	state
4	Carl Ox	1	m	merchant	250	friendly
12	Otto Alexander	2	m	dealer	680	friendly
15	Helen Grasshead	2	f	dealer	680	friendly

💬Helen Grasshead(dealer): 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.

UPDATE ITEM SET owner = 15 WHERE item IN ('ring', 'teapot');

💬Helen Grasshead(dealer): Thank you!

💬Helen Grasshead(dealer): Here, some gold!

UPDATE inhabitant SET gold = gold + 120 WHERE personid = 20

💬You: 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.

UPDATE INHABITANT SET name = 'Somanshu' WHERE personid = 20;

💬You: 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.)

SELECT * FROM INHABITANT WHERE job = 'baker' ORDER BY gold DESC;

Output:

personid	name	villageid	gender	job	gold	state
1	Paul Bakerman	1	m	baker	850	friendly
9	Tiffany Drummer	1	f	baker	550	evil
3	Rita Ox	1	f	baker	350	friendly

💬You: Aha, Paul! I know him!

💬Paul: Hi, you again! So, Somanshu is your name. I saw you want to work as a baker? Okay! You will be paid 1 gold for 100 bread rolls.

💬You: (8 hours later...) Here, I made ten thousand bread rolls! I quit! This should be enough money to buy a sword. Let's see what happens with my gold balance.

UPDATE inhabitant SET gold = gold + 100 - 150 WHERE personid = 20

💬Edward: Here's your new sword, Somonsho! Now you can go everywhere.

INSERT INTO item (item, owner) VALUES ('sword', 20)

💬You: My name is Somanshu! Thanks anyway!

💬You: Is there a pilot on this island by any chance? He could fly me home.

SELECT * FROM INHABITANT WHERE job = 'pilot';

Output:

personid	name	villageid	gender	job	gold	state
8	Arthur Tailor	2	m	pilot	490	kidnapped

💬You: Oh no, his state is 'kidnapped'.

💬Edward: Horrible, the pilot is held captive by Dirty Dieter! I will show you a trick how to find out the name of the village where Dirty Dieter lives.

SELECT village.name FROM village, inhabitant WHERE village.villageid = inhabitant.villageid AND inhabitant.name = 'Dirty Dieter' Output:

name
Onionville

💬Edward: The expression presented here is called a join. It combines the information of the inhabitant table with information of the village table by matching villageid values.

💬You: 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).

SELECT i.name FROM INHABITANT i INNER JOIN VILLAGE v ON v.chief = i.personid WHERE v.name = 'Onionville';

Output:

name
Fred Dix

💬You: I've got it! I will visit Fred and ask him about Dirty Dieter and the pilot.

💬You: Um, how many inhabitants does Onionville have?

SELECT COUNT(*) FROM inhabitant, village WHERE village.villageid = inhabitant.villageid AND village.name = 'Onionville' Output:

COUNT(*)
8

💬Fred Dix(Chief of Onionville): Hello Somanshu, 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 i INNER JOIN VILLAGE v ON v.villageid = i.villageid WHERE v.name = 'Onionville' AND gender = 'f';

Output:

COUNT(*)
1

💬You: Oh, only one woman. What's her name?

SELECT i.name FROM INHABITANT i INNER JOIN VILLAGE v ON v.villageid = i.villageid WHERE v.name = 'Onionville' AND gender = 'f';

Output:

name
Dirty Diane

💬You: Let's go!

💬Dirty Dieter: Somanshu, if you hand me over the entire property of our nearby village Cucumbertown, I will release the pilot. I will show you now what this property consists of.

SELECT SUM(inhabitant.gold) FROM inhabitant, village WHERE village.villageid = inhabitant.villageid AND village.name = 'Cucumbertown' Output:

SUM(inhabitant.gold)
8980

💬You: 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?

SELECT SUM(gold) FROM INHABITANT WHERE job IN ('baker', 'dealer' ,'merchant');

Output:

SUM(gold)
4130

💬You: That's not enough.

💬You: Let's have a look at how much average gold people own, depending on their job.

SELECT job, SUM(inhabitant.gold), AVG(inhabitant.gold) FROM inhabitant GROUP BY job ORDER BY AVG(inhabitant.gold) Output:

job	SUM(inhabitant.gold)	AVG(inhabitant.gold)
farmer	10	10.0
?	70	70.0
merchant	250	250.0
blacksmith	390	390.0
weaponsmith	790	395.0
author	420	420.0
pilot	490	490.0
baker	1750	583.333333333333
smith	1250	625.0
dealer	2130	710.0
butcher	11370	2842.5

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

SELECT state, AVG(gold) FROM INHABITANT GROUP BY state;

Output:

state	AVG(gold)
?	70.0
evil	1512.85714285714
friendly	706.363636363636
kidnapped	490.0

💬You: Ok, so the only way is to mug the villains.

💬You: Or I might as well go ahead and just kill Dirty Dieter with my sword!

DELETE FROM inhabitant WHERE name = 'Dirty Dieter'

💬Dirty Diane: Heeeey! Now I'm very angry! What will you do next, Somanshu?

DELETE FROM INHABITANT WHERE name = 'Dirty Diane';

💬You: Yeah! Now I release the pilot!

UPDATE INHABITANT SET state = 'friendly' WHERE state = 'kidnapped';

💬Pilot: Thank's for releasing me, Somanshu! I will fly you home!

💬You: I take my sword, some gold and lots of useless items with me as a souvenir. What a big adventure!

UPDATE inhabitant SET state = 'emigrated' WHERE personid = 20

The game is over. Get your certificate of completion now! If you want to change the name on the certificate, use an UPDATE command on the inhabitants table.

Enjoyed this repo? A ⭐ lets me know it was worth sharing!