Describe the type of relationship (One-to-Many, Many-to-Many, One-to-One) between the following tables, using the Database Diagram (SoITClinic) as a guide:

 

Patients and Admissions (please supply the primary key as well as any foreign key if applicable)

Admissions and diagnostics (please supply the primary key as well as any foreign key if applicable)

Doctors and Admissions (please supply the primary key as well as any foreign key if applicable).

 

 

2. Paste a screenshot of all the columns and rows from the doctors' table below:

 

Codes here:

 

 

 

Screenshot here:

3. Make a clone of the Physicians table, rename it DoctorsCopy, and add the three newest doctors listed below to that DoctorsCopy Table. Then, display all the columns and rows in the new DoctorsCopy table and paste a screenshot here.

New doctors to be added:

Justin Lee is a Cardiologist with the DoctorId 3215.

Lauren West is a Gynecologist with the DoctorId 4523.

Marie Depaul is a Urologist with the DoctorId 3000.

 

 

Codes here:

 

 

 

Screenshot here:

 

 

4. Show the columns for all the patients with the first name that begins and also ends with the letter "a" 

 

Codes here:

 

 

 

Screenshot here:

 

5. i NEED TO HAVE a SELECT statement that displays only the first name, last name, and area codes for all patients:

a. Don't show the entire phone number (Display only the AreaCodes without the parentheses).

 

 

Codes here:

 

 

Screenshot here:

 

6. Change Kelly Lawson's DoctorID to "2021" in the DoctorsCopy table.

Attn: (The table might contain multiple doctors named Kelly Lawson) 

 

Codes here:

 

 

 

Screenshot here:

 

 

 

7. Have a SELECT statement that only shows columns from the Admissions and Doctors tables using a JOIN statement.

Attn: A JOIN statement is required

 

Codes here:

 

 

Screenshot here:

 

 

 

 

8. Have a subquery that displays the columns listed below: All patients diagnosed with tuberculosis in the Admissions table have a PatId, firstname, and lastname.

Attn: A Subquery is required

 

Codes here:

 

 

 

 

Screenshot here:

 

 

 

 

 

9. HAVE a SELECT statement that counts the number of diagnoses in the Admissions table while avoiding duplicates in the result set. (NumberOfDiagnostics is the temporary column's name.)

 

Codes here:

 

 

 

Screenshot here:

 

 

 

10. HAVE a SELECT statement that displays the complete SoITClinic Database's columns and rows using a JOIN statement.

Attn: A JOIN statement is required

 

 

Codes here:

 

 

 

Screenshot here:

Transcribed Image Text:

Diagnostics DiagNo Description Cost CO Admissions AdmitNo Patid Diag_code Admit_Doct Admission Date Discharge_Date Patients Patld first Name last Name DOB Address City State Zip Phone -0 Doctors Doctor_ld first Name last Name speciality Diagnostics DiagNo Description Cost CO Admissions AdmitNo Patid Diag_code Admit_Doct Admission Date Discharge_Date Patients Patld first Name last Name DOB Address City State Zip Phone -0 Doctors Doctor_ld first Name last Name speciality