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 customer number (i.e., ID) of every customer that does not have any loan (use a set operation).

Find the customer number (i.e., ID) of every customer that does not have any loan (use either an in clause or a not in clause).

Find the customer number (i.e., ID) of every customer that does not have any loan (use an outer join).

Solution

1) SELCT customer_number, loan_id FROM branch MINUS (SELECT customer_number, id FROM loan)

In order to do MINUS, both tables should have same number of columns. So, assuming customer_number exists in loan table also. MINUS operator excludes all the common rows in the first table.

2) SELECT customer_number FROM branch where loan_number NOT IN (SELECT id FROM loan)

Here, the second query gives all the entries and we are doing NOT IN, so that it will be excluded

3) SELECT t1.customer_number FROM branch t1 LEFT JOIN Loan t2 ON t1.customer_number = t2.id WHERE t2.id IS NULL

Here, we are using left outer join that gives all the rows from branch table regardless of whether the entry exists in second table or not. WHERE clause ensures customer doesn\'t have loan i.e. loan.id IS NULL.