Part I Questions

Return to Home Page

 Make sure you store each query needed in a separate file, or in one file that you can cut-and-paste from.

 1.    Get a List of all of Dr. Smith’s Patients. I’ll give you this one (as I did above):

             SQL>  SELECT * FROM patient WHERE physid = '123456789';

        This will result in a list containing 11 entries.

 2.       Get a List of all Patients who live in Texas. A simple substitution in the WHERE clause should do it. This will result in a list with 6 entries.

 3.   Get a List all of Patient Names and Addresses (ONLY) who have Dr. Smith as a physician and live in Texas. Select the fields you wish to display and include an AND clause. (2 entries in the list).

 4.   Get a list of all patient names (only) listed in ascending order by attending physician (make sure to list the physician name). I’ll (almost) give you this one:

         SQL>  SELECT physname, patient

      a.                  FROM physician, patient
b.                  WHERE physician.physid = patient.physid;

         (There is one line missing.). This will result in a list with 29 entries.

5.   Get an (alphabetic by patient name) list of all patient names, their IDs, and the names of the illnesses they have suffered.  This one will require the use of three tables (patient, treatment, and suffer). What you will have to do join the tables on patient ID AND join on illness code. (Don’t forget the order by clause at the end). This will result in 200 entries.

 6.   Get a list of all the unique illness that each patient has had. This is not really as simple as it might seem initially. Basically, you will have to take the commands you used in the above assignment and repeat them, separating them with the command UNION. I know I am being somewhat vague, but I want you to experiment until you get  it on your own. There should be 59 entries in the list.

 7.   Get a list of all illnesses treated using aspirin. A simple select here will result in 12 entries. HOWEVER, there will be duplications. If you use the UNION command as above, you will come up with 8 entries.

 8.  Get a list of all the drugs which have been used to treat neurosis. As with the queries above, 15 entries will be displayed without the UNION command, and 6 entries will be displayed with it.

 9.  Get a list of all of the UNIQUE illnesses Dr. Smith has treated. Dr Smith is credited with 91 entries in the treatment table BUT has only treated 14 different illness.

 10.Get a list of all of the UNIQUE drugs Dr. Smith has prescribed There should be  22 different drugs.

 11.Get a list of all Dr. Smith’s  patients (by patient Id and Patient Name) and the illnesses (by illness name) and the drugs prescribed (by drug name) to treat the illness, ordered by patient name. This was our original question, and is actually much easier than some of the other assignment question. We already know that the list will contain 91 entries. Some of these, as we also know are duplicates (i.e., Some patients were seen many times for the same illness and treated with the same prescription). That is ok for this question. IF we didn’t want duplicates, using the union command, we would generate a list with 54 entries.

 12. Get a list of all Dr. Smith’s patients AS WELL AS THOSE PATIENTS TREATED BY PHYSICIANS WHO DR. SMITH SUPERVISES (by patient Id and Patient Name) and the illnesses (by illness name) and the drugs prescribed (by drug name) to treat the illness, ordered by patient name. The complete list will contain 165 entries. Eliminating duplicates will yield a list with 112 entries.

 13.Get a list of all physicians who have prescribed codeine to their patients. If you were to issue the command: select * from treatment where drugcode = ‘D30495876’; you would get a list of 43 entries. Not surprisingly, if you issue the command without the UNION command, you will get a list of 43 entries (interesting, since there are only 5 physicians). If you wish to eliminate duplicate names, however, you will get a list with 4 names on it.

 14.Get a count of how many times each physician has prescribed codeine. An example: Suppose I wished to find out how many times Codeine was been prescribed. The command select count(*) as Number_Times from treatment where drugcode = ‘D30495867’; should do it (43 times). The command as Number_Times will generate the field heading Number_Times. We can use basically the same scheme. Take the commands you used in the previous question (without the UNION), modify the select statement to reflect the count component, AND make sure you add the command GROUP by physname at the end.

You may wonder why I am not giving you more explicit commands. The answer is simple. I can  not teach you SQL. YOU have to learn it on your own. I am not being cruel. That is the reality of it all. You can’t learn how to play the violin by listening to someone else play. You have to practice, and learn by your mistakes.

  This page was last updated on 08/24/03.