Cycle sheet 3

Queries Involving Joins, aggregate, group by-having, any, all, at least, at most
exactly, except ,In Not In functions.
Consider the below table insert appropriate values for attribute relevant to query and display
the resultant output.
Customer ( cname,cno primary key,cage,csex,caddress,c_mobileno,c_mailid)
Account ( Ano primary key,cid foreign key from customer
reference,amount,acc_type,opening_date)
Loan (lno primary key, cid foreign key from customer
reference,amount,opening_date,due_date,loan_type( (home and personal age >
25),(education<age =25),interest_rate)
Bank ( bname, Bid primary key, cnum foreign key customer table reference ,location, assets)
1. Write a query to retrieve customer having loan in more than one branch
SELECT CNAME FROM CUSTOMER A
WHERE 1 <
(SELECT COUNT(*)
FROM BANK
WHERE CID=[Link]);

2. Write a query to retrieve customer having account and loan in different branch
SELECT CNAME FROM CUSTOMER
JOIN BANK ON [Link] = [Link]
WHERE [Link] != [Link];
3. Write a query to retrieve number of customer availed home loan of amount > 25
lakhs at Indian bank located in Hyderabad.

SELECT CNAME FROM CUSTOMER

JOIN LOAN ON [Link] = [Link]
JOIN BANK ON [Link] = [Link]
WHERE LOAN.LOAN_TYPE = 'HOME' AND [Link] > 2500000 AND
[Link] = 'HYDEREBAD' AND [Link] = 'INDIAN';

4. Write a query to retrieve customer having only loan in Indian bank and account in
SBI branch residing in Hyderabad

SELECT DISTINCT CNAME FROM CUSTOMER

JOIN LOAN ON [Link] = [Link]
JOIN BANK ON [Link] = [Link]
JOIN ACCOUNT ON [Link] = [Link]
WHERE [Link] = 'HYDEREBAD' AND ([Link] = 'INDIAN' OR
[Link] = 'SBI');
5. Write a query to retrieve customer having loan amount lesser than account
amount at ICICI bank Hyderabad branch.

SELECT DISTINCT CNAME FROM CUSTOMER

JOIN LOAN ON [Link] = [Link]
JOIN BANK ON [Link] = [Link]
JOIN ACCOUNT ON [Link] = [Link]
WHERE [Link] < [Link] AND [Link] = 'ICICI' AND
[Link] = 'HYDEREBAD';

6. Write a query to retrieve customer having education loan branchwise

SELECT DISTINCT CNAME FROM CUSTOMER

JOIN LOAN ON [Link] = [Link]
JOIN BANK ON [Link] = [Link]
JOIN ACCOUNT ON [Link] = [Link]
WHERE LOAN.LOAN_TYPE = 'EDUCATION'
GROUP BY [Link];
7. Write a query to retrieve customer having personal loan in any bank located at
bangalore

SELECT [Link] FROM CUSTOMER INNER JOIN LOAN ON [Link]

= [Link]
WHERE [Link] = 'BANGALORE' AND LOAN.LOAN_TYPE = 'PERSONAL';

8. Write a query to retrieve customer having loan and account except ICICI bank.

SELECT DISTINCT CNAME FROM CUSTOMER

JOIN LOAN ON [Link] = [Link]
JOIN BANK ON [Link] = [Link]
JOIN ACCOUNT ON [Link] = [Link]
WHERE [Link] != 'ICICI' AND [Link] = [Link];
9. Write a query to retrieve customer having loan in more than one branch and not
account in any of the branch
SELECT DISTINCT CNAME FROM CUSTOMER
JOIN LOAN ON [Link] = [Link]
JOIN BANK ON [Link] = [Link]
JOIN ACCOUNT ON [Link] = [Link]
WHERE (1 <
(SELECT COUNT(*)
FROM BANK
WHERE CID=[Link])) AND ([Link] = [Link]) AND
([Link] != [Link]);

10. Write a query to retrieve customer having loan home in one branch and account
more than one branch.
SELECT DISTINCT CNAME FROM CUSTOMER
JOIN LOAN ON [Link] = [Link]
JOIN BANK ON [Link] = [Link]
JOIN ACCOUNT ON [Link] = [Link]
WHERE (1 <
(SELECT COUNT(*)
FROM ACCOUNT
WHERE CID=[Link])) AND ([Link] = [Link]) AND
(LOAN.LOAN_TYPE = 'HOME') AND ([Link] = [Link]);
11. Write a query to retrieve customer having educational loan and list them as
students
SELECT DISTINCT [Link] AS STUDENT FROM CUSTOMER INNER JOIN
LOAN ON [Link] = [Link]
WHERE LOAN.LOAN_TYPE = 'EDUCATION' ;

12. Write a query to retrieve customer having account in more than one branch and
only one loan in at Vellore branch and Bangalore branch
SELECT DISTINCT CNAME FROM CUSTOMER
JOIN LOAN ON [Link] = [Link]
JOIN BANK ON [Link] = [Link]
JOIN ACCOUNT ON [Link] = [Link]
WHERE (1 <
(SELECT COUNT(*)
FROM ACCOUNT
WHERE CID=[Link])) AND ([Link] = [Link]) AND
([Link] = 'BANGALORE' OR 'VELLORE') AND (1 =
(SELECT COUNT(*)
FROM BANK
WHERE CID=[Link]));
13. Write a query to retrieve customer having loan amount > avg account amount at
SBI Hyderabad branch
SELECT DISTINCT CNAME FROM CUSTOMER
JOIN LOAN ON [Link] = [Link]
JOIN BANK ON [Link] = [Link]
JOIN ACCOUNT ON [Link] = [Link]

WHERE ( [Link]> (SELECT AVG(AMOUNT) AS AVGAMT FROM BANK WHERE

[Link]='SBI’ AND LOCATION='VELLORE'));
14. Delete the customer availed loan but not maintaining account in any of the bank
DELETE CUSTOMER FROM CUSTOMER INNER JOIN LOAN ON [Link] =
[Link] AND LNO <> NULL INNER JOIN ACCOUNT ON [Link] = [Link]
AND ANO = NULL;

15. Update the amount 10% for the long term loan holders
UPDATE LOAN
SET
INTEREST_RATE = 3
WHERE
DUE_DATE = '2041-10-16' ;

BEFORE
 AFTER

16. Apply full outer join for loan and account table
SELECT * FROM LOAN
LEFT JOIN ACCOUNT ON [Link] = [Link]
UNION
SELECT * FROM LOAN
RIGHT JOIN ACCOUNT ON [Link] = [Link];
17. Apply left outer join for loan and customer table
SELECT *
FROM LOAN
LEFT OUTER JOIN CUSTOMER
ON [Link] = [Link];

18. Display the customer having maximum account comparatively all loan amount
SELECT * FROM CUSTOMER WHERE CID=(SELECT CID FROM LOAN WHERE
[Link]=(SELECT MAX([Link]) FROM LOAN ));

19. Display the customer having one loan and atleast one account in any bank
SELECT DISTINCT CNAME FROM CUSTOMER
JOIN LOAN ON [Link] = [Link]
 JOIN BANK ON [Link] = [Link]
JOIN ACCOUNT ON [Link] = [Link]
WHERE (1 =
(SELECT COUNT(*)
FROM LOAN
WHERE CID=[Link])) AND (1 <
(SELECT COUNT(*)
FROM ACCOUNT
WHERE CID=[Link])) AND ([Link] = [Link]) AND
([Link] = [Link]);

20. Display the customer who is not having either account or loan in any branch.
SELECT DISTINCT CNAME FROM CUSTOMER
WHERE ([Link] NOT IN ((SELECT CID FROM LOAN)) AND ([Link]
NOT IN (SELECT CID FROM ACCOUNT)));