Consider the banking example we used in lecture: branch (branch-name, branch-cit

Question

Consider the banking example we used in lecture: branch (branch-name, branch-city, assets) customer (customer-name, customer-street, customer-city) account (account-number, branch-name, balance) loan (loan-number, branch-name, amount) depositor (customer-name, account-number) borrower (customer-name, loan-number) Write and execute the following queries in SQL: 1. Find the names of customers who live in cities with names ending in "on" 2. Find the names and cities of customers who have an account at "Downtown" branch. 3. Print in descending order the names of customer who have a loan balance between 1000 and 2000

Explanation / Answer

----------------------------------create table-----------------------------------------------
create table account
(
account_number char(5) not null primary key,
branch_name varchar(100),
balance double
);
create table branch
(
branch_name varchar(100) not null primary key,
branch_city varchar(100),
assets double
);
create table customer
(
customer_name varchar(200) not null primary key,
customer_street varchar(200),
customer_city varchar(100)
);
create table loan
(
loan_number varchar(5) not null primary key,
branch_name varchar(100),
amount double
);
create table borrower
(
customer_name varchar(200) not null,
loan_number varchar(5) not null,
primary key(customer_name, loan_number)
);
create table depositor
(
customer_name varchar(200) not null,
account_number char(5) not null,
primary key(customer_name, account_number)
);

---------------------------insert-------------------------
insert into account values('101', 'Downtown', 500);
insert into account values('102', 'Perryridge', 400);
insert into account values('201', 'Brighton', 900);
insert into account values('215', 'Mianus', 700);
insert into account values('217', 'Brighton', 750);
insert into account values('222', 'Redwood', 700);
insert into account values('305', 'Round Hill', 350);

insert into branch values('Brighton', 'Brooklyn', 100000);
insert into branch values('Downtown', 'Brooklyn', 900000);
insert into branch values('Mianus', 'Horseneck', 40000);
insert into branch values('North Town', 'Rye', 70000);
insert into branch values('Perryridge', 'Horseneck', 170000);
insert into branch values('Round Hill', 'Horseneck', 800000);

insert into customer values('Nick', 'Spring', 'Pittsfield');
insert into customer values('Brooks', 'Senator', 'Brooklyn');
insert into customer values('Hayes', 'Main', 'Harrison');
insert into customer values('Johnson', 'Alma', 'Palo Alto');
insert into customer values('Jones', 'Main', 'Harrison');
insert into customer values('Lindsay', 'Park', 'Pittsfield');
insert into customer values('Smiths', 'North', 'Rye');
insert into customer values('lohan', 'Putnam', 'Stamford');
insert into customer values('Williams', 'Nasau', 'Princeton');

insert into depositor values('Hayes', '102');
insert into depositor values('Johnson', '102');
insert into depositor values('Johnson', '201');
insert into depositor values('Jones', '217');
insert into depositor values('Lindsay', '222');
insert into depositor values('Smith', '215');
insert into depositor values('Turner', '305');

insert into loan values('Lno11', 'Round Hill', 900);
insert into loan values('Lno14', 'Downtown', 1500);
insert into loan values('Lno15', 'Perryridge', 1500);
insert into loan values('Lno16', 'Perryridge', 1300);
insert into loan values('Lno17', 'Downtown', 1000);
insert into loan values('Lno23', 'Redwood', 2000);
insert into loan values('Lno93', 'Mianus', 500);

insert into borrower values('Curry', 'Lno93');
insert into borrower values('Hayes', 'Lno15');
insert into borrower values('Jackson', 'Lno14');
insert into borrower values('Jones', 'Lno17');
insert into borrower values('Smith', 'Lno11');
insert into borrower values('Smith', 'Lno23');
insert into borrower values('Williams', 'Lno17');
---------------------solution-------------------------------------------
Ans:-
branch (branch-name, branch-city, assets)
customer (customer-name, customer-street, customer-city)
account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)

1)select customer-name from customer
where customer-city like'%on';
  
2)1)select c.customer-name,c.customer-city from customer c,depositor d,account a,branch b
where c.customer-name=d.customer-name
and d.account-number=a.account-number
and a.branch-name=b.branch-name
and b.branch-name='Downtown';
  
3)select customer.customer-name from customer,borrower,loan
where customer.customer-name=borrower.customer-name
and borrower.loan-number=loan.loan-number
and loan.amount between 1000 and 2000
order by customer.customer-name desc ;
  

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.