Objective:

You are required to address a set of Data Query Language (DQL) tasks for a Hospital Management System using the tables provided. Each task focuses on different aspects of querying data from the system using SELECT statements with a variety of conditions, functions, and joins. Students are required to utilize the tables created during the Chapter 5 DDL assignment.

1. SELECT Command

• Select all columns from the PATIENT table.
• Select the first_name, last_name, and email of all patients.
• Select all specialization from the DOCTOR table.
• Select the appointment_date and status from the APPOINTMENT table.

2. WHERE Command

• Select all patients where date_of_birth is before '1990-01-01'.
• Select all doctors whose specialization is 'Cardiology'.
• Select all appointments where status is 'Scheduled'.
• Select all patient admits where treatment_start_date is after '2022-01-01'.

3. ORDER BY Command

• Select all patients and order them by last_name in ascending order.
• Select all doctors and order them by first_name in descending order.
• Select all appointments and order them by appointment_date in descending order.
• Select all medications and order them by medication_name in ascending order.

4. LIMIT Command

• Select the first 10 patients from the PATIENT table.
• Select the first 5 doctors from the DOCTOR table.
• Select the top 3 appointments ordered by appointment_date.
• Select the first 5 prescriptions from the PRESCRIPTION table.

5. DISTINCT Command

• Select distinct specialization from the DOCTOR table.
• Select distinct status from the APPOINTMENT table.
• Select distinct department_name from the DEPARTMENT table.
• Select distinct last_name from the PATIENT table.

6. GROUP BY Command

• Group by specialization from the DOCTOR table and count the number of doctors per specialization.
• Group by status in the APPOINTMENT table and count the number of appointments per status.
• Group by department_name from the ROOM table and calculate the number of rooms in each type.
• Group by medication_type in the MEDICATION table and calculate the total number of medications per type.

7. HAVING Command

• Select specialization from the DOCTOR table and group by it, having more than 2 doctors per specialization.
• Group by department_name and calculate the number of doctors in each department, having more than 3 doctors.
• Group by status in the APPOINTMENT table and filter the groups having more than 5 appointments.
• Group by medication_type and calculate the total medications, having more than 3 medications per type.

8. INNER JOIN Command

• Select all patients and their corresponding appointments using INNER JOIN between PATIENT and APPOINTMENT.
• Select all treatments along with the corresponding doctor details using INNER JOIN between PATIENT_ADMIT and DOCTOR.
• Select all prescriptions along with the corresponding medication details using INNER JOIN between PRESCRIPTION and MEDICATION.
• Select all doctors and the departments they belong to using INNER JOIN between DOCTOR and DOCTOR_DEPARTMENT.

9. LEFT JOIN Command

• Select all doctors and their corresponding departments using LEFT JOIN, including doctors without any departments.
• Select all patients and their appointments using LEFT JOIN, including patients without appointments.
• Select all nurses and their assigned rooms using LEFT JOIN, including nurses without assigned rooms.
• Select all medication and the corresponding prescriptions using LEFT JOIN, including medication without prescriptions.

10. COUNT, SUM, AVG Command

• Count the total number of patients in the PATIENT table.
• Count the number of distinct specialization in the DOCTOR table.
• Calculate the sum of total_amount in the BILLING table.
• Calculate the average dosage for medications in the MEDICATION table.

11. CASE Command

• Select all appointments and use a CASE statement to show 'Upcoming' if the appointment_date is after the current date, and 'Past' otherwise.
• Select all patients and categorize them as 'Senior' if their date_of_birth is before '1950-01-01', otherwise 'Adult'.
• Use a CASE statement to categorize patient admits as 'Recent' if the treatment_start_date is within the last year, otherwise 'Old'.
• Use a CASE statement in the APPOINTMENT table to label appointments as 'Critical' if the patient has undergone multiple doctor visits.

12. EXISTS and NOT EXISTS Command

• Select all patients where an appointment exists in the APPOINTMENT table using EXISTS.
• Select all doctors where no appointments exist in the APPOINTMENT table using NOT EXISTS.

13. SUBQUERY Command

• Select all doctors whose doctor_id is in the result of a subquery selecting doctor_id from the APPOINTMENT table where status is 'Scheduled'.
• Select all medications where the medication_id is in the result of a subquery selecting medication_id from the PRESCRIPTION table.
• Select all patients who have not scheduled an appointment using a subquery.
• Select all patient admits where the patient has been admitted more than once using a subquery.

14. RANK and DENSE_RANK Command

• Rank patients based on the number of appointments using RANK().
• Use DENSE_RANK() to rank doctors based on their specialization.
• Rank medications by their dosage using RANK().
• Use DENSE_RANK() to rank treatments by treatment_date.

15. PIVOT and UNPIVOT

• Pivot the APPOINTMENT data by status and patient_id.
• Unpivot the treatment details to show the treatment_description and doctor_id in a single row per patient.
• Pivot the room data grouped by room_type and daily_rate.
• Unpivot medication data by medication_name and dosage.

16. UNION and UNION ALL Command

• Select all doctors from different departments using UNION ALL.
• Use UNION to combine scheduled and completed appointments.
• Use UNION ALL to select all patient admits from two different years.

17. COALESCE, IFNULL, NULLIF Command

• Select all patients and use COALESCE to replace null emails with 'No Email'.
• Use IFNULL to replace null phone_number values in the DOCTOR table.
• Use NULLIF to compare treatment_start_date and appointment_date in the PATIENT_ADMIT table and return NULL if they are the same.
• Select all nurses and replace null hire_date with 'Unknown' using COALESCE.

18. STRING Functions

• Select all patients' first_name in uppercase using the UPPER() function.
• Select all doctors' specialization in lowercase using the LOWER() function.
• Use CONCAT() to combine the first_name and last_name of doctors.
• Select all patients' last_name and find the length of the name using LENGTH().

19. DATE Functions

• Select all patients and show their date_of_birth formatted as 'YYYY-MM-DD' using TO_CHAR().
• Add 1 year to all appointment_date values in the APPOINTMENT table using AGE().
• Subtract 1 month from all treatment_start_date values using INTERVAL.
• Select all nurses and extract the year from their hire_date using EXTRACT().

20. NUMERIC Functions

• Select the dosage from the MEDICATION table and round it to the nearest integer using ROUND().
• Select all total_amount from the BILLING table and use CEIL() to round up.
• Use FLOOR() to round down the total_amount in the BILLING table.
• Select the highest total_amount from the BILLING table using MAX().

21. CAST and CONVERT Command

• Select all patients and cast the patient_id as a string using CAST().
• Convert total_amount in the BILLING table to DECIMAL using CONVERT().
• Cast treatment_id in the TREATMENT table to INTEGER.
• Convert the appointment_date from the APPOINTMENT table into TEXT.

22. CONCAT and CONCAT_WS Functions

• Use CONCAT() to join first_name and last_name with a space in between in the DOCTOR table.
• Use CONCAT_WS() to combine the department_name and location with a comma in the DEPARTMENT table.
• Use CONCAT() to combine the room_number and room_type from the ROOM table.
• Use CONCAT_WS() to combine first_name, last_name, and email from the PATIENT table.

23. LIKE and NOT LIKE

• Select patients whose email ends with 'hospital.com' using LIKE.
• Select doctors where specialization contains 'Surgery' using LIKE.
• Select patients whose last_name does not start with 'J' using NOT LIKE.
• Select departments where department_name contains 'Oncology' using LIKE.

24. EXISTS and NOT EXISTS

• Select doctors where appointments exist in the APPOINTMENT table using EXISTS.
• Select patients where no prescriptions exist using NOT EXISTS.
• Select rooms where treatments exist using EXISTS.
• Select departments where no doctors are assigned using NOT EXISTS.

25. JOIN Commands

• Select all patients and their corresponding appointments using INNER JOIN between the PATIENT and APPOINTMENT tables.
• Select all prescriptions and their corresponding medications using INNER JOIN between PRESCRIPTION and MEDICATION.
• Select all nurses and their assigned departments using LEFT JOIN between NURSE and DEPARTMENT.
• Select all treatments and their corresponding doctors using INNER JOIN between TREATMENT and DOCTOR.

26. BETWEEN Command

• Select all appointments where the appointment_date is between '2023-01-01' and '2023-12-31'.
• Select all treatments where the treatment_date is between '2022-01-01' and '2022-12-31'.
• Select all patients where the date_of_birth is between '1960-01-01' and '2000-12-31'.
• Select all rooms where the capacity is between 2 and 10.

27. IN and NOT IN Command

• Select all patients where the patient_id is in (1, 2, 3).
• Select all doctors where the specialization is in ('Cardiology', 'Oncology').
• Select all appointments where the status is not in ('Cancelled', 'No Show').
• Select all departments where the department_id is in (1, 2, 5).

28. UNION Command

• Select patients from PATIENT and HOSPITAL_PATIENT using UNION.
• Select doctors from two different departments using UNION.
• Use UNION to combine appointments from two different years.
• Use UNION ALL to combine treatments from multiple departments.

29. ARRAY Command

• Select all specialization and convert it into an array using ARRAY_AGG().
• Use UNNEST() to expand arrays from the DEPARTMENT table.
• Convert patient names into an array using ARRAY_AGG().
• Use ARRAY functions to select and manipulate data from the TREATMENT table.

GOOD LUCK WITH YOUR ASSIGNMENT!!!

Don't forget to contact us if you need any further assistance with your assignments, and most importantly, for a manual review and approval of your work.

Sample ERD Data Model for Hospital Management System