Solutions - The theft of the Mona Lisa

12. Use the select command on the table sqlite_master and pragma table_info on all tables present in the database.

13. Flight

14. Use

    select * from flight;
    

    and then

    pragma table_info(flight);
    

15. Use

    pragma table_info (person);
    select * from person where residence = 'Paris';
    

16. Use

    select name from flight where dest_city = 'Paris' 
    and date < "2014-10-23";
    

17. Use

    select distinct name from flight where start_city = 'Paris' 
    and date > "2014-10-23";
    

18. Use

    select distinct name from flight where dest_city = 'Paris'
    and date < "2014-10-23" 
    and name in (select name from flight where start_city 
    = 'Paris' and date > "2014-10-23");
    

19. Use

    select distinct person.name from person, flight
    where residence = 'Paris'
    or (flight.name = person.name and dest_city = 'Paris'
    and date < "2014-10-23" and flight.name in
    (select flight.name from flight 
    where start_city = 'Paris' and date > "2014-10-23"));
    

20. Use

    select * from flight where name in ('Philipp', 'Kesia', 'Sarah');
    

    and

    select distinct person.name, residence from person, flight
    where residence = 'Paris' or
    (flight.name = person.name and dest_city = 'Paris' and
    date < "2014-10-23" and flight.name in
    (select flight.name from flight
    where start_city = 'Paris' and date > "2014-10-23"));
    

21. Foreign keys: contract_sender_id, contract_receiver_id. Table: phone_contract

    select sql from sqlite_master
    where type = 'table' and name = 'messages';
    

22. Use

    select distinct person.name, residence from person, flight 
    where residence = 'Paris' or 
    (flight.person_id = person.id and dest_city = 'Paris' 
    and date < "2014-10-23" and flight.name in 
    (select flight.name from flight where start_city = 'Paris'
    and date > "2014-10-23"));
    

23. Use

    select name from sqlite_master where type = 'table';
    

24. Use

    select * from messages where sent > "2014-10-20"
    or sent < "2014-10-25";
    

25. Use

    select id from phone_contract where phone_contract.person_id
    in (select distinct person.id from person, flight
    where residence = 'Paris' or 
    (flight.person_id = person.id
    and dest_city = 'Paris' and date < "2014-10-23"
    and flight.name in (select flight.name from flight
    where start_city = 'Paris' and date > "2014-10-23")));
    

26. Use

    select * from messages where sent > "2014-10-20"
    and sent < "2014-10-25" and contract_sender_id in
    (select id from phone_contract
    where phone_contract.person_id in
    (select distinct person.id from person, flight
    where residence = 'Paris' or (flight.person_id = person.id
    and dest_city = 'Paris' and date < "2014-10-23"
    and flight.name in (select flight.name from flight
    where start_city = 'Paris' and date > "2014-10-23"))));
    

27. Use

    select * from messages
    where sent > "2014-10-20" and sent < "2014-10-25"
    and contract_sender_id in (select id from phone_contract
    where phone_contract.person_id in
    (select distinct person.id from person, flight
    where residence = 'Paris' or (flight.person_id = person.id
    and dest_city = 'Paris' and date < "2014-10-23"
    and flight.name in (select flight.name from flight
    where start_city = 'Paris' and date > "2014-10-23"))))
    order by sent;
    

    After having found the ids of the two thieves you can use

    select * from person
    where id = 100 or id = 106;
    

28. Philipp and Sarah

Back to Tutorial