What SQL query can be used to find the VendorIDs that appear in both the top 20
Fantastic news! We've Found the answer you've been seeking!
Question:
- What SQL query can be used to find the VendorIDs that appear in both the top 20 and bottom 20 invoices, based on their total invoice amounts? (using CTEs and the INTERSECT join condition)
Transcribed Image Text:
Connect C + dbo.InvoiceArchive dbo.InvoiceLineltems + dbo.Invoices dbo.Terms dbo.Vendors Columns VendorID (PK, int, not null) VendorName (varchar(50), not null) VendorAddress 1 (varchar(50), null) VendorAddress2 (varchar(50), null) E VendorCity (varchar(50), not null) VendorState (char(2), not null) VendorZipCode (varchar(20), not null) VendorPhone (varchar(50), null) VendorContactLName (varchar(50), null) VendorContactFName (varchar(50), null) DefaultTermsID (FK, int, not null) DefaultAccountNo (FK, int, not null) Keys TO PK_Vendors FK Vendors GLAccounts OFK Vendors Terms Constraints DF_Vendors_AccountNo DF_Vendors TermsID Triggers Indexes IX VendorName (Non-Unique, Non-Clustered) IX_Vendors_Account No (Non-Unique, Non-Clustered) IX_Vendors_TermsID (Non-Unique, Non-Clustered) TOPK_Vendors (Clustered) Statistics _WA_Sys_00000008_3F466844 IX_VendorName MIX Vendors_AccountNo IX Vendors_TermsID HPK_Vendors Views External Resources Synonyms Connect C + dbo.InvoiceArchive dbo.InvoiceLineltems + dbo.Invoices dbo.Terms dbo.Vendors Columns VendorID (PK, int, not null) VendorName (varchar(50), not null) VendorAddress 1 (varchar(50), null) VendorAddress2 (varchar(50), null) VendorCity (varchar(50), not null) VendorState (char(2), not null) VendorZipCode (varchar(20), not null) VendorPhone (varchar(50), null) VendorContactLName (varchar(50), null) VendorContactFName (varchar(50), null) DefaultTermsID (FK, int, not null) DefaultAccountNo (FK, int, not null) Keys TO PK_Vendors FK Vendors_GLAccounts OFK Vendors_Terms Constraints DF_Vendors_AccountNo DF_Vendors TermsID Triggers Indexes IX_VendorName (Non-Unique, Non-Clustered) IX_Vendors_Account No (Non-Unique, Non-Clustered) IX_Vendors_TermsID (Non-Unique, Non-Clustered) TOPK_Vendors (Clustered) Statistics _WA_Sys_00000008_3F466844 IX_VendorName MIX Vendors_AccountNo IX Vendors_TermsID HPK_Vendors Views External Resources Synonyms Connect C + dbo.InvoiceArchive dbo.InvoiceLineltems + dbo.Invoices dbo.Terms dbo.Vendors Columns VendorID (PK, int, not null) VendorName (varchar(50), not null) VendorAddress 1 (varchar(50), null) VendorAddress2 (varchar(50), null) E VendorCity (varchar(50), not null) VendorState (char(2), not null) VendorZipCode (varchar(20), not null) VendorPhone (varchar(50), null) VendorContactLName (varchar(50), null) VendorContactFName (varchar(50), null) DefaultTermsID (FK, int, not null) DefaultAccountNo (FK, int, not null) Keys TO PK_Vendors FK Vendors GLAccounts OFK Vendors Terms Constraints DF_Vendors_AccountNo DF_Vendors TermsID Triggers Indexes IX VendorName (Non-Unique, Non-Clustered) IX_Vendors_Account No (Non-Unique, Non-Clustered) IX_Vendors_TermsID (Non-Unique, Non-Clustered) TOPK_Vendors (Clustered) Statistics _WA_Sys_00000008_3F466844 IX_VendorName MIX Vendors_AccountNo IX Vendors_TermsID HPK_Vendors Views External Resources Synonyms Connect C + dbo.InvoiceArchive dbo.InvoiceLineltems + dbo.Invoices dbo.Terms dbo.Vendors Columns VendorID (PK, int, not null) VendorName (varchar(50), not null) VendorAddress 1 (varchar(50), null) VendorAddress2 (varchar(50), null) VendorCity (varchar(50), not null) VendorState (char(2), not null) VendorZipCode (varchar(20), not null) VendorPhone (varchar(50), null) VendorContactLName (varchar(50), null) VendorContactFName (varchar(50), null) DefaultTermsID (FK, int, not null) DefaultAccountNo (FK, int, not null) Keys TO PK_Vendors FK Vendors_GLAccounts OFK Vendors_Terms Constraints DF_Vendors_AccountNo DF_Vendors TermsID Triggers Indexes IX_VendorName (Non-Unique, Non-Clustered) IX_Vendors_Account No (Non-Unique, Non-Clustered) IX_Vendors_TermsID (Non-Unique, Non-Clustered) TOPK_Vendors (Clustered) Statistics _WA_Sys_00000008_3F466844 IX_VendorName MIX Vendors_AccountNo IX Vendors_TermsID HPK_Vendors Views External Resources Synonyms
Expert Answer:
Answer rating: 100% (QA)
Based on the information provided in the screenshots for the database structure you can write a SQL ... View the full answer
Related Book For
Systems analysis and design
ISBN: 978-0136089162
8th Edition
Authors: kenneth e. kendall, julie e. kendall
Posted Date:
Students also viewed these databases questions
-
Solve the following equations in the interval [0,2): a) tan + 3=0 b) sin(30) = 1 c) 8 12sin20 = 4cos20 d) sin cos 0 = 1 e) csc0 = 1
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
On your computer, go to this tutorial to study the basic MongoDB CRUD operations: https://docs.mongodb.com/manual/crud/ Open our lab MongoDB collection at: https://mongoplayground.net/p/xxyyMDsySoJ...
-
1. A projectile is launched in a vertical plane, at an angle 0 with initial velocity vo. It must be caught in a frictionless circular tube of radius R in such a way that the trajectory of the...
-
We know that a compass points northward because Earth is a giant magnet. Will the northward-pointing needle point northward when the compass is brought to the Southern Hemisphere?
-
Determine \(a\) so that the following system has a nontrivial solution: \[ \left[\begin{array}{cccc} 2 & -1 & 0 & 0 \\ 1 & a & 0 & 0 \\ 0 & 0 & 2 a & 1 \\ 0 & 0 & -1 & \frac{1}{2} \end{array}...
-
Barbara Whitley had great expectations about her future as she sat at her graduation ceremony in May 2000. She was about to receive her Masters of Accountancy degree, and the following week she would...
-
Using data from the CVS Corporation annual report in the Supplement to Chapter 5, conduct a comprehensive ratio analysis that compares the companys performance in 2008 and 2007. If you have computed...
-
State the dual problem. Use y1, y2, y3 and y4 as the variables. Given: y1 0, y2 = 0, y3 0, and y4 0. 21) Minimize w=2x1 +3x2+x3 subject to: x1+3x2 + 2x3 32 2x1+4x2+3x3 66 x10, x2 0, x3 0 3
-
A hair dryer draws 9.0A when plugged into a 120V outlet. What is its resistance? How much charge passes through it in 15 min?
-
1 . I now have $ 7 5 , 0 0 0 in the bank earning interest of 0 . 8 2 % per month. I need $ 7 5 0 , 0 0 0 to make a down payment on a house. I can save an addiAonal $ 2 , 5 0 0 per month. How long...
-
As a management accountant, what recommendations are you going to give to the senior management team to manage and reduce cost?
-
Petty Corp. purchased a building on January 1, 2017, for P6,000,000. The same had an expected useful life of 10 years. Straight-line depreciation method is in place for similar items with no residual...
-
On December 31, 2020, Patty Corp. subjected to impairment test a building which was the company's factory site. Because of the expected decline the demand for the company's product, the company...
-
I need to choose three income streams that I currently have or will have in the future, and then classify them as active or passive according to the Internal Revenue Code. I am employed full-time and...
-
The Sweetwater Candy Company would like to buy a new machine for $ 2 4 0 , 0 0 0 that automatically dips chocolates. The manufacturer estimates the machine would be usable for five years but would...
-
Suppose that the laptop of Prob. 2.16 is placed in an insulating briefcase with a fully charged battery, but it does not go into sleep mode, and the battery discharges as if the laptop were in use....
-
In what four categories does the FOLKLORE documentation method collect information?
-
As one of the systems analysts who is part of Sylva Foxxs firm, suggest some ways that Sam Pelt can use software on the PC he has to adequately sample the opinions of his customers, store managers,...
-
Whose primary responsibility is it to test computer programs?
-
\(3 \pi+7 \sqrt{21}=7 \sqrt{21}+3 \pi\) Identify the property of real numbers that is being illustrated.
-
\((-10 \pi)+10 \pi=0\) Identify the property of real numbers that is being illustrated.
-
\(14 \times(3 \sqrt{2}+11.6)=14 \times 3 \sqrt{2}+14 \times 11.6\) Identify the property of real numbers that is being illustrated.
Study smarter with the SolutionInn App