A. Given the database schema on the relation as follows:
PATIENT (Name, Disease, Treatment)
DOCTOR (Name, Specialty)
Assume:
1. Name uniquely identifies both patients and doctors and that some doctors may also be patients.
2. Diseases and specialties are the same kinds of things and are of the same domain. (For example, Flu might be both a disease and a specialty)
3. A doctor may have more than one specialty, a patient may have more than one disease, and each disease a patient has may be treated in more than one way.
Please write the following queries in SQL (60 points)
You are not allowed to use aggregation function such as count(), avg(), min(), max(), sum().
Answer:
a. Retrieve the names of doctors who have a disease which is one of their specialties.
b. Retrieve the names of doctors who have all the diseases which are their specialties
c. Retrieve the diseases for which there is only one treatment.
d. For each patient with some disease, if she/he is receiving all known treatments for that disease, list the patient name.
