Скачать в pdf «PostgreSQL»


MA    |    2

NJ    |    2

(2 rows)

Figure 5.5: Having

line. This convention makes queries easier to understand. Clear queries also use appropriate capitalization.

In a test database, mistakes do not create a problem. In a live production database, however, one incorrect query can cause great difficulty. It takes five seconds to issue an erroneous query, and sometimes five days to recover from it. Double-check your queries before executing them. This consideration is especially important for update, delete, and insert queries, because they modify the database. Also, before performing an update or delete, do a select or select COUNT(*) with the same where clause. Make sure the select result is reasonable before doing the update or delete.

5.5 Summary

Sometimes users want less output rather than more. They want a total, count, average, maximum, or minimum value for a column. Aggregates make this calculation possible. They aggregate data into fewer rows and then send the result to the user.

Chapter 6

Joining Tables

This chapter discusses how to store data using multiple tables. Both multitable storage and multitable queries are fundamental to relational databases.

We start this chapter by examining table and column references, which are important in multitable queries. Then, we cover the advantages of splitting data into multiple tables. Next, we introduce an example based on a mail-order company, showing table creation, insertion, and queries using joins. Finally, we explore a variety of join types.

6.1    Table and Column References

Before dealing with joins, we must mention one important feature. Up to this point, all queries have involved a single table. When a query involves multiple tables, column names can become confusing. Unless you are familiar with each table, it is difficult to know which column names belong to which tables. Sometimes two tables may use the same column name. For these reasons, SQL allows you to fully qualify column names by preceding the column name with the table name. Figure
6.1 shows an example of table name prefixing. In the figure, the first query has unqualified column names. The second query is the same, but with fully qualified column names. A period separates the table name from the column name.

Скачать в pdf «PostgreSQL»