SQL SERVER Study

1. SQL SERVER Install
Free to try theDays. The latest is version 2014. 2. Attach, Detach can add or remove existing database files. The format of the database file is:. mdfSuffix 3. The E-r diagram can be established on the client. Primary note is the primary foreign key relationship, and the foreign key of one table is the primary key of the other table . 4. T-SQL StudyEG1:order by No.Example: Select ProductID, Name, Productnumber,color, Size, ListPrice from Production.Product o Rder by 2 Description: Here's 2, which means to sort by "Name".Sort by the number of column after select.  EG2:IsNull function: Determine whether the simulation of a data is empty.
For example: Select ProductID, Name, ProductNumber,IsNull(Color, "),IsNull(Size, '), ListPrice
From Production.Product ORDER by 2 Eg3:As keyword: alias the table column.
Example: Select ProductID, Name, ProductNumber, IsNull (Color, ") asColor, IsNull (Size, ') asSize, ListPrice
From Production.Product ORDER by 2 EG4:wildcard: Wildcard charactersExample: SELECT * from Production.Product where nameLike '%mountain% '----Wildcard% matches any zero or more characters SELECT * FROM Production.Product
where nameLike ' _ountain% 'EG5:In ; not inExample: SELECT * from production.product where sizeinch(' 20 ', ' 50 ', ' 55 ')  SELECT * FROM production.product where size not in(' 20 ', ' 50 ', ' 55 ') Eg6:is null; is not NULLExample: SELECT * from production.product where sizeis null  SELECT * FROM production.product where sizeis not null Eg6:and ; orExample: SELECT * from production.product where color = ' red ' andcolor = ' Black ' SELECT * from production.product where color = ' red 'orcolor = ' Black ' 5. Aggregation FunctionsEG1:count; distinctFor example: SELECTCount(SalesPersonID)
from [Sales]. [SalesOrderHeader] where SalesPersonID is not null Selectdistinct(SalesPersonID) from [Sales]. [SalesOrderHeader] where SalesPersonID is not NULL selectCount(distinct(SalesPersonID)) from [Sales]. [SalesOrderHeader] where SalesPersonID is not NULL EG2:AVG, Min, Max,sumFor example: SELECTAVG(SalesPersonID) as Averagetotalsales,Min(SalesPersonID) as Minimumtotalsales,Max(SalesPersonID) as Maximumtotalsales,Sum(SalesPersonID) as summarytotalsales from [Sales]. [SalesOrderHeader] Eg3:The classical T-SQL query!!!For example: Select SalesPersonID, OrderDate,Max(TotalDue) as maximumtotalsales from [Sales]. [SalesOrderHeader] where SalesPersonID is isn't null and OrderDate > ' 2016/1/1 ' GROUP by Sa Lespersonid, OrderDate hasMax(TotalDue) > 150000 ORDER BY SalesPersonID Desc 6. TipsEG1:How do i show line number?Solution: In the top row of the SS Management Studio Ribbon, find the Tool--and Options EG2:How to convert queries casing freely? Solution: In the top row of the SS Management Studio Ribbon, find Edit----------and--uppercase/lowercase

SQL SERVER Study