Advanced query for SQL database

1. Connection Query

SELECT * FROM Info,nation--to form a Cartesian product

SELECT * from info,nation where info.nation = Nation.code

Select Info.code,info.name,sex,nation.name,birthday from info,nation where info.nation = Nation.code

SELECT * FROM Info join Nation in info.nation = Nation.code--join on form

2. Joint queries

Select Code,name from Info
Union
Select Code,name from Nation

3. Sub-query

An SQL statement contains two queries, one of which is the parent query (the outer query), the other is a subquery (the inner query), and the result of the subquery query is the condition of the parent query.

--Query the people for the Han nationality of all personnel information
SELECT * from Info where Nation = (select Code from Nation where Name = ' Han ')

(1) Unrelated subqueries

Subqueries can be executed independently, and the subquery does not have a relationship with the parent query

--Inquiry series is all the car information of BMW 5 system
SELECT * from Car where brand = (select Brand_Code from Brand where brand_name = ' BMW 5 Series ')

(2) Related sub-query

--find cars with fuel consumption below the average fuel consumption of the series

SELECT * from Car where oil< (average fuel consumption for this series)
Select AVG (oil) from Car where Brand = (this series)

SELECT * from Car a where oil< (select Avg. from car b where B.brand = A.brand)

Advanced query for SQL database