Create a new database and execute the code below in SQL Server's query window to create the database tables. CREATE TABLE PhysicianSpecialties. (Specialtid integer, SpecialtyNane varchar(50), CONSTRAINT PK. PhysicianSpesialties PRIMARY KEY (SpecialtyID)) go CREATE TABLE ZipCodes (Zincode varchar(10), City varchar(5e), State varchar(2), CONSTRAINT PK Zipcodes PRIMARY KEY (Zipcode)) go CREATE TABLE PhysicianPractices (PracticeIn integer PracticeName varchar(50), Address Linel varchar(50) Address Line2 varchar(5e), ZipCode varchar cie), Phone yarchar (14) Fax varchar 14 WebsiteURL varchar(50) CONSTRAINT PKLPhysicianPractices PRIMARY KEY PracticeIR) CONSTRAINT EXUPhysicianPractices. Zipcodes. FOREIGN Key zipcode REFERENCES zipcodes.) go CREATE TABLE Physicians (Physician in integer, FirstNane varchar(40), LastName varchar(5e), PracticeIn integer, Specialtyd integer, Email varchar(50), CONSTRAINT PK..Physicians. PRIMARY KEY (PhysicianIR), CONSTRAINT EK..Physicians.Practices. FOREIGN KEY (PracticeID) REFERENCES Physicianfractices. CONSTRAINT EK Physicians..Physicianspecialities. FOREIGN KEY (SpecialtyID) REFERENCES PhysicianSpecialties.) go CREATE TABLE Patients (Patient ID integer, FirstName varchar(5e), MiddleInitial varchar(1), Last Name varchar(50) Addressi Linel yanchar (50) Address Line2 varchar(50), Zipcode varchanie Phone Home varchar(14) Phone Alternate varchar(14), Email varchar(5e CONSTRAINT PK Patients PRIMARY KEY (PatientID)) go CREATE TABLE Referrals (Refec.calin integer CREATE TABLE Referrals (Refeccal integer, StartDate smalldatetime, EndDats smalldatetus, Patientin integer, Physician integer CONSTRAINT PK Referrals. PRIMARY KEY Refeccalin) CONSTRAINT EK Referral Patients. FOREIGN KEY PatientID REFERENCES Patients, CONSTRAINT EK Referrals Physicians. FOREIGN KEY (PhysisianID REFERENCES Physicians CREATE TABLE Services Seri IR integer Sscviceneme varchar 50 CONSTRAINT EKSISEID PRIMARY KEY Servisi RO CREATE TABLE Frequencies (FrequencyID integer, Frequency varchar(30), CONSTRAINT PK Frequencies. PRIMARY KEY (FrequencyID)) CREATE TABLE Referralservices. (ReferralID integer, Service ID integer, FrequencyID integer, CONSTRAINT PK ReferralServices PRIMARY KEY (ReferralID, ServiceID), CONSTRAINT EK ReferralServices Referrals. FOREIGN KEY (ReferralID) REFERENCES Referrals, CONSTRAINT EK ReferralServices Services FOREIGN KEY (ServiceID) REFERENCES Services, CONSTRAINT EK ReferralServices Frequencies FOREIGN KEY (FrequencyID) REFERENCES Frequencies) go CREATE TABLE Payment Tyres (Payment TypeID integer Payment Type varchan 25) CONSTRAINT PK Payment Types PRIMARY KEY (Payment TypeID go CREATE TABLE Insurance Companies. (Insurance ID integer, Insurance Company varchar(50), Address Line1 varchar(50), Address Line2 varchar(50), ZipCode varchar(10), Phone varchar(15), Fax varchar(15), Email varchar(50), CONSTRAINT PK Insurance Companies. PRIMARY KEY (InsuranceID), CONSTRAINT EK Insurance Companies ZipCodes. FOREIGN KEY (ZipCode) REFERENCES ZipCodes) go CREATE TABLE Contracts (ContractID integer, ReferralID integer, StartDate smalldatetime, EndDate smalldatetime, Payment TypeID integer Insurance ID integer, NegotiatedRate float: CONSTRAINT PK Contracts PRIMARY KEY (ContractID CONSTRAINT PK Contracts Referrals FOREIGN KEY KRRID REFERENCES Referrals, CONSTRAINT FK Contracts PaymentTypes FOREIGN KEY (PaymentTypeID REFERENCES Payment Types CONSTRAINT EK Contracts Insurance Companies. FOREIGN KEY (InsuranceID) REFERENCES Insurance Companies) go CREATE TABLE EmployeeTypes. (EmployeeTypeID integer identity, Employee Type varchar(25), CONSTRAINT PK. EmployeeTypes. PRIMARY KEY (Employee TYRID) go CREATE TABLE EmployeeTitles (Employee TitleIn integer, Employee Title varchar (30) CONSTRAINT PK. EmployeeTitles PRIMARY KEY (EmployeeTitleID)) CREATE TABLE EmployeeSkillsvila SkillvelID integer, Skilllevel varchar(15) CONSTRAINT PK Employeeskilllevels PRIMARY KEY (SkilllevelID)) go CREATE TABLE BillingRates (EmployeeTypeID integer, Skilllevel ID integer, BillingRate float, CONSTRAINT PK PrimaryKey PRIMARY KEY (EmployeeTypeID. SkillleveUD), CONSTRAINT EK BillingRates EmployeeTypes FOREIGN KEY (EmployeeTypeID) REFERENCES EmployeeTypes, CONSTRAINT EK BillingRates Employeeskilllevels FOREIGN KEY (Skillleve LID) REFERENCES Employeeskilllevels) CREATE TABLE Employee Ranks. (RankID integer, Employee TypeID integer, TitleID integer, Skilllevel ID integer, Hourly Rate float, Salary float, CONSTRAINT PK EmployeeRanks PRIMARY KEY (RankIR) CONSTRAINT EK EmployeeRanks EmployeeTypes. FOREIGN KEY (Employee Type IR REFERENCES EmployeeTypes. CONSTRAINT EK EmployeeRanks. EmployeeTitles FOREIGN KEY (TitleID REFERENCES Employee Titles CONSTRAINT EK EmployeeRanks Employeeskilllevels. FOREIGN KEY (Skillleve.ID REFERENCES Employeeskilllevels) CREATE TABLE Employees (EmployeeID integer, FirstName varchar(30), MiddleInitial varchar(1), LastName, varchar(50), Address Linel varchar(50), Address Line2 varchar(50), ZipCode varchar(10), Phone varchar(14), Cell Phone varchar(14), Email varchar(50) RankID integer, HourlyWage float, Salary float, CONSTRAINT PK. Employees PRIMARY KEY (EmployeeID), CONSTRAINT EK Employees Employee Ranks. FOREIGN KEY (RankID) REFERENCES EmployeeRanks. CONSTRAINT FK Employee ZipCodes. FOREIGN KEY (ZipCode) REFERENCES ZipCodes) go CREATE TABLE Shifts (Shift ID integer, ShiftName varchar(20), StartTime time, EndTime time, CONSTRAINT PK Shifts PRIMARY KEY (ShiftID)) go CREATE TABLE DaysOfWeek (DayOfWeekID integer, Dayof Wesls varchar(15), CONSTRAINT PK DaysOfWeek PRIMARY KEY (DayOfWeek ID)) go CREATE TABLE Availability Employee ID integer Week of smalldatetime. DayOfWeekID integer ShiftID integer CONSTRAINT PK Availability PRIMARY KEY EmployeeID WeekOf DayOfWeekID ShiftID CONSTRAINT EK Availability Employees. FOREIGN KEY (Employee ID) REFERENCES Employees CONSTRAINT FK Availability DaysOfWeek FOREIGN KEY DayOfWeekID REFERENCES Days OfWeek CONSTRAINT EKI Availability Shifts FOREIGN KEY ShiftID REFERENCES Shifts go CREATE TABLE MedicalSuppliers (Suppliecin integer, SupplierNane varchar(50), Address Line1 varchar(5e), Address Line2 varchar(5e), ZinCode varchar(10), Phone varchar(14), Fax varchar(14), Email varchar(5e), CONSTRAINT PK MedicalSuppliers. PRIMARY KEY (Suppliecin), CONSTRAINT EK MedicalSurrliecs.Zipcodes FOREIGN KEY (Zipcode) REFERENCES Zincades) go CREATE TABLE Supplies (Supply integer, SupplyDescription varchar(40), Charceperunt Float, CONSTRAINT PK Supplies PRIMARY KEY (SupplyID)) go CREATE TABLE SupplyInventory. (SURYID integer Supplier integer paterede ved small datetime, UnitCost float Quantity float CONSTRAINT PKI supply Inventory. PRIMARY KEY SUR XIR. Supplierin DateReceixed) CONSTRAINT EKL Supply Inventory Supplies FOREIGN KEY (SUPRIYID REFERENCES Supplies CONSTRAINT EXUSURPAVInventory Suppliers. FOREIGN KEY SuppliecID: REFERENCES MedicalSuppliers go CREATE TABLE Visits (Visiti integer, DateRendered smalldatetime, Startline, time, Endtime tine, EmployeeIR integer, Patient ID integer CONSTRAINT PK Visits PRIMARY KEY Visit CONSTRAINT EK. Visits. Employees. FOREIGN KEY EmployeeIR REFERENCES Employees CONSTRAINT FK Visits Patients FOREIGN KEY PatientID REFERENCES Patients go CREATE TABLE VisitRetails. (Visit ID integer, VisitDetailin integer, Supply integer, SupplyQuantity integer, ServiceIn integer, Charge float, CONSTRAINT PK VisitDetails. PRIMARY KEY (VisitID. VisitDetailin), CONSTRAINT EK VisitRetaills Supplies. FOREIGN KEY (SupplyID) REFERENCES Supplies, CONSTRAINT EK VisitDetails Services. FOREIGN KEY (ServiceID) REFERENCES Services) go Write and execute queries to perform the following functions: Criteria Output Format Your Answer: Type your query in this column. The first one has been done for you. Screenshots should be pasted below this table. (Make sure the number of rows that are returned appears in the screenshot.) 1. Display a list of Patient Last Name, followed by a all patients who comma and a space, followed by the have a last name patient's first name. (e.g. Smith, John) beginning with Sort order: Patient Last Name. the letter P. ascending select Lastname FirstName as PatientName from Patients where lastname e order by lastname 31. Display a list of all the visits that occurred from March 20, 2014 to March 25, 2014 (including March 20 and March 25) 32. List the total charges for the Visit that occurred on 2/12/2014 for Helen Ramirez that was provided by Laura White. DateRendered, Patient Last Name, Employee Last Name, Start Time, End time Sort order: DateRendered - ascending Patient Last Name - ascending Employee Last Name - ascending Start Time - ascending Total charges (single value) Month, average cost per visit Sort order: month number - ascending 35. List the average charge per visit per month in 2013 broken out by months 36. Provide a unique list of patients who received visits for feeding from November 1, 2014 until the current date. Patient Last Name, Patient First Name Sort order: Patient Last Name - ascending Patient First Name - ascending Step 3: Queries Write and execute the queries below to answer each question. As in your labs, be sure to write out your query in Word and then take a screenshot of the results after execution of each query. Data loaded into tables is worth 30% of your content grade. Correct construction and execution of your queries is worth 70% of your content grade. Write and execute queries to perform the following functions: Criteria Output Format Your Answer: Type your query in this column. The first one has been done for you. Screenshots should be pasted below this table. (Make sure the number of rows that are returned appears in the screenshot.) Write and execute queries to perform the following functions: Criteria Output Format Your Answer: Type your query in this column. The first one has been done for you. Screenshots should be pasted below this table. (Make sure the number of rows that are returned appears in the screenshot.) 1. Display a list of Patient Last Name, followed by a all patients who comma and a space, followed by the have a last name patient's first name. (e.g. Smith, John) beginning with Sort order: Patient Last Name - the letter P. ascending select Lastname First Name as Patient Name from Patients where lastname, like order by lastname, 31. Display a list of all the visits that occurred from March 20, 2014 to March 25, 2014 (including March 20 and March 25) 32. List the total charges for the visit that occurred on 2/12/2014 for Helen Ramirez that was provided by Laura White. DateRendered, Patient Last Name, Employee Last Name, Start Time, End time Sort order: DateRendered - ascending Patient Last Name - ascending Employee Last Name - ascending Start Time - ascending Total charges (single value) Month, average cost per visit Sort order: month number - ascending 35. List the average charge per visit per month in 2013 broken out by months 36. Provide a unique list of patients who received visits for feeding from November 1, 2014 until the current date. Patient Last Name, Patient First Name Sort order: Patient Last Name - ascending Patient First Name - ascending