10. List the department ID, department name, count of sales reps, and average commission rate for...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
10. List the department ID, department name, count of sales reps, and average commission rate for each department. Group by department, and sort by average commission rate in ascending order. Show the average commission rate as a percentage (e.g., 3.5% instead of .035). Use the following column headings: DeptID, DeptName, SalesRepCount. AvgCommRate 11. List the sales rep ID, first name, last name, department name, commission class and commission rate for all sales reps that earn a commission greater than 0%. Sort by sales rep ID in ascending order. 12. List the sales rep ID, first name, last name, department ID, and department name for all sales reps whose commission class is 'A.' Concatenate the first and last names together, using SalesRep Name as the column heading. Sort by department ID then by sales rep ID. 13. List the order ID and calculated total for all products sold in order 104. Format the total as currency. Use the following column headings: Qrder ID, Order Total 14. List the average product price of all products sold, formatted as currency. Use Avg Price as the column heading. 15. List the product ID, product name, and current product price for the product(s) sold in more orders than any other products (based on number of order occurrences, not the quantity). Format the price as currency, and use the following column headings: ProductID, Name, Price. 16. List the category ID, product ID, product name, and current product price for the lowest priced product in each category. Format the price as currency, and use the following column headings: Cat JD. Prod ID. Prod Name, Price. 17. List the product ID, product name, category name, and current product price for all products which have a price greater than the average product price. Format the price as currency. 18. List the order ID, order date, customer ID, customer first name, customer last name, and customer phone number for all orders on or before 1/26/22. Combine the first and last name into one column (include a space between them). Sort by order date then by customer ID, both in ascending order. Format the date as "mm-dd-xxxx" and use the following column headings: Order ID, Order Date, Cust ID, Name, Phone. 19. List the customer ID, first name, and last name of all customers whose first names start with the letter 'A' and sort by last name. Use the following column headings: CustID, FirstName, LastName 20. List the customer ID, customer first name, customer last name, and phone number of all customers who belong to sales rep 12. Combine the first and last name into one column (include a space between them). Format the phone number as '###-###-####' by using concatenation and the SUBSTR function, and use the following column headings: Customer ID, Name, Phone. SQL Plus File Edit View Window Select SQL Plus $31 =| File Explorer SQL> SQL> --14 SQL> SELECT TO_CHAR (AVG (ProdPrice),'$999,999') AS "Avg_Price" 2 FROM PRODUCT_TRJ; Avg_Price 7 GROUP BY OD.ProdID), AS TEMP )); GROUP BY OD.ProdID), AS TEMP )) ERROR at line 7: ORA-00903: invalid table name SQL> SQL> --15 SQL> SELECT 2 FROM PRODUCT WHERE ProdID IN (SELECT OD.ProdID 3 FROM ORDERDETAIL_TRJ OD INNER JOIN PRODUCT_TRJ P ON P.ProdID GROUP BY OD. ProdID HAVING COUNT (OrderID) 4 5 (SELECT MAX(TEMP. MAXCOUNT) FROM ( SELECT COUNT (OrderID) AS MAXCOUNT 6 FROM ORDERDETAIL_TRJ OD INNER JOIN PRODUCT_TRJ P ON P.ProdID OD. ProdID OD. ProdID, ProdName AS "Name", ProdPrice AS "Price" = I (SELECT ProdCatID, Min(ProdPrice) minPrice | INNER JOIN (Q) = OD. ProdID = (SELECT ProdCat ID, Min (ProdPrice) minPrice FROM PRODUCT_TRJ GROUP BY ProdCatID ) b ON a. ProdCat ID = b. ProdCatID AND a.ProdPrice = b. minPrice ; < SQL> SQL> --16 SQL> SELECT a.ProdCatID AS "Cat_ID", ProdID AS "Prod_ID", ProdName AS "Prod_Name", TO_CHAR(ProdPrice, '$999,999') AS "Price" 2 FROM PRODUCT_TRJ a 3 INNER JOIN 4 407 CH X 00 Activate Windows --17 SELECT ProdID AS "Product_ID", ProdName AS "Product_Name", ProdCat Name AS "Category", TO_CHAR ( ProdPrice, G$999,99919 SAS Pricee Windows. CROM DRADUCT TOn TANED TATM DRODCAT TDT DC ON DC Dunda+TD - D DAC+TO Sun Dec 4 9:26 PM SCOLA HIF A 10. List the department ID, department name, count of sales reps, and average commission rate for each department. Group by department, and sort by average commission rate in ascending order. Show the average commission rate as a percentage (e.g., 3.5% instead of .035). Use the following column headings: DeptID, DeptName, SalesRepCount. AvgCommRate 11. List the sales rep ID, first name, last name, department name, commission class and commission rate for all sales reps that earn a commission greater than 0%. Sort by sales rep ID in ascending order. 12. List the sales rep ID, first name, last name, department ID, and department name for all sales reps whose commission class is 'A.' Concatenate the first and last names together, using SalesRep Name as the column heading. Sort by department ID then by sales rep ID. 13. List the order ID and calculated total for all products sold in order 104. Format the total as currency. Use the following column headings: Qrder ID, Order Total 14. List the average product price of all products sold, formatted as currency. Use Avg Price as the column heading. 15. List the product ID, product name, and current product price for the product(s) sold in more orders than any other products (based on number of order occurrences, not the quantity). Format the price as currency, and use the following column headings: ProductID, Name, Price. 16. List the category ID, product ID, product name, and current product price for the lowest priced product in each category. Format the price as currency, and use the following column headings: Cat JD. Prod ID. Prod Name, Price. 17. List the product ID, product name, category name, and current product price for all products which have a price greater than the average product price. Format the price as currency. 18. List the order ID, order date, customer ID, customer first name, customer last name, and customer phone number for all orders on or before 1/26/22. Combine the first and last name into one column (include a space between them). Sort by order date then by customer ID, both in ascending order. Format the date as "mm-dd-xxxx" and use the following column headings: Order ID, Order Date, Cust ID, Name, Phone. 19. List the customer ID, first name, and last name of all customers whose first names start with the letter 'A' and sort by last name. Use the following column headings: CustID, FirstName, LastName 20. List the customer ID, customer first name, customer last name, and phone number of all customers who belong to sales rep 12. Combine the first and last name into one column (include a space between them). Format the phone number as '###-###-####' by using concatenation and the SUBSTR function, and use the following column headings: Customer ID, Name, Phone. 10. List the department ID, department name, count of sales reps, and average commission rate for each department. Group by department, and sort by average commission rate in ascending order. Show the average commission rate as a percentage (e.g., 3.5% instead of .035). Use the following column headings: DeptID, DeptName, SalesRepCount. AvgCommRate 11. List the sales rep ID, first name, last name, department name, commission class and commission rate for all sales reps that earn a commission greater than 0%. Sort by sales rep ID in ascending order. 12. List the sales rep ID, first name, last name, department ID, and department name for all sales reps whose commission class is 'A.' Concatenate the first and last names together, using SalesRep Name as the column heading. Sort by department ID then by sales rep ID. 13. List the order ID and calculated total for all products sold in order 104. Format the total as currency. Use the following column headings: Qrder ID, Order Total 14. List the average product price of all products sold, formatted as currency. Use Avg Price as the column heading. 15. List the product ID, product name, and current product price for the product(s) sold in more orders than any other products (based on number of order occurrences, not the quantity). Format the price as currency, and use the following column headings: ProductID, Name, Price. 16. List the category ID, product ID, product name, and current product price for the lowest priced product in each category. Format the price as currency, and use the following column headings: Cat JD. Prod ID. Prod Name, Price. 17. List the product ID, product name, category name, and current product price for all products which have a price greater than the average product price. Format the price as currency. 18. List the order ID, order date, customer ID, customer first name, customer last name, and customer phone number for all orders on or before 1/26/22. Combine the first and last name into one column (include a space between them). Sort by order date then by customer ID, both in ascending order. Format the date as "mm-dd-xxxx" and use the following column headings: Order ID, Order Date, Cust ID, Name, Phone. 19. List the customer ID, first name, and last name of all customers whose first names start with the letter 'A' and sort by last name. Use the following column headings: CustID, FirstName, LastName 20. List the customer ID, customer first name, customer last name, and phone number of all customers who belong to sales rep 12. Combine the first and last name into one column (include a space between them). Format the phone number as '###-###-####' by using concatenation and the SUBSTR function, and use the following column headings: Customer ID, Name, Phone. 10. List the department ID, department name, count of sales reps, and average commission rate for each department. Group by department, and sort by average commission rate in ascending order. Show the average commission rate as a percentage (e.g., 3.5% instead of .035). Use the following column headings: DeptID, DeptName, SalesRepCount. AvgCommRate 11. List the sales rep ID, first name, last name, department name, commission class and commission rate for all sales reps that earn a commission greater than 0%. Sort by sales rep ID in ascending order. 12. List the sales rep ID, first name, last name, department ID, and department name for all sales reps whose commission class is 'A.' Concatenate the first and last names together, using SalesRep Name as the column heading. Sort by department ID then by sales rep ID. 13. List the order ID and calculated total for all products sold in order 104. Format the total as currency. Use the following column headings: Qrder ID, Order Total 14. List the average product price of all products sold, formatted as currency. Use Avg Price as the column heading. 15. List the product ID, product name, and current product price for the product(s) sold in more orders than any other products (based on number of order occurrences, not the quantity). Format the price as currency, and use the following column headings: ProductID, Name, Price. 16. List the category ID, product ID, product name, and current product price for the lowest priced product in each category. Format the price as currency, and use the following column headings: Cat JD. Prod ID. Prod Name, Price. 17. List the product ID, product name, category name, and current product price for all products which have a price greater than the average product price. Format the price as currency. 18. List the order ID, order date, customer ID, customer first name, customer last name, and customer phone number for all orders on or before 1/26/22. Combine the first and last name into one column (include a space between them). Sort by order date then by customer ID, both in ascending order. Format the date as "mm-dd-xxxx" and use the following column headings: Order ID, Order Date, Cust ID, Name, Phone. 19. List the customer ID, first name, and last name of all customers whose first names start with the letter 'A' and sort by last name. Use the following column headings: CustID, FirstName, LastName 20. List the customer ID, customer first name, customer last name, and phone number of all customers who belong to sales rep 12. Combine the first and last name into one column (include a space between them). Format the phone number as '###-###-####' by using concatenation and the SUBSTR function, and use the following column headings: Customer ID, Name, Phone. SQL Plus File Edit View Window Select SQL Plus $31 =| File Explorer SQL> SQL> --14 SQL> SELECT TO_CHAR (AVG (ProdPrice),'$999,999') AS "Avg_Price" 2 FROM PRODUCT_TRJ; Avg_Price 7 GROUP BY OD.ProdID), AS TEMP )); GROUP BY OD.ProdID), AS TEMP )) ERROR at line 7: ORA-00903: invalid table name SQL> SQL> --15 SQL> SELECT 2 FROM PRODUCT WHERE ProdID IN (SELECT OD.ProdID 3 FROM ORDERDETAIL_TRJ OD INNER JOIN PRODUCT_TRJ P ON P.ProdID GROUP BY OD. ProdID HAVING COUNT (OrderID) 4 5 (SELECT MAX(TEMP. MAXCOUNT) FROM ( SELECT COUNT (OrderID) AS MAXCOUNT 6 FROM ORDERDETAIL_TRJ OD INNER JOIN PRODUCT_TRJ P ON P.ProdID OD. ProdID OD. ProdID, ProdName AS "Name", ProdPrice AS "Price" = I (SELECT ProdCatID, Min(ProdPrice) minPrice | INNER JOIN (Q) = OD. ProdID = (SELECT ProdCat ID, Min (ProdPrice) minPrice FROM PRODUCT_TRJ GROUP BY ProdCatID ) b ON a. ProdCat ID = b. ProdCatID AND a.ProdPrice = b. minPrice ; < SQL> SQL> --16 SQL> SELECT a.ProdCatID AS "Cat_ID", ProdID AS "Prod_ID", ProdName AS "Prod_Name", TO_CHAR(ProdPrice, '$999,999') AS "Price" 2 FROM PRODUCT_TRJ a 3 INNER JOIN 4 407 CH X 00 Activate Windows --17 SELECT ProdID AS "Product_ID", ProdName AS "Product_Name", ProdCat Name AS "Category", TO_CHAR ( ProdPrice, G$999,99919 SAS Pricee Windows. CROM DRADUCT TOn TANED TATM DRODCAT TDT DC ON DC Dunda+TD - D DAC+TO Sun Dec 4 9:26 PM SCOLA HIF A SQL Plus File Edit View Window Select SQL Plus $31 =| File Explorer SQL> SQL> --14 SQL> SELECT TO_CHAR (AVG (ProdPrice),'$999,999') AS "Avg_Price" 2 FROM PRODUCT_TRJ; Avg_Price 7 GROUP BY OD.ProdID), AS TEMP )); GROUP BY OD.ProdID), AS TEMP )) ERROR at line 7: ORA-00903: invalid table name SQL> SQL> --15 SQL> SELECT 2 FROM PRODUCT WHERE ProdID IN (SELECT OD.ProdID 3 FROM ORDERDETAIL_TRJ OD INNER JOIN PRODUCT_TRJ P ON P.ProdID GROUP BY OD. ProdID HAVING COUNT (OrderID) 4 5 (SELECT MAX(TEMP. MAXCOUNT) FROM ( SELECT COUNT (OrderID) AS MAXCOUNT 6 FROM ORDERDETAIL_TRJ OD INNER JOIN PRODUCT_TRJ P ON P.ProdID OD. ProdID OD. ProdID, ProdName AS "Name", ProdPrice AS "Price" = I (SELECT ProdCatID, Min(ProdPrice) minPrice | INNER JOIN (Q) = OD. ProdID = (SELECT ProdCat ID, Min (ProdPrice) minPrice FROM PRODUCT_TRJ GROUP BY ProdCatID ) b ON a. ProdCat ID = b. ProdCatID AND a.ProdPrice = b. minPrice ; < SQL> SQL> --16 SQL> SELECT a.ProdCatID AS "Cat_ID", ProdID AS "Prod_ID", ProdName AS "Prod_Name", TO_CHAR(ProdPrice, '$999,999') AS "Price" 2 FROM PRODUCT_TRJ a 3 INNER JOIN 4 407 CH X 00 Activate Windows --17 SELECT ProdID AS "Product_ID", ProdName AS "Product_Name", ProdCat Name AS "Category", TO_CHAR ( ProdPrice, G$999,99919 SAS Pricee Windows. CROM DRADUCT TOn TANED TATM DRODCAT TDT DC ON DC Dunda+TD - D DAC+TO Sun Dec 4 9:26 PM SCOLA HIF A SQL Plus File Edit View Window Select SQL Plus $31 =| File Explorer SQL> SQL> --14 SQL> SELECT TO_CHAR (AVG (ProdPrice),'$999,999') AS "Avg_Price" 2 FROM PRODUCT_TRJ; Avg_Price 7 GROUP BY OD.ProdID), AS TEMP )); GROUP BY OD.ProdID), AS TEMP )) ERROR at line 7: ORA-00903: invalid table name SQL> SQL> --15 SQL> SELECT 2 FROM PRODUCT WHERE ProdID IN (SELECT OD.ProdID 3 FROM ORDERDETAIL_TRJ OD INNER JOIN PRODUCT_TRJ P ON P.ProdID GROUP BY OD. ProdID HAVING COUNT (OrderID) 4 5 (SELECT MAX(TEMP. MAXCOUNT) FROM ( SELECT COUNT (OrderID) AS MAXCOUNT 6 FROM ORDERDETAIL_TRJ OD INNER JOIN PRODUCT_TRJ P ON P.ProdID OD. ProdID OD. ProdID, ProdName AS "Name", ProdPrice AS "Price" = I (SELECT ProdCatID, Min(ProdPrice) minPrice | INNER JOIN (Q) = OD. ProdID = (SELECT ProdCat ID, Min (ProdPrice) minPrice FROM PRODUCT_TRJ GROUP BY ProdCatID ) b ON a. ProdCat ID = b. ProdCatID AND a.ProdPrice = b. minPrice ; < SQL> SQL> --16 SQL> SELECT a.ProdCatID AS "Cat_ID", ProdID AS "Prod_ID", ProdName AS "Prod_Name", TO_CHAR(ProdPrice, '$999,999') AS "Price" 2 FROM PRODUCT_TRJ a 3 INNER JOIN 4 407 CH X 00 Activate Windows --17 SELECT ProdID AS "Product_ID", ProdName AS "Product_Name", ProdCat Name AS "Category", TO_CHAR ( ProdPrice, G$999,99919 SAS Pricee Windows. CROM DRADUCT TOn TANED TATM DRODCAT TDT DC ON DC Dunda+TD - D DAC+TO Sun Dec 4 9:26 PM SCOLA HIF A
Expert Answer:
Related Book For
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last
Posted Date:
Students also viewed these databases questions
-
To determine the focal length of a lens, you place the lens in front of a small lightbulb and then adjust a viewing screen to get a sharply focused image. Varying the lens position produces the...
-
On January 1, 2015, Randy Incorporated purchased $500,000 of 20-year, 10% bonds when the market rate of interest was 8%. Interest is to be paid on June 30 and December 31 of each year. 1. Prepare the...
-
(4) Sky High Company is considering the purchase of an investment of $250,000. Data related to the investment are as follows: $7,000 Cash flows (at the end of the next 4 years) $125,000 Salvage value...
-
The following MINITAB output presents a multiple regression equation y = b0 + b1x1 + b2x2 + b3x3 + b4x4. It is desired to drop one of the explanatory variables. Which of the following is the most...
-
Identify four potential unethical actions or inactions related to capacity planning, and the ethical principle each violates.
-
Develop written responses for the Application Exercise below outlining three buying situations. Overview Assume the role of marketing manager for a large firm that produces stain resistant fabrics...
-
Overjoyed to be alive, George (James Stewart) runs home bankruptcy and scandal now seem trivial next to the love he feels for his family and their drafty old house. Before he can be carted off to...
-
A straight beam of solid and rectangular cross section made of AISI 4340 steel (Sut = 250 kpsi). The surface of the beam is ground to improve its fatigue life. The beam (non-rotating) is subjected to...
-
Consider the following circuit: C D 1. Create the truth table for function F.. 2. Represent function F as sum-of-minterms. Vcc I 0 A Mux so B FL
-
For SVHN Dataset- http://ufldl.stanford.edu/housenumbers/ (Format 2 - train_32x32.mat, test_32x32.mat,extra_32x32.mat) In python 1. Preprocess it . executing it will generate a set of processed...
-
An arrow is shot at an angle 360 with the horizontal. It has a velocity of 49 m/s. How high will the arrow go? The acceleration of gravity is 9.8 m/s. Answer in units of m. What horizontal distance...
-
The U.S. Department of Agriculture (USDA) uses sample surveys to produce important economic estimates. One pilot study estimated durum wheat prices in July and in January using independent samples...
-
Which of the following is not a functional budget? a) Labour budget b) Cash budget c) Materials budget d) Expenses budget Select one: a. Expenses budget b. Materials budget c. Cash budget d. Labour...
-
Q1) What is the a3 Value Q2) What is the a7 Value Q3) What is the a4 Value Q4) What is the b3 Value Q5) What is the b2 Value Q6) What is the sign of 2nd constraint? A pastry chef at a bakery wants to...
-
What are the SQL built-in functions? How do you use them in an SQL query?
-
Define functional dependence.
-
The owner of Colonial Adventure Tours knows the importance of the SQL language in database management. He realizes that he can use SQL to perform the same functions that you performed with queries in...
-
Write the equations of motion of a multidegree-of-freedom system in matrix form using a. the flexibility matrix, and b. the stiffness matrix.
-
Is the stiffness matrix \([k]\) always positive definite? Why?
-
What is a generalized mass matrix?
Study smarter with the SolutionInn App