This project must be completed using an Oracle database with all statements manually typed into an...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
This project must be completed using an Oracle database with all statements manually typed into an SQL file and run as a batch in Oracle, and the output must be written to a text (.txt) file. Statements and results (feedback from Oracle) must be included in the output file for submission. Directions for downloading the software and for creating and running the SQL file will be posted in Canvas. Project 2 Relational Schema PRODUCT ProdID ProdName ProdCatID ProdPrice ORDERDETAIL OrderD ProdID ProdQty ProdPrice PRODCAT ProdCatID ProdCatName ORDER OrderID OrderDate CustID CUSTOMER CustID CustFName Custl Name CustPhone SalesRepID COMMISSION CommClass CommRate DEPARTMENT DeptiD DeptName SALESREP Sales ReplD SalesRepFName Sales ReplName CommClass DeptID Using Oracle, access the tables you created in Project 2 and complete the following SQL transactions. The text file must contain both your statements and the results from Oracle (this will be the spooled .txt file). All column headings must be shown in their entirety (i.e., points will be deducted if column names are truncated). Insert comment lines at the top of your file containing your full name, course-section number, and project number). In addition, use comment lines to number your statements. Add a cover sheet with your name, course, project, and submission date; and submit at the beginning of class on the date specified in Canvas. Part I. 1. All column headings must show in their entirety--no truncated column headings. 2. Line size should be set at least 125 and no more than 150 to minimize column wrapping. 3. Properly use table aliases and dot notation where applicable. 4. Add a new Customer: CustID T104 7 5. Add a new Product: ProdID 246 CustFName Wes ProdID 618 407 124 Add Order Details using the Order Prodoty INSY 3304 Project 3 ProdName Milwaukee Power Drill ProdID 535 246 610 CustLName, Thomas 1 2 1 6. Add a new Order. Generate the Order by incrementing the max QrderID, by 1. OrderDate CustID 1/28/2022 T104 Add Order Details, using the Order Prodoty 3 1 2 CustPhone 469-555-1215 ProdCatID 2 SalesRenID 22 7. Add a new Order. Generate the Order by incrementing the max OrderID by 1. OrderDate CustID 1/29/2022 S100 Price $179 for the new Order above: ProdPrice Current Price* Current Price* Current Price* Current Price* Current Price* Current Price* for the new Order above: ProdPrice L * Use a nested SELECT statement to get the current price from the PRODUCT table. 8. Change the phone number for Customer B200 to 817-555-8918. L 9. Commit all changes above before proceeding to the next step. Part II. 1. List the first name, last name, sales rep ID, commission class, and commission rate for all Sales Reps. Combine the first and last name into one column (include a space between them). Sort by last name in ascending order, and use the following column headings: SalesRep Name, Sales Rep ID, Commission Class, Commission Rate. 2. List all rows and all columns from QrderDetail; sort by Order ID then by Product ID, both in ascending order; format the Product Price as currency. Use the following column headings: Order ID, Product ID, Qty, Price 3. For all customers, list the customer ID, customer first name, customer last name, and customer phone number, along with the sales rep ID, sales rep first name, and sales rep last name of the sales rep to whom that customer belongs; sort by Customer ID in ascending order; format the phone number as '(###) ###-####' by using concatenation and the SUBSTR function. Use the following column headings: CustID, CustFirstName, CustLastName, CustPhone, SalesRepID SalesRepFirstName. SalesRepLastName. 4. List the department ID, department name, sales rep ID, first name, last name, commission class, and commission rate of the sales rep(s) who earn the highest commission in each department. Use the following column headings: Dept ID. Dept Name, Sales Rep JD. First Name. Last Name, Commission Class, Commission Rate 5. List the product ID, product name, category name, and price for the highest priced product(s) sold in order 100. Show the price formatted as currency, and use the following column headings: Product ID, Product Name, Category, Price. 6. List the department name and the count of sales reps in each department; group by department name, and sort by sales rep count in ascending order. Use the following column headings: Dept Name, Sales Rep Count. 7. List the sales rep ID, first name, last name, and commission rate for each sales rep who earns a commission rate less than or equal to 5%, but do NOT include sales reps who earn 0% commission. Sort by commission rate in descending order. Show the commission rate as a percentage (e.g., 5% instead of .05). Use the following column headings: Sales Rep JD. First Name, Last Name, Commission Rate 8. For each order, list the order ID, order date, customer ID, customer first name, customer last name, sales rep ID, sales rep first name, and sales rep last name; sort by order ID; Format the order date as "mm/dd/yx" 9. List the order ID, product ID, product name, category ID, product price, product qty, and extended price (price* qty) for all products sold in order 104. Sort by extended price and format the price as currency. Use the following column headings: QrderID, ProdID, ProdName, CatID, Price, Qty, ExtPrice 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. This project must be completed using an Oracle database with all statements manually typed into an SQL file and run as a batch in Oracle, and the output must be written to a text (.txt) file. Statements and results (feedback from Oracle) must be included in the output file for submission. Directions for downloading the software and for creating and running the SQL file will be posted in Canvas. Project 2 Relational Schema PRODUCT ProdID ProdName ProdCatID ProdPrice ORDERDETAIL OrderD ProdID ProdQty ProdPrice PRODCAT ProdCatID ProdCatName ORDER OrderID OrderDate CustID CUSTOMER CustID CustFName Custl Name CustPhone SalesRepID COMMISSION CommClass CommRate DEPARTMENT DeptiD DeptName SALESREP Sales ReplD SalesRepFName Sales ReplName CommClass DeptID Using Oracle, access the tables you created in Project 2 and complete the following SQL transactions. The text file must contain both your statements and the results from Oracle (this will be the spooled .txt file). All column headings must be shown in their entirety (i.e., points will be deducted if column names are truncated). Insert comment lines at the top of your file containing your full name, course-section number, and project number). In addition, use comment lines to number your statements. Add a cover sheet with your name, course, project, and submission date; and submit at the beginning of class on the date specified in Canvas. Part I. 1. All column headings must show in their entirety--no truncated column headings. 2. Line size should be set at least 125 and no more than 150 to minimize column wrapping. 3. Properly use table aliases and dot notation where applicable. 4. Add a new Customer: CustID T104 7 5. Add a new Product: ProdID 246 CustFName Wes ProdID 618 407 124 Add Order Details using the Order Prodoty INSY 3304 Project 3 ProdName Milwaukee Power Drill ProdID 535 246 610 CustLName, Thomas 1 2 1 6. Add a new Order. Generate the Order by incrementing the max QrderID, by 1. OrderDate CustID 1/28/2022 T104 Add Order Details, using the Order Prodoty 3 1 2 CustPhone 469-555-1215 ProdCatID 2 SalesRenID 22 7. Add a new Order. Generate the Order by incrementing the max OrderID by 1. OrderDate CustID 1/29/2022 S100 Price $179 for the new Order above: ProdPrice Current Price* Current Price* Current Price* Current Price* Current Price* Current Price* for the new Order above: ProdPrice L * Use a nested SELECT statement to get the current price from the PRODUCT table. 8. Change the phone number for Customer B200 to 817-555-8918. L 9. Commit all changes above before proceeding to the next step. Part II. 1. List the first name, last name, sales rep ID, commission class, and commission rate for all Sales Reps. Combine the first and last name into one column (include a space between them). Sort by last name in ascending order, and use the following column headings: SalesRep Name, Sales Rep ID, Commission Class, Commission Rate. 2. List all rows and all columns from QrderDetail; sort by Order ID then by Product ID, both in ascending order; format the Product Price as currency. Use the following column headings: Order ID, Product ID, Qty, Price 3. For all customers, list the customer ID, customer first name, customer last name, and customer phone number, along with the sales rep ID, sales rep first name, and sales rep last name of the sales rep to whom that customer belongs; sort by Customer ID in ascending order; format the phone number as '(###) ###-####' by using concatenation and the SUBSTR function. Use the following column headings: CustID, CustFirstName, CustLastName, CustPhone, SalesRepID SalesRepFirstName. SalesRepLastName. 4. List the department ID, department name, sales rep ID, first name, last name, commission class, and commission rate of the sales rep(s) who earn the highest commission in each department. Use the following column headings: Dept ID. Dept Name, Sales Rep JD. First Name. Last Name, Commission Class, Commission Rate 5. List the product ID, product name, category name, and price for the highest priced product(s) sold in order 100. Show the price formatted as currency, and use the following column headings: Product ID, Product Name, Category, Price. 6. List the department name and the count of sales reps in each department; group by department name, and sort by sales rep count in ascending order. Use the following column headings: Dept Name, Sales Rep Count. 7. List the sales rep ID, first name, last name, and commission rate for each sales rep who earns a commission rate less than or equal to 5%, but do NOT include sales reps who earn 0% commission. Sort by commission rate in descending order. Show the commission rate as a percentage (e.g., 5% instead of .05). Use the following column headings: Sales Rep JD. First Name, Last Name, Commission Rate 8. For each order, list the order ID, order date, customer ID, customer first name, customer last name, sales rep ID, sales rep first name, and sales rep last name; sort by order ID; Format the order date as "mm/dd/yx" 9. List the order ID, product ID, product name, category ID, product price, product qty, and extended price (price* qty) for all products sold in order 104. Sort by extended price and format the price as currency. Use the following column headings: QrderID, ProdID, ProdName, CatID, Price, Qty, ExtPrice 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.
Expert Answer:
Related Book For
Data Modeling and Database Design
ISBN: 978-1285085258
2nd edition
Authors: Narayan S. Umanath, Richard W. Scammel
Posted Date:
Students also viewed these databases questions
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Three employees of the Horizon Distributing Company will receive annual pension payments from the company when they retire. The employees will receive their annual payments for as long as they live....
-
On January 1, 2015, Pruitt Company issued 25,500 shares of its common stock ($2 par) in exchange for 85% of the outstanding common stock of Shah Company. Pruitt's common stock had a fair value of $28...
-
At the instant shown, link AB has an angular velocity AB = 2 rad/s and an angular acceleration AB = 6 rad/s. Determine the acceleration of the pin at C and the angular acceleration of link CB at...
-
What is the free-rider problem? Why does it discourage innovation in environmental protection and the adoption of high environmental standards? Can anything be done to overcome the free-rider problem?
-
The Lubbock plant of Morrils Small Motor Division produces a major subassembly for a 6.0 horsepower motor for lawn mowers. The plant uses a standard costing system for production costing and control....
-
Your friend's father just turned 55 and is thinking of joining CPF LIFE. He tells you that if he sets aside the Basic Retirement Sum (BRS) of $90,500 today, he can expect an approximate monthly...
-
Burnard (1992) sent a questionnaire to a stratified sample of nursing tutors and students in Wales, to study what the tutors and students understood by the term experiential learning. The population...
-
3. With reference to the data and using an AD/AS diagram, assess the likely impact that changes in UK interest rates will on inflation and unemployment. (12 marks) 6% 5% 4% 3% 2% 1% 0% 4 4.25% 2007...
-
= Let f(x) 15. Calculate the following values: f(a) = = f(a+h) = = f(a + h) - f(a) h for h 0
-
CMNS 1140 A4 AND A5: Analysis and Presentation (Assignments are worth 10% each; A4-PDF, Moodle; A5-PPTX x 2, with and without voiceover, Moodle) RESEARCH: CREDIBLE, INFORMATIVE, RELEVANT TO THE...
-
Solve the system of equations graphically. 2x-y=-2 x-3y=-2
-
Polly Woodsides is planning to invest her savings of $100,000 into three stocks: ABC, PQR, and XYZ. For each dollar invested, she can expect to get 4%, 6.5%, and 7.5%, respectively. Her investment...
-
2. Identify a problem (only one problem relating to OB) in an organisation that you have access to (preferably an organisation where at least one group member works or has worked recently. Do not...
-
From the demand equation, Substituting - 0.504p Since Q=p S -1.504 1.269 2.226 for Pg 3 -0.504 1.269 2.226, substituting p Pg 8 = AQ Ap 3 = -0.504p AQ in the formula for the price elasticity of...
-
The activities listed in lines 2125 serve primarily as examples of A) Underappreciated dangers B) Intolerable risks C) Medical priorities D) Policy failures
-
Describe what constitutes an ER Model.
-
Given the Universal Relation Schema URS (A, B, C, D, F, G) and the set of FDs prevailing over URS F {fd1, fd2, fd3, fd4, fd5, fd6}, where: a. Derive a canonical cover of F. b. Derive all the...
-
Display the sum along with a three month moving average of the production of milk across the regions of the 2006-2010 period starting with January, 2006 and ending in December, 2010?
-
Think of an organizationa place of employment, a club, a sports team, a musical group, an academic societythat provided you with a significant work experience, and answer the following questions. 1....
-
What factors determine whether behavior in organizations is ethical or unethical? Divide up into small groups, and each person think of some unethical behaviors or incidents that you have observed in...
-
Now that you understand the nature of OB and management, and the kinds of issues they address, debate the following topic: Team A. The best way to increase organizational effectiveness is to clearly...
Study smarter with the SolutionInn App