How Do I Populate The Table Using JOIN Statement To Obtain The Table As Listed As The Table Below? Description For Columns In The Tables: CasePatient All Columns Are Required,

How do I populate the table using JOIN statement to obtain the table as listed as the table below?

Description for columns in the tables: CasePatient All columns are required, caseNumber is a whole number starting from 1 with step increment 1 and is auto-generated. patientAge is an whole number from 0 and 150. gender is one character, and is limited to the letter M or F.

Drug All columns are required, drugCode is exactly 4 characters starting with two letters (A-Z) followed by 2 digits (0-9). maxDailyDosage is a decimal number that is greater than 0.

TreatementGiven All columns except dateStopped are required, runningNumber is a whole number, and starts from 1 with step increment of 1 for every new patient. The auto-generation of runningNumber will be implemented using a trigger. dateStarted and dateStopped are dates. dateStopped is on or after dateStarted. dosage is a decimal number that is greater than 0. frequency is a whole number greater than 0. frequencyInterval is a whole number which is 1 for daily, 7 for weekly, 14 for fortnightly, 30 for monthly, 90 for quarterly, 180 for half-yearly and 365 for yearly.

TreatmentResponse All columns are required. responseNumber is a whole number starting from 1 with step increment 1 and is auto-generated. dateRecorded is a date with today as the default value. outcome is exactly 17 character long, and is one of these values: negative reaction, no change, slightly improved, improved, greatly improved.

SQL code as follows:

CREATE TABLE CasePatient ( caseNumber INT NOT NULL IDENTITY(1,1), patientAge INT NOT NULL, gender CHAR(1), CONSTRAINT CaseNumberPK PRIMARY KEY (caseNumber), CONSTRAINT AgeCheck CHECK (patientAge>= 0 AND patientAge=> CONSTRAINT GenderCheck CHECK (gender IN ('F', 'M')) );

CREATE TABLE Drug ( drugCode CHAR(4) NOT NULL, maxDailyDosage DECIMAL(8,2) NOT NULL, CONSTRAINT DrugCodePK PRIMARY KEY(drugCode), CONSTRAINT DrugCodeCheck CHECK (drugCode LIKE '[A-Z][A-Z][0-9][0-9]'), CONSTRAINT MaxDailyDosageCheck CHECK (maxDailyDosage> 0) );

CREATE TABLE TreatmentGiven ( caseNumber INT NOT NULL, runningNumber INT NOT NULL, dateStarted DATE NOT NULL, dateStopped DATE, dosage DECIMAL(8,2) NOT NULL, frequency INT NOT NULL, frequencyInterval INT NOT NULL, drugCode CHAR(4) NOT NULL, CONSTRAINT NumberPK PRIMARY KEY(caseNumber, runningNumber), CONSTRAINT DateStoppedCheck CHECK (dateStopped>= dateStarted), CONSTRAINT DosageCheck CHECK (dosage > 0), CONSTRAINT FrequencyCheck CHECK (frequency > 0), CONSTRAINT FrequencyIntervalCheck CHECK (frequencyInterval IN (1, 7, 14, 30, 90, 180, 365)), CONSTRAINT TreatmentGivenCasePatientFK FOREIGN KEY (caseNumber) REFERENCES CasePatient (caseNumber) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT TreatmentGivenDrugFK FOREIGN KEY (drugCode) REFERENCES Drug (drugCode) ON DELETE NO ACTION ON UPDATE CASCADE );

CREATE TABLE TreatmentResponse ( responseNumber INT NOT NULL IDENTITY(1,1), dateRecorded DATE NOT NULL DEFAULT SYSDATETIME(), outcome CHAR(17) NOT NULL, caseNumber INT NOT NULL, runningNumber INT NOT NULL, CONSTRAINT ResponseNumberPK PRIMARY KEY(responseNumber), CONSTRAINT OutcomeCheck CHECK (outcome IN ('negative reaction', 'no change', 'slightly improved', 'improved', 'greatly improved')), CONSTRAINT TreatmentResponseTreatmentGivenFK FOREIGN KEY (caseNumber, runningNumber) REFERENCES TreatmentGiven (caseNumber, runningNumber) ON DELETE NO ACTION ON UPDATE NO ACTION );

SET IDENTITY_INSERT CasePatient ON; INSERT INTO CasePatient (caseNumber, patientAge, gender) VALUES (1, 62, 'M'), (2, 58, 'F'), (3, 47, 'M'), (4, 60, 'M'), (5, 70, 'M'), (6, 56, 'F'), (7, 55, 'F'), (8, 74, 'M'), (9, 43, 'F'), (10, 45, 'M'), (11, 73, 'F'); SET IDENTITY_INSERT CasePatient OFF;

INSERT INTO Drug (drugCode, maxDailyDosage) VALUES ('WA15', 7.0), ('AU02', 2.5), ('DR06', 5.0), ('MA01', 4.0), ('PN05', 1.2);

INSERT INTO TreatmentGiven (caseNumber, runningNumber, dateStarted, dateStopped, dosage, frequency, frequencyInterval, drugCode) VALUES (1, 1, CAST('2022-01-01' AS DATE), CAST('2022-01-04' AS DATE), 0.5, 3, 1, 'AU02'), (1, 2, CAST('2022-01-05' AS DATE), CAST('2022-01-19' AS DATE), 5.0, 1, 7, 'DR06'), (2, 1, CAST('2022-01-03' AS DATE), CAST('2022-01-10' AS DATE), 0.5, 3, 1, 'AU02'), (4, 1, CAST('2022-01-05' AS DATE), CAST('2022-01-11' AS DATE), 0.5, 3, 1, 'AU02'), (4, 2, CAST('2022-01-13' AS DATE), CAST('2022-01-13' AS DATE), 2.0, 2, 1, 'MA01'), (4, 3, CAST('2022-01-21' AS DATE), CAST('2022-01-23' AS DATE), 0.5, 3, 1, 'AU02'), (5, 1, CAST('2022-01-15' AS DATE), CAST('2022-01-16' AS DATE), 0.5, 3, 1, 'AU02'), (5, 2, CAST('2022-01-17' AS DATE), CAST('2022-01-17' AS DATE), 5.0, 1, 7, 'DR06'), (5, 3, CAST('2022-01-24' AS DATE), CAST('2022-01-26' AS DATE), 2.0, 2, 1, 'MA01'), (6, 1, CAST('2022-01-16' AS DATE), null, 5.0, 1, 7, 'DR06'), (7, 1, CAST('2022-01-16' AS DATE), null, 5.0, 1, 7, 'DR06'), (8, 1, CAST('2022-01-17' AS DATE), CAST('2022-01-20' AS DATE), 0.5, 3, 1, 'AU02'), (8, 2, CAST('2022-01-21' AS DATE), CAST('2022-01-21' AS DATE), 5.0, 1, 7, 'DR06'), (8, 3, CAST('2022-01-29' AS DATE), null, 2.0, 2, 1, 'MA01'), (11, 1, CAST('2022-01-18' AS DATE), CAST('2022-01-25' AS DATE), 0.5, 3, 1, 'AU02'), (11, 2, CAST('2022-01-26' AS DATE), CAST('2022-01-26' AS DATE), 5.0, 1, 7, 'DR06'), (11, 3, CAST('2022-02-03' AS DATE), null, 2.0, 2, 1, 'MA01'), (11, 4, CAST('2022-02-05' AS DATE), null, 5.0, 1, 14, 'PN05');

SET IDENTITY_INSERT TreatmentResponse ON; INSERT INTO TreatmentResponse (responseNumber, caseNumber, runningNumber, dateRecorded, outcome) VALUES (1, 1, 1, CAST('2022-01-04' AS DATE), 'no change'), (5, 1, 2, CAST('2022-01-11' AS DATE), 'slightly improved'), (9, 1, 2, CAST('2022-01-19' AS DATE), 'improved'), (17, 1, 2, CAST('2022-01-26' AS DATE), 'greatly improved'), (2, 2, 1, CAST('2022-01-06' AS DATE), 'improved'), (4, 2, 1, CAST('2022-01-10' AS DATE), 'greatly improved'), (3, 4, 1, CAST('2022-01-07' AS DATE), 'no change'), (6, 4, 1, CAST('2022-01-12' AS DATE), 'slightly improved'), (7, 4, 2, CAST('2022-01-16' AS DATE), 'improved'), (10, 4, 2, CAST('2022-01-19' AS DATE), 'improved'), (13, 4, 3, CAST('2022-01-23' AS DATE), 'greatly improved'), (8, 5, 1, CAST('2022-01-16' AS DATE), 'negative reaction'), (16, 5, 2, CAST('2022-01-24' AS DATE), 'improved'), (19, 5, 3, CAST('2022-01-27' AS DATE), 'greatly improved'), (14, 6, 1, CAST('2022-01-23' AS DATE), 'slightly improved'), (22, 6, 1, CAST('2022-01-30' AS DATE), 'improved'), (15, 7, 1, CAST('2022-01-23' AS DATE), 'slightly improved'), (23, 7, 1, CAST('2022-01-30' AS DATE), 'improved'), (11, 8, 1, CAST('2022-01-20' AS DATE), 'negative reaction'), (21, 8, 2, CAST('2022-01-28' AS DATE), 'slightly improved'), (24, 8, 3, CAST('2022-02-02' AS DATE), 'slightly improved'), (26, 8, 3, CAST('2022-02-07' AS DATE), 'improved'), (12, 11, 1, CAST('2022-01-22' AS DATE), 'no change'), (18, 11, 1, CAST('2022-01-26' AS DATE), 'negative reaction'), (20, 11, 2, CAST('2022-01-07' AS DATE), 'slightly improved'), (25, 11, 3, CAST('2022-02-05' AS DATE), 'improved'); SET IDENTITY_INSERT TreatmentResponse OFF;