Alternate Week 2 Exercises:
SQL/XML

Notes

This is an alternate set of SQL/XML exercises for those who would like to redo their SQL/XML homework. The maximum possible score is 90, rather than 100. For details about the necessary software and data set, see the original homework assigniment.

Exercises

1. Write a query that creates a result set with two columns. The first column is doctor ID and the second column is an XML document with the form:

      <Doctor>
         <ID>...</ID>
         <FirstName>...</FirstName>
         <LastName>...</LastName>
         <Specialty>...</Specialty>
      </Doctor>
   

2. Same as (1), except that the document uses different SQL/XML functions to get the same result. (For example, if (1) uses XMLELEMENT, (2) should use XMLFOREST.)

      <Doctor>
         <ID>...</ID>
         <FirstName>...</FirstName>
         <LastName>...</LastName>
         <Specialty>...</Specialty>
      </Doctor>
   

3. Same as (1), except that ID is an attribute:

      <Doctor ID="...">
         <FirstName>...</FirstName>
         <LastName>...</LastName>
         <Specialty>...</Specialty>
      </Doctor>
   

4. Write a query that creates a result set with a single column containing an XML document. The document contains multiple Doctor elements (which use attributes for data) wrapped in a Doctors element:

      <Doctors>
         <Doctor ID="..." FirstName="..." LastName="..." Specialty="..." />
         ...
         <Doctor ID="..." FirstName="..." LastName="..." Specialty="..." />
      </Doctors>
   

5. Same as (3), except that the document also lists all prescriptions prescribed by the doctor:

      <Doctor ID="...">
         <FirstName>...</FirstName>
         <LastName>...</LastName>
         <Specialty>...</Specialty>
         <Prescription>
            <Medication>...</Medication>
            <Dosage>...</Dosage>
            <Quantity>...</Quantity>
         </Prescription>
         ...
      </Doctor>
   

6. Same as (5), except that (a) the document lists the doctor's patients doctors instead of prescriptions, and (b) that the Patient elements are wrapped in a Patients element:

      <Doctor ID="...">
         <FirstName>...</FirstName>
         <LastName>...</LastName>
         <Specialty>...</Specialty>
         <Patients>
            <Patient>
               <FirstName>...</FirstName>
               <LastName>...</LastName>
            </Patient>
            ...
         </Patients>
      </Doctor>
   

7. Write a query that creates a result set with a single column. The contains XML documents listing each prescription, as well as information about the prescribing doctor and the patient:

      <Prescription>
         <Patient>
            <FirstName>...</FirstName>
            <LastName>...</LastName>
         </Patient>
         <Doctor>
            <FirstName>...</FirstName>
            <LastName>...</LastName>
         </Doctor>
         <Medication>...</Medication>
         <Dosage>...</Dosage>
         <Quantity>...</Quantity>
      </Prescription>
   

8. Write a query that creates a result set with a single column. The column contains an XML document that lists doctors in ascending order by last name, first name:

      <Doctors>
         <Doctor>
            <FirstName>Kim</FirstName>
            <LastName>Gonzales</LastName>
         </Doctor>
         <Doctor>
            <FirstName>Devin</FirstName>
            <LastName>Liu</LastName>
         </Doctor>
         <Doctor>
            <FirstName>Sean</FirstName>
            <LastName>Wilson</LastName>
         </Doctor>
      </Doctors>
   

   Hint: First write the query without any sorting. This will get you the basic structure you need. Then rewrite the query, taking into account that you cannot sort on elements (XML values), since XML values cannot be compared. Therefore, in your rewritten query, the names must be sorted before they are used to construct elements. For a similar problem (and solution) encountered when using the DISTINCT keyword, see problem 11 on the original homework set.

9. Write a query that creates a result set with two columns. The first column is the doctor's ID and the second column is a document that lists the diagnoses made by the doctor:

      <Doctor DoctorID="...">
         <Diagnosis>...<Diagnosis>
         ...
         <Diagnosis>...<Diagnosis>
      </Doctor>
   

10. Write query that creates a result set with one column. The document is an XHTML document listing each doctor's patients:

       <html>
       <head>
          <title>Patient summary</title>
       </head>
       <body>
       <p>Doctor first-name last-name has the following patients:</p>
       <table>
       <tr><th>Last name</th><th>First name</th></tr>
       <tr><td>last-name</td><td>first-name</td></tr>
       ...
       <tr><td>last-name</td><td>first-name</td></tr>
       </table>
       </body>
       </html>
    

11. OPTIONAL (NO CREDIT -- THIS IS JUST FOR FUN). Write a query that creates a result set with a single column. The column contains a list of medication names and a list of doctors who prescribe them. Each medication name should be listed only once, and for a given medication, each doctor's name should be listed only once.

       <Medication>
          <Name>...</Name>
          <Doctor>
             <FirstName>...</FirstName>
             <LastName>...</LastName>
          </Doctor>
          ...
       </Medication>
    

    Hint: For information about using the DISTINCT keyword with XML values, see problem 11 on the original homework set.

    Comment: I was not able to solve this problem, so I'd love to know the answer.