Joins, Inserts, Updates, Deletes

Select SalesOrderID, Name,
orderQTy,UnitPrice,UnitPriceDiscount,LineTotal
From SalesLT.Product p
INNER JOIN SalesLT.SalesOrderDetail od
ON p.ProductID=od.ProductID

Select FirstName, LastName,
Phone, City, StateProvince
From SalesLT.Customer c
Inner join SalesLT.CustomerAddress ca
on c.CustomerID=ca.CustomerID
Inner Join Saleslt.Address a
on a.AddressID=ca.AddressID

--alternate way to join tables
Select FirstName, LastName,
Phone, City, StateProvince
From SalesLT.Address a,SalesLT.Customer c,
SalesLT.CustomerAddress ca
WHERE c.CustomerID=ca.CustomerID
AND a.AddressID=ca.AddressID

Select p.ProductID, SalesOrderDetailID
From SalesLT.Product p
Left outer Join SalesLT.SalesOrderDetail so
on p.ProductID=so.ProductID
Where SalesOrderDetailID is null

Select * from SalesLT.Customer

Use MagazineSubscription

Insert into Customer (CustLastName,
CustFirstName, CustAddress,
CustCity, CustState,
CustZipcode, CustPhone)
Values ('Smith', 'Pedro','1000 Somewhere',
'Seattle','WA','98001','2065551234'),
('Sanches', 'Pedro','1000 Elsewhere','Seattle',
'WA','98001','2065554321')

Select * from Customer
Where CustLastName='Smith' or CustLastName='Sanches'

Begin Tran

Update Customer
Set CustLastName='Jordan',
CustAddress='2000 South Mercer Street'
Where CustID=1

Select * From Customer

Commit Tran
Rollback tran

Select * from MagazineDetail
Update magazineDetail
Set SubscriptionPrice = SubscriptionPrice * 1.05

Select * From Customer

Delete From Customer
Where CustID=13