Associative queries, group queries in Oracle

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
The ancient notation
Select First_name,name from s_emp,s_dept where s_emp.dept_id = s_dept.id;
The wording of the present
Select E.first_name,s.name from S_emp e joins s_dept s on e.dept_id = S.id;
Grammar:
Select columns, columns, columns
From table 1 Join table 2
On table 1 FOREIGN key = Table 2 PRIMARY key

Case:
--Find out all the employees in the sales department
SELECT * FROM S_emp E
Join S_dept D on e.dept_id = D.id
where d.name = ' Sales ';
--Find out who's working in the Asia region
SELECT * FROM S_emp E
Join S_dept D on e.dept_id=d.id
Join S_region R on D.region_id=r.id
where r.name= ' Asia ';

--Find out all order numbers, fees, and dates of the customer's ' Hamada Sport '
Select o.id,o.total,o.date_ordered from S_ord o
Join S_customer C on o.customer_id = C.id
where c.name = ' Hamada Sport ';

--Find out all the information about customers in ' Asia '
SELECT * FROM S_customer s
Join S_ord o on s.id = o.customer_id
Join S_region r on s.region_id = R.id
where r.name = ' Asia ';
Practice:
--Check out the order information of the customer's name called Unisports
Select o.* from S_customer s
Join S_ord o on s.id = o.customer_id
where lower (s.name) = lower (' unisports ');
--Find out the name of the department in North America
Select S.name from S_dept s
Join S_region r on s.region_id = R.id
Where R.name= ' North America ';
--Find out the name, salary, entry date and position of employees working in North America
Select S.first_name,s.salary,s.start_date,s.title from S_emp s
Join S_dept D on d.id = s.dept_id
Join S_region r on r.id = d.region_id
Where R.name= ' North America ';
--Check out all customer names and their order numbers
Select S.name,o.id from S_customer s
Left JOIN S_ord o s.id = o.customer_id;

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.
Complementary: full-outreach, cross-outreach

--Check out all customer names and their order numbers
Select S.name,o.id from S_customer s
Left JOIN S_ord o s.id = o.customer_id;
--Query all order numbers, order fees and the customer name of the order
Select C.name,o.id,o.total from S_customer C
Right Join S_ord o o.customer_id = c.id;

--Find out the order information purchased by Womansport (order number, fee, payment method)
Select O.id,o.total,o.payment_type from S_customer C
Left JOIN S_ord o o.customer_id = c.id
where lower (c.name) = lower (' Womansport ');
--Find out the employee's name, salary, and rank in descending order of Operations department work
Select S.first_name,s.salary,d.name from S_emp s
Left join S_dept D on d.id=s.dept_id
where Lower (D.name) =lower (' Operations ')
ORDER BY s.salary Desc;

Self-correlating:
--Check out all employee names and employee's boss names

Select S.first_name,e.first_name from S_emp s
Left joins S_emp E on s.manager_id=e.id;

----
Note: The associated conditions do not always compare in equivalent.

==========================
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)

Such as:
--Find out the highest, lowest, average, and total wages of employees
Select Max (Salary), min (Salary), AVG (Salary), sum (salary) from s_emp;
--Find out the highest, lowest, average, and total wages for each department employee
Select Dept_id,max (Salary), min (Salary), AVG (Salary), sum (salary) from s_emp
GROUP BY dept_id
Order by dept_id;
--Find out the highest, lowest, average, and total wages of 41,42,50 Department employees
Select Dept_id,max (Salary), min (Salary), AVG (Salary), sum (salary) from s_emp
GROUP BY dept_id
Have dept_id in (41,42,50)
Order by dept_id;

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.

--statistics on the number of people with wages above 1100 in each title.
Select COUNT (*) from s_emp where salary >1100
Group BY title;

--Find out the number of customers with orders exceeding >=2
Select C.name
From S_customer C joins S_ord O on o.customer_id=c.id
GROUP BY C.name
Having count (o.id) >=2;
--statistics total number of employees
Select COUNT (id) from s_emp;

--statistics total number of titles [cannot be duplicated]
Select COUNT (distinct title) from S_emp;

--Practice:
--1. Find out the Department ID and department name of 4 + employees
Select D.id,d.name,count (*) from S_emp E
Left join S_dept D on e.dept_id=d.id
GROUP BY D.id,d.name
Having Count (*) >=4;
--2. Find customers with total order cost exceeding 10000 yuan
Select C.name,o.total from S_ord o right joins S_customer C on o.customer_id=c.id
where o.total>10000
Group BY C.name,o.total;

--3. Count the number of customers in each region, sorted by its descending order
Select count (c.name), r.id from S_customer C joins s_region R on C.region_id=r.id
GROUP BY R.id
Order by Count (c.name) desc;

--4. Statistics of the number of employees managed by each manager
Select S.manager_id,count (s.first_name) from S_emp s left joins S_emp E on s.manager_id=e.id
Group BY S.MANAGER_ID;

--5. Fees for various payments in the statistics order
Select Sum, payment_type from S_ord
Group BY Payment_type;

Associative queries, group queries in Oracle