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.description,
			item.ListPrice,
			(SELECT	t2.LastCost
			FROM	tblitemCostHistory t2
			WHERE	t2.LastCostDate = (
				SELECT	MAX(t3.LastCostDate)
				FROM	tblitemCostHistory t3
				WHERE t3.itemID = t2.itemID
				)
			),
			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

*** I tried this, but it didn't work.






------------------------------
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
			)