Learning SQL is easy, mastering it is hard. Every object created in the RDBMS and every query can have dozens of different options and clauses. The concepts introduced in this chapter span the entire spectrum, from basic to advanced.
Indices can be used to optimize query performance and, ultimately, the database application. There are different types of indices, and significant differences exist between SQL syntaxes implemented by various RDBMSs.
Temporary tables are used as workbenches to store and manipulate intermediate results; they can differ in scope and different lifecycle options, depending on implementation.
Materialized view is a concept introduced by several vendors to combine the advantages of views and tables into a single SQL object; the primary objective is to speed data retrieval by caching output of the query upon which the view is based and refresh it periodically.
The fully qualified names in the RDBMS are used uniquely to identify objects in multiuser, multischema environments; aliases and synonyms allow you to shorten these names for better readability and code maintenance.
The ability to generate numeric sequences automatically is a very useful feature provided by every RDBMS discussed in the book. There are two different approaches: identity columns (implemented in Microsoft SQL Server, IBM DB2, Microsoft Access, MySQL and OpenOffice BASE HSQLDB), and sequences (favored by Oracle and PostgreSQL). Some RDBMSs, such as IBM DB2, support both.
The sequences ...