Team Fly

Page 67

Mark             1961 Edward
Beth             1964 Edward
Cari             1966 Edward

SQL> select a.name, a.birth_year,
  2         a.father, b.birth_year
  3  from   family a, family b, family c
  4  where  a.father = b.name;

NAME       BIRTH_YEAR FATHER     BIRTH_YEAR
---------- ---------- ---------- ----------
Edward           1935 Isaac            1894
Mark             1961 Edward           1935
Beth             1964 Edward           1935
Cari             1966 Edward           1935

The FAMILY table is found in the from clause twice with table aliases of a and b. The table is joined back to itself to retrieve the father's details. In order to accomplish this, the value found in the father column for each retrieved record (a.father) is joined back to the table to obtain a match on the name column (b.name), which will return the father's details—in this case, his year of birth (b.birth_year).

CRITICAL SKILL 2.9
Learn the group by and having Clauses

Earlier, we learned about functions that can work on sets of rows. We can also group sets of rows to lump similar types of information together and return summary information, also referred to as aggregated information. A large number of queries you write will perform group functions as the data is retrieved from the database. Mastering the use of functions and grouping is fundamental to understanding the full power of SQL.

group by

You can use many of the functions we presented earlier with or without the group by clause, but when you use them without it, Oracle treats all of the selected rows as one group. For example, the following query when written without a group by clause, returns the average amount sold for products within the Electronics category.

SQL> select avg(amount_sold)
  2  from   sales s, products p
  3  where  s.prod_id = p.prod_id
  4  and    prod_category = 'Electronics';

AVG(AMOUNT_SOLD)
----------------
      125.551667

Team Fly