Problem #1 Display all columns from the Vendors table for those vendors that are
ID: 3639223 • Letter: P
Question
Problem #1Display all columns from the Vendors table for those vendors that are located in New Jersey and have a value in the VendorPhone column (hint: not a null). Order the results by VendorName in ascending order.
Problem #2
Display the VendorID, VendorName, and the contact name (show the contact name as the VendorContactLName concatenated to the vendorcontactFName separated by a comma.). Only show those vendors that have the word “electric” somewhere in the VendorName. Order the results by VendorContactLName in ascending order and then VendorContactFName in ascending order.
Problem #3
Display VendorName’s from the Vendors table and any associated InvoiceID’s from the Invoices table. Show VendorName’s even if they do not have any associated invoices. Order the results by VendorName in ascending order.
Problem #4 (20 points)
Display InvoiceID’s from the Invoices table and the sum of the associated InvoiceLineItemAmounts’s from the InvoiceLineItems table. Only show those invoices where the sum of InvoiceLineItemAmounts is more than $10,000. Order the results in descending order by the sum of the InvoiceLineItemAmount’s.
Problem #5
Display the InvoiceID from the Invoices table that has the highest InvoiceTotal. (hint: use a subquery
Explanation / Answer
1. select * from Vendors where VendorState ="New Jersey" and VendorPhone is not null order by VendorName 2. select VendorID, VendorName,VendorContactLName + "," + VendorContactFName as contact name from Vendors where VendorName like '%electric%' order by VendorContactLName,VendorContactFName 3. select v.VendorName, i.InvoiceID from Vendors v left outer join Invoices i on v.VendorId=i.VendorId order by VendorName 4. select I.InvoiceID, Sum(L.invoiceLineItemAmounts) as Sum from Invoices I, InvoiceLineItems L where I.InvoiceID=L.InvoiceID group by I.InvoiceID having Sum(L.invoiceLineItemAmounts)>10000 order by Sum(L.invoiceLineItemAmounts) desc 5. select InvoiceID from Invoices where InvoiceTotal=(select max(InvoiceTotal) from Invoices)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.