An SQL database that contains data on patients, nurses, hospital departments, diseases, medication, and billing. This code also contains SQL queries extracting specific and relevant data from our database.
SQL Blog Post »
Table of Contents
-
About The Project
- Exploring the Data
- Contact Me
This is a Hospital Database built with SQL. It contains several tables with data related to hospital departments, nurses, patients, etc. Many of the tables relate to one another, creating a complex SQL schema. After creating the database, I explored the data using SQL queries that could provide interesting insights into hospital employee and patient trends. The SQL queries include simple to advanced SQL, ranging from Joins, Aggregates, Window Functions, and Subqueries. Listed below are the schema of all of the tables in the hospital database:
| Column Name |
Description |
| patient_id |
Uniquely identifies each patient (Primary Key) |
| first_name |
First name of patient |
| last_name |
Last name of patient |
| birthdate |
Patient date of birth |
| sex |
Patient sex |
| weight |
Patient weight |
| nurse_id |
Nurse caring for patient (Foreign Key) |
| department_id |
Department caring for patient (Foreign Key) |
| disease_id |
Patient's disease |
| Column Name |
Description |
| nurse_id |
Uniquely identifies each nurse (Primary Key) |
| first_name |
First name of nurse |
| last_name |
Last name of nurse |
| salary |
Nurse salary |
| department_id |
Nurse's department (Foreign Key) |
| birthdate |
Nurse date of birth |
| sex |
Nurse sex |
| Column Name |
Description |
| department_id |
Uniquely identifies each hospital department (Primary Key) |
| department_name |
Name of department |
| head_id |
Head Nurse of department (Foreign Key) |
| Column Name |
Description |
| disease_id |
Uniquely identifies each disease (Primary Key) |
| disease_name |
Name of disease |
| Column Name |
Description |
| disease_id |
Uniquely identifies disease for this bill (First Primary Key/ Foreign Key) |
| department_id |
Uniquely identifies department for this bill (Second Primary Key/ Foreign Key) |
| total_bill |
Bill amount |
| Column Name |
Description |
| disease_id |
Uniquely identifies disease for this medication (First Primary Key/ Foreign Key) |
| medication_name |
Uniquely identifies medication name (Second Primary Key) |
| dosage_mg |
Daily medication dosage |
(back to top)
(back to top)
After creating the database, I wanted to explore the data further to identify trends and extract important information. I did this using SQL queries. Some of the questions that are explored in this project are listed below :
- Which patients have cancer?
- How many patients are being treated for COVID-19?
- What is the average weight for patients who suffered a heart attack?
- What are the common diseases for patients over 60?
- What is the average salary of male nurses vs. the average salary of female nurse?
- What is the average age of patients in each department?
- Which patients under 18 have the flu?
- How many milligrams of Ciprofloxacin total will be admistered in a day?
- Which departments have the highest average bill?
- Which nurses are treating patients with pneumonia?
- Are there more females or males in Intensive Care?
- Which disease does the eldest patient in the hospital have?
Questions like these helped me to get a better understanding of the hospital data that I'm working with and allowed me to identify trends and patterns for further analysis or to influence future decisions in the hospital.
(back to top)
(back to top)