assignment-test3 queries sql

Create following Tables:

DEPOSITE

COLUMN NAME DATA TYPE SIZE

Account No varchar 5

Customer No. varchar 5

Branch No. varchar 5

Amount numeric 10 with 2 precision

Creation Date Date

CUSTOMER



Customer Name varchar 30

City varchar 20

BORROW


Loan No varchar 5



Amount Numeric 10 with 2 precision

BRANCH


Branch Name varchar 20

City varchar 20

Insert Following records into tables:

DEPOSITE

Account No. Customer No. Branch No. Amount Creation Date

Ac101 C1001 B101 5000 21-dec-2009

Ac102 C1002 B102 4000 1-jan-2010

Ac103 C1003 B103 42563 8-jan-2010

Ac104 C1004 B104 25415 23-feb-2010

Ac105 C1005 B105 1256.25 5-march-2010

Ac106 C1006 B106 1258.32 18 –april-2010

Ac107 C1007 B107 12563.21 23-april-2010

Ac108 C1008 B108 1256.24 6-may-2010

Ac109 C1009 B109 159322.25 22-jun-2010

Ac110 C1010 B110 658.25 1-jul-2010

BRANCH Branch No. Branch Name City

B101 Palasia Indore

B102 M.G. Road Indore

B103 Bhawarkua Indore

B104 Andheri Mumbai

B105 Juhu Mumbai

B106 Kal papal Bhopal

B107 Kalani nagar Ujjain

B108 Karol bagh Dewas

B109 Airport Chennai

B110 Juna raigarh Palitana

CUSTOMER

Customer No. Customer Name City

C1001 Anil Indore

C1002 Sunil Indore

C1003 Mehul Indore

C1004 Madhuri Mumbai

C1005 Pramod Mumbai

C1006 Sandeep Bhopal

C1007 Shivani Ujjain

C1008 Kranti Dewas

C1009 Powai Goa

C1010 DeepKamal Gujrat

BORROW Loan No. Customer No. Branch No. Amount

L2001 C1001 B101 50000

L2006 C1002 B101 60000

L3001 C1003 B107 80000

L3112 C1004 B109 55000

L3752 C1005 B104 42000

L4852 C1006 B106 86250

Perform Following Queries:

1. List all data from table BRANCH.

2. List all data from table DEPOSITE.

3. List all data from table CUSTOMER

4. List all data from table BORROW.

5. Give Account no and Amount of depositors.

6. Give Name and Account no. of depositors.

7. List all the Names of customers.

8. List all the Names of branches.

9. List all the Names of borrowers.

10. List all the Names of customers living in city INDORE.

11. List all the Names of depositors having amount greater than 3000.

12. Display account date of customer ANIL.

13. Names of all branches located in city MUMBAI.

14. Names of borrower having loan no L3001.

15. Names of depositor having account at PALASIA;

16. Names of all branches located in city INDORE.

17. Name of customers who opened account after 31-MARCH-2010.

18. Give account no and deposit amount of customer having account opened between dates

1-FEB-2010 and 1-MAY-2010.

19. Give name of city where branch PALASIA is located.

20. Give details of customer ANIL.

21. Name of customer having living city MUMBAI and branch city JUHU.

22. Give names of customers having the same living city as their branch city.

23. Give names of customers who are borrowers as well as depositors and having living city

INDORE.

24. Give names of customers who are depositors and have the same branch city as that of

SUNIL.

25. Give names of depositors having the same living city as ANIL and having deposits

amount greater than 2000;

26. Give names of borrowers having deposits amount greater than 2500 and loan amount

greater than 60000.

27. Give names of depositors having the same branch as the branch of SUNIL.

28. Give names of borrowers having loan amount greater than the loan amount of PRAMOD.

29. Give names of customers living in the city where branch of depositor SUNIL is located.

30. Give loan no and loan amount for borrowers having same branch as that of SUNIL.

31. Give loan no and loan amount , account no and deposits amount of customers living in

city INDORE.

32. Give loan no and loan amount, account no and deposits amount of customers having

branch located in MUMBAI.

33. Give loan no, loan amount, account no, deposits amount, branch name, branch city and

living city of PRAMOD.

34. Give deposits details and loan details of customer in the city where PRAMOD is living.

35. Give names of depositors having same branch city as that of SUNIL and having the same

living city as that of ANIL.

36. Give names of depositors having amount greater than 5000 and having the same living

city as PRAMOD.

37. Give city of customer having the same branch city as that of PRAMOD.

38. Give branch city and living city of PRAMOD.

39. Give branch city of SUNIL and branch city of ANIL.

40. Give living city of ASHOK and living city of AJAY.

41. List all the customers who are depositors but not borrowers.

42. List all the customers who are both depositors and borrowers.

43. List all the customers along with their amount who are either borrowers or depositors and

living in city MUMBAI.

44. List all the depositors having deposits in all the branches where SUNIL is having

account.

45. List all the customers living in the city INDORE and having branch city MUMBAI or

BHOPAL.

46. List all the depositors living in city UJJAIN.

47. List all the depositors living in city INDORE and having branch in city MUMBAI.

48. List the branch cities of ANIL & SUNIL.

49. List the customer having deposit greater than 2000 and loan less 50000.

50. List the borrowers having branch city same as that of SUNIL.

assignment-test3 queries sql

Documents