DATABASE SQL PROBLEM QUESTIONS: 1. List all items (number and description) which

Question

DATABASE SQL PROBLEM QUESTIONS:

1. List all items (number and description) which cost between $200 and $500.

2. List all medical record numbers of all patients who were seen in December 2015 with physician 5

3. List all items(number and description) along with cost which have “test” in their description.

4. List all physicians (number, name) in order by name, who work in either oncology or radiology.

5. List all care centers (number, description) in order by description.

6. List all medical record numbers, date admitted, date discharged for all patients who had physician 5 in order by date admitted.

7. List the number of patients each physician saw in order by physician number.

8. List the number of beds in each care center in order of care center.

9. List the care center which includes maternity.

10. List the total number of patients seen.

11. List the room numbers and the bed numbers without a patient.

MOUNTAIN VIEW HOSPITAL PROBLEM:

CARE CENTER

Ctr#

Description

1

Maternity

2

Oncology

3

Emergency

4

Cardiac

5

Surgery

6

ICU

7

Radiology

8                        Physical Therapy

BED

Bed#

Room#

Ctr#

Pat#

1

100

1

3

2

100

1

5

1

102

2

1

2

102

2

-

1

200

1

6

1

300

4

7

PATIENT

Pat#

PatFirst

PatLast

1

James

Brooks

2

Jennie

Davis

3

Milla

Saunders

4

Andrew

Smith

5

Pamela

Miller

6

Kerry

Lawrence

7

Nathan

Halleck

8

Abigal

Clark

PHYSICIAN

Phys#

PhysFirst

PhysLast

Specialty

1

Phyllis

Quinn

Maternity

2

Richard

Smith

Oncology

3

Doris

Davis

Maternity

4

Mario

Gomez

Radiology

5

Robert

Jones

Cardiac

6

George

Carter

Surgery

7

William

Zide

Emergency

8

Kimberly

Vasquez

Surgery

ITEM (MEDICAL/SURGICAL)

Item#

Description

UnitCost

100

Room Semi-Private

1600

101

Room Private

2000

102

Speech Therapy

350

103

Radiology

275

104

Physical Therapy

400

105

EKG Test

500

106

EEG Test

350

107

IV

470

108

X-Rays

450

109

Lab Tests

250

110

Aspirin

3

111

Penicillin

7

PATIENT VISIT

MedRec#                                DateIn                         DateOut          Phys#              Pat#

20151226001I                         2015-12-26                  2015-12-28      2                      1

20151226002O                       2015-12-26                  2015-12-26      7                      2

20151227001I                         2015-12-27                  2015-12-28      1                      5

20151227002I                         2015-12-27                  2015-12-28      3                      6

20160103001O                       2016-01-03                  2016-01-03      7                      7

20160103002I                         2016-01-03                  2016-01-04      3                      8

20160104001O                       2016-01-04                  2016-01-04      5                      4

20160104002O                       2016-01-04                  2016-01-04      5                      1

PATIENT TREATMENT

MedRec#

Item#

Date/Time

Phys#

TrackSample

20151226001I

108

2015-12-26

2

partplan.mp3

20151226001I

109

2015-12-26

2

hearthotel.mp3

20151226001I

103

2015-12-26

2

hardtosay.mp3

20151226001I

100

2015-12-26

2

bluesuede.mp3

20151226002O

107

2015-12-26

7

franfspec.mp3

20151226002O

109

2015-12-26

7

woodheart.mp3

20151226001I

100

2015-12-27

1

rubytues.mp3

20151227001I

110

2015-12-27

1

powergold.mp3

20151227001I

100

2015-12-28

1

heyhey.mp3

20151227002I

110

2015-12-27

3

layla.mp3

20151227002I

100

2015-12-27

3

alberta.mp3

20160103001O

108

2016-01-03

7

20160103002I

100

2016-01-03

3

20160103002I

109

2016-01-03                     

3

20160103002I

108

2016-01-04                 

3

20160104002O      

109

2016-01-04

5

20160104003O

109

2016-01-04

5

Ctr#

Description

1

Maternity

2

Oncology

3

Emergency

4

Cardiac

5

Surgery

6

ICU

7

Radiology

Explanation / Answer

1) Select * from ITEM where UnitCost BETWEEN 200 and 500;

Selects all the rows whose unitcost is between 200 and 500 from ITEMS table

2) Select MedRec form PatientTreatment where Date/Time between 2015-11-30 and 2016-01-01;

  Selects all the rows from PatientTreatment table who was seen in December month

3) Select Item,Description from ITEM where Description LIKE ‘%test%’;

Select all the iteams from ITEM table where description contains word test.

4) Select Phys,PhysFirst,PhysLast form PHYSICIAN where Speciality  IN (‘Oncology’,’Radiology’) ORDER BY PhysFirst,physLast;

Select all the rows from the PHYSICIAN table where speciality matches with wither Oncology or Radiology

5)  Select * from CareCenter ORDER BY Description;

Lists all the rows from carecenter table ordered by description column

6) Select MedRec,DateIn,DateOut from PATIENTVISIT where Phys=5;

Selects all the records from PATIENTVISIT table where physician number is 5.

7) select Phys,count(*) as "No Of Pateints" from PATIENTVISIT group by Phys having count(*)>0 order by Phys;

displays count of patients seen by each doctor

8) Select Ctr,Count(*) as “No Of Beds” from BED group by Ctr by having count(*)>0 order by Ctr;

Displays all the Ctr centers with no of beds.

9) Select Ctr from CARECENTER where Description = ‘Maternity’;

Displays all the maternity centers

10) Select count(MedREC) from PATIENTVISIT;

  Displays number of who has taken treatment

11) Select Room,Bed form BED where Pat!=’’;

Displays all the rows where a patient occupies the where condition is to filter the empty rooms

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.