CMIS-420 Homework #3
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 <table_name>; 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.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
Our Services
Custom Writings Help is a Quality-Oriented Company in Online Writing as a result of hiring exceptional professionals to execute clients' tasks.
Essays
At Custom Writings Help,We understand the struggle of research paper writing, and that is why at Custom WritingS Help, we are all out to help you. We pride ourselves on having a team of clinical writers. The stringent and rigorous vetting process ensures that only the 'BEST' Writers are chosen for the job. We have highly qualified Ph.D. and MA writers working with us; we equally offer these experienced writers specific bonuses and incentives to make them deliver highly original, unique, and informative content at reasonably low prices.
Admissions
Thesis Writing Service
Worlwide, Many Masters Students are struggling with Thesis Completion. A thesis is likely to be the longest and most challenging piece of work a student has ever completed. However, unlike essays and other assignments, a student can pick a particular interest topic and work on their initiative. Fortunately, we are there for you. At Custom Writings Help, you are assured of an authentic, imaginative, informative, linguistically great, and advantageous thesis that adheres to all your needs. So, why continue considering different writers when you have discovered the best in the field?
Editing
Custom Papers
Not a single student can avoid writing custom papers. However, a total lack of experience, skills, and time makes it very hard to produce a superb writing piece. Therefore, if you are seeking professional help, turn to us. Our specialized and experienced writers compose a variety of model papers, including custom essays, college term papers, research papers, book reports, MBA essays, executive summaries, dissertations, Ph.D. theses, admission essays, and research proposals for college and university students at any level.
Coursework
Essay Writing
Most of the students disregard the critical principles of essay writing and compose papers below sensible guidelines. Therefore, with Custom Writings Help, one should not worry about his/her essay. Our Writers compose informative and engaging content on all complexities and topics. We write meaningful and smart essays while prioritizing all aspects that bring about a good grade, such as impeccable grammar, proper structure, zero-plagiarism, and conformance to guidelines.
Coursework
Coourse Work Writing
Don't let the seemingly never-ending onslaught of writing assignments get you down. If you are looking where to get course work assistance online, the writers at Custom Writings Help are here to assist you with all of your writing needs. We undertake to unique delivery of papers that meet the professor's requirements. The content is proofread, edited, and checked plagiarism before submission to customers. No matter how big or small your work is, we will deliver on time. Try US Now! !
Coursework
Dissertation Writing Service
High-Quality Dissertation Writing Services are rare. They require Ph.D. academicians – not easily found. However, are an exception. The years, time, and resources we have invested in the dissertation world has given us a competitive advantage over others. Choose to come to Custom Writings Help; You will find perfect Ph.D. consultants who have written hundreds of dissertations theses ready to help you. Let our dissertation-writing services help you craft your dissertation, for you are assured we will give you the results.