Reference no: EM13780885
In this assignment you will be performing some additional queries against your Online Vehicle Sales (OVS), Inc. online transaction processing (OLTP) database and also creating two simple PL/SQL user-defined functions and a simple PL/SQL stored procedure.
This assignment is based on the fully populated tables from Project #1 so it assumes you've completed all work for that assignment.
You can perform this assignment based on a database on Nova or any other Oracle system you wish, but you must use the Oracle RDBMS.
You should use one or more SQL script files to complete this assignment. Your script files should contain all your SQL and PL/SQL code. Do NOT submit your SQL script files. Doing so may result in confusion and will result in lost points.
Everything for this assignment must be in a single file. If you are using SQL*Plus you must put all your SQL, PL/SQL, and results together in a single SPOOL file. If you are using SQL Developer or other GUI, put all your screen snapshots in a single file for both your SQL statements and PL/SQL as they executed and the results. Failure to include all your SQL, PL/SQL, and all your results along with them will result in lost points.
Do NOT submit additional files as this only complicates the grading, and will result in lost points.
Here are the specific assignment steps. In order to earn full credit you must keep your steps in order, number your steps, and put everything in a single file.
1) Execute SELECT COUNT(*) FROM ; statements for all 6 of your OVS, Inc. OLTP tables. You should have at least the following counts: CUSTOMERS table - 100 rows, VEHICLES table - 50 rows, SALESPERSONS table - 10 rows, FINANCING_PLANS - 5 rows, SALES table - 200 rows, and SALES_FINANCINGS table - 200 rows.
2) Via a single SELECT query display the sale ID and plan ID of all sales in the last 30 days, sorted by sale ID. Show the SQL statement you used and the results returned by Oracle from executing your SQL statement right after the statement.
3) Via a single SELECT query display the zip code, make, and count with the largest total car purchases (there may be a tie with two or more). Show the SQL statement you used and the results returned by Oracle from executing your SQL statement right after the statement.
4) Develop a PL/SQL user-defined function ZIP_CODE_SALES that returns the total sales for a zip code when the zip code is supplied as an input parameter. You may use any of your zip codes you wish. Show the PL/SQL statements in your function, the execution of your function, and the results returned.
5) Develop a PL/SQL user-defined function ZIP_CODE_SALES_WINNER that returns the zip code with the largest total car purchases. Since there can be a tie with two or more zip codes, ensure that the lowest numeric zip code is returned. Your returned value should match the results of Step #3 above. Show the PL/SQL statements in your function, the execution of your function, and the results returned.
6) Develop a PL/SQL stored procedure ZIP_CODE_MAKE_SALES_WINNER that returns the zip code and make with the largest total car purchases. Since there can be a tie with two or more zip codes and makes, ensure that the lowest combined numeric zip code and character make combination is returned. Your returned value should match the results of Step #3 above. Show the PL/SQL statements in your stored procedure, the execution of your stored procedure, and the results returned.
9700762_1420_script_hw2.txt
9700762_2cmis420_project1_spool_0.txt
9700777_3homework3spool_3_0.txt
9700717_1project_1sql_script_2_1.txt
|
Provide a bulleted plan of action for solutions you suggest
: Provide a bulleted plan of action for the solutions you suggest. Finally discuss the best solutions in your estimation.
|
|
Assignment on global operations management
: Global Operations Management Assignment.As part of the management team for PPQ Parts manufacturing company.
|
|
Evaluation of each carriers overarching business strategy
: Conclude with an evaluation of each carriers overarching business strategy. Explain your assessment of the most profitable strategy - Upon reading the paper, a potential passenger should have the information needed to choose between the two airli..
|
|
Respond to the given post
: There are steps that should be taken that can produce results with minimum errors. These steps, known as the intelligence cycle, include planning, collection, processing, analysis, and dissemination
|
|
Develop a pl/sql user-defined function
: Develop a PL/SQL user-defined function ZIP_CODE_SALES_WINNER that returns the zip code with the largest total car purchases. Since there can be a tie with two or more zip codes, ensure that the lowest numeric zip code is returned.
|
|
Find the fourier transform of f(t)
: Find the Fourier Transform of f(t),Plot (with axis labels) the frequency spectrum for the continuous signal. (both magnitude and phase)What is the bandwidth of this signal in rad/s and Hz?
|
|
Ethical issues related to documenting
: What specific legal issues can arise in documenting assessment, testing, and diagnosis?
|
|
Compare and contrast the management information systems
: Achieve work with MIS to develop and recommend workable solutions to specific business problems
|
|
Ethical alternatives for resolving this dilemma
: How might Irina's age and parents' involvement in the referral affect how Dr. Matthews can resolve the dilemma? How might the state law on treatment of minors and HIPAA rule on access of guardians to a minor's health care record influence Dr. Matt..
|