PRG150

Assessment 3 Worksheet – Multiple Table Queries (50pts)

Instructions : Add your SQL command and results (using copy/paste and screen capture) to each question. Do NOT remove any existing content or images from this file. You MUST include both your SQL Command and Results from running that command in your response to each question.

Take advantage of the “Hints” that accompany each question.

The Entity Relationship Diagram (ERD) for the Shoreline Birth Center database is shown below. You need this diagram to know the table and field names and relationships between the tables.

The following variant of the Shoreline Birth Center ERD was generated using “diagrameditor.com”. This free tool takes a bit to learn and use all of its features. The advantage of this tool is that it displays primary and foreign keys which are critical when building and running queries that involve multiple tables.

Shoreline Birth Center ERD

(generated using “diagrameditor.com”)

1. (10pts) List the first name, last name and appointment day of every patient.

Put your SQL command and your results here:

2. (10pts) List the patient’s first name and last name along with all of their provider’s credentials and last name.

Put your SQL command and your results here:

3. (10pts) List the patient’s last name and their provider’s last name and phone number. Display the patient’s last name column as “Patient”, the provider’s last name as “Provider” and the provider’s phone number as “Provider Phone #”.

.

Put your SQL command and your results here:

4. (10pts) List the patient’s last name, day of their appointment and their provider’s last name and phone number. Display the patient’s last name column as “PATIENT_NAME”, the provider’s last name as “PROVIDER_NAME”.

Put your SQL command and your results here:

5. (10pts) On this last question you will be writing your own question that requires two or more tables. Then generate and run the SQL command to answer that question. Take advantage of the SQL statements you have already accomplished in this Worksheet that included multiple tables.

Put your question here:

Put your SQL command and your results here: