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
SELECT column lists and
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.
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 ...