1, Multi table join query method

If the information source of the query is multiple tables, a multi table connection query can be established by connecting two tables.
The syntax of three table connection query is as follows:

SELECT feldlist FROM table1 JOIN table2 ON
 table1.column1=table2.column2_1 JOIN table3 on 
 mable2.colamn2_2=table3.column3[where condition]

In daily data query application practice, the data to be obtained comes from more than three tables. At this time, it needs to be queried through multi table connection.
For example: obtain the license plate number, model and driver name of all non air-conditioned vehicles, the line number of the line, and the information of the starting station and terminal station.
The code is as follows:

select d.name Driver's name,v.plateNo license plate number,v.model model,l.lineNo line
 number,l.from_station starting station, l.end_station Terminus 
from vehicle v 
JOIN driver d ON v.driverID=d.driverID
JOIN line l ON l.lineID=v.lineID
WHERE type='Non air conditioned vehicle';

Execution result:

2, Simple join query

If you directly list all the tables to be joined in the FROM clause, and then specify the join condition in the WHERE clause, this is a simple multi table query.
The connection syntax of the two tables is as follows:

SELECT fieldlist from table1,table2
WHERE table1.column1=table2.column2[and Other conditions]

The three table connection syntax is as follows:

ELECT fieldlist from table1,table2，table3
WHERE table1.column1=table2.column2_1 and 
table2.column2_2=table3.column3[and Other conditions]

example:
1. Obtain the license plate number, model and driver's name of all non air-conditioned vehicles, route number of the route, starting station and terminal station information.
The code is as follows:

SELECT name,plateNo,model,lineNo,from_station,end_station 
from vehicle v,driver D,line l 
where v.driverID=D.driverID
and v.lineID=l.lineID
and type='Non air conditioned vehicle';

Execution result:

2. Obtain the information of all drivers of the second bus company. The driver's name, ID card, gender and telephone number are required to be output.
The code is as follows:

SELECT name,licenseNo,gender,phone
from vehicle v,driver d,line l 
where v.driverID=d.driverID
and v.lineID=l.lineID
and company='Bus Company II';

Execution result:

Job:
1. Count the total number of stops on the route of each bus company
The code is as follows:

SELECT company,COUNT(*)
FROM line_station ls,line l
WHERE ls.lineNo=l.lineNo
GROUP BY company ORDER BY COUNT(*)

Execution result:

2. Calculate the number of bus lines passing through the station "Jiefang Avenue 4th Road" and the number of operating vehicles
The code is as follows:

SELECT COUNT(*),sum(number)
from line_station ls,line l
WHERE ls.lineNo=l.lineNo
and station='Gutian 4th Road, Jiefang Avenue'

Execution result:
3. Calculate the number of drivers of each bus line, and display the grouping information with the number of drivers greater than 3, which is displayed in reverse order according to the number of drivers
The code is as follows:

SELECT lineNo ,COUNT(*)Number of drivers 
from driver d,vehicle v,line l
WHERE d.driverID=v.driverID
AND v.lineID=l.lineID
GROUP BY lineNo
having count(*)>3
ORDER BY Number of drivers desc;

Execution result:

4. Count the sales quantity and sales amount of each commodity, and display the commodity name, sales volume and sales amount in ascending order of sales volume and sales amount
The code is as follows:

SELECT
	g.goodsName Trade name,
	sum( od.quantity ) sales volume,
	sum( od.quantity * g.unitPrice ) sales amount 
FROM
	goods g
	LEFT JOIN ordersdetail od ON g.goodsID = od.goodsID 
GROUP BY
	g.goodsID 
ORDER BY
	sales volume,sales amount;

Execution result:

5. Calculate the amount of each order. It is required to display the order ID, order date, order amount and customer name in ascending order of order date and descending order of order amount
The code is as follows:

SELECT o.ordersID order,o.ordersDate Order date ,sum(od.quantity*g.unitPrice) Order amount,c.cName Customer name
FROM orders o,ordersdetail od,goods g,customer c
WHERE o.ordersID=od.ordersID
AND od.goodsID=g.goodsID 
AND o.customerID=c.customerID
GROUP BY o.ordersID
ORDER BY Order date ,Order amount desc;

Execution result:

That's all for today!