Write a SQL query for: Display customers that have neither loan nor account. cre

Question

Write a SQL query for:

Display customers that have neither loan nor account.

create table account

(account_number varchar(15) not null,

branch_name varchar(15) not null,

balance number not null,

primary key(account_number));

create table branch

(branch_name varchar(15) not null,

branch_city varchar(15) not null,

assets number not null,

primary key(branch_name));

create table customer

(customer_name varchar(15) not null,

customer_street varchar(12) not null,

customer_city varchar(15) not null,

primary key(customer_name));

create table loan

(loan_number varchar(15) not null,

branch_name varchar(15) not null,

amount number not null,

primary key(loan_number));

create table depositor

(customer_name varchar(15) not null,

account_number varchar(15) not null,

primary key(customer_name, account_number),

foreign key(account_number) references account(account_number),

foreign key(customer_name) references customer(customer_name));

create table borrower

(customer_name varchar(15) not null,

loan_number varchar(15) not null,

primary key(customer_name, loan_number),

foreign key(customer_name) references customer(customer_name),

foreign key(loan_number) references loan(loan_number));

Here is the solution I have:

I was wondering if there was an alternate solution.

Explanation / Answer

Alternate Way:

SELECT custmer_name FROM customer

MINUS

SELECT customer_name FROM customer c,borrower b, depositor d WHERE b.customer_name=c.custmer_name

OR

d.customer_name=c.customer name;

In the above query

i) SELECT custmer_name FROM customer --------(1)

in the above(1) takes the all the customer name from custmer

ii)

SELECT customer_name FROM customer c,borrower b, depositor d WHERE   b.customer_name=c.custmer_name

OR

d.customer_name=c.customer name -----------(2)

in the above (2) takes the customername from the tables customer depositor, and borrower who are borrower and who has an deposit(account)

So (1) MINUS (2) displays the required result

The SQL MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The MINUS operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.

The MINUS operator is not supported in all SQL databases. It can used in databases such as Oracle.For databases such as SQL Server, PostgreSQL, and SQLite, use the EXCEPT operator to perform this type of query.

The OR operator displays a record if any of the conditions separated by OR is TRUE.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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