SQL Afternoon

Select * from Person;
Select PersonLastName, PersonFirstName, PersonEmail
From Person;
Select PersonLastName, PersonFirstName, PersonEmail
From Person
order by PersonLastName;

Select PersonLastName, PersonFirstName, PersonEmail
From Person
order by PersonLastName Desc, PersonFirstName;

Select PersonLastName, PersonFirstName, PersonEmail
From Person
Where PersonLastName='Lewis'

Select * from Product
Where ProductPrice > 3

Select * from Product
Where ProductPrice between 3 and 5

Select * from Product
Where ProductPrice >=3 and ProductPrice <=5
Order by ProductPrice desc

Select * From Sale
Where SaleDate>'11/2/2016'and saleDate < '11/3/2016'

Select Month(SaleDate) as [Month], 
Year(SaleDate) as [Year] from Sale

Select Sale.SaleKey, SaleDate, PersonLastName, EmployeeKey,
ProductName,
SaleDetailPriceCharged,SaleDetailQuantity,
SaleDetailDiscount,SaleDetailSaleTaxPercent,
SaleDetailEatInTax
From Sale
inner join Person
on Sale.CustomerKey=Person.Personkey
inner Join SaleDetail
on Sale.SaleKey=SaleDetail.SaleKey
inner join Product
on Product.ProductKey=SaleDetail.ProductKey
Where Sale.SaleKey = 1

Insert into Sale(SaleDate, CustomerKey, EmployeeKey)
Values(GetDate(),5,2)
Insert into SaleDetail(SaleKey, ProductKey, 
SaleDetailPriceCharged, SaleDetailQuantity, 
SaleDetailDiscount, SaleDetailSaleTaxPercent, 
SaleDetailEatInTax)
Values(Ident_Current('Sale'), 3, 3.25,1,0,.09,.02),
(Ident_Current('Sale'), 5, 2.25,1,0,.09,.02)

Select * from SaleDetail

Select * from Person
Begin tran
Update Person Set PersonLastName='Smith'
Where PersonKey=3

Rollback tran
Commit tran