Learning SQL on SQL Server 2005

Chapter 6. Query Development and Derived Structures

A problem in SQL—and in all programming, for that matter—is the development of long queries or statements. One way to create long queries is to begin modestly and to incrementally build or develop the query of interest. This is the approach described in this chapter, which we will illustrate by developing a few queries. And, as you’ll find out, often the appropriate placement of parentheses within the query is required to get the right answer to a question.

Another way to develop queries is to use derived structures—a pseudo-table, of sorts. In Server SQL 2005, derived structures include such things as views (both real and inline views) and temporary tables (both temporary and global), both of which enable us to easily manipulate partial displays of tables. The partial displays can then be connected to answer a complicated database query. This chapter discusses derived structures, focusing specifically on views and temporary tables, and how query development can be aided with the use of derived structures.

Query Development

Queries are sometimes developed after some initial experimentation, yet other times they are the result of modifying previously stored queries. The best way to understand how the query building process works is to look at an example. Suppose we want to find the names of all students in the Student_course database who major in computer science (COSC) and have earned a grade of B in some course. To do so, we can ...