SQL Inquiry

Assignment Notes - Joins, Functions and Views Assignment Part 1

Questions 1, 2, and 3

For questions 1, 2, 3 and you will use the following views in the FROM clause of your queries:

1. vDiagnosesByPatient

2. vImmunizationsByPatient

3. vLabResultsByPatient

Use the following information to help verify your SQL logic:

Question 1

o 52 rows returned

o Results for NY: MIN=1, MAX=30

Question 2

o 2 rows returned

o Results for Michigan: AVERAGE = 1.0000

Question 3

o 4 rows returned

o Results for 1993: MIN=1 lab, MAX=7 labs, AVERAGE = 2.7500 labs

Questions 4 and 5

Question 4

o 1611 rows returned

o Results for patient A79241F6-3D8E-4208-B64E-7C2591F72398: Total Diagnoses= 72

Question 5

o 6706 rows returned

o Results for patient C0AD1E2F-6F08-4723-9589-C6FE64753BD7: Total Immunizations= 1

Assignment Part 2

Functions

Chapter 12 of the MySQL 5.6 Reference Manual

http://dev.mysql.com/doc/refman/5.6/en/functions.html

CREATE VIEW Syntax

Chapter 13 (13.1.12)

http://dev.mysql.com/doc/refman/5.0/en/create-view.html