[Solved] SQL querying – Doctors and Hospitals [closed]

by


I’ll help you with your first question, and I’ll leave to you the second.

  1. Display doctorid, dname, total fees received by the doctor(s) who have treated more than one patient?

Let’s split this problem in pieces:

So you need first to know which doctors have treated more than one patient. That information is in the table billing. So:

select doctorId, count(patientId) as patientCount
from (select distinct doctorId, patientId from billing) as a
group by doctorId
having count(patientId)>1;

This query will return only the Ids of the doctors that have more than one patient. Notice that I’m using a subquery to deduplicate the doctor-patient tuple.

Now let’s attack the other part of this question: The total fees of each doctor. Again, that info is in the table billing:

select doctorId, sum(fees) as totalFees
from billing
group by doctorId;

Finally, let’s put it all together, and include the doctor’s info, which is in the table doctor:

select
    d.doctorId, d.doctorName, a.totalFees
from
    doctor as d
    inner join (
        select doctorId, sum(fees) as totalFees
        from billing
        group by doctorId
    ) as a on d.doctorId = a.doctorId
    inner join (
        select doctorId, count(patientId) as patientCount
        from (select distinct doctorId, patientId from billing) as a
        group by doctorId
        having count(patientId)>1;
    ) as b on d.doctorId = b.doctorId;

Hope this helps

Things you need to study and (or) keep in mind:

  1. You need to understand how to relate data stored in different tables. Study how to use INNER JOIN (and also LEFT JOIN and RIGHT JOIN)
  2. You need to understand how does GROUP BY works, and how to use aggregate functions (sum(), count(), etcetera).
  3. You know how to write subqueries. Now try to use them not only for where conditions, but as data sources (including them in from statements)
  4. Keep a copy of the reference manual of your RDBMS at hand. Also a good book on SQL can help you (go to a bookstore or library and find one you like).

6

solved SQL querying – Doctors and Hospitals [closed]