Write a SELECT statement based on the view you just created that returns the TOP 10...
Write a SELECT statement based on the view you just created that returns the TOP 10 vendorName and the SUM of the invoiceTotal (aliased as sumOfTotal) field of the view sorted by the sumOfTotal in descending order. Hold that thought, because you will use that code in the next exercise!
Homework Answers
Dear Student,
Following is the query for creating the view :
CREATE VIEW Top10PaidInvoices
AS
SELECT TOP 10 VendorName,
MAX(InvoiceDate) AS LastInvoice,
SUM(InvoiceTotal) AS SumOfTotal
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceTotal - CreditTotal - PaymentTotal = 0
GROUP BY VendorName
ORDER BY SUM(InvoiceTotal) DESC
Now lets create a select query on the view :
Select VendorName, SumOfTotal from Top10PaidInvoices;
If you like this answer, give a thumbs up! If you have any doubt feel free to revert back, would be happy to help you. Keep HomeworkLibing. Happy Learning :)
Add Answer to:
Write a SELECT statement based on the view you just created that
returns the TOP 10...
SQL - write a select statement that returns four columns: vendorName, invoiceNumber, invoiceDate, and invoiceTotal. Return...
SQL - write a select statement that returns four columns: vendorName, invoiceNumber, invoiceDate, and invoiceTotal. Return ONE ROW per vendor, representing the vendors invoice with the earliest date.
#6 Write a SELECT statement that returns these columns: The count of the number of orders...
#6 Write a SELECT statement that returns these columns: The count of the number of orders in the Orders table The sum of the tax_amount columns in the Orders table Write a SELECT statement that returns one row for each category that has products with these columns: The category_name column from the Categories table The count of the products in the Products table The list price of the most expensive product in the Products table Sort the result set so...
Write a SELECT statement that returns: - Vendor Name - Invoice Number - Invoice Line Item...
Write a SELECT statement that returns: - Vendor Name - Invoice Number - Invoice Line Item Amount Use a subquery to display only the records that have an Invoice Line Item Amount that is greater than the Average Invoice Line Item Amount. Display in descending Invoice Line Item Amount order.
3. Write a script that adds rows to the database that you created in exercise 2....
3. Write a script that adds rows to the database that you created in exercise 2. Add two rows to the Users and Products tables. Add three rows to the Downloads table: one row for user 1 and product 2; one row for user 2 and product 1; and one row for user 2 and product 2. Use the SYSDATE function to insert the current date into the download_date column. Use the sequences created in the previous exercise to get...
Write the SQL statement to select records from a table called CUSTOMER_INFO that returns the customer's...
Write the SQL statement to select records from a table called CUSTOMER_INFO that returns the customer's first name, last name, area code and phone number (you can make up field names). Only return records that have a Stephenville area code (254).
Write a select statement that returns the vendorname and paymentsum of each vendor, where paymentsum is...
Write a select statement that returns the vendorname and
paymentsum of each vendor, where paymentsum is the sum of the
paymentotal column. Return only the top ten vendors who have been
paid the most and the number of invoices is >5.
CREATE TABLE InvoiceArchive InvoiceID int NOT NULL, VendorID int NOT NULL InvoiceNumber varchar(50) NOT NULL, InvoiceDate smalldatetime NOT NULL, Invoice Total money NOT NULL, Payment Total money NOT NULL, CreditTotal money NOT NULL, TermsID int NOT NULL, InvoiceDueDate smalldatetime...
and blankS iI a SELECT statement that returns these columns from the Invoices Write table 4....
and blankS iI a SELECT statement that returns these columns from the Invoices Write table 4. The invoice number column The invoice date column The invoice_date column plus 30 days The payment_date column A column named days_to_pay that shows the number of days between the invoice date and the payment date The number of the invoice date's month The four-digit year of the invoice date When you have this working, add a WHERE clause that retrieves just the invoices for...
Answer the following SQL study guide questions... Question 1 (1 point) Unless you assign a column...
Answer the following SQL study guide
questions...
Question 1 (1 point) Unless you assign a column name in the base table. the column name in the result set is the same as the 1) unique syntax 2) column alias 3) qualification 4) all of the above Question 2 (1 point) Which clause specifies the number of rows that should be skipped before rows are returned from the result set? 1) FETCH 2) OFFSET 3) FIRST 4) NEXT Question 3 (1...
What happens when you try to compile and run the following code? String query = "INSERT...
What happens when you try to compile and run the following code? String query = "INSERT INTO Invoices (InvoiceDate InvoiceTotal) " + "VALUES ('2/10/01', '443.55')"; Statement statement = connection.createStatement(); statement.executeUpdate(query); a. A SQLException is thrown at runtime because the executeUpdate method can’t accept a String object. b. A SQLException is thrown at runtime because the SQL statement is coded incorrectly. c. An error occurs at compile time because the SQL statement is coded incorrectly. d. This code compiles and...
I need to write an SQL statement to run this query: SALES has the following column...
I need to write an SQL statement to run this query: SALES has the following column names: vendorid, name, upc, move, price, qty, year, store the table name is SALES... the sales is not a column in the SALES so it needs to be created in the query with the formula as well. QUESTION BELOW: Query 4: Return the vendor id, vendor name, product UPC code, move, and sales (price * move/qty) for each product sold by store 100 in...
Write a select statement that returns the vendorname and
paymentsum of each vendor, where paymentsum is the sum of the
paymentotal column. Return only the top ten vendors who have been
paid the most and the number of invoices is >5.
CREATE TABLE InvoiceArchive InvoiceID int NOT NULL, VendorID int NOT NULL InvoiceNumber varchar(50) NOT NULL, InvoiceDate smalldatetime NOT NULL, Invoice Total money NOT NULL, Payment Total money NOT NULL, CreditTotal money NOT NULL, TermsID int NOT NULL, InvoiceDueDate smalldatetime...
and blankS iI a SELECT statement that returns these columns from the Invoices Write table 4. The invoice number column The invoice date column The invoice_date column plus 30 days The payment_date column A column named days_to_pay that shows the number of days between the invoice date and the payment date The number of the invoice date's month The four-digit year of the invoice date When you have this working, add a WHERE clause that retrieves just the invoices for...
Answer the following SQL study guide
questions...
Question 1 (1 point) Unless you assign a column name in the base table. the column name in the result set is the same as the 1) unique syntax 2) column alias 3) qualification 4) all of the above Question 2 (1 point) Which clause specifies the number of rows that should be skipped before rows are returned from the result set? 1) FETCH 2) OFFSET 3) FIRST 4) NEXT Question 3 (1...
Most questions answered within 3 hours.
-
Define the following Marketing Psychology Principles of Human
Behavior: Priming, Reciprocity, Social Proof, Decoy Effect,
Scarcity,...
asked 10 minutes ago -
What does the graph of Range vs angle look like? Also what is
the slope of...
asked 12 minutes ago -
a
sample size of _ is needed So there a 99% confidence interval will
have a...
asked 18 minutes ago -
Strategy is an important part of project management and business
decisions. Define strategy and describe in...
asked 21 minutes ago -
Task 5.2 Numerical Analysis Using Nested Loops (13 pts)
Consider the following program:
void setup()
{...
asked 35 minutes ago -
If a lossless transformer has 1000 turns for a primary winding
and 100 turns for the...
asked 43 minutes ago -
Write the net ionic equation for the precipitation reaction that
occurs when aqueous solutions of potassium...
asked 52 minutes ago -
it
should be written in c++
Your program should take numbers from the user until the...
asked 58 minutes ago -
Buses are powered by chemical reactions. Define matter and the
four states of matter. What is...
asked 1 hour ago -
Use conservation of energy to find the velocity of a free point
charge q1 at 22cm...
asked 1 hour ago -
First, describe policies promoted by governments of the
political right to address economic globalization. Second, describe...
asked 1 hour ago -
M2-9 Completing T-Accounts LO2-4
Following are the transactions of Dennen, Inc., for the month of
January....
asked 1 hour ago