1. Use the AP database. Write a query that returns the VendorName and VendorState from those vendors from the state of Ohio plus show the VendorName along with the words 'Outside OH' for those vendors outside of OH.

		SELECT DISTINCT Vendors1.VendorName , Vendors2.VendorName + ' Outside Ohio' , Vendors1.VendorState FROM Vendors AS Vendors1 JOIN Vendors AS Vendors2 ON (Vendors1.VendorState = Vendors2.VendorState) AND (Vendors1.VendorName = Vendors2.VendorName) AND (Vendors1.VendorState = 'OH') OR (Vendors2.VendorState <> 'OH') ORDER BY VendorState, VendorName
		SELECT VendorName, CASE WHEN VendorState = 'OH' THEN VendorState ELSE 'Outside OH' END As Location FROM Vendors
		SELECT VendorName, VendorState FROM Vendors WHERE VendorState = 'OH' UNION SELECT VendorName, 'Outside OH' FROM Vendors WHERE VendorState <> 'OH'
		SELECT VendorName, VendorState FROM Vendors WHERE s.VendorNumber = v.VendorName AND VendorState = 'Outside OH' Order By VendorState
		SELECT VendorName ,IIF(VendorState <> 'OH', 'Outside OH','OH') AS 'Vendor State' FROM Vendors
		SELECT Vendors.VendorName 'VendorName Outside OH' , Vendors.VendorState FROM Vendors WHERE Vendors.VendorState = 'OH'

Question 2

1. Use the Northwind database. Write a query that returns the three most recent orders for each client.

		SELECT CustomerID, OrderDate, OrderID FROM Orders WHERE OrderID in (SELECT TOP 3 Max(OrderDate) From Orders AS iQ WHERE iQ.CustomerID = Orders.CustomerID)
		SELECT CustomerID, OrderDate, OrderID FROM Orders WHERE OrderDate in (SELECT TOP 3 OrderDate From Orders AS iQ WHERE iQ.CustomerID = Orders.CustomerID GROUP BY OrderDate)
		SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID FROM Orders WHERE Orders.OrderID IN (SELECT TOP 3 OrderID FROM Orders AS TopOrders WHERE TopOrders.CustomerID = Orders.CustomerID ORDER BY TopOrders.OrderDate DESC, TopOrders.OrderID DESC ) ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID
		SELECT CustomerID, OrderDate, OrderID FROM Orders WHERE OrderDate in (SELECT TOP 3 Max(OrderDate) From Orders AS iQ WHERE iQ.CustomerID = Orders.CustomerID)
		SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID FROM Orders WHERE OrderDate In (SELECT TOP 3 OrderID From Orders AS iQ WHERE iQ.CustomerID = Orders.CustomerID Order By iQ.OrderDate Desc, iQ.OrderID Desc ) ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID
		SELECT CustomerID, OrderDate, OrderID FROM ( SELECT CustomerID, OrderDate, OrderID, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RecID FROM Orders ) AS d WHERE RecID BETWEEN 1 AND 3 ORDER BY CustomerID, OrderDate, OrderID
		SELECT Customers.CustomerID, OrderDate, OrderID FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE OrderDate in (SELECT TOP 3 Max(OrderDate) From Orders AS iQ WHERE iQ.CustomerID = Orders.CustomerID GROUP BY OrderDate )

Question 3

1. Use the AP database. Write a SELECT statement that returns a single value that represents the sum of the largest unpaid invoices submitted by each vendor. Use a CTE or derived table that returns Max(InvoiceTotal) grouped by VendorID, filtering for invoices with a balance due. Balance due means where InvoiceTotal - CreditTotal - PaymentTotal > 0 as per the book.

		With Balances As ( Select Vendors.VendorName, Vendors.VendorID, Invoices.InvoiceNumber, Invoices.InvoiceTotal, Invoices.PaymentTotal, (InvoiceTotal - PaymentTotal) as BalanceDue From Vendors Join Invoices On Vendors.VendorID = Invoices.VendorID Where (InvoiceTotal - PaymentTotal) > 0 ) , TopBalance As ( Select VendorName, Max(BalanceDue) as BalanceDue From Balances Group By VendorName ) Select Balances.VendorName, Balances.InvoiceTotal, TopBalance.BalanceDue From Balances Join TopBalance On Balances.VendorName = TopBalance.VendorName And Balances.BalanceDue = TopBalance.BalanceDue;
		WITH SUM_CTE as (SELECT VendorID, MAX(InvoiceTotal) AS InvoiceMax FROM Invoices WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0 GROUP BY VendorID) select * from SUM_CTE;
		SELECT Invoices.VendorID, Vendors.VendorName, MAX(MaxInvoice) AS MaxInvoice FROM Invoices INNER JOIN Vendors ON Invoices.VendorID = Vendors.VendorID INNER JOIN (SELECT TOP 10 VendorID, SUM(InvoiceTotal - (PaymentTotal + CreditTotal)) AS MaxInvoice FROM Invoices WHERE InvoiceTotal > (PaymentTotal + CreditTotal) GROUP BY VendorID ORDER BY MAXInvoice DESC) AS BalanceDue ON Invoices.VendorID = BalanceDue.VendorID GROUP BY Invoices.VendorID, Vendors.VendorName ORDER BY MAXInvoice DESC
		WITH MaxInvoice AS ( SELECT VendorID, MAX(InvoiceTotal) AS InvoiceMax FROM Invoices WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0 GROUP BY VendorID ) SELECT SUM(InvoiceMax) AS SumOfMaximums FROM MaxInvoice;

Question 4

1. Use the AP database. Write a SELECT that returns two columns from the GLAccounts table: AccountNo AccountDescription The result set should have one row for each account number that has never been used. Sort the final result set by AccountNo.

		SELECT GLAccounts.AccountNo , AccountDescription FROM GLAccounts LEFT JOIN InvoiceLineItems ON GLAccounts.AccountNo = InvoiceLineItems.AccountNo WHERE InvoiceLineItems.AccountNo IS NULL ORDER BY GLAccounts.AccountNo;
		SELECT DISTINCT GLAccounts.AccountNo, GLAccounts.AccountDescription FROM GLAccounts LEFT JOIN InvoiceLineItems ON GLAccounts.AccountNo <> InvoiceLineItems.AccountNo ORDER BY GLAccounts.AccountNo
		SELECT GLAccounts.AccountNo, AccountDescription FROM GLAccounts LEFT OUTER JOIN InvoiceLineItems ON (InvoiceLineItems.AccountNo = NULL)
		SELECT GLAccounts.AccountNo , AccountDescription From GLAccounts INNER JOIN InvoiceLineItems ON GLAccounts.AccountNo = InvoiceLineItems.AccountNo WHERE InvoiceLineItems.AccountNo IS NULL Order By AccountNo
		SELECT GLAccounts.AccountNo , AccountDescription FROM GLAccounts RIGHT JOIN InvoiceLineItems ON GLAccounts.AccountNo = InvoiceLineItems.AccountNo WHERE InvoiceLineItems.AccountNo IS NULL ORDER BY GLAccounts.AccountNo;
		SELECT GLAccounts.AccountNo , AccountDescription From GLAccounts CROSS JOIN InvoiceLineItems ON GLAccounts.AccountNo = InvoiceLineItems.AccountNo WHERE InvoiceLineItems.AccountNo IS NULL Order By AccountNo

Question 5

1. Use the Northwind database. Display those distinct orders where the quantity of each item on an order is less than 10% of the average of quantity of items bought.

Adding in the Quantity in the Select clause, one will see orders where the line-item quantity is less than 2.3 items.

		Select OrderId, Quantity From [Order Details] OD Where Quantity < (Select Avg(Quantity) * .1 from [Order Details] where OD.ProductID = ProductID )
		select OrderId, Quantity from [Order Details] OD where Quantity < (select avg(Quantity * .1) from [Order Details] where OD.ProductID = ProductID )
		select OrderId, Quantity from [Order Details] OD where Quantity < (select avg(Quantity) * .1 from [Order Details] )
		Select OrderId, Quantity From [Order Details] OD Where Quantity > (Select Avg(Quantity) * .1 from [Order Details] where OD.ProductID = ProductID )

Question 6

1. Using the AP database.

Write a SELECT statement that returns four columns:

VendorName

InvoiceNumber

InvoiceDate

Balance: InvoiceTotal (PaymentTotal + CreditTotal) or InvoiceTotal PaymentTotal CreditTotal

The result set should have one row for each invoice with a non-zero balance. Sort the result set by the VendorName in ascending order.

		SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal - (PaymentTotal + CreditTotal) AS Balance FROM Vendors Join Invoices ON (InvoiceTotal - (PaymentTotal + CreditTotal) > 0)
		SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal - PaymentTotal - CreditTotal AS Balance FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY VendorName;
		SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal - (PaymentTotal + CreditTotal) AS Balance FROM Vendors Cross Join Invoices WHERE (InvoiceTotal - (PaymentTotal + CreditTotal) > 0)
		SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal - PaymentTotal - CreditTotal AS Balance FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE Balance > 0 ORDER BY VendorName;

Question 7

1. Using the AP database.

Write a SELECT statement that returns three columns:

VendorName

DefaultAccountNo

AccountDescription

The result set should have one row for each vendor, with the account number and account description for that vendors default account number. Sort the result set by AccountDescription, then by VendorName.

		SELECT distinct v.VendorName, v.DefaultAccountNo, g.AccountDescription FROM Vendors v, GLAccounts g where v.accountnumber = g.accountnumber ORDER By AccountDescription, VendorName
		SELECT VendorName, DefaultAccountNo, AccountDescription FROM Vendors JOIN GLAccounts ON Vendors.DefaultAccountNo = GLAccounts.AccountNo ORDER BY AccountDescription, VendorName;
		SELECT VendorName, DefaultAccountNo, AccountDescription FROM Vendors LEFT JOIN GLAccounts ORDER BY AccountDescription, VendorName;
		SELECT DISTINCT Vendors.VendorName, Vendors.DefaultAccountNo, GLAccounts.AccountDescription FROM Vendors JOIN GLAccounts ON DefaultAccountNo = AccountNo ORDER BY VendorName, AccountDescription;

Question 8

1. Using the AP database.

Write a SELECT statement that returns three columns:

VendorID

VendorName

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.

		SELECT v1.VendorID, v1.VendorName,(v1.VendorContactFName+' '+v1.VendorContactLName) as Name FROM VENDORS v1, VENDORS v2 WHERE v1.VendorContactFName = v2.VendorContactFName
		SELECT v1.VendorID , v1.VendorName , v1.VendorContactFName + ' ' + v1.VendorContactLName AS Name FROM Vendors AS v1 JOIN Vendors AS v2 ON (v1.VendorID <> v2.VendorID) AND (v1.VendorContactFName = v2.VendorContactFName) ORDER BY Name;
		SELECT DISTINCT Vendors1.VendorID, Vendors1.VendorName, Vendors1.VendorContactFName + ' ' + Vendors1.VendorContactLNameAS 'Name' FROM Vendors AS Vendors1 JOIN Vendors AS Vendors2 ON Vendors1.VendorContactLName = Vendors2.VendorContactLName ORDER BY Name
		SELECT Vendors.VendorID, Vendors.VendorName, Vendors_1.VendorContactFName + ' ' + Vendors_1.VendorContactLName AS Name FROM Vendors INNER JOIN Vendors AS Vendors_1 ON Vendors.VendorID = Vendors_1.VendorID ORDER BY Name

Question 9

1. Using the AP database. Which of the following queries does NOT correctly answer this query statement: Show all the Vendor information for only those vendors who appear on invoices.

		SELECT Distinct Vendors.* FROM Vendors WHERE VendorID IN (SELECT DISTINCT VendorID FROM Invoices)
		SELECT DISTINCT Vendors.* FROM Invoices LEFT JOIN Vendors ON Vendors.VendorID=Invoices.VendorID;
		SELECT DISTINCT Vendors.* FROM Invoices RIGHT JOIN Vendors ON Vendors.VendorID=Invoices.VendorID;
		SELECT Distinct Vendors.* FROM Vendors JOIN Invoices ON Vendors.VendorID=Invoices.VendorID;

Question 10

1. Using the AP database. Write a SELECT statement that answers this question: Which vendors are being paid from more than one account? Return the vendor name and the total number of accounts that apply to the vendor's invoices.

		SELECT Vendors.VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID JOIN (SELECT VendorID FROM Invoices GROUP BY VendorID HAVING COUNT(VendorID) = 1 ) V ON Vendors.VendorID = V.VendorID ORDER BY VendorName;
		SELECT Vendors.VendorName, COUNT(DISTINCT InvoiceLineItems.AccountNo) AS TotalNumberOfAccounts FROM Vendors JOIN GLAccounts ON GLAccounts.AccountNo=Vendors.DefaultAccountNo JOIN InvoiceLineItems ON GLAccounts.AccountNo=InvoiceLineItems.AccountNo GROUP BY InvoiceLineItems.AccountNo, Vendors.VendorName ORDER BY Vendors.VendorName;
		SELECT VendorName, COUNT(DISTINCT InvoiceLineItems.AccountNo) AS [# of Accounts] FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID JOIN InvoiceLineItems ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID GROUP BY VendorName HAVING COUNT(DISTINCT InvoiceLineItems.AccountNo) > 1 ORDER BY VendorName;
		SELECT DISTINCT VendorName, COUNT (*) AS NumberofAccounts FROM Vendors JOIN InvoiceLineItems ON Vendors.DefaultAccountNo = InvoiceLineItems.AccountNo GROUP BY VendorName HAVING COUNT (*) > 1

Question 11

1. Using the AP database. Write a SELECT statement that returns VendorID and PaymentSum from the Invoices table. Set it so that the PaymentSum is the sum of the PaymentTotal column. Group the results by VendorID.

		SELECT DISTINCT VendorName FROM Vendors WHERE Vendors.VendorID IN (SELECT VendorID FROM Invoices) ORDER BY VendorName;
		SELECT VendorID, SUM(PaymentTotal) AS PaymentSum FROM Invoices GROUP BY VendorID;
		Select VendorID, PaymentSum from Invoice Where PaymentSum = sum(paymentTotal) Group by VendorID
		SELECT VendorID, SUM(PaymentTotal) FROM Invoices ORDER BY VendorID;

Question 12

1. Using the AP database. Write a SELECT statement that returns the following columns: VendorID InvoiceDate InvoiceTotal VendorTotal -- the sum of the invoice totals for each vendor VendorCount -- the count of invoices for each vendor VendorAvg -- the average of the invoice totals for each vendor The result set should include the individual invoices for each vendor.

		SELECT VendorID, InvoiceDate, InvoiceTotal, SUM(InvoiceTotal) OVER (PARTITION BY VendorID) AS VendorTotal, COUNT(InvoiceTotal) OVER (PARTITION BY VendorID) AS VendorCount, AVG(InvoiceTotal) OVER (PARTITION BY VendorID) AS VendorAvg FROM Invoices GROUP BY VendorID, InvoiceDate, InvoiceTotal;
		SELECT DISTINCT VendorID, InvoiceDate, InvoiceTotal, Count(*) AS VendorCount, SUM(InvoiceTotal) AS VendorTotal, AVG(InvoiceTotal) AS VendorAvg FROM Invoices, Vendors GROUP BY Invoices.VendorID, InvoiceDate, InvoiceTotal ORDER BY VendorCount
		SELECT Invoices.VendorID, InvoiceDate,InvoiceTotal, SUM(InvoiceTotal) AS VendorTotal, COUNT(InvoiceID) AS VendorCount, AVG(InvoiceTotal) As VendorAvg FROM Vendors JOIN Invoices ON Vendors.VendorID=Invoices.VendorID GROUP BY Invoices.VendorID,Invoices.InvoiceDate,InvoiceTotal with ROLLUP
		SELECT Vendors.VendorID, Invoices.InvoiceDate, Invoices.InvoiceTotal, SUM(Invoices.InvoiceTotal) OVER (PARTITION BY Invoices.InvoiceID) AS VendorTotal, COUNT(*) OVER (PARTITION BY Invoices.InvoiceID) AS VendorCount, AVG(Invoices.InvoiceTotal) OVER (PARTITION BY Invoices.InvoiceID) AS VendorAvg FROM Vendors JOIN Invoices ON Vendors.VendorID=Invoices.VendorID LEFT JOIN InvoiceLineItems ON Invoices.InvoiceID=InvoiceLineItems.InvoiceID GROUP BY Vendors.VendorID, Invoices.InvoiceDate, Invoices.InvoiceTotal, Invoices.InvoiceID WITH ROLLUP ORDER BY Invoices.InvoiceDate DESC, Vendors.VendorID; Need help ASAP please! Fast thumbs up!