-
Notifications
You must be signed in to change notification settings - Fork 10
/
chapter6.html
49 lines (35 loc) · 3.09 KB
/
chapter6.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
<!doctype html>
<html>
<head>
<title>Entering data</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>Entering data</h1>
<p>Thanks to the huge amount of data stored by police and intelligence you were able to solve the theft of the Mona Lisa. Now you are asked to contribute to the database and to submit new information you have gathered during your work.</p>
<p>Before you start editing data you might want to make a backup of the database file. In any case, you can always <a href="databaseOTS.sqlite">download</a> it again.</p>
<h2>Adding new rows - Insert</h2>
<p>The simplest task is to insert new data into an existing table. This is done with the <code>insert</code> command. Assume you want to add a person called Max, age 21, living in Berlin. The statement reads.
<pre>
insert into person values (250,'Max','Berlin',21);
</pre>
<p>Remember that the first entry is a <em>primary key</em> which means that it needs to be <em>unique</em>.</p>
<p class=question>40.) What happens if you replace the <code>250</code> by <code>230</code>?</p>
<p>Before inserting data, it is usually necessary to get information about the table (e.g. using the <code>pragma table_info</code> command) because the columns must be entered in the correct order with entries of the correct type.</p>
<p class=question>41.) You found out that Max took a flight from his home town of Berlin to Istanbul. Add this information to the <code>flight</code> table and use a suitable query to check if you were successful. </p>
<h2>Editing existing rows - Update</h2>
<p>Max has moved from Berlin to Leipzig. To change the data use the <code>update</code> and <code>set</code> command together with a <code>where</code> clause:</p>
<pre>
update person
set residence = 'Leipzig'
where id = 250;
</pre>
<p>You can enter a comma seperated list of column assignments in the <code>set</code> command. It is very important to use the <em>primary key</em> and not the name column (i.e. <code>where name='Max'</code>). In this case you would move all persons called Max to Leipzig. Please use a query to check if Max was moved correctly.</p>
<p class=question>42.) In the <code>flight</code> table there is a flight of a passenger Evie from Rabat to Doha. However, Evie has used a fake name and is, in reality Sherril from Bangalore. Correct this! Hint: Remember to set the <code>id</code> id to the correct value. Check if the update was successful.</p>
<p>This is the end of the tutorial so far. If you have time left you can explore the database on your own. If you want to learn more about the commands used to enter data and to create tables you can have a look at the <a href="databaseOTS.sql">SQL script used to create the sample database</a>. You can also have a look in the <a href="http://en.wikibooks.org/wiki/Structured_Query_Language">SQL wikibook</a> which is very good.</p>
<p><a href="chapter6_solutions.html">Solutions</a></p>
<p><a href="index.html">Back to start</a></p>
</body>
</html>