Program5-Write a program to calculate the simple interest in PL/SQL

Method-1:
==========================================================
Input-let p=1000 ,r=10 and t=10
Output- Simple interest (si) =1000
Calculation -Let Principal(P),Rate (r% per annum) and Time = t years Then
Simple Interest, si = P*r*t/100
===========================================================
Code-
SQL> set serveroutput on;
SQL> declare
  2  p number;
  3  r number;
  4  t number;
  5  si number;
  6  Begin
  7  p :=1000;
  8  r :=10;
  9  t :=10;
 10  si:=(p*r*t)/100;
 11  Dbms_output.put_line('The Simple Interest='||si);
 12  End;
 13  /
The Simple Interest=1000

PL/SQL procedure successfully completed.
======================================================================

Method-2:
======================================================================
Input-Ask user to enter the principal amount, rate of interest and time take and store the result into third variable si .
Output- simple interest
Operation-Simple Interest, si = P*r*t/100
========================================
Code-
SQL> declare
  2  p number;
  3  r number;
  4  t number;
  5  si number;
  6  Begin
  7  p :=&p;
  8  r :=&r;
  9  t :=&t;
 10  si :=(p*r*t)/100;
 11  Dbms_output.put_line('The Simple Interest='||si);
 12  End;
 13  /
Enter value for p: 1000
old   7: p :=&p;
new   7: p :=1000;
Enter value for r: 10
old   8: r :=&r;
new   8: r :=10;
Enter value for t: 10
old   9: t :=&t;
new   9: t :=10;
The Simple Interest=1000

PL/SQL procedure successfully completed.

SQL>

Program4-Write a program to find the area of rectangle in PL/SQL

Method-1:
=================
Suppose rectangle have length and breadth .so area of rectangle is multiplication of length and width.
Input-length=20 meters and width=10 meters
Output-200 meter square
Operation- area of rectangle=length*width

Code-
SQL> set serveroutput on;
SQL> declare
  2      length  number;
  3      width  number;
  4      Begin
  5         length  :=20;
  6         width :=10;
  7     Dbms_output.put_line('Area of the Rectangle='||length*width);
  8     End;
  9  /
Area of the Rectangle=200

PL/SQL procedure successfully completed.
========================================================================

Method-2:
=======================
Input-Take two variables length and width  and store the result into third variable result.
Output- Area of the rectangle
Operation- result=length*width

SQL> declare
  2      length  number;
  3      width  number;
  4     result number;
  5      Begin
  6         length  :=20;
  7         width :=10;
  8        result:=length*width;
  9     Dbms_output.put_line('Area of the Rectangle='||result);
 10     End;
 11  /
Area of the Rectangle=200

PL/SQL procedure successfully completed.

SQL>
===============================================================
Method-3:
====================
Input-Ask user to enter two number  length and width  and store the result into third variable rec_area.
Output- rec_area
Operation-Area of rectangle

Code-
SQL> declare
  2      length  number;
  3      width  number;
  4      rec_area number;
  5      Begin
  6         length :=&length ;
  7         width :=&width;
  8      rec_area :=length*width;
  9
 10     Dbms_output.put_line('Area of the Rectangle='||rec_area);
 11     End;
 12  /
Enter value for length: 20
old   6:        length :=&length ;
new   6:        length :=20 ;
Enter value for width: 5
old   7:        width :=&width;
new   7:        width :=5;
Area of the Rectangle=100

PL/SQL procedure successfully completed.

SQL>

Program3- Advance concept : Write a program to add two number using the function and procedure.

Write a program to add two number using the function  named as find_sum  and procedure.

Method-1

Input-Suppose two input values are n1 and n2
Output-Add of two numbers in third variable

Code-
SQL> create or replace function find_sum
  2  (n1 in number, n2 in number)
  3  return number
  4  as
  5  v_sum number;
  6  begin
  7  v_sum := n1+n2;
  8  return v_sum;
  9  end;
 10  /

Function created.

--Perform the sum using function find_sum

SQL> select find_sum (10, 30) sum from dual;

      SUM
----------
        40

SQL> select find_sum (10, 30)  from dual;

FIND_SUM(10,30)
---------------
             40

SQL> select find_sum (10, 30) "Sum of two numbers" from dual;

Sum of two numbers
------------------
                40


=================================================================
Method-2 :Procedure
==================================================================

SQL> create or replace procedure find_sum_procedure
  2  (n1 in number, n2 in number)
  3  as
  4  v_sum number;
  5  begin
  6  v_sum := n1+n2;
  7  DBMS_OUTPUT.PUT_LINE('Sum of two numbers='||v_sum);
  8  end;
  9  /

Procedure created.

SQL> execute find_sum_procedure(10,20);

Sum of two numbers=30

PL/SQL procedure successfully completed.

SQL>

Program2-Write a program to add two number in PL/SQL

This program can be solved in many ways. So Let us understand how the add is performed
Method-1:
Suppose we have two constant value 2 and 3 .So we need the addition operator(+) to add these two numbers
Input-2 and 3
Output-5
Operation- addition so use addition operator (+)

Code-
SQL> set serveroutput on
SQL>
SQL>
SQL> Begin
  2  Dbms_output.put_line(2+3);
  3  End;
  4  /
5

PL/SQL procedure successfully completed.

SQL>


Method-2:
Suppose you want to add two values taken at run time
Input-Take two variable and store two value into it.Assume two variable are n1 and n2.
Output- addition a+b
Operation- addition (+)
Code-
SQL> declare
  2  n1 number;
  3  n2 number;
  4  Begin
  5  n1 :=2;
  6  n2 :=3;
  7  Dbms_output.put_line(n1+n2);
  8  End;
  9  /
5

PL/SQL procedure successfully completed.

SQL>



Method-3:
Take two number n1 and n2 and store it into third variable result.
Input-Take two variable and store the result into third variable result.
Output- Result of addition
Operation- addition (+)

SQL> declare
  2    n1 number;
  3    n2 number;
  4    result number;
  5    Begin
  6     n1 :=2;
  7     n2 :=3;
  8     result:=n1+n2;
  9    Dbms_output.put_line(result);
 10    End;
 11    /
5

PL/SQL procedure successfully completed.

SQL>
Method-4:
Ask user to enter two number n1 and n2 and store it into third variable result.
Input-Take two variable and store the result into third variable result.
Output- Result of addition
Operation- addition (+)

Code-
SQL> declare
  2    n1 number;
  3    n2 number;
  4    result number;
  5    Begin
  6     n1 :=&n1;
  7     n2 :=&n2;
  8     result:=n1+n2;
  9    Dbms_output.put_line(result);
 10    End;
 11    /
Enter value for n1: 10
old   6:    n1 :=&n1;
new   6:    n1 :=10;
Enter value for n2: 20
old   7:    n2 :=&n2;
new   7:    n2 :=20;
30

PL/SQL procedure successfully completed.

SQL>

Note- This program can be solved in many ways .I have taken from very basic to often used method.
I hope you can get the idea how to approach the problem to solve in real-time in PL/SQL.

SQL-Primary key and foreign key Relation/Parent-child relationship between tables/ On delete cascade/foreign key constraint with Example

SQL-Primary key and foreign key Relation/Parent-child relationship between tables/ On delete cascade/foreign key constraint with Example

Suppose we have two table customer and supplier
CREATE TABLE supplier
( supplier_id numeric(10) not null,
 supplier_name varchar2(50) not null,
 contact_name varchar2(50),
 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

insert into supplier values(1,'Sarat','Chand');
insert into supplier values(2,'Sartaj','Ahmed');
insert into supplier values(3,'Saral','Chand');


CREATE TABLE products
( product_id numeric(10) not null,
 supplier_id numeric(10) not null,
 CONSTRAINT fk_supplier
   FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id)
  ON DELETE CASCADE
);

insert into products values(101,1);
insert into products values(102,2);
insert into products values(103,3);

--Check/verify the entered data into the table using below command

select * from products;
select * from supplier ;

--not Allowed to add the product into the supplier table because the supplier id is not exist in the parent table name supplier

insert into products values(103,4);

--Error Message
--ORA-02291: integrity constraint (HR.FK_SUPPLIER) violated - parent key not found

So we can say that the table relation between table is established

--The data can be deleted from the supplier table because it is parent table but cannot delete the data into child table
--delete the data from supplier table whose supplier_id is 1;
delete from supplier where supplier_id=1;

delete from products where supplier_id=1;
--check the constraints
select * from user_cons_columns;
select * from user_constraints;

Program1- Write a program to print the Hello Message to the terminal

Solution-often we start writing the first program as hello Message.


So let us write the first hello program using various methods.

Method-1:Using Anonymous block 
Code-
SQL> begin
  2  dbms_output.put_line('HELLO');
  3  end;
  4  /
HELLO
PL/SQL procedure successfully completed.
SQL>

Method-2:Using PROCEDURE 
code-
SQL> SET SERVEROUTPUT ON
SQL> CREATE OR REPLACE PROCEDURE hello
  2  AS
  3  BEGIN
  4     dbms_output.put_line('Hello World!');
  5  END;
  6  /

Procedure created.

SQL>

Note- we can execute the procedure in two way -
Use SET SERVEROUTPUT ON  command to view the output on terminal-
1.
SQL> EXECUTE hello;
Hello World!

PL/SQL procedure successfully completed.
SQL>

2.
SQL> BEGIN
  2    hello;
  3  END;
  4  /
Hello World!

PL/SQL procedure successfully completed.
SQL>

Oracle-Concatenation Operator(||)

Concatenation Operator(||)

Concatenation operator is used to concatenate the column or string to another column or string and it is represented by two vertical bar (||).

SQL Statement-

select 'The employee Name '||Ename||' and employee id is '||empno As   "Employee Information" from emp

Result-
             Employee Information
The employee Name KING and employee id is 7839
The employee Name BLAKE and employee id is 7698
The employee Name CLARK and employee id is 7782
The employee Name JONES and employee id is 7566
The employee Name SCOTT and employee id is 7788
The employee Name FORD and employee id is 7902
The employee Name SMITH and employee id is 7369