DBMS-Slip17

Slip17

Consider the following Entities and Relationships                                   
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.

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=Donor.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;

•Displaythe 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%';