2) Create a SELECT statement that returns the count, average, max and min of the invoices submitted byeach vendor, who has submitted more than one invoice. Order by the number of invoices in descendingorder. Should return 12 rows.number ofvendor nameaverage_invoice_total min_invoice_total max_invoice_totalinvoicesFederal Exoress CorporationUnited Parcel Service4793.1510.00739.202575.331927.543813.33Zvlka DesianPacific BelMalov Lithoarachina Inc313. 5516.33867.531962. 1328.5046.2123978.4810976.0637966, 1925.67224.00Roadwav Packaoe Svstem. Ic410.926.00Blue CrossComouserveIncramData Reoroductions CorpIBM|Cardinal Business Media. Inc.188.00116.009.959.959.9521077.21579. 421575.0085.31116.5410963.6621842.00600.06132.681083. 5890.36175.00

SQL: SQL is defined as Standard Query Language it is mainly used for the manipulation of the…

2) Create a SELECT statement that returns the count, average, max and min of the invoices submitted by each vendor, who has submitted more than one invoice. Order by the number of invoices in descending order. Should return 12 rows. number of vendor name average_invoice_total min_invoice_total max_invoice total invoices Federal Exoress Corporation United Parcel Service Zvlka Desian Pacific Bel Malov Lithoarachina Inc Roadwav Packaoe Svstem. Inc Blue Cross Compuserve Inoram Data Reoroductions Coro IBM Cardinal Business Media. Inc. 47 93. 15 10.00 739.20 9 2575.33 1927.54 3813.33 867.53 313. 55 1962. 13 46.21 28.50 16.33 23978.48 10976.06 37966. 19 10.92 6.00 25.67 188.00 116.00 224.00 9.95 9.95 9.95 1077.21 579. 42 1575.00 2. 10963.66 85.31 21842.00 1083. 58 600.06 116. 54 90.36 132.68 175.00

2) Create a SELECT statement that returns the count, average, max and min of the invoices submitted by each vendor, who has submitted more than one invoice. Order by the number of invoices in descending order. Should return 12 rows. number of vendor name average_invoice_total min_invoice_total max_invoice total invoices Federal Exoress Corporation United Parcel Service Zvlka Desian Pacific Bel Malov Lithoarachina Inc Roadwav Packaoe Svstem. Inc Blue Cross Compuserve Inoram Data Reoroductions Coro IBM Cardinal Business Media. Inc. 47 93. 15 10.00 739.20 9 2575.33 1927.54 3813.33 867.53 313. 55 1962. 13 46.21 28.50 16.33 23978.48 10976.06 37966. 19 10.92 6.00 25.67 188.00 116.00 224.00 9.95 9.95 9.95 1077.21 579. 42 1575.00 2. 10963.66 85.31 21842.00 1083. 58 600.06 116. 54 90.36 132.68 175.00

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

You have been told that you need to store the zip code for employees. Add a column to the EMPLOYEE table which will be used to store each employee’s zip code Update two rows in the EMPLOYEE table and assign a zip code (must have a WHERE clause). (you MAY use 1 or 2 statements) Write a query that includes each employee's last name, department name (not ID), and salary for each employee whose salary is less than the average for their department
ERD:- PK CHECKOUT Check Num FK1 Book_Num FK2 Pat_ID Check Out Date Check_Due Date Check_In_Date PK BOOK Book Num Book Title Book_Year Book_Cost Book_Subject FK1 Pat_ID -H- PATRON PK Pat ID Pat_FName Pat_LName Pat_Type WRITES PK,FK1 Book Num PK,FK2 Au ID AUTHOR PK AU ID Au_FName Au_LName Au_BirthYear
1. Create a SELECT statement that returns the order number and date for all records in the ORDERS table. Using an inner join, include the customer name and email address associated with each order.
Task 19: List the REP_ID and the corresponding sum of the balances, as BALANCE, of all customers for each sales rep. Order and group the results by sales rep ID.
List all vendors with their address(city, state,zipcode) for all the vendors who have credited account (credit total>0)) Hint:Vendors & Invoices tables with credit total column WHERE vendors.vendor id = invoices.vendor id AND CREDIT_TOTAL>0; %3D
Assume the PRODUCT table contains multiple rows. The following code would include: SELECT P_DESCRIPT, P_PRICE FROM PRODUCT WHERE P_CODE IN(SELECT P_CODE FROM LINE GROUP BY P_CODE HAVING COUNT(*) > 1); Group of answer choices A) Select the products that have been ordered before. B) Select the products that have been ordered at least once. C) Select the products that have been ordered at least twice. D) An error message.
help please Create a table named BRANCH. The table has the following structureBRANCH_NUM decimal(2,0) NOT NULL default '0'BRANCH_NAME char(50) default NULLBRANCH_LOCATION char(50) default NULLNUM_EMPLOYEES decimal(2,0) deault NULL PRIMARY KEY (`BRANCH_NUM`) (Please type answer no write by hand)
2. Create a SELECT statement that returns the ORDER_NUM and PROD_ID for all records in the ORDERITEMS table. Using inner joins include the vendor name, product name and customer name for each item.
SQL: For every invoice, display the invoice number, invoice date and the total dollar amount for all products purchased in the invoice, ordered by invoice number in descending order and then by invoice date in ascending order. Here you will use an aggregate SUM(x,y) function in your SELECT clause to calculate the total dollar amount (a calculated field – name it TOTAL). Don’t forget to join on the tables as well. [hint: you should have 8 rows of output ordered properly]. select inv_number, inv_date sum(list_price) as 'total' from invoice, line order by desc inv_num, asc inv_date. I am getting the error: ORA-00923: FROM keyword not found where expected Am I formulating the query incorrectly?
Write a SELECT statement that returns three columns: EmailAddress, OrderID, and the order total for each customer. To do this, you can group the result set by the EmailAddress and OrderID columns. In addition, you must calculate the order total from the columns in the OrderItems table. Write a second SELECT statement that uses the first SELECT statement in its FROM clause. The main query should return two columns: the customer�s email address and the largest order for that customer. To do this, you can group the result set by the EmailAddress column.
Create a query to show the Technician Number, Last Name, First Name, Client Number,and Client Name for clients whose technician number is 23 (use Boolean variables).Save your query
Write a query that returns all customers listed in the orders table who placed orders on the most recently recorded day, return the OrderID, the CustomerID and the OrderDate .