Class-In Assignment 4: Chapters 6&7 Chapter 6: How to code subqueries Exercises
ID: 3795950 • Letter: C
Question
Class-In Assignment 4: Chapters 6&7
Chapter 6: How to code subqueries
Exercises
1. 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.
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 paid invoices? Return the invoice_number and invoice_total for each invoice.
3. Write a SELECT statement that returns two columns from the General_Ledger_Accounts table: account_number and account_description. The result set should have one row for each account number that has never been used. Use a subquery introduced with the NOT EXISTS operator, and sort the final result set by account_number.
4. Write a SELECT statement that returns four columns: vendor_name, invoice_id, invoice_sequence, and line_item_amt for each invoice that has more than one line item in the Invoice_Line_Items table. Hint: Use a subquery that tests for invoice_sequence > 1.
5. Write a SELECT statement that returns a single value that represents the sum of the largest unpaid invoices for each vendor (just one for each vendor). Use an inline view that returns MAX(invoice_total) grouped by vendor_id, filtering for invoices with a balance due.
6. Rewrite exercise 6 so it uses subquery factoring.
7. 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.
8. 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.
9. Rewrite exercise 8 so it gets the same result but doesn’t use a correlated subquery.
Chapter 7
How to insert, update, and delete data
Exercises
To test whether a table has been modified correctly as you do these exercises, you can write and run an appropriate SELECT statement. Or, when you’re using Oracle SQL Developer, you can click on a table name in the Connections window and then on the Data tab to display the data for all of the columns in the table. To refresh the data on this tab, click the Refresh button.
1. Write an INSERT statement that adds this row to the Invoices table:
invoice_id The next id in sequence (find out what this should be)
vendor_id: 32
invoice_number: AX-014-027
invoice_date: 8/1/2008
invoice_total: $434.58
payment_total: $0.00
credit_total: $0.00
terms_id: 2
invoice_due_date: 8/31/2008
payment_date: null
2. Write an UPDATE statement that modifies the Vendors table. Change the default account number to 403 for each vendor that has a default account number of 400.
3. Write an UPDATE statement that modifies the Invoices table. Change the terms_id to 2 for each invoice that’s for a vendor with a default_terms_id of 2.
Explanation / Answer
1.
Select distinct vendor_name from vendors Where vendor_id in (select vendor_id from Invoices) Order By vendor_name
2.
Select payment_total , invoice_number , invoice_total from invoices where payment_total > ( select AVG ( payment_total ) TOP 50 PERCENT payment_total from Invoices )
3.
4
Select AccountNo, AccountDescription
From GLAccounts GLA
where NOT EXISTS
(select 1 from InvoiceLineItems I
where I.AccountNo=GLA.AccountNo)
order by AccountNo
5
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)