MySQL :
- To install on Mac OS, you can follow this YouTube video. Link
- To install on Windows OS, you can follow this YouTube video. Link
Note :- You can follow any video or other method to download and install. I just provided an example here to install. Install carefully as per your system requirements.
- I have used MySQL Workbench 8.0 version 8.0.41.
In this Repo, I have uploaded day-wise challenges and the solution using my approach.
The main aim is to improve SQL knowledge by doing problems, improving our approach and improving logic building
About the dataset: Link
It's a hospital data set. There are a total of 4 tables.
1. hospital_staff.csv – List of hospital staff
2. hospital_patients.csv – Patient records
3. hospital_service_weekly.csv – Weekly service-level data
4. hospital_staff_schedule.csv – Weekly schedule
First, create a database and table structure using the Code
| Days | Challenege | Link |
|---|---|---|
| 1 | List all unique hospital services available in the hospital. | Code |
| 2 | Find all patients admitted to the 'Surgery' service with a satisfaction score below 70, showing their patient_id, name, age, and satisfaction score. | Code |
| 3 | Retrieve the top 5 weeks with the highest patient refusals across all services, showing week, service, patients_refused, and patients_request. Sort by patients_refused in descending order. | Code |
| 4 | Find the 3rd to 7th highest patient satisfaction scores from the patients table, showing patient_id, name, service, and satisfaction. Display only these 5 records. | Code |
| 5 | Calculate the total number of patients admitted, total patients refused, and the average patient satisfaction across all services and weeks.Round the average satisfaction to 2 decimal places. | Code |
| 6 | For each hospital service, calculate the total number of patients admitted, total patients refused, and the admission rate ( percentage of requests that were admitted ). Order by admission rate descending. | Code |
| 7 | Identify services that refused more than 100 patients in total and had an average patient satisfaction below 80. Show service name, total refused, and average satisfaction. | Code |
| 8 | Create a patient summary that shows patient_id, full name in uppercase, service in lowercase, age category (if age >= 65 then 'Senior', if age >= 18 then 'Adult', else 'Minor'), and name length. Only show patients whose name length is greater than 10 characters. | Code |