Question

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 PK_ZipCodes PRIMARY KEY (ZipCode))

go

CREATE TABLE PhysicianPractices

(PracticeID integer,

PracticeName varchar(50),

Address_Line1 varchar(50),

Address_Line2 varchar(50),

ZipCode varchar(10),

Phone varchar(14),

Fax varchar(14),

WebsiteURL varchar(50),

CONSTRAINT PK_PhysicianPractices PRIMARY KEY (PracticeID),

CONSTRAINT FK_PhysicianPractices_ZipCodes FOREIGN KEY (ZipCode) REFERENCES Zipcodes)

go

CREATE TABLE Physicians

(PhysicianID integer,

FirstName varchar(40),

LastName varchar(50),

PracticeID integer,

SpecialtyID integer,

Email varchar(50),

CONSTRAINT PK_Physicians PRIMARY KEY (PhysicianID),

CONSTRAINT FK_Physicians_Practices FOREIGN KEY (PracticeID) REFERENCES PhysicianPractices,

CONSTRAINT FK_Physicians_PhysicianSpecialities FOREIGN KEY (SpecialtyID) REFERENCES PhysicianSpecialties)

go

CREATE TABLE Patients

(PatientID integer,

FirstName varchar(50),

MiddleInitial varchar(1),

LastName varchar(50),

Address_Line1 varchar(50),

Address_Line2 varchar(50),

ZipCode varchar(10),

Phone_Home varchar(14),

Phone_Alternate varchar(14),

Email varchar(50),

CONSTRAINT PK_Patients PRIMARY KEY (PatientID))

go

CREATE TABLE Referrals

(ReferralID integer,

StartDate smalldatetime,

EndDate smalldatetime,

PatientID integer,

PhysicianID integer,

CONSTRAINT PK_Referrals PRIMARY KEY (ReferralID),

CONSTRAINT FK_Referrals_Patients FOREIGN KEY (PatientID) REFERENCES Patients,

CONSTRAINT FK_Referrals_Physicians FOREIGN KEY (PhysicianID) REFERENCES Physicians)

go

CREATE TABLE Services

(ServiceID integer,

ServiceName varchar(50),

CONSTRAINT PK_ServiceID PRIMARY KEY (ServiceID))

go

CREATE TABLE Frequencies

(FrequencyID integer,

Frequency varchar(30),

CONSTRAINT PK_Frequencies PRIMARY KEY (FrequencyID))

go

CREATE TABLE ReferralServices

(ReferralID integer,

ServiceID integer,

FrequencyID integer,

CONSTRAINT PK_ReferralServices 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 (FrequencyID) REFERENCES Frequencies)

go

CREATE TABLE PaymentTypes

(PaymentTypeID integer,

PaymentType varchar(25),

CONSTRAINT PK_PaymentTypes PRIMARY KEY (PaymentTypeID))

go

CREATE TABLE InsuranceCompanies

(InsuranceID integer,

InsuranceCompany varchar(50),

Address_Line1 varchar(50),

Address_Line2 varchar(50),

ZipCode varchar(10),

Phone varchar(15),

Fax varchar(15),

Email varchar(50),

CONSTRAINT PK_InsuranceCompanies PRIMARY KEY (InsuranceID),

CONSTRAINT FK_InsuranceCompanies_ZipCodes FOREIGN KEY (ZipCode) REFERENCES ZipCodes)

go

CREATE TABLE Contracts

(ContractID integer,

ReferralID integer,

StartDate smalldatetime,

EndDate smalldatetime,

PaymentTypeID integer,

InsuranceID integer,

NegotiatedRate float,

CONSTRAINT PK_Contracts PRIMARY KEY (ContractID),

CONSTRAINT PK_Contracts_Referrals FOREIGN KEY (ReferralID) REFERENCES Referrals,

CONSTRAINT FK_Contracts_PaymentTypes FOREIGN KEY (PaymentTypeID) REFERENCES PaymentTypes,

CONSTRAINT FK_Contracts_InsuranceCompanies FOREIGN KEY (InsuranceID) REFERENCES InsuranceCompanies)

go

CREATE TABLE EmployeeTypes

(EmployeeTypeID integer identity,

EmployeeType varchar(25),

CONSTRAINT PK_EmployeeTypes PRIMARY KEY (EmployeeTypeID))

go

CREATE TABLE EmployeeTitles

(EmployeeTitleID integer,

EmployeeTitle varchar(30),

CONSTRAINT PK_EmployeeTitles PRIMARY KEY (EmployeeTitleID))

go

CREATE TABLE EmployeeSkillLevels

(SkillLevelID integer,

SkillLevel varchar(15),

CONSTRAINT PK_EmployeeSkillLevels PRIMARY KEY (SkillLevelID))

go

CREATE TABLE BillingRates

(EmployeeTypeID integer,

SkillLevelID integer,

BillingRate float,

CONSTRAINT PK_PrimaryKey PRIMARY KEY (EmployeeTypeID, SkillLevelID),

CONSTRAINT FK_BillingRates_EmployeeTypes FOREIGN KEY (EmployeeTypeID) REFERENCES EmployeeTypes,

CONSTRAINT FK_BillingRates_EmployeeSkillLevels FOREIGN KEY (SkillLevelID) REFERENCES EmployeeSkillLevels)

go

CREATE TABLE EmployeeRanks

(RankID integer,

EmployeeTypeID integer,

TitleID integer,

SkillLevelID integer,

HourlyRate float,

Salary float,

CONSTRAINT PK_EmployeeRanks PRIMARY KEY (RankID),

CONSTRAINT FK_EmployeeRanks_EmployeeTypes FOREIGN KEY (EmployeeTypeID) REFERENCES EmployeeTypes,

CONSTRAINT FK_EmployeeRanks_EmployeeTitles FOREIGN KEY (TitleID) REFERENCES EmployeeTitles,

CONSTRAINT FK_EmployeeRanks_EmployeeSkillLevels FOREIGN KEY (SkillLevelID) REFERENCES EmployeeSkillLevels)

go

CREATE TABLE Employees

(EmployeeID integer,

FirstName varchar(30),

MiddleInitial varchar(1),

LastName varchar(50),

Address_Line1 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 FK_Employees_EmployeeRanks FOREIGN KEY (RankID) REFERENCES EmployeeRanks,

CONSTRAINT FK_Employee_ZipCodes FOREIGN KEY (ZipCode) REFERENCES ZipCodes)

go

CREATE TABLE Shifts

(ShiftID integer,

ShiftName varchar(20),

StartTime time,

EndTime time,

CONSTRAINT PK_Shifts PRIMARY KEY (ShiftID))

go

CREATE TABLE DaysOfWeek

(DayOfWeekID integer,

DayOfWeek varchar(15),

CONSTRAINT PK_DaysOfWeek PRIMARY KEY (DayOfWeekID))

go

CREATE TABLE Availability

(EmployeeID integer,

WeekOf smalldatetime,

DayOfWeekID integer,

ShiftID integer,

CONSTRAINT PK_Availability PRIMARY KEY (EmployeeID, WeekOf, DayOfWeekID, ShiftID),

CONSTRAINT FK_Availability_Employees FOREIGN KEY (EmployeeID) REFERENCES Employees,

CONSTRAINT FK_Availability_DaysOfWeek FOREIGN KEY (DayOfWeekID) REFERENCES DaysOfWeek,

CONSTRAINT FK_Availability_Shifts FOREIGN KEY (ShiftID) REFERENCES Shifts)

go

CREATE TABLE MedicalSuppliers

(SupplierID integer,

SupplierName varchar(50),

Address_Line1 varchar(50),

Address_Line2 varchar(50),

ZipCode varchar(10),

Phone varchar(14),

Fax varchar(14),

Email varchar(50),

CONSTRAINT PK_MedicalSuppliers PRIMARY KEY (SupplierID),

CONSTRAINT FK_MedicalSuppliers_ZipCodes FOREIGN KEY (ZipCode) REFERENCES ZipCodes)

go

CREATE TABLE Supplies

(SupplyID integer,

SupplyDescription varchar(40),

ChargePerUnit float,

CONSTRAINT PK_Supplies PRIMARY KEY (SupplyID))

go

CREATE TABLE SupplyInventory

(SupplyID integer,

SupplierID integer,

DateReceived smalldatetime,

UnitCost float,

Quantity float,

CONSTRAINT PK_SupplyInventory PRIMARY KEY (SupplyID, SupplierID, DateReceived),

CONSTRAINT FK_SupplyInventory_Supplies FOREIGN KEY (SupplyID) REFERENCES Supplies,

CONSTRAINT FK_SupplyInventory_Suppliers FOREIGN KEY (SupplierID) REFERENCES MedicalSuppliers)

go

CREATE TABLE Visits

(VisitID integer,

DateRendered smalldatetime,

StartTime time,

EndTime time,

EmployeeID integer,

PatientID integer,

CONSTRAINT PK_Visits PRIMARY KEY (VisitID),

CONSTRAINT FK_Visits_Employees FOREIGN KEY (EmployeeID) REFERENCES Employees,

CONSTRAINT FK_Visits_Patients FOREIGN KEY (PatientID) REFERENCES Patients)

go

CREATE TABLE VisitDetails

(VisitID integer,

VisitDetailID 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

2. Display a list of all patients who have an alternate/cell phone number	Patient First Name, followed by a space, followed by the patient’s last name. (e.g. Melesa Poole), alternate/cell phone number Sort order: Patient First Name – ascending                       Patient Last Name – ascending
3. Display a list of all patients who do not have an email address.	Patient First Name, followed by a space, followed by the patient’s last name. (e.g. Melesa Poole) Sort order: Patient First Name – ascending                       Patient Last Name – ascending
4. Display a list of all patients who live in zipcode 24551.	Patient Last Name, Address1, Address2, City, State, Zip Sort order: Patient Last Name – descending
5. Display a list of all physicians whose specialty is Internal Medicine or Orthopedics	Physician First Name, space, last name (call this column Physician), Specialty Sort order: Physician First Name – ascending                       Physician Last Name – descending
6. Display a list of all physicians, their specialties, and their practices	Physician Last Name, Specialty, Practice Sort order: Physician Specialty – ascending                      Physician Last Name – ascending                      Practice – ascending
7. Display a list of all physicians whose practices are in Lynchburg	Physician Last Name, Practice Name, Address, City, State, Zipcode, Phone Sort order:   Zipcode – ascending                       Practice Name – descending                       Physician Last Name – ascending
8. Display the number of physicians in each specialty	Specialty, number of physicians in each specialty Sort order: Specialty
9. Display the number of physicians in each practice, broken out by specialty	Practice, Specialty, number of physicians in each Sort order: Practice – ascending                     Specialty -- ascending
10. Display the list of specialties that have no physicians assigned to them.	Specialty Sort order: Specialty – ascending

Answer 1

2.

Select FirstName,LastName,Phone-Alternate from Patients order by FirstName,LastName;

3.

Select FirstName,LastName from Patients where Email IS NULL order by Email;

4.

Select Lastname,Address_Line1,Address_Line2, ZipCode from Patients where ZipCode = 24551 order by LastName desc;

5.

Select FirstName,LastName as Physician,SpecialityName from Physicians inner join PhysicianSpecialities on Physicians.SpecialityID = PhysicianSpecialities.SpecialityID order by FirstName,LastName desc;

6.

Select LastName,SpecialityName , PracticeName from Physicians inner join PhysicianSpecialities on Physicians.SpecialityID = PhysicianSpecialities.SpecialityID inner join PhysicianPractices on Physicians.PracticeID = PhysicianPractices.PracticeID order by LastName , PracticeName;

7.

Select LastName , PracticeName, Address_Line1,Address_Line2, ZipCode,Phone from Physicians inner join PhysicianPractices on Physicians.PracticeID = PhysicianPractices.PracticeID order by ZipCode,PracticeName desc, LastName ;

8.

Select SpecialityName, count(PhysicianID) from Physicians inner join PhysicianSpecialities on Physicians.SpecialityID = PhysicianSpecialities.SpecialityID group by SpecialityName order by SpecialityName;

9.

Select PracticeName, count(PhysicianID) from Physicians inner join PhysicianPractices on Physicians.PracticeID = PhysicianPractices.PracticeID group by PracticeName order by PracticeName;

10.

Select SpecialityName from Physicians , PhysicianSpecialities where Physicians.SpecialityID != PhysicianSpecialities.SpecialityID order by SpecialityName;

Do ask if any doubt. Please upvote.