sql-1.sql

SELECT [JobTitle]
  FROM [AdventureWorks2012].[HumanResources].[Employee]


SELECT DISTINCT  [JobTitle], Gender
  FROM [AdventureWorks2012].[HumanResources].[Employee]


SELECT VacationHours + SickLeaveHours as daysOff, JobTitle,VacationHours, SickLeaveHours
FROM [AdventureWorks2012].[HumanResources].[Employee]

SELECT [CommissionPct] ,[SalesYTD],
      ([SalesYTD]/100)*[CommissionPct] as commision
  FROM [AdventureWorks2012].[Sales].[SalesPerson]


  SELECT [JobTitle] , Gender,
	CASE Gender
		WHEN 'F' THEN 'Woman'
		ELSE 'Men'
	END
FROM [AdventureWorks2012].[HumanResources].[Employee]
========================

SELECT [JobTitle] as job
  FROM [AdventureWorks2012].[HumanResources].[Employee]

  SELECT [JobTitle] = job
  FROM [AdventureWorks2012].[HumanResources].[Employee]

  SELECT [JobTitle] job
  FROM [AdventureWorks2012].[HumanResources].[Employee]

  SELECT        Sales.Currency.Name, Sales.CurrencyRate.CurrencyRateDate, Sales.CurrencyRate.FromCurrencyCode, Sales.CurrencyRate.ToCurrencyCode, Sales.CurrencyRate.AverageRate,  Sales.CurrencyRate.EndOfDayRate
FROM            
Sales.Currency INNER JOIN Sales.CurrencyRate 
ON Sales.Currency.CurrencyCode = Sales.CurrencyRate.FromCurrencyCode


SELECT        Sales.Currency.Name, Sales.CurrencyRate.CurrencyRateDate, Sales.CurrencyRate.FromCurrencyCode, Sales.CurrencyRate.ToCurrencyCode, Sales.CurrencyRate.AverageRate,  Sales.CurrencyRate.EndOfDayRate
FROM            
Sales.Currency a INNER JOIN Sales.CurrencyRate  b
ON a.CurrencyCode = b.FromCurrencyCode

SELECT        a.Name, b.CurrencyRateDate, b.FromCurrencyCode, b.ToCurrencyCode, b.AverageRate, b.EndOfDayRate, Sales.Currency.Name AS ToName
FROM            
Sales.Currency AS a INNER JOIN Sales.CurrencyRate AS b 
ON a.CurrencyCode = b.FromCurrencyCode 
INNER JOIN Sales.Currency 
ON b.ToCurrencyCode = Sales.Currency.CurrencyCode


SELECT        a.Name, b.CurrencyRateDate, b.FromCurrencyCode, b.ToCurrencyCode, b.AverageRate, b.EndOfDayRate
FROM            Sales.CurrencyRate AS b RIGHT OUTER JOIN
 Sales.Currency AS a ON b.FromCurrencyCode = a.CurrencyCode


SELECT        Production.Product.ProductID, Production.Product.Name
FROM            
Production.Product 
INNER JOIN Production.ProductSubcategory 
ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID 
INNER JOIN Production.ProductCategory 
ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID

SELECT top 10 Percent * FROM [Production].[Product]

SELECT  * FROM [Production].[Product]
order by ProductID

offset 10 rows fetch first 10 rows only


SELECT TOP 1000 [FirstName],
      SUBSTRING([FirstName],2,2)
  FROM [AdventureWorks2012].[Person].[Person]

SELECT TOP 1000 [FirstName],
      left([FirstName],2)
  FROM [AdventureWorks2012].[Person].[Person]

SELECT TOP 1000 [FirstName],
      right([FirstName],2)
  FROM [AdventureWorks2012].[Person].[Person]

SELECT TOP 1000 [FirstName],
      len([FirstName])
  FROM [AdventureWorks2012].[Person].[Person]

SELECT TOP 1000 [FirstName],
      DATALENGTH([FirstName])
  FROM [AdventureWorks2012].[Person].[Person]

SELECT TOP 1000 [FirstName],
      CHARINDEX('e',[FirstName],2)
  FROM [AdventureWorks2012].[Person].[Person]

SELECT TOP 1000 [FirstName],
      REPLACE([FirstName],'e','ii')
  FROM [AdventureWorks2012].[Person].[Person]

  UPPER()
  LOWER()

  SELECT TOP 1000 [FirstName] 
  FROM [AdventureWorks2012].[Person].[Person]
  where firstname like 'sy_d' // single charactor

SELECT TOP 1000 [FirstName] 
  FROM [AdventureWorks2012].[Person].[Person]
  where firstname like 'sy[def]d'

  SELECT TOP 1000 [FirstName] 
  FROM [AdventureWorks2012].[Person].[Person]
  where firstname like 'sy[t-z]d' // charactor range

  SELECT TOP 1000 [FirstName] 
  FROM [AdventureWorks2012].[Person].[Person]
  where firstname like 'sy[^t-z]d' // not in

SELECT SYSDATETIME()
    ,SYSDATETIMEOFFSET()
    ,SYSUTCDATETIME()
    ,CURRENT_TIMESTAMP
    ,GETDATE()
  ,SYSDATETIME()
    ,GETUTCDATE();

select DATEADD(Y,1, getdate() )
select EOMONTH( getdate() ) , EOMONTH( getdate(), 5 )
select SWITCHOFFSET( SYSDATETIMEOFFSET(), 5) // change the time zone
select SYSDATETIME(), TODATETIMEOFFSET( SYSDATETIME() , 5) // convert date time into data time offset
select DATENAME(m, getdate() ), 
DATEPART(m, getdate() ), day( getdate() ), month( getdate() ), year(getdate())
select DATEDIFF(d, getdate(), dateadd(d,5,getdate()) ) 
select ISDATE('01/01/2012')

// rank data compared to others
SELECT TOP(10) BusinessEntityID, Rate, 
       RANK() OVER (ORDER BY Rate DESC) AS RankBySalary
FROM HumanResources.EmployeePayHistory AS eph1
ORDER BY BusinessEntityID;
*******************************************
SELECT CAST(SYSDATETIME() AS date);
SELECT CAST( '12/01/2013' AS date);
SELECT CAST( '123' AS varchar) + CAST( '2' AS varchar) 
SELECT CAST( '123' AS int) + CAST( '2' AS int) 

http://msdn.microsoft.com/en-us/library/ms187928.aspx // chart/

USE AdventureWorks2012;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO

-- Use CONVERT.
USE AdventureWorks2012;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO
Convert to more than cast in mssql server, Currency, time, etc..
*******************************************
SELECT PARSE('02/12/2012' AS datetime2 USING 'en-US') AS parse_result; 

select CONVERT(date, 'Monday, 13 December 2010' ) << failes.
SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result;

SELECT TRY_PARSE('SQLServer' AS datetime2 USING 'en-US') AS try_parse_result; 
select try_CONVERT(date, 'Monday, 13 December 2010' )
*********************************************
SELECT ISNUMERIC('SQL') AS isnmumeric_result;

SELECT top 1000   productid, [ListPrice], 
      IIF([ListPrice] > 50, 'high','low') AS pricepoint
FROM Production.Product;

SELECT CHOOSE (3, 'a', 'b', 'c') 
SELECT CHOOSE (4, 'a', 'b', 'c') 

SELECT isnull([Title],'Dr.') ,[FirstName]  FROM [AdventureWorks2012].[Person].[Person]

SELECT isnull([Title],'Dr.') , COALESCE([Title],'Dr.'), 
isnull([Title],MiddleName) ,
COALESCE([Title],MiddleName,LastName) , 
[FirstName]  FROM [AdventureWorks2012].[Person].[Person]

select nullif(1,2)
select nullif(1,1)

SELECT AVG(ListPrice) , MIN(ListPrice), max(ListPrice),
sum(ListPrice), count(ListPrice), COUNT_BIG(ListPrice)
FROM Production.Product;

SELECT AVG(ListPrice) , MIN(ListPrice), max(ListPrice),
sum(ListPrice), count(ListPrice), COUNT_BIG(ListPrice), color
FROM Production.Product
group by color

SELECT AVG(ListPrice) , MIN(ListPrice), max(ListPrice),
sum(ListPrice), count(ListPrice) as c, COUNT_BIG(ListPrice), color
FROM Production.Product
group by color
having count(*) > 30

As a rule of thumb, use WHERE before GROUP BY and HAVING after GROUP BY. It is a rather primitive rule, but it is useful in 90+ %% of cases.

*********************** sub query
SELECT *
FROM Sales.SalesPerson where TerritoryID in
( select TerritoryID from Sales.SalesTerritory where name = 'Northwest' )

talk about Writing Correlated Subqueries
SELECT *
FROM Sales.SalesPerson as SalesQ
where TerritoryID in
( select TerritoryID from Sales.SalesTerritory where name = 'Northwest' and costytd = SalesQ.SalesLastYear)

========================
union vs union all

select * from [Sales].[Currency]
union all
select * from [Sales].[Currency]