1. 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)
2.      

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

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