database concepts

Using a database: multiple table queries

SELECT processing order

Knowing the order in which SQL statements are processed is very important in understanding how more complex queries work. View the diagram of the six step process adapted from your text.

For the following query on the Invoice database, we will work out results for each intermediate step.

For those customers who have more than one invoice, find their names and number of invoices.
List the output in reverse alphabetical order.

To get the most out of this exercies, run each step in Oracle.


Write your query

Write the query for those customers who have more than one invoice, find their names and number of invoices. List the output in reverse alphabetical order. Your query should return

Output of the query

View the answer

SELECT CustName, Count(*)
FROM Customer, Invoice
WHERE Customer.CustABN = Invoice.CustABN
GROUP BY CustName
HAVING Count(*) > 1
ORDER BY CustName DESC;

Step 1 FROM

This creates the cross product for Customer and Invoice. All fields are included at this stage.

To see this in Oracle:
     SELECT *
     FROM Customer, Invoice;

What would be the first lines of the output? View the answer

Output

Step 2 WHERE

All the rows that do not satisfy the WHERE condition
     WHERE Customer.CustABN = Invoice.CustABN
 are eliminated

To see this in Oracle:
     SELECT *
     FROM Customer, Invoice
     WHERE Customer.CustABN = Invoice.CustABN;

What would be the first lines of the output? View the answer

Output

 

The spurious rows from Step One are deleted. For example, invoice 3 is no longer listed for every customer. Instead it only appears for the customer it belongs to, namely Mellow Yellow Lighting.

Remember

Including a join condition for each pair of tables in the FROM clause is essential.

 

 

Step 3 GROUP BY

The remaining rows are now grouped according to
     GROUP BY CustName
 That is all the rows for a customer are combined into one group. For example the three invoices for Mellow Yellow Lighting are combined into one group and treated as one.

To see this in Oracle:
     SELECT CustName
     FROM Customer, Invoice
     WHERE Customer.CustABN = Invoice.CustABN
     GROUP BY CustName;

The output for this step cannot be shown as clearly as was done in the steps above. What we can say is after this step there are 16 groups.

What would be the output? View the answer

Output

These are all the customers who have an invoice.

Step 4 HAVING

In this step only those groups satisfying
     HAVING Count(*) > 1
 are included.

To see this in Oracle:
     SELECT CustName
     FROM Customer, Invoice
     WHERE Customer.CustABN = Invoice.CustABN
     GROUP BY CustName
     HAVING Count(*) > 1;

Thus only customers who have more than one invoice (where the group has more than one row) are included.

What would be the output? View the answer

Output

Step 5 SELECT

The output columns are now chosen:
     SELECT CustName, Count(*)

To see this in Oracle:
     SELECT CustName, Count(*)
     FROM Customer, Invoice
     WHERE Customer.CustABN = Invoice.CustABN
     GROUP BY CustName
     HAVING Count(*) > 1;

What would be the output? View the answer

Output

Step 6 ORDER BY

The last step is to order the output in reverse alphabetical order:
     ORDER BY CustName;

To see this in Oracle:
     SELECT CustName, Count(*)
     FROM Customer, Invoice
     WHERE Customer.CustABN = Invoice.CustABN
     GROUP BY CustName
     HAVING Count(*) > 1
     ORDER BY CustName DESC;

What would be the output? View the answer

Output

Remember

To speed up execution of queries, DBMS such as Oracle optimise query execution wherever possible. Thus the actual execution of the query may not follow the steps above exactly.