Database Operations

The remaining chapters in this book show what you can do with databases. There are two main categories of operations. Chapter 3 shows you how to use the data-definition operators. These affect the structure of the database itself; they enable you to:

• Create new tables

• Delete tables and their contents

• Change the structure of (add and remove columns from) a table

• Add and remove indexes

These are important functions, but once the database is set up they will be infrequently used.

The data-manipulation operators are more interesting. Some of these modify an existing table by:

• Inserting new rows

• Updating existing rows

• Deleting rows

Other data manipulation operators work on tables and return collections of data. Think of them as virtual tables that you can apply other operators to or use in your programs. Logically, there are four of these operators:

• RESTRICT specifies the rows to be returned.

• PROJECT specifies the columns.

• JOIN tells the DBMS how tables are joined by specifying the relationships among columns of the various tables.

• UNION combines two or more tables that have the same structure.

In SQL (the Structured Query Language, used by all DBMS), these operations are combined in the SELECT statement. To start, however, look at each operation separately.

RESTRICT rows

RESTRICT lets you choose the rows you are interested in. It returns a virtual table, containing only the chosen rows. In Figure 1-2, the rows are specified where the name is "Andy."

Figure 1-2: RESTRICT returns a subset of the rows.

The following is the SQL code for Figure 1-2:

SELECT * FROM person WHERE name = ‘Andy'

PROJECT columns

The PROJECT operation specifies the columns you want. From the rows returned in Figure 1-2, the phone_type and phone_number columns are chosen, as shown in Figure 1-3.

Figure 1-3: PROJECT returns a subset of the columns.

You can treat the result of RESTRICT and PROJECT as another table-with fewer rows and columns-that you can print or process in your program, either a row at a time or all at once.

The following is the SQL code for Figure 1-3:

SELECT phone_type, phone_number FROM person WHERE name = ‘Andy'

JOIN tables

A database with just one table is not very interesting. Most databases have more tables, and we often want to get related data from several tables. The JOIN operation creates a new virtual table by joining the tables on columns with the same value.

The top portion of Figure 1-4 shows a more realistic table structure, where the addresses and phone numbers have been split into separate tables. We can get our original table back by combining rows that have the same value in the name column of both tables, as shown in the bottom portion of Figure 1-4.

Figure 1-4: JOIN returns a table made by combining the source tables.

The following is the SQL code for Figure 1-4:

SELECT phone.name, phone_type, phone_number, address FROM phone, address WHERE phone.name = address.name

Note 

If you don't tell the DBMS which columns to join, you will get a natural join: every possible combination of rows from each table.

Being able to join tables by any columns with common values is what makes relational databases so powerful and useful. Sometimes joining tables does not provide the result you want. If there is an address with no matching phone number, the address is not selected. If what you want is 'all the addresses with any phone numbers that exist,' you need an OUTER JOIN. If an OUTER JOIN can't find a matching row in a table, it returns a row with NULL values in the missing fields, as shown in Figure 1-5.

Figure 1-5: An OUTER JOIN supplies NULL values for missing data.

UNION of queries

Most database queries use RESTRICT and PROJECT on one or more joined tables. But sometimes you will need to combine the result of multiple queries: perhaps you want title and author from a table of books and title and artist from a table of CDs together in one table. That's what UNION does.

In Figure 1-6, the queries return the title and author from the books table and title and artist from a CD table; UNION combines these into one table. The only limitation is that you must choose the same number of columns from each table and they must be compatible data types.

Figure 1-6: UNION returns a table made by combining two similar tables.

All the preceding operations are used in subsequent chapters.