--create new column using customer last & first
SELECT FirstName + " " + Lastname AS 'Customer Name' FROM Customers



--calculate sum of all records
SELECT SUM(Tax) AS 'Taxes Collected' FROM Orders


---calculate number of records
SELECT COUNT(*) AS 'Total Orders' FROM Orders


---here we can compute and create a new column
SELECT ProductID, ProductPrice AS 'Regualar Price', ProductPrice - 5 AS 'Sale Price'
FROM Products


---find cheapest/max/average item
SELECT Min(ProductPrice) FROM products
SELECT Max(ProductPrice) FROM products
SELECT Avg(ProductPrice) FROM products


---get current date
SELECT GetDate()

---determine how old an order is
SELECT datediff(dd, getdate(),OrderDateTime) FROM Orders


---parse a string
SELECT substring(ProductDescription,3,5) FROM Products

SELECT right(ProductDescription,5) FROM Products
SELECT left(ProductDescription,5) FROM Products

SELECT upper(ProductDescription) FROM Products
SELECT lower(ProductDescription) FROM Products


---search for a match
SELECT * FROM Customers WHERE LastName LIKE 'J%'


---use the IN clause to simplify searches to find people in several states
SELECT * FROM Customers WHERE State IN ('CA', 'NV', 'ID')

---ordering our result
SELECT * FROM Customers ORDER BY LastName ASC
SELECT * FROM Customers ORDER BY LastName DESC

SELECT * FROM Customers ORDER BY LastName, FirstName


---calculate what each customer paid in shipping
SELECT CustomerID, SUM(Shipping) FROM Orders
GROUP BY CustomerID

SELECT CustomerID, SUM(Shipping) FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) >= 3


--------------------JOIN Tables for Queries------------------

SELECT Products.ProductID, Products.ProductName, ProductTypes.ProductTypeName
FROM Products INNER JOIN ProductTypes ON Products.ProductID = ProductTypes.ProductTypeID


---shorten it using an alias

SELECT p.ProductID, p.ProductName, pt.ProductTypeName
FROM Products p INNER JOIN ProductTypes pt
ON p.ProductID = pt.ProductTypeID



SELECT p.* pt.ProductTypeName
FROM Products p INNER JOIN ProductTypes pt
ON p.ProductID = pt.ProductTypeID
WHERE p.ProductPrice > 15.95