Step 1: Table Creation Create a new database and execute the code below in SQL Server's query window to create the database tables CREATE TABLE PhysicianSpecialties (Specialty integer, Sascialtxams, varchar(5e), CONSTRAINT PK PhysicianSpecialties. PRIMARY KEY (SpecialtyID)) go CREATE TABLE ZipCodes. (Zipcode xarchar(10), City archar(5e), State varchar(2), CONSTRAINT PK zipcodes. PRIMARY KEY (Zipcode>> go CREATE TABLE PhysisianPractices. (PracticeIn integer PracticeName varchar(50). AddressLine1 varchar(50 Address Line2 varchar(50 Zip Code yanchan 10) Phone varchar(14) Fax varchar 14 WebsiteURL varchar 501 CONSTRAINT PK PhysicianPractices PRIMARY KEY Practised CONSTRAINT EK Physician Practicesiecodes FOREIGN KEX icode REFERENCES Zipcodes CREATE TABLE Physicians (ehysiciant integer, FirstNane varchar(40), LastName varchar(50), PracticeIn integer Specialty. In integer Email varchar(5e), CONSTRAINT PK Physicians PRIMARY KEY (Physician), CONSTRAINT EK Physicians Practices. FOREIGN KEY (PracticeIR) REFERENCES Physicianecactices CONSTRAINT EK Physicians Physicianspesialities FOREIGN KEY (SpecialtyID) REFERENCES Physician Specialties) go CREATE TABLE Patients Patient integer FirstName varchar(50) Middletoitial varchar: 1). Last Name varchar se Address Linel varchar.se Address Line2 yacchar(50) Zipcode varchar 10 Phone Hore varchar 140 Phone Altschale varchar(14) Email varchar 501 CONSTRAINT PK Patients PRIMARY KEY Patient ID 89 CREATE TABLE Referral Referral intesen StartDate swall.datetime. EndDate, salddatetime, Patientin integer, Physician ID integer, CONSTRAINT PK. Referrals. PRIMARY KEY (BsfecclIR), CONSTRAINT EK..Refecals.Raticats. FOREIGN KEY (PatientIR) REFERENCES Patients, CONSTRAINT EK Refeccal Physicians. FOREIGN KEY (PhysicianIR) REFERENCES Physicians) go CREATE TABLE Services (SecviceIn integer, SecviceName varchar(50), CONSTRAINT PK Service ID. PRIMARY KEY (SecviceIR)) BO CREATE TABLE Frequencies KEceguessin, integer, Frequency yacshar(30) CONSTRAINT PIECsquensiss PRIMARY KEY Frequency) 80 CREATE TABLE Befecalservices Refsccali integer SECARD Integer Frequency. Integer CONSTRAINT PK Refeccelservices PRIMARY KEY Rebeccal Servis ID CONSTRAINT EK Referralservices Referrals FOREIGN KEY ReferralID REFERENCES Referrals CONSTRAINT EK Referralsexices Services FOREIGN KEY SciSER REFERENCES Services CONSTRAINT EK Referralsecxies Fregucacies FOREIGN KEY Fregus SKID REFERENCES Frequencies CREATE TABLE Payment Types (Payment IxRIR integer Payment Ixne varchar(25), CONSTRAINT PK PaymentTyRES. PRIMARY KEY (PaynsatTXRCID)) go CREATE TABLE Insurancecomranies. KInsurance, integer, Insurance company varchar(50), Address Linel varchar(5e), Address Line2 varchar(50), Zincade, vacchar(10) Phone varchar(15) Fax varchar(15) Email varchar(se), CONSTRAINT PInsurance Companies PRIMARY KEY (InsuranceIR). CONSTRAINT EKUITOS CANSECompanies zipsodes. FOREIGN KEY (Zipcode) REFERENCES Zipsades) 80 CREATE TABLE Contracts Contract in integer BAL ter StartDate moldatering EndDate smalldatetime PaymentTypeID integer InsuranceID integer NegotiatedRate float CONSTRAINT PK Contracts PRIMARY KEY (SantrasID CONSTRAINT PK Cont.cat Refscals. FOREIGN KEY CIDERECES Serial CONSTRAINT FX Contrast Payment Types FOREIGN KEY Payment TypeID REFERENCES Paymenttypes CONSTRAINT EK.Contracts. Insurance companies. FOREIGN KEY (InsuranceID) REFERENCES Insurancesenanies) go CREATE TABLE Employee types. (EmployeeTypeIR integer identity, EmployeeType varchar(25). CONSTRAINT PK.EnnlovesTypes PRIMARY KEY (EmployeeTypeID)) go CREATE TABLE Employee titles. (Employee TitleIR integer, Employee tits varchar(30), CONSTRAINT PK. Ennlovestitles PRIMARY KEY (Employectilein)) go CREATE TABLE Employseskilllevels skilllevelid integer, Skillised varchar(15), CONSTRAINT PKLER lovseskilllexels PRIMARY KEY (Skillleve.ID 89 CREATE TABLE BillingRates Employse Type in integer Skilled integer Ballingrats Float CONSTRAINT PK Primary key PRIMARY KEY EmployeeTypeID: Skilllevelin CONSTRAINT EK SingRates Employee Types FOREIGN KEMIKER JoxeeType. REFERENCES Employee Types CONSTRAINT EK BlingRates Employeeskallevels FOREIGN KEY Skrillex IR REFERENCES Employeeskilllevels. go CREATE TABLE EmployceRanks. (Rank In integer, EmployeeTypeIR integer, Title ID integer, Skiltlevel integer, Hourly.Rate float, Salary float, CONSTRAINT PK. EmployesBanks, PRIMARY KEY (RankIR), CONSTRAINT EK..EmployeeRanks. Employee Types. FOREIGN KEY (EmployeeTYRSIR) REFERENCES EmployecType. CONSTRAINT EK. EmployeeRanks. EmployeeTitles. FOREIGN KEY (TitleID) REFERENCES EmployeeTitles, CONSTRAINT EK. EnplexeeRanks. Employeeskilllevels. FOREIGN KEY (Skildhexs.ID) REFERENCES Employeeskilllexels) go CREATE TABLE Employees (Employee ID integer, FirstName varchar( 30 Middl-Initial archard Last Name varchar(50) Address Linel varchar(50) Address Line2 varchar(50) 26pCode varchar 10 Phone varchar(14) Cell Phone archan 143 Email Marchandise BankID integer Hourly ware Float, Salary Float CONSTRAINT PIERlexess. PRIMARY KEY Eloxes I CONSTRAINT FRIEnAloyees EmployeeRacks FOREIGN REY RankIR REFERENCES Employee Rank CONSTRAINT EKLE Roxee zipodea FOREIGN KEY Ripcode> REFERENCES ZipCodes go CREATE TABLE Shifts (Shiftin. integer, Shift.Name varchar(20), Stact Time time, Eostime time, CONSTRAINT PK. Shifts PRIMARY KEY (Shiftin)) go CREATE TABLE Raxsofbeek. (RayofWeeki integer, Rastack varchar(15), CONSTRAINT PK. DaysOfWeek PRIMARY KEY (RayofWeekID)) go CREATE TABLE Availability (EmployeeIR integer, Weekgf small datetime, RayofWeekID integer, ShiftID integer CONSTRAINT PK. Availability PRIMARY KEY (EmployeeID. Weekof. DayofweekID. ShiftID). CONSTRAINT FK. Availability Employees FOREIGN KEY EmployeeID REFERENCES Employees, CONSTRAINT EK. Availability Raysofbeek FOREIGN KEY (RayofWeekIR) REFERENCES Daysof week. CONSTRAINT EK. Availability Shifts FOREIGN KEY ShiftiD REFERENCES Shifts) go CREATE TARLE MedicalSuppliecs. (Suppliecin integer, Surrliccane varchar(5e), Address Linel varchar(50), Address Line2 varchar(se), Zarcode varchar(10), Phone varchar(14) Fax varchar(14), Email varchar(50), CONSTRAINT PK Medical Suppliers. PRIMARY KEY (Supplieci), CONSTRAINT EK..MedicalSuppliers.Zisodes. FOREIGN KEY (ZipCode) REFERENCES ZIRCodes) go CREATE TABLE Supplies SURR VIR integer, Supply Description varchar(40), Sharkalecos float) CONSTRAINT PK Supplies PRIMARY KEY (SupplyID)) go CREATE TABLE SupplyInventory Supply integer SupplieciD integer Datensceived small datetime Unit cost float Quantity float CONSTRAINT PK Supply inventory. PRIMARY KEY SupRLID. SuppliecID DateReceived) CONSTRAINT EK ISuppl.inventory Supplies FOREIGN KEY SURR SID. REFERENCES Supplies CONSTRAINT EKUSURRAVInventory Suppliers. FOREIGN KEY SuppliecID REFERENCES Medica supplies) 80 CREATE TABLE Visits (VisitID integer, DateRendered smalldatetime, StartTime time, EndTime time, Employee ID integer, Patient ID integer, CONSTRAINT PK Visits PRIMARY KEY (VisitID), CONSTRAINT EK Visits Employees. FOREIGN KEY (EmployeeID) REFERENCES Employees, CONSTRAINT FK Visits Patients FOREIGN KEY (Patient ID REFERENCES Patients) go CREATE TABLE VisitDetails (VisitID integer, VisitDetailed integer, SupplyID integer SupplyQuantity integer, ServiceID integer, Charge float CONSTRAINT PK VisitDetails PRIMARY KEY VisitID VisitDetailID CONSTRAINT FK VisitDetaiils Supplies FOREIGN KEY SupplyID REFERENCES Supplies, CONSTRAINT FK 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 ! ascending select Lastname FirstName as Patient Name from Patients where lastname, like % order by lastname, 30. Display the total cost of all items purchased from suppliers broken out by supplier. Supplier, Total cost of all items provided by supplier Sort order: Supplier - ascending Total number of patients (single value) 33. List the number of patients who received insulin injections during 2014 (Note this is the number of unique patients who ever received insulin injections - not the number of visits in which insulin injections were provided). 34. List the total number of 4" self adhesive bandages that were used in 2014 Total number of 4" self-adhesive bandages (single value) Step 1: Table Creation Create a new database and execute the code below in SQL Server's query window to create the database tables CREATE TABLE PhysicianSpecialties (Specialty integer, Sascialtxams, varchar(5e), CONSTRAINT PK PhysicianSpecialties. PRIMARY KEY (SpecialtyID)) go CREATE TABLE ZipCodes. (Zipcode xarchar(10), City archar(5e), State varchar(2), CONSTRAINT PK zipcodes. PRIMARY KEY (Zipcode>> go CREATE TABLE PhysisianPractices. (PracticeIn integer PracticeName varchar(50). AddressLine1 varchar(50 Address Line2 varchar(50 Zip Code yanchan 10) Phone varchar(14) Fax varchar 14 WebsiteURL varchar 501 CONSTRAINT PK PhysicianPractices PRIMARY KEY Practised CONSTRAINT EK Physician Practicesiecodes FOREIGN KEX icode REFERENCES Zipcodes CREATE TABLE Physicians (ehysiciant integer, FirstNane varchar(40), LastName varchar(50), PracticeIn integer Specialty. In integer Email varchar(5e), CONSTRAINT PK Physicians PRIMARY KEY (Physician), CONSTRAINT EK Physicians Practices. FOREIGN KEY (PracticeIR) REFERENCES Physicianecactices CONSTRAINT EK Physicians Physicianspesialities FOREIGN KEY (SpecialtyID) REFERENCES Physician Specialties) go CREATE TABLE Patients Patient integer FirstName varchar(50) Middletoitial varchar: 1). Last Name varchar se Address Linel varchar.se Address Line2 yacchar(50) Zipcode varchar 10 Phone Hore varchar 140 Phone Altschale varchar(14) Email varchar 501 CONSTRAINT PK Patients PRIMARY KEY Patient ID 89 CREATE TABLE Referral Referral intesen StartDate swall.datetime. EndDate, salddatetime, Patientin integer, Physician ID integer, CONSTRAINT PK. Referrals. PRIMARY KEY (BsfecclIR), CONSTRAINT EK..Refecals.Raticats. FOREIGN KEY (PatientIR) REFERENCES Patients, CONSTRAINT EK Refeccal Physicians. FOREIGN KEY (PhysicianIR) REFERENCES Physicians) go CREATE TABLE Services (SecviceIn integer, SecviceName varchar(50), CONSTRAINT PK Service ID. PRIMARY KEY (SecviceIR)) BO CREATE TABLE Frequencies KEceguessin, integer, Frequency yacshar(30) CONSTRAINT PIECsquensiss PRIMARY KEY Frequency) 80 CREATE TABLE Befecalservices Refsccali integer SECARD Integer Frequency. Integer CONSTRAINT PK Refeccelservices PRIMARY KEY Rebeccal Servis ID CONSTRAINT EK Referralservices Referrals FOREIGN KEY ReferralID REFERENCES Referrals CONSTRAINT EK Referralsexices Services FOREIGN KEY SciSER REFERENCES Services CONSTRAINT EK Referralsecxies Fregucacies FOREIGN KEY Fregus SKID REFERENCES Frequencies CREATE TABLE Payment Types (Payment IxRIR integer Payment Ixne varchar(25), CONSTRAINT PK PaymentTyRES. PRIMARY KEY (PaynsatTXRCID)) go CREATE TABLE Insurancecomranies. KInsurance, integer, Insurance company varchar(50), Address Linel varchar(5e), Address Line2 varchar(50), Zincade, vacchar(10) Phone varchar(15) Fax varchar(15) Email varchar(se), CONSTRAINT PInsurance Companies PRIMARY KEY (InsuranceIR). CONSTRAINT EKUITOS CANSECompanies zipsodes. FOREIGN KEY (Zipcode) REFERENCES Zipsades) 80 CREATE TABLE Contracts Contract in integer BAL ter StartDate moldatering EndDate smalldatetime PaymentTypeID integer InsuranceID integer NegotiatedRate float CONSTRAINT PK Contracts PRIMARY KEY (SantrasID CONSTRAINT PK Cont.cat Refscals. FOREIGN KEY CIDERECES Serial CONSTRAINT FX Contrast Payment Types FOREIGN KEY Payment TypeID REFERENCES Paymenttypes CONSTRAINT EK.Contracts. Insurance companies. FOREIGN KEY (InsuranceID) REFERENCES Insurancesenanies) go CREATE TABLE Employee types. (EmployeeTypeIR integer identity, EmployeeType varchar(25). CONSTRAINT PK.EnnlovesTypes PRIMARY KEY (EmployeeTypeID)) go CREATE TABLE Employee titles. (Employee TitleIR integer, Employee tits varchar(30), CONSTRAINT PK. Ennlovestitles PRIMARY KEY (Employectilein)) go CREATE TABLE Employseskilllevels skilllevelid integer, Skillised varchar(15), CONSTRAINT PKLER lovseskilllexels PRIMARY KEY (Skillleve.ID 89 CREATE TABLE BillingRates Employse Type in integer Skilled integer Ballingrats Float CONSTRAINT PK Primary key PRIMARY KEY EmployeeTypeID: Skilllevelin CONSTRAINT EK SingRates Employee Types FOREIGN KEMIKER JoxeeType. REFERENCES Employee Types CONSTRAINT EK BlingRates Employeeskallevels FOREIGN KEY Skrillex IR REFERENCES Employeeskilllevels. go CREATE TABLE EmployceRanks. (Rank In integer, EmployeeTypeIR integer, Title ID integer, Skiltlevel integer, Hourly.Rate float, Salary float, CONSTRAINT PK. EmployesBanks, PRIMARY KEY (RankIR), CONSTRAINT EK..EmployeeRanks. Employee Types. FOREIGN KEY (EmployeeTYRSIR) REFERENCES EmployecType. CONSTRAINT EK. EmployeeRanks. EmployeeTitles. FOREIGN KEY (TitleID) REFERENCES EmployeeTitles, CONSTRAINT EK. EnplexeeRanks. Employeeskilllevels. FOREIGN KEY (Skildhexs.ID) REFERENCES Employeeskilllexels) go CREATE TABLE Employees (Employee ID integer, FirstName varchar( 30 Middl-Initial archard Last Name varchar(50) Address Linel varchar(50) Address Line2 varchar(50) 26pCode varchar 10 Phone varchar(14) Cell Phone archan 143 Email Marchandise BankID integer Hourly ware Float, Salary Float CONSTRAINT PIERlexess. PRIMARY KEY Eloxes I CONSTRAINT FRIEnAloyees EmployeeRacks FOREIGN REY RankIR REFERENCES Employee Rank CONSTRAINT EKLE Roxee zipodea FOREIGN KEY Ripcode> REFERENCES ZipCodes go CREATE TABLE Shifts (Shiftin. integer, Shift.Name varchar(20), Stact Time time, Eostime time, CONSTRAINT PK. Shifts PRIMARY KEY (Shiftin)) go CREATE TABLE Raxsofbeek. (RayofWeeki integer, Rastack varchar(15), CONSTRAINT PK. DaysOfWeek PRIMARY KEY (RayofWeekID)) go CREATE TABLE Availability (EmployeeIR integer, Weekgf small datetime, RayofWeekID integer, ShiftID integer CONSTRAINT PK. Availability PRIMARY KEY (EmployeeID. Weekof. DayofweekID. ShiftID). CONSTRAINT FK. Availability Employees FOREIGN KEY EmployeeID REFERENCES Employees, CONSTRAINT EK. Availability Raysofbeek FOREIGN KEY (RayofWeekIR) REFERENCES Daysof week. CONSTRAINT EK. Availability Shifts FOREIGN KEY ShiftiD REFERENCES Shifts) go CREATE TARLE MedicalSuppliecs. (Suppliecin integer, Surrliccane varchar(5e), Address Linel varchar(50), Address Line2 varchar(se), Zarcode varchar(10), Phone varchar(14) Fax varchar(14), Email varchar(50), CONSTRAINT PK Medical Suppliers. PRIMARY KEY (Supplieci), CONSTRAINT EK..MedicalSuppliers.Zisodes. FOREIGN KEY (ZipCode) REFERENCES ZIRCodes) go CREATE TABLE Supplies SURR VIR integer, Supply Description varchar(40), Sharkalecos float) CONSTRAINT PK Supplies PRIMARY KEY (SupplyID)) go CREATE TABLE SupplyInventory Supply integer SupplieciD integer Datensceived small datetime Unit cost float Quantity float CONSTRAINT PK Supply inventory. PRIMARY KEY SupRLID. SuppliecID DateReceived) CONSTRAINT EK ISuppl.inventory Supplies FOREIGN KEY SURR SID. REFERENCES Supplies CONSTRAINT EKUSURRAVInventory Suppliers. FOREIGN KEY SuppliecID REFERENCES Medica supplies) 80 CREATE TABLE Visits (VisitID integer, DateRendered smalldatetime, StartTime time, EndTime time, Employee ID integer, Patient ID integer, CONSTRAINT PK Visits PRIMARY KEY (VisitID), CONSTRAINT EK Visits Employees. FOREIGN KEY (EmployeeID) REFERENCES Employees, CONSTRAINT FK Visits Patients FOREIGN KEY (Patient ID REFERENCES Patients) go CREATE TABLE VisitDetails (VisitID integer, VisitDetailed integer, SupplyID integer SupplyQuantity integer, ServiceID integer, Charge float CONSTRAINT PK VisitDetails PRIMARY KEY VisitID VisitDetailID CONSTRAINT FK VisitDetaiils Supplies FOREIGN KEY SupplyID REFERENCES Supplies, CONSTRAINT FK 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 ! ascending select Lastname FirstName as Patient Name from Patients where lastname, like % order by lastname, 30. Display the total cost of all items purchased from suppliers broken out by supplier. Supplier, Total cost of all items provided by supplier Sort order: Supplier - ascending Total number of patients (single value) 33. List the number of patients who received insulin injections during 2014 (Note this is the number of unique patients who ever received insulin injections - not the number of visits in which insulin injections were provided). 34. List the total number of 4" self adhesive bandages that were used in 2014 Total number of 4" self-adhesive bandages (single value)