Chapter 4. More SQL: Queries, Subqueries, and Views

In the last chapter, we surveyed the different SQL statements that are available and learned the essentials that we need to create, maintain, and use a database. In this chapter, we will concentrate in detail on the most versatile SQL statement, the SELECT statement. We'll learn how to use SELECT statements to:

• Group data

• Put data in order

• Combine data from different tables

• Use as subqueries

• Create views

The first three topics are elaborations on the SELECT statement as we already know it. We'll learn a few new clauses for grouping and ordering data. We'll also learn a few new options for the FROM and WHERE clauses that allow us to combine data from multiple tables.

The next topic, subqueries, is an entirely new use for SELECT statements as clauses of other SQL statements. Subqueries can be used with CREATE TABLE, INSERT, DELETE, and UPDATE. But they are especially useful within other SELECT statements. Queries with subqueries are another way of combining data from multiple tables.

The final topic in this chapter, views, provide us with a way of "freezing" a query and having the database make it appear as a virtual table.