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...
-
A doctor claims that the number of births by day of the week is uniformly distributed. To test this claim, you randomly select 700 births from a recent year and record the day of the week on which...
-
Which of the following ranking systems best describes consumer preferences within a utility function? A. Util B. Ordinal C. Cardinal
-
In a study of birth order and intelligence, IQ tests were given to 18- and 19-year-old men to estimate the size of the difference, if any, between the mean IQs of firstborn sons and secondborn sons....
-
On January 3, 2014, Mega Limited purchased 3,000 shares (30%) of the common shares of Sonja Corp. for $438,000. The following information is provided about the identifiable assets and liabilities of...
-
For at least the last 20 years, most state and local jurisdictions have begun institutionalizing ADR programs and procedures for a variety of case types. In many situations, this has been the result...
-
The company has made the following profit-sharing contributions to the retirement plan for Archie and Elaine for each of the related years. 2021 2020 2019 2018 2017 2016 Balance 1/1/16 Archie None...
-
Ilham Cipta Berhad is one of the ladies shoes manufacturers. It purchases the steel logos for the shoes from an external supplier at RM 10 per set. It needs 8,000 sets in a year. The carrying cost is...
-
Research and write a synthesis of your findings on the following topic: How does offshoring affect the domestic economy as compared to the organization's performance?
-
Assume the following information: Milling Department Cost of beginning work in process inventory Costs added during the period Total cost Materials $ 10,000 294,000 $ 304,000 Conversion $ 15,000...
-
A quality engineer collected process data for five subgroups with each subgroup consisting of two samples (n=2). The median and ranges of samples in each subgroup are summarized in Table 6 below....
-
Consider the NFA specified by the following transition table with start state p and set of accepting states {s,t}. State/symbol 0 1 p {p,q} {p} 9 {r. s} {t} r {p,r} {t} * S * t {q, s} 0 0 {p} a. [3...
-
Sandhill Corporation has the following cost records for the year ended December 31, 2022: Indirect factory labour $5,120 Direct materials used 252,100 Work in process (1/1/2022) 31,300 Work in...
-
Using Desmos Calculator, sketch the graph of the following exponential functions. Take a screenshot of the graph and submit. * f(x)=2x+5 and f (x) = 2x
-
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.
-
Derive Equation 3.9, \(x(t)=C e^{-\zeta \omega_{n} t} \cos \left(\omega_{d} t-\phi ight)\).
-
(a) The response of an oscillator with mass \(m=1\) kg is shown in Figure 3.51. What can you say about the properties of the oscillator and the response? Determine the equation of motion and the...
-
Solve Equation 3.6, \(\ddot{x}+2 \zeta \omega_{n} \dot{x}+\omega_{n}^{2} x=0\) for \(\zeta>1\).\
Study smarter with the SolutionInn App