Review

The following questions are designed to reinforce key information presented in the chapter. If you are unable to answer a question, review the appropriate lesson and then try the question again.

The Duluth Mutual Life health care organization has a database that tracks information about doctors and their patients. The database includes the following tables:

Doctors table

Column	Data Type and Constraints
doc_id	char(9), PRIMARY KEY
Fname	char(20)
lname	char(25)
specialty	char(25)
phone	char(10)

Patients table

Column	Data Type and Constraints
pat_id	char(9), PRIMARY KEY
fname	char(20)
lname	char(25)
insurance_company	char(25)
phone	char(10)

Cases table

Column	Data Type and Constraints
admission_date	datetime, PRIMARY KEY (composite)
pat_id	char(9), PRIMARY KEY (composite), FOREIGN KEY to patient.pat_id
doc_id	char(9), FOREIGN KEY to doctor.doc_id
diagnosis	varchar(150)

Based on this table structure, answer the following questions:

1. How can you generate a list of patient names and their hospital admission dates?

Join the patient table to the case table on the pat_id column. For example:

SELECT fname, lname, admission_date FROM Patients JOIN Cases ON Patients.pat_id = Cases.pat_id 

2. How can you generate a list of names of doctors and each of their patients?

You must join all three tables. The relationship between doctor and patient is a many-to-many relationship. Although you want information from the doctor and patient tables only, you must also use the case table because this table relates doctor to patient. Join the doctor table to the case table on doc_id and join the patient table to the case table on pat_id. Use a WHERE clause to limit the results for a particular doctor. For example:

SELECT D.fname, D.lname, P.fname, P.lname FROM Doctors D JOIN Cases C ON C.doc_id = D.doc_id JOIN Patients P ON C.pat_id = P.pat_id

3. How can you produce a list of pairs of doctors who have the same specialty?

Join the doctor table to itself. Join the two copies of the table on the specialty column. Restrict the results to rows where the doc_id does not match. Be sure to eliminate "mirror image" pairs by using a less than (<) operator in the WHERE clause. For example:

SELECT D1.fname + ' ' + D1.lname AS FirstDoctor, D2.fname + ' ' + D2.lname AS SecondDoctor, D1.specialty FROM Doctors D1 JOIN Doctors D2 ON D1.specialty = D2.specialty WHERE D1.doc_id < D2.doc_id

4. How can you produce a single list of names and addresses for both doctors and patients?

Write a query that retrieves name and phone number information from the doctor table. Write a second query that retrieves similar information from the patient table. Use the UNION operator to combine the queries. For example:

SELECT lname + ', ' + fname AS [Name], phone FROM Doctors UNION SELECT lname + ', ' + fname, phone FROM Patients ORDER BY [Name]