murach's SQL SERVER 2016: Ch4 Class Notes

ch4_classNotes.sql

--10/10/17

--All invoices for vendors in CA
SELECT Invoices.InvoiceNumber --Invoices.InvoiceNumber isn't necessary to put, InvoiceNumber is fine. Less syntax is better, though.
	, Invoices.VendorID --Asking which VendorID column are you referencing? Vendors? Or Invoices?
	, Vendors.VendorName
FROM AP.dbo.Invoices INNER --INNER Not necessary, but always use the keyword for now until we get used to it.
	JOIN AP.dbo.Vendors ON	Invoices.VendorID = Vendors.VendorID --using AP.dbo. isn't necessary, but will select DB automatically if still on Master DB
WHERE VendorState = 'CA';

--Look up avoid codesmells, too short or too long alias can be detrimental
SELECT InvoiceNumber
	, VendorName
FROM Invoices AS I INNER --From table, join table, on condition
	JOIN Vendors AS V ON I.VendorID = V.VendorID --Have to use alias after renaming
ORDER BY VendorName DESC;

SELECT InvoiceNumber
	, InvoiceLineItemDescription
FROM InvoiceLineItems AS LineItems INNER
	JOIN Invoices ON Invoices.InvoiceID = LineItems.InvoiceID --order doesn't matter when joining

--All vendors with invoices in CA with balance due
--Vendors/Invoices
SELECT *
FROM Invoices INNER
	JOIN Vendors ON Invoices.VendorID = Vendors.VendorID
	AND VendorState = 'CA'
	AND InvoiceTotal - PaymentTotal - CreditTotal > 0

--All vendors with invoices in CA with balance due
--Vendors/Invoices Condensed FROM with WHERE clause
SELECT *
FROM Invoices INNER
	JOIN Vendors ON Invoices.VendorID = Vendors.VendorID
WHERE VendorState = 'CA'
	AND InvoiceTotal - PaymentTotal - CreditTotal > 0

--EXPLICIT JOIN SYNTAX (Preferred)
SELECT *
FROM Vendors INNER
	JOIN Invoices ON Vendors.VendorID = Invoices.VendorID

--IMPLICIT JOIN SYNTAX (Older, not preferred)
SELECT *
FROM Vendors, Invoices --Cross Join, doubles up info, way wrong (Cancel Query Button by Execute Button if too many)
WHERE Vendors.VendorID = Invoices.VendorID

--All vendors in the same city as another vendor (SELF-JOIN)
--For self-joins make sure you don't compare a row to itself ( = )
SELECT DISTINCT Vendors1.VendorID --DISTINCT filters our rows that are duplicate, no matter the column pulled
	, Vendors1.VendorName --Have to use Vendors1. call on SELF JOIN
	, Vendors1.VendorCity
	, Vendors1.VendorState
FROM Vendors AS Vendors1 INNER --Have to Use an ALIAS to self-join on at least one table
	JOIN Vendors AS Vendors2 ON Vendors1.VendorID <> Vendors2.VendorID -- making sure the vendors are different
	AND Vendors1.VendorCity = Vendors2.VendorCity --making sure the vendors are in the same city
	AND Vendors1.VendorState = Vendors2.VendorState --making sure vendors are in the same state
ORDER BY VendorCity --Do not have to table qualify (Vendors.1) because there is only on VendorCity in SELECT
	, VendorName

--10/12/17

--Multi-Join & Outer-Join
SELECT *
FROM Vendors INNER --Doesn't matter which table you start with in a join.
	JOIN Invoices ON Vendors.VendorID = Invoices.VendorID INNER
	JOIN InvoiceLineItems ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID INNER --Do NOT need to repeat tables i.e. Invoices & InvoiceLineitems are now declared
	JOIN GLAccounts ON GLAccounts.AccountNo = InvoiceLineItems.AccountNo

--To make a A LEFT or RIGHT OUTER JOIN all you really have to do is swap Tables in the FROM clause. Most developers prefer the LEFT JOIN, avoid RIGHT JOIN
--All vendors in CA and their corresponding invoices
--Invoice number
--whether or not they have any
--Tables: Vendors, Invoices
SELECT VendorName
	, InvoiceNumber
FROM Vendors LEFT OUTER --Just like the INNER option OUTER is optional, LEFT, RIGHT define OUTER it is INNER w/o directional
	JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
WHERE VendorState = 'CA'
ORDER BY VendorName

--*************************************
--Switch DATABASES to Examples Database

--All departments, whether or not they have employees
SELECT *
FROM Departments LEFT JOIN Employees --LEFT is always the first table written, RIGHT would be the second.
	ON Departments.DeptNo = Employees.DeptNo

--All departments with employees
--All employees in a dept
SELECT *
FROM Departments JOIN Employees --Minus the LEFT JOIN, which is only where these two tables overlap
	ON Departments.DeptNo = Employees.DeptNo

--All employees dept or not
SELECT *
FROM Employees LEFT JOIN Departments
	ON Departments.DeptNo = Employees.DeptNo

--All the employees that don't have a Dept
SELECT FirstName
	, LastName
FROM Employees LEFT JOIN Departments
	ON Departments.DeptNo = Employees.DeptNo
WHERE Departments.DeptNo IS NULL

--All employees AND all departments
SELECT *
FROM Departments FULL JOIN Employees --Starting with FULL joins is a good starting point to find out wich join you need
	ON Departments.DeptNo = Employees.DeptNo

--All employees that don't have a department using a FULL join
SELECT *
FROM Departments FULL JOIN Employees
	ON Departments.DeptNo = Employees.DeptNo
WHERE Departments.DeptNo IS NULL

--**************************
--SWITCH BACK TO AP DATABASE

--All vendors that do not have invoices
--The result set should include:
--VendorName
SELECT VendorName
FROM Vendors LEFT JOIN Invoices
	ON Vendors.VendorID = Invoices.VendorID
--WHERE InvoiceID IS NULL
--WHERE Inoices.VendorID IS NULL
WHERE InvoiceNumber IS NULL

--All invoices and whether they are paid or unpaid with a Union
	SELECT InvoiceNumber
		, 'Paid' AS [Status]
		, VendorName
	--	, InvoiceTotal - PaymentTotal - CreditTotal AS [Balance]
	FROM Invoices JOIN Vendors
		ON Vendors.VendorID = Invoices.VendorID
	WHERE InvoiceTotal - PaymentTotal - CreditTotal = 0
UNION --Order Matters with UNION'ing
	SELECT InvoiceNumber
		, 'Unpaid' AS [Status]
		, VendorName
	--	, InvoiceTotal - PaymentTotal - CreditTotal AS [Balance]
	FROM Invoices JOIN Vendors
		ON Vendors.VendorID = Invoices.VendorID
	WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
ORDER BY InvoiceNumber DESC

--Union Letter by first name example in CH 3 letter by letter, a lot of syntax, though