-
Notifications
You must be signed in to change notification settings - Fork 10
/
Copy pathchapter2_solutions.html
80 lines (64 loc) · 1.75 KB
/
chapter2_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
<!doctype html>
<html>
<head>
<title>Solutions - SQL Basics</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 - SQL Basics</h1>
<ol>
<li><p class=answer>Use</p>
<pre>
select distinct residence from person;
</pre></li>
<li><p class=answer>Use</p>
<pre>
select count(distinct residence) from person;
</pre></li>
<li><p class=answer>Use</p>
<pre>
select * from person where name like '%oh%';
</pre></li>
<li><p class=answer>Use</p>
<pre>
select * from flight where date < "2014-10-20";
</pre></li>
<li><p class=answer>Use</p>
<pre>
select * from person where name in ('Philipp', 'Carlos')
</pre>
<p class=answer>and</p>
<pre>
select * from person where name = 'Philipp' or name = 'Carlos'
</pre>
</li>
<li><p class=answer>Use</p>
<pre>
select * from person where name = 'Philipp' and age > 50;
</pre></li>
<li><p class=answer>For all contracts use</p>
<pre>
select count(*) from phone_contract;
</pre>
</li>
<p class=answer>and for the actives ones</p>
<pre>
select count(*) from phone_contract where status = 'active';
</pre></li>
<li><p class=answer>We get all combinations of names and phone numbers.</p></li>
<li><p class=answer>Use</p>
<pre>
select person.name, person.age from person, phone_contract
where person.name=phone_contract.name and status = 'active';
</pre></li>
<li><p class=answer>Use</p>
<pre>
select p.name, phone_number from person p, phone_contract c where
p.name = c.name and age > 30;
</pre></li>
<li><p class=answer>It returns name, age, residence and phone number for all people who have the same age as persons living in Paris.</p></li>
</ol>
</body>
</html>