CS336 Principles of Database Management Systems
Query Review Problems

Not For Credit

1.

The following relations are part of a patient billing database for a hospital.

• Patient(PID,name,address,phone)
• Stay(PID,admitDate,dischargeDate)
• Treated(PID,Tcode,Tdate)
• Treatment(Tcode,Tdescription,Tfee)

Write queries in the Relational Algebra (RA), the Tuple Relational Calculus (TRC), and SQL for each of the following:

(a)

The address of patient ``D. Z. Spells''.

(b)

The names of all patients treated on June 1, 2002.

(c)

The PIDs of any patients treated under Tcode='000100'.

(d)

The description of all treatments received by patient ``N. Welle''.

(e)

The description of the most expensive treatment.

(f)

The Tcode of any treatment received by all of the patients on June 9, 2002.