SQL Learning Summary (4)--sql Multi-Table query technology

4.1 Cartesian product

Select name, salary, city from staff, warehouse

Warehouse 5 records, staff table 18 Records, Results 18*5=90 Records.

Staff table 18 Employees, Warehouse table 5 warehouses, that is, 5 workers in the city, to display the names of employees, wages and city information, the result should be 18 records:

Select name, salary, city from employee, warehouse where worker. Warehouse number = Warehouse. Warehouse Number

4.2 Multi-table query with operators

Displays the names, wages and city information of the employees who are not in the 1500~2000, and the city is empty:

Select name, salary, city, from employee, warehouse where worker. Warehouse number = Warehouse. Warehouse number and not (payroll between and) and city is null

4.3 Multi-table queries with statistical functions

Displays the difference between the employee's name, salary, salary and average wage and the amount of sales information:

Select name, salary, Salary-(select AVG (Salary) from worker) as with average wage difference, amount from employee, order where employee. Employee number = Order Form. Employee number

4.4 Multi-table query with predicates

Display warehouse information for employees:

/* use predicate in to implement */

SELECT * from warehouse where warehouse number in (select warehouse number from staff)

/* implemented with predicate exists */

SELECT * from warehouse where exists (SELECT * from staff where warehouse number = warehouse. Warehouse number)

4.5 Multi-table query with sorting and grouping

Display WH1 or WH2 warehouse, wages are not greater than employees zg16 wages of staff information, and require workers name does not contain "flat" word, and then by salary from low to high display:

SELECT * FROM employee where wage <= (select salary from employee where employee number = ' Zg16 ') and name not as '% flat% ' and warehouse number in (' Wh1 ', ' wh2 ') Order BY Wages

Displays the name of the same employee, the average sales amount, and the maximum sales amount information, provided the average sales amount is greater than 10000 and the maximum sales amount is greater than 16000:

Select Name, AVG (amount) as Average amount, max (amount) as maximum amount from the employee, order a where employee. Employee number = Order Form. Employee number GROUP BY name having AVG (amount) >10000 and max (amount) >16000

4.6 Multi-table nested queries

Show the city's non-Shanghai staff information:

SELECT * from staff where warehouse number in (select warehouse number from warehouse where city! = ' Shanghai ')

Displays the average sales amount and warehouse number information for employees in different warehouses, provided that the average sales amount is greater than 15000 or the maximum sales amount is less than 18000 and the average wage of all employees is greater than 800 of the warehouse area:

Select warehouse Number, AVG (amount) as average amount from the employee, order a where employee. Employee number = Order Form. Employee number and Payroll > (select AVG (payroll) from worker where warehouse number in (select warehouse number from Warehouse where area >800) group by warehouse number having AVG (amount) <15000 or max (amount) <18000

4.7 Hyper-Join query

Includes: INNER join (inner join), left join (left join), right join, full join

SQL Learning Summary (4)--sql Multi-Table query technology