Given the following schema. Primary keys are underlined and highlighted in yellow and foreign keys are bold. The meaning of the relations is straightforward. Medical_Staff(MS_Id, MS_First, MS_Last, MS_PhoneNumber, MS_Occupation, Salary) Blood_Bank(Bank_Id, Bank_Name, Bank_Street, Bank_City, Bank_St, Bank_PhoneNumber) Donor(Donor_Id, Donor_First, Donor_Last, Donor_BloodType, Donor_City, Donor_St, DOB, TotalNumberOfDonations) Donation_Record(Donor_Id, Donation_Date, Donation_Amount) Employee(MS_Id, Bank_Id) Storage(Donor_Id, Donation_Date, Bank_Id) Write SQL queries to answer the following questions: a.Find the average donation amount for Sue Thompson (Donor Id = 3329) for the year 2018. b.Sum all donations by Bank for each State (i.e. the State the blood bank is located in, not the Donor). c.Find the name of the youngest donor and all the donations made in by that donor in 2020.
SQL Queries:
Given the following schema. Primary keys are underlined and highlighted in yellow and foreign keys are bold. The meaning of the relations is straightforward.
Medical_Staff(MS_Id, MS_First, MS_Last, MS_PhoneNumber, MS_Occupation, Salary)
Blood_Bank(Bank_Id, Bank_Name, Bank_Street, Bank_City, Bank_St, Bank_PhoneNumber)
Donor(Donor_Id, Donor_First, Donor_Last, Donor_BloodType, Donor_City, Donor_St, DOB, TotalNumberOfDonations)
Donation_Record(Donor_Id, Donation_Date, Donation_Amount)
Employee(MS_Id, Bank_Id)
Storage(Donor_Id, Donation_Date, Bank_Id)
Write SQL queries to answer the following questions:
a.Find the average donation amount for Sue Thompson (Donor Id = 3329) for the year 2018.
b.Sum all donations by Bank for each State (i.e. the State the blood bank is located in, not the Donor).
c.Find the name of the youngest donor and all the donations made in by that donor in 2020.
Step by step
Solved in 3 steps
