create table branch(branch_name varchar(30) primary key, branch_city varchar(30), assets varchar(60) ); create table customer(ID_number integer primary key, customer_name varchar(30), customer_street varchar(40), customer_city varchar(40) ); create table loan(loan_number integer primary key, branch_name varchar(30),amount integer, foreign key(branch_name) references branch(branch_name) ); create table borrower(ID_number integer primary key, loan_number integer , foreign key(loan_number) references loan(loan_number) ); create table account(account_number integer primary key, branch_name varchar(30), balance integer , foreign key(branch_name) references branch(branch_name) ); create table depositor(ID_number integer primary key, account_number int , foreign key(account_number) references account(account_number) ); SELECT * FROM branch, customer,loan,borrower, account, depositor; Write a query to find the ID and customer name of each customer at the bank who only has a loan at the bank, and no account. b. Write a query to find the ID and customer name of each customer who lives on the same street and in the same city as customer ‘12345’. Include customer ‘12345’ in your query results. c. Write a query to find the name of each branch that has at least one customer who has an account in the bank and who lives in “Harrison”. d. Write a query to find each customer who has an account at every branch located in “Brooklyn”.
create table branch(branch_name varchar(30) primary key, branch_city varchar(30), assets varchar(60) );
create table customer(ID_number integer primary key, customer_name varchar(30), customer_street varchar(40), customer_city varchar(40) );
create table loan(loan_number integer primary key, branch_name varchar(30),amount integer, foreign key(branch_name) references branch(branch_name) );
create table borrower(ID_number integer primary key, loan_number integer , foreign key(loan_number) references loan(loan_number) );
create table account(account_number integer primary key, branch_name varchar(30), balance integer , foreign key(branch_name)
references branch(branch_name) );
create table depositor(ID_number integer primary key, account_number int , foreign key(account_number) references account(account_number) );
SELECT * FROM branch, customer,loan,borrower, account, depositor;
Write a query to find the ID and customer name of each customer at the bank who only
has a loan at the bank, and no account.
b. Write a query to find the ID and customer name of each customer who lives on the same
street and in the same city as customer ‘12345’. Include customer ‘12345’ in your query
results.
c. Write a query to find the name of each branch that has at least one customer who has an
account in the bank and who lives in “Harrison”.
d. Write a query to find each customer who has an account at every branch located in
“Brooklyn”.
3. Consider the bank
out the following action: If an account is deleted, then write a trigger to delete the
dependent tuple(s) from the depositor table for every owner of the deleted account. Note
that there may be jointly-owned bank accounts. In other words, you are to write a trigger
that performs the exact action of an ON DELETE CASCADE clause of a FOREIGN
KEY CONSTRAINT. You must submit both your trigger function definition, and your
trigger definition. You must name your trigger function
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
