gs gs114 Practical Questions Subjective Solved Past Paper No.1

create table account (account_number varchar(15) not null unique,
branch_name varchar(15) not null,
balance number not null,
primary key(account_number));

create table branch
(branch_name varchar(15) not null unique,
branch_city varchar(15) not null,
assets number not null,
primary key(branch_name));

create table customer
(customer_name varchar(15) not null unique,
customer_street varchar(12) not null,
customer_city varchar(15) not null,
primary key(customer_name));

create table loan
(loan_number varchar(15) not null unique,
branch_name varchar(15) not null,
amount number not null,
primary key(loan_number));

create table depositor
(customer_name varchar(15) not null,
account_number varchar(15) not null,
primary key(customer_name, account_number),
foreign key(account_number) references account(account_number),
foreign key(customer_name) references customer(customer_name));

create table borrower
(customer_name varchar(15) not null,
loan_number varchar(15) not null,
primary key(customer_name, loan_number),
foreign key(customer_name) references customer(customer_name),
foreign key(loan_number) references loan(loan_number));

select branch_name, assets from branch;

select account_number from account natural join branch where branch_city=’Brooklyn’;

select * from loan where amount>1000;

select account_number from account where branch_name=’Perryridge’ and balance < 1000;

select count(account_number) from account where balance > 700 and balance < 900;

update branch set assets = 340000000 where branch_name=’Perryridge’;

update account set branch_name=’Downtown’ where branch_name=’Perryridge’; update loan set branch_name=’Downtown’ where branch_name = ‘Perryridge’;

update account set balance = case
when account_number=’A-101′ then balance-100
when account_number=’A-215′ then balance+100
else balance
end;

delete from account where branch_name=’Perryridge’; delete from branch where branch_name=’Perryridge’;
delete from loan where branch_name=’Perryridge’;

delete from borrower where loan_number in (select loan_number from loan where amount<1000);
delete from loan where amount<1000;

delete from depositor where account_number IN (select account_number from account where branch_name=’Downtown’);

delete from account where branch_name=’Downtown’;

delete from borrower where loan_number IN
(select loan_number from loan where branch_name=’Downtown’);

delete from loan where branch_name=’Downtown’;

alter table customer add phoneNo numeric(10);

ALTER TABLE branch ALTER COLUMN branch_city type varchar(20);

ALTER TABLE customer drop phoneNo;

select customer_name,loan_number,amount from borrower natural join loan;

select customer_name,loan_number,amount from depositor natural join loan where loan.branch_name=’Perryridge’;

select customer_name,loan_number as loan_id,amount from depositor natural join loan;

select branch_name from branch where assets > (select min(assets) from branch where branch_city = ‘Brooklyn’);

select customer_name from customer where customer_street like ‘%Main%’;

select * from loan order by amount desc, loan_number asc;

select customer_name from depositor union all
select customer_name from borrower;

select customer_name from depositor intersect all
select customer_name from borrower;

select avg(balance) from account where branch_name=’Perryridge’;

select avg(balance),branch_name from account natural join depositor
group by branch_name;

select count(Distinct customer_name),branch_name from account natural join depositor
group by branch_name;

select branch_name from account group by branch_name
having avg(balance)>1200

select count(distinct branch_name) from branch;

select loan_number from loan where amount is null.

select customer_name from loan natural join borrower where branch_name = ‘Perryridge’ and
customer_name in
(select customer_name from account natural join depositor
where branch_name = ‘Perryridge’);

select customer_name from loan natural join borrower where customer_name not in
(select customer_name from account natural join depositor);

select customer_name from loan natural join borrower where branch_name = ‘Perryridge’ and customer_name not in
(‘Smith’, ‘Jones’);

select branch_name from branch where assets > some(select assets from branch
where branch_city = ‘Brooklyn’);

select branch_name from branch where assets > some(select assets from branch
where branch_city = ‘Brooklyn’);

select distinct customer_name from borrower as b1 where exists (select customer_name from depositor as b2
where b1.customer_name = b2.customer_name);

select customer_name from account as b1 natural join depositor
where exists (select * from branch
where b1.branch_name = ‘Downtown’
and b1.branch_name=’Brighton’);

select customer_name from account natural join depositor where branch_name = ‘Perryridge’ group by customer_name
having count(account_number)<=1;

select avg(balance),branch_name from account where balance > 1200 group by branch_name;