Scribd
Upload
2 views2 pages

Database Queries for Hospital Management

The document outlines the homework tasks for CS486 - Introduction to Databases, including the creation of a Hospital database and various SQL queries related to patients, doctors, and departments. Students are required to write SQL scripts for 20 specific queries, focusing on medical costs, ratings, appointments, and prescriptions. Additionally, it mentions the use of Common Table Expressions (CTEs) for the last two queries involving consultation fees and similarity scoring of medical records.

Uploaded by

Nguyễn Đức Duy
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 views2 pages

Database Queries for Hospital Management

The document outlines the homework tasks for CS486 - Introduction to Databases, including the creation of a Hospital database and various SQL queries related to patients, doctors, and departments. Students are required to write SQL scripts for 20 specific queries, focusing on medical costs, ratings, appointments, and prescriptions. Additionally, it mentions the use of Common Table Expressions (CTEs) for the last two queries involving consultation fees and similarity scoring of medical records.

Uploaded by

Nguyễn Đức Duy
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

CS486 – Introduction to Databases

​ Lecturer: ​ Lê Thị Nhàn, PhD.


​ TA: ​ Phan Thị Phương Uyên, MSc.
​ Lab instructor:​ Nguyễn Ngọc Toàn, MSc.
​ ​ Nguyễn Ngọc Minh Châu, MSc.
​ ​
Homework
Task 1 : Run CS468_Hospital script to create the Hospital database and import its data. (Do not
submit this file or its content)
Task 2: Write the SQL script for the following queries:
1.​ Patients with High Total Medical Costs
2.​ Doctors with High Average Ratings
3.​ Symptoms Frequently Appearing in Records
4.​ List the names of patients who have had at least one completed appointment.
5.​ Find doctors who have appointments with patients older than 40.
6.​ Display names of doctors who are head of a department.
7.​ Find names of departments where the average doctor rating is above 4.5.
8.​ Find the patients who were prescribed 'Ibuprofen'.
9.​ List all patients who have never had a cancelled appointment.
10.​Find doctors who treated at least one VIP insurance plan patient.
11.​List doctors who have never prescribed any medicine. (Students should import the data to
test the query)
12.​Find the patients who were diagnosed with a condition that had symptoms with average
severity > 7.
13.​Find the most expensive consultation fee for each department.
14.​List names of doctors whose rating is above the average rating of all doctors.
15.​Find the names of patients whose total prescription cost is more than 100.
16.​Find doctors who have treated patients with the symptom 'Chest Pain'.
17.​Find departments where all doctors have more than 10 years of experience.
18.​Find patients who have appointments only with doctors rated 4.8 or higher.
CTE are allowed to use for queries 19-20:
19.​For each doctor, calculate their average consultation fee per year and their overall average
fee
20.​Given a new medical case described by a set of symptoms (e.g., {SYM00002, SYM00003,
SYM00007}), suggest the most similar historical medical record based on symptom overlap.
Similarity is calculated using the harmonic mean of the matching symptoms:
2×𝑜𝑣𝑒𝑟𝑙𝑎𝑝_𝑐𝑜𝑢𝑛𝑡
𝑠𝑖𝑚𝑖𝑙𝑎𝑟𝑖𝑡𝑦_𝑠𝑐𝑜𝑟𝑒 = 𝑠𝑦𝑚𝑝𝑡𝑜𝑚_𝑐𝑜𝑢𝑛𝑡_𝑖𝑛_𝑛𝑒𝑤_𝑐𝑎𝑠𝑒 + 𝑠𝑦𝑚𝑝𝑡𝑜𝑚_𝑐𝑜𝑢𝑛𝑡_𝑖𝑛_𝑝𝑎𝑠𝑡_𝑟𝑒𝑐𝑜𝑟𝑑
Return the record(s) with the highest similarity score.

You might also like