Exercises Write a SELECT statement that returns the same result set as this SELE

Question

Exercises Write a SELECT statement that returns the same result set as this SELECT statement, but don't use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword 1. SELECT DISTINCT vendor_name FROM vendors JOIN invoices ON vendors.vendor idinvoices.vendor id ORDER BY vendor name Write a SELECT statement that answers this question: Which invoices have a payment total that's greater than the average payment total for all invoices with a payment total greater than 0? Return the invoice number and invoice total columns for cach 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. 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- duced with the NOT EXISTS operator. This should return 54 rows. Sort the results by the account_number column. 2. 3. Chapter 7 How to code subqueries Write a SELECT statement that returns four columns: vendor_name, invoice_id, invoice_sequence, and line_item amount. Return a row for each line item of each invoice that has more than one line tem in the Invoice Line_Items table. Hint: Use a subquery that tests for invoice sequence >1. This should return 6 rows Write a SELECT statement that returns two columns: vendor_id and the largest unpaid invoice for each vendor. To do this, you can group the result set by the vendor_id column. This should return 7 rows Write a second SELECT statement that uses the first SELECT statement in its FROM clause. The main query should return a single value that represents the sum of the largest unpaid invoices for each vendor Write a SELECT statement that returns the name, city, and state of each vendor that's located in a unique city and state. In other words, don't include vendors that have a city and state in common with another vendor. This should return 38 rows 5. 6. Sort the results by the vendor_state and vendor_city columns. Use a correlated subquery to return one row per vendor, representing the vendor's oldest invoice (the one with the earliest date). Each row should include these four columns: vendor_name, invoice_number, invoice date, and invoice total. This should return 34 rows. Sort the results by the vendor_name column. Rewrite exercise 7 so it gets the same result but uses an inline view instead of a correlated subquery 7. 8.

Explanation / Answer

1. Following query shows the SELECT statement that returns the same result set as this SELECT statement, Here we use a subquery in a WHERE clause that uses the IN keyword.

SELECT DISTINCT vendor_name FROM vendors WHERE vendor_id IN (SELECT vendor_id FROM invoices) ORDER BY vendor_name
2. Following query shows the SELECT statement that returns two columns invoice_number, invoice_total for each invoices where the invoice_total is greater than the average of invoice_total column values and also greater than 0. Sorting invoice_total in descending order.
SELECT TOP 20 invoice_number, invoice_total FROM invoices WHERE invoice_total > (SELECT AVG(invoice_total) FROM invoices) AND invoice_total > 0 ORDER BY invoice_total DESC
3. Following query shows the SELECT statement that returns two columns account_number and account_description from General_Ledger_Accounts and also check that the account_number not assigned to any item in the Invoice_Line_Items table. Here we use NOT EXISTS operator with subquery.
SELECT TOP 54 account_number,account_description FROM General_Ledger_Accounts WHERE NOT EXISTS (SELECT account_number FROM Invoice_Line_Items) ORDER BY account_number

4. Following query shows the SELECT statement that returns 4 columns vendor_name,invoice_id,invoice_sequence,line_item_amount from Invoice_Line_Items and also check the invoice_sequence value is greater than 1.
SELECT TOP 6 vendor_name,invoice_id,invoice_sequence,line_item_amount from Invoice_Line_Items WHERE invoice_sequence > 1
5. Following query shows the SELECT statement that return 2 columns vendor_id,largest_unpaid_invoice for each vendor. Here we group vendor_id column.
First Statement:
SELECT TOP 7 vendor_id,largest_unpaid_invoice FROM vendors GROUP BY vendor_id
Second statement shows the SELECT statement that uses the first statement from FROM clause. It represents sum of the largest unpaid invoices for each vendor.
Second Statement:
SELECT vendor_id, SUM(largest_unpaid_invoice) FROM (SELECT TOP 7 vendor_id,largest_unpaid_invoice FROM vendors) GROUP BY vendor_id

6. . Following query shows the SELECT statement that returns the name,city,state of each vendors that located in a unique city and state and sort by state and city.
SELECT TOP 38 vendor_name,vendor_city,vendor_state FROM vendors GROUP BY vendor_city,vendor_state ORDER BY vendor_state,vendor_city

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

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