#Advanced 6: link query
/*
Meaning: it is also called multi table query. When the query fields come from multiple tables, the join query will be used
Cartesian product phenomenon: Table 1 has m rows, table 2 has n rows, and the result is mn rows
Cause: there is no valid continuous condition
How to avoid: adding valid connection conditions
Classification:
Classification by age: sql92 standard: only internal connection is supported
sql99 standard (recommended): support inner connection + outer connection (left outer and right outer) + cross link
Classification by function:
Inner links: equivalent links
Non equivalent link
Self link
Outer link: left outer link
Right outer connection
Total external connection
Cross connect
/

SELECT NAME,boyname
FROM boys,beauty

SELECT * FROM beauty;

SELECT * FROM boys;
#sql92 standard
/*
1. The result of multi table value link is the corpus of multi table
2.n table link, at least n-1 connection conditions are required
3. The sequence of multiple tables is not required
4. It is generally necessary to name the table
5. It can be used with all the clauses described above
*/
#Case 1: query the goddess name and the corresponding male god name

SELECT NAME,boyname 
FROM boys,beauty
WHERE beauty.`boyfriend_id`= boys.id;

#Case 2: query employee name and corresponding department name

SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

#2. Alias the table
/*
Improve sentence conciseness
Distinguish multiple fields with duplicate names
Note: if the table is aliased, the query fields cannot be qualified with the original table name
*/
#Query employee name, work type name and work type No

SELECT e.last_name,e.job_id,j.job_title
FROM employees AS e,jobs j
WHERE e.`job_id`=j.`job_id`

#3. Can the order of two tables be changed
#Query employee name, work type name and work type No

SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees AS e
WHERE e.`job_id`=j.`job_id`

#4. Can I add a filter
#Case: query employee name and department name with bonus

SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;

#Case 2: query the Department name and city name whose second character is o in the city name

SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';

#5. Can you add groups
#Case 1: query the number of departments in each city

SELECT COUNT(*) number,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

#Case 2: query the Department name and department leader number of each department with bonus and the minimum wage of the Department

SELECT department_name,d.manager_id,MIN(salary)
FROM employees e,departments d 
WHERE e.`department_id`=d.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id;

#6. Query the type of work name and number of employees of each employee in descending order by the number of employees

SELECT job_title,COUNT(*) number
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*);

#7. Can you link three tables?
#Case: query employee name, department name and city

SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;

#2. Non equivalent link
I don't understand at present. I'll add it later
#3. Self connection
#Case: query employee name and superior name

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;