QUESTION 1 Use the AP database. Write a query that returns the VendorName and Ve

Question

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)

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)

Explanation / Answer

Answer : 1

SELECT VendorName

,IIF(VendorState <> 'OH', 'Outside OH','OH') AS 'Vendor State'

FROM Vendors

Explanation : The above query will give the Vendor Name, Vendor state. It is using IIF function which has the syntax as IIF (Boolean_Expression, True_Value, False_Value). Thus if VendorState is OH then it will give OH else it will give outside OH.

Answer : 2

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

Explanation : The subquery will give 3 most recent orders as it is sorted in the descending Order Date sequence. Now we are grouping the result set on the basis of customer ID first which means it will be groupped customer wise. It is the desired output.

Answer : 3

WITH SUM_CTE as

(SELECT VendorID, MAX(InvoiceTotal) AS InvoiceMax

FROM Invoices

WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0

GROUP BY VendorID)

select * from SUM_CTE;

Explanation : As we know the CTE is a temporary table output. We have rewritten the below query to make use of the CTE (common table expression).  

SELECT SUM(InvoiceMax) AS SumMax

FROM (SELECT VendorID, MAX(InvoiceTotal) AS InvoiceMax

FROM Invoices

WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0

GROUP BY VendorID) AS MaxInvoice

Answer : 5

select OrderId, Quantity

from [Order Details] OD

where Quantity <

(select avg(Quantity * .1)

from [Order Details]

where OD.ProductID = ProductID )

Explanation : The orders having quantity less than 10% of the average quantity will be shown. The avg function is used in sub query to produce the average of Quantity. The subquery result set should be matching on the table's ProductID

Answer : 6

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;

Explanation : The above query will produce the desired four columns as required. The where clause is put so that the result set will have one row for each invoice with a non-zero balance also the result set is sorted by Vendor Name(default is ascending).

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.