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.)
1. Display a list of all patients
who have a last name beginning
with the letter “P”.
Patient Last Name, followed by
a comma and a space, followed
by the patient’s first name. (e.g.
Smith, John)
Sort order: Patient Last Name -
ascending
select Lastname + ', ' +
FirstName as PatientName
from Patients
where lastname like 'P%'
order by lastname
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
Jared
Done
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
McKenna
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
Reagan
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
Selame
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
Jared
Done
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
McKenna
8. Display the number of
physicians in each specialty
Specialty, number of physicians
in each specialty
Sort order: Specialty
Reagan
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
Selame
10. Display the list of specialties
that have no physicians
assigned to them.
Specialty
Sort order: Specialty –
ascending
Jared
Done
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
McKenna
Physician Last Name (call this
field “Physician”), StartDate,
EndDate
Sort Order: StartDate –
ascending
Patient First Name –
ascending
Physician Last Name
- ascending
12. Display a list of all the
referrals whose start date is
between October 1, 2014 and
November 5, 2014
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
Reagan
13. Display the number of
referrals given by each physician
Physician Last name, Physician
First Name, number of referrals
Sort Order: Physician Last Name
– ascending
Physician First Name
– ascending
Selame
14. List the number of referrals
in 2014 for each service
requested.
Service name, number of
referrals
Sort order: Service name
Jared
Done
15. Display a list of all patients
requiring exercise therapy in
2013
Patient Last Name, Patient First
Name
Sort order: Patient last name –
ascending
Patient first name –
ascending
McKenna
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
Reagan
17. Display the contracts and Patient Last Name, Physician Selame
payment methods associated
with each referral
Last Name, Referral Start Date,
Contract Start Date, Payment
Method
Sort Order: Payment Method -
ascending
Physician Last
Name – ascending
Patient Last Name –
ascending
Referral Start Date –
ascending
Contract Start Date
– ascending
18. Display the number of
contracts whose payment
method is Insurance
Number of contracts (This is a
single value)
Jared
Done
19. Display the number of
contracts whose payment
method is Insurance, broken out
by Insurance Company
Insurance Company Name,
number of contracts
Sort order: Insurance company
name
McKenna
20. List the Employees who are
Nurses
Employee First Name, followed
by a space, followed by
Employee Middle Initial,
followed by a space, followed by
Employee Last Name (call this
whole field “Nurses”)
Reagan
21. Display the average hourly
wage for all employees who are
aides.
Average hourly wage (single
value)
Selame
22. Display the average hourly
wage for all hourly employees
broken out by level.
Skill level, average wage
Sort order: Skill Level
Jared
Done
23. Display the total salary for
all salaried employees.
Total salaries (single value) McKenna
24. Display the number of
employees assigned to each
rank.
RankID, Employee Type, Skill
Level, Employee Title, number
of employees
Sort Order: RankID – ascending
Employee type –
ascending
Skill Level –
ascending
Employee Title –
ascending
Reagan
25. Display a list of Employees
who are nurses and were
available to work on Sunday
Employee Last Name, Employee
First Name
Sort order: Last Name –
Selame
evenings during the week of
11/2/2014
ascending
First Name –
ascending
26. Display a list of Employees
who were available to work
during morning shifts during the
week of 11/2/2014 and had a
skill level of level 3.
Employee Last Name, Employee
First Name,
Employee Type, Employee Title
Sort order: Employee Type –
ascending
Employee Title –
ascending
Employee Last
Name – ascending
Employee First
Name – ascending
Jared
Done
27. Display the total quantity of
catheters added to inventory
during 2013.
Total catheters (single value) McKenna
28. Display the total cost of
“sterile gloves – small” provided
by Poole’s Medical supplies
during 2013.
Total cost (single value) Reagan
29. Display the average cost of
supplies for each supply item
broken out by supplier.
Supply, Supplier, Average cost
per supply item
Sort order: Supply – ascending
Supplier – ascending
Selame
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
Jared
Done
31. Display a list of all the visits
that occurred from March 20,
2014 to March 25, 2014
(including March 20 and March
25)
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
McKenna
32. List the total charges for the
visit that occurred on
2/12/2014 for Helen Ramirez
that was provided by Laura
White.
Total charges (single value)
Reagan
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).
Total number of patients (single
value)
Selame
34. List the total number of 4”
self-adhesive bandages that
were used in 2014
Total number of 4” self-adhesive
bandages (single value)
Jared
35. List the average charge per
visit per month in 2013 broken
out by months
Month, average cost per visit
Sort order: month number -
ascending
McKenna
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
Reagan
Queries:
1. Display a list of all patients who have a last name beginning with the letter “P”.
select Lastname + ', ' + FirstName as PatientName
from Patients
where lastname like 'P%'
order by lastname
2. Display a list of all patients who have an alternate/cell phone number.
SELECT LastName + ', ' + FirstName + ', ' + Phone_Alternate FROM Patients
WHERE Phone_Alternate IS NOT NULL
ORDER BY FirstName, LastName
3. Display a list of all patients who do not have an email address.
select FirstName + ' ' + LastName as 'Patient Name'
from Patients
where Email IS NULL
order by FirstName, LastName
4. Display a list of all patients who live in zipcode 24551.
select [Last Name], Address1, Address2, z.City, z.State, p.Zipcode
from Patients p inner join ZipCodes z on p.ZipCode = z.ZipCode
where p.ZipCode = '24551'
order by "Last Name" desc
5. Display a list of all physicians whose specialty is Internal Medicine or Orthopedics.
select p.FirstName + ' ' + p.LastName AS 'Physician', ps.SpecialtyName AS
'Specialty'
from Physicians p LEFT OUTER JOIN PhysicianSpecialties ps ON p.SpecialtyID =
ps.SpecialtyID
where ps.SpecialtyName = 'Internal Medicine' OR ps.SpecialtyName = 'Orthopedics'
order by p.FirstName, p.LastName DESC
Fs
fi
elect
p.Firstame
+
°
+p.
‘Spec
rom
Physicians
p
LEFT
OUTER
JOIN
Physicianspecialties
ps
ON
p.SpecialtyID
=
where ps.SpecialtyName
‘Internal Nedicine’
OR
ps.Speci
order
by
p-Firstllame,
p.Lastilame
DESC|
100%
El
Resuts
|[y
Messages
Physician
Specialty
1
Alan
Brown
Intemal
Medicine
2
“Angela
Nieton”””
Orthopedics
3 Ann
Garcia
Orthopedics
4
Anthony
Ross
Orthopedics
5
April
Hall
Orthopedics
6
Ashley
Sanders
Orthopedics
7
Brian
Ramirez
Orthopedics
8
Chrisina
Henderson
Intemal
Medicine
9
Craig
Cox
‘Intemal
Medicine
10
Cynthia
Rodriguez
Orthopedics
11
Donna
Lacroix
Orthopedics
12
Ear
Perez
Orthopedics
13°
Bric
Ward
Orthopedics
14
Gary
Smith
‘Intemal
Medicine
15
Gloria
Bailey
Irtemal
Medicine
16
Harold
Anderson
Orthopedics
17
rene
Young
Orthopedics
18
Janice
Green
Irtemal
Medicine
19
Jessica
Bell
Irtemal
Medicine
LastName
AS
‘Physician’,
ps.
6. Display a list of all physicians, their specialties, and their practices.
SELECT Physicians.LastName, PhysicianSpecialties.SpecialtyName,
PhysicianPractices.PracticeName FROM Physicians
INNER JOIN PhysicianSpecialties ON PhysicianSpecialties.SpecialtyID =
Physicians.SpecialtyID
INNER JOIN PhysicianPractices ON PhysicianPractices.PracticeID =
Physicians.PracticeID
ORDER BY SpecialtyName, LastName, PracticeName
7. Display a list of all physicians whose practices are in Lynchburg.
select p.LastName as 'Physician Last Name', pr.PracticeName as 'Practice Name',
pr.Address_Line1 as 'Address', z.City, z.State, z.ZipCode, pr.Phone
from Physicians p
LEFT OUTER JOIN PhysicianPractices pr ON p.PracticeID = pr.PracticeID
LEFT OUTER JOIN ZipCodes z ON pr.ZipCode = z.ZipCode
where z.City = 'Lynchburg'
order by z.ZipCode, pr.PracticeName DESC, p.LastName ASC
8. Display the number of physicians in each specialty.
select ps.SpecialtyName as 'Specialty', COUNT(p.SpecialtyID) as 'Number of
Physicians in Each Specialty'
from Physicians p inner join PhysicianSpecialities ps on p.SpecialtyID =
ps.SpecialtyID
group by ps.SpecialtyName
order by SpecialtyName asc
9. Display the number of physicians in each practice, broken out by specialty.
SELECT PracticeName, PhysicianSpecialties.SpecialtyName, COUNT(PhysicianID) AS
'Number of Doctors' FROM Physicians
INNER JOIN PhysicianPractices ON PhysicianPractices.PracticeID =
Physicians.PracticeID
INNER JOIN PhysicianSpecialties ON PhysicianSpecialties.SpecialtyID =
Physicians.SpecialtyID
GROUP BY PracticeName, PhysicianSpecialties.SpecialtyName
ORDER BY PracticeName, SpecialtyName
10. Display the list of specialties that have no
physicians assigned to them.
SELECT SpecialtyName FROM PhysicianSpecialties
WHERE SpecialtyID NOT IN (
SELECT SpecialtyID FROM Physicians
)
ORDER BY SpecialtyName
EISELECT
SpecialtyName
FROM
PhysicianSpecialties
WHERE SpecialtyID
NOT
IN
(
SELECT
SpecialtyID
FROM
Physicians
ORDER
BY
SpecialtyName
100%
~
GH
Results
fy
Messages
SpecialtyName
Gastroenterology
Nephrology
‘Oncology
Otology
-
Neurotology
Thoratic
Surery
ahoeno
wk
11. Display a list of all referrals whose start date was in 2013.
select p.FirstName + ' ' + p.LastName as 'Patient Name', ph.LastName as
'Physician', r.StartDate, r.EndDate
from Referrals r
LEFT OUTER JOIN Patients p ON r.PatientID = p.PatientID
LEFT OUTER JOIN Physicians ph ON r.PhysicianID = ph.PhysicianID
where YEAR(r.StartDate) = '2013'
order by r.StartDate, p.FirstName, ph.LastName
12. Display a list of all the referrals whose start date is between October 1, 2014 and November 5, 2014.
select (Patients.[First Name] + ' ' + Patients.[Last Name]) as PatientName,
Physicians.LastName as Physician,
convert (date, Referrals.StartDate) as StartDate, convert(date,
Referrals.EndDate) as EndDate
from Referrals
inner join Physicians on Physicians.ID = Referrals.PhysicianID
inner join Patients on Patients.ID = Referrals.PatientID
where StartDate between convert(date,'2014-10-01') and convert(date, '2014-11-
05')
order by Referrals.StartDate, Patients.[First Name], Physicians.LastName asc
13. Display the
number of referrals given by each physician.
SELECT LastName, FirstName, Count(Referrals.PhysicianID) AS 'Number of Referrals'
FROM Referrals
INNER JOIN Physicians ON Physicians.PhysicianID = Referrals.PhysicianID
GROUP BY Referrals.PhysicianID, LastName, FirstName
ORDER BY LastName, FirstName
14. List the number of referrals in 2014
for each service requested.
SELECT ServiceName, COUNT(ReferralServices.ReferralID) AS 'Number of Referrals'
FROM ReferralServices
INNER JOIN Referrals ON Referrals.ReferralID = ReferralServices.ReferralID
INNER JOIN Services ON Services.ServiceID = ReferralServices.ServiceID
WHERE StartDate BETWEEN '2014-01-01' AND '2014-12-31'
GROUP BY ServiceName
ORDER BY ServiceName
15. Display a list of all patients requiring exercise
therapy in 2013.
select distinct p.LastName, p.FirstName
from Patients p
CLEFT OUTER JOIN Referrals r ON p.PatientID = r.PatientID
CLEFT OUTER JOIN ReferralServices rs ON r.ReferralID = rs.ReferralID
CLEFT OUTER JOIN Services s ON rs.ServiceID = s.ServiceID
where s.ServiceName = 'Exercise Therapy' AND YEAR(r.StartDate) = '2013'
order by p.LastName, p.FirstName
Eiselect
distinct
p.LastName,
p.FirstNane
from Patients p
LEFT OUTER
JOIN
Referrals
r
ON p.PatientID
r.PatientID
LEFT OUTER
JOIN
ReferralServices
rs
ON
r.ReferralID
rs.ReferralID
LEFT OUTER
JOIN
Services
5
ON
rs.ServiceID
s.ServiceID
here
5.Servicelane
"Exercise
Therapy’
alld
YEAR(r.StartDate)
'2013°
order
by
p.Lastiame,
p.Firstiione|
10%
-
Cl
Rests
|p
Mensoges
Llane
FretNane
1
[vedo]
Lorene
2
“Andrews”
Guillermo
3
Amarong
Wiad
4 Barks
Dora
5
Bamet
el
&
Benet
nice
7
Bake Mons
8
Bd
Ben
3
Clayton
rene
10
Coleman
Mara
1
Coe
Wile
12
foes
Michele
12
Fontaine
Afed
1
foster
Jeremy
18
Gace
Oa
16
Gi
Lora
17
Grant
Boyd
18
Hemandez
Tey
19
Hopkins
Bonnie
16. Display a list of any referrals that require “Insulin injections” and ”2x Daily” is NOT listed as their
frequency.
select Patients.[Last Name], Physicians.LastName as Physician,
Referrals.StartDate
from Referrals
inner join Physicians on Physicians.ID = Referrals.PhysicianID
inner join Patients on Patients.ID = Referrals.PatientID
inner join ReferralServices on ReferralServices.ReferralID = Referrals.ReferralID
where ServiceID = 6 and FrequencyID != 2
order by Physicians.LastName, Patients.[Last Name], Referrals.StartDate asc
17. Display the contracts
and payment methods associated with each referral.
SELECT Patients.LastName, Physicians.LastName, Referrals.StartDate,
Contracts.StartDate, PaymentType FROM Referrals
INNER JOIN Patients ON Patients.PatientID = Referrals.PatientID
INNER JOIN Physicians ON Physicians.PhysicianID = Referrals.PhysicianID
INNER JOIN Contracts ON Contracts.ReferralID = Referrals.ReferralID
INNER JOIN PaymentTypes ON PaymentTypes.PaymentTypeID = Contracts.PaymentTypeID
ORDER BY PaymentType, Physicians.LastName, Patients.LastName,
Referrals.StartDate, Contracts.StartDate
18. Display the number of contracts
whose payment method is Insurance.
SELECT COUNT(ContractID) AS 'Number of Contracts' FROM Contracts
WHERE PaymentTypeID IN (
SELECT PaymentTypeID FROM PaymentTypes
WHERE PaymentType = 'Insurance'
)
EISELECT
COUNT(ContractID)
AS
‘Number
of
Contracts’
FROM
(#|
WHERE
PaymentTypeID
IN
(
A
SELECT
PaymentTypeID
FROM
PaymentTypes
WHERE
Paymentlype
=
‘Insurance’
)
100%
~
<
>
|
Results
[y
Messages
Number
of
Contra
1
(273
19. Display the number of contracts whose payment method is Insurance, broken out by Insurance
Company.
select ic.InsuranceCompany, COUNT(c.ContractID) AS 'Assigned Contracts'
from Contracts c LEFT OUTER JOIN InsuranceCompanies ic ON c.InsuranceID = ic.InsuranceID
where c.InsuranceID IS NOT NULL
group by ic.InsuranceCompany
order by ic.InsuranceCompany
20. List the Employees who are Nurses.
select FirstName + ' ' + MiddleInitial + ' ' + LastName AS Nurses
from Employees
where RankID between 1 and 12
21. Display the average hourly wage for all employees who are aides.
SELECT AVG(HourlyWage) AS 'Average Hourly Wage' FROM Employees
WHERE RankID IN (
SELECT RankID FROM EmployeeRanks
WHERE EmployeeTypeID IN (
SELECT EmployeeTypeID FROM EmployeeTypes
WHERE EmployeeType = 'Aide'
)
)
22. Display the average hourly
wage for all hourly employees broken out by level.
SELECT AVG(HourlyWage) AS 'Avg Hourly Wage', SkillLevelID FROM EmployeeRanks
INNER JOIN Employees ON Employees.RankID = EmployeeRanks.RankID
WHERE SkillLevelID IS NOT NULL
GROUP BY SkillLevelID
ORDER BY SkillLevelID
EISELECT
AVG(HourlyWage)
AS
‘Avg
Hourly
Wage’,
Skilllevell=
INNER
JOIN
Employees
ON
Employees
RankID
=
EmployeeRanks
~
WHERE
SkillLevelID
IS
NOT
NULL
GROUP
BY
SkillLevelI0|
100%
~
<
>
|
Results
[y
Messages
AvgHourly
Wage
SkillLevellD
1
[103
1
2
16.8571428571429
2
3
335
3
23. Display the total salary for all salaried employees.
select SUM(er.Salary)
from Employees e LEFT OUTER JOIN EmployeeRanks er ON e.RankID = er.RankID
where er.Salary IS NOT NULL
24. Display the number of employees assigned to each rank.
select Employees.RankID, EmployeeTypes.EmployeeType,
EmployeeSkillLevels.SkillLevel, EmployeeTitles.EmployeeTitle,
count(Employees.EmpID) as NumberOfEmployees
from EmployeeRanks
inner join Employees on Employees.RankID = EmployeeRanks.RankID
inner join EmployeeTypes on EmployeeTypes.EmployeeTypeID =
EmployeeRanks.EmpTypeID
inner join EmployeeSkillLevels on EmployeeSkillLevels.SkillLevelID =
EmployeeRanks.SkillLevelID
inner join EmployeeTitles on EmployeeTitles.EmployeeTitleID =
EmployeeRanks.TitleID
group by Employees.RankID, EmployeeTypes.EmployeeType,
EmployeeSkillLevels.SkillLevel, EmployeeTitles.EmployeeTitle
order by Employees.RankID, EmployeeTypes.EmployeeType,
EmployeeSkillLevels.SkillLevel, EmployeeTitles.EmployeeTitle asc
25. Display a list of Employees who are nurses and were available to work on Sunday evenings during the
week of 11/2/2014.
select Employees.LastName, Employees.FirstName
from Employees
inner join EmployeeRanks on EmployeeRanks.RankID = Employees.RankID
inner join EmployeeTypes on EmployeeTypes.EmployeeTypeID =
EmployeeRanks.EmpTypeID
inner join Availability on Availability.EmpID = Employees.EmpID
inner join DaysOfWeek on DaysOfWeek.DayOfWeekID = Availability.DayofWeekID
where EmployeeTypes.EmployeeType = 'Nurse' and Availability.ShiftID = 3 and
DaysOfWeek.DayOfWeek = 'Sunday' and Availability.WeekOf = '11/2/2014'
order by Employees.LastName, Employees.FirstName asc
26. Display a list of
Employees who were available to work during morning shifts during the week of 11/2/2014 and had a
skill level of level 3.
SELECT LastName, FirstName, EmployeeType, EmployeeTitle FROM Employees
INNER JOIN EmployeeRanks ON EmployeeRanks.RankID = Employees.RankID
INNER JOIN EmployeeTitles ON EmployeeTitles.TitleID = EmployeeRanks.TitleID
INNER JOIN EmployeeTypes ON EmployeeTypes.EmployeeTypeID =
EmployeeRanks.EmployeeTypeID
WHERE EmployeeID IN (
SELECT EmployeeID FROM Availability
WHERE WeekOf = '11/02/2014'
)
AND SkillLevelID = 3
ORDER BY EmployeeType, EmployeeTitle, LastName, FirstName
EISELECT
LastName,
FirstName,
EmployeeType,
EmployeeTitle
|
INNER
JOIN
EmployeeRanks
ON
EmployeeRanks.RankID
=
Emplc
~
INNER
JOIN
EmployeeTitles
ON
EmployeeTitles.TitleID
= En
INNER
JOIN
EmployeeTypes
ON
EmployeeTypes
.EmployeeTypeIL
WHERE
EmployeeID
IN
(
SELECT
EmployeeID
FROM
Availability
WHERE
WeekOf
'11/02/2014'
100
%
)
AND
SkillLevelID
=
3
ORDER
BY
EmployeeType,
EmployeeTitle,
LastName,
FirstNan
~<
|
Results
[3
Messages
LastName
FirstName
EmployeeTy.
Lee
Jeffrey
Nurse
Wilson
Jerry
Nurse
‘Smith
Rendy
Nurse
White
Laura
Nurse
Alexander
Jimmy
Nurse
Evans
Keith
Nurse
Lopez
Carlos
Nurse
Thompson
Wilma Nurse
Cox
Georgina
Nurse
Kelly
Richard
Nurse
Jackson
Tammy
Nurse
Rogers
Maria
Nurse
Johnson
Michelle
Nurse
Long
Marie
Nurse
Baker
Louis
Nurse
Clark
Ruby
Nurse
Henderson
Aaron
Nurse.
Jena
Jena
Nurse
Morgan
Janice.
=—‘Nurse
‘Swain
Denise
Nurse
Campbell
Craig
Nurse
Mitchell
Stephanie
Nurse
Ortiz
Lillian
Nurse
EmployeeTi
LPN-3
LPN-3
LPN-4
LPN-4
LPN-S
LPN-S
RN-1
RN-1
RN-2
RN-2
RN-3
RN-3
RN-4
RN-4
RN-S
RN-S
RN-S
RN-S
RN-S
RN-S
RN-6
RN-6
RN-6
27. Display the total quantity of catheters added to inventory during 2013.
select SUM(si.Quantity)
from SupplyInventory si LEFT OUTER JOIN Supplies s ON si.SupplyID = s.SupplyID
where s.SupplyDescription = 'catheters' AND YEAR(si.DateReceived) = '2013'
28. Display the total cost of “sterile gloves – small” provided by Poole’s Medical supplies during 2013.
select SUM(si.UnitCost * si.Quantity) as 'Total Cost'
from SupplyInventory si inner join Supplies s on si.SupplyID = s.SupplyID
inner join [Medical Suppliers] ms on si.SupplierID = ms.SupplierID
where ms.SupplierID = 3 and YEAR(si.dateReceived) = 2013 and s.SupplyID = 12
29. Display
the average cost of supplies for each supply item broken out by supplier.
SELECT SupplierName, SupplyDescription, AVG(UnitCost) AS 'Purchase Cost' FROM
Supplies
INNER JOIN SupplyInventory ON SupplyInventory.SupplyID = Supplies.SupplyID
INNER JOIN MedicalSuppliers ON MedicalSuppliers.SupplierID =
SupplyInventory.SupplierID
GROUP BY SupplierName, SupplyDescription
ORDER BY SupplyDescription, SupplierName
30. Display the total cost of all items
purchased from suppliers broken out by supplier.
SELECT SupplierName, SUM(UnitCost * Quantity) AS 'Total for Supplier' FROM
SupplyInventory
INNER JOIN MedicalSuppliers ON MedicalSuppliers.SupplierID =
SupplyInventory.SupplierID
GROUP BY SupplierName
ORDER BY SupplierName
EISELECT
SupplierName,
SUM(UnitCost
*
Quantity)
AS
‘Total
for Supplier’
FROM
SupplyInventory
INNER
JOIN
MedicalSuppliers
ON MedicalSuppliers.SupplierID
=
SupplyInventory.SupplierID
GROUP
BY
Supplie-Name
ORDER
BY
Supplie-Name
100%
~
<
|
Results
[y
Messages
‘SupplierName
Total
for
Supp.
1
[Lynchburg
Medical
Supplies
11961
2
Pooles
Medical
Supplies.
15712
Virginia
Medical
Supplies
11506
>
31. Display a list of all the visits that occurred from March 20, 2014 to March 25, 2014 (including March
20 and March 25).
select v.DateRendered, p.LastName AS 'Patient Name', e.LastName AS 'Employee Name', v.StartTime,
v.EndTime
from Visits v LEFT OUTER JOIN Patients p ON v.PatientID = p.PatientID
LEFT OUTER JOIN Employees e ON v.EmployeeID = e.EmployeeID
where v.DateRendered >= '3-20-2014' AND v.DateRendered <= '3-25-2014'
order by v.DateRendered, p.LastName, e.LastName, v.StartTime
32. List the total charges for the visit that occurred on 2/12/2014 for Helen Ramirez that was provided by
Laura White.
select SUM(Charge)
From ((VisitDetails left join Visits on VisitDetails.VisitID = Visits.VisitID)
left join Patients on Patients.ID = Visits.PatientID)
left join Employees on Employees.EmpID = Visits.EmpID
where Patients.[First Name] = 'Helen' and Patients.[Last Name] = 'Ramirez'
and Employees.FirstName = 'Laura' and Employees.LastName = 'White'
and DateRendered = '2/12/2014'
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)
SELECT COUNT(DISTINCT PatientID) AS 'Number of Patients' FROM Visits
WHERE VisitID IN (
SELECT VisitID FROM VisitDetails
WHERE ServiceID IN (
SELECT ServiceID FROM Services
WHERE ServiceName = 'Insulin Injections'
) AND (DateRendered >= '1/1/2014' AND DateRendered <= '12/31/2014')
)
34. List the total number of 4” self-adhesive
bandages that were used in 2014.
SELECT SUM(SupplyQuantity) AS 'Total Bandages' FROM VisitDetails
WHERE SupplyID IN (
SELECT SupplyID FROM Supplies
WHERE SupplyDescription = '4" self-adhesive bandages'
)
AND VisitID IN (
SELECT VisitID FROM Visits
WHERE DateRendered >= '1/1/2014' AND DateRendered <= '12/31/2014'
)
[SELECT
SUM(SupplyQuantity)
AS
‘Total
Bandages’
FROM
VisitDetails
WHERE SupplyID
11
(
SELECT
SupplyID
FROM
Supplies
WHERE
SupplyDescription
"4"
self-adhesive
bandages’)
AND
VisitID
IN
(
SCLECT
VisitID
FROM
Visits
WHERE
DateRendered
>=
'1/1/2014'
AND
DateRendered
<=
'12/31/2014")]
35. List the average charge per visit per month in 2013 broken out by months.
select MONTH(v.DateRendered) AS 'Month', FORMAT(AVG(vd.Charge), 'C') AS 'Average Cost per Visit'
from VisitDetails vd LEFT OUTER JOIN Visits v ON vd.VisitID = v.VisitID
where YEAR(v.DateRendered) = '2014'
group by MONTH(v.DateRendered)
order by MONTH(v.DateRendered)
36. Provide a unique list of patients who received visits for feeding from November 1, 2014 until the
current date.
SELECT DISTINCT LastName, FirstName FROM Patients
WHERE PatientID IN (
SELECT PatientID FROM VisitDetails
INNER JOIN Visits ON Visits.VisitID = VisitDetails.VisitID
WHERE ServiceID IN (
SELECT ServiceID FROM Services
WHERE ServiceName = 'Feeding'
) AND DateRendered > '11/1/2014'
) ORDER BY LastName, FirstName
Tables:
1. PhysicianSpecialties
select
*
from
PhysicianSpecialties
0%
Otology
-
Neurotology
Physical
Medicine
&
Rehabiltation
10%
-
(El
Rests
Ey
Messages
ZipCode
_
oy,
‘State
1
[Bz
3920"]
ina
VA
2
23669-7533
Toponanaulka
VA
3
(24149-2028
Gainsborough
VA
4
Firksown
VA
5
(24501
‘Lynchburg
VA
6
(24502
‘Lynchburg
VA
7
(24503
‘Lynchburg
VA
8 24550
edo
«VA
9
2551 Fest vA
10
28561
Roarcke
VA
128862
Blacksburg—VA
1228701
Beeld
WV
13530454945
Mintones
Wl
1670223
Foes vA
2. ZipCodes
i
select
*
from
Dhaiclorbractied
+
100%
esuts
|)
Messages
FracticelD
_PracticeName
‘Address
_Line?
‘Address_Line2
ZpCode
Phone
Fax
WebsteURL
-
14
|
Amhurst
Medicine
2973
Lincoln
Street
NULL
24550
(G40)
273-3484
(G40)
167-9622
NULL.
2 2
Bedford
Associates
2074
Oral
Lake
Road
NULL
24551
(434)
496-2525
(434)
2825288
NULL
303
Blue
Ridge
intemal
Medicine
976
Stroop
Hil
Read
NULL
24501
(434)
557-6615
(434)
316-4524
NULL
44
Blue
Ridge
Osteopathic
Medicine
1957
Crowfield
Road
NULL
24502
(434)
622-1294
(434)
181-4878
NULL
5 5
Centra
Family
Practice
2689
Woodridge
Lane
NULL
24503
(434)
388-9541
(434)
4282867
NULL
6 6
Centra
Othopedies
377
Midway
Road
NULL
24550
(G40)
246-9526
(40)617-2195
NULL
77
Central
VA
Physicians
4383
Walt
Nuzum
Farm
Road
NULL
24551
(434)
578-3906
(434)995-6483
NULL
8 8
Donna
Lacroix,
MD
282Gerald
L
Bates
Dive
NULL
24501
(434)
494-7335
(434)
9643421
NULL
83
Dr.
Ray
A.
defies
2144 Todds Lane
NULL
24502
(434)
522.2892
(434)
768-758
NULL
1
10
Dr.
Water
Jess,
MD
249
Aaron
Smith Drive
NULL
24503
(434)
283-1843
(434)
7443927
NULL
nou
Forest
£870
Bird
Spring
Lane
NULL
24550
(G40)
127-2881
(640)
9834382
NULL
2 2
Irtemal
Medicine
Associates
1557
Faland
Avenue
NULL
24551
(434)598-3278
(434)
9585954
NULL
3013
John
Rosenbery,
DO
4057
Fannie
Street
NULL
24501
(434)
242.3157
(434)
5485478
NULL
“4
Light
Medical
4682
Riter
Avenue
NULL
24502
(434)
247-6254
(434)
494.6672
NULL
1% 15
Lynchburg
Associates
3888
Cooks
Mine
Road
NULL
24503
(434)
863-1443
(434)
623-1769
NULL
6
16
Lynchburg
Regional
Associates
3764
Platinum
Dive
NULL
24550
(G40)
876-4449
NULL
vow
Martin
Ramsey,
MD
161
Snowbird
Lane
NULL
24551
(434)
899-7333
(434)
2125742
NULL
3
18
Medical
Associates
of
Lchburg
875
Tall
Ends
Road
NULL
24501
(434)
418-8859
(434)958-8485
NULL v
3. PhysicianPractices
T
select
*
from
ere
|
100%
El
Resuts
|[y
Messages
PhysicianID
FirstName
LastName
—PracticelD
SpeciattyID
Email
a
1@
|
Betty
Pletke
15
3
BPletke@gmaicom
2 2
Jim
Richards
14
3 3
Blake
Collins
4 9
BColiins@gmail.
com
4 4
David
Andrews
5
55
Bonnie
Jones
«18
19
6 6
Ea
MeDonald 24 6
EMcDonald@gmailcom
77
dames
Kenan
12
31
Kenan
@gmaiicom
8 8
ifton
Jameson 7 8
9 9
Gregory
Johnson
4
19
10 10
Ray
Jeffries,
go
W
Rueffries@gmail
com
non
Jonas
Alen 19
4
‘JAlen@gmaicom
2 2
Pal
Boman
2
8
BoB
Kate
Cantell
11
2
KCarirel@amaiicom
414
John
Roarke
1
24
JRoarke@gmail
com
1515
Gary
‘Smith
16
GSmith@gmail.
com
1% 16
Henry
Gray,
ry
15
717
Linda
Gupta
2B 20
1
18
Poril
Hall
2
1
¥
4. Physicians
T
select
*
from
Patients
a
PetientID.FisiName
—Middeintil
LastName
Address_Lne1
‘Address
Line?
ZipCode
Phone
Home
Phone
Atemate
Email
-
14
|
Simon
NULL
Peter
2873
Lincoln
Steet
Ao.
154
24501
(434)
246-6143
22
James
NULL
Grester_—_2074
Qwen
Lake
Road
NULL
24562
(434)
243-1357
NULL NULL
303
John
NULL
Nathan
976
Stoop
Hill
Road
NULL
24561
G40)
161-3517
NULL
John
a4
Andew NULL
Pop
1557
Crowfeld
Road
NULL
24562
(434)
161-6787
NULL NULL
55
Pio
NULL
Beth
2689
Woodridge
Lane
NULL
24508434)
3624963
(494)
627-4514
NULL
6 6
Thomas
NULL
Didymus
377
Midway
Road
Sute
14324502
(434)
3595268
(434)
584-9251
NULL
7 7
Bartholomew
NULL
Nathaniel
4383
Welt
Nuzum
Farm
Road
NULL
24501
(434)737-5689
(434)
896-4489
28
Matthew
NULL
Lew
282Gerald
L
Bates
Dive
NULL
24562
(434)
188-6821
(434)
4635965
99
James
NULL
Alphaeus
2144 Todds Lane
NULL
24551
(434)
861-4668
(434)
2865715
JamesHHAbhaeus@telewomus
10 10
‘Simon
NULL
Zealot
249
Aaron
Smth
Drive
NULL
24550
WULL
NULL
nou
Thaddaeus
NULL
Lebbaeus
870
Bird
Spring
Lane
NULL
24508
434)
2174494
(494)
099-7922
NULL
Rw wR
Judas
NULL
lscafot
1557
Fatand
Avenue
NULL
79229
(434)331-2973
NULL
JudasMiscaot
@nyta.com
BB
aon
NULL
Lofer
4057
Fannie
Steet
NULL
24561
G40)737-2434
G40)
559.6584
AaronMLofer@dayrep
com
“ou
Moses
NULL
Ramser 4682
Ritter
Avenue
NULL
24550
(640)992-5999
(40)
178-4559
‘com
1%
1
Abednego
NULL
Nebo
3588
Cooks
Mine
Road
NULL
24502
(434)
222-6428
(434)
1592495
6
16
Jacob
NULL
Smals 253
Oak
Lane
NULL
24562 (434)7134375
(434)
648-7495
NULL
vow
Jean
NULL
Eicssson
241
Sycamore
Grove
NULL
24551
(434)
392.7961
(494)
395-6914
NULL
wo
Gerald
NULL
Madison
4455 Grove
Mill
Ra
NULL
24550
(G40)
983-5342
NULL
Gerald
Madison
@gmai.com
a
5. Patients
T
select
*
from
Referrals
a
ReferallD
StartDate
EndDate
PatientID
PhysicianID
8
g
8
8
8
8
8
8
8
8
BeSeees
geeeee
BeeaR2
sssess
sssess
SS8s8ses
BeSeees
sssess
sssess
SS8s8ses
18
2013-01-11
00:00:00
201303-1200:00:00
18 18
6. Referrals
7. Services
select
from
Erequencies|
17
1X
Daly
2 2
Daly
203
3x
Daly
8. Frequencies
select
*
from
Referralservices|
Messages:
ReferallD
ServicelD
FrequencylD
iG
[
10
2
6
4
8
"
7
10
1
10
5
9
3
4
6
1
10
9. ReferralServices
select
*
from
PaymentTy
100%
[Bl
Resuts
[23
Messages
PaymentTypelD
_
PaymentType
14
Medi
23
"Medicaid
3a
_|s
Irauranes
44
Private
Pay
10. PaymentTypes
select
*
from
InsuranceCompanies|
10%
-
Cl
Rests
Ely
Messages
InsurancelD
InsuranceCompany
Address_Line1
Address_Line2
ZipCode
Phone
Fax
Email
1
ff
Allowance
8299
Emerald
Moor
NULL
24149-2028
G40)
962-1929
NULL NULL
2 2
”
Best
insurance:
4559
Silver
Horse
Avenue
‘NULL
(22233-2424
(757)
748-8459
NULL NULL
3 3
Friendly
Insurance
3602
Fallen
Canyon
NULL
53845-4946
(262)
1464158
NULL NULL
a4
Insurance
One
_1509Galden
Lake
Subdivision
NUL
243609809
276)
3104644
NULL NULL
5 5
‘Safety
Insurance
1881
Buming
Gate
Dale
‘NULL
22669-7533
(434)
252-5171
NULL NULL
11. InsuranceCompanies
T
select
*
from
Contracts
=
10%
-
(El
Rests
Ey
Messages
ContractID
ReferallD
StartDate_
‘EndDate
PaymentTypelD
—InsurancelD
—_NegotiatedRate
a
1 1
12
2013-01-01
00:00:00
2013-03-02
00:00:00
2
‘NULL ‘NULL
23
1
201301401
000000
2013020200000
2
NUL
NULL
203
3
2013012000000 2013020300000
2
NUL
NULL
a4
4
2013010300000 2013020400000
2
NUL
NULL
5 5 7
2013010300000 2013020400000
1
NUL
NULL
6 6 8
2013010300000 2013020400000
1
NUL
NULL
77
5
2013010300000 2013020400000
1
NUL
NULL
28
6
2013010300000 2013020400000
3
1
54
9 3 9
201301-4000000
2013025000000
2
NUL
NULL
0
10 10
2013014000000 2013025000000
3 4 "7
non
12
—_20130105000000
20130206
000000
2
NUL
NULL
RR
1
20130105
000000
20130206
000000
2
NUL
NULL
Bon
12
—_201301-08000000
2013020900000
3 3
2
“ou
18
2013010800000
20130209000000
3 3
6
B15
14
2013010800000 2013020900000
3 3
a
616
16
201301-10000000
20130311
000000
3
1
"7
v4
17
201301-10000000
20130211
000000
1
NUL
NULL
18
18
—201301-11
000000
2013022000000
1
NUL
NULL .
12. Contracts
select
*
from
EmployeeTys
13. EmployeeTypes
select
*
from
6
14. EmployeeTitles
select
*
from
EmployeeskillLevels|
15. EmployeeSkillLevels
select
*
from
Billi
100%
=
Sl
Rents
enases
EnployeeTypelD
SkilLevellD
BlingRate
:
1
160
207
3
16
3
2
1
135
42
g
16. BillingRates
Salary
NULL
NULL
NULL
NULL
ILL
NULL
NULL
ILL
NULL
NULL
ILL
NULL
NULL
ILL
NULL
NULL
ULL
NULL
20
205
2B
26
a
285
20
3B
5
65
9
2
a5
10
105
1"
1
2
‘
i
Bana
arma
mam
ree
.
3
Bl
_|esleeloe
io ol
mlelolelele/e|eleleles|e
i
i
7
|i
i
beer
own
ooeteeezesee
17. EmployeeRanks
i
select
*
from
toes
a
100%
esuts
|)
Messages
Employee!
FirstName
Middlential
LastName
Phone
Call
Email
RankiDHouryWage
Salary
“
14
7
Usa
K
‘Adams
(434)
212-8982
(434)
818-7158
Lisa
3
85
NULL.
2 2
simmy
Alexander
(434)
482-2643
(434)
242.5755
Jimmy
5
a
NULL
303
Shawn
F
‘Alen
(434)
989-2467
(434)
7996168
Sharon
21
NULL
20000
44
Shei
X
Baez
(434)
3715121
(434)
684-1263
Shen
Baez@mhccon
25
NULL
19000
5 5
Athor
Bailey
(434)
935-4647
(434)
9698971
AthurBaiey@mhecom
16
11
NULL
6 6
Lois
NULL
Boker
(434)
135.3682
Louis
Beker@mhccom
10,385
NULL
77
rie
S
Bames
(434)
1146918
1
2»
NULL
88
Chey
E
Bentz
(640)
953-4197
Chen.
Bertz@mhccom
22,
NULL
15000
83
Cag G
Campbell
(640)
125.2665
CraigCampbel@mhccom
1139,
NULL
0
10
Virginia
=U
Chen
(434)
567-6277
Vigiria
Chen
@mhe.com
NULL
25000
nou
iby
F
Clark (434)
985-7786
Ruby
Clark
@mhc.com
1
(385
NULL
2 2
Samuel
Coleman
(434)
927.9839
19
125
NULL
3013
Georgina
A
Cox
(434)
847-6964
Georgina
7 30
NULL
“4
lee
=
Dennison
(434)
2313177
Leola
20
NULL
50000
1% 15
Gay
ou
Diaz
(434)
139-2764
Gary
Diaz@mhe-com
2
205 NULL
6
16
an)
Dougherty
(434)
922-3142
Anta
24
NULL
18000
vow
Babare
ON
Echals
(434)
6165588
(434)
1638416
2
205 NULL
3
18
Amy
L
Edwards
(434)
968-7418
(434)
1673967
Amy
Edwards@mhccom
15105
NULL v
18. Employees
T
select
*
from
Shifts
a
100%
~
Sl
Resuts
ly
Messoves
Shi
ShitNeme
Satine
Endive
1
[777]
oming
—08:00-00.0000000
1200:00.0000000
22
wemoon
—1200-00.0000000 16:00-00.0000000
203
Evening
16:00:00.0000000
_20:00:00.0000000
19. Shifts
20. DaysOfWeek
2014-11-02
00:00:00
DayOMWeekID
ShitID
2 2
4
1
5 2
6 2
1
3
2 3
4
1
5
1
6 3
1
3
2 2
4 3
5 3
6 2
7 2
1
3
2
1
3 3
21. Availability
SupplierID
SupplierName
Address_Line1
Address_line2
ZipCode
Phone
Fax
Email
17
Virginia
Medical
Suppies
2198
Old
Jetty
NULL
24502
NULL NULL NULL
2
2
"
Lynchburg
Medical
Supplies
999
Dusty
Promenade
NULL
24503
NULL NULL NULL
3 3
Pooles
Medical
Supplies
(5884
Heather
Run
NULL
24701 NULL)
NULL NULL
22. MedicalSuppliers
23. Supplies
T
select
*
from
ee
sawentonh
|
10%
-
(El
Rests
Ey
Messages
SupplyID
SupplierID
Dat
‘UnitCost
Quantity
a
1 1
12
2013-01-10
00:00:00
3 6
24
2
2013-02-05
00:00:00
2
12
304
2
zor30223000000
4
19
44
2
2013031600000
§
15
54
2
2013042400000
7 3
64
2
2012052400000
10 16
74
2
2013061900000
1
4
84
2
zor307-13000000
8
14
94
2
zor208-18000000
4
19
wt
2
2012091900000
2 20
nod
2
20r310-18000000
10
8
nt
2
porair-s4000000
12
Bod
2
zor31203000000
8
16
wd
2
zorzi221000000
8 3
64
2
zovsor-6000000
1
20
wt
2
powor200000
6
3
v4
2
zov4030600000
5
3
wt
2
2014040600000
6
14
.
24. SupplyInventory
T
select
*
from
Visits
|
10%
-
(El
Rests
Ey
Messages
VetiD
Dat
Tine
i
EnployelD
PaetID
-
1
[777]
2014004000000
1300000000000
13:3000.0000000
33
401
2"
aove0t05
00.0000
1300:00.0000000
1330000000000
7
401
33
2a¥#0+-9600.00.00
1300.00.000000
1330000000000
23
401
4 4
2014010700000
1300.00.0000000
133000.0000000
64
401
5 5
—20¥40+-080000.00
1300.00.0000000
133000.0000000
18,
401
& 6
20¥407-090000.00
1400.00.0000000
1430000000000
52
401
77
2a¥#0-1000.00.00
1400°00.0000000
143000000000
22
401
8 8
20¥40'-11000000
1600:00.0000000
1630000000000
2
401
9 8
20¥401-120000.00
16.00.00.0000000
16:3000.0000000
25
401
to 10
201401-13000000
1:0000.0000000
15:3000.0000000
44
401
11 11
201401-14000000
16.0000.0000000
1:3000.0000000
12
401
12 12
201401-15000000
1:0000.0000000
15:3000.0000000
50
401
12 13
201401-416000000
13:0000.0000000
1330000000000
23
401
4
14
2014-01-17
00:00:00
—13:00:00.0000000
13:30:00.0000000
54
41
18 15
201401-18000000
1300000000000
1330.00 6
41
16
1
201401-19000000
1300000000000
1330000000000
27
401
17 17
2014020000000
400000000000
1430000000000
60
401
18 18
20140121
000000
160000.0000000
16:3000.0000000
38
401
.
25. Visits
Ww
NULL
NULL
Ww
NULL
NULL
Ww
NULL
NULL
Ww
NULL
NULL
Ww
NULL
NULL
Ww
NULL
NULL
VistiD
VisitDetallD
SupplyiD
SupphyQuantty
ServicelD
Charge
ULL
NULL
NULL
ULL
NULL
NULL
ULL
NULL
NULL
ULL
NULL
NULL
ULL
NULL
NULL
ULL
NULL
NULL
26. VisitDetails
