Question

Database

For this lab you will be using SQL SELECT statements to query your database tables. You will be turning in the results of the following queries:

1. List all Patients and what Bed they are assigned to

2. List all patients who had Treatments and what Treatment they received

3. List all patients who had tests and what Test they had

4. List the employees (doctors, nurses, etc.) who assisted each patient.

5. List all patients in alphabetical order

6. List all patients who live in Atlanta and had a test completed

7. List all patients who live in either Woodstock or Roswell who had a treatment completed.

If you don't have data in your created tables that will allow you to create these queries - add appropriate data to your tables before doing the queries.

Capture your results from each SQL query (SELECT statement) - that also shows the actual written query - and turn them in.

Answer 1

I have created Patients,Employees,Services,Treatments,Tests,

PatientCareTakers,PatientTreatments,PatientServices,PatientTests

tables to hold data. Find creations scripts below.

I have added example records in All tables.

I have used foreign key relationships wherever needed and Inner Joins to fetch data

from tables to get intercepted records.

In the last there are 7 Select queries to get results as per the question.

You have add example records in tables like Patients,Employee,atientCareTakers,PatientTreatments,PatientServices,PatientTests

to get data in result queries otherwise it will give blank result.

Table Creation Scripts :

Create Schema ECH

GO

Create Table [ECH].[Services](

[ServiceId] int Identity(1,1) Primary Key,

[Title] varchar(64) NOT NULL

)

INSERT INTO [ECH].[Services] Values ('Laboratory tests')

INSERT INTO [ECH].[Services] Values ('Radiology procedures')

INSERT INTO [ECH].[Services] Values ('Admissions')

INSERT INTO [ECH].[Services] Values ('In-patient and out-patient surgeries')

INSERT INTO [ECH].[Services] Values ('Labor and delivery services')

INSERT INTO [ECH].[Services] Values ('Intensive care')

INSERT INTO [ECH].[Services] Values ('Cardiology')

INSERT INTO [ECH].[Services] Values ('Open-heart surgery')

INSERT INTO [ECH].[Services] Values ('Neurology department')

INSERT INTO [ECH].[Services] Values ('Pediatric medical and surgical care')

INSERT INTO [ECH].[Services] Values ('Orthopedics')

INSERT INTO [ECH].[Services] Values ('Oncology')

INSERT INTO [ECH].[Services] Values ('Emergency')

Create Table [ECH].[Treatments](

[TreatmentId] int Identity(1,1) Primary Key,

[Title] varchar(64) NOT NULL

)

INSERT INTO [ECH].[Treatments] Values ('Treatment 1')

INSERT INTO [ECH].[Treatments] Values ('Treatment 2')

INSERT INTO [ECH].[Treatments] Values ('Treatment 3')

INSERT INTO [ECH].[Treatments] Values ('Treatment 4')

INSERT INTO [ECH].[Treatments] Values ('Treatment 5')

INSERT INTO [ECH].[Treatments] Values ('Treatment 6')

INSERT INTO [ECH].[Treatments] Values ('Treatment 7')

INSERT INTO [ECH].[Treatments] Values ('Treatment 8')

INSERT INTO [ECH].[Treatments] Values ('Treatment 9')

Create Table [ECH].[Tests] (

[TestId] int Identity(1,1) Primary Key,

[Title] varchar(64) NOT NULL

)

INSERT INTO [ECH].[Tests] Values ('Test 1')

INSERT INTO [ECH].[Tests] Values ('Test 2')

INSERT INTO [ECH].[Tests] Values ('Test 3')

INSERT INTO [ECH].[Tests] Values ('Test 4')

INSERT INTO [ECH].[Tests] Values ('Test 5')

INSERT INTO [ECH].[Tests] Values ('Test 6')

INSERT INTO [ECH].[Tests] Values ('Test 7')

INSERT INTO [ECH].[Tests] Values ('Test 8')

INSERT INTO [ECH].[Tests] Values ('Test 9')

Create Table [ECH].[EmployeeType](

[TypeId] int Identity(1,1) Primary Key,

[Title] varchar(64) NOT NULL

)

INSERT INTO [ECH].[EmployeeType] Values ('Doctor')

INSERT INTO [ECH].[EmployeeType] Values ('Registered nurse')

INSERT INTO [ECH].[EmployeeType] Values ('Laboratory technician')

INSERT INTO [ECH].[EmployeeType] Values ('Counselor')

INSERT INTO [ECH].[EmployeeType] Values ('Rehabilitation therapist')

INSERT INTO [ECH].[EmployeeType] Values ('Social services staff')

INSERT INTO [ECH].[EmployeeType] Values ('Admittance/discharge staff')

INSERT INTO [ECH].[EmployeeType] Values ('Insurance staff')

INSERT INTO [ECH].[EmployeeType] Values ('Janitorial staff,')

INSERT INTO [ECH].[EmployeeType] Values ('Security staff')

Create Table [ECH].[Employees] (

[EmpId] int Identity(1,1) Primary Key,

[EmpType] int NOT NULL,

[Name] varchar(64) NOT NULL

)

-- FOREIGN KEY reference to [TypeId] in [EmployeeType] table

ALTER TABLE [ECH].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_EmployeeType] FOREIGN KEY([EmpType])

REFERENCES [ECH].[EmployeeType] ([TypeId])

ALTER TABLE [ECH].[Employees] CHECK CONSTRAINT [FK_Employees_EmployeeType]

Insert Into [ECH].[Employees] (EmpType, Name) Values (1,'Emp 11')

Insert Into [ECH].[Employees] (EmpType, Name) Values (2,'Emp 21')

Insert Into [ECH].[Employees] (EmpType, Name) Values (3,'Emp 31')

Insert Into [ECH].[Employees] (EmpType, Name) Values (4,'Emp 41')

Insert Into [ECH].[Employees] (EmpType, Name) Values (5,'Emp 51')

Insert Into [ECH].[Employees] (EmpType, Name) Values (6,'Emp 61')

Insert Into [ECH].[Employees] (EmpType, Name) Values (7,'Emp 71')

Insert Into [ECH].[Employees] (EmpType, Name) Values (8,'Emp 81')

Insert Into [ECH].[Employees] (EmpType, Name) Values (9,'Emp 91')

Insert Into [ECH].[Employees] (EmpType, Name) Values (1,'Emp 12')

Insert Into [ECH].[Employees] (EmpType, Name) Values (2,'Emp 22')

Insert Into [ECH].[Employees] (EmpType, Name) Values (3,'Emp 32')

Insert Into [ECH].[Employees] (EmpType, Name) Values (4,'Emp 42')

Insert Into [ECH].[Employees] (EmpType, Name) Values (5,'Emp 52')

Insert Into [ECH].[Employees] (EmpType, Name) Values (6,'Emp 62')

Insert Into [ECH].[Employees] (EmpType, Name) Values (7,'Emp 72')

Insert Into [ECH].[Employees] (EmpType, Name) Values (8,'Emp 82')

Insert Into [ECH].[Employees] (EmpType, Name) Values (9,'Emp 92')

Insert Into [ECH].[Employees] (EmpType, Name) Values (1,'Emp 13')

Insert Into [ECH].[Employees] (EmpType, Name) Values (2,'Emp 23')

Insert Into [ECH].[Employees] (EmpType, Name) Values (3,'Emp 33')

Insert Into [ECH].[Employees] (EmpType, Name) Values (4,'Emp 43')

Insert Into [ECH].[Employees] (EmpType, Name) Values (5,'Emp 53')

Insert Into [ECH].[Employees] (EmpType, Name) Values (6,'Emp 63')

Insert Into [ECH].[Employees] (EmpType, Name) Values (7,'Emp 73')

Insert Into [ECH].[Employees] (EmpType, Name) Values (8,'Emp 83')

Insert Into [ECH].[Employees] (EmpType, Name) Values (9,'Emp 93')

Create Table [ECH].[Patients](

[PatientId] int Identity(1,1) Primary Key,

[Name] varchar(64) NOT NULL,

[City] varchar(64) NOT NULL,

[BedNumber] int NULL

)

Insert Into [ECH].[Patients] (Name, City, BedNumber) Values ('Patient 1','Woodstock', 1)

Insert Into [ECH].[Patients] (Name, City, BedNumber) Values ('Patient 2','Roswell', 2)

Insert Into [ECH].[Patients] (Name, City, BedNumber) Values ('Patient 3','Woodstock', 3)

Insert Into [ECH].[Patients] (Name, City, BedNumber) Values ('Patient 4','Atlanta', 4)

Insert Into [ECH].[Patients] (Name, City, BedNumber) Values ('Patient 5','Woodstock', 5)

Insert Into [ECH].[Patients] (Name, City, BedNumber) Values ('Patient 6','Atlanta', 6)

Insert Into [ECH].[Patients] (Name, City, BedNumber) Values ('Patient 7','Woodstock', 7)

Insert Into [ECH].[Patients] (Name, City, BedNumber) Values ('Patient 8','Roswell', 8)

Insert Into [ECH].[Patients] (Name, City, BedNumber) Values ('Patient 9','Atlanta', 9)

Create Table [ECH].[PatientCareTakers]( [PatientId] int NOT NULL,

[EmpId] int NOT NULL,

[StartTime] datetime NOT NULL,

[EndTime] datetime NULL)

-- FOREIGN KEY reference to Patient Id in Patient table

ALTER TABLE [ECH].[PatientCareTakers] WITH CHECK ADD CONSTRAINT [FK_PatientCareTakers_Patients] FOREIGN KEY([PatientId])

REFERENCES [ECH].[Patients] ([PatientId])

ALTER TABLE [ECH].[PatientCareTakers] CHECK CONSTRAINT [FK_PatientCareTakers_Patients]

-- FOREIGN KEY reference to [EmpId] in [Employees] table

ALTER TABLE [ECH].[PatientCareTakers] WITH CHECK ADD CONSTRAINT [FK_PatientCareTakers_Employees] FOREIGN KEY([EmpId])

REFERENCES [ECH].[Employees] ([EmpId])

ALTER TABLE [ECH].[PatientCareTakers] CHECK CONSTRAINT [FK_PatientCareTakers_Employees]

Insert Into [ECH].[PatientCareTakers] (PatientId, EmpId, StartTime, EndTime)

Select p.PatientId, e.EmpId, GETDATE() , case WHEN p.PatientId % 2 = 0 Then GETDATE() else NULL end

From [ECH].[Patients] p INNER JOIN [ECH].[Employees] e

on p.PatientId = e.EmpType

Create Table [ECH].[PatientTreatments]( [PatientId] int NOT NULL,

[TreatmentId] int NOT NULL,

[StartTime] datetime NOT NULL,

[EndTime] datetime NULL)

-- FOREIGN KEY reference to Patient Id in Patient table

ALTER TABLE [ECH].[PatientTreatments] WITH CHECK ADD CONSTRAINT [FK_PatientTreatments_Patients] FOREIGN KEY([PatientId])

REFERENCES [ECH].[Patients] ([PatientId])

ALTER TABLE [ECH].[PatientTreatments] CHECK CONSTRAINT [FK_PatientTreatments_Patients]

-- FOREIGN KEY reference to [TreatmentId] in [PatientTreatments] table

ALTER TABLE [ECH].[PatientTreatments] WITH CHECK ADD CONSTRAINT [FK_PatientTreatments_Treatments] FOREIGN KEY([TreatmentId])

REFERENCES [ECH].[Treatments] ([TreatmentId])

ALTER TABLE [ECH].[PatientTreatments] CHECK CONSTRAINT [FK_PatientTreatments_Treatments]

Insert Into [ECH].[PatientTreatments] (PatientId,TreatmentId,StartTime)

Select p.PatientId, t.TreatmentId, GETDATE()

From [ECH].[Patients] p INNER JOIN [ECH].[Treatments] t

on p.PatientId = t.TreatmentId

Create Table [ECH].[PatientServices]( [PatientId] int NOT NULL,

[ServiceId] int NOT NULL,

[StartTime] datetime NOT NULL,

[EndTime] datetime NULL)

-- FOREIGN KEY reference to Patient Id in Patient table

ALTER TABLE [ECH].[PatientServices] WITH CHECK ADD CONSTRAINT [FK_PatientServices_Patients] FOREIGN KEY([PatientId])

REFERENCES [ECH].[Patients] ([PatientId])

ALTER TABLE [ECH].[PatientServices] CHECK CONSTRAINT [FK_PatientServices_Patients]

-- FOREIGN KEY reference to [ServiceId] in [PatientServices] table

ALTER TABLE [ECH].[PatientServices] WITH CHECK ADD CONSTRAINT [FK_PatientServices_Services] FOREIGN KEY([ServiceId])

REFERENCES [ECH].[Services] ([ServiceId])

ALTER TABLE [ECH].[PatientServices] CHECK CONSTRAINT [FK_PatientServices_Services]

Insert Into [ECH].[PatientServices] (PatientId,ServiceId,StartTime)

Select p.PatientId, s.ServiceId, GETDATE()

From [ECH].[Patients] p INNER JOIN [ECH].[Services] s

on p.PatientId = s.ServiceId

Create Table [ECH].[PatientTests]( [PatientId] int NOT NULL,

[TestId] int NOT NULL,

[StartTime] datetime NOT NULL,

[EndTime] datetime NULL)

-- FOREIGN KEY reference to Patient Id in Patient table

ALTER TABLE [ECH].[PatientTests] WITH CHECK ADD CONSTRAINT [FK_PatientTests_Patients] FOREIGN KEY([PatientId])

REFERENCES [ECH].[Patients] ([PatientId])

ALTER TABLE [ECH].[PatientTests] CHECK CONSTRAINT [FK_PatientTests_Patients]

-- FOREIGN KEY reference to [ServiceId] in [PatientServices] table

ALTER TABLE [ECH].[PatientTests] WITH CHECK ADD CONSTRAINT [FK_PatientTests_Tests] FOREIGN KEY([TestId])

REFERENCES [ECH].[Tests] ([TestId])

ALTER TABLE [ECH].[PatientTests] CHECK CONSTRAINT [FK_PatientTests_Tests]

Insert Into [ECH].[PatientTests] (PatientId,TestId,StartTime)

Select p.PatientId, t.TestId, GETDATE()

From [ECH].[Patients] p INNER JOIN [ECH].[Tests] t

on p.PatientId = t.TestId

Select Queries to get final results :

-- 1

Select Name, BedNumber From [ECH].Patients

-- 2

Select p.Name, t.Title 'Treatment Name', pt.StartTime, pt.EndTime

From [ECH].[Patients] p INNER JOIN [ECH].[PatientTreatments] pt

On p.PatientId = pt.PatientId

INNER JOIN [ECH].Treatments t

On pt.[TreatmentId] = t.TreatmentId

-- 3

Select p.Name, t.Title 'Test Name', pt.StartTime, pt.EndTime

From [ECH].[Patients] p INNER JOIN [ECH].[PatientTests] pt

On p.PatientId = pt.PatientId

INNER JOIN [ECH].Tests t

On pt.TestId = t.TestId

-- 4

Select p.Name 'Patient', e.Name 'Care Taker', et.Title 'Designation'

From [ECH].[Patients] p INNER JOIN [ECH].[PatientCareTakers] ct

On p.PatientId = ct.[PatientId]

INNER JOIN [ECH].Employees e

ON ct.EmpId = e.EmpId

INNER JOIN [ECH].EmployeeType et

On et.TypeId = ct.[EmpId]

-- 5

Select Name From [ECH].Patients Order By Name

-- 6

Select Name

From [ECH].Patients p INNER JOIN [ECH].[PatientTests] t

ON p.PatientId = t.PatientId

Where p.City = 'Atlanta'

AND t.EndTime IS NOT NULL

-- 7

Select Name

From [ECH].Patients p INNER JOIN [ECH].[PatientTreatments] t

ON p.PatientId = t.PatientId

Where (p.City = 'Woodstock' OR p.City = 'Roswell')

AND t.EndTime IS NOT NULL