SQL Problem 1

Assume we have loaded a flat file with patient diagnosis data into a table called Data. The table structure is:

Create table Data (

Firstname varchar(50),

Lastname varchar(50),

Date_of_birth datetime,

Medical_record_number varchar(20),

Diagnosis_date datetime,

Diagnosis_code varchar(20))

The data in the flat file looks like this:

'jane','jones','2/2/2001','MRN-11111','3/3/2009','diabetes'

'jane','jones','2/2/2001','MRN-11111','1/3/2009','asthma'

'jane','jones','5/5/1975','MRN-88888','2/17/2009','flu'

'tom','smith','4/12/2002','MRN-22222','3/3/2009','diabetes'

'tom','smith','4/12/2002','MRN-33333','1/3/2009','asthma'

'tom','smith','4/12/2002','MRN-33333','2/7/2009','asthma'

'jack','thomas','8/10/1991','MRN-44444','3/7/2009','asthma'

You can assume that no two patients have the same firstname, lastname, and date of birth combination. However one patient might have several visits on different days. These should all have the same medical record number.

The problem is this: Tom Smith has 2 different medical record numbers. Write a query that would always show all the patients

who are like Tom Smith patients with more than one medical record number.

This problem has many solutions, but if you know SQL, you should be able to find one that uses a single query with nosubqueries.