lastname in Font

Encoder DataMatrix in Font lastname

5 1 3

Changing the data stored in the database tables is handled with the UPDATE statement. After being combined with a WHERE clause, the changes can now be controlled. Because the id column is unique for each row, it can be used to change the name of one individual. The following line renames John Noakes to Nisse Svensson: UPDATE names SET firstname = 'Nisse', lastname = 'Svensson' WHERE id = 7 In this example, the WHERE clause is used to limit the update to the row with an id value of 7. The changes are delimited by commas, and you can change both the firstname and lastname fields. You can use a more open WHERE clause to update several rows at once. The following line changes the lastname field for all rows in which the firstname is Jane; it renames both Jane Doe and Jane Roe to Jane Johnson: UPDATE names SET lastname = 'Johnson' WHERE firstname = 'Jane'

The DELETE statement is used to delete data from database tables. It looks very much like the UPDATE statement you specify which table you want to delete rows from (and which rows) by using a WHERE clause. You can start by removing the Nisse Svensson (formerly known as John Noakes) row: DELETE FROM names WHERE id = 7 Just as with updating, you can use less specific WHERE clauses to delete several rows at once. The following statement removes the two Johnsons that were created from the two Janes: DELETE FROM names WHERE lastname = 'Johnson'

When you work with databases, you often need several tables that contain information about different aspects of the same things. By using the JOIN clause together with SELECT, you can still extract the information you need with a single query. You join tables by specifying a relation you define what ties the two tables together. In the database used here there is a second table for salaries called salaries. The columns are id and annual, and both are of the INTEGER type. The id column is used to link a salary to an individual in the names table (this is the relation between the tables), while the annual column holds the annual income for each individual. The contents of the table can be seen as follows (notice that some values for id are missing from the table):

1 2 3 5 6 8 9

1000 900 900 1100 1000 1200 1200

Now you can SELECT from names and ask the database to JOIN the tables names and salaries ON the id columns. This is expressed in SQL as follows: SELECT names.firstname, names.lastname, salaries.annual FROM names JOIN salaries ON names.id = salaries.id The result from this statement is shown as follows (the rows not represented in both tables are left out):

1000 900 900 1100 1000 1200 1200

To get all the rows from the names table, replace JOIN with LEFT JOIN. All the rows are returned from the first table (the one on the left in the statement). The resulting statement is this: SELECT names.firstname, names.lastname, salaries.annual FROM names LEFT JOIN salaries ON names.id = salaries.id The rows not represented in the salaries table get the value NULL. The result from the query can be seen in the following table:

When working with databases with several tables, it is important to have a normalized structure. Under normal circumstances, no information should appear more than once. An example of the opposite is if the salaries table contains the lastname and id. In such a case, changing the lastname requires two UPDATE calls. The tables used this far are pretty simple, but try to remember to keep data in only one place (which might sometimes require additional id columns just to tie things together). This is a time well spent because it makes the structure easier to work with. This introduction to SQL only scratches the surface of database design and join statements. There are many more aspects to take into account before implementing a complex database, and there are numerous other ways of joining tables and creating relationships. Some of them are standardized, and others are very dependent on the database server you are using. Before implementing any complex database design I suggest that you consult you database server s documentation as well as books focusing on the topic.