sql 1

 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