Step 1: Lapte creation Create a new database and execute the code below in SQL Server's query window to create the database tables. CREATE TABLE PhysicianSpecialties. (Spesialt in integer, SpecialtxNane varchar(50), CONSTRAINT PK Physicianspecialties PRIMARY KEY (Spesial xid)) go CREATE TABLE Zipcodes. (Zincade varchar(10), City varchar(50). State varchar(2), CONSTRAINT PK ZipCodes PRIMARY KEY (ZipCode.) go CREATE TABLE PhysicianPractices PracticeIR Integer PracticeName varchar(50), Address Line1 varchar(50) Address Line2 archack58) zincode varchar(10) Phone archani 14 Fax vandhan 14 Website varcharse CONSTRAINT PK Physician cactices PRIMARY KEY (Practice CONSTRAINT EXUPysicianenadiices Zipcodes. FOREIGN KEYZincode REFERENCES Zipcodes.) 80 CREATE TABLE Physicians (PhysicianIn integer, FirstNane varchar(40), Last Name, xarchar(58), Practice integer, Specialty integer, Email varchar(58), CONSTRAINT PK Physicians PRIMARY KEY (Physician IR), CONSTRAINT EK..Physicians.Pcactices. FOREIGN KEY (ecasticeIR) REFERENCES. PhysicianPractices. CONSTRAINT EK..Pusicians..ehysicianSpesialities FOREIGN KEY (SpecialtyID) REFERENCES Physicianspecialties) go CREATE TABLE Patients (Patient ID integer, FirstName varchar(50), MiddleInitial sacchar(1), Last Name varchar(50) Address Line1 xarchar(50). Address Line2 varchar5e). Zipcode varchar(10), Phone. Hone varchar(14) Phone Alternate, archar 14) Email kacchar(50 CONSTRAINT PKL Patients PRIMARY KEY Patient ID go CREATE TABLE Referrals (Refercalis integer, StartDate smalldatetime, EndDate small datetime. Patientin integer, Physician in integer, CONSTRAINT PK Referrals PRIMARY KEY (RefercalIR). CONSTRAINT EK Referrals Patients FOREIGN KEY (PatientID) REFERENCES Patients, CONSTRAINT EK Referrals...Physicians FOREIGN KEY (PhysicianIR) REFERENCES Physicians) go CREATE TABLE Services ServiceIn integer, ServiceName varchar(50), CONSTRAINT PK ServiceID PRIMARY KEY (Service BO CREATE TABLE Frequencies FrequencyIn integer. Frequency varchar 30 CONSTRAINT PK_Erequencies PRIMARY KEY (FrequencyID) go CREATE TABLE Referralservices, (Refercalin integer, ServiceIR integer, Erequency ID integer, CONSTRAINT PK ReferralServices. PRIMARY KEY (Refercalin, ServiceIn). CONSTRAINT EK ReferralServices Referrals. FOREIGN KEY (ReferralID REFERENCES Referrals, CONSTRAINT EK RefecralServices Services. FOREIGN KEY (ServiceID) REFERENCES Services, CONSTRAINT EK Referralservices Frequencies. FOREIGN KEY (ErequencyId) REFERENCES Frequencies) go CREATE TABLE Rayment Tyres. (Payment IyRetn integer, Payment Tyre varchar(25), CONSTRAINT PK Payment Tyres, PRIMARY KEY (Payment. TYRID)) go CREATE TABLE Insurance Companies. (InsucanceIn integer, Insurance Company varchar(50) Address Line1 varchar (58) Address Line2 varchar(50) Zincode varchar(10 Phone varchar 15 Fax varchar(15) Email varchar 450 CONSTRAINT PK Insurance Companies PRIMARY KEY Insurance ID CONSTRAINT EllensucanceCompanies Zipcodes FOREIGN KEV Zipcode. REFERENCES Zipcodes. go CREATE TABLE Contracts (Cont.cactin integer, Referralin integer, StartDate smalldatetime. EndDate smalldatetime, Payment TypeIn integer, InsuranceIR integer NegotiatedRate float CONSTRAINT PK. Contracts PRIMARY KEY Contract CONSTRAINT PK contcacts Referrals FOREIGN KEY CROCHIR) REFERENCES Referrals, CONSTRAINT EK Contracts Payment Types FOREIGN KEY PaymentTypeIR REFERENCES Payment Types CONSTRAINT EK Contracts Insurance Companies. FOREIGN KEY InsuranceID REFERENCES Insurance Companies go CREATE TABLE EnplaveeJypes (Employee TypeIR integer identity. EmployeeLyre varchar(25), CONSTRAINT PK.EmployeeTypes PRIMARY KEY (EmployeeTypeID)) go CREATE TABLE EmployeeTitles. (Landaveetitiei integer, Employee Title varchar(38), CONSTRAINT PK. EmployeeLitles. PRIMARY KEY (EmployeeTitleID)) go CREATE TABLE Employeeskilllexels. Skilllevel in integer, Skilllexel varchar(15), CONSTRAINT PK. Employeeskillevels PRIMARY KEY (skilllevelin) go CREATE TABLE BillingRates. (Employee Type IR integer Skulevel in integer BillineRate float CONSTRAINT cinaykey PRIMARY KEY EmployeeTypeID. Skilllevel) CONSTRAINT EXUBIllingRates Employee types. FOREIGN KEY EmployeeTypeID REFERENCES Employee Types CONSTRAINT EKBllingRates Employeeskinlevels FOREIGN KEY SkilllevelID REFERENCES Employeeskilllevels) go Focus CREATE TABLE EmployeeRanks (BankIn integer, Emplovec. TypeID integer, TitleID integer, Skilllevelin integer, Hourly rate float, Salary float, CONSTRAINT PK EmployeeRanks PRIMARY KEY (RankIR), CONSTRAINT EK EmployeeRanks. Employee Iynes. FOREIGN KEY (Employee TypeIR) REFERENCES Carloyee Tyres. CONSTRAINT EK Employee Ranks. Employeelitles. FOREIGN KEY (TitleID) REFERENCES Employee titles. CONSTRAINT EK EmployeeRanks...Employeeskill levels. FOREIGN KEY (SkilllevelIR) REFERENCES Employeeskilllevels go CREATE TABLE Employees (Employees integer, FirstName varchar(30) MiddleInitial varchar(1), Last Name varchar(50) Address Linen varchar(50) Address Line2 yarchar(50) Zipcode varchar(10) Phone varchar(14) Cell Phone varchar(14), Email varchar(5e), BankID Integer, HourlyWage float, LALARIN 23 5 Salary Float, CONSTRAINT PK. Employees. PRIMARY KEY (Employee IQ), CONSTRAINT EK Carlexees. EmployeeRanks. FOREIGN KEY (Rank) REFERENCES Earlaves Banks. CONSTRAINT EKUIER. Loxes Zipcodes. FOREIGN KEY (Zincade) REFERENCES Zirsades.) 80 CREATE TABLE Shifts (Shiftin integer, Shift Nans varchar(20), StartTime tine, EndTime tine, CONSTRAINT DK Shifts PRIMARY KEY (ShiftID)) go CREATE TABLE Daysofbleek (RayofWeekin, integer, Dusk varchar(15), CONSTRAINT PK DaysOfWeek PRIMARY KEY (Rayof WesKID)) CREATE TABLE Availability Employee integer Weekor smalldatetime DayofweckId integer shift integer CONSTRAINT PKI Avaliability PRIMARY KEY EmployeeIQ Weskof Dayofkeskio. Shift CONSTRAINT EK Avalabil Employees. FOREIGN KEY EmployeeIR REFERENCES Employees CONSTRAINT EKLARATSIRAxsonce. FOREIGN KEY DavofleckID REFERENCES Daysofbeek CONSTRAINT EKUADORES FOREIGN KEY Shift D REFERENCES shifts go CREATE TABLE Medicalsunliccs. (Sunrisci integer, Suppliec None varchar(50), Address Linei varchar(50), Address Line2 varchar(5e), Zircode varchar(10), Phone varchar(14), Fax varchar(14), Email varchar(50), CONSTRAINT PK Medical Suppliers. PRIMARY KEY (Suppliecin), CONSTRAINT EK. Medical Suppliers.Zipcodes. FOREIGN KEY (ZipCode) REFERENCES ZipCodes) CREATE TABLE Supplies (SupRUVIR integer, SWARI Description varchar(40) Chargeperiod float, CONSTRAINT PK Supplies PRIMARY KEY (SURRLVIR)) BO CREATE TABLE SURRlyinventory Star VID integer Supplilerin integer DALER ved small datetime Unitat float Quantity float CONSTRAINT PK SupplyInventory PRIMARY KEY SupplyID Suppliecin. Datesceived CONSTRAINT EKL Supplyinventory Supplies FOREIGN KEY SURYAD REFERENCES Supplies CONSTRAINT EK Suplyinventory Suppliers. FOREIGN KEY (SuppliecID REFERENCES dedica suppliers go 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 (Employee ID REFERENCES Employees, CONSTRAINT EK Visits Patients. FOREIGN KEY (Patient ID) REFERENCES Patients) go CREATE TABLE VisitDetails (VisitID integer, VisitDetailID integer, SupplyID integer, SupplyQuantity integer, Service ID integer Charge float CONSTRAINT PK VisitDetails PRIMARY KEY (VisitID. VisitDetailID CONSTRAINT FK VisitDetaiils Supplies FOREIGN KEY SupplyID REFERENCES Supplies, CONSTRAINT FRIVIS itDetails Services. FOREIGN KEY ServiceIDS 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 Patient Name from Patients where lastname like P% order by lastname Focus 33. List the number of Total number of patients (single value) 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 Total number of 4" self-adhesive number of 4" self- adhesive bandages that bandages (single value) were used in 2014 35. List the average Month, average cost per visit charge per visit per month in 2013 broken Sort order: month number - ascending out by months 36. Provide a unique list Patient Last Name, Patient First Name of patients who Sort order: Patient Last Name - received visits for feeding from November ascending 1, 2014 until the Patient First Name - current date. ascending Step 1: Lapte creation Create a new database and execute the code below in SQL Server's query window to create the database tables. CREATE TABLE PhysicianSpecialties. (Spesialt in integer, SpecialtxNane varchar(50), CONSTRAINT PK Physicianspecialties PRIMARY KEY (Spesial xid)) go CREATE TABLE Zipcodes. (Zincade varchar(10), City varchar(50). State varchar(2), CONSTRAINT PK ZipCodes PRIMARY KEY (ZipCode.) go CREATE TABLE PhysicianPractices PracticeIR Integer PracticeName varchar(50), Address Line1 varchar(50) Address Line2 archack58) zincode varchar(10) Phone archani 14 Fax vandhan 14 Website varcharse CONSTRAINT PK Physician cactices PRIMARY KEY (Practice CONSTRAINT EXUPysicianenadiices Zipcodes. FOREIGN KEYZincode REFERENCES Zipcodes.) 80 CREATE TABLE Physicians (PhysicianIn integer, FirstNane varchar(40), Last Name, xarchar(58), Practice integer, Specialty integer, Email varchar(58), CONSTRAINT PK Physicians PRIMARY KEY (Physician IR), CONSTRAINT EK..Physicians.Pcactices. FOREIGN KEY (ecasticeIR) REFERENCES. PhysicianPractices. CONSTRAINT EK..Pusicians..ehysicianSpesialities FOREIGN KEY (SpecialtyID) REFERENCES Physicianspecialties) go CREATE TABLE Patients (Patient ID integer, FirstName varchar(50), MiddleInitial sacchar(1), Last Name varchar(50) Address Line1 xarchar(50). Address Line2 varchar5e). Zipcode varchar(10), Phone. Hone varchar(14) Phone Alternate, archar 14) Email kacchar(50 CONSTRAINT PKL Patients PRIMARY KEY Patient ID go CREATE TABLE Referrals (Refercalis integer, StartDate smalldatetime, EndDate small datetime. Patientin integer, Physician in integer, CONSTRAINT PK Referrals PRIMARY KEY (RefercalIR). CONSTRAINT EK Referrals Patients FOREIGN KEY (PatientID) REFERENCES Patients, CONSTRAINT EK Referrals...Physicians FOREIGN KEY (PhysicianIR) REFERENCES Physicians) go CREATE TABLE Services ServiceIn integer, ServiceName varchar(50), CONSTRAINT PK ServiceID PRIMARY KEY (Service BO CREATE TABLE Frequencies FrequencyIn integer. Frequency varchar 30 CONSTRAINT PK_Erequencies PRIMARY KEY (FrequencyID) go CREATE TABLE Referralservices, (Refercalin integer, ServiceIR integer, Erequency ID integer, CONSTRAINT PK ReferralServices. PRIMARY KEY (Refercalin, ServiceIn). CONSTRAINT EK ReferralServices Referrals. FOREIGN KEY (ReferralID REFERENCES Referrals, CONSTRAINT EK RefecralServices Services. FOREIGN KEY (ServiceID) REFERENCES Services, CONSTRAINT EK Referralservices Frequencies. FOREIGN KEY (ErequencyId) REFERENCES Frequencies) go CREATE TABLE Rayment Tyres. (Payment IyRetn integer, Payment Tyre varchar(25), CONSTRAINT PK Payment Tyres, PRIMARY KEY (Payment. TYRID)) go CREATE TABLE Insurance Companies. (InsucanceIn integer, Insurance Company varchar(50) Address Line1 varchar (58) Address Line2 varchar(50) Zincode varchar(10 Phone varchar 15 Fax varchar(15) Email varchar 450 CONSTRAINT PK Insurance Companies PRIMARY KEY Insurance ID CONSTRAINT EllensucanceCompanies Zipcodes FOREIGN KEV Zipcode. REFERENCES Zipcodes. go CREATE TABLE Contracts (Cont.cactin integer, Referralin integer, StartDate smalldatetime. EndDate smalldatetime, Payment TypeIn integer, InsuranceIR integer NegotiatedRate float CONSTRAINT PK. Contracts PRIMARY KEY Contract CONSTRAINT PK contcacts Referrals FOREIGN KEY CROCHIR) REFERENCES Referrals, CONSTRAINT EK Contracts Payment Types FOREIGN KEY PaymentTypeIR REFERENCES Payment Types CONSTRAINT EK Contracts Insurance Companies. FOREIGN KEY InsuranceID REFERENCES Insurance Companies go CREATE TABLE EnplaveeJypes (Employee TypeIR integer identity. EmployeeLyre varchar(25), CONSTRAINT PK.EmployeeTypes PRIMARY KEY (EmployeeTypeID)) go CREATE TABLE EmployeeTitles. (Landaveetitiei integer, Employee Title varchar(38), CONSTRAINT PK. EmployeeLitles. PRIMARY KEY (EmployeeTitleID)) go CREATE TABLE Employeeskilllexels. Skilllevel in integer, Skilllexel varchar(15), CONSTRAINT PK. Employeeskillevels PRIMARY KEY (skilllevelin) go CREATE TABLE BillingRates. (Employee Type IR integer Skulevel in integer BillineRate float CONSTRAINT cinaykey PRIMARY KEY EmployeeTypeID. Skilllevel) CONSTRAINT EXUBIllingRates Employee types. FOREIGN KEY EmployeeTypeID REFERENCES Employee Types CONSTRAINT EKBllingRates Employeeskinlevels FOREIGN KEY SkilllevelID REFERENCES Employeeskilllevels) go Focus CREATE TABLE EmployeeRanks (BankIn integer, Emplovec. TypeID integer, TitleID integer, Skilllevelin integer, Hourly rate float, Salary float, CONSTRAINT PK EmployeeRanks PRIMARY KEY (RankIR), CONSTRAINT EK EmployeeRanks. Employee Iynes. FOREIGN KEY (Employee TypeIR) REFERENCES Carloyee Tyres. CONSTRAINT EK Employee Ranks. Employeelitles. FOREIGN KEY (TitleID) REFERENCES Employee titles. CONSTRAINT EK EmployeeRanks...Employeeskill levels. FOREIGN KEY (SkilllevelIR) REFERENCES Employeeskilllevels go CREATE TABLE Employees (Employees integer, FirstName varchar(30) MiddleInitial varchar(1), Last Name varchar(50) Address Linen varchar(50) Address Line2 yarchar(50) Zipcode varchar(10) Phone varchar(14) Cell Phone varchar(14), Email varchar(5e), BankID Integer, HourlyWage float, LALARIN 23 5 Salary Float, CONSTRAINT PK. Employees. PRIMARY KEY (Employee IQ), CONSTRAINT EK Carlexees. EmployeeRanks. FOREIGN KEY (Rank) REFERENCES Earlaves Banks. CONSTRAINT EKUIER. Loxes Zipcodes. FOREIGN KEY (Zincade) REFERENCES Zirsades.) 80 CREATE TABLE Shifts (Shiftin integer, Shift Nans varchar(20), StartTime tine, EndTime tine, CONSTRAINT DK Shifts PRIMARY KEY (ShiftID)) go CREATE TABLE Daysofbleek (RayofWeekin, integer, Dusk varchar(15), CONSTRAINT PK DaysOfWeek PRIMARY KEY (Rayof WesKID)) CREATE TABLE Availability Employee integer Weekor smalldatetime DayofweckId integer shift integer CONSTRAINT PKI Avaliability PRIMARY KEY EmployeeIQ Weskof Dayofkeskio. Shift CONSTRAINT EK Avalabil Employees. FOREIGN KEY EmployeeIR REFERENCES Employees CONSTRAINT EKLARATSIRAxsonce. FOREIGN KEY DavofleckID REFERENCES Daysofbeek CONSTRAINT EKUADORES FOREIGN KEY Shift D REFERENCES shifts go CREATE TABLE Medicalsunliccs. (Sunrisci integer, Suppliec None varchar(50), Address Linei varchar(50), Address Line2 varchar(5e), Zircode varchar(10), Phone varchar(14), Fax varchar(14), Email varchar(50), CONSTRAINT PK Medical Suppliers. PRIMARY KEY (Suppliecin), CONSTRAINT EK. Medical Suppliers.Zipcodes. FOREIGN KEY (ZipCode) REFERENCES ZipCodes) CREATE TABLE Supplies (SupRUVIR integer, SWARI Description varchar(40) Chargeperiod float, CONSTRAINT PK Supplies PRIMARY KEY (SURRLVIR)) BO CREATE TABLE SURRlyinventory Star VID integer Supplilerin integer DALER ved small datetime Unitat float Quantity float CONSTRAINT PK SupplyInventory PRIMARY KEY SupplyID Suppliecin. Datesceived CONSTRAINT EKL Supplyinventory Supplies FOREIGN KEY SURYAD REFERENCES Supplies CONSTRAINT EK Suplyinventory Suppliers. FOREIGN KEY (SuppliecID REFERENCES dedica suppliers go 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 (Employee ID REFERENCES Employees, CONSTRAINT EK Visits Patients. FOREIGN KEY (Patient ID) REFERENCES Patients) go CREATE TABLE VisitDetails (VisitID integer, VisitDetailID integer, SupplyID integer, SupplyQuantity integer, Service ID integer Charge float CONSTRAINT PK VisitDetails PRIMARY KEY (VisitID. VisitDetailID CONSTRAINT FK VisitDetaiils Supplies FOREIGN KEY SupplyID REFERENCES Supplies, CONSTRAINT FRIVIS itDetails Services. FOREIGN KEY ServiceIDS 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 Patient Name from Patients where lastname like P% order by lastname Focus 33. List the number of Total number of patients (single value) 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 Total number of 4" self-adhesive number of 4" self- adhesive bandages that bandages (single value) were used in 2014 35. List the average Month, average cost per visit charge per visit per month in 2013 broken Sort order: month number - ascending out by months 36. Provide a unique list Patient Last Name, Patient First Name of patients who Sort order: Patient Last Name - received visits for feeding from November ascending 1, 2014 until the Patient First Name - current date. ascending