DataManagement-Homework 5 Solved

PL/SQL Questions:


2.            Run the “set define on;” command to allow substitution variables.  Update the previous statementbut this time prompt the user to enter a customer ID and dynamically use that input to pull the count of reservations for the customer ID the user entered.  Also update the output as well to include the customer ID and the count of reservations like so:

e.g. When you prompt the user and they enter 100002, it should return something like “The customers with customer ID: 100002, has placed more than 15 reservations.” If you enter a customer ID = 100003, it should say something like “The customers with customer ID: 100003, has placed 15 or fewer reservations.”. Hint: you’ll need to adjust the SELECT to pull in customer ID with the count which will create a new to aggregate (i.e. group) data.

3.            Write a script that uses an anonymous block of PL/SQL code that attempts to insert a newcustomer into the customer table. Just utilize the customer_id_seq to assign the customer_id. Make up your own data. Only fill in the fields that require a value. Use a column list to complete the insert statement. Also commit after the data has been inserted. If the insert is successful, the PL/SQL code should display this message:

 

If the update is unsuccessful, the procedure should display this message:

 

4.            Write a script that uses an anonymous block of PL/SQL code that uses a bulk collect to capture alist of available features that begin with the letter P. The rows in this result set should be sorted by feature name. Then, the code should display a string variable for each feature and its feature.  NOTE: We’re to just going to assign a number for the feature based on it’s place in the list and not its actual feature_id. Your output should look like this:

 

5.            Write a more complex version of the previous script that uses a cursor to capture the locationname, city, and feature name.  Then output the rows like the shortened sample list below which is sorted by location name, city, and feature name. ***3 bonus points will be given if you can prompt the user for a city and then change the outputted list  to output the location name, city, and feature name for that city.***

Sample Output: (Only first four lines are shown)

Sample Output: (Only first four lines are shown)

Balcones Canyonlands Cabins in Marble Falls has feature: Business Center

Balcones Canyonlands Cabins in Marble Falls has feature: Free Breakfast

Balcones Canyonlands Cabins in Marble Falls has feature: Full Kitchen

 Balcones Canyonlands Cabins in Marble Falls has feature: Parking Included …

6.            Take your script from problem 3 and change it from an anonymous block of PL/SQL code to anamed procedure called insert_customer that allows you to insert a new customer by passing in customer ID, first name, last name, email, phone, address_line_1, city, state, and zip. Make sure that you are still using the sequence to generate the customer ID.  Handle exceptions generally by rolling back the transaction “when others” occurs. Once your procedure compiles correctly, test it with the following calls.

 

7.            Create a function called hold_count that returned the total number of rooms that a customer hasreserved when it is passed a customer_id. Once you have compiled your function, test it using the following select statement: