Slip13
Consider the following Entities and
Relationships
Account (ano, branchname, balance)
Customer (cust_no, cust_name, street, city)
Relation between Account and Customer is Many to
Many.
Constraint: Primary key, balance should be > 500.
Solution:-
Create a Database in 3NF & write queries for
following.
•Display customer details with balance between 100000 and 200000.
SQL>select * from account,customer,ac where
account.ano=ac.ano and customer.cno=ac.cno and balance=(select balance from
account where balance=100000 and balance=200000);
•Display customers having more than two accounts in
Chinchwad branch.
SQL>select cname,branchname from account,customer,ac group by
cname,branchname having count(branchname)>=1 and branchname=’chinchwad’;
Or
SQL>select cname,branchname from account,customer,ac where
account.ano=ac.ano and customer.cno=ac.cno group by cname,branchname having
count(cname)>2 and branchname=’chinchwad’;
•Delete account whose balance is below the balance
<500.
SQL>
•Select names of all Customers whose street name
include the substring “road” and whose city is ‘Mumbai’.
SQL>Select instr(street,’road’),cname,street from
customer where city=’mumbai’ group by street,cname;
•Find number of depositor for each branch.
SQL>select count(*),branchname from
account,customer,ac where account.ano=ac.ano and customer.cno=ac.cno group by
branchname;
