Scribd
Upload
2 views3 pages

SQL Assignment

The document outlines a 30-day SQL micro course assignment consisting of ten tasks related to hospital data analysis. Each task includes an SQL query to retrieve specific information, such as total patients, average doctors per hospital, and monthly medical expenses. The queries cover various aspects of hospital operations, including patient counts, medical expenses, and length of stay metrics.

Uploaded by

a2024cse10187
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views3 pages

SQL Assignment

The document outlines a 30-day SQL micro course assignment consisting of ten tasks related to hospital data analysis. Each task includes an SQL query to retrieve specific information, such as total patients, average doctors per hospital, and monthly medical expenses. The queries cover various aspects of hospital operations, including patient counts, medical expenses, and length of stay metrics.

Uploaded by

a2024cse10187
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

30 Days SQL Micro Course Assignment

1. Total Number of Patients

o Write an SQL query to find the total number of patients across all hospitals.

Answer-

select sum(patients_count)as total_patients

from hospital_data;

2. Average Number of Doctors per Hospital

o Retrieve the average count of doctors available in each hospital.

Answer-

select hospital_Name,avg(doctors_count)as average_doctors

from hospital_data

group by hospital_Name;

3. Top 3 Departments with the Highest Number of Patients

o Find the top 3 hospital departments that have the highest number of patients.

Answer-

SELECT Department,

SUM(Patients_Count) AS Total_Patients

FROM Hospital_Data

GROUP BY Department

ORDER BY Total_Patients DESC

LIMIT 3;

4. Hospital with the Maximum Medical Expenses

o Identify the hospital that recorded the highest medical expenses.

Answer-

select hospital_Name,

sum(medical_expenses)as total_expenses

from hospital_Data

group by hospital_Name
order by total_expenses desc

limit 1;

5. Daily Average Medical Expenses

o Calculate the average medical expenses per day for each hospital.

Answer-

SELECT Hospital_Name,

AVG(Medical_Expenses / ((Discharge_Date - Admission_Date) + 1))as Avg_Expenses_Per_Day


FROM Hospital_Data

GROUP BY Hospital_Name;

6. Longest Hospital Stay

o Find the patient with the longest stay by calculating the difference between Discharge Date and
Admission Date.

Answer-

SELECT Hospital_Name, Department, Patients_Count,

((Discharge_Date- Admission_Date) + 1) AS Stay_Duration

FROM Hospital_Data

ORDER BY Stay_Duration DESC

LIMIT 1;

7. Total Patients Treated Per City

o Count the total number of patients treated in each city

Answer-

select Location as city,

sum(Patients_Count)as Total_patients

from hospital_Data

Group By Location

Order By Total_patients desc;

8. Average Length of Stay Per Department

o Calculate the average number of days patients spend in each department.


Answer-

select department,

avg((discharge_date-admission_date)+1)as avg_stay_days

from hospital_Data

Group By department

Order By avg_stay_days DESC;

9. Identify the Department with the Lowest Number of Patients

o Find the department with the least number of patients.

Answer-

select department,sum(patients_count)as least_patient

from hospital_Data

Group By department

Order By least_patient asc limit 1;

10. Monthly Medical Expenses Report

• Group the data by month and calculate the total medical expenses for each month.

Answer-

SELECT

TO_CHAR(Admission_Date, 'YYYY-MM') AS Month,

SUM(Medical_Expenses) AS Total_Expenses

FROM Hospital_Data

GROUP BY TO_CHAR(Admission_Date, 'YYYY-MM')

ORDER BY Month;

You might also like