SQL Exercise 1
1
create table EmployeeName(
EmployeeID int identity(1,1) Primary key not null,
FirstName varchar(100) not null,
MiddleName varchar(100),
LastName varchar(100) not null,
CallOutName varchar(100),
LastUpdatedDate datetime default GetDATE()
)
create table EmployeeOfficialDetails(
OfficialID int identity(1,1) Primary key not null,
EmployeeID int null,
Designation varchar(100) null,
JoinedYear int not null,
ResignedYear int null,
LastUpdatedDate datetime default GetDATE()
)
alter table EmployeeOfficialDetails
add constraint Fk_EmployeeOfficialDetails_EmployeeName
foreign key(EmployeeID)
references EmployeeName(EmployeeID)
create table EmployeeExperienceDetails(
ExperienceID int identity(1,1) Primary key not null,
OfficialID int null,
ReportTo varchar(100) null,
Location varchar(100) null,
LastUpdatedDate datetime default GetDATE(),
Constraint fk_EmployeeExperienceDetails_EmployeeOfficialDetails
foreign key(OfficialID) references EmployeeOfficialDetails(OfficialID)
)
2 select * from Nurse where Registered = 0
3
select [Name], position
from Nurse
where Position = 'Head Nurse'
4
select d.Name as Department, p.Name as Physician
from Department d
join Physician p
on d.Head = p.EmployeeID
5:
select count(distinct(Patient)) as 'Number of Patients taken at least one appointment'
from Appointment
Another way for more accurate
select count(distinct(ap.Patient)) as 'Number of Patients taken at least one appointment'
from Appointment ap
join Physician p
on ap.Physician = p.EmployeeID
6
select BlockFloor as [Floor], BlockCode as Code from Room
where Number = 212
7
select count(Number) as 'Number of available rooms' from Room
where Unavailable = 0
8
select count(Number) as 'Number of unavailable rooms' from Room
where Unavailable = 1
9
select p.Name as Physician, d.Name as Department
from Physician p
join Affiliated_With a
on p.EmployeeID = a.Physician
join Department d
on d.DepartmentID = a.Department
where a.PrimaryAffiliation = 1
10
select p.Name as Physician, pr.Name as Treatment
from Physician p
join Trained_In t on p.EmployeeID = t.Physician
join ProcedureTbl pr on pr.Code = t.Treatment
11
select p.Name as Physician, p.Position, d.Name as Department
from Physician p
join Affiliated_With a
on p.EmployeeID = a.Physician
join Department d
on d.DepartmentID = a.Department
where a.PrimaryAffiliation = 0
12
select p.Name as Physician, p.Position
from Physician p
left join Trained_In t on p.EmployeeID = t.Physician where t.Treatment is null
13
select pt.Name as Patient, pt.Address, ph.Name as Physician
from Patient pt
join Physician ph on pt.PCP = ph.EmployeeID
14
select p.Name as PatientName, COUNT(a.Physician) as 'Appointment for No. of Physicians'
from Appointment a
left join Patient p
on a.Patient = p.SSN
group by p.Name
15
select count(distinct(Patient)) as 'No. of patient got appointment for room C'
from Appointment
where ExaminationRoom = 'C'
16
select p.Name as Patient, a.ExaminationRoom as 'Room No',
a.Start as 'Date and Time of appointment'
from Patient p
join Appointment a
on p.SSN = a.Patient
17
select n.Name as Nurse, a.ExaminationRoom as 'Room No.' from Nurse n
join Appointment a
on n.EmployeeID = a.PrepNurse
18
select p.Name as Patient,n.Name as 'Name of Nurse assisting the Physician', ph.Name as Physician, a.ExaminationRoom as 'Room No.', a.Start as 'Start_dt_time'
from Appointment a
join Patient p on a.Patient = p.SSN
join Physician ph on a.Physician = ph.EmployeeID
join Nurse n on a.PrepNurse = n.EmployeeID
where a.Start = '2008-04-25 10:00:00'
19
select p.Name as 'Name of Patient', ph.Name as 'Name of Physician', a.ExaminationRoom as 'Room No.'
from Appointment a
join Patient p on a.Patient = p.SSN
join Physician ph on a.Physician = ph.EmployeeID
where a.PrepNurse is null
20
select p.Name as 'Patient', ph.Name as 'Physician', m.Name as 'Medication'
from Patient p
join Physician ph on p.PCP = ph.EmployeeID
join Prescribes prb on prb.Patient = p.SSN
join Medication m on m.Code = prb.Medication
21
select p.Name as 'Patient', ph.Name as 'Physician', m.Name as 'Medication'
from Patient p
join Physician ph on p.PCP = ph.EmployeeID
join Prescribes prb on prb.Patient = p.SSN
join Medication m on m.Code = prb.Medication
where prb.Appointment is not null
22
select p.Name as 'Patient', ph.Name as 'Physician', m.Name as 'Medication'
from Patient p
join Physician ph on p.PCP = ph.EmployeeID
join Prescribes prb on prb.Patient = p.SSN
join Medication m on m.Code = prb.Medication
where prb.Appointment is null
23
select BlockCode as Block, count(*) as 'Number of available rooms'
from Room
where Unavailable = 0
group by BlockCode
order by BlockCode
24
select BlockFloor as Block, count(*) as 'Number of available rooms'
from Room
where Unavailable = 0
group by BlockFloor
order by BlockFloor
25
select BlockFloor as Floor, BlockCode as Block, count(*) as 'Number of available rooms'
from Room
where Unavailable = 0
group by BlockFloor, BlockCode
order by BlockFloor
26
select BlockFloor as Floor, BlockCode as Block, count(*) as 'Number of unavailable rooms'
from Room
where Unavailable = 1
group by BlockFloor, BlockCode
order by BlockFloor
27
select top 1 BlockFloor as Floor, count(*) as 'Number of available rooms'
from Room
where Unavailable = 0
group by BlockFloor
order by count(*) desc
28
select top 1 with ties BlockFloor as Floor, count(*) as 'Number of available rooms'
from Room
where Unavailable = 0
group by BlockFloor
order by count(*) asc
29
select p.Name as Patient, s.Room, r.BlockFloor as [Floor], r.BlockCode as Block
from Patient p
join Stay s on s.Patient = p.SSN
join Room r on r.Number = s.Room
30
select n.Name as Nurse, o.BlockCode as [Block]
from Nurse n
join On_Call o on o.Nurse = n.EmployeeID
