16)Consider the following Entities and Relationships [30 Marks]
Branch (bno, bname, bcity, assets)
Account (acc_no ,balance)
Relation between Branch and Account is One to Many.
Constraint: Primary key, balance and assets should be > 0.
Create a Database in 3NF & write queries for following.
• Find the maximum account balance of each branch.
• Find branches where average account balance is more than 30000.
• Find names of all branches that have assets value greater than that of each branch in ‘pune’.
• Decrease 3% balance on account whose balance is greater than 100000.
• Display details of branch whose city starts from 'A'.
Soluation:-
Create a Database in3NF & write qureies for following.
• Find the maximum account balance of each branch.
SQL> Select max(balance),bname form branches,accounts where brancges. bno=account.bno group by bname;
• Find branches where average account balance is more than 30000.
SQL> Select avg(balance)from branches,accouts where branches.bno=accounts.bno and balance>30000;
• Find names of all branches that have assets value greater than that of each branch in ‘pune’.
SQL> Select max(assets),bname from branches,accounts where branches.bno=accounts.bno and bcity='pune' group by bname;
• Decrease 3% balance on account whose balance is greater than 100000.
SQL> Update accounts set balance=balance-balance*0.03 where balance >3000;
• Display details of branch whose city starts from 'A'.
SQL>select *from branches where bcity like 'A%';
/
• Find branches where average account balance is more than 30000. SQL> Select avg(balance)from branches,accouts where branches.bno=accounts.bno and balance>30000;
• Find names of all branches that have assets value greater than that of each branch in ‘pune’. SQL> Select max(assets),bname from branches,accounts where branches.bno=accounts.bno and bcity='pune' group by bname;
• Decrease 3% balance on account whose balance is greater than 100000. SQL> Update accounts set balance=balance-balance*0.03 where balance >3000;
• Display details of branch whose city starts from 'A'. SQL>select *from branches where bcity like 'A%';
Search
Recent Posts
Mail Management System
Posted on 2019-07-18Online food ordering system
Posted on 2019-07-18Library Management System
Posted on 2019-07-17Health center system project
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17furniture management system
Posted on 2019-07-17Electronic shop management system
Posted on 2019-07-17Automobile Workshop Management
Posted on 2019-07-17Online Visa Processing System
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17petrol-management system
Posted on 2019-07-17Cloths management system
Posted on 2019-07-17Society Management system
Posted on 2019-07-17Mall management system
Posted on 2019-07-17school management system
Posted on 2019-07-17Sales Order Processing System
Posted on 2019-07-17Retail sales management
Posted on 2019-07-17Raw Materials Management
Posted on 2019-07-17railway reservation system
Posted on 2019-07-17purchase and sales management system
Posted on 2019-07-17Placement Management System
Posted on 2019-07-17Pet Shop Management System
Posted on 2019-07-17petrol pump management system
Posted on 2019-07-17Patient Information System
Posted on 2019-07-17news agency system
Posted on 2019-07-17Cinema Booking System
Posted on 2019-07-17Medical Store System
Posted on 2019-07-17leave management System
Posted on 2019-07-17Laboratory Information Management System
Posted on 2019-07-17content management system
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17Institute Management System
Posted on 2019-07-17Hotel management System
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17Garage Management System
Posted on 2019-07-17Furniture shop management system
Posted on 2019-07-17Fisheries management System
Posted on 2019-07-17Fertilizer scheduling system
Posted on 2019-07-17online eye care system
Posted on 2019-07-17Dental Clinic Management System
Posted on 2019-07-17Cyber Café Management
Posted on 2019-07-17Milk Billing System
Posted on 2019-07-17Colddrink management system
Posted on 2019-07-17Cable management System
Posted on 2019-07-17Beauty parlor management system
Posted on 2019-07-17Facebook Clone
Posted on 2019-05-28Dance Class Management System
Posted on 2019-05-24Library Management System
Posted on 2019-05-24Cab Management System
Posted on 2019-05-23Blood Bank Management system
Posted on 2019-05-23Beauty Parlour Management System
Posted on 2019-05-23vissa proccesing system
Posted on 2019-05-23Toll Plazza
Posted on 2019-05-23BILLING APPLICATION
Posted on 2019-05-23FLORICULTURE MANAGEMENT SYSTEM
Posted on 2019-05-23Car On Rent
Posted on 2019-05-23E-commers Shop
Posted on 2019-05-23