1. Write a SELECT statement that returns the same result set as this SELECT statement, but...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 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. 3. 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. Chapter 7 How to code subqueries 4. 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 item in the Invoice_Line_Items table. Hint: Use a subquery that tests for invoice_sequence > 1. This should return 6 rows. 5. 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. 6. 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. Sort the results by the vendor state and vendor city columns. 7. 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. 8. Rewrite exercise 7 so it gets the same result but uses an inline view instead of a correlated subquery. 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 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. 3. 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. Chapter 7 How to code subqueries 4. 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 item in the Invoice_Line_Items table. Hint: Use a subquery that tests for invoice_sequence > 1. This should return 6 rows. 5. 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. 6. 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. Sort the results by the vendor state and vendor city columns. 7. 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. 8. Rewrite exercise 7 so it gets the same result but uses an inline view instead of a correlated subquery.
Expert Answer:
Answer rating: 100% (QA)
1 SELECT DISTINCT vendorname FROM VENDORS WHERE vendorid IN SELECT vendorid from invoices ORDER BY v... View the full answer
Related Book For
Posted Date:
Students also viewed these programming questions
-
Write a SELECT statement that returns three columns: email_address, order_id, and the order total for each customer. To do this, you can group the result set by the email_address and order_id...
-
Write a response discussing on your primary group classification based on the classification assigned as result of the answers to the questionnaire. What does the classification mean to you? What...
-
The following table gives information on the calorie count and grams of fat for the 11 types of bagels produced by Panera Bread. With calories as the dependent variable and fat content as the...
-
McIntyre Industries Work in Process Inventory account had a $68,000 beginning balance on May 1 ($40,000 of this related to direct materials used during April, while $28,000 related to conversion...
-
The account balances, dated 30 September 2013, of sole trader Anakin Verstege (Verstege Bike Hire) are shown below. Use this information to prepare an income statement and a balance sheet as at this...
-
A strand of wire has resistance 5.60. Find the net resistance of 120 such strands if they are. (a) Placed side by side to form a cable of the same length as a single strand, and (b) connected end to...
-
I spent time with you. It seems that this deed is out of character for you. You were not awarded your normal annual bonus. I would probably feel the same. Is that what happened? You normally wouldnt...
-
Sams Cat Hotel operates 52 weeks per year, 6 days per week, and uses a continuous review inventory system. It purchases kitty litter for $11.70 per bag. The following information is available about...
-
which of these items are included in the calculation of an asset's adjusted basis? add costs necessary to prepare the asset for use in the business, start with the asset's original basis, subtract...
-
Mike Greenberg opened Sheffield Window Washing Inc. on July 1, 2022. During July, the following transactions were completed. July 1 Issued 12,700 shares of common stock for $12,700 cash. 1 Purchased...
-
Smart Solution Sdn Bhd is a manufacturer of cell phone accessories in Kedah. Its Internal Audit department performs operational audits procedures on a regular basis. Mrs. Tanti, a senior internal...
-
Why might the calculated intrinsic value differ from the stocks current market price? Which would be correct, and what does correct mean?
-
Is the equation used to value preferred stock more like the one used to value a bond or the one used to value a normal constant growth common stock? Explain. Explain the following statement:...
-
List and explain which of your activities during the last seven days produced data for the Internet of Things. Do you feel as though the IoT could be an invasion of privacy? If so, what regulations...
-
Carlos starts a business buying and selling a new type of foldaway commuter bike. In his first year of trading, he buys 100 bikes for 90 and sells 80 of them for 170 each. How should the 20 bikes in...
-
Suppose that you are the founder of a new fashion merchandising company. Create a role-playing scenario that would allow you to get feedback on your new line of summer apparel.
-
Problem 2 (15 pts). The crystal orientation map of an unknown crystal is provided in the attached ".csv" file in terms of Euler angles, where 9 = Euler 1, = Euler 2, and 92 Euler 3. The unit of the...
-
A bar of a steel alloy that exhibits the stress-strain behavior shown in Figure 6.22 is subjected to a tensile load; the specimen is 375 mm (14.8 in.) long and has a square cross section 5.5 mm (0.22...
-
Briefly explain the meaning of the degrees of freedom for a t distribution. Give one example.
-
The probability that an adult has ever experienced a migraine headache is .35. If two adults are randomly selected, what is the probability that neither of them has ever experienced a migraine...
-
What is meant by the joint probability of two or more events? Give one example.
-
Two-dimensional surfaces that can be made by rolling up a sheet of paper are called developable surfaces. Find the geodesic equations on the following developable surfaces and solve the equations....
-
Using Euler's equation for \(y(x)\), prove that This equation provides an alternative method for solving problems in which the integrand \(f\) is not an explicit function of \(x\), because in that...
-
The time required for a particle to slide from the cusp of a cycloid to the bottom is \(t=\pi \sqrt{a / 2 g}\). Show that if the particle starts from rest at any point other than the cusp, it will...
Study smarter with the SolutionInn App