Create a new database and execute the code below in SQL Server's query window to create the database tables, CREATE TABLE PhysicianSpecialties (SpecialtyID integer, SpecialtyName varchar(50), CONSTRAINT PK PhysicianSpecialties. PRIMARY KEY (SpecialtyID)) go CREATE TABLE ZipCodes (ZipCode varchar(10), City varchar(50), State varchar(2), CONSTRAINT PKZipCodes PRIMARY KEY (ZipCode) || go CREATE TABLE PhysicianPractices (PracticeID integer PracticeName Marchank 50) Address Linel varchar(50), Address Line2 macsban 50 Zipcode van hank10 Phone varchar(14) Fax yanchani 14 WebsiteURL varshan se CONSTRAINT PIPhysician Practices PRIMARY KEY (PracticeID) CONSTRAINT EK IPhysician Practices. Zircodes FOREIGN KEYZipCode REFERENCES Zipcodes go CREATE TABLE Physicians (Physician ID integer, FirstName varchar(40), Last Name varchar(50), Practice ID integer, Specialty ID integer, Email varchar(50), CONSTRAINT PK Physicians PRIMARY KEY (Physician ID), CONSTRAINT EK Physicians Practices FOREIGN KEY (PracticeID REFERENCES PhysicianPractices, CONSTRAINT EK Physicians. PhysicianSpecialities. FOREIGN KEY (SpecialtyID) REFERENCES PhysicianSpecialties.) go CREATE TABLE Patients (Patient ID integer, First Name varchar(50) MiddleInitial varchar(1). Last Name varchar(50) Address Line1 yarchar(50), Address Line2 varchar(50) ZipCode varchar(10), Phone Home varchar (14) Phone. Alternate varshan 14 Email varchar(50 CONSTRAINT PK Patients. PRIMARY KEY Patient ID go CREATE TABLE Referrals CREATE TABLE Referrals (Referralt integer, StartDate smalldatetime, EndDate smalldatetime, Patient ID integer, Physician ID integen CONSTRAINT PK Referrals PRIMARY KEY ReferralID CONSTRAINT EK Referrals Patients. FOREIGN KEY Patient ID REFERENCES Patients CONSTRAINT EK Referrals. Physicians FOREIGN KEY PhysicianID REFERENCES Physicians go CREATE TABLE Services (Service ID integer, ServiceName varchar(50), CONSTRAINT PK ServiceID PRIMARY KEY (ServiceID)) go CREATE TABLE Frequencies (Frequency ID integer, Frequency varchar(30), CONSTRAINT PK Frequencies PRIMARY KEY (FrequencyID)) 80 CREATE TABLE ReferralServices, ReferralID integer, ServiceID Integer FrequencyID integer CONSTRAINT PK Referral Services PRIMARY KEY (ReferralID ServiceID), CONSTRAINT FK ReferralServices Referrals FOREIGN KEY (ReferralID REFERENCES Referrals, CONSTRAINT FK ReferralServices Services. FOREIGN KEY (ServiceID REFERENCES Services, CONSTRAINT FK ReferralServices Frequencies. FOREIGN KEY Frequency ID REFERENCES Frequencies go CREATE TABLE Payment Types (Payment TypeID Integer Payment Type varcharl 25 CREATE TABLE Payment Types (PaymentTypeID integer, Payment Type varchar(25), CONSTRAINT PK PaymentTypes. PRIMARY KEY (Payment TypeID)) go CREATE TABLE Insurance Companies. (InsuranceID integer, InsuranceCompany, varchar(50), Address Linel varchar(50), Address Line2 yarchar(50), ZipCode varchar(10). Phone varchar(15) Fax varchar(15) Email varchar50) CONSTRAINT PK Insurance Companies. PRIMARY KEY K Insurance ID) CONSTRAINT EK Insurance Companies. Zipcodes FOREIGN KEY (Zipcode. REFERENCES ZipCodes) BO CREATE TABLE Contracts (Contractin integer, Referralin integer, StartDate small.datetime. EndDate small datetime, Payment TypeID integer, InsuranceID integer, NegotiatedRate float, CONSTRAINT DK Contcasts. PRIMARY KEY (ContractIR), CONSTRAINT PE..Contracts Referrals FOREIGN KEY (RefercalID) REFERENCES Referrals, CONSTRAINT EK..Contracts. Payment Tyres, FOREIGN KEY (PaymentTypeIR) REFERENCES Payment Types CONSTRAINT EKLI Contracts Insurancecompanies FOREIGN KEY InsuranceIR REFERENCES Insurancecompanies go Doc CREATE TABLE EnnloveeTypes. (Employee TypeID integer identity, Employee Tyre varchar(25), CONSTRAINT PKEunloveeTypeS PRIMARY KEY (Employee LYRIR) go CREATE TABLE Engloveetitles. (Employee titleIn integer, Employee Title varchar(30), CONSTRAINT PK.EmplexeeTitles PRIMARY KEY (Employee Title) go CREATE TABLE Employeeskilllexels. (skillLevel ID integer, SkillLevel varchar(15), CONSTRAINT PK. Employeeskill levels. PRIMARY KEY (SkilllexelID)) go CREATE TABLE BillingRates Employee TxReID integer Skill Level IR integer BillingRate float CONSTRAINT PK Primarykey. PRIMARY KEY (EmployeeTypeID SkilllevelID CONSTRAINT EKL BillingRates. Employee Tyres FOREIGN KEY Employee TvreID REFERENCES Employee Tyres. CONSTRAINT EKIBA lingRates. Employeeskillevels. FOREIGN KEYSkillLevel IR REFERENCES Employeeskilllevels go TE CREATE TABLE EmployeeBanks. (BankId integer, Employee Iyeth integer Title integer; Skillevel ID integer HourlyBets float, Salary float, CONSTRAINT PK.Enn LexseRanks. PRIMARY KEY (RankIR), CONSTRAINT EK EmplexeeRanks. Earloxes Tyres. FOREIGN KEY (EmployeeTypeIR) REFERENCES Employee Types CONSTRAINT EK EmplexeeRanks. EmployeeTitles. FOREIGN KEY (TitleIRJ) REFERENCES EmployeeTitles. CONSTRAINT EK. EmplexceRanks. Employeeskilllevels. FOREIGN KEY (SkilllevelID) REFERENCES Employeeskilllexels) eo CREATE TABLE Employees EmployeeIR integer FirstName ye nar 30 MiddleInitial varchar(1), Last Name varchar 50 Address Linel varchar(50) Address Line2 varchar c50 ZipCode Vacchan 10 Phone archar 14 Cell Phone varchar 14) Email: varchar 500 BankID integer HoudlyWage float Salary Float CONSTRAINT PKI Employees PRIMARY KEYExtr loyeeID CONSTRAINT EKLEMRLOXES EmployeeRanks FOREIGN KEY BankID REFERENCES EsplexeeBanks. CONSTRAINTEKTEBLOvercodes FOREIGN KEMERCode REFERENCES ZipCodes go CREATE TABLE Shifts (Shittir. integer, Shift.Name varchar(20), StartTine tine, EndTime tine, CONSTRAINT PK Shifts PRIMARY KEY (ShiftIR) go CREATE TABLE RaysofWeek (Rayof WeekIR integer, Radesk varchar(15), CONSTRAINT PK DaysOfWeek PRIMARY KEY (RayofWeekID)) go CREATE TABLE Availability Employee IR integer, Weekef smalldatetime RayOfWeekin integer Shit in integer, CONSTRAINT PK. Availability PRIMARY KEY EmployeeIR, Weekof. DayOfWeekID. ShiftID. CONSTRAINT EK Availability Employees FOREIGN KEY EmployeeIR REFERENCES Employees, CONSTRAINT EK. Availability DawsofWeek FOREIGN KEY RaxofweekID REFERENCES Daysofiseek. CONSTRAINT EK. Availability Shifts. FOREIGN KEY ShiftID REFERENCES Shifts go CREATE TABLE MedicalSuppliecs. (Supplier in integer, SurrliscNane varchar(52), Address Linei varchar(50), Address Line2 varchar(50), Zircode varchar(10), Phone varchar(14), Fax varchar(14), Email varchar(5e), CONSTRAINT PK Medical Suppliers. PRIMARY KEY (Suppliecin), CONSTRAINT EK MedicalSurrlicu.ZipCodes, FOREIGN KEY (ZipCode) REFERENCES Zircodes) CREATE TABLE Supplies (Supplir integer Suprlydesscirtien varchar(40), Changelou float, CONSTRAINT PK Supplies PRIMARY KEY SupplyID)) co CREATE TABLE SUR Inventory (SUBRIVIR integen SupRCID integer, DateReceived smalldatetus Voit.cast float, Quantity float CONSTRAINT PELISpalvinxenter. PRIMARY KEY SURRO SURRI CR DateReceived CONSTRAINT EKSupalvivente surelites FOREIGN KEM SLARIMIO REFERENCES Supplies CONSTRAINT EUSURR Venter suppliers. FOREIGN KEY SPRIECIRREFERENCES MedicalSuppliers CREATE TABLE Visits (Xisiti integer, RateRendered salidatetis Startline tine, Endtime, time, EmployeeIR integer, Patientis integer, CONSTRAINT PK. Visits PRIMARY KEY Visitin CONSTRAINT EK..Visits... Employees FOREIGN KEY (EmployeeIR REFERENCES Employees. CONSTRAINT FK.Xiaits. Patients. FOREIGN KEY PatientIR) REFERENCES Patients go CREATE TABLE VisitRetails. (Visit integer, VisitRetailid. integer, Surrlvir integer, SupplyQuantity integer, Secxiseid integer, Charge float, CONSTRAINT PK. VisitDetails PRIMARY KEY (Visitin, VisitRetailin), CONSTRAINT FK VisitRetaiils.Surrdies FOREIGN KEY (SupplyID) REFERENCES Supplies, CONSTRAINT EK VisitRetails... Secrises. FOREIGN KEY (SecxiseiR) 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 "PM! ascending select Lastname FirstName as Patient Name from Patients where lastname le PS order by lastname 11. Display a list of all referrals whose start date was in 2013. Patient first name, followed by a space, followed by patient last name (Call this whole field "Patient Name"), Referring Physician Last Name (call this field "Physician"), StartDate, EndDate Sort Order: StartDate - ascending Patient First Name - ascending Physician Last Name - ascending 16. Display a list of any referrals that require "Insulin injections" and "2x Daily" is NOT listed as their frequency Patient Last Name, Physician Last Name, referral start date Sort order: Physician Last Name - ascending Patient Last Name - ascending Referral Start Date - ascending Create a new database and execute the code below in SQL Server's query window to create the database tables, CREATE TABLE PhysicianSpecialties (SpecialtyID integer, SpecialtyName varchar(50), CONSTRAINT PK PhysicianSpecialties. PRIMARY KEY (SpecialtyID)) go CREATE TABLE ZipCodes (ZipCode varchar(10), City varchar(50), State varchar(2), CONSTRAINT PKZipCodes PRIMARY KEY (ZipCode) || go CREATE TABLE PhysicianPractices (PracticeID integer PracticeName Marchank 50) Address Linel varchar(50), Address Line2 macsban 50 Zipcode van hank10 Phone varchar(14) Fax yanchani 14 WebsiteURL varshan se CONSTRAINT PIPhysician Practices PRIMARY KEY (PracticeID) CONSTRAINT EK IPhysician Practices. Zircodes FOREIGN KEYZipCode REFERENCES Zipcodes go CREATE TABLE Physicians (Physician ID integer, FirstName varchar(40), Last Name varchar(50), Practice ID integer, Specialty ID integer, Email varchar(50), CONSTRAINT PK Physicians PRIMARY KEY (Physician ID), CONSTRAINT EK Physicians Practices FOREIGN KEY (PracticeID REFERENCES PhysicianPractices, CONSTRAINT EK Physicians. PhysicianSpecialities. FOREIGN KEY (SpecialtyID) REFERENCES PhysicianSpecialties.) go CREATE TABLE Patients (Patient ID integer, First Name varchar(50) MiddleInitial varchar(1). Last Name varchar(50) Address Line1 yarchar(50), Address Line2 varchar(50) ZipCode varchar(10), Phone Home varchar (14) Phone. Alternate varshan 14 Email varchar(50 CONSTRAINT PK Patients. PRIMARY KEY Patient ID go CREATE TABLE Referrals CREATE TABLE Referrals (Referralt integer, StartDate smalldatetime, EndDate smalldatetime, Patient ID integer, Physician ID integen CONSTRAINT PK Referrals PRIMARY KEY ReferralID CONSTRAINT EK Referrals Patients. FOREIGN KEY Patient ID REFERENCES Patients CONSTRAINT EK Referrals. Physicians FOREIGN KEY PhysicianID REFERENCES Physicians go CREATE TABLE Services (Service ID integer, ServiceName varchar(50), CONSTRAINT PK ServiceID PRIMARY KEY (ServiceID)) go CREATE TABLE Frequencies (Frequency ID integer, Frequency varchar(30), CONSTRAINT PK Frequencies PRIMARY KEY (FrequencyID)) 80 CREATE TABLE ReferralServices, ReferralID integer, ServiceID Integer FrequencyID integer CONSTRAINT PK Referral Services PRIMARY KEY (ReferralID ServiceID), CONSTRAINT FK ReferralServices Referrals FOREIGN KEY (ReferralID REFERENCES Referrals, CONSTRAINT FK ReferralServices Services. FOREIGN KEY (ServiceID REFERENCES Services, CONSTRAINT FK ReferralServices Frequencies. FOREIGN KEY Frequency ID REFERENCES Frequencies go CREATE TABLE Payment Types (Payment TypeID Integer Payment Type varcharl 25 CREATE TABLE Payment Types (PaymentTypeID integer, Payment Type varchar(25), CONSTRAINT PK PaymentTypes. PRIMARY KEY (Payment TypeID)) go CREATE TABLE Insurance Companies. (InsuranceID integer, InsuranceCompany, varchar(50), Address Linel varchar(50), Address Line2 yarchar(50), ZipCode varchar(10). Phone varchar(15) Fax varchar(15) Email varchar50) CONSTRAINT PK Insurance Companies. PRIMARY KEY K Insurance ID) CONSTRAINT EK Insurance Companies. Zipcodes FOREIGN KEY (Zipcode. REFERENCES ZipCodes) BO CREATE TABLE Contracts (Contractin integer, Referralin integer, StartDate small.datetime. EndDate small datetime, Payment TypeID integer, InsuranceID integer, NegotiatedRate float, CONSTRAINT DK Contcasts. PRIMARY KEY (ContractIR), CONSTRAINT PE..Contracts Referrals FOREIGN KEY (RefercalID) REFERENCES Referrals, CONSTRAINT EK..Contracts. Payment Tyres, FOREIGN KEY (PaymentTypeIR) REFERENCES Payment Types CONSTRAINT EKLI Contracts Insurancecompanies FOREIGN KEY InsuranceIR REFERENCES Insurancecompanies go Doc CREATE TABLE EnnloveeTypes. (Employee TypeID integer identity, Employee Tyre varchar(25), CONSTRAINT PKEunloveeTypeS PRIMARY KEY (Employee LYRIR) go CREATE TABLE Engloveetitles. (Employee titleIn integer, Employee Title varchar(30), CONSTRAINT PK.EmplexeeTitles PRIMARY KEY (Employee Title) go CREATE TABLE Employeeskilllexels. (skillLevel ID integer, SkillLevel varchar(15), CONSTRAINT PK. Employeeskill levels. PRIMARY KEY (SkilllexelID)) go CREATE TABLE BillingRates Employee TxReID integer Skill Level IR integer BillingRate float CONSTRAINT PK Primarykey. PRIMARY KEY (EmployeeTypeID SkilllevelID CONSTRAINT EKL BillingRates. Employee Tyres FOREIGN KEY Employee TvreID REFERENCES Employee Tyres. CONSTRAINT EKIBA lingRates. Employeeskillevels. FOREIGN KEYSkillLevel IR REFERENCES Employeeskilllevels go TE CREATE TABLE EmployeeBanks. (BankId integer, Employee Iyeth integer Title integer; Skillevel ID integer HourlyBets float, Salary float, CONSTRAINT PK.Enn LexseRanks. PRIMARY KEY (RankIR), CONSTRAINT EK EmplexeeRanks. Earloxes Tyres. FOREIGN KEY (EmployeeTypeIR) REFERENCES Employee Types CONSTRAINT EK EmplexeeRanks. EmployeeTitles. FOREIGN KEY (TitleIRJ) REFERENCES EmployeeTitles. CONSTRAINT EK. EmplexceRanks. Employeeskilllevels. FOREIGN KEY (SkilllevelID) REFERENCES Employeeskilllexels) eo CREATE TABLE Employees EmployeeIR integer FirstName ye nar 30 MiddleInitial varchar(1), Last Name varchar 50 Address Linel varchar(50) Address Line2 varchar c50 ZipCode Vacchan 10 Phone archar 14 Cell Phone varchar 14) Email: varchar 500 BankID integer HoudlyWage float Salary Float CONSTRAINT PKI Employees PRIMARY KEYExtr loyeeID CONSTRAINT EKLEMRLOXES EmployeeRanks FOREIGN KEY BankID REFERENCES EsplexeeBanks. CONSTRAINTEKTEBLOvercodes FOREIGN KEMERCode REFERENCES ZipCodes go CREATE TABLE Shifts (Shittir. integer, Shift.Name varchar(20), StartTine tine, EndTime tine, CONSTRAINT PK Shifts PRIMARY KEY (ShiftIR) go CREATE TABLE RaysofWeek (Rayof WeekIR integer, Radesk varchar(15), CONSTRAINT PK DaysOfWeek PRIMARY KEY (RayofWeekID)) go CREATE TABLE Availability Employee IR integer, Weekef smalldatetime RayOfWeekin integer Shit in integer, CONSTRAINT PK. Availability PRIMARY KEY EmployeeIR, Weekof. DayOfWeekID. ShiftID. CONSTRAINT EK Availability Employees FOREIGN KEY EmployeeIR REFERENCES Employees, CONSTRAINT EK. Availability DawsofWeek FOREIGN KEY RaxofweekID REFERENCES Daysofiseek. CONSTRAINT EK. Availability Shifts. FOREIGN KEY ShiftID REFERENCES Shifts go CREATE TABLE MedicalSuppliecs. (Supplier in integer, SurrliscNane varchar(52), Address Linei varchar(50), Address Line2 varchar(50), Zircode varchar(10), Phone varchar(14), Fax varchar(14), Email varchar(5e), CONSTRAINT PK Medical Suppliers. PRIMARY KEY (Suppliecin), CONSTRAINT EK MedicalSurrlicu.ZipCodes, FOREIGN KEY (ZipCode) REFERENCES Zircodes) CREATE TABLE Supplies (Supplir integer Suprlydesscirtien varchar(40), Changelou float, CONSTRAINT PK Supplies PRIMARY KEY SupplyID)) co CREATE TABLE SUR Inventory (SUBRIVIR integen SupRCID integer, DateReceived smalldatetus Voit.cast float, Quantity float CONSTRAINT PELISpalvinxenter. PRIMARY KEY SURRO SURRI CR DateReceived CONSTRAINT EKSupalvivente surelites FOREIGN KEM SLARIMIO REFERENCES Supplies CONSTRAINT EUSURR Venter suppliers. FOREIGN KEY SPRIECIRREFERENCES MedicalSuppliers CREATE TABLE Visits (Xisiti integer, RateRendered salidatetis Startline tine, Endtime, time, EmployeeIR integer, Patientis integer, CONSTRAINT PK. Visits PRIMARY KEY Visitin CONSTRAINT EK..Visits... Employees FOREIGN KEY (EmployeeIR REFERENCES Employees. CONSTRAINT FK.Xiaits. Patients. FOREIGN KEY PatientIR) REFERENCES Patients go CREATE TABLE VisitRetails. (Visit integer, VisitRetailid. integer, Surrlvir integer, SupplyQuantity integer, Secxiseid integer, Charge float, CONSTRAINT PK. VisitDetails PRIMARY KEY (Visitin, VisitRetailin), CONSTRAINT FK VisitRetaiils.Surrdies FOREIGN KEY (SupplyID) REFERENCES Supplies, CONSTRAINT EK VisitRetails... Secrises. FOREIGN KEY (SecxiseiR) 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 "PM! ascending select Lastname FirstName as Patient Name from Patients where lastname le PS order by lastname 11. Display a list of all referrals whose start date was in 2013. Patient first name, followed by a space, followed by patient last name (Call this whole field "Patient Name"), Referring Physician Last Name (call this field "Physician"), StartDate, EndDate Sort Order: StartDate - ascending Patient First Name - ascending Physician Last Name - ascending 16. Display a list of any referrals that require "Insulin injections" and "2x Daily" is NOT listed as their frequency Patient Last Name, Physician Last Name, referral start date Sort order: Physician Last Name - ascending Patient Last Name - ascending Referral Start Date - ascending