SQL Challenges-1: Students and Examinations

SQL Challenges-1: Exercise-46 with Solution

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:

Data:

Table: speciality

Structure:

Data:

Table: treatment

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

CREATE TABLE patient(
patient_id integer NOT NULL UNIQUE,
patient_name varchar(25));


INSERT INTO patient VALUES(1001,'Gilbart Kane   ');
INSERT INTO patient VALUES(1002,'Thomas Richi   ');
INSERT INTO patient VALUES(1003,'Ricardo Grance ');
INSERT INTO patient VALUES(1004,'Vanio Tishuma  ');
INSERT INTO patient VALUES(1005,'Charls Brown   ');


CREATE TABLE speciality(
specialist varchar(25));

INSERT INTO speciality VALUES('medicine   ');
INSERT INTO speciality VALUES('cardiology ');
INSERT INTO speciality VALUES('neurology  ');
INSERT INTO speciality VALUES('hematology ');


CREATE TABLE treatment(
patient_id integer ,
specialist_call varchar(25),
foreign key (patient_id) references patient(patient_id));


INSERT INTO treatment VALUES(1001,'medicine   ');
INSERT INTO treatment VALUES(1003,'medicine   ');
INSERT INTO treatment VALUES(1002,'cardiology ');
INSERT INTO treatment VALUES(1001,'hematology ');
INSERT INTO treatment VALUES(1004,'medicine   ');
INSERT INTO treatment VALUES(1003,'cardiology ');
INSERT INTO treatment VALUES(1005,'neurology  ');
INSERT INTO treatment VALUES(1002,'neurology  ');
INSERT INTO treatment VALUES(1001,'cardiology ');
INSERT INTO treatment VALUES(1005,'cardiology ');
INSERT INTO treatment VALUES(1003,'cardiology ');
INSERT INTO treatment VALUES(1005,'hematology ');
INSERT INTO treatment VALUES(1004,'hematology ');
INSERT INTO treatment VALUES(1005,'neurology  ');
INSERT INTO treatment VALUES(1002,'neurology  ');
INSERT INTO treatment VALUES(1001,'hematology ');



SELECT s.patient_id, patient_name, sub.specialist , COUNT(e.specialist_call) AS "Specialist Attended"
FROM patient AS s
CROSS JOIN speciality AS sub
LEFT JOIN treatment AS e
ON s.patient_id = e.patient_id
AND sub.specialist = e.specialist_call
GROUP BY s.patient_id, patient_name, sub.specialist
ORDER BY patient_id, patient_name, specialist;

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.