SQL Exercises – Part II

1. SQL Exercises – Part II October 31, 2014

2. Exercise 1 • Consider the following relational database scheme: • Treatment (disease, medication) • Doctor(name, disease-of-specialization) • Treated (doctor_name, patient_name, date, procedure, diagnostic) • Patients and doctors are uniquely identified by their name • A patient may suffer from several diseases, and may take several medications for each disease. • It is possible to a doctor to be a patient and vice-versa. • The domains of the attributes "disease" and "disease-of-specialization" are the same, namely, the set of all diseases. • In "treated" relation, the procedure could be consultation, or intervention (surgery etc.) and the diagnostic could be the name of a disease or the type of intervention. • Write the following queries in SQL:

3. Exercise 1 (a) • Treatment (disease, medication) • Doctor (name, disease-of-specialization) • Treated (doctor_name, patient_name, date, procedure, diagnostic) • Give the name of doctors who don't suffer from any disease.

4. Solution 1 (a) – Method 1 • Give the name of doctors who don't suffer from any disease. DoctorsWhoSuffered(name(name=patient_name (Doctor  Treated))) name(Doctor) – DoctorsWhoSuffered • In SQL: SELECT distinct name FROM Doctor EXCEPT SELECT name FROM Doctor, Treated WHERE name = patient_name

5. Solution 1 (a) – Method 2 • Give the name of doctors who don't suffer from any disease. SELECT distinctd.name FROM DOCTORd WHERE NOT EXISTS (SELECT * FROM TREATEDt WHERE d.name = t.patient_name);

6. Exercise 1 (b) • Treatment (disease, medication) • Doctor (name, disease-of-specialization) • Treated (doctor_name, patient_name, date, procedure, diagnostic) • List patients who suffer from more than one disease.

7. Solution 1 (b) • List patients who suffer from more than one disease. Patient1(name1, diagnostic1) (patient_name, diagnostic( Treated)) Patient2(name2, diagnostic2) (Patient1) name1(name1=name2 AND diagnostic1 <> diagnostic2 (Patient1  Patient2)) • In SQL: SELECT distinct p1.patient_name FROM TREATEDp1, TREATEDp2 WHERE p1.patient_name = p2.patient_name AND p1.diagnostic <> p2.diagnostic;

8. Exercise 1 (c) • Treatment (disease, medication) • Doctor (name, disease-of-specialization) • Treated (doctor_name, patient_name, date, procedure, diagnostic) • Give the names of doctors who are also patients suffering from a disease in their own specialization.

9. Solution 1 (c) • Give the names of doctors who are also patients suffering from a disease in their own specialization. name(name=patient_name AND disease-of-specialty = diagnostic(Doctor  Treated)) • In SQL: SELECT distinct name FROM DOCTOR, TREADTED WHERE name = patient_name AND disease-of-specialization = diagnostic;

10. Exercise 1 (d) • Treatment (disease, medication) • Doctor (name, disease-of-specialization) • Treated (doctor_name, patient_name, date, procedure, diagnostic) • Find diseases for which there is only one medication.

11. Solution 1 (d) – Method 1 • Find diseases for which there is only one medication. DiseaseWithMoreThanOneTreatment (t1.disease (t1.disease=t2.disease AND t1.medication <> t2.medication(t1(Treatment)  t2(Treatment)) disease (Treatment) – DiseaseWithMoreThanOneTreatment • In SQL: SELECT distinctdisease FROM Treatment EXCEPT SELECT t1.disease FROM Treatment t1, Treatment t2 WHERE t1.disease =t2.disease AND t1.medication <> t2.medication

12. Solution 1 (d) – Method 2 • Find diseases for which there is only one medication. SELECT distinctt1.disease FROM TREATMENTt1 WHERE NOT EXISTS (SELECT t2.disease FROM TREATMENT t2 WHERE t1.disease = t2.disease AND t1.medication <> t2.medication);

13. Exercise 1 (e) • Treatment (disease, medication) • Doctor (name, disease-of-specialization) • Treated (doctor_name, patient_name, date, procedure, diagnostic) • Find the names of patient who had an operation done by a doctor with HIV

14. Solution 1 (e) • Find the names of patient who had an operation done by a doctor with HIV. SELECT distinct t1.patient_name FROM TREATEDt1 WHERE (t1.procedure=“intervention” AND t1.doctor_name IN ( SELECT distinctd1.name FROM TREATEDt2, DOCTORd1 WHERE (t2.patient_name=d1.name AND t2.diagnostic=“HIV”)));

15. Exercise 1 (f) • Treatment (disease, medication) • Doctor (name, disease-of-specialization) • Treated (doctor_name, patient_name, date, procedure, diagnostic) • List the patients who consulted 2 or more doctors and was given exactly the same diagnosis. List patient’s name, doctor’s name, the date, and the diagnosis.

16. Solution 1 (f) • List the patients who consulted 2 or more doctors and was given exactly the same diagnosis. List patient’s name, doctor’s name, the date, and the diagnosis. SELECT distinctt1.patient_name, t1.doctor_name, t1.date, t1.diagnostic FROM TREATEDt1, TREATEDt2 WHERE t1.patient_name = t2.patient_name AND t1.doctor_name <> t2.doctor_name AND t1.diagnostic=t2.diagnostic

17. Exercise 2 • Consider the following relation scheme about hockey teams: • Team (tname, year, coach, salary) • Player (pname, position) • Winner (tname, year) • Played (pname, tname, year, salary) • Assume that players and coaches appointments with teams are on a calendar year basis. • The team relation indicates teams, their coaches on yearly basis and their salaries. • The player relation indicates players and the position each player plays. • The winner relation indicates the Stanley Cup winners. • The played relation indicates players and the team they have played for on a yearly basis. • Write the following queries in SQL:

18. Exercise 2 (a) • Team (tname, year, coach, salary) • Player (pname, position) • Winner (tname, year) • Played (pname, tname, year, salary) • List all the players who have salary higher than 2M$, and have played for all the teams in the league

19. Solution 2 (a) • List all the players who have salary higher than 2M$, and have played for all the teams in the league SELECT p1.pname FROM PLAYEDp1 WHERE p1.salary > 20000000 Group byp1.pname Havingcount (distinctp1.tname) = (Select count (distinct team.tname) From TEAM);

20. Exercise 2 (b) • Team (tname, year, coach, salary) • Player (pname, position) • Winner (tname, year) • Played (pname, tname, year, salary) • List coaches who have also been players, and have coached only teams they once played for.

21. Solution 2 (b) • List coaches who have also been players, and have coached only teams they once played for. SELECT distinctt1.coach FROM TEAMt1 EXCEPT SELECT distinctt1.coach FROM TEAMt1 WHERE t1.coach NOT IN (SELECT p1.pname FROM PLAYEDp1 WHERE p1.tname=t1.tname);

22. Exercise 2 (c) • Team (tname, year, coach, salary) • Player (pname, position) • Winner (tname, year) • Played (pname, tname, year, salary) • List all the players who won the Stanley Cup during two consecutive years with two different teams.

23. Solution 2 (c) • List all the players who won the Stanley Cup during two consecutive years with two different teams. SELECT distincta1.pname FROM(SELECT p.pname, p.tname, p.year FROM PLAYEDp, WINNERw WHERE p.tname = w.tname AND p.year=w.year;) a1, (SELECT p.pname, p.tname, p.year FROM PLAYEDp, WINNERw WHERE p.tname = w.tname AND p.year=w.year;) a2 WHERE a1.pname = a2.pname AND a1.tname <> a2.tname AND (a1.year = a2.year – 1 OR a1.year = a2.year + 1);

24. Exercise 2 (d) • Team (tname, year, coach, salary) • Player (pname, position) • Winner (tname, year) • Played (pname, tname, year, salary) • List all coaches who earn more than the highest player's salary in the team.

25. Solution 2 (d) • List all coaches who earn more than the highest player's salary in the team. Select coach From Team Where salary > All (Select salary From Played Where Team.tname=Played.tname)