Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave
SQL Stored Procedures, Triggers, and Scripts for Car Sales Database
Answered

Stored Procedure for Finding Number of Cars and Total Spend Amount by Customers

For these questions you must use the tables created in your own schema, because you DO NOT have privileges to INSERT, UPDATE or DELETE values in SI schema. Write a stored procedure that can be used to find the number of cars and total amount of money a customer has spent on all cars they have bought. This procedure will be used in question 2, but it can be used in any circumstance to find the number of cars and the total amount spent by one or more customers. Only use parameters to pass/receive data. Donot use DBMS_OUTPUT.PUT_LINE to display data. Use a copy of the SI schema in your own schema Write an anonymous block that would prompt the user to enter a city and uses the procedure created previously (in #1) to display the number of cars and the total amount spent on cars from all the customers from that city. You can use a cursor or a collection to store all the customers from the city and loop through and call the procedure within to display the number of cars and the money spent by the customers. Keep in mind that each city may have been stored in the database in different case spellings (for example OAKVILLE, oakville or Oakville are to be treated as identical). Create a trigger for car table that will reject inserting or updating values for carlistprice that are either negative or larger than 250000.Write a script with three commands. An INSERT statement inserting a new record in car table. You will have to deal with referential integrity constraints caused by purchinvno, custname etc. Insert values for all columns in the car table and make sure that the value for carlistprice is between 1 and 249999.99. Write an UPDATE statement and update the carlistprice to a value that is either negative or greater than 250000. Follow by a COMMIT. Execute the script and show the response from the server

support
close