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 ;

• 

Using Group Functions - Avg, Sum, Min, Max and Count

Using the AVG and SUM Functions :

• You can use AVG and SUM for numeric data
• SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal) FROM emp WHERE job LIKE '%MAN%';

Using the MIN and MAX Functions :

• You can use MIN and MAX for numeric, character, and date data types
• SELECT MIN(hiredate), MAX(hiredate) FROM emp;

Using the COUNT Function :

• COUNT(*) returns the number of rows in a table
  
• SELECT COUNT(*) FROM emp WHERE deptno = 10;
• COUNT(expr) returns the number of rows with non-null values for the expr
• SELECT COUNT(comm) FROM emp WHERE deptno = 30;

Using the DISTINCT Keyword :

• COUNT(DISTINCT expr) returns the number of distinct non-null values of the expr
• To display the number of distinct department values in the EMP table
• SELECT COUNT(DISTINCT deptno) FROM emp;