From this schema: CREATE TABLE branch ( ID int(10) ,branch_name varchar(25) NOT NULL , branch_city varchar(15), assets vrachar(20), PRIMARY KEY (ID ,branch_name) ); CREATE TABLE customer ( ID int(10) , customer_name varchar(25), customer_street vrchar(30), customer_city varchar(20), PRIMARY KEY (ID) ); CREATE TABLE loan ( loan_number int(15) , branch_name varchar (25) , amount double(10,2) CHECK (amount >1000), PRIMARY KEY (loan_number), FOREIGN KEY( branch_name) REFERENCES branch(branch_name) ON DELETE CASCADE ); CREATE TABLE borrower ( ID int(20) , loan_number int(15), PRIMARY KEY (ID), FORIEGN KEY (loan_number) REFERENCES loan ( loan_number) ON UPDATE CASCADE ); CREATE TABLE account ( ID int(20) ,account_number int(20) , branch_name varchar(25) , balance double(10,2) DEFAULT 0.00 , PRIMARY KEY (ID, account_number), FOREIGN KEY(branch_name) REFERENCES branch(branch_name)); CREATE TABLE depositor ( ID int(20) , account_number int(20), PRIMARY KEY(ID ), FOREIGN KEY (account_number) REFERENCES account(account_number)); Answer the following questions: a. 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’. 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”.
From this schema:
CREATE TABLE branch ( ID int(10) ,branch_name varchar(25) NOT NULL , branch_city varchar(15), assets vrachar(20), PRIMARY KEY (ID ,branch_name) );
CREATE TABLE customer ( ID int(10) , customer_name varchar(25), customer_street vrchar(30), customer_city varchar(20), PRIMARY KEY (ID) );
CREATE TABLE loan ( loan_number int(15) , branch_name varchar (25) , amount double(10,2) CHECK (amount >1000), PRIMARY KEY (loan_number), FOREIGN KEY( branch_name) REFERENCES branch(branch_name) ON DELETE CASCADE );
CREATE TABLE borrower ( ID int(20) , loan_number int(15), PRIMARY KEY (ID), FORIEGN KEY (loan_number) REFERENCES loan ( loan_number) ON UPDATE CASCADE );
CREATE TABLE account ( ID int(20) ,account_number int(20) , branch_name varchar(25) , balance double(10,2) DEFAULT 0.00 , PRIMARY KEY (ID, account_number), FOREIGN KEY(branch_name) REFERENCES branch(branch_name));
CREATE TABLE depositor ( ID int(20) , account_number int(20), PRIMARY KEY(ID ), FOREIGN KEY (account_number) REFERENCES account(account_number));
Answer the following questions:
a. 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’.
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”.
Trending now
This is a popular solution!
Step by step
Solved in 4 steps
