Advanced queries for databases

Advanced Query

1. Connection queries (extensions to columns)

The first form of:
SELECT * FROM Info,nation #会形成笛卡尔积
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 Info.code as ' codename ', Info.name as ' name ', sex as ' gender ', nation.name as ' national ', Birthday as ' birthday ' from info,nation where info.na tion = Nation.code #换表头

The second form of:
SELECT * from Info join Nation #join连接
SELECT * from Info join Nation on info.nation = Nation.code #join the ON keyword

2. Federated queries (expansion of rows)
SELECT * from Info where Nation = ' n002 '
Union
SELECT * from Info where Code = ' p002 '

3. Sub-query (unrelated subquery)
In an SQL statement, there are at least two queries, one of the results of a query as another B query criteria, A is a query or subquery,
b becomes the outer query or parent query.

Query people for "Han" Personnel information:
SELECT * from Info where Nation = (select Code from Nation where Name = ' Han ')

Query people for "Han" or "Hui" personnel information
SELECT * from Info where Nation in (select Code from Nation where name = ' Han ' or name = ' hui ')

4. Sub-query (related subquery)

Query the same series of fuel consumption than the average fuel consumption of car information

Sub-query: Select AVG (oil) from Car where Brand = "
Parent query: SELECT * from Car where oil< average fuel consumption

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

Advanced queries for databases