consider the following relational database defined below cre

consider the following relational database defined below

create table branch(

branch_name varchar(30)

branch_city varchar(30)

assets numeric(14,2)

primary key(branch_name));

create table customer(

customer_number char(10),

customer_name varchar(30) not null,

customer_city varchar(30),

primary key(customer_number));

create table loan(

loan_number char(10),

branch_name varchar(30),

amount numeric(11,2),

primary key(loan_number),

foreign key(branch_name) references branch);

create table borrower(

customer_number char(10),

loan_number char(10),

primary key(customer_number,loan_number),

foreign key(customer_number) references customer

foreign key(loan_number) references loan));

Express in SQL each of the following queries:

Find the name(s) of the branch(s) that has/have the largest amount of assets (i.e., branches whose amount of assets is equal to or larger than the amount of assets of every other branch).

Find the customer number (i.e., ID) of every customer that shares all of the loans that customer “1234” has. If the customer whose customer number is “1234” has loans “L1” and “L2”, you need to find all customers (including customer “1234”) that share both “L1” and “L2”.

Find the name of each branch that manages more than 1000 loans. (b) (10 points) For each loan whose amount is greater than $1,000,000, show the loan number (i.e., ID) and the number (i.e., count) of customers sharing that loan

Solution

(1)

select branch_name from branch where assets >= (select max(assets) from branch) ;

(2)

##### Creating Intermidiate view for final answer ###############

create view temp as

select customer_number ,loan_number from borrow where loan_number in (select loan_no where customer_no =\'1234\") ;

###### FInal Query ###########

select customer_nember from temp group by customer_number  having count(loan_number) => (select count(loan_number) from temp where customer_number=\'1234\') ;

(3)

with t1 as( select loan_number , count(customer_number) as loan_count from borrower group by loan_number ;),