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"
and 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