QUESTION 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. Choose all that apply.

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 Vendors.VendorName 'VendorName Outside OH' , Vendors.VendorState FROM Vendors WHERE Vendors.VendorState = '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 ,IIF(VendorState <> 'OH', 'Outside OH','OH') AS 'Vendor State' FROM Vendors

QUESTION 2

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

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 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 )

SELECT CustomerID, OrderDate, OrderID FROM Orders WHERE OrderDate in (SELECT TOP 3 Max(OrderDate) From Orders AS iQ WHERE iQ.CustomerID = Orders.CustomerID)

SELECT CustomerID, OrderDate, OrderID FROM Orders WHERE OrderID 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

QUESTION 3

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 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;

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 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;

QUESTION 5

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

Using the AP database.

Write a SELECT statement that returns four columns:

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 Vendors.VendorID = Invoices.VendorID WHERE Balance > 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 InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY VendorName;
		SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal - (PaymentTotal + CreditTotal) AS Balance FROM Vendors Join Invoices ON (InvoiceTotal - (PaymentTotal + CreditTotal) > 0)