Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


alter session set NLS_DATE_FORMAT='DD/MM/YYYY'; 
CREATE TABLE PERSON
(
  Person_id      NUMBER(3) PRIMARY KEY,
  Surname        VARCHAR2(20),
  First_name     VARCHAR2(20),
  Sex            CHAR(1),
  Birth_date     DATE,
  Street         VARCHAR2(40),
  Town           CHAR(20),
  Postcode       NUMBER(4),
  Next_of_kin    NUMBER(3)
 );
INSERT INTO person (Person_id, First_name, Surname) VALUES (1, 'First1', 'Last1');
1 rows affected

INSERT INTO person (Person_id, First_name, Surname) VALUES (2, 'First2', 'Last2');
1 rows affected

CREATE TABLE STAFF
(
  Person_id     NUMBER(3) PRIMARY KEY,
  Start_date    DATE,
  Staff_type    VARCHAR2(15),
  Charges       NUMBER(10,2),
  Resign_date   DATE,
  FOREIGN KEY (Person_id) references PERSON(Person_id)
  );
CREATE TABLE WARD (
   Ward_code         CHAR(3) PRIMARY KEY,
   Ward_name         VARCHAR2(20),
   Bed_count         NUMBER(4),
   Opened_date       DATE,
   Last_painted_date DATE,
   Daily_charge      NUMBER(10,2)
);
INSERT INTO ward (Ward_code, Daily_charge)  VALUES (1, 100);
1 rows affected

INSERT INTO ward (Ward_code, Daily_charge)  VALUES (2, 120);
1 rows affected

CREATE TABLE OPERATION_TYPE (
   Op_code         CHAR(3) PRIMARY KEY,
   Operation_name  VARCHAR2(50),
   Theatre_fee     NUMBER(10,2),
   Days_in         NUMBER(5)
);
CREATE TABLE ADMISSION (
   Admission_id    NUMBER(3) PRIMARY KEY,
   Patient_id      NUMBER(3),
   Admission_date  DATE NOT NULL,
   Expected_op     CHAR(3),
   Admitted_by     NUMBER(3),
   Ward_code       CHAR(3),
   Discharge_date  DATE,
FOREIGN KEY (Patient_id) references PERSON(Person_id),
FOREIGN KEY (Expected_op) references OPERATION_TYPE(Op_code),
FOREIGN KEY (Admitted_by) references PERSON(Person_id),
FOREIGN KEY (Ward_code) references WARD(Ward_code)
);
INSERT INTO admission (admission_id, patient_id, admission_date, discharge_date, ward_code) VALUES (1, 1, current_date, current_date, 1);
1 rows affected

INSERT INTO admission (admission_id, patient_id, admission_date, discharge_date, ward_code) VALUES (2, 2, current_date, current_date, 2);
1 rows affected

CREATE TABLE OPERATION (
   Operation_id     NUMBER(3) PRIMARY KEY,
   Actual_op        CHAR(3),
   Admission_id     NUMBER(3),
   Op_date          DATE,
   Surgeon          NUMBER(3),
   Anaesthetist     NUMBER(3),
FOREIGN KEY (Surgeon) references PERSON(Person_id),
FOREIGN KEY (Anaesthetist) references PERSON(Person_id),
FOREIGN KEY (Actual_op) references OPERATION_TYPE(Op_code),
FOREIGN KEY (Admission_id) references ADMISSION(Admission_id)
);
CREATE TABLE OBSERVATION(
   Admission_id  NUMBER(3),
   Observ_date DATE,
   Observ_time   NUMBER(4),
   Observ_type   CHAR(10),
   Observ_value  NUMBER(4),
   Staff_id      NUMBER(3),
PRIMARY KEY (Admission_id,Observ_date,Observ_time,Observ_type),
FOREIGN KEY (Admission_id) references ADMISSION(Admission_id),
FOREIGN KEY (Staff_id) references STAFF(Person_id)
);
INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (1, 1000, 'Temp', current_date, 1000);
1 rows affected

INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (1, 1100, 'Temp', current_date, 1100);
1 rows affected

INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (2, 2000, 'Temp', current_date, 2000);
1 rows affected

INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (2, 2100, 'Temp', current_date, 2100);
1 rows affected

SELECT patient_id AS "Patient ID"
     , CONCAT(CONCAT(first_name,' '),surname) AS "Patient Name"
     , admission_date AS "Date Admitted"
     , discharge_date AS "Date Discharged"
     , ROUND(AVG(observ_value),2) AS "Average Temperature"
     , daily_charge AS "Expenses"
  FROM person
  JOIN admission
    ON person.person_id = admission.patient_id
   AND admission.admission_id NOT IN (SELECT operation.admission_id FROM operation)
   AND discharge_date IS NOT NULL
  JOIN ward
    ON ward.ward_code = admission.ward_code
  JOIN observation
    ON observ_type = 'Temp'
   AND observation.admission_id = admission.admission_id
 GROUP BY patient_id, admission_date, discharge_date, daily_charge, first_name, surname
 ORDER BY admission_date
;


Patient ID
Patient Name
Date Admitted
Date Discharged
Average Temperature
Expenses


1
First1 Last1
17/12/2022
17/12/2022
1050
100

2
First2 Last2
17/12/2022
17/12/2022
2050
120


-- Original SQL with wrong join criteria
SELECT patient_id AS "Patient ID"
     , CONCAT(CONCAT(first_name,' '),surname) AS "Patient Name"
     , admission_date AS "Date Admitted"
     , discharge_date AS "Date Discharged"
     , ROUND(AVG(observ_value),2) AS "Average Temperature"
     , daily_charge AS "Expenses"
  FROM person
     , ward
     , admission
     , observation
 WHERE person.person_id = admission.patient_id
   AND admission.admission_id NOT IN (SELECT operation.admission_id FROM operation)
   AND ward.ward_code = admission.ward_code
   AND observ_type = 'Temp'
   AND discharge_date IS NOT NULL
 GROUP BY patient_id, admission_date, discharge_date, daily_charge, first_name, surname
 ORDER BY admission_date
;


Patient ID
Patient Name
Date Admitted
Date Discharged
Average Temperature
Expenses


1
First1 Last1
17/12/2022
17/12/2022
1550
100

2
First2 Last2
17/12/2022
17/12/2022
1550
120
Patient ID	Patient Name	Date Admitted	Date Discharged	Average Temperature	Expenses
1	First1 Last1	17/12/2022	17/12/2022	1050	100
2	First2 Last2	17/12/2022	17/12/2022	2050	120