SQL Examples

Use AdventureworksLT

/*basic sql syntax
11/15/2010
Select statements*/
SELECT FirstName,LastName,Phone
FROM SalesLT.Customer;

--this uses the * wild card
Select *
from SalesLT.Customer;

Select ProductID
from SalesLT.SalesOrderDetail;

Select Distinct ProductID
From SalesLT.SalesOrderDetail;

Select *
From SalesLT.SalesOrderDetail;

Select ProductID, OrderQTY,
UnitPrice, UnitPriceDiscount,
(OrderQty * UnitPrice)[Unit Total]
from SalesLT.SalesOrderDetail
Order by ProductID DESC, (OrderQty * UnitPrice)Desc;

--Select with where clause

Select * from SalesLT.Address
Where City='Bothell'

Select * from SalesLT.SalesOrderDetail
Where UnitPrice <= 100

Select * From SalesLT.Customer
Where Lastname Like 'Bre_er'

Select * From SalesLT.Address
Where City='Bellevue' or City='Dallas'
Order by City

Select * from SalesLT.Customer
Where LastName Not Like 'G%'
And (CompanyName Like '%bike%'
Or CompanyName Like '%cycle%')

Select * From SalesLT.SalesOrderHeader
Where SalesOrderID Between 71774 and 71784

Select * From SalesLT.SalesOrderHeader
Where CreditCardApprovalCode Is Not Null

Select Distinct MONTH(OrderDate) as [Month],
YEAR(OrderDate) AS [Year], Day(OrderDate) as [Day]
from SalesLT.SalesOrderHeader

--aggregate functions
--count avg sum max min
Select COUNT(ProductID) From SalesLT.SalesOrderDetail

Select SUM(Linetotal) from SalesLT.SalesOrderDetail
Select Avg(Linetotal) from SalesLT.SalesOrderDetail
Select Max(Linetotal) from SalesLT.SalesOrderDetail
Select Min(Linetotal) from SalesLT.SalesOrderDetail

Select ProductID,UnitPrice, Count(ProductID)
from SalesLT.SalesOrderDetail
Group by ProductID, unitprice
Having Count(productID) <= 3
Order by ProductID