IS3030 Database Design & Tools Spring 2021 Using The Rel ✓ Solved

IS3030 Database Design & Tools Spring 2021 Using the rel

Using the relational database for the 'Patient Care System' from the starter database provided, construct SQL queries to answer the following questions:

  1. Produce a list of Physician IDs of all physicians, sorted by last name alphabetically, showing their ID, Specialty, and Last Name. Also, show the number of patients to whom each physician is assigned to. [Use joins]
  2. Find the oldest and youngest patients admitted into the hospital, grouped by gender (male and female). Names of patients do not need to be listed. Give the query columns appropriate titles.
  3. Produce a list of all medication codes used in the clinic, sorted by Medication_Desc alphabetically. Also show the number of patients to whom each medication has been prescribed.
  4. List the medication prescribed for patients who are in for ‘Pneumonia’; Show patient first and last name, date of admission, room number, medication code, and medication dosage. [Use joins]
  5. List the doctor(s) that are attending to patients diagnosed with ‘Cardiac Arrest’ by their name (first & last) and Physician_ID. [Use nested sub queries]
  6. Produce a list of patients who were prescribed ‘Penicillin’. The list should contain the patient’s first and last name, dosage, illness description, and the last name and Physician ID of their doctor. [Use joins]
  7. List names (first and last), specialty and Physician_ID of doctors who have a patient that is in for ‘Work Stress’. [Write nested sub queries]
  8. List names (first and last), specialties, and IDs of doctors who have no patients. [Use NOT IN query]
  9. List the patients who do not have a prescription. The list should show the patient's number, first name, last name, age, attending physician’s last name and physician ID, and illness description. [Use combination of Join and nested sub query]
  10. List the physicians name (first and last), telephone extension, and specialty for those who are assigned the role ‘Specialty’. Make sure this list is distinct. [Use join query]

Paper For Above Instructions

The objective of this paper is to construct SQL queries for the Patient Care System database that comply with the requirements outlined in the assignment. SQL (Structured Query Language) is a domain-specific language used in programming and managing relational databases. By leveraging SQL, we can perform various data retrieval tasks, transformations, and manipulations in a systematic way. Below are the SQL queries for each of the ten tasks laid out in the assignment.

Query 1: List of Physicians

SELECT Physician.Physician_ID, Physician.Last_Name, Physician.Specialty, COUNT(Patient.Patient_ID) AS Number_of_Patients

FROM Physician

LEFT JOIN Patient ON Physician.Physician_ID = Patient.Physician_ID

GROUP BY Physician.Physician_ID, Physician.Last_Name, Physician.Specialty

ORDER BY Physician.Last_Name;

Query 2: Oldest and Youngest Patients

SELECT Gender, MIN(Date_of_Admission) AS Oldest_Patient_Date, MAX(Date_of_Admission) AS Youngest_Patient_Date

FROM Patient

GROUP BY Gender;

Query 3: Medication Codes

SELECT Medication.Medication_Code, Medication.Medication_Desc, COUNT(Prescription.Patient_ID) AS Number_of_Patients

FROM Medication

LEFT JOIN Prescription ON Medication.Medication_Code = Prescription.Medication_Code

GROUP BY Medication.Medication_Code, Medication.Medication_Desc

ORDER BY Medication.Medication_Desc;

Query 4: Medication for Pneumonia Patients

SELECT Patient.First_Name, Patient.Last_Name, Patient.Date_of_Admission, Patient.Room_Number, Prescription.Medication_Code, Prescription.Dosage

FROM Patient

JOIN Prescription ON Patient.Patient_ID = Prescription.Patient_ID

WHERE Patient.Illness_Description = 'Pneumonia';

Query 5: Doctors Attending to Cardiac Arrest Patients

SELECT Physician.First_Name, Physician.Last_Name, Physician.Physician_ID

FROM Physician

WHERE Physician.Physician_ID IN (

SELECT Prescription.Physician_ID FROM Prescription

JOIN Patient ON Prescription.Patient_ID = Patient.Patient_ID

WHERE Patient.Illness_Description = 'Cardiac Arrest'

);

Query 6: Patients Prescribed Penicillin

SELECT Patient.First_Name, Patient.Last_Name, Prescription.Dosage,

Patient.Illness_Description, Physician.Last_Name, Prescription.Physician_ID

FROM Patient

JOIN Prescription ON Patient.Patient_ID = Prescription.Patient_ID

JOIN Physician ON Prescription.Physician_ID = Physician.Physician_ID

WHERE Prescription.Medication_Desc = 'Penicillin';

Query 7: Doctors with Patients in for Work Stress

SELECT Physician.First_Name, Physician.Last_Name, Physician.Physician_ID, Physician.Specialty

FROM Physician

WHERE Physician.Physician_ID IN (

SELECT DISTINCT Prescription.Physician_ID

FROM Prescription

JOIN Patient ON Prescription.Patient_ID = Patient.Patient_ID

WHERE Patient.Illness_Description = 'Work Stress'

);

Query 8: Doctors with No Patients

SELECT Physician.First_Name, Physician.Last_Name, Physician.Physician_ID, Physician.Specialty

FROM Physician

WHERE Physician.Physician_ID NOT IN (SELECT DISTINCT Prescription.Physician_ID FROM Prescription);

Query 9: Patients Without Prescriptions

SELECT Patient.Patient_Number, Patient.First_Name, Patient.Last_Name,

Patient.Age, Physician.Last_Name, Prescription.Physician_ID, Patient.Illness_Description

FROM Patient

LEFT JOIN Prescription ON Patient.Patient_ID = Prescription.Patient_ID

LEFT JOIN Physician ON Prescription.Physician_ID = Physician.Physician_ID

WHERE Prescription.Patient_ID IS NULL;

Query 10: Physicians Assigned the Role 'Specialty'

SELECT DISTINCT Physician.First_Name, Physician.Last_Name, Physician.Telephone_Extension, Physician.Specialty

FROM Physician

JOIN Roles ON Physician.Role_ID = Roles.Role_ID

WHERE Roles.Role_Description = 'Specialty';

These SQL queries are structured according to the requirements of the Patient Care System database. Each query is designed to address specific questions about the information stored within the database, using appropriate SQL constructs such as JOINs, nested queries, and grouping functions. It is critical to ensure that the SQL statements follow the ANSI/ISO standards and are not generated from Access to meet the assignment criteria.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.
  • Ullman, J. D., & Widom, J. (2011). A First Course in Database Systems. Pearson.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020). Database System Concepts. McGraw-Hill.
  • Databasestar. (2021). SQL Joins Explained. Retrieved from https://databasestar.com/sql-joins/
  • W3Schools. (2022). SQL JOIN. Retrieved from https://www.w3schools.com/sql/sql_join.asp
  • Mode Analytics. (2021). The Ultimate Guide to SQL JOINs. Retrieved from https://mode.com/sql-tutorial/sql-joins/
  • Oracle Corporation. (2022). SQL Language Reference. Retrieved from https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/index.html
  • Microsoft Docs. (2021). SQL Server Documentation. Retrieved from https://docs.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver15
  • Gookin, D. (2011). SQL for Dummies. Wiley.
  • Weiss, S. (2018). Data Structures and Algorithm Analysis in C++. Pearson.

Related Assignments