1-

CREATE VIEW VItemCostHistory AS
SELECT	item.itemID,
		item.description,
		item.ListPrice,
		h1.LastCost,
		h1.LastCostDate
FROM		TblItem item
LEFT JOIN	tblitemCostHistory h1
ON		h1.itemID = item.itemID
WHERE	h1.LastCostDate = 	(
					SELECT	MAX(h2.LastCostDate)
					FROM	tblitemCostHistory h2
					WHERE h2.itemID = h1.itemID
					)

----------
CREATE VIEW VShippedQty AS
SELECT	ol.orderID "OrderID",
		ord.OrderDate,
		cust.LastName + ', '+ SUBSTRING(cust.firstname,1,1)+'.' "CustomerName",
		ol.itemID "ItemID",
		ol.quantity "QuantityOrdered",
		ISNULL(SUM(sl.QtyShipped), 0) "TotalShipped",
		ol.quantity - ISNULL(SUM(sl.QtyShipped), 0) "quantityremaining"
FROM		TblOrderLine ol
LEFT JOIN	TblOrder	ord
ON		ord.orderID = ol.OrderID
LEFT JOIN	TblShipLine sl
ON		sl.ItemID = ol.ItemID AND
		sl.OrderID = ol.OrderID
LEFT JOIN	TblCustomer cust
ON		ord.customerID = cust.customerID
GROUP BY	ol.orderID,
		cust.LastName,
		cust.FirstName,
		ol.ItemID,
		ord.OrderDate,
		ol.quantity
	
----------

SELECT	v.orderid "Order ID",
		CONVERT(VARCHAR, v.OrderDate, 101) "Order Date",
		v.CustomerName "Customer Name",
		v.ItemID "Item ID",
		item.description "Item Name",
		v.QuantityOrdered "Quantity Ordered",
		v.TotalShipped "Total Shipped",
		v.quantityremaining "Quantity Left to Ship",
		CASE
			WHEN v.TotalShipped = 0
				THEN 'Not Shipped'
			WHEN v.QuantityOrdered = v.TotalShipped
				THEN 'Completed'
			WHEN v.TotalShipped > v.QuantityOrdered
				THEN 'Over Shipped'
			ELSE	 'Partially Shipped'
		END "Ship Status"
FROM		VShippedQty v
LEFT JOIN tblItem item
ON		v.itemID = item.itemID

****************************************
2-
SELECT	v.orderid "Order ID",
		v.CustomerName "Customer Name",
		v.ItemID "Item ID",
		item.description "Item Name",
		v.QuantityOrdered "Quantity Ordered",
		v.TotalShipped "Total Shipped",
		v.TotalShipped-v.QuantityOrdered "Quantity Overshipped"
FROM		VShippedQty v
LEFT JOIN tblItem item
ON		v.itemID = item.itemID
WHERE	v.TotalShipped > v.QuantityOrdered

****************************************
3-
SELECT	cust.lastname + ', ' + cust.firstName "Customer Name",
		ol.orderid "Order ID",
		CONVERT(VARCHAR, OrderDate, 101) "Date of Order",
		ol.ItemID "Item ID",
		v.description "Item Name",
		ol.listprice "Price Paid",
		v.LastCost,
		CONVERT(VARCHAR, v.LastCostDate, 101) "Last Cost Date"
FROM	TblOrderLine ol
LEFT JOIN tblOrder ord
ON		ol.orderID = ord.orderID
LEFT JOIN tblCustomer cust
ON		cust.customerID = ord.customerID
LEFT JOIN VItemCostHistory v
ON		ol.itemID = v.itemID
WHERE	ol.listprice = (
		SELECT	MIN(ol2.listprice)
		FROM	TblOrderLine ol2
		LEFT JOIN TblItem item
		ON		ol2.itemID = item.itemID
		WHERE	item.description = 'Knowledge Management:  Create a Learning Organization'
		)

****************************************
4-
create view VOrderLine AS
SELECT	cust.lastname,
		cust.firstName,
		cust.customerID,
		ol.orderid,
		ord.OrderDate,
		ol.ItemID,
		ol.quantity "QuantityOrdered",
		ol.listprice "PricePaid",
		item.description,
		item.listprice "itemlistprice"
FROM	TblOrderLine ol
LEFT JOIN tblOrder ord
ON		ol.orderID = ord.orderID
LEFT JOIN tblCustomer cust
ON		cust.customerID = ord.customerID
LEFT JOIN tblItem item
ON		ol.itemID = item.itemID

-------

SELECT	v1.lastname + ', ' + v1.firstName "Customer Name",
		v1.orderid "Order ID",
		CONVERT(VARCHAR, v1.orderdate, 101) "Order Date",
		v1.itemID "Item ID",
		v1.description "Item Name",
		v1.itemlistprice "Item List Price",
		vhist.LastCost "Most Current Last Cost",
		CONVERT(VARCHAR, vhist.LastCostDate, 101) "Last Cost Date"
FROM	VOrderLine v1
LEFT JOIN VItemCostHistory vhist
ON		v1.itemID = vhist.itemID
WHERE	orderdate = (
		select MAX(orderdate)
		from VOrderLine v2
		where v2.description = 'Cranium WOW Edition'
		)
AND		v1.description = 'Cranium WOW Edition'

****************************************
5-
SELECT	v1.orderid "Order ID",
		CONVERT(VARCHAR, v1.orderdate, 101) "Order Date",
		v1.lastname "Customer Last Name",
		v1.itemID "Item ID",
		v1.description "Item Name",
		v1.QuantityOrdered,
		v1.PricePaid,
		vhist.LastCost "Most Current Last Cost",
		v1.PricePaid - vhist.LastCost "pricediff",
		(v1.PricePaid - vhist.LastCost) * v1.QuantityOrdered "extendeddiff"
FROM	VOrderLine v1
LEFT JOIN VItemCostHistory vhist
ON		v1.itemID = vhist.itemID

-----

CREATE VIEW VTotalOrderProfit AS
SELECT	v1.orderid,
		SUM((v1.PricePaid - vhist.LastCost) * v1.QuantityOrdered ) "TotalOrderProfit"
FROM	VOrderLine v1
LEFT JOIN VItemCostHistory vhist
ON		v1.itemID = vhist.itemID
GROUP BY v1.orderid

-------

SELECT	v1.orderID
FROM	Vtotalorderprofit v1
WHERE	v1.totalorderprofit = (
		SELECT	MAX(totalorderprofit)
		FROM	Vtotalorderprofit
		)

------

SELECT	v1.orderid "Order ID",
		CONVERT(VARCHAR, v1.orderdate, 101) "Order Date",
		v1.lastname "Customer Last Name",
		v1.itemID "Item ID",
		v1.description "Item Name",
		v1.QuantityOrdered,
		v1.PricePaid,
		vhist.LastCost "Most Current Last Cost",
		v1.PricePaid - vhist.LastCost "pricediff",
		(v1.PricePaid - vhist.LastCost) * v1.QuantityOrdered "extendeddiff"
FROM	VOrderLine v1
LEFT JOIN VItemCostHistory vhist
ON		v1.itemID = vhist.itemID
WHERE	v1.orderID = (
		SELECT	v1.orderID
		FROM	Vtotalorderprofit v1
		WHERE	v1.totalorderprofit = (
				SELECT	MAX(totalorderprofit)
				FROM	Vtotalorderprofit
				)
		)

**********************************************
6-
CREATE VIEW VTotalItemQuantity AS
SELECT	cust.lastname,
		cust.firstName,
		cust.customerID,
		cust.phone,
		cust.state,
		ol.orderid,
		ord.OrderDate,
		SUM(ol.quantity) "TotalItemQuantity"
FROM	TblOrderLine ol
LEFT JOIN tblOrder ord
ON		ol.orderID = ord.orderID
LEFT JOIN tblCustomer cust
ON		cust.customerID = ord.customerID
LEFT JOIN tblItem item
ON		ol.itemID = item.itemID
GROUP BY	cust.lastname,
		cust.firstName,
		cust.customerID,
		cust.phone,
		cust.state,
		ol.orderid,
		ord.OrderDate
			
------
SELECT	lastname + ', ' + firstname "Customer Name",
		'('+SUBSTRING(Phone,1,3)+') '+ SUBSTRING(Phone,5,12) "Phone Number",
		UPPER(state) "State",
		TotalItemQuantity "Sum of Items Ordered"
FROM	VTotalItemQuantity
WHERE	totalitemquantity = (
			SELECT	MAX(totalitemquantity)
			FROM	VTotalItemQuantity
			)

***********************************************
7-
CREATE VIEW VOpenOrders AS
SELECT	DISTINCT(orderID)
FROM	VShippedQty
WHERE	quantityremaining > 0
----
SELECT	DISTINCT(orderID),
		orderdate
FROM	VShippedQty
WHERE	quantityremaining > 0

-------

SELECT	v.orderid "Order ID",
		CONVERT(VARCHAR, v.OrderDate, 101) "Order Date",
		v.CustomerName "Customer Name",
		v.ItemID "Item ID",
		item.description "Item Name",
		v.QuantityOrdered "Quantity Ordered",
		v.TotalShipped "Total Shipped",
		v.quantityremaining "Quantity Left to Ship",
		CASE
			WHEN v.TotalShipped = 0
				THEN 'Not Shipped'
			WHEN v.QuantityOrdered = v.TotalShipped
				THEN 'Completed'
			WHEN v.TotalShipped > v.QuantityOrdered
				THEN 'Over Shipped'
			ELSE	 'Partially Shipped'
		END "Ship Status"
FROM		VShippedQty v
LEFT JOIN tblItem item
ON		v.itemID = item.itemID
WHERE	v.orderID IN (
				SELECT	OrderID
				FROM	VOpenOrders
				)


***************************************
8-
SELECT	v.orderid "Order ID",
		CONVERT(VARCHAR, v.OrderDate, 101) "Order Date",
		v.CustomerName "Customer Name",
		v.ItemID "Item ID",
		item.description "Item Name",
		v.QuantityOrdered "Quantity Ordered",
		v.TotalShipped "Total Shipped",
		v.quantityremaining "Quantity Left to Ship",
		CASE
			WHEN v.TotalShipped = 0
				THEN 'Not Shipped'
			WHEN v.QuantityOrdered = v.TotalShipped
				THEN 'Completed'
			WHEN v.TotalShipped > v.QuantityOrdered
				THEN 'Over Shipped'
			ELSE	 'Partially Shipped'
		END "Ship Status"
FROM		VShippedQty v
LEFT JOIN tblItem item
ON		v.itemID = item.itemID
WHERE	v.orderID IN (
				SELECT	OrderID
				FROM	VOpenOrders
				)
AND		v.OrderDate = (
				SELECT	MIN(OrderDate)
				FROM	VOpenOrders
				)

***************************************
9-
SELECT	v.orderID "Order ID",
		v.orderdate "Date of Order",
		v.customerID "Customer ID",
		v.lastname +', '+ UPPER(SUBSTRING(v.firstname,1,1)) "Customer Name",
		(SELECT	CONVERT(VARCHAR, MIN(dateshipped), 101)
		FROM	TblShipline t
		WHERE	t.orderID = v.orderID
		) "First Date Shipped",
		(SELECT	CONVERT(VARCHAR, MAX(dateshipped), 101)
		FROM	TblShipline t
		WHERE	t.orderID = v.orderID
		) "Last Date Shipped"
FROM	VTotalItemQuantity v
WHERE	orderID NOT IN (
		SELECT	orderID
		FROM	VOpenOrders
)
ORDER BY v.orderID

*****************************************
10-
insert into tblitemlocation values
('B67466','30',4)
insert into tblitemlocation values
('B67466','20',3)

SELECT	ItemID,
		SUM(QtyOnHand) "totalonhand"
FROM	TblItemLocation
GROUP BY ItemID

-----
CREATE VIEW Vitemtotalleft AS
SELECT	itemID,
		SUM(quantityremaining) "totalleft"
FROM	VShippedQty
GROUP BY itemID
HAVING	SUM(quantityremaining) > 0

-----

SELECT	TblItem.ItemID "Item ID",
		TblItem.description "Item Name",
		v.totalleft "Total Left to Ship",
		SUM(TblItemLocation.QtyOnHand) "Total On Hand",
		v.totalleft - SUM(TblItemLocation.QtyOnHand) "Total Short"
FROM	TblItemLocation 
LEFT JOIN TblItem
ON		TblItemLocation.itemID = tblitem.itemID
LEFT JOIN Vitemtotalleft v
ON		TblItemLocation.itemID = v.itemID
GROUP BY	TblItem.ItemID,
			TblItem.description,
			v.totalleft
HAVING	v.totalleft > SUM(TblItemLocation.QtyOnHand)

**********************************************
11-
SELECT	'CUSTOMER: '++ UPPER(lastname+', '+SUBSTRING(firstname,1,1)+'.') 
		"Customer or Item Not Purchased in January"
FROM	TblCustomer
WHERE	customerID NOT IN (
			SELECT	DISTINCT(customerID)
			FROM	VOrderLine
			WHERE	DATEPART(mm, OrderDate) = 1
			AND		DATEPART(yyyy, OrderDate) = DATEPART(yyyy, GETDATE())
			)
UNION
SELECT	'ITEM: '+Description
FROM	TblItem
WHERE	itemID NOT IN (
			SELECT	DISTINCT(itemID)
			FROM	VOrderLine
			WHERE	DATEPART(mm, OrderDate) = 1
			AND		DATEPART(yyyy, OrderDate) = DATEPART(yyyy, GETDATE())
			)
ORDER BY 1

------























######################################################
1-
SELECT		CONVERT(VARCHAR, OrderDate, 101) "Date of Order",
			orderID,
			lastname + ', ' + SUBSTRING(firstname,1,1)+'.' "Customer Name",	
			'('+SUBSTRING(Phone,1,3)+') '+ SUBSTRING(Phone,5,12) "Phone Number"
FROM		TblOrder	ord
LEFT JOIN	TblCustomer cust
ON			ord.CustomerID = cust.CustomerID
WHERE		DATEPART(yyyy, OrderDate) = DATEPART(yyyy, getdate()) AND
			DATEPART(mm, OrderDate) = '01'
ORDER BY	OrderDate DESC

------------------------------
2-
SELECT		CONVERT(VARCHAR, OrderDate, 101) "Date of Order",
			ord.orderID "Order Number",
			lastname + ', ' + SUBSTRING(firstname,1,1)+'.' "Customer Name",	
			'('+SUBSTRING(Phone,1,3)+') '+ SUBSTRING(Phone,5,12) "Phone Number",
			item.ItemID "Item Number",
			item.description "Item Name",
			ol.quantity "Qty on Order",
			ol.ListPrice "Price",
			ol.quantity * ol.ListPrice "Extended Price"
FROM		TblOrder	ord
LEFT JOIN	TblCustomer cust
ON			ord.CustomerID = cust.CustomerID
LEFT JOIN	TblOrderLine ol
ON			ord.orderID = ol.OrderID
LEFT JOIN	TblItem item
ON			ol.ItemID = item.ItemID
WHERE		DATEPART(yyyy, OrderDate) = DATEPART(yyyy, getdate()) AND
			DATEPART(mm, OrderDate) = '01'
ORDER BY	OrderDate DESC


------------------------------
3-
SELECT		item.ItemID "Item Number",
			item.description "Item Name",
			ISNULL(LocationID, '**') "Location Number",
			ISNULL(QtyOnHand, 0) "Quantity on Hand"
FROM		TblItem item
LEFT JOIN	TblItemLocation loc
ON			item.ItemID = loc.ItemID

------------------------------
4-
SELECT	CustomerID "Customer Number",
		firstname + ' ' + lastname "Customer Name",
		UPPER(SUBSTRING(city,1,1))+LOWER(SUBSTRING(city,2,20)) "City",
		UPPER(state) "State",
		Zip,
		CONVERT(VARCHAR, FirstBuyDate, 107) "First Buy Date"
FROM	TblCustomer
WHERE   CustomerID NOT IN 
        (
		SELECT CustomerID
        FROM TblOrder
		)
ORDER BY CustomerID

------------------------------
5-
SELECT		ol.orderID "Order Number",
			ol.itemID "Item Number",
			item.description "Item Name",
			ol.ListPrice "Selling Price",
			item.ListPrice "List Price"
FROM		TblOrderLine ol
LEFT JOIN	tblitem item
ON			ol.itemID = item.itemID
WHERE		ol.ListPrice = 
			(
			SELECT MAX(ListPrice)
			FROM TblOrderLine
			)

------------------------------
6-
SELECT		ol.itemID "Item Number",
			item.description "Item Name",
			ol.orderID "Order Number",
			ol.ListPrice "Selling Price",
			item.ListPrice "List Price",
			lastCost "Most Recent Cost",
			item.ListPrice - lastCost "Difference Between Price and Cost",
			CONVERT(VARCHAR, lastCostDate, 101) "Most Recent Purchase Date"
FROM		TblOrderLine ol
LEFT JOIN	tblitem item
ON			ol.itemID = item.itemID
LEFT JOIN	tblitemCostHistory
ON			tblitemCostHistory.itemID = item.itemID
WHERE		ol.ListPrice = 
			(
			SELECT	MAX(ListPrice)
			FROM	TblOrderLine
			)
AND			lastCostDate = 
			(
			SELECT	MAX(lastCostDate)
			FROM	tblItemCostHistory
			)

------------------------------
7-
SELECT		ord.orderID "Order Number",
			CONVERT(VARCHAR, OrderDate, 101) "Order Date",
			--lastname,
			lastname + ', ' + firstname "Customer Name",
			COUNT(ol.ItemID) "Number of Items",
			SUM(ol.quantity) "Total Quantity Ordered",
			SUM(ol.quantity * ol.ListPrice) "Total Extended Price"
FROM		TblOrder	ord
LEFT JOIN	TblCustomer cust
ON			ord.CustomerID = cust.CustomerID
LEFT JOIN	TblOrderLine ol
ON			ord.orderID = ol.OrderID
LEFT JOIN	TblItem item
ON			ol.ItemID = item.ItemID
GROUP BY	ord.orderID,
			cust.lastname,
			cust.firstname,
			ord.OrderDate

------------------------------
8-
SELECT		ord.orderID "Order Number",
			CONVERT(VARCHAR, OrderDate, 101) "Order Date",
			--lastname,
			lastname + ', ' + firstname "Customer Name",
			COUNT(ol.ItemID) "Number of Items",
			SUM(ol.quantity) "Total Quantity Ordered",
			SUM(ol.quantity * ol.ListPrice) "Total Extended Price"
FROM		TblOrder	ord
LEFT JOIN	TblCustomer cust
ON			ord.CustomerID = cust.CustomerID
LEFT JOIN	TblOrderLine ol
ON			ord.orderID = ol.OrderID
LEFT JOIN	TblItem item
ON			ol.ItemID = item.ItemID
GROUP BY	ord.orderID,
			cust.lastname,
			cust.firstname,
			ord.OrderDate
HAVING		SUM(ol.quantity * ol.ListPrice) > 3500

------------------------------
9-
SELECT		item.itemID,
			item.description,
			item.ListPrice,
			tblitemCostHistory.LastCost "LastCost",
			--MAX(tblitemCostHistory.LastCostDate) "LastCost",
			AVG(ol.ListPrice) "Average Selling Price",
			MIN(ol.ListPrice) "Minimum Selling Price",
			MAX(ol.ListPrice) "Maximum Selling Price"
FROM		TblItem item
LEFT JOIN	tblitemCostHistory
ON			tblitemCostHistory.itemID = item.itemID
LEFT JOIN	TblOrderLine ol
ON			ol.ItemID = item.ItemID
GROUP BY	item.itemID,
			item.description,
			item.ListPrice
HAVING		LastCostDate = 
			(
			SELECT	MAX(LastCostDate)
			FROM	tblitemCostHistory
			)

-------------

SELECT		item.itemID,
			item.description,
			item.ListPrice,
			--tblitemCostHistory.LastCost "LastCost",
			MAX(tblitemCostHistory.LastCostDate) "LastCostDate",
			AVG(ol.ListPrice) "Average Selling Price",
			MIN(ol.ListPrice) "Minimum Selling Price",
			MAX(ol.ListPrice) "Maximum Selling Price"
FROM		TblItem item
LEFT JOIN	tblitemCostHistory
ON			tblitemCostHistory.itemID = item.itemID
LEFT JOIN	TblOrderLine ol
ON			ol.ItemID = item.ItemID
GROUP BY	item.itemID,
			item.description,
			item.ListPrice

-------------

SELECT	LastCost
FROM	tblitemCostHistory t1
WHERE	LastCostDate = (
	SELECT	MAX(t1.LastCostDate)
	FROM	tblitemCostHistory t1
	WHERE itemID = 'A23441'
	)

-----------

SELECT	LastCost
			FROM	tblitemCostHistory t1
			WHERE	LastCostDate = (
				SELECT	MAX(t2.LastCostDate)
				FROM	tblitemCostHistory t2
				WHERE t2.itemID = item.itemID
				)

-------------

SELECT		item.itemID,
			item.description,
			item.ListPrice,
			--tblitemCostHistory.LastCost "LastCost",
			(SELECT	t2.LastCost
			FROM	tblitemCostHistory t2
			WHERE	t2.LastCostDate = (
				SELECT	MAX(t3.LastCostDate)
				FROM	tblitemCostHistory t3
				WHERE t3.itemID = t2.itemID
				)
			),
			MAX(t1.LastCostDate) "LastCostDate",
			AVG(ol.ListPrice) "Average Selling Price",
			MIN(ol.ListPrice) "Minimum Selling Price",
			MAX(ol.ListPrice) "Maximum Selling Price"
FROM		TblItem item
LEFT JOIN	tblitemCostHistory t1
ON			t1.itemID = item.itemID
LEFT JOIN	TblOrderLine ol
ON			ol.ItemID = item.ItemID
GROUP BY	item.itemID,
			item.description,
			item.ListPrice
----------------

SELECT	LastCost
FROM	tblitemCostHistory t1
WHERE	LastCostDate = (
	SELECT	MAX(t2.LastCostDate)
	FROM	tblitemCostHistory t2
	WHERE t2.itemID = t1.itemID
	)
** WORKS
------------------

SELECT		item.itemID,
			item.description,
			item.ListPrice,
			--tblitemCostHistory.LastCost "LastCost",
			MAX(hist1.LastCostDate) "LastCostDate",
			AVG(ol.ListPrice) "Average Selling Price",
			MIN(ol.ListPrice) "Minimum Selling Price",
			MAX(ol.ListPrice) "Maximum Selling Price"
FROM		TblItem item
LEFT JOIN	tblitemCostHistory hist1
ON			hist1.itemID = item.itemID
LEFT JOIN	TblOrderLine ol
ON			ol.ItemID = item.ItemID
GROUP BY	item.itemID,
			item.description,
			item.ListPrice
HAVING		item.ListPrice > 30
---------------------

SELECT		item.itemID,
			item.description,
			item.ListPrice,
			hist1.LastCost "LastCost",

			MAX(hist1.LastCostDate) "LastCostDate",
			AVG(ol.ListPrice) "Average Selling Price",
			MIN(ol.ListPrice) "Minimum Selling Price",
			MAX(ol.ListPrice) "Maximum Selling Price"
FROM		TblItem item
LEFT JOIN	tblitemCostHistory hist1
ON			hist1.itemID = item.itemID
LEFT JOIN	TblOrderLine ol
ON			ol.ItemID = item.ItemID
WHERE		hist1.LastCostDate = 
			(SELECT	MAX(hist1.LastCostDate)
			FROM	tblitemCostHistory hist1
			)
GROUP BY	item.itemID,
			item.description,
			item.ListPrice,
			hist1.LastCost
------------
SELECT		item.itemID,
			item.description,
			item.ListPrice,
			hist1.LastCost "LastCost",

			MAX(hist1.LastCostDate) "LastCostDate",
			AVG(ol.ListPrice) "Average Selling Price",
			MIN(ol.ListPrice) "Minimum Selling Price",
			MAX(ol.ListPrice) "Maximum Selling Price"
FROM		TblItem item
LEFT JOIN	tblitemCostHistory hist1
ON			hist1.itemID = item.itemID
LEFT JOIN	TblOrderLine ol
ON			ol.ItemID = item.ItemID
GROUP BY	item.itemID,
			item.description,
			item.ListPrice,
			hist1.LastCost
HAVING		hist1.LastCostDate = 
			(SELECT	MAX(hist2.LastCostDate)
			FROM	tblitemCostHistory hist2
			WHERE	hist1.itemID = hist2.itemID
			)
Msg 8121, Level 16, State 1, Line 1
Column 'tblitemCostHistory.ItemID' is invalid in the HAVING clause because it is not contained in either an aggregate fu

------------------

SELECT	t1.itemID,
		item.description "Item Name",
		LastCost "Most Recent Cost"
FROM		TblItem item
LEFT JOIN	tblitemCostHistory t1
ON			t1.itemID = item.itemID
WHERE	LastCostDate = (
	SELECT	MAX(t2.LastCostDate)
	FROM	tblitemCostHistory t2
	WHERE t2.itemID = t1.itemID
	)
ORDER BY t1.itemID

** By this query, I was able to show itemID, "Item Name", and "Most Recent Cost".

-----------------------------
SELECT		item.itemID "Item ID",
			item.description "Item Name",
			item.ListPrice "List Price",
			h1.LastCost "Most Recent Cost",
			AVG(ol.ListPrice) "Average Selling Price",
			MIN(ol.ListPrice) "Minimum Selling Price",
			MAX(ol.ListPrice) "Maximum Selling Price"
FROM		TblItem item
LEFT JOIN	tblitemCostHistory h1
ON			h1.itemID = item.itemID
LEFT JOIN	TblOrderLine ol
ON			ol.ItemID = item.ItemID
WHERE		h1.LastCostDate = (
			SELECT	MAX(h2.LastCostDate)
			FROM	tblitemCostHistory h2
			WHERE h2.itemID = h1.itemID
			)
GROUP BY	item.itemID,
			item.description,
			item.ListPrice,
			h1.LastCost
ORDER BY	item.itemID






------------------------------
10-
SELECT		lastname + ', ' + firstname "Customer Name",
			ord.orderID "Order Number",
			ol.itemID "Item Number",
			item.description "Item Name"
FROM		TblCustomer cust
LEFT JOIN	TblOrder	ord
ON			ord.CustomerID = cust.CustomerID
LEFT JOIN	TblOrderLine ol
ON			ord.orderID = ol.OrderID
LEFT JOIN	TblItem item
ON			ol.ItemID = item.ItemID
LEFT JOIN	TblShipLine sl
ON			sl.ItemID = ol.ItemID AND
			sl.OrderID = ol.OrderID
WHERE		LOWER(MethodShipped) = 'fedex'

------------------------------
11-

SELECT		olOuter.itemID "Item Number",
			item.description "Item Name",
			ord.orderID "Order Number",
			CONVERT(VARCHAR, ord.OrderDate, 101) "Order Date",
			olOuter.ListPrice "Actual Order Unit Price",
			(
			SELECT		AVG(olSel.ListPrice) 
			FROM		TblOrderLine olSel
			WHERE		olSel.itemID = olOuter.ItemID
			) "Average Order Price"
FROM		TblOrderLine olOuter
LEFT JOIN	TblOrder	ord
ON			ord.orderID = olOuter.OrderID
LEFT JOIN	TblItem item
ON			olOuter.ItemID = item.ItemID
WHERE		olOuter.ListPrice <
			(
			SELECT		AVG(olInner.ListPrice)
			FROM		TblOrderLine olInner
			WHERE		olInner.itemID = olOuter.ItemID
			)
GROUP BY	olOuter.ItemID,
			item.description,
			ord.orderID,
			ord.OrderDate,
			olOuter.ListPrice
ORDER BY	olOuter.itemID,
			olOuter.ListPrice DESC


------------------------------
12-
SELECT		ol.orderID "Order ID",
			CONVERT(VARCHAR, ord.OrderDate, 101) "Order Date",
			ol.itemID "Item ID",
			ol.quantity "Quantity Ordered",
			ISNULL(SUM(sl.QtyShipped), 0) "Total Quantity Shipped",
			ol.quantity - ISNULL(SUM(sl.QtyShipped), 0) "Quantity Left to Ship"
FROM		TblOrderLine ol
LEFT JOIN	TblOrder	ord
ON			ord.orderID = ol.OrderID
LEFT JOIN	TblShipLine sl
ON			sl.ItemID = ol.ItemID AND
			sl.OrderID = ol.OrderID
GROUP BY	ol.orderID,
			ol.ItemID,
			ord.OrderDate,
			ol.quantity
ORDER BY	ol.orderID,
			ol.ItemID

*** REPRINT

------------------------------
13-
SELECT		ol.orderID "Order ID",
			CONVERT(VARCHAR, ord.OrderDate, 101) "Order Date",
			ol.itemID "Item ID",
			item.description "Item Name",
			MAX(ol.quantity) "Quantity Ordered",
			ISNULL(SUM(sl.QtyShipped), 0) "Total Quantity Shipped",
			MAX(ol.quantity) - ISNULL(SUM(sl.QtyShipped), 0) "Quantity Left to Ship"
FROM		TblOrderLine ol
LEFT JOIN	TblOrder	ord
ON			ord.orderID = ol.OrderID
LEFT JOIN	TblShipLine sl
ON			sl.ItemID = ol.ItemID AND
			sl.OrderID = ol.OrderID
LEFT JOIN	TblItem item
ON			ol.ItemID = item.ItemID
GROUP BY	ol.orderID,
			ol.ItemID,
			item.description,
			ord.OrderDate
HAVING		MAX(ol.quantity) > ISNULL(SUM(sl.QtyShipped), 0)
ORDER BY	ol.orderID,
			ol.ItemID


------------------------------
14-
a-
UPDATE	TblCustomer
SET		State = UPPER(State)

b-
UPDATE	TblCustomer
SET		City = UPPER(City)

c-
UPDATE		ord
SET			ShipZip = ISNULL(44, ShipZip)
FROM		TblOrder	ord
LEFT JOIN	TblCustomer cust
ON			cust.CustomerID = ord.CustomerID

UPDATE		ord
SET			ShipZip = ISNULL(ord.ShipZip, 45)
FROM		TblOrder	ord
LEFT JOIN	TblCustomer cust
ON			cust.CustomerID = ord.CustomerID


UPDATE		ord
SET			ShipZip = ISNULL(
			ord.ShipZip

			, 45)
FROM		TblOrder	ord
LEFT JOIN	TblCustomer cust
ON			cust.CustomerID = ord.CustomerID


SELECT		MAX(cust.zip)
FROM		TblCustomer cust


UPDATE		ord
SET			ShipName = firstname + ' ' + lastname
FROM		TblOrder	ord
LEFT JOIN	TblCustomer cust
ON			cust.CustomerID = ord.CustomerID
WHERE		ShipName IS NULL


UPDATE		ord
SET			ShipName	= lastname + ', ' + firstname,
			ShipAddress	= Address,
			ShipZip		= Zip,
			ShipCountry	= Country,
			ShipPhone	= Phone
FROM		TblOrder	ord
LEFT JOIN	TblCustomer cust
ON			cust.CustomerID = ord.CustomerID
WHERE		ShipName IS NULL


SELECT		ShipName,
			lastname + ', ' + firstname,
			ShipAddress,
			Address,
			ShipZip,
			Zip,
			ShipCountry,
			Country,
			ShipPhone,
			Phone
FROM		TblOrder	ord
LEFT JOIN	TblCustomer cust
ON			cust.CustomerID = ord.CustomerID


SELECT	*



####################################################################

1-

SELECT		firstname + ' ' + lastname "Customer Name",
			city "City",
			state "State"
FROM		TblCustomer
WHERE		LOWER(State) = 'nv'
ORDER BY	lastname

------------------------------
2-

SELECT	lastname + ', ' + SUBSTRING(firstname,1,1)+'.' "Customer Name",	
	'('+SUBSTRING(Phone,1,3)+') '+ SUBSTRING(Phone,5,12) "Phone Number",
	UPPER(SUBSTRING(city,1,1))+LOWER(SUBSTRING(city,2,20)) "City",
	UPPER(state) "State"
FROM	TblCustomer
WHERE	LOWER(State) = 'nv'
ORDER BY lastname

-------------------------------
3-

SELECT		CONVERT(VARCHAR, OrderDate, 107) "Date of Order",
			OrderID "Order Number",
			CustomerID "Customer Number",
			DiscountCode "Discount Code"
FROM		TblOrder
WHERE		ShipAddress IS NULL
ORDER BY	OrderDate

-------------------------------
4-

SELECT		OrderID "Order Number",
			ItemID "Item Number",
			Quantity "Quantity Ordered",
			ListPrice "Price Paid",
			Quantity * ListPrice "Extended Price"
FROM		tblOrderLine
WHERE		ItemID = 'A23441'
ORDER BY	OrderID

-------------------------------
5-

SELECT		OrderID "Order Number",
			ItemID "Item Number",
			Quantity "Quantity Ordered",
			ListPrice "Price Paid",
			(Quantity * ListPrice) "Extended Price"
FROM		tblOrderLine
WHERE		Quantity * ListPrice > 500 
ORDER BY	OrderID

-------------------------------
6-

SELECT		OrderID "Order Number",
			ItemID "Item Number",
			Quantity "Quantity Ordered",
			ListPrice "Price Paid",
			(Quantity * ListPrice) "Extended Price"
FROM		tblOrderLine
WHERE		Quantity * ListPrice > 1000 AND ItemID='C34122'
ORDER BY	OrderID

-------------------------------
7-

SELECT		FirstName + ' ' + LastName "Customer Name",
			CONVERT(VARCHAR, FirstBuyDate, 107) "First Buy Date"
FROM		TblCustomer
WHERE		DATEPART(yyyy, FirstBuyDate) < 2000
ORDER BY	FirstName

-------------------------------
8-

SELECT		CONVERT(VARCHAR, MAX(FirstBuyDate), 107) "The Most Current First Buy Date"
FROM		TblCustomer


-------------------------------
9-

SELECT		AVG(ListPrice) "Average List Price"
FROM		tblItem

-------------------------------
10-

SELECT		AVG(QtyShipped) "Average Quantity Shipped via FedEx"
FROM		tblShipLine
WHERE		LOWER(MethodShipped) = 'fedex'

-------------------------------
11-

SELECT		ItemID "Item Number",
			SUM(Quantity) "Total Quantity Sold",
			MIN(ListPrice) "Minimum Price",
			MAX(ListPrice) "Maximum Price",
			AVG(ListPrice) "Average Price"
FROM		TblOrderLine
GROUP BY	ItemID
ORDER BY	ItemID

-------------------------------
12-

SELECT		ItemID "Item Number",
			SUM(Quantity) "Total Quantity Sold",
			MIN(ListPrice) "Minimum Price",
			MAX(ListPrice) "Maximum Price",
			AVG(ListPrice) "Average Price",
			CAST((MAX(ListPrice)/MIN(ListPrice)-1)*100 AS VARCHAR)+ ' %' "Percent Difference"
FROM		TblOrderLine
GROUP BY	ItemID
HAVING		MAX(ListPrice)/MIN(ListPrice) > 1.25
ORDER BY	ItemID

-------------------------------
13-

SELECT		OrderID "Order Number",
			CustomerID "Customer Number",
			CONVERT(VARCHAR, OrderDate, 107) "Date Ordered",
			CONVERT(VARCHAR, DATEADD(day, 40, OrderDate), 107) "Date 40 Days After Date Ordered",
			DATEDIFF(day, OrderDate, getdate()) "Number of Days Difference"
FROM		TblOrder
WHERE		DATEDIFF(day, OrderDate, getdate()) > 40
ORDER BY	OrderID

-------------------------------
14-

SELECT		ItemID "Item Number",
			COUNT(LocationID) "Number of Locations"
FROM		TblItemLocation
GROUP BY	ItemID
HAVING		COUNT(LocationID) > 2
ORDER BY	ItemID

-------------------------------
15-

SELECT		CustomerID "Customer Number",
			lastname + ', ' + SUBSTRING(firstname,1,1)+'.' "Customer Name",
			'('+SUBSTRING(Phone,1,3)+') '+ SUBSTRING(Phone,5,12) "Phone Number",
			UPPER(SUBSTRING(city,1,1))+LOWER(SUBSTRING(city,2,20)) "City",
			UPPER(state) "State",
			CONVERT(VARCHAR, FirstBuyDate, 107) "First Purchase Date"
FROM		TblCustomer
WHERE		FirstBuyDate = 
			(SELECT		MAX(FirstBuyDate)
			 FROM		TblCustomer)

-------------------------------
16-

SELECT		ItemID "Item Number",
			Description	"Item Name",
			ListPrice "List Price"
FROM		TblItem
WHERE		ItemID NOT IN
			(
			SELECT	DISTINCT ItemID
			FROM	tblOrderLine
			)