microtechpoint首席执行官创始人brian alderman mctmodule 2 advanced select 2高级选择statementsod1.pptx

02|AdvancedSELECTStatementsBrianAlderman|MCT,CEO/FounderofMicroTechPointTobiasTernstrom|MicrosoftSQLServerProgramManager

CourseTopicsQueryingMicrosoftSQLServer2012JumpStart01|IntroducingSQLServer2012 SQLServertypesofstatements;otherSQLstatementelements;basicSELECTstatements02|AdvancedSELECTStatements DISTINCT,Aliases,scalarfunctionsandCASE,usingJOINandMERGE;Filteringandsortingdata,NULLvalues03|SQLServerDataTypes Introducedatatypes,datatypeusage,convertingdatatypes,understandingSQLServerfunctiontypes04|GroupingandAggregatingData Aggregatefunctions,GROUPBYandHAVINGclauses,subqueries;self-contained,correlated,andEXISTS;Views,inline-tablevaluedfunctions,andderivedtables|LunchBreak Eat,drink,andrechargefortheafternoonsession

AdvancedSELECTclauses(DISTINCT,aliases,CASE,andscalarfunctions)QuerymultipletablesusingJOINstatementsFilteringandsortingdataModuleOverview

AdvancedSELECTClauses

UnderstandingDISTINCTSpecifiesthatonlyuniquerowscanappearintheresultsetRemovesduplicatesbasedoncolumnlistresults,notsourcetableProvidesuniquenessacrosssetofselectedcolumnsRemovesrowsalreadyoperatedonbyWHERE,HAVING,andGROUPBYclausesSomequeriesmayimproveperformancebyfilteringoutduplicatespriortoexecutionofSELECTclause

SELECTDISTINCTsyntaxSELECTDISTINCTcolumnlistFROMtableorviewSELECTDISTINCTStoreIDFROMSales.Customer;StoreID-------12345709021898710

UsingaliasestorefertocolumnsColumnaliasesusingASColumnaliasesusing=AccidentalcolumnaliasesSELECTSalesOrderID,UnitPrice,OrderQtyASQuantityFROMSales.SalesOrderDetail;SELECTSalesOrderID,UnitPrice,Quantity=OrderQtyFROMSales.SalesOrderDetail;SELECTSalesOrderID,UnitPriceQuantityFROMSales.SalesOrderDetail;

UsingaliasestorefertotablesCreatetablealiasesintheFROMclauseusingASTablealiaseswithoutASUsingta