DAT 560G - Fall 2020 – Final Exam – Solutions
- 1 -

DAT560G: Database Design and SQL
Fall 2020 Mini A

Sample Final Exam with Solutions

INSTRUCTIONS
1. This is an individual assignment. You may not discuss your approach to solving these
questions with anyone, other than the instructor or TA. We will only answer clarification
questions.
2. Please write your SID at the bottom of this page.
3. You are not permitted to use any online resources.
4. You are supposed to have only MySQL, and Ms. Word open on your laptop. A browser
is necessary at the last moments to submit your work on Canvas.
5. Submit the pdf file on Canvas page. Keep your time.
6. Don’t worry if you can’t complete this exam. Grades in the course will be curved.
GOOD LUCK



DAT 560G - Fall 2020 – Final Exam – Solutions
- 2 -

ASSIGNMENT

PharmaVisits is a database collecting patients visit information around the world. Once a patient
visits a medical facility for a reason, the patient’s information along the disease information will
be recorded. In certain times, drugs might have serious adverse effect and outcomes. Scientists use
this information to detect the effectiveness of drugs to improve the efficacy or recall drugs due to
their outcomes.
This database consists of 4 tables as visits, drugs, medicalclinics, and patients. Each visit consists
of information about the patient, medical facility, drugs that has been administrated, patients’
disease, reaction, and severe outcome after using drugs. Tables along with their attributes are as
follow:
Visits Table:
x VisitID
x Date: Date and time of the visit
x DrugID
x Drug Sequence: The sequence of the corresponding drug that has been administrated. In
each visit, multiple drugs might be administrated.
x PatientID
x Medical Clinic: Name of medical Clinic
x Disease
x Reaction: Reaction after using a drug. There might be multiple reactions after using a drug.
x Severe Outcome: The outcome after using the drug including Hospitalization, Life
Threatening, Disability, Death, and Other Severe Outcomes.

Patients Table:
x PatientID
x First name
x Last name
x Birthdate: Date and time of birth
x Gender
x Weight: Weight in KG
x City
x State
x Postal Code
x Country

Medical Clinics Table
x Medical Clinic: The name of the facility
x Specialization
x Year Opened: Founding year
x City
x State
x Postal Code
x Country

DAT 560G - Fall 2020 – Final Exam – Solutions
- 3 -

Drugs Table:
x DrugID
x Drug name
x Generic name: Generic name of the given drug
x Manufacturer: Manufacturer of the drug
x Route: The route that the drug is being administrated.


The E/R diagram for this database is below:

DAT 560G - Fall 2020 – Sample Final Exam
- 4 -


For each question, submit your SQL code and a screen-shot of the results. If the results are too
long, partial results are fine. Include the number of rows that are included in the solution.
You may include a few additional attributes, but do not include many useless attributes. The
attributes returned in the query must make it clear that the result is correct.
You will submit a hard copy.

Please write your SID, before Question 1:


4 Sample Questions
1) List unique drugs that have been used in the St. Louis Oncology Center medical clinic. Please
list the drug name, drug generic name, and the route of the drug.

SELECT DISTINCT DrugName, GenericName, Route
FROM visits JOIN drugs USING(drugID)
WHERE medicalclinic='St. Louis Oncology Center';




/* 2 rows returned */

2) Please list the full name (first name and last name in one column) of all patients who have
been born between (and including) 1950 and 1960. If the names are not available, show it as
“Not Available”.

SELECT IF(CONCAT(FirstName, " ", LastName) IS NULL, "Not Available", CONCAT(FirstName, " ",
LastName)) AS Name
FROM patients
WHERE Year(birthdate)>=1950 and Year(birthdate)<=1960;


/* 11 rows returned */
DAT 560G - Fall 2020 – Sample Final Exam
- 5 -


3) Find the average weight of patients who are suffering from frequent diseases (frequent disease
is a disease that has more associated patients than average number of patients per disease).

SELECT disease, AVG(weight)
FROM
(SELECT DISTINCT patientID, weight, disease
FROM patients JOIN visits USING(patientID)
WHERE disease IN
(SELECT Disease
FROM visits
GROUP BY Disease
HAVING COUNT(DISTINCT PatientID)>
(SELECT AVG(NumberOfPatients)
FROM
(SELECT Disease, COUNT(DISTINCT PatientID) AS NumberOfPatients
FROM visits
GROUP BY Disease) AS t1))) AS t2
GROUP BY disease



/* 8 rows returned */

4) Find the pair of patients who are suffering from the same disease and have been visited in
2017 or after. Please list the patientIDs, and disease.

SELECT DISTINCT v1.PatientID, v2.PatientID, v1.Disease
FROM visits AS v1 JOIN visits AS v2 USING(disease)
WHERE v1.PatientID=2017 AND Year(v2.date)>=2017;
DAT 560G - Fall 2020 – Sample Final Exam
- 6 -




/* 13 rows returned */

欢迎咨询51作业君