-
Notifications
You must be signed in to change notification settings - Fork 10
/
Copy pathchapter5.html
50 lines (37 loc) · 2.54 KB
/
chapter5.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
<!doctype html>
<html>
<head>
<title>Advanced topics</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>Advanced topics</h1>
<h2>Nullable columns</h2>
<p>There are situations where not all fields of a row have a value. For example, in the <code>bank_transaction</code> table, the date might be missing because the intelligence service did not obtain this information from the bank. To find the flights without a date we can use the command<p>
<pre>
select * from bank_transaction where date is null;
</pre>
<p class=question>37.) What is the percentage of flights where the date information is missing?</p>
<p>In practice, there are usually columns which are created to be <em>not nullable</em>. In this case, the database refuses to add rows which contain null values in the respective fields. This is always true for primary keys.</p>
<h2>Joins</h2>
<p>Assume we are looking for all information on passengers who have send or received a bank transfer on the date of their flights.</p>
<p class=question>38.) What is the SQL query?</p>
<p>This kind of query is called an <em>inner join</em> on the two tables. An alternative syntax making this a little bit more explicit is</p>
<pre>
select * from flight <strong>inner join</strong> bank_transaction
<strong>on</strong> flight.date = bank_transaction.date;
</pre>
<p>Now, we might want to see all flight data even if no bank transaction is performed on the same date while still showing the corresponding transaction data if there is one. This is called an <em>left outer join</em> or simply <em>left join</em>:</p>
<pre>
select * from flight <strong>left join</strong> bank_transaction
<strong>on</strong> flight.date = bank_transaction.date;
</pre>
<p class=question>39.) What happens when no bank transactions occur at the flight date?</p>
<p>In a <em>right (outer) join</em> the roles of the two tables are just interchanged while in a <em>full outer join</em> all rows of both tables are shown. However, they are both not supported by SQLite.</p>
<p>The commands you have learned so far should be enough to get information from SQL data bases for most application. If you are also interested in entering data into databases and also changing existing data you might want to look at the <a href="chapter6.html">last chapter</a>.</p>
<p><a href="chapter5_solutions.html">Solutions</a></p>
<p><a href="index.html">Back to start</a></p>
</body>
</html>