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

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.

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 vendor’s 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 vendor’s 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!

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'

Explanation / Answer

Question 1:-

SELECT VendorName, VendorState
FROM Vendors
WHERE VendorState = 'OH'
UNION
SELECT VendorName, 'Outside OH'
FROM Vendors
WHERE VendorState <> 'OH';

Question 2:-

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)

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

Question 4

SELECT GLAccounts.AccountNo
    , AccountDescription
FROM GLAccounts LEFT JOIN InvoiceLineItems ON GLAccounts.AccountNo =
InvoiceLineItems.AccountNo
WHERE InvoiceLineItems.AccountNo IS NULL
ORDER BY GLAccounts.AccountNo;

Question 5:-

select OrderId, Quantity
from [Order Details] OD
where Quantity <
    (select avg(Quantity) * .1
    from [Order Details]);

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

Question 7:-

SELECT VendorName, DefaultAccountNo, AccountDescription

FROM Vendors JOIN GLAccounts

ON Vendors.DefaultAccountNo = GLAccounts.AccountNo

ORDER BY AccountDescription, VendorName;

Question 8:-

SELECT v1.VendorID, v1.VendorName,(v1.VendorContactFName+' '+v1.VendorContactLName) as Name

FROM VENDORS v1, VENDORS v2

WHERE v1.VendorContactFName = v2.VendorContactFName;

Question 9:-

SELECT DISTINCT Vendors.*
FROM Invoices RIGHT JOIN Vendors
ON Vendors.VendorID=Invoices.VendorID;

Question 10:-

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;

Question 11:-

SELECT VendorID, SUM(PaymentTotal) AS PaymentSum

FROM Invoices

GROUP BY VendorID;

Question 12:-

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;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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