murach's SQL SERVER 2016: Exercises 1

exercises1.sql

--Exercise 1
SELECT VendorContactLName AS [Last Name]
	, VendorContactFName AS [First Name]
	, VendorName AS [Vendor]
FROM Vendors
ORDER BY VendorContactLName
	, VendorContactFName; --Learned to use column numbers as ORDER BY's, 
                              --but not recommended & Semi-Colon use will be 
                              --required in future SQL Server editions so to 
                              --get used to ending each statement with one.

--Exercise 2
SELECT InvoiceNumber AS [Number]
	, InvoiceTotal AS [Total]
	, PaymentTotal + CreditTotal AS [Credits]
	, InvoiceTotal - (PaymentTotal + CreditTotal) AS [Balance] 
        --Learned to use arithmetic expressions.
FROM Invoices
ORDER BY [Balance] DESC;

--Exercise 3
SELECT VendorContactLName +
	', ' + VendorContactFName AS [Contact Name] 
        --Learned to format string data with literal values.
FROM Vendors
ORDER BY VendorContactLName
	+ VendorContactFName;

--Exercise 4
SELECT InvoiceTotal AS [Total]
	, (InvoiceTotal * 0.1) AS [10% of Total]
	, (InvoiceTotal * 0.1) + InvoiceTotal AS [Total + 10%]
FROM Invoices
WHERE InvoiceTotal > 1000 --Learned the WHERE clause.
ORDER BY [Total] DESC;

--Exercise 5
SELECT InvoiceNumber AS [Number]
	, InvoiceTotal AS [Total]
	, PaymentTotal + CreditTotal AS [Credits]
	, InvoiceTotal - (PaymentTotal + CreditTotal) AS [Balance]
FROM Invoices
WHERE InvoiceTotal BETWEEN 500 AND 10000 
--Learned the WHERE clause with logical operators.
ORDER BY [Total] DESC;

--Exercise 6 (This one took some trial & error, got it, though, I think!)
SELECT VendorContactLName +
	', ' + VendorContactFName AS [Contact Name]
FROM Vendors
WHERE VendorContactLName 
	LIKE '[ABCE]%' 
--Learned the WHERE clause with a LIKE operator. 
--Easy once you see it, but it took a few tries on this one!
ORDER BY VendorContactLName	+ VendorContactFName;

--Exercise 7 We want to make sure there are no invalid invoices, no invalid invoices is good from a business standpoint. 
SELECT PaymentDate
	, InvoiceTotal - (PaymentTotal + CreditTotal) AS [Balance]
FROM Invoices
WHERE PaymentDate IS NULL
	AND InvoiceTotal - (PaymentTotal + CreditTotal) = 0
	OR PaymentDate IS NOT NULL
	AND InvoiceTotal - (PaymentTotal + CreditTotal) > 0
ORDER BY [Balance] DESC;