MSIS$/$IT $230$

Phase $5$ Requirements

Selects Using Multiple Tables

In this phase you will demonstrate your knowledge of selecting data from multiple tables. You will need to use techniques such as concatenation, sorting, and different uses of the where clause to return the data. These also require the use of various types of joins. Remember when joining tables that two tables require at least one join, three tables require at least two joins, and so on$.$

You will create one $.$sql file that contains all the Select statements and submit it to Blackboard for grading. Any statement that indicates full name means to concatenate the first and last name. Use aliases where needed.

There are five Select statements that you will write. Each is worth the point value listed with the requirement. To receive full credit your statement must be syntactically correct and must return the correct results. Please be sure that your statements are in the order listed below.

$1.$ Write the select statement that will create an employee schedule that has the full name, day of the week, start time and end time sorted by employee id within start time. $(15$ points$)$

$2.$ Write the select statement that will create a list of employee last name, employee first name, and their specialty description, sorted by description within last name. $(20$ points$)$

$3.$ Write the select statement that will create a list of employee last name, employee first name, and their type description, sorted by last name within description. $(15$ points$)$

$4.$ Write the select statement that will create an appointment schedule. Display the appointment date, patient$$s full name, employee$$s full name, start time and duration. Only return rows where the appointment has been scheduled. Sort on patient last name within date. $(50$ points$)$

$5.$ Write the select statement that will create a medical history for all patients. Display the date of the history, patient last name, patient first name, description from the medical history table, and the type of history record $($Condition$,$ Surgery, or Prescription$).$ Sort on patient last name within date. $(50$ points$)$

Query $5$ requires you to use a UNION clause for the three medical history tables and create a column in the Select statement to display the type of history record $($hint: use a constant and an alias$).$ The best way to tackle this is one Select at a time. Write the Select statement that uses the MedicalHistoryCond table. Once you are satisfied that the output is correct, add a UNION clause, copy and paste the Select statement, and change what is needed for the MedicalHistorySurg table. Run that and once you are satisfied that the output is correct add another UNION clause and repeat for the MedicalHistoryPres table. I have included a partial screenshot of the expected output as a guide.