Write SQL statements to answer questions 1.1, 1.2, 1.3 and 1.4 based on the foll

Question

Write SQL statements to answer questions 1.1, 1.2, 1.3 and 1.4 based on the following schema. Primary keys are (BOLD and italicized) and attributes with the same names in different tables are foreign keys.

Doctors(doctorId INT, doctorName VARCHAR, specialty)

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

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

1.1) Create a view, called DoctorSmithsPatients, to list all the patients Dr. Smith has.

1.2) Write a trigger, each time a row is inserted in the table Records, the numberOfVisits in the corresponding Patient row is updated by 1 (i.e. add 1 to the numberOfVisits). Hint: Consider using Update statement.

1.3) Find the cost of the most expensive visit (Remember: Aggregate functions)

1.4) For each doctor, list doctor identifier, doctor name and the total number of visits that were administered by the doctor. (Remember: Aggregate/GROUP BY)

Explanation / Answer

Solution:

1.1)

CREATE VIEW DoctorSmithsPatients
AS SELECT patientid from Doctors d, Patients p, Records r where d.doctorId = r.doctorId and r.patientId = p.patientId WHERE doctorName= 'Dr. Smith';

1.2)

if Patients.patientId = NEW. patientId then update Patients set numberOfVisits = numberOfVisits+1

   where patientId = NEW. patientId; end if;

END;

1.3)

select MAX(visitCost) AS mostExpensive from Records

1.4)

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;

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.