1) To retrieve a list of all doctors, the following SQL query can be used:
SELECT *
FROM Doctor;
This query selects all columns from the Doctor table, which includes the doctor’s ID, name, medical license number, and phone number. It retrieves all the rows from the table, providing a list of all doctors.
2) To find the number of patients that were born before 01/01/1994, the following SQL query can be used:
SELECT COUNT(*)
FROM Patient
WHERE DOB < '1994-01-01';
This query counts the number of rows in the Patient table where the date of birth (DOB) is earlier than the specified date. The result of the query is the number of patients born before 01/01/1994.
3) To retrieve patient information with the ID of the prescribing doctor for each patient, the following SQL query can be used:
SELECT Patient.PatientID, Patient.Name, Prescription.PrescriberID
FROM Patient
JOIN Prescription ON Patient.PatientID = Prescription.PatientID;
In this query, a join operation is performed between the Patient and Prescription tables using the common PatientID column. It selects the PatientID and Name columns from the Patient table and the PrescriberID column from the Prescription table. This query retrieves the patient information along with the ID of the prescribing doctor for each patient.
4) To retrieve prescription information along with patient name, DOB, medication brand name, and prescribing doctor name sorted by the most recent date, the following SQL query can be used:
SELECT Prescription.PrescriptionID, Prescription.Date, Patient.Name AS 'PatientName', Patient.DOB, Medication.BrandName, Doctor.Name AS 'DoctorName'
FROM Prescription
JOIN Patient ON Prescription.PatientID = Patient.PatientID
JOIN Medication ON Prescription.MedicationID = Medication.MedicationID
JOIN Doctor ON Prescription.PrescriberID = Doctor.DoctorID
ORDER BY Prescription.Date DESC;
This query performs multiple join operations, connecting the Prescription table with the Patient, Medication, and Doctor tables based on their respective IDs. It selects the PrescriptionID, Date, Patient Name (aliased as 'PatientName'), DOB, Medication BrandName, and Doctor Name (aliased as 'DoctorName'). The results are sorted in descending order by date, ensuring that the most recent prescriptions appear at the top.
