10.11 Sub-query, table connection

--Data conversion

SelectIds,code,name,brand, (cast( Year(time) as varchar(Ten))+'years'+cast(Month(time) as varchar(Ten))+'Month'+cast( Day(time) as varchar(Ten))+'Day') asTime, Oil,powers,exhaust,price, pic fromCarSelect *  fromCarwhere Ceiling(oil)>=8.0Select *  fromCarwhere Ceiling(oil)>8.0--Sub-query--Use the result of a query statement as a numeric value or a set of values--all of, any one, modifier--in (', ') is not within the value range of the parentheses in the value rangeSelectPrice fromCarwhereName='BMW 3 Series 325i Fashion type'Select * fromCarwhereName like '% Audi%' andPrice>=42.38Select * fromCarwhereName like '% Audi%' andPrice>=(SelectPrice fromCarwhereName='BMW 3 Series 325i Fashion type')Select * fromCarwhereName like '% Audi%' andPrice>(Select Max(Price) fromCarwhereName like'% BMW%')Select * fromCarwhereName like '% Audi%' andPrice>all(SelectPrice fromCarwhereName like '% BMW%' )Select * fromCarwhereName like '% Audi%' andPrice>any(SelectPrice fromCarwhereName like '% BMW%' )--in (', ') within the value range not in () is not the value of the parentheses = = or usageSelect *  fromCarwhereBrand='b001'orBrand='b002'orBrand='b003' Select *  fromCarwhereBrandinch('b001','b002','b003')Select *  fromCarwhereBrand not inch('b001','b002','b003')--table Connection (inner) join on inner connectionSelect*  fromCarSelect* fromBrandSelectCode,name,brand,brand_code fromCarJoinBrand onCar.brand=brand Brand_Code.SelectCode,name,brand,brand_code fromCarInner JoinBrand onCar.brand=brand Brand_Code.SelectCode,name,brand,brand_code fromCar Left JoinBrand onCar.brand=brand Brand_Code.SelectCode,name,brand,brand_code fromCar Right JoinBrand onCar.brand=brand Brand_Code.--Sub-querySelectCode,name, (SelectBrand_Name fromBrandwhereCar.brand=Brand. Brand_Code) asBrand_Name fromCar--table stitching, Cartesian product, similar for the poor liftingSelect* fromCar,brandSelect* fromCar,brandwhereCar.brand=Brand Brand_Code.

10.11 Sub-query, table connection