17)Consider the following Entities and Relationships [30 Marks]
Donor (donor_no, donor_name, city)
Blood_Donation(bid,blood_group,quantity,date_of_collection)
Relation between Donor and Blood_Donation is One to Many.
Constraint: Primary key, blood_group should not be null.
Create a Database in 3NF & write queries for following.
• Display total blood quantity collected on 25th December 2013.
• Display total blood donated by each donor.
• Display Donor details having blood group 'A+ve'.
• Display the donor who has donated blood more than two times.
• Display the donor information with blood group whose city name contains “sh” in it.
Solution:-
Create a Database in 3NF & write queries for following.
• Display total blood quantity collected on 25th December 2013.
SQL>Select sum(quantity)from Blood_Donation where date_of_collection='25-12-2013';
• Display total blood donated by each donor.
SQL>Select donor_name,sum(quantity)from Blood_Donation,Donor where Blood_Donation.donor_no group by donor_name;
• Display Donor details having blood group 'A+ve'.
SQL> Select donor-name,city from Donor,Blood_Donation where Donor.donor_no=Blood_Donation.donor_no and blood_group='A+VE';
• Display the donor who has donated blood more than two times.
SQL>Select donor_name from Donor, Blood_Donation where Donor.donor_no=Blood_Donation.donor_no and Blood_Donation. donor_no>=2;
SQL>Select donor_name from Donor,Blood_Donation where Donor.donor_no=Blood_Donation.donor_no and Blood_Donation. donor_no>2;
• Display the donor information with blood group whose city name contains “sh” in it.
SQL>Select donor_name,city,blood_group from Donor,Blood_Donation where Donor.donor_no=Blood_Donation..donor_no and city like '%sh%';
/
• Display total blood donated by each donor. SQL>Select donor_name,sum(quantity)from Blood_Donation,Donor where Blood_Donation.donor_no group by donor_name;
• Display Donor details having blood group 'A+ve'. SQL> Select donor-name,city from Donor,Blood_Donation where Donor.donor_no=Blood_Donation.donor_no and blood_group='A+VE';
• Display the donor who has donated blood more than two times. SQL>Select donor_name from Donor, Blood_Donation where Donor.donor_no=Blood_Donation.donor_no and Blood_Donation. donor_no>=2;
SQL>Select donor_name from Donor,Blood_Donation where Donor.donor_no=Blood_Donation.donor_no and Blood_Donation. donor_no>2;
• Display the donor information with blood group whose city name contains “sh” in it. SQL>Select donor_name,city,blood_group from Donor,Blood_Donation where Donor.donor_no=Blood_Donation..donor_no and city like '%sh%';
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