Write an SQL statement to answer the question Primary keys a

Write an SQL statement to answer the question.

Primary keys are( BOLD and italicized) and attributes with the same names in different tables are foreign keys.  

Doctors(doctorId, doctorName, specialty)

Patients(patientId, patientName, sex, patientBalance, numberOfVisits)

Records(doctorId, patientId, date, diagnosis, visitCost)

1. For each doctor, list doctor identifier, doctor name and the total number of visits that were administered by the doctor.

Solution

Answer:

select d.doctorId, d.doctorName, sum(p.numberOfVisits) from Doctors d, Patients p, Records r where d.doctorId = r.doctorId and r.patientId = p.patientId group by d.doctorId, d.doctorName;