bank.oracle

CREATION OF TABLES:

SQL> create table customer
	(
    	customer_id number(10) primary key,
    	customername varchar2(20),
    	contactno number(10),
    	address varchar2(20),
    	aadharnumber number(20),
    	emailid varchar2(30),
    	);
Table created.

SQL> create table account
	(
     	 account_id number(10) primary key,
    	 type varchar2(10),
    	 opendate date,
   	 closedate date,
     	 interest number(10),
   	 balance number(10));
     	 foreign key(customer_id) references customer(customer_id)
	);

Table created.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INSERTING VALUES:


SQL> insert into Account values(101,'saving',TO_DATE('01-01-2018','dd-mm-yyyy'),TO_DATE('01-01-2030','dd-mm-yyyy'),10,54321,201);

1 row created.

SQL> insert into Account values(102,'current',TO_DATE('02-01-2018','dd-mm-yyyy'),TO_DATE('02-01-2030','dd-mm-yyyy'),5,45321,203);

1 row created.

SQL>

SQL> insert into Account values(103,'salary',TO_DATE('03-01-2018','dd-mm-yyyy'),TO_DATE('03-01-2030','dd-mm-yyyy'),10,32451,201);

1 row created.

SQL>  insert into customer values(201,'raja',9688161234,'erode',330953639542,'kiruthiik15@gmail.com');

1 row created.

SQL> insert into customer values(202,'kiruthiik',8667495530,'coimbatore',330965458789,'kiruthiik750@gmail.com');

1 row created.

SQL>  insert into customer values(203,'hari',8074456458,'kerala',354852526454,'hariguru@gmail.com');

1 row created.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TO DISPLAY TABLES:

SQL> select * from account;

ACCOUNT_ID TYPE       OPENDATE  CLOSEDATE   INTEREST    BALANCE	  CUSTOMER_ID
---------- ---------- --------- --------- ---------- -------------------------
       101 saving     01-JAN-18 01-JAN-30         10      54321     201
       102 current    02-JAN-18 02-JAN-30          5      45321     203
       103 salary     03-JAN-18 03-JAN-30         10      32451     201


SQL> select * from customer;

CUSTOMER_ID CUSTOMERNAME          CONTACTNO ADDRESS              AADHARNUMBER   EMAILID
----------- -------------------- ---------- -------------------- ---------------------------------------

        201 raja                 9688161234 erode                  3.3095E+11   kiruthiik15@gmail.com


        202 kiruthiik            8667495530 coimbatore             3.3097E+11   kiruthiik750@gmail.com


        203 hari                 8074456458 kerala                 3.5485E+11   hariguru@gmail.com

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3)  Query 1:

Write an SQL query to display the account details alongwith balance for all the customers.


SQL> select account.*,customer.customer_id,customer.customername from account,customer where customer.customer_id=account.customer_id;

ACCOUNT_ID TYPE       OPENDATE  CLOSEDATE   INTEREST    BALANCE CUSTOMER_ID
---------- ---------- --------- --------- ---------- ---------- -----------
CUSTOMERNAME
--------------------
       101 saving     01-JAN-18 01-JAN-30         10      54321         201
raja

       102 current    02-JAN-18 02-JAN-30          5      45321         202
kiruthiik

       103 salary     03-JAN-18 03-JAN-30         10      32451         203
hari
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

4) Query 2:

Write an SQL query to display the details of the accounts opened for the given date range.


SQL>  select * from account where opendate between TO_DATE('31-12-2017','dd-mm-yyyy') and TO_DATE('04-01-2018','dd-mm-yyyy');

ACCOUNT_ID TYPE       OPENDATE  CLOSEDATE   INTEREST    BALANCE
---------- ---------- --------- --------- ---------- ----------
       101 saving     01-JAN-18 01-JAN-30         10      54321
       102 current    02-JAN-18 02-JAN-30          5      45321
       103 salary     03-JAN-18 03-JAN-30         10      32451

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
5) Write a procedure to display the account details of the accounts having maximum and minimum balance.

SQL> !vi displaydetails.sql

create or replace procedure displaydetails
is
cursor c is select * from account where balance=(select min(balance) from account);
cursor d is select * from account where balance=(select max(balance) from account);
a c% rowtype;
b d% rowtype;
BEGIN
open c;
fetch c into a;
dbms_output.put_line('***************************************');
dbms_output.put_line('Account datails for Maximum balance');
dbms_output.put_line('accountid: '||a.account_id);
dbms_output.put_line('accountType: '||a.type);
dbms_output.put_line('Opendate: '||a.opendate);
dbms_output.put_line('closedate: '||a.closedate);
dbms_output.put_line('interest is: '||a.interest);
dbms_output.put_line('balance: '||a.balance);
close c;
open d;
fetch d into b;
dbms_output.put_line('***************************************');
dbms_output.put_line('Account datails for Minimum balance');
dbms_output.put_line('accountid: '||b.account_id);
dbms_output.put_line('accountType: '||b.type);
dbms_output.put_line('Opendate: '||b.opendate);
dbms_output.put_line('closedate: '||b.closedate);
dbms_output.put_line('interest is: '||b.interest);
dbms_output.put_line('balance: '||b.balance);
dbms_output.put_line('***************************************');
close d;
end;
/
show errors;

SQL> @displaydetails.sql

Procedure created.

No errors.
SQL> exec displaydetails
***************************************
Account datails for Maximum balance
accountid: 103
accountType: salary
Opendate: 03-JAN-18
closedate: 03-JAN-30
interest is: 10
balance: 32451
***************************************
Account datails for Minimum balance
accountid: 101
accountType: saving
Opendate: 01-JAN-18
closedate: 01-JAN-30
interest is: 10
balance: 54321
***************************************

PL/SQL procedure successfully completed.