The Institute for Psychological Research helps administer field studies for research psychologists from various academic institutions.  Their database has the following design:

VOLUNTEER(VolunteerID, FirstName, LastName, Address, City, State, Zip,

Phone, Email, BirthDate, Gender, AnnualIncome, StudyID)

               StudyID foreign key to STUDY

STUDY(StudyID, StudyName, StartDate, EndDate)

RESEARCHER(ResearcherID, FirstName, LastName, Email, Phone, InstitutionID)

               InstitutionID foreign key to INSTITUTION

STUDYDETAIL(ResearcherID, StudyID, PrimaryInvestigator)

               ResearcherID foreign key to RESEARCHER

               StudyID foreign key to STUDY

INSTITUTION(InstitutionID, InstName, Address, City, State, Zip)

It is possible for two studies to have the same name.  Furthermore:

• BirthDate, StartDate, and EndDate are in date/time format.

• PrimaryInvestigator is a yes/no field that holds “yes” when researcher ResearcherID  is the primary investigator for study StudyID.  Any number of researchers may work on a study, but only one of them can be the primary investigator.

• AnnualIncome is in currency format.

• All other fields are text fields.  Gender is a one-character field holds “M” for males and “F” for females.

Write SQL statements to implement the following queries.  Fields should be displayed in the order specified.
  

(a)  Show the first name, last name, city, and birth date of all female volunteers living in Massachusetts (state code “MA”) and born before 1965.  Sort the results, with the oldest volunteers appearing first.

(b)  List the name and start date of each study, along with the first name, last name, and institution name of its primary investigator.  List in order of start date, earliest first.

(c)   Show the last name, city, and state for all volunteers not in New Jersey (state code “NJ”), participating in studies whose primary investigator is from an institution in New Jersey.  Also include the study name and institution name.

(d)  Show the study name, start date, and total number of researchers (labeled "NumResearchers") working on each study.

(e)  For each study whose primary researcher is from an institution in California (state code “CA”), show the study name and average income (labeled "AvgIncome") of all its volunteers who are from Massachusetts or New Jersey.  There should be exactly one output row for each study with a California primary researcher and any volunteers meeting the criteria; studies with no Massachusetts or New Jersey volunteers need not appear on the output.

(f)  Show the study ID, name, start date, and end date of all studies which have any volunteers in Wisconsin.  Sort by start date, most recent studies first.  Among studies with the same start date, sort alphabetically.  No study should appear more than once in the output.