SQL Challenges-1: Students and Examinations
From the following tables write a SQL query to find the number of times each patient call the specialist doctor since their treating period. Order the result table by patient_id and specialist_call.
Input:
Table: patient
Structure:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
patient_id | int(11) | NO | PRI | ||
patient_name | varchar(25) | YES |
Data:
patient_id | patient_name |
---|---|
1001 | Gilbart Kane |
1002 | Thomas Richi |
1003 | Ricardo Grance |
1004 | Vanio Tishuma |
1004 | Charls Brown |
Table: speciality
Structure:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
specialist | varchar(25) | YES |
Data:
specialist |
---|
medicine |
cardiology |
neurology |
hematology |
Table: treatment
Structure:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
patient_id | int(11) | YES | MUL | ||
specialist_call | varchar(25) | YES |
Data:
patient_id | specialist_call |
---|---|
1001 | medicine |
1003 | medicine |
1002 | cardiology |
1001 | hematology |
1004 | medicine |
1003 | cardiology |
1005 | neurology |
1002 | neurology |
1001 | cardiology |
1005 | cardiology |
1003 | cardiology |
1005 | hematology |
1004 | hematology |
1005 | neurology |
1002 | neurology |
1001 | hematology |
Sample Solution:
SQL Code(MySQL):
Sample Output:
patient_id|patient_name |specialist |Specialist Attended| ----------|---------------|-----------|-------------------| 1001|Gilbart Kane |cardiology | 1| 1001|Gilbart Kane |hematology | 2| 1001|Gilbart Kane |medicine | 1| 1001|Gilbart Kane |neurology | 0| 1002|Thomas Richi |cardiology | 1| 1002|Thomas Richi |hematology | 0| 1002|Thomas Richi |medicine | 0| 1002|Thomas Richi |neurology | 2| 1003|Ricardo Grance |cardiology | 2| 1003|Ricardo Grance |hematology | 0| 1003|Ricardo Grance |medicine | 1| 1003|Ricardo Grance |neurology | 0| 1004|Vanio Tishuma |cardiology | 0| 1004|Vanio Tishuma |hematology | 1| 1004|Vanio Tishuma |medicine | 1| 1004|Vanio Tishuma |neurology | 0| 1005|Charls Brown |cardiology | 1| 1005|Charls Brown |hematology | 1| 1005|Charls Brown |medicine | 0| 1005|Charls Brown |neurology | 2|
SQL Code Editor:
Contribute your code and comments through Disqus.
Previous: All People Report to the Given Manager.
Next: Find the Team Size.