Transact SQL 101 - Lesson 09_01_SQL.txt

--Example 1
SELECT * 
FROM saleslt.productcategory
WHERE productcategoryid = 
		(SELECT productcategoryid FROM saleslt.product WHERE productid = 680)
		
SELECT *
FROM saleslt.product
WHERE productmodelid IN 
		(SELECT productmodelid FROM saleslt.productmodel WHERE [name] LIKE 'HL Mountain%')
	

--Example 2
SELECT c.firstname, c.lastname, c.companyname
FROM saleslt.customer c 
	INNER JOIN saleslt.salesorderheader s ON c.customerid = s.customerid

SELECT c.firstname, c.lastname, c.companyname
FROM saleslt.customer c 
WHERE c.customerid IN 
		(SELECT customerid FROM saleslt.salesorderheader)
	
	
--Example 3	
--UPDATE
SELECT * 
FROM saleslt.product
WHERE productmodelid = 
		(SELECT productmodelid FROM saleslt.productmodel WHERE [name] = 'HL Road Frame')

UPDATE saleslt.product
SET listprice = 1500
WHERE productmodelid = 
		(SELECT productmodelid FROM saleslt.productmodel WHERE [name] = 'HL Road Frame')


--Example 4
--DELETE (No Records deleted due to Referential Integrity Constraints)
DELETE FROM saleslt.address
WHERE addressid IN 
		(SELECT addressid FROM saleslt.address WHERE city = 'Austin')