w3resource

SQL Exercise: Patients and doctors who gave them preliminary care.

SQL hospital Database: Exercise-13 with Solution

13. From the following tables, write a SQL query to identify the patients and the number of physicians with whom they have scheduled appointments. Return Patient name as "Patient", number of Physicians as "Appointment for No. of Physicians".

Sample table: appointment


Sample table: patient


Sample Solution:

SELECT p.name "Patient",
       count(t.patient) "Appointment for No. of Physicians"
FROM appointment t
JOIN patient p ON t.patient=p.ssn
GROUP BY p.name
HAVING count(t.patient)>=1;

Sample Output:

      Patient      | Appointment for No. of Physicians
-------------------+-----------------------------------
 Grace Ritchie     |                                 2
 John Smith        |                                 3
 Dennis Doe        |                                 3
 Random J. Patient |                                 1
(4 rows)

Explanation:

The said query in SQL that retrieves the number of appointments made by each patient, along with the count of how many physicians each patient has seen.

The table appointment aliases as t and the table patient aliases as p are used to simplify the syntax.

The JOIN statement links the appointment table with the patient table using a matching condition where the patient's SSN matches the appointment's patient ID.

The GROUP BY statement ensures that the count function aggregates the appointment count for each patient, while the HAVING statement applies a condition to include only those patients who have had at least one appointment.

Practice Online


E R Diagram of Hospital Database:

E R Diagram: SQL Hospital Database.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Find the patients and the physicians who treated them.
Next SQL Exercise: Count unique patients who came to examination room C.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

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

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook