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