On July the 12th, a hundred prestigious guests were invited to the Precious Gemstones Exposition at the famous Royal Castle, England.
This exposition provides a unique opportunity to admire the most precious and valuable gemstones in the world.
The gemstones were exposed in the different rooms of the castle and were protected with a state-of-the-art security system.
At 10:12pm, a power cut left all the guests in the dark for a duration of just under five minutes. At the same time, the security system was deactivated.
When the power returned, the visitors were aghast to see that the twelve most precious gemstones of the exposition had simply vanished. The twelve gemstones were located in twelve different rooms. It would have been impossible for a single individual to steal more than one gemstone in such a short time. This means that this carefully planned robbery has been performed by a team of at least twelve robbers.
The guests have collected evidence left behind by the robbers and have asked you to use it to identify the potential robbers.
You have also been given access to a database of all staff members and guests who were present on that night.
You can also access and query this database online using our online SQL Editor:
Will you be able to identify the twelve robbers and confront them to retrieve the precious gemstones? To do so you will need to write and execute twelve different SQL queries using the set of clues listed below.. Here are some examples of SQL queries based on the suspects table:
SELECT * FROM suspects WHERE gender="MALE" AND Nationality="Australia";
SELECT * FROM suspects WHERE firstname like "A%";
SELECT * FROM suspects WHERE gender="Male" AND (nationality="USA" OR nationality="Canada");
SELECT * FROM suspects WHERE height>=1.80 AND staff=TRUE;
It would appear that the robber broke the glass case to grab the precious stone. They then broke the window and jumped outside.
Footprints have been found outside just below the broken window. The shoes are male shoes, size 10.5. They are not the type of shoes worn by the staff as part of their uniform. So the first robber must be one of the guests.
Can you use these clues to identify a potential culprit from the guests and staff list?
The robber seems to have hurt themselves when breaking the glass case protecting the gemstone. Some blood stains have been found on the carpet. A handkerchief has also been found with blood stains. It has most likely been inadvertently dropped by the robber who used it to clean their wound. On the handkerchief we can see two embroidered initials A.H.
Can you find anyone on the guests list with the initials A.H.?
The back door of the billiard room was open. This door is equipped with a magnetic card/badge reader and can therefore only be opened by staff members from the security team.
A wine glass was found next to the stand where the topaz was exposed. Red lipstick marks can be seen on the glass which would indicate a female robber.
We can deduct that the robber may come from China, Taiwan or Singapore.
On their way out of the room, the robber must have caught their jacket on the door hatch, ripping off a piece of the jacket and inside pocket. Inside this pocket, the guests found a torn piece of a business card where part of a name can be seen: “Mr Oli…” We can assume that the robber is a man and his first name or his last name starts with the letters “OLI”..
The jacket is not part of the staff uniform, so the robber must be a one of the guests.
We can assume that the robber is either American (USA), Canadian, English or Australian and is also vegan.
A tray of Spanish tapas was presented on the kitchen table. The robber picked a few tapas with superior quality chorizo. This suggests that the robber is neither vegetarian nor vegan.
In the dustbin, an empty pack of disposable tissues was found. It had a price tag on it with a price of 8.5 kr (Danish Krones). This would indicate that the robber is most likely Danish.
It is an expensive piece of jewellery and is unlikely that the staff members would wear such jewellery at work.
The brand was also identified and is only sold in the following South American countries: Peru, Chile, Argentina, Brazil and Venezuela.
In the fireplace, some warm ashes of paper were found. However, the paper did not burn fully. It appears to be a plane ticket, indicating a flight leaving from Dublin Airport (Ireland). We would therefore assume that the robber might be a guest from Ireland.
The royal suite has a slanted roof with wooden beams. It would appear that the robber bumped their head on one of these beams as blood was found. It would suggest the robber was between 1.70m and 1.80m tall. When this happened, the robber dropped their cap. It is a men’s cap worn by the gardening staff at the Royal Castle.