SQL Investigation: The Stolen Gemstones

Posted on February 20, 2020 by Administrator Posted in A Level Concepts, A Level Quiz, Computer Science, Computing Concepts, Database Concepts, GCSE Concepts, GCSE Quiz, Quizzes, Solved Challenges, SQL

On July the 12^th, 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.

suspects.csv

You can download this database as a CSV file and use it on your own computer (e.g. import the data using MS Access or Apache Open Office).

You can also access and query this database online using our online SQL Editor:
Open in new windowAccess Online Database / 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;

RubySapphireOnyxTopazDiamondTanzanitePink SapphireQuartzEmeraldAmberPeridotAmethyst

The first gemstone is an intense red ruby which was exposed in the main lounge.

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 second stone is the world largest blue sapphire. It was stolen from the dining room.

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

This rare semi-translucent Onyx was exposed in the billiard room. A long blond hair has been found just next to the stand where the Onyx was displayed. The length of the hair suggests a female robber.

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.

This clear topaz was exposed in the centre of the ballroom. A candle from the main chandelier was found on the floor. This would suggest that, while leaving the room in the dark, the robber inadvertently hit the chandelier. This means that the robber must be at least 1.85m tall.

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.

The diamond was exposed in the library. A post-it note was found on the floor and one can assume it fell off the robber’s pocket or handbag. Some handwritten symbols appear on the post-it note but these are written using the Mandarin alphabet.

We can deduct that the robber may come from China, Taiwan or Singapore.

The tanzanite was exposed in the small lounge/tearoom.

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.

The pink sapphire was exposed on the upstairs landing area. On their way out, the robber must have fallen down the stairs, as the guests reported hearing a loud noise. They also heard someone swear in English. The robber dropped their “Exposition information pack” which was given to all guests. In this pack an extra vegan menu page was found. This page is only given to guests with a vegan diet.

We can assume that the robber is either American (USA), Canadian, English or Australian and is also vegan.

The quartz was exposed in the kitchen.

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.

The emerald was exposed in the study room. It would seem that the burglar had to force the window open to escape the room. An earring in the shape of the Eiffel Tower was found at the scene. It is believed to belong to a French female guest.

It is an expensive piece of jewellery and is unlikely that the staff members would wear such jewellery at work.

The amber was exposed in the main hall. A flower pot was knocked over during the robbery and the soil from the pot spread over the carpet. The robber walked over it and left a footprint on their way out. It appeared to be woman’s shoe with a size between 6 and 7.

The brand was also identified and is only sold in the following South American countries: Peru, Chile, Argentina, Brazil and Venezuela.

The peridot was exposed in the master bedroom. When the guests arrived at the scene, they could smell a subtle scent of jasmine, an essence used in women’s perfumes.

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 amethyst was exposed in the royal suite.

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.