RDBMS Assignment_1

Assignment:1

Q-1: Banking management system

Deposit (actno,cno,Bno,amount,adate)
Customer(cno,cname,city)
Branch(Bno,Bname,city)
Borrow(Loanno,cno,bno,amount)

Deposit (actno,cno,Bno,amount,adate)

create table dips

(        actno varchar2(5) primary key,

          cno varchar2(5) references cust,

          bno varchar2(5) references branch,

          amount number,

          adate date

);

Customer(cno,cname,city)

create table cust

(        cno varchar2(5) primary key,

          cname varchar2(20),

          city varchar2(20)

);

Branch(Bno,Bname,city)

create table branch

(        bno varchar2(5) primary key,

          bname varchar2(20),

          city varchar2(20)

);

Borrow(Loanno,cno,bno,amount)

create table borrow

(        loanno varchar2(10) primary key,

          cno varchar2(5) references cust,

          bno varchar2(5) references branch,

          amount number

);

Create all tables with constraints insert 5 records for each tables

insert into branch values('1','vrce','surat');

insert into branch values('2','ak_road','bombay');

insert into branch values('3','varachha','delhi');

insert into branch values('4','karolabagh','vip_road');

insert into branch values('5','kapodra','anand');

insert into cust values('101','anil','surat');

insert into cust values('102','janak','nagpur');

insert into cust values('103','ravi','baroda');

insert into cust values('104','dhaval','bombay');

insert into cust values('105','mayur','surat');

insert into dips values('111','103','1',5000,'1-dec-95');

insert into dips values('222','105','3',3000,'11-jul-96');

insert into dips values('333','102','5',8000,'21-jan-97');

insert into dips values('444','104','2',4000,'16-nov-96');

insert into dips values('555','101','4',8500,'25-apr-94');

insert into borrow values('206','103','5',5000);

insert into borrow values('204','105','3',4000);

insert into borrow values('202','102','4',9500);

insert into borrow values('203','104','1',3000);

insert into borrow values('201','103','2',10000);

Q-1.List all data from table deposit

select * from dips;

Q-2.List all data from table borrow

select * from borrow;

Q-3.List all data from table customer

select * from cust;

Q-4.Give account number and amount of depositors

Select actno,amount from dips;

Q-5.Give cname and account number of depositors.

select actno,cname from dips d,cust c where d.cno=c.cno;

Q-6.Give names of borrower.

select cname from cust c, borrow b where b.cno=c.cno;

Q-7.Give names of customers living in city Nagpur.

select cname from cust where city=’surat’;

Q-8.Give name of depositors having amount greater than4000.

select cname from cust c,dips d where d.cno=c.cno and amount>2000;

Q-9.Give account date of customer anil.

select adate from dips d,cust c where d.cno=c.cno and cname=’vishal’;

Q-10.Give names of all branches located in city Bombay.

select bname from branch where city=’surat’;

Q-11.Give name of borrower having loan no 206

select cname from cust c,borrow b where c.cno=b.cno and loanno=’1’;

Q-12.Give names of depositors having account at vrce.

select cname from cust c,branch b,dips d where c.cno=d.cno and b.bno=d.bno and b.city=’surat’;

Q-13.Give names of all branches located in city Delhi.

select bname from branch where city=’ahemdabad’;

Q-14.Give names of customers who opened account after date‘1-12-95’.

select  cname from cust c,dips d where c.cno=d.cno and adate>’1-dec-1995’;

Q-15.Give name of the city where branch karolbagh is located.

select city from branch where bname=’baroda’;

Q-2: College Management

University(ID,Name,Address,City)

College(ID,Name,Address,City)

Department(ID,Type)

Student(ID,RollNo,Name,Address,City,PhNo)

Course(ID,Type,Semester)

Student(ID,Name,Address,City,PhNo,Designation)

create table University

(U_id number primary key,

U_name varchar(10) not null,

U_add varchar(10),

U_city varchar (10));

create table College

(C_id number primary key,

C_name varchar(10) not null,

C_add varchar(10),

C_city varchar (10),

C_state varchar(10));

create table Department

(D_id number primary key,

D_type varchar(10) not null);

create table Student

(S_id number primary key,

R_no number not null,

S_name varchar(10) not null,

S_add varchar(10),

S_city varchar (10),

P_no number(10));

create table Course

(D_id number primary key,

D_type varchar(10) not null,

SEM number not null);

create table Professor

(P_id number primary key,

P_name varchar(10) not null,

P_add varchar(10),

P_city varchar (10),

P_no number(10),

P_desi varchar(10) not null);

insert into University

values

(1,'VNSGU','Vesu','Surat');

insert into University

values

(2,'UKA','ABC','Navsari');

insert into University

values

(3,'Maliba','EFG','Mumbai');

insert into University

values

(4,'Parul','HIJ','Delhi');

insert into University

values

(5,'DU','KLM','Gadhvi');

insert into COllege

values

(1,'SVP','ABC','Surat','ABC');

insert into COllege

values

(2,'CBP','EFG','Gadhvi','EFG');

insert into COllege

values

(3,'BM','HIJ','Mumbai','HIJ');

insert into COllege

values

(4,'SDJ','KLM','Delhi','KLM');

insert into COllege

values

(5,'MSM','DEF','Surat','DEF');

insert into Department

values

(1,'ABC');

insert into Department

values

(2,'EFG');

insert into Department

values

(3,'HIJ');

insert into Department

values

(4,'KLM');

insert into Department

values

(5,'NOP');

insert into Student

values

(1,1.1,'Gaurav','ABC','Surat',1236547896);

insert into Student

values

(2,1.2,'Mohini','EFG','Navsari',4125784196);

insert into Student

values

(3,1.3,'Chinmay','HIJ','Delhi',5421347896);

insert into Student

values

(4,1.4,'Meet','KLM','Mumbai',4521547896);

insert into Student

values

(5,1.5,'Dhruv','NOP','Gadhvi',7896541232);

insert into Course

values

(1,'NOP',1);

insert into Course

values

(2,'EFG',2);

insert into Course

values

(3,'HIJ',3);

insert into Course

values

(4,'KLM',4);

insert into Course

values

(5,'NOP',5);

insert into Professor

values

(1,'Gaurav','ABC','Surat',1236547896,'TS');

insert into Professor

values

(2,'Mohini','EFG','Navsari',4125784196,'Principle');

insert into Professor

values

(3,'Chinmay','HIJ','Delhi',5421347896,'HR');

insert into Professor

values

(4,'Meet','KLM','Mumbai',4521547896,'TS');

insert into Professor

values

(5,'Dhruv','NOP','Gadhvi',7896541232,'HOD');

Qeries:

3. select * from College;

4. select * from Course;

5. select P_name,P_add,P_city from Professor;

6. select * from University;

7. select R_no,S_name,S_add,S_city from Student;

8. update Student set S_name='Raj' where S_name='Rajesh';

9. update Professor set P_desi='MCA' where P_desi='BCA';

10. update Course set D_type='BCOM' where D_type='BBA';

11. alter table Student add (E_mail varchar(15));

12. alter table Course add (D_desc varchar(15));

13. alter table Professor add (P_desc_depart varchar(10));

                                      Q-3 Banking management system

Table: Branch(code, name, address, city)

Customer(code, name, address, city, state)
Loan(number, type, amount)
Account(number, type, balance)
Employee(id, name, type, address, city, phno, designation)

Table: Branch(code, name, address, city)

drop table branch;

Create table branch

(

          Code number primary key,

          name varchar(20),

          address varchar(20),

          city varchar(20)

);

Customer(code, name, address, city, state)

Create table customer

(

          code number primary key,

name varchar(20),

address varchar(20),

city varchar(20),

state varchar(7)

);

Loan(number, type, amount)

Create table loan

(

          no number primary key,

          type varchar2(20),

          amount number(7)

);

Account(number, type, balance)

Create table account

(

          no number primary key,

          type varchar(20),

balance number(7)

);

Employee(id, name, type, address, city, phno, designation)

Create table employee

(

          id number primary key,

          name varchar(15),

          type varchar(10),

          address varchar(10),

          city varchar(10),

          phno number,

          designation varchar(15)

);

Insert  5 records for each tables

insert into branch values(1,'a.k.road','a.k.road','surat');

insert into branch values(2,'daman','daman','surat');

insert into branch values(3,'dabholi','dabholi','surat');

insert into branch values(4,'faridabad','faridabad','surat');

insert into branch values(5,'varachha','varachha','surat');

insert into cust values(11,'janak','hirabagh','surat');

insert into cust values(12,'smita','kargil chok','bombay');

insert into cust values(13,'dhaval','katargam','valsad');

insert into cust values(14,'rutva','hirabagh','surat');

insert into cust values(15,'sunil','varachha','surat');

insert into loan values(101,'vehicle',96000);

insert into loan values(102,'home',9000000);

insert into loan values(103,'personal',999999);

insert into loan values(104,'shop',900000);

insert into loan values(105,'bike',9999);

insert into account values(201,'chacking',100000);

insert into account values(202,'saving',50000);

insert into account values(203,'current',5000000);

insert into account values(204,'personal',1000000);

insert into account values(205,'chacking',100000);

insert into employee values(301,'viral','manager','katargam','surat',9874563210,'bca');

insert into employee values(302,'piyush','clark','hirabugh','surat',9999911223,'bba');

insert into employee values(303,'anisha','accountant','varachha','surat',9632587410,'bcom');

insert into employee values(304,'kushik','assistant','amroli','surat',9012365478,'mca');

insert into employee values(305,'ronak','manager','kamrage','surat',9563214780,'bca');

Q-3. View records from branch table.

Select * from branch;

Q-4. View records from loan table.

Select * from loan;

Q-5. View records name, address, city from customer table.

Select name,address,city from customer;

Q-6. Display records from employee table.

Select * from employee;

Q-7. Display records rollno, name, address, city from customer table whose name is sunil.

Select rollno,name,address,city from customer where name=’vishal’;

Q-8. Change name tejash instead of kamal in employee table.

update student set name=’rajesh’ where name=’dhaval’;

Q-9. Change type saving instead of checking in account table.

update account set type=’saving’ where type=’checking’;

Q-10.Change course type home instead of vehicle in loan table.

update loan set type=’home’ where type=‘health’;

Q-11.Add field email_id in customer table.

alter table customer add (email_id varchar(15));

Q-12.Add field  description field in loan table

alter table loan add (description varchar(15));

Q-13.Add field  desc_acct  field in account table

alter table account add (desc_acct varchar(20));

Q-14.Change fieldname designation instead of  post in employee table

alter table employee rename column designation to post;

Q-15.Remove record from brach table.

delete from branch;

Q-16.Remove record of  customers whose located in valsad city

delete from customer where city=’anad’;

Q-17.Remove record of employee whose post is manager.

delete from employee where post=’employee’;

Q-18.Destroy table of branch table.

drop table branch;

Q-19.Delete structure of loan table.

drop table loan;

Q-20.Delete all columns in employee table.

drop table employee;