Beginning SQL

Chapter 9. Advanced Queries

In database programming, you'll find that roughly 95 percent of queries are fairly straightforward and are just a matter of working out what columns are required and including a simple WHERE clause to filter out the unwanted results. This chapter is all about how to tackle the other 5 percent, which are difficult and complex queries. This chapter also presents a number of questions and examines how to write the SQL to answer them. Specifically, this chapter covers the following:

• Tackling complex queries

• Formulating precise SELECT column lists and FROM clauses

• Writing ruthlessly efficient queries

Before getting into the specifics of the chapter, you need to begin by making some additions to the Film Club database.

Updating the Database

In order to give more scope for tricky queries and avoid repeating examples from previous chapters, this chapter extends the Film Club database and adds some new tables and data. Imagine that the film club chairperson wants to sell DVDs to members; the film club will employ salespeople to contact members and sell them DVDs. Therefore, you want to store details of the salespeople, details of orders taken, and details of what each order contains. In order to do this, you need to create three new tables (Orders, OrderItems, and SalesPerson), as shown in Figure 9-1:

Figure 9.1. Figure 9-1

The SQL needed to create the new tables is shown ...