Oracle Advanced Query 1 Association query Group Query

Advanced Query

1. Related queries
Function: Can be queried across multiple tables

--Find out the name of the employee and the name of his department

Grammar:
Select columns, columns, columns
From table 1 Join table 2
On table 1 FOREIGN key = Table 2 PRIMARY key

2. Outer Joins

Left OUTER JOIN
On the left side of the association, even if there is no record to match on the right, the record on the left is also
Appears in the result set, and the right side is all displayed as null values.

Right-hand-out [outer join]
To the right of the association, even if there is no record to match on the left, the record on the right is also
Appears in the result set, and the left side is all displayed as null values.

3 Group queries
Definition: Use the built-in grouping function to query

The so-called grouping, is the view of the data "angle" is different.
That is, a class of values is the same as a group.

Grammar:
Select column name, group function (column name) ... from table name
Where condition
Group BY column
Having words
ORDER BY column

Grouping functions:
SUM ([distinct] column | expression | value) sum
AVG ([distinct] column | expression | value) averaging
Max (column | expression | value) to find the maximum value
Min (column | expression | value) to find the minimum value
Count ([distinct] column |*) number (contains null)

Note 1: Only columns that appear after group by [columns used as grouping criteria] are eligible
Write to the back of the select unless you use a group function to decorate it.

Note that both 2:having and where are conditional
Difference:
A group function cannot be used in a WHERE clause because it precedes GROUP by.
However, a group function can be used in a HAVING clause because it is after group by.

Oracle Advanced Query 1 Association query Group Query