SQL Inquiry

SQL Joins-Functions-View Assignment

Part 1: SQL Joins and Functions (15 points – 3 points each)

1. Write a query to display the minimum and maximum number of diagnoses (distinct diagnosis codes)

by state, for each state in which patients in our database live. Display the name of the state, the

minimum number of diagnoses, and the maximum number of diagnoses. Be sure each field is

displayed with a meaningful, user-friendly name (e.g., ‘MinDiagnoses’).

2. Write a query to display the average number of immunizations given to female patients by state, for

each state in which patients in our database live. Display the name of the state, patient gender, and

the average number of immunizations. Be sure each field is displayed with a meaningful, user-

friendly name.

3. Write a query to display the minimum, maximum, and average, and total number of lab tests

resulted for patients born in each year between 1990 and 2000 and living in California. Display

patient year of birth, minimum number of lab tests, maximum number of lab tests, average number

of lab tests number of lab tests and total number of lab tests resulted for patients having each birth

year. Be sure each field is displayed with a meaningful, user-friendly name.

4. Write a query to display all female patients living in California along with the total number of

diagnosis records that have been entered for the patient. Display patient Id, patient gender, patient

state, year of birth, and the total number of diagnosis records. Give the summary column (total

number of diagnosis records) a meaningful name. Include all patients whether or not they have a

diagnosis. HINT: Use a left or right join.

5. Run a query to display all patients born in 1960 or later (DISTINCT patients) and the total number

(COUNT) of immunizations they have received. Display patient Id, patient gender, patient state, year

of birth and total number of immunizations. Include all patients in your list, even if they have

received no immunizations. HINT: Use a left or right join.

SQL View with Functions (15 points)

For this question, I'd like each of you to create a view in the class database (cssdata). Your view should include the following elements:

 The view contains a comment that describes the creator and create date of the view and a description of what the view accomplishes (3 points)

 The view will join 3 or more tables together (3 points)

 The view will display fields from at least 2 of the tables (1 points)

 At least 2 fields displayed will have a name different from the name of the field in the underlying table (2 points)

 The view will include at least one calculated field (3 points)

 The view will include a field that displays the current date and time (3 points)