assignment4

CREATE TABLE Account (

    AccountNo VARCHAR2(10) PRIMARY KEY,

    BranchName VARCHAR2(50),

    AmountRs NUMBER(10)

);

 

INSERT INTO Account VALUES (‘A1’, ‘Kolkata’ , 50000);

INSERT INTO Account VALUES (‘A2’, ‘Howrah’  , 40000);

INSERT INTO Account VALUES (‘A3’, ‘Howrah’  , 40000);

INSERT INTO Account VALUES (‘A4’, ‘Kolkata’ , 20000);

INSERT INTO Account VALUES (‘A5’, ‘Durgapur’, 30000);

 

select * from Account;

 

 

–a) Create a view that will show the branch name and total amount of that branch. The name of the view will be Account1.

CREATE VIEW Account1 AS

SELECT BranchName, SUM(AmountRs) AS TotalAmount

FROM Account

GROUP BY BranchName;

 

SELECT * FROM Account1;

 

 

–b) Select the branch names having total amount greater than 50,000

–I) using Accountl view 

 

SELECT BranchName

FROM Account1

WHERE TotalAmount > 50000;

 

 

–II) without using the view.

 

SELECT BranchName, SUM(AmountRs) AS TotalAmount

FROM Account

GROUP BY BranchName

HAVING SUM(AmountRs) > 50000;

 

 

–c) Create an index table named Branch from the Account table on Branch Name attribute and then display the details of Branch table.

 

CREATE INDEX Branch

ON Account (BranchName);

 

SELECT *

FROM USER_INDEXES

WHERE INDEX_NAME = ‘BRANCH’;