Question

I am using Oracle SQL and am new to it. I have seven tables, one of them is a subtable of two of the others. I need to do the following queries:

1. List all Patients and what Bed they are assigned to

2. List all patients who had Treatments and what Treatment they received

3. List all patients who had tests and what Test they had

4. List the employees (doctors, nurses, etc.) who assisted each patient.

5. List all patients in alphabetical order

6. List all patients who live in Atlanta and had a test completed

7. List all patients who live in either Woodstock or Roswell who had a treatment completed.

Can someone please show me theproper Oracle SQL syntax to pull these queries with the given tables i have?

Please note the Primary and Foreign keys in the tables, and that one table, Patient_Employee, is a subtable of the Patient table and the Employee table.

Here are pics to help you understand what i have:

Oracle SQL Develo : test Eile Edit View Navigate Run Versioning Iools Help θ G 网@ %E 咆 . O. 皛. ask test x test ▼ iorksheet Query Builder CREATE TABLE Patient Pat S3N nunber (9) HOT HULL, Bed_ID number (3] NOT NULL, Pat First None vrchar (S), Pat_Last Nene varchar (15), Pat_Phone nunber 15) Pat Street Addreaa varchar (25) Pat_City varchar 1s, Pat State varchar 15, Pat 2ip nuber ( Gender varchar (10) Pat Birth Dte DRTE Check In Date DATE Check Cut Date DTATE Insurance Provider varchar (15) PRDARY KEY (Pat_SsN, CONSTRAINT Bed-永FOREIGH KEY (Bed-TD) REFERENCES Bed (Bed-TD) INSERT INTO Patient VALUBS (666666666, ill, JeEf,Johnson', 6782224783, '93 Firat St. ', 'Atlanta', -Georgia. 30405, 'Male', TO-DATE('1983-11-06', 'YYYY/MX/DD"),TO_DATE ('2018-10-06', 'YYYY/MX/DD"), נת DATE ('2018-10-06', TYYY/TI/DD '), 'Kaiser'); INSERT 끄TTO Patient VALUES(777777777, 2ZZ, 'Chris', 'Patterson, 4703674628, 'Rosue 11', 'Georgio', 30186, .Mole', TO DATE('1990-01-17', 'YYYY/MH/DD'), Τ0-DATEI. 2018-10-22., 'YYYY/MM/DD'], TO DATE( '2018-10-23', '/DD'. 'Blue Cross INSERTINTO Patient VALUES (888888888, 177, Ee11Y', י smith. 40 4949 2408, '354 Third St. 'Woodstock. 'Georgia', 40897, 'Female', TO DATE ('1992-01-17', 'YYYY/MX/DD'), TO-DATE! '2018-10-22', 'YYYY/MM/DD'), TO DATE( '2018-10-23', 'YYYY MADD'), 'Blue Cross): INSERT 끄T0 Patient VALUES (999999999, 316, 'Preston', 'Mortis', 678 4647893, '18 Aclanta Georgi', 45326, 'Male', TO DATEl '1975-11-11, TYYYIM DD', TO DATE '2018-10-02', 'TYY?/M/DD'. TO_DATE('2018-10-21', 'TTYIINDD')r 'Tricare Second St. rth St. ', L Line 36 Column 1 Insert Modfied Windows: OR/LF Editing 11:07 PM 11/9/2018

Also, Here are SELECT * FROM queries of each table:

I didnt include the code for the Department table since it isnt necessary to perform any of the queries listed above. Please show me the proper syntax to make those queries specifically using Oracle SQL. Thanks in advance for the help!

Answer 1

1. List all Patients and what Bed they are assigned to

select Pat_First_Name, Pat_Last_Name, Bed_Type from Patient p, Bed b where p.Bed_ID = b.Bed_ID;

2. List all patients who had Treatments and what Treatment they received

select Pat_First_Name , Pat_Last_Name, Treatment_Type , Treatment_Details from Patient p, Treatment t where t.Pat_SSN = p.Pat_SSN;

3.  List all patients who had tests and what Test they had

select Pat_First_Name, Pat_Last_Name, Test_Type, Test_Details from Diagnostic_Test t, Patient p where t.Pat_SSN = p.Pat_SSN;

4. List the employees (doctors, nurses, etc.) who assisted each patient.

select Emp_First_Name, Emp_Last_Name from Employee e, Patient_Employee pe where pe.Emp_SSN = e.Emp_SSN;

5. List all patients in alphabetical order

select * from Patients order by Pat_First_Name, Pat_Last_Name;

6. List all patients who live in Atlanta and had a test completed

select Pat_First_Name, Pat_Last_Name from Patient p, Diagnostic_Test t where p.Pat_SSN = t.Pat_SSN and lower(p.Pat_City) = 'atlanta' and p.check_out_date is not null;

7. List all patients who live in either Woodstock or Roswell who had a treatment completed.

select Pat_First_Name , Pat_Last_Name from Patient p, Treatment t where p.Pat_SSN = t.Pat_SSN and lower(p.Pat_City) in ('woodstock','rosewell') and p.check_out_date is not null;