Q) Consider following database schema, Write the queries using SQL
branch (branch_name, branch_city,
assets)
customer (customer_name, customer_street,
customer_city)
loan (loan_number, branch_name, amount)
borrower (customer_name,
loan_number)
account (account_number, branch_name, balance)
depositor (customer_name,
account_number)
-- a) Find all
customers who have both a loan and an account (eliminate duplicates if exists)
SELECT distinct customer_name from borrower
WHERE customer_name IN (SELECT customer_name FROM
depositor );
-- b) Find all
customers who have a loan or an account or both (eliminate duplicates if
exists)
SELECT customer_name FROM borrower
UNION
SELECT customer_name FROM depositor;
-- c) Find all customers who have a loan but not an account
(eliminate duplicates if exists)
SELECT DISTINCT customer_name FROM borrower
WHERE customer_name NOT IN (SELECT customer_name FROM
depositor );
-- d) Find all
customer who have a loan or an account or both
SELECT customer_name FROM borrower
UNION ALL
SELECT customer_name FROM depositor;
-- e) Find all
customer who have both a loan and an account
SELECT customer_name FROM borrower
WHERE customer_name IN (SELECT customer_name FROM depositor);
----Demonstration
use databasename;
create table BRANCH (
branch_name varchar(20) primary key,
branch_city varchar(20),
assets int
);
create table CUSTOMER (
customer_name varchar(20) primary key,
customer_street varchar(20),
customer_city varchar(20)
);
create table ACCOUNT (
account_number int primary key,
branch_name varchar(20),
amount int,
foreign key (branch_name) references branch(branch_name)
);
create table LOAN (
loan_number int primary key,
branch_name varchar(20),
amount int,
foreign key (branch_name) references branch(branch_name)
);
create table DEPOSITOR (
customer_name varchar(20),
account_number int,
primary key(customer_name, account_number),
foreign key (customer_name) references customer(customer_name),
foreign key (account_number) references account(account_number)
);
create table BORROWER (
customer_name varchar(20),
loan_number int,
primary key(customer_name, loan_number),
foreign key (customer_name) references customer(customer_name),
foreign key (loan_number) references loan(loan_number)
);
create table EMPLOYEE (
employee_name varchar(20),
branch_name varchar(20),
salary int,
primary key(employee_name, branch_name),
foreign key (branch_name) references branch(branch_name)
);
INSERT INTO BRANCH VALUES
("JP Nagar","Bangalore",1000),
("Charchgate","Mumbai",1500),
("Red Hill","Hyderabad",2000),
("Jaya Nagar","Bangalore",5000);
INSERT INTO CUSTOMER VALUES
("c1","s1","Bangalore"),
("c2","s2","Hyderabad"),
("c3","s3","Mumbai"),
("c4","s4","Kolkatta");
INSERT INTO ACCOUNT VALUES
(101,"JP NAGAR",5000),
(102,"Red Hill",3000),
(103,"Charchgate",2000);
INSERT INTO LOAN VALUES
(1,"JP NAGAR",3000),
(2,"Charchgate",1000),
(3,"Red Hill",5000),
(4,"Jaya Nagar",6000);
INSERT INTO DEPOSITOR VALUES
("c1",101),
("c3",103);
INSERT INTO BORROWER VALUES
("c1",1),
("c4",4);
-- Q) Consider following database schema, Write the queries using SQL
-- branch (branch_name, branch_city, assets)
-- customer (customer_name, customer_street, customer_city)
-- loan (loan_number, branch_name, amount)
-- borrower (customer_name, loan_number)
-- account (account_number, branch_name, balance)
-- depositor (customer_name, account_number)
-- a) Find all customers who have both a loan and an account (eliminate duplicates if exists)
SELECT distinct customer_name from borrower
WHERE customer_name IN (SELECT customer_name FROM depositor );
-- b) Find all customers who have a loan or an account or both (eliminate duplicates if exists)
SELECT customer_name FROM borrower
UNION
SELECT customer_name FROM depositor;
-- c) Find all customers who have a loan but not an account (eliminate duplicates if exists)
SELECT DISTINCT customer_name FROM borrower
WHERE customer_name NOT IN (SELECT customer_name FROM depositor );
-- d) Find all customer who have a loan or an account or both
SELECT customer_name FROM borrower
UNION ALL
SELECT customer_name FROM depositor;
-- e) Find all customer who have both a loan and an account
SELECT customer_name FROM borrower
WHERE customer_name IN (SELECT customer_name FROM depositor );