nested subquel correlated subquery uncorrelated subquery inline view comment pse

Question

nested subquel correlated subquery uncorrelated subquery inline view comment pseudocode Exercises I. Write a SELECT statement that returns the same result set as this SELEC statement. but don 't use a join. Instead, use a subquery in a WHERE ca se that uses the IN keyword. SELECT DISTINCT vendor_name FROM vendors JOIN invoices ON vendors. vendor_id invoices. vendor-id ORDER BY vendor name Write a SELECT statement that answers this question: Which invoic a payment total that's greater than the average payment total for all invoices with a payment total greater than 0? 2. es have Return the invoice_number and invoice_total columns for each invoice. This should return 20 rows. Sort the results by the invoice_total column in descending order. Write a SELECT statement that returns two columns from the General Ledger_Accounts table: account_number and account_description. 3. duced with the NOT EXISTS operator. This should return 54 rows. Sort the results by the account_ number column. Return one row for each account number that has never been assigned to any line item in the Invoice_Line_Items table. To do that, use a subquery intro-

Explanation / Answer

1.

SELECT DISTINCT vendor_name FROM VENDORS WHERE vendor_id IN(SELECT vendor_id from invoices) order by vendor_name

2.

SELECT invoice_number, invoice_total FROM Invoice WHERE PaymentTotal > (SELECT AVG(PaymentTotal) FROM Invoice WHERE PaymentTotal > 0) order by invoice_total desc

3.

SELECT account_number, account_description from General_Ledger_Account where NOT EXISTS(Select * from Invoice_Line_Items where account_number Is Not Null) order by account_number

4.

SELECT VendorName, L.InvoiceID, InvoiceSequence, InvoiceLineItemAmount FROM InvoiceLineItems L JOIN Invoices I ON L.InvoiceID = I.InvoiceID JOIN Vendors V ON I.VendorID = V.VendorID WHERE L.InvoiceID IN (SELECT InvoiceID FROM InvoiceLineItems WHERE InvoiceSequence > 1)

5.

(a) SELECT vendor_id, MAX(InvoiceTotal) AS InvoiceMax FROM Invoices WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0 GROUP BY VendorID

(b)SELECT SUM(InvoiceMax) AS SumOfMaximums FROM (SELECT VendorID, MAX(InvoiceTotal) AS InvoiceMax FROM Invoices WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0 GROUP BY VendorID) AS MaxInvoice

6.

Select * FROM Venders WHERE VendorCity + VendorState NOT IN (SELECT VendorCity + VendorState FROM Vendors GROUP BY VendorCity, VendorState HAVING COUNT(*) > 1 ) ORDER BY VendorState, VendorCity

7.

SELECT vendor_name, invoice_number, invoice_date, invoice_total FROM Vendors V JOIN Invoices I ON V.vendor_id=I.vendor_id WHERE invoice_date <= ( SELECT Min(invoice_date) FROM Invoices JOIN Vendors ON V.vendor_id=Vendors.vendor_id ) GROUP BY vendor_name, invoice_number, invoice_date, invoice_total

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

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