Creating Groups of Data

GROUP BY Clause Syntax :

• SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];

• You can divide rows in a table into smaller groups by using the GROUP BY clause

Using the GROUP BY Clause :

• All columns in the SELECT list that are not in group functions must be in the GROUP BY
  clause
• SELECT deptno, AVG(sal) FROM emp GROUP BY deptno ;

• The GROUP BY column does not have to be in the SELECT list
• SELECT AVG(sal) FROM emp GROUP BY deptno ;

Grouping by More Than One Column :

• You can also group by multiple columns
• Example : select deptno, job, avg(sal) from emp group by deptno, job;
  

Cross Joins

Cartesian Products :

• 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

• To avoid a Cartesian product, always include a valid join condition

Creating Cross Joins :

• The CROSS JOIN clause produces the cross-product of two tables
• This is also called a Cartesian product between the two tables

• SELECT ename, dname FROM emp CROSS JOIN dept ;

•