MYSQL Exercises Write a SELECT statement that returns the same result set as thi

Question

MYSQL

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. I. SELECT DISTINCT vendor name FROM vendors JOIN invoices ON vendors.vendor id invoices.vendor id ORDER BY vendor name 2. 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 each invoice. 1 should return 20 rows. Sort the results by the invoice_total column in de Write a SELECT statement that returns two columns from the 3. 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.

Explanation / Answer

Hi,

Ans 1- Select distinct vendor_name from vendors where vendor_id in(select vendor_id from invoices) order by vendor_name;

Ans 2- Select invoice_number, invoice_total from invoices I where payment>(select avg(payment) from invoices I1 where I.invoice_id=I1.invoice_id);

Ans 3- select account_number,account_description from general_ledger_accounts gla where account_number not exists(select account_number from Invoice_Line_Items) order by account_number;

Ans 4- select invoice_id,invoice_sequence,line_item_amount,vendor_name from Invoice_Line_Items where invoice_sequence in(select invoice_sequence from Invoice_Line_Items where invoice_sequence>1 );

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

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