[go: up one dir, main page]
Scribd
Upload
9 views2 pages

(HW) Topic02 BasicSQLQueries

The document outlines the homework tasks for CS486 - Introduction to Databases, including the creation of a Hospital database and various SQL queries to extract specific information. Tasks involve retrieving data related to doctors, patients, appointments, and medical records based on various criteria. The queries cover a range of scenarios such as filtering by department, insurance plans, and appointment statuses.

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
9 views2 pages

(HW) Topic02 BasicSQLQueries

The document outlines the homework tasks for CS486 - Introduction to Databases, including the creation of a Hospital database and various SQL queries to extract specific information. Tasks involve retrieving data related to doctors, patients, appointments, and medical records based on various criteria. The queries cover a range of scenarios such as filtering by department, insurance plans, and appointment statuses.

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
You are on page 1/ 2

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.
Task 2: Write the SQL script for the following queries:
1.​ Find the DoctorName and Email of all doctors in the 'Cardiology' department.
2.​ List the AppointmentID, PatientName, and DoctorName for all appointments that were
'Completed' in May 2025
3.​ Retrieve the PatientName and RegistrationDate for all patients with a 'Premium' insurance
plan, ordered by RegistrationDate in descending order.
4.​ Get the RecordID and Diagnosis for medical records that include 'Fever' as a symptom..
5.​ List the PatientName of patients who have not scheduled any appointments.
6.​ For all completed appointments, retrieve the AppointmentID, PatientName, DoctorName,
and the Diagnosis from the associated medical record.
7.​ List all symptoms and, if available, the Diagnosis of the medical records they are associated
with. Ensure all symptoms are listed, even if they have no associated medical record.
8.​ Doctors who treated patients from more than one insurance plan.
9.​ Patients diagnosed with the same condition as another patient.
10.​Patients with a medical record that shares at least one symptom with another.
11.​List doctors who have email addresses end with ‘@gmail.com’.
12.​List patients whose address includes the city 'TX'.
13.​List patients who registered in the current month.
14.​Show patient names and their age.
15.​List appointments made on weekends.
16.​Show records created more than 7 days after appointment.
17.​List future appointments ordered by nearest first.
18.​List all unique patient IDs who either had an appointment or a medical record.
19.​Find patients who had appointments but no medical records.
20.​List patient IDs that have both appointments and medical records.

You might also like