-
Notifications
You must be signed in to change notification settings - Fork 10
/
chapter3_solutions.html
159 lines (135 loc) · 4.32 KB
/
chapter3_solutions.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
<!doctype html>
<html>
<head>
<title>Solutions - The theft of the Mona Lisa</title>
<link rel=stylesheet href="http://fonts.googleapis.com/css?family=Averia+Serif+Libre:300,400">
<link rel=stylesheet href=style.css>
<meta http-equiv=Content-Type content="text/html; charset=utf-8">
</head>
<body>
<h1>Solutions - The theft of the Mona Lisa</h1>
<ol start="12">
<li><p class=answer>Use the <code>select</code> command on the table <code>sqlite_master</code> and <code>pragma table_info</code> on all tables present in the database.</p></li>
<li><p class=answer>Flight</p></li>
<li><p class=answer>Use</p>
<pre>
select * from flight;
</pre>
<p class=answer>and then</p>
<pre>
pragma table_info(flight);
</pre></li>
<li><p class=answer>Use</p>
<pre>
pragma table_info (person);
select * from person where residence = 'Paris';
</pre></li>
<li><p class=answer>Use</p>
<pre>
select name from flight where dest_city = 'Paris'
and date < "2014-10-23";
</pre></li>
<li><p class=answer>Use</p>
<pre>
select distinct name from flight where start_city = 'Paris'
and date > "2014-10-23";
</pre></li>
<li><p class=answer>Use</p>
<pre>
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");
</pre></li>
<li><p class=answer>Use</p>
<pre>
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"));
</pre></li>
<li><p class=answer>Use</p>
<pre>
select * from flight where name in ('Philipp', 'Kesia', 'Sarah');
</pre>
<p class=answer>and</p>
<pre>
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"));
</pre></li>
<li><p class=answer>Foreign keys: <code>contract_sender_id</code>, <code>contract_receiver_id</code>.
Table: <code>phone_contract</code></p>
<pre>
select sql from sqlite_master
where type = 'table' and name = 'messages';
</pre></li>
<li><p class=answer>Use</p>
<pre>
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"));
</pre></li>
<li><p class=answer>Use</p>
<pre>
select name from sqlite_master where type = 'table';
</pre></li>
<li><p class=answer>Use</p>
<pre>
select * from messages where sent > "2014-10-20"
or sent < "2014-10-25";
</pre></li>
<li><p class=answer>Use</p>
<pre>
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")));
</pre></li>
<li><p class=answer>Use</p>
<pre>
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"))));
</pre></li>
<li><p class=answer>Use</p>
<pre>
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;
</pre>
<p class=answer>After having found the ids of the two thieves you can use</p>
<pre>
select * from person
where id = 100 or id = 106;
</pre>
</li>
<li><p class=answer>Philipp and Sarah</p></li>
</ol>
<p><a href="chapter3.html">Back to Tutorial</a></p>
</body>
</html>