MySQL Installer can install and manage multiple, separate MySQL server instances on the same host at the same time. For example, MySQL Installer can install, configure, and upgrade a separate instance of MySQL 5.6, MySQL 5.7, and MySQL 8.0 on the same host.

MySQL open source software is provided under the GPL License. ... MySQL Community Edition is a freely downloadable version of the world's most popular open source database that is supported by an active community of open source developers and enthusiasts. MySQL Cluster Community Edition is available as a separate download

MySQL itself is open source and can be used as a standalone product in a commercial environment. If you're running mySQL on a web server, you are free to do so for any purpose, commercial or not. If you run a website that uses mySQL, you won't need to release any of your code. You'll be fine

It is not really difficult to learn SQL.

SQL is not a programming language, it's a query language. ... It is also an English like language so anyone who can use English at a basic level can write SQL query easily. The good news is that most DB engines are compatible with all SQL code

SQL Exercises, Practice, Solution - exercises on hospital Database

https://www.w3resource.com/sql-exercises/hospital-database-exercise/index.php

An introduction about hospital Database

Sample Database: hospital

With the help of a Hospital Database, this exercises will help you undestand simple SQL select queries to advanced multi table JOIN queries.

Sample Database description:

Hospitals are the most important part of our lives, trying to provide the best medical facilities to people suffering from various type of illness, which may be due to change in climate conditions, increased work-load, emotional trauma stress etc. It is very much difficult for the hospital to maintain its day-to-day acitivities and records manually. That is why a database is required to keep records of all type of acitivities of a hospital.

List of tables in the hospital database:

physician
department
affiliated_with
procedure
trained_in
patient
nurse
appointment
medication
prescribes
block
room
on_call
stay
undergoes

E R Diagram of Hospital Database:

physician:

employeeid – this is a unique ID of a physician
name – this is the name of a physician
position – this is the designation of a physician
ssn – this is a security number of a physician

department:

departmentid – this is a unique ID for a department
name – this is the name of a department
head – this is the ID of the physician who is the head of a department, referencing to the column employeeid of the table physician

affiliated_with:

physician – this is the ID of the physicians which is referencing to the column employeeid of the physician table
department – this is the ID the department which is referencing to the column departmentid of the department table
primaryaffiliation – this is a logical column which indicate that whether the physicians are yet to be affiliated or not
Note: The combination of physician, department will come once in that table.

procedure:

code – this is the unique ID of a medical procedure
name – the name of the medical procedure
cost – the cost for the procedure

trained_in:

physician – this is ID of the physicians which is referencing to the column employeeid of the physician table
treatment – this is the ID of the medical procedure which is referencing to the column code of the procedure table
certificationdate – this is the starting date of certification
certificationexpires – this is the expiry date of certification
Note: The combination of physician and treatement will come once in that table.

patient:

ssn – this is a unique ID for each patient
name – this is the name of the patient
address – this is the address of the patient
phone – this is the phone number of the patient
insuranceid – this is the insurance id of the patient
pcp – this is the ID of the physician who primarily checked up the patient which is referencing to the column employeeid of the physician table

nurse:

employeeid – this is the unique ID for a nurse
name – name of the nurses
position – the designation of the nurses
registered – this is a logical column which indicate that whether the nurses are registered for nursing or not
ssn – this is the security number of a nurse

appointment:

appointmentid – this is the unique ID for an appointment
patient – this is the ID of each patient which is referencing to the ssn column of patient table
prepnurse – the ID of the nurse who may attend the patient with the physician, which is referencing to the column employeeid of the nurse table
physician – this is the ID the physicians which is referencing to the employeeid column of the physician table
start_dt_time – this is the schedule date and approximate time to meet the physician
end_dt_time – this is the schedule date and approximate time to end the meeting
examinationroom – this the room where to meet a patient to the physician

medication:

code – this is the unique ID for a medicine
name – this is the name of the medicine
brand – this is the brand of the medicine
description – this is the description of the medicine

prescribes:

physician – this is the ID of the physician referencing to the employeeid column of the physician table
patient – this is the ID of the patient which is referencing to the ssn column of the patient table
medication – the ID of the medicine which is referencing to the code of the medication table
date – the date and time of the prescribed medication
appointment – the prescription made by the physician to a patient who may taken an appointment which is referencing to column appointmentid of appointment table
dose – the dose prescribed by the physician
Note: The combination of physician, patient, medication, date will come once in that table.

block:

blockfloor – ID of the floor
blockcode - ID of the block
Note: The combination of blockfloor, blockcode will come once in that table.

room:

roomnumber – this is the unique ID of a room
roomtype – this is type of room
blockfloor - this is the floor ID where the room in
blockcode – this is the ID of the block where the room in
unavailable – this is the logical column which indicate that whether the room is available or not
Note: The of blockfloor, blockcode columns are refercing to the combination of blockfloor and blockcode columns of the table block.

on_call:

nurse – this is ID of the nurse which is referencing to the employeeid column of the table nurse
blockfloor - this is the ID of the floor
blockcode – this is the ID of block
oncallstart - the starting date and time of on call duration
oncallend – the ending date and time of on call duration
Note: The combination of nurse, blockfloor, blockcode, oncallstart, oncallend will come once in that table and the combination of blockfloor, blockcode columns are refercing to the combination of blockfloor and blockcode columns of the table block .

stay:

stayid - this is unique ID for the admission
patient – this is the ID of the patient which is referencing the ssn column of patient table
room - this is the ID of the room where the patient admitted and which is referencing to the roomnumber column of the room table
start_time – this is the time when a patient admitted
end_time – this is the time how long a patient is staying

undergoes:

patient - this is ID of the patient which is referencing to the ssn column of the patient table
procedure – this is ID of the procedure and referencing to the code column of the procedure table
stay - this is the ID admission of a patient, which is referencing to the stayid column of the stay table
date – this is the date when a patient undergoes for a medical procedure
physician – this is the ID of a physician which is referencing to the column employeeid of the table physician
assistingnurse – this is the ID of a nurse who will assists the physician, referencing to the column employeeid of the table nurse
Note: The combination ofpatient, procedure, stay, date will come once in that table.

The database for hospital management system used for this exercises is based upon a database available in wikipedia. Sturcture and Data are may have gone through alterations sporadically.

https://www.w3resource.com/sql-exercises/hospital-database-exercise/sql-exercise-hospital-database-3.php

SQL [39 exercises with solution]

[An editor is available at the bottom of the page to write and execute the scripts.]

Sample Database: hospital

1. Write a query in SQL to find all the information of the nurses who are yet to be registered. Go to the editor

ANSWER

Sample Solution:

SELECT *

FROM nurse

WHERE registered='false';

Copy

Sample Output:

employeeid | name | position | registered | ssn

------------+--------------+----------+------------+-----------

103 | Paul Flowers | Nurse | f | 333333330

(1 row)

2. Write a query in SQL to find the name of the nurse who are the head of their department. Go to the editor

ANSWER

Sample Solution:

SELECT name AS "Name",

POSITION AS "Position"

FROM nurse

WHERE POSITION='Head Nurse';

Copy

Sample Output:

Name | Position

----------------+------------

Carla Espinosa | Head Nurse

(1 row)

3. Write a query in SQL to obtain the name of the physicians who are the head of each department. Go to the editor

Sample table: physician

Sample table: department

Click me to see the solution

ANSWER

Sample Solution:

SELECT d.name AS "Department",

p.name AS "Physician"

FROM department d,

physician p

WHERE d.head=p.employeeid;

Copy

Sample Output:

Department | Physician

------------------+--------------

General Medicine | Percival Cox

Surgery | John Wen

Psychiatry | Molly Clock

(3 rows)

4. Write a query in SQL to count the number of patients who taken appointment with at least one physician. Go to the editor

Sample table: appointment

ANSWER

Sample Solution:

SELECT blockfloor AS "Floor",

blockcode AS "Block"

FROM room

WHERE roomnumber=212;

Copy

Sample Output:

Floor | Block

-------+-------

2 | 2

(1 row)

Click me to see the solution

5. Write a query in SQL to find the floor and block where the room number 212 belongs to. Go to the editor

Sample table: room

ANSWER

ample Solution:

SELECT blockfloor AS "Floor",

blockcode AS "Block"

FROM room

WHERE roomnumber=212;

Copy

Sample Output:

Floor | Block

-------+-------

2 | 2

(1 row)

Click me to see the solution

6. Write a query in SQL to count the number available rooms. Go to the editor

Sample table: room

Click me to see the solution

ANSWER

Sample Solution:

SELECT count(*) "Number of available rooms"

FROM room

WHERE unavailable='false';

Copy

Sample Output:

Number of available rooms

---------------------------

(1 row)

7. Write a query in SQL to count the number of unavailable rooms. Go to the editor

Sample table: room

ANSWER

Sample Solution:

SELECT count(*) "Number of available rooms"

FROM room

WHERE unavailable='true';

Copy

Sample Output:

Number of available rooms

---------------------------

(1 row)

Click me to see the solution

8. Write a query in SQL to obtain the name of the physician and the departments they are affiliated with. Go to the editor

Sample table: physician

Sample table: department

Sample table: affiliated_with

ANSWER

Sample Solution:

SELECT p.name AS "Physician",

d.name AS "Department"

FROM physician p,

department d,

affiliated_with a

WHERE p.employeeid=a.physician

AND a.department=d.departmentid;

Copy

Sample Output:

Physician | Department

-------------------+------------------

John Dorian | General Medicine

Elliot Reid | General Medicine

Christopher Turk | General Medicine

Christopher Turk | Surgery

Percival Cox | General Medicine

Bob Kelso | General Medicine

Todd Quinlan | Surgery

John Wen | General Medicine

John Wen | Surgery

Keith Dudemeister | General Medicine

Molly Clock | Psychiatry

(11 rows)

Click me to see the solution

9. Write a query in SQL to obtain the name of the physicians who are trained for a special treatement. Go to the editor

Sample table: physician

Sample table: procedure

Sample table: trained_in

Click me to see the solution

ANSWER

Sample Solution:

SELECT p.name AS "Physician",

c.name AS "Treatement"

FROM physician p,

PROCEDURE c,

trained_in t

WHERE t.physician=p.employeeid

AND t.treatment=c.code;

Copy

Sample Output:

Physician | Treatement

------------------+--------------------------------

Christopher Turk | Reverse Rhinopodoplasty

Christopher Turk | Obtuse Pyloric Recombobulation

Christopher Turk | Obfuscated Dermogastrotomy

Christopher Turk | Reversible Pancreomyoplasty

Christopher Turk | Follicular Demiectomy

Todd Quinlan | Obtuse Pyloric Recombobulation

Todd Quinlan | Obfuscated Dermogastrotomy

Todd Quinlan | Reversible Pancreomyoplasty

John Wen | Reverse Rhinopodoplasty

John Wen | Obtuse Pyloric Recombobulation

John Wen | Folded Demiophtalmectomy

John Wen | Complete Walletectomy

John Wen | Obfuscated Dermogastrotomy

John Wen | Reversible Pancreomyoplasty

John Wen | Follicular Demiectomy

(15 row

10. Write a query in SQL to obtain the name of the physicians with department who are yet to be affiliated. Go to the editor

Sample table: physician

Sample table: affiliated_with

Sample table: department

ANSWER

Sample Solution:

SELECT p.name AS "Physician",

p.position,

d.name AS "Department"

FROM physician p

JOIN affiliated_with a ON a.physician=p.employeeid

JOIN department d ON a.department=d.departmentid

WHERE primaryaffiliation='false';

Copy

Sample Output:

Physician | position | Department

------------------+------------------------------+------------------

Christopher Turk | Surgical Attending Physician | General Medicine

John Wen | Surgical Attending Physician | General Medicine

(2 rows)

Click me to see the solution

11. Write a query in SQL to obtain the name of the physicians who are not a specialized physician. Go to the editor

Sample table: physician

ANSWER

Sample Solution:

SELECT p.name AS "Physician",

p.position "Designation"

FROM physician p

LEFT JOIN trained_in t ON p.employeeid=t.physician

WHERE t.treatment IS NULL

ORDER BY employeeid;

Copy

Sample Output:

Physician | Designation

-------------------+----------------------------

John Dorian | Staff Internist

Elliot Reid | Attending Physician

Percival Cox | Senior Attending Physician

Bob Kelso | Head Chief of Medicine

Keith Dudemeister | MD Resident

Molly Clock | Attending Psychiatrist

(6 rows)

Click me to see the solution

12. Write a query in SQL to obtain the name of the patients with their physicians by whom they got their preliminary treatement. Go to the editor

Sample table: patient

Sample table: physician

ANSWER

Sample Solution:

SELECT t.name AS "Patient",

t.address AS "Address",

p.name AS "Physician"

FROM patient t

JOIN physician p ON t.pcp=p.employeeid;

Copy

Sample Output:

Patient | Address | Physician

-------------------+--------------------+------------------

John Smith | 42 Foobar Lane | John Dorian

Grace Ritchie | 37 Snafu Drive | Elliot Reid

Random J. Patient | 101 Omgbbq Street | Elliot Reid

Dennis Doe | 1100 Foobaz Avenue | Christopher Turk

(4 rows)

Click me to see the solution

13. Write a query in SQL to find the name of the patients and the number of physicians they have taken appointment. Go to the editor

Sample table: appointment

Sample table: patient

ANSWER

Sample Solution:

SELECT p.name "Patient",

count(t.patient) "Appointment for No. of Physicians"

FROM appointment t

JOIN patient p ON t.patient=p.ssn

GROUP BY p.name

HAVING count(t.patient)>=1;

Copy

Sample Output:

Patient | Appointment for No. of Physicians

-------------------+-----------------------------------

Grace Ritchie | 2

John Smith | 3

Dennis Doe | 3

Random J. Patient | 1

(4 rows)

Click me to see the solution

14. Write a query in SQL to count number of unique patients who got an appointment for examination room C. Go to the editor

Sample table: appointment

ANSWER

Sample Solution:

SELECT count(DISTINCT patient) AS "No. of patients got appointment for room C"

FROM appointment

WHERE examinationroom='C';

Copy

Sample Output:

No. of patients got appointment for room C

--------------------------------------------

(1 row)

Click me to see the solution

15. Write a query in SQL to find the name of the patients and the number of the room where they have to go for their treatment. Go to the editor

Sample table: patient

Sample table: appointment

ANSWER

Sample Solution:

SELECT p.name AS "Patient",

a.examinationroom AS "Room No.",

a.start_dt_time AS "Date and Time of appointment"

FROM patient p

JOIN appointment a ON p.ssn=a.patient;

Copy

Sample Output:

Patient | Room No. | Date and Time of appointment

-------------------+----------+------------------------------

John Smith | A | 2008-04-24 10:00:00

Grace Ritchie | B | 2008-04-24 10:00:00

John Smith | A | 2008-04-25 10:00:00

Dennis Doe | B | 2008-04-25 10:00:00

Dennis Doe | C | 2008-04-26 10:00:00

Random J. Patient | C | 2008-04-26 11:00:00

John Smith | C | 2008-04-26 12:00:00

Dennis Doe | A | 2008-04-27 10:00:00

Grace Ritchie | B | 2008-04-27 10:00:00

(9 rows)

Click me to see the solution

16. Write a query in SQL to find the name of the nurses and the room scheduled, where they will assist the physicians. Go to the editor

Sample table: nurse

Sample table: appointment

Click me to see the solution

ANSWER

Sample Solution:

SELECT n.name AS "Name of the Nurse",

a.examinationroom AS "Room No."

FROM nurse n

JOIN appointment a ON a.prepnurse=n.employeeid;

Copy

Sample Output:

Name of the Nurse | Room No.

-------------------+----------

Carla Espinosa | A

Carla Espinosa | B

Laverne Roberts | A

Paul Flowers | B

Paul Flowers | C

Laverne Roberts | A

Carla Espinosa | B

(7 rows)

17. Write a query in SQL to find the name of the patients who taken the appointment on the 25th of April at 10 am, and also display their physician, assisting nurses and room no. Go to the editor

Sample table: patient

Sample table: appointment

Sample table: nurse

Sample table: physician

Click me to see the solution

ANSWER

Sample Solution:

SELECT t.name AS "Name of the patient",

n.name AS "Name of the Nurse assisting the physician",

p.name AS "Name of the physician",

a.examinationroom AS "Room No.",

a.start_dt_time

FROM patient t

JOIN appointment a ON a.patient=t.ssn

JOIN nurse n ON a.prepnurse=n.employeeid

JOIN physician p ON a.physician=p.employeeid

WHERE start_dt_time='2008-04-25 10:00:00';

Copy

Sample Output:

Name of the patient | Name of the Nurse assisting the physician | Name of the physician | Room No. | start_dt_time

---------------------+-------------------------------------------+-----------------------+----------+---------------------

John Smith | Laverne Roberts | John Dorian | A | 2008-04-25 10:00:00

Dennis Doe | Paul Flowers | Percival Cox | B | 2008-04-25 10:00:00

(2 rows)

18. Write a query in SQL to find the name of patients and their physicians who does not require any assistance of a nurse. Go to the editor

Sample table: patient

Sample table: appointment

Sample table: physician

Click me to see the solution

ANSWER

Sample Solution:

SELECT t.name AS "Name of the patient",

p.name AS "Name of the physician",

a.examinationroom AS "Room No."

FROM patient t

JOIN appointment a ON a.patient=t.ssn

JOIN physician p ON a.physician=p.employeeid

WHERE a.prepnurse IS NULL;

Copy

Sample Output:

Name of the patient | Name of the physician | Room No.

---------------------+-----------------------+----------

John Smith | Christopher Turk | C

Dennis Doe | Percival Cox | C

(2 rows)

19. Write a query in SQL to find the name of the patients, their treating physicians and medication. Go to the editor

Sample table: patient

Sample table: prescribes

Sample table: physician

Sample table: medication

ANSWER

Sample Solution:

SELECT t.name AS "Patient",

p.name AS "Physician",

m.name AS "Medication"

FROM patient t

JOIN prescribes s ON s.patient=t.ssn

JOIN physician p ON s.physician=p.employeeid

JOIN medication m ON s.medication=m.code;

Copy

Sample Output:

Patient | Physician | Medication

------------+-------------+--------------

John Smith | John Dorian | Procrastin-X

Dennis Doe | Molly Clock | Thesisin

(3 rows)

Click me to see the solution

20. Write a query in SQL to find the name of the patients who taken an advanced appointment, and also display their physicians and medication. Go to the editor

Sample table: patient

Sample table: prescribes

Sample table: physician

Sample table: medication

ANSWER

Sample Solution:

SELECT t.name AS "Patient",

p.name AS "Physician",

m.name AS "Medication"

FROM patient t

JOIN prescribes s ON s.patient=t.ssn

JOIN physician p ON s.physician=p.employeeid

JOIN medication m ON s.medication=m.code

WHERE s.appointment IS NOT NULL;

Copy

Sample Output:

Patient | Physician | Medication

------------+-------------+--------------

John Smith | John Dorian | Procrastin-X

Dennis Doe | Molly Clock | Thesisin

(2 rows)

Click me to see the solution

21. Write a query in SQL to find the name and medication for those patients who did not take any appointment. Go to the editor

Sample table: patient

Sample table: prescribes

Sample table: physician

Sample table: medication

Click me to see the solution

ANSWER

Sample Solution:

SELECT t.name AS "Patient",

p.name AS "Physician",

m.name AS "Medication"

FROM patient t

JOIN prescribes s ON s.patient=t.ssn

JOIN physician p ON s.physician=p.employeeid

JOIN medication m ON s.medication=m.code

WHERE s.appointment IS NULL;

Copy

Sample Output:

Patient | Physician | Medication

------------+-------------+------------

Dennis Doe | Molly Clock | Thesisin

(1 row)

22. Write a query in SQL to count the number of available rooms in each block. Go to the editor

Sample table: room

ANSWER

Sample Solution:

SELECT blockcode AS "Block",

count(*) "Number of available rooms"

FROM room

WHERE unavailable='false'

GROUP BY blockcode

ORDER BY blockcode;

Copy

Sample Output:

Block | Number of available rooms

-------+---------------------------

1 | 9

2 | 10

3 | 10

(3 rows)

Click me to see the solution

23. Write a query in SQL to count the number of available rooms in each floor. Go to the editor

Sample table: room

Click me to see the solution

ANSWER

Sample Solution:

SELECT blockfloor AS "Floor",

count(*) "Number of available rooms"

FROM room

WHERE unavailable='false'

GROUP BY blockfloor

ORDER BY blockfloor;

Copy

Sample Output:

Floor | Number of available rooms

-------+---------------------------

1 | 8

2 | 7

3 | 7

4 | 7

(4 rows)

24. Write a query in SQL to count the number of available rooms for each block in each floor. Go to the editor

Sample table: room

ANSWER

Sample Solution:

SELECT blockfloor AS "Floor",

       blockcode AS "Block",

       count(*) "Number of available rooms"

FROM room

WHERE unavailable='false'

GROUP BY blockfloor,

        blockcode

ORDER BY blockfloor,

        blockcode;

Copy

Sample Output:

 Floor | Block | Number of available rooms

-------+-------+---------------------------

     1 |     1 |                         3

     1 |     2 |                         2

     1 |     3 |                         3

     2 |     1 |                         2

     2 |     2 |                         2

     2 |     3 |                         3

     3 |     1 |                         2

     3 |     2 |                         3

     3 |     3 |                         2

     4 |     1 |                         2

     4 |     2 |                         3

     4 |     3 |                         2

(12 rows)

Practice Online

Click me to see the solution

25. Write a query in SQL to count the number of unavailable rooms for each block in each floor. Go to the editor

Sample table: room

Click me to see the solution

ANSWER

Sample Solution:

SELECT blockfloor AS "Floor",

blockcode AS "Block",

count(*) "Number of available rooms"

FROM room

WHERE unavailable='true'

GROUP BY blockfloor,

blockcode

ORDER BY blockfloor,

blockcode;

Copy

Sample Output:

Floor | Block | Number of available rooms

-------+-------+---------------------------

1 | 2 | 1

2 | 1 | 1

2 | 2 | 1

3 | 1 | 1

3 | 3 | 1

4 | 1 | 1

4 | 3 | 1

(7 rows)

26. Write a query in SQL to find out the floor where the maximum no of rooms are available. Go to the editor

Sample table: room

Click me to see the solution

ANSWER

Sample Solution:

SELECT blockfloor as "Floor",

count(*) AS "No of available rooms"

FROM room

WHERE unavailable='false'

GROUP BY blockfloor

HAVING count(*) =

(SELECT max(zz) AS highest_total

FROM

( SELECT blockfloor ,

count(*) AS zz

FROM room

WHERE unavailable='false'

GROUP BY blockfloor ) AS t );

Copy

Sample Output:

Floor | No of available rooms

-------+-----------------------

1 | 8

(1 row

27. Write a query in SQL to find out the floor where the minimum no of rooms are available. Go to the editor

Sample table: room

ANSWER

Sample Solution:

SELECT blockfloor as "Floor",

count(*) AS "No of available rooms"

FROM room

WHERE unavailable='false'

GROUP BY blockfloor

HAVING count(*) =

(SELECT min(zz) AS highest_total

FROM

( SELECT blockfloor ,

count(*) AS zz

FROM room

WHERE unavailable='false'

GROUP BY blockfloor ) AS t );

Copy

Sample Output:

Floor | No of available rooms

-------+-----------------------

3 | 7

4 | 7

2 | 7

(3 rows

Click me to see the solution

28. Write a query in SQL to obtain the name of the patients, their block, floor, and room number where they are admitted. Go to the editor

Sample table: stay

Sample table: patient

Sample table: room

Click me to see the solution

ANSWER

Sample Solution:

SELECT p.name AS "Patient",

s.room AS "Room",

r.blockfloor AS "Floor",

r.blockcode AS "Block"

FROM stay s

JOIN patient p ON s.patient=p.ssn

JOIN room r ON s.room=r.roomnumber;

Copy

Sample Output:

Patient | Room | Floor | Block

-------------------+------+-------+-------

John Smith | 111 | 1 | 2

Random J. Patient | 123 | 1 | 3

Dennis Doe | 112 | 1 | 2

(3 rows)

29. Write a query in SQL to obtain the nurses and the block where they are booked for attending the patients on call. Go to the editor

Sample table: nurse

Sample table: on_call

Click me to see the solution

ANSWER

Sample Solution:

SELECT n.name AS "Nurse",

o.blockcode AS "Block"

FROM nurse n

JOIN on_call o ON o.nurse=n.employeeid;

Copy

Sample Output:

Nurse | Block

-----------------+-------

Carla Espinosa | 1

Carla Espinosa | 2

Laverne Roberts | 3

Paul Flowers | 1

Paul Flowers | 2

Paul Flowers | 3

(6 rows

30. Write a query in SQL to make a report which will show -
a) name of the patient,
b) name of the physician who is treating him or her,
c) name of the nurse who is attending him or her,
d) which treatement is going on to the patient,
e) the date of release,
f) in which room the patient has admitted and which floor and block the room belongs to respectively. Go to the editor

Sample table: undergoes

Sample table: patient

Sample table: physician

Sample table: nurse

Sample table: stay

Sample table: room

Click me to see the solution

ANSWER

Sample Solution:

SELECT p.name AS "Patient",

y.name AS "Physician",

n.name AS "Nurse",

u.date AS "Date of release",

r.roomnumber AS "Room",

r.blockfloor AS "Floor",

r.blockcode AS "Block"

FROM undergoes u

JOIN patient p ON u.patient=p.ssn

JOIN physician y ON u.physician=y.employeeid

LEFT JOIN nurse n ON u.assistingnurse=n.employeeid

JOIN stay s ON u.stay=s.stayid

JOIN room r ON s.room=r.roomnumber;

Copy

Sample Output:

------------+------------------+-----------------+---------------------+------+-------+-------

John Smith | Christopher Turk | Carla Espinosa | 2008-05-02 00:00:00 | 111 | 1 | 2

John Smith | John Wen | Carla Espinosa | 2008-05-03 00:00:00 | 111 | 1 | 2

Dennis Doe | Christopher Turk | Laverne Roberts | 2008-05-07 00:00:00 | 112 | 1 | 2

Dennis Doe | Todd Quinlan | | 2008-05-09 00:00:00 | 112 | 1 | 2

John Smith | John Wen | Carla Espinosa | 2008-05-10 00:00:00 | 112 | 1 | 2

Dennis Doe | Christopher Turk | Paul Flowers | 2008-05-13 00:00:00 | 112 | 1 | 2

(6 rows)

31. Write a SQL query to obtain the names of all the physicians performed a medical procedure but they are not ceritifed to perform. Go to the editor

Sample table: physician

Sample table: undergoes

Sample table: trained_in

ANSWER

Sample Solution:

SELECT name AS "Physician"

FROM physician

WHERE employeeid IN

( SELECT undergoes.physician

FROM undergoes

LEFT JOIN trained_In ON undergoes.physician=trained_in.physician

AND undergoes.procedure=trained_in.treatment

WHERE treatment IS NULL );

Copy

Sample Output:

Physician

------------------

Christopher Turk

(1 row)

Click me to see the solution

32. Write a query in SQL to obtain the names of all the physicians, their procedure, date when the procedure was carried out and name of the patient on which procedure have been carried out but those physicians are not cetified for that procedure. Go to the editor

Sample table: physician

Sample table: undergoes

Sample table: patient

Sample table: procedure

Click me to see the solution

ANSWER

Sample Solution:

SELECT p.name AS "Physician",

pr.name AS "Procedure",

u.date,

pt.name AS "Patient"

FROM physician p,

undergoes u,

patient pt,

PROCEDURE pr

WHERE u.patient = pt.SSN

AND u.procedure = pr.Code

AND u.physician = p.EmployeeID

AND NOT EXISTS

( SELECT *

FROM trained_in t

WHERE t.treatment = u.procedure

AND t.physician = u.physician );

Copy

Sample Output:

Physician | Procedure | date | Patient

------------------+-----------------------+---------------------+------------

Christopher Turk | Complete Walletectomy | 2008-05-13 00:00:00 | Dennis Doe

(1 row)

33. Write a query in SQL to obtain the name and position of all physicians who completed a medical procedure with certification after the date of expiration of their certificate. Go to the editor

Sample table: physician

Sample table: undergoes

Sample table: trained_in

ANSWER

Sample Solution:

SELECT name AS "Physician",

position AS "Position"

FROM physician

WHERE employeeid IN

( SELECT physician

FROM undergoes u

WHERE date >

( SELECT certificationexpires

FROM trained_in t

WHERE t.physician = u.physician

AND t.treatment = u.procedure ) );

Copy

Sample Output:

Physician | Position

--------------+------------------------------

Todd Quinlan | Surgical Attending Physician

(1 row)

Click me to see the solution

34. Write a query in SQL to obtain the name of all those physicians who completed a medical procedure with certification after the date of expiration of their certificate, their position, procedure they have done, date of procedure, name of the patient on which the procedure had been applied and the date when the certification expired. Go to the editor

Sample table: physician

Sample table: undergoes

Sample table: patient

Sample table: procedure

Sample table: trained_in

Click me to see the solution

ANSWER

Sample Solution:

SELECT p.name AS "Physician",

p.position AS "Position",

pr.name AS "Procedure",

u.date AS "Date of Procedure",

pt.name AS "Patient",

t.certificationexpires AS "Expiry Date of Certificate"

FROM physician p,

undergoes u,

patient pt,

PROCEDURE pr,

trained_in t

WHERE u.patient = pt.ssn

AND u.procedure = pr.code

AND u.physician = p.employeeid

AND Pr.code = t.treatment

AND P.employeeid = t.physician

AND u.Date > t.certificationexpires;

Copy

Sample Output:

--------------+------------------------------+----------------------------+---------------------+------------+-------------------------

(1 row)

35. Write a query in SQL to obtain the names of all the nurses who have ever been on call for room 122. Go to the editor

Sample table: nurse

Sample table: on_call

Sample table: room

ANSWER

Sample Solution:

SELECT n.name

FROM nurse n

WHERE employeeid IN

( SELECT oc.nurse

FROM on_call oc,

room r

WHERE oc.blockfloor = r.blockfloor

AND oc.blockcode = r.blockcode

AND r.roomnumber = 122 );

Copy

Sample Output:

name

-----------------

Laverne Roberts

Paul Flowers

(2 rows)

Click me to see the solution

36. Write a query in SQL to Obtain the names of all patients who has been prescribed some medication by his/her physician who has carried out primary care and the name of that physician. Go to the editor

Sample table: patient

Sample table: prescribes

Sample table: physician

Click me to see the solution

ANSWER

Sample Solution:

SELECT pt.name AS "Ptient",

p.name AS "Physician"

FROM patient pt

JOIN prescribes pr ON pr.patient=pt.ssn

JOIN physician p ON pt.pcp=p.employeeid

WHERE pt.pcp=pr.physician

AND pt.pcp=p.employeeid;

Copy

Sample Output:

Ptient | Physician

------------+-------------

John Smith | John Dorian

(1 row)

37. Write a query in SQL to obtain the names of all patients who has been undergone a procedure costing more than $5,000 and the name of that physician who has carried out primary care. Go to the editor

Sample table: patient

Sample table: undergoes

Sample table: physician

Sample table: procedure

ANSWER

Click me to see the solution

Sample Solution:

SELECT pt.name AS "Ptient",

p.name AS "Primary Physician",

pd.cost AS "Porcedure Cost"

FROM patient pt

JOIN undergoes u ON u.patient=pt.ssn

JOIN physician p ON pt.pcp=p.employeeid

JOIN PROCEDURE pd ON u.procedure=pd.code

WHERE pd.cost>5000;

Copy

Sample Output:

Ptient | Primary Physician | Porcedure Cost

------------+-------------------+----------------

John Smith | John Dorian | 5600

Dennis Doe | Christopher Turk | 10000

(2 rows)

38. Write a query in SQL to Obtain the names of all patients who had at least two appointment where the nurse who prepped the appointment was a registered nurse and the physician who has carried out primary care. Go to the editor

Sample table: appointment

Sample table: patient

Sample table: nurse

Sample table: physician

ANSWER

Sample Solution:

SELECT pt.name AS "Patient",

p.name AS "Primary Physician",

n.name AS "Nurse"

FROM appointment a

JOIN patient pt ON a.patient=pt.ssn

JOIN nurse n ON a.prepnurse=n.employeeid

JOIN physician p ON pt.pcp=p.employeeid

WHERE a.patient IN

(SELECT patient

FROM appointment a

GROUP BY a.patient

HAVING count(*)>=2)

AND n.registered='true'

ORDER BY pt.name;

Copy

Sample Output:

Patient | Primary Physician | Nurse

---------------+-------------------+-----------------

Dennis Doe | Christopher Turk | Laverne Roberts

Grace Ritchie | Elliot Reid | Carla Espinosa

John Smith | John Dorian | Carla Espinosa

John Smith | John Dorian | Laverne Roberts

(5 rows)

Click me to see the solution

39. Write a query in SQL to Obtain the names of all patients whose primary care is taken by a physician who is not the head of any department and name of that physician along with their primary care physician. Go to the editor

Sample table: patient