Question

Using the following data to create various queries below.

Database is Pensacola Healthcare

Facility(FacilityID, SuborgID, Addr)

Provider(ProviderID, FirstName, LastName, NPI, FacilityID)

Patient(PatientID, FirstName, LastName, Addr)

Appointment (AppointmentID, ApptTime, ProviderID, PatientID, FacilityID)

1. Write a query displaying all patients

2. Write a query showing all appointments for a patient with an ID of 2.

3. Write a query in DESC order of Addr for all facilities.

4. Write a query showing all Providers with name LIKE Smith.

5. Write a query showing appointment and patient info for the patient with ID of 5 and AppointmentID of 21.

6. Create new table called SubOrg. Insert calues for SubOrgID, OrgID, and addr.

7. make an update query to facility table where ID of facility was 3 but now is 4.

8. Write a query showing an appointment for patient with ID of 5 and providerID of 7. Ensure to include patient.FirstName patient.Lastname provider.foirstName provider.lastName, PatientID, ProviderID, appointmentID and ApptTime. Hint use Inner Join query.

Answer 1

Hi,

Please find the queries as below:

(1) Select * from Patient;
(2) Select * from Appointment where PatientID = 2;
(3) Select * from Facility ORDER BY Addr DESC;
(4) Select * from Provider where FirstName LIKE '%Smith%' or LastName LIKE '%Smith%';
(5) Select * from Appointment a,Patient p where p.PatientID = '5' and a.AppointmentID = '21';
(6) CREATE TABLE SubOrg (
SubOrgID int,
OrgID int,
addr varchar(255));
Insert into SubOrg ( SubOrgID , OrgID , addr ) values (1,2, "New Town City, LA");
(7) update Facility set FacilityID = 4 where FacilityID = 3;
(8) Select pa.FirstName, pa.Lastname, pr.FirstName, pr.LastName, pa.PatientID, pr.ProviderID, a.AppointmentID , a.ApptTime from Patient pa inner join Appointment a on pa.PatientID = a.PatientID inner join Provider pr on pr.ProviderID = a.ProviderID where a.PatientID = 5 and pr.ProviderID = 7;
  
Also, I am new to Chegg, this being my first answer, hence kindly let me know if any more info is required for this answer.