The theft of the Mona Lisa!
Now we can start to use our new skills to solve a criminal case: one of the most startling art thefts in history: On October the 23rd, 2014 the Mona Lisa was stolen from the Louvre in Paris. There is no trace of the thieves. You are working for Interpol and want to find out who is responsible for this crime. Due to new regulations you have access to any data of many international companies and states, like registers of residents, mobile phone text messages, and bank data. So, using these resources, find the thieves!
Which tables are there?
So, first you need to get an overview of what kind of data is actually available.
1.) How many tables are in the database?
2.) What are the various columns of each table and their data types?
3.) Create a database diagram of all tables and their associated relationships
4.) What are the columns used for each table that has a foreign key relationship?
How to find the thief
The step-by-step approach:
- Find out, who was in Paris at that time.
- The thief was probably not working alone. Is there any suspicious communication during the time in question?
- If you find the people responsible, who was the actual wire-puller and who was "only" the henchman?
Who was in Paris?
When you try to transform the following questions into SQL queries, you might want to check for the actual column names. The following is a useful query that can be used to run against SQL Server's system table to gain insight into all tables of the database:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
5.) Alter the query above so that tables named "sysdiagrams" are excluded, list tables in descending order.
6.) Return only 5 rows from the results of the last query.
7.) Alter the query above so that only the bank_transaction table is displayed in the result with all rows.
8.) Look at ALL the tables of this database. What table could contain information regarding traveling?
9.) Get an idea what kind of information is stored in the table with traveling information?
Hint: have a look at the actual data, and a second look on the table information
10.) Get the name of all persons who live in Paris.
11.) Get all names that did a journey to Paris before 23.10.2014.
12.) Get all names that did a journey from Paris after 23.10.2014.
13.) Get all names, that did a journey to Paris before 23.10.2014, where this name is also in an entry for a journey from Paris after the 23.10.2014.
14.) Get all names of persons who live in Paris or spent their time in Paris on 23.10.2014 (according to the travel data).
Congratulations! You have reduced the number of suspects tremendously!
The local police will pay these people a visit to ask for an alibi for the time in question.
15.) What foreign keys a......