murach's SQL SERVER 2016: Join Queries

joinQueries.sql

--Chapter 4 Join Queries
--10/10/17

/*
	Exercise 1 (COMPLETE)
	Write a SELECT statement that returns all columns from the Vendors table 
	inner-joined with the Invoices table
*/
SELECT *
FROM Vendors INNER
	JOIN Invoices ON Vendors.VendorID = Invoices.VendorID

/*
	Exercise 2 (COMPLETE)
	Write a SELECT statement that returns four columns:
	VendorName From the Vendors table
	InvoiceNumber From the Invoices table
	InvoiceDate From the Invoices table
	Balance InvoiceTotal minus the sum of PaymentTotal and CreditTotal
	The result set should have one row for each invoice with a non-zero 
	balance. Sort the result set by VendorName in ascending order.
*/
SELECT VendorName
	, InvoiceNumber
	, InvoiceDate
	, InvoiceTotal - (PaymentTotal + CreditTotal) AS Balance
FROM Vendors INNER
	JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceTotal - (PaymentTotal + CreditTotal) > 0
ORDER BY VendorName

/*
	Exercise 3 (COMPLETE)
	Write a SELECT statement that returns three columns:
	VendorName From the Vendors table
	DefaultAccountNo From the Vendors table
	AccountDescription From the GLAccounts table
	The result set should have one row for each vendor, 
	with the account number and account description for that 
	vendor's default account number. Sort the result set by 
	AccountDescription, then by VendorName.
*/
SELECT VendorName
	, DefaultAccountNo
	, AccountDescription
FROM Vendors INNER
	JOIN GLAccounts ON Vendors.DefaultAccountNo = GLAccounts.AccountNo
ORDER BY AccountDescription
	, VendorName

/*
	Exercise 4 (COMPLETE)
	Generate the same result set described in exercise 2, 
	but with the implicit join syntax.
*/
SELECT VendorName
	, InvoiceNumber
	, InvoiceDate
	, InvoiceTotal - (PaymentTotal + CreditTotal) AS Balance
FROM Vendors, Invoices
WHERE Vendors.VendorID = Invoices.VendorID
	AND InvoiceTotal - (PaymentTotal + CreditTotal) > 0
ORDER BY VendorName

/*
	Exercise 5 (COMPLETE)
	Write a SELECT statement that returns five columns from three tables, 
	all using column aliases:
	Vendor - VendorName column
	Date - InvoiceDate column
	Number - InvoiceNumber column
	# - InvoiceSequence column
	LineItem - InvoiceLineItemAmount column
	Assign the following correlation names to the tables:
	v Vendors table
	i Invoices table
	li InvoicesLineItems table
	Sort the final result set by Vendor, Date, Number, and #.
*/
SELECT VendorName AS [Vendor]
	, InvoiceDate AS [Date]
	, InvoiceNumber AS [Number]
	, InvoiceSequence AS [#]
	, InvoiceLineItemAmount AS [LineItem]
FROM Vendors AS v INNER
	JOIN Invoices AS i ON v.VendorID = i.VendorID INNER
	JOIN InvoiceLineItems AS li ON i.InvoiceID = li.InvoiceID
ORDER BY [Vendor]
	, [Date]
	, [Number]
	, [#]

/*
	Exercise 6 (COMPLETE)
	Write a SELECT statement that returns three columns:
	VendorID From the Vendors table
	VendorName From the Vendors table
	Name - A concatenation of VendorContactFName and VendorContactLName, 
	with a space in between
	The result set should have one row for each vendor whose contact has 
	the same first name as another vendors contact. Sort the final result set by Name.
	Hint: Use a self-join
*/
SELECT Vendors1.VendorID
	, Vendors1.VendorName
	, Vendors1.VendorContactFName + ' ' + Vendors1.VendorContactLName AS [Name]
FROM Vendors AS Vendors1 INNER
	JOIN Vendors AS Vendors2 ON Vendors1.VendorID <> Vendors2.VendorID
	AND Vendors1.VendorContactFName = Vendors2.VendorContactFName
ORDER BY [Name]

/*
	Exercise 7 (COMPLETE)
	Write a SELECT statement that returns two columns from the GLAccounts table: 
	AccountNo and AccountDescription. The result set should have one row for each 
	account number that has never been used. Sort the final result set by AccountNo.
	Hint: Use an outer join to the InvoiceLineItems table.
*/
SELECT GLAccounts.AccountNo
	, GLAccounts.AccountDescription
FROM GLAccounts LEFT JOIN InvoiceLineItems 
	ON GLAccounts.AccountNo = InvoiceLineItems.AccountNo
WHERE InvoiceLineItems.AccountNo IS NULL
ORDER BY GLAccounts.AccountNo

/*
	Exercise 8 (COMPLETE)
	Use the UNION operator to generate a result set consisting of two columns 
	from the Vendors table: VendorName and VendorState. If the vendor is in 
	California, the VendorState value should be "CA" otherwise, the VendorState 
	value should be "Outside CA". Sort the final result set by VendorName
*/
	SELECT VendorName
		, 'CA' AS [Location]
	FROM Vendors
	WHERE VendorState = 'CA'
UNION
	SELECT VendorName
		, 'Outside CA' AS [Location]
	FROM Vendors
	WHERE VendorState <> 'CA'
ORDER BY VendorName