w3resource

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:

FieldTypeNullKeyDefaultExtra
patient_idint(11)NOPRI
patient_namevarchar(25)YES

Data:

patient_idpatient_name
1001Gilbart Kane
1002Thomas Richi
1003Ricardo Grance
1004Vanio Tishuma
1004Charls Brown

Table: speciality

Structure:

FieldTypeNullKeyDefaultExtra
specialistvarchar(25)YES

Data:

specialist
medicine
cardiology
neurology
hematology

Table: treatment

Structure:

FieldTypeNullKeyDefaultExtra
patient_idint(11)YESMUL
specialist_callvarchar(25)YES

Data:

patient_idspecialist_call
1001medicine
1003medicine
1002cardiology
1001hematology
1004medicine
1003cardiology
1005neurology
1002neurology
1001cardiology
1005cardiology
1003cardiology
1005hematology
1004hematology
1005neurology
1002neurology
1001hematology

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.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8