PL / SQL Unnamed PL SQL code block Assignment

Problem Statement:

Unnamed PL/SQL code block: Use of Control structure and Exception handling is

mandatory.

Suggested Problem statement:

Consider Tables:

1. Borrower (Roll_no, Name, DateofIssue, NameofBook, Status)

2. Fine (Roll_no,Date,Amt)

 Accept Roll_no and NameofBook from user.

 Check the number of days (from date of issue).

 If days are between 15 to 30 then fine amount will be Rs 5per day.

 If no. of days>30, per day fine will be Rs 50 per day and for days less than 30, Rs. 5 per

day.

 After submitting the book, status will change from I to R.

 If condition of fine is true, then details will be stored into fine table.

 Also handles the exception by named exception handler or user define exception handler.

OR

Write a PL/SQL code block to calculate the area of a circle for a value of radius varying from 5 to

9. Store the radius and the corresponding values of calculated area in an empty table named areas,

consisting of two columns, radius and area

Now the first question comes to our mind is how to implement this code? 

1. Which Software I have to use for implementation, if I don't have that software how to install it in my computer so that I can implement the same.

Ans: To implement this assignment we need following installation or setup

1. Choose Operating System: Windows

2. Open below link in new tab inside the browser to download free oracle database setup

https://www.oracle.com/database/free/get-started/?source=ubi-lightbox

3. Windows appear like this here you have to click on "Link to Download"

4. Once the file downloaded successfully you have to unzip or extract it in some where then go to the folder and double click on Setup file of type Application.

5. Select the destination folder or by default it shows the installation path keep it same as it is and click on Install button.

6. It will ask for password keep it simple like 123456 and just click on next button.

7. In between it will ask for java update just click on Allow button. after that it will take 5 to 6 minutes for installation.

8. If your installation done successfully following message appears on the screen.

Now required software installation is done. what next?

2. How do we use this software for implementation of above PL/ SQL assignment.

2.1 To connect the install database, first open your command prompt in Run as Administrator mode.

       and type following command 

       > sqlplus / as sysdab

2.2 In the above command prompt if we carefully observe then we come to know that we have successfully connected the Oracle free pluggable database container CDS$ROOT. as a system database administrator so we have all rights which administrator have.

if you want to see tables from the database you have to write following query:

SQL > select table_name from user_tables;

2.3 Now if you are having a knowledge of PL/SQL code then you can directly write code in the following manner inside the command prompt.

2.4 First basic PL/SQL unnamed code for understanding.

2.5 How to use for(...) loop inside the PL/SQL code block simple demo for the same. Here we have used DML insert command it means first of all we have to create table circle_data with two columns radius and area.

2.6 Complete code as per problem statement is as follows.

Write a PL/SQL code block to calculate the area of a circle for a value of radius varying from 5 to

9. Store the radius and the corresponding values of calculated area in an empty table named areas,

consisting of two columns, radius and area