Introduction to Oracle9i: SQL (7)

Aggregating Data and Group Functions
##CONTINUE##

• Natural [inner] join
• NATURAL join selects rows from the tables that have equal values in all matched columns(same column names, same datatype)
• Set operators such as Union, Union All, Intersect and Minus are used to select data from multiple tables. The basically combine the results of two queries into one and hence are called Compound queries.
• Conversion functions can be used to convert the first column so that the datatype returned by the function is the same as the second column’s datatype. So there is possible join two tables which first columns datatype is different from the first column in the second.
• A Cartesian product is formed when:
  － A join condition is omitted
  － A join condition is invalid
  － All rows in the first table are joined to all rows in the second table
• Group function can be used in SELECT clause and GROUP BY clause.
• AVG, COUNT, MAX, MIN, STDDEV, SUM, and VARIANCE are aggregate functions. Except for COUNT(*), all aggregate functions ignore nulls.
• It is possible to mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. Also it is acceptable to pass column names, expressions, constraints, or other functions as parameters to an aggregate function.
• It is not possible to use columns in the GROUP BY clause which are not included in the select list. But ORDER BY is possible.
• It is not possible to use alias name in the GROUP BY clause. But ORDER BY is possible.
• DISTINCT is used to display unique data.
• HAVING is used to restrict the output of a group function. It is used to further restrict the groups which GROUP BY is to include.
• GROUP BY clause should contain the items listed in the SELECT list that do not use a group function.
• Group_id helps identify duplicate groups. Grouping helps to identify duplicate rows. Keep function returns the first or last row of a sorted group.