How to write Queries in Database mangament system???

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=A.CID);

2. Write a query to retrieve customer having account and loan in different branch
   SELECT CNAME FROM CUSTOMER
   JOIN BANK ON BANK.CID = CUSTOMER.CID
   WHERE BANK.LOCATION != CUSTOMER.CADDRESS;

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 CUSTOMER.CID =
   LOAN.CID JOIN BANK ON
   CUSTOMER.CID = LOAN.CID
   WHERE LOAN.LOAN_TYPE = 'HOME' AND LOAN.AMOUNT >
   2500000 AND BANK.LOCATION = 'HYDEREBAD' AND
   BANK.BNAME = 'INDIAN';

4. Write a query to retrieve customer having only loan in Indian bank
   and accountin SBI branch residing in Hyderabad
   SELECT DISTINCT CNAME FROM
   CUSTOMER JOIN LOAN ON
   CUSTOMER.CID = LOAN.CID JOIN
   BANK ON CUSTOMER.CID = LOAN.CID
   JOIN ACCOUNT ON CUSTOMER.CID = ACCOUNT.CID
   WHERE CUSTOMER.CADDRESS = 'HYDEREBAD' AND (BANK.BNAME =
   'INDIAN' OR BANK.BNAME = '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
   CUSTOMER.CID = LOAN.CID JOIN
   BANK ON CUSTOMER.CID = LOAN.CID
   JOIN ACCOUNT ON CUSTOMER.CID = ACCOUNT.CID
   WHERE LOAN.AMOUNT < ACCOUNT.AMOUNT AND BANK.BNAME =
   'ICICI' AND BANK.LOCATION = 'HYDEREBAD';

6. Write a query to retrieve customer having education loan branchwise
   SELECT DISTINCT CNAME FROM
   CUSTOMER JOIN LOAN ON
   CUSTOMER.CID = LOAN.CID JOIN
   BANK ON CUSTOMER.CID = LOAN.CID
   JOIN ACCOUNT ON CUSTOMER.CID =
   ACCOUNT.CID WHERE LOAN.LOAN_TYPE =
   'EDUCATION'
   GROUP BY BANK.LOCATION;

7. Write a query to retrieve customer having personal loan in any
   bank locatedat bangalore
   SELECT CUSTOMER.CNAME FROM CUSTOMER INNER JOIN LOAN ON
   CUSTOMER.CID
   = LOAN.CID
   WHERE CUSTOMER.CADDRESS = '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
   CUSTOMER.CID = LOAN.CID JOIN
   BANK ON CUSTOMER.CID = LOAN.CID
   JOIN ACCOUNT ON CUSTOMER.CID = ACCOUNT.CID
   WHERE BANK.BNAME != 'ICICI' AND BANK.CID = CUSTOMER.CID;

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
   CUSTOMER.CID = LOAN.CID JOIN
   BANK ON CUSTOMER.CID = BANK.CID
   JOIN ACCOUNT ON CUSTOMER.CID =
   ACCOUNT.CID WHERE (1 <
   (SELECT COUNT(*)
   FROM BANK
   WHERE CID=CUSTOMER.CID)) AND (BANK.CID =
   CUSTOMER.CID) AND (CUSTOMER.CID != ACCOUNT.CID);

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
CUSTOMER.CID = LOAN.CID JOIN
BANK ON CUSTOMER.CID = BANK.CID
JOIN ACCOUNT ON CUSTOMER.CID =
ACCOUNT.CID WHERE (1 <
(SELECT COUNT(*)
FROM ACCOUNT
WHERE CID=CUSTOMER.CID)) AND (CUSTOMER.CID =
ACCOUNT.CID) AND (LOAN.LOAN_TYPE = 'HOME') AND
(CUSTOMER.CID = LOAN.CID);

11.Write a query to retrieve customer having educational loan and
list themas students
SELECT DISTINCT CUSTOMER.CNAME AS STUDENT FROM CUSTOMER
INNER JOIN LOAN ON CUSTOMER.CID = LOAN.CID
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
CUSTOMER.CID = LOAN.CID JOIN
BANK ON CUSTOMER.CID = BANK.CID
JOIN ACCOUNT ON CUSTOMER.CID =
ACCOUNT.CID WHERE (1 <
(SELECT COUNT()
FROM ACCOUNT
WHERE CID=CUSTOMER.CID)) AND (CUSTOMER.CID =
ACCOUNT.CID) AND (BANK.LOCATION = 'BANGALORE' OR
'VELLORE') AND (1 =
(SELECT COUNT()
FROM BANK
WHERE CID=CUSTOMER.CID));

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
CUSTOMER.CNO = LOAN.CNO JOIN
BANK ON CUSTOMER.CNO =
BANK.CNO
JOIN ACCOUNT ON CUSTOMER.CNO = ACCOUNT.CNO
WHERE ( LOAN.AMOUNT> (SELECT AVG(AMOUNT) AS AVGAMT FROM
BANK WHERE BANK.BNAME='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
CUSTOMER.CID = LOAN.CID AND LNO <> NULL INNER JOIN ACCOUNT
ON ACCOUNT.CID = LOAN.CID 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 LOAN.CID =
ACCOUNT.CID UNION
SELECT * FROM LOAN
RIGHT JOIN ACCOUNT ON LOAN.CID = ACCOUNT.CID;

17.Apply left outer join for loan and customer table
SELECT *
FROM
LOAN
LEFT OUTER JOIN
CUSTOMER ON LOAN.CID =
CUSTOMER.CID;

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

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

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

Posted with STEMGeeks