Entering data

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.

Before you start editing data you might want to make a backup of the database file. In any case, you can always download it again.

Adding new rows - Insert

The simplest task is to insert new data into an existing table. This is done with the insert command. Assume you want to add a person called Max, age 21, living in Berlin. The statement reads.

insert into person values (250,'Max','Berlin',21);

Remember that the first entry is a primary key which means that it needs to be unique.

40.) What happens if you replace the 250 by 230?

Before inserting data, it is usually necessary to get information about the table (e.g. using the pragma table_info command) because the columns must be entered in the correct order with entries of the correct type.

41.) You found out that Max took a flight from his home town of Berlin to Istanbul. Add this information to the flight table and use a suitable query to check if you were successful.

Editing existing rows - Update

Max has moved from Berlin to Leipzig. To change the data use the update and set command together with a where clause:

update person
set residence = 'Leipzig'
where id = 250;

You can enter a comma seperated list of column assignments in the set command. It is very important to use the primary key and not the name column (i.e. where name='Max'). In this case you would move all persons called Max to Leipzig. Please use a query to check if Max was moved correctly.

42.) In the flight 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 id id to the correct value. Check if the update was successful.

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 SQL script used to create the sample database. You can also have a look in the SQL wikibook which is very good.


Back to start