Write and answer some (at least five) “reasonable” queries for your database. Wh

Question

Write and answer some (at least five) “reasonable” queries for your database. When I say reasonable queries – I mean 3-4 table joins to use most of the tables or group of tables in your database. You should not have tables that are of no use. For this part, first, write the queries in English – that is, telling me what you are finding in the queries. Then, present the query and finally present the output of the query

Mapping

HOSPITAL                  (hospital_id, telephone, name, address)

DOCTOR                    (doctor_id, d.name, department, telephone, employee_id(fk))

PATIENT                     (patient_id, check_in_date, p.name, address, employee_id(fk))

RESIDENT PATIENT (patient_id, check_in_date, p.name, address, date_admitted)

OUTPATIENT             (patient_id, check_in_date, p.name, address, discharge_time)

EMPLOYEE                (employee_id, e.name)

HOSPITAL Table

Hospital_id

Telephone

Name

Address

BMC4400

(727) 355-4400

Health Center

501 6th Ave S

ACH7451

(954) 898-7451

Children Hospital

1600 S Andrews Ave

PBH6141

(561) 842-6141

West Palm Hospital

2201 45th St

DMH3535

(863) 494-3535

DeSoto Hospital

900 N Robert Ave

JMH1111

(305) 585-1111

Jackson Hospital

1611 NW 12th Ave

DOCTOR   Table

Doctor_id

d.name

Department

Telephone

employee_id

121231

George Layton

Neurological Surgery

(954) 245-8564

null

111452

Julia Tucker

Obstetrics/Gynecology

(727) 659-3548

null

230157

Tim Roth

Internal Medicine

(561) 842-6785

null

011254

Nicholas Briggs

Plastic Surgery

(863) 494-3568

null

251487

Terry Hines

Pediatrics

(305) 585-1122

1004

PATIENT Table

patient_id

doctor_id

check_in_date

p.name

address

employee_id

014567

121231

12/1/15

Andy Bernard

12345 1st Street

null

014568

111452

12/3/15

Kelly Kapoor

85025 Main Street

1006

014569

230157

12/3/15

Ryan Howard

7521 Royal Road

null

014570

011254

12/5/15

Erin Hannon

555 Nice Way

1008

014571

251487

12/7/15

Stanley Hudson

765 4th Street

null

RESIDENT PATIENT table

patient_id

check_in_date

p.name

address

date_admitted

014567

12/1/15

Andy Bernard

12345 1st Street

12/1/15

014568

12/3/15

Kelly Kapoor

85025 Main Street

12/3/15

014569

12/3/15

Ryan Howard

7521 Royal Road

12/3/15

014570

12/5/15

Erin Hannon

555 Nice Way

12/5/15

014571

12/7/15

Stanley Hudson

765 4th Street

12/7/1

OUTPATIENT table

  

patient_id

check_in_date

p.name

address

discharge_time

024567

12/1/15

Rachel Brandon

103 Good Lane

12:15PM

027654

12/3/15

Kasey Smith

45 S Main Street

4:15PM

020987

12/3/15

Raymon Rivera

1254 NW 11th Blvd

7:55PM

021234

12/5/15

Alex Davids

1548 S Way Drive

2:10PM

021234

12/7/15

Sara Chills

765 4th Street

null

EMPLOYEE Table   

employee_id

e.name

1010

Michael Scott

1006

Kelly Kapoor

1011

Pam Beesly

1008

Erin Hannon

1004

Terry Hines

Hospital_id

Telephone

Name

Address

BMC4400

(727) 355-4400

Health Center

501 6th Ave S

ACH7451

(954) 898-7451

Children Hospital

1600 S Andrews Ave

PBH6141

(561) 842-6141

West Palm Hospital

2201 45th St

DMH3535

(863) 494-3535

DeSoto Hospital

900 N Robert Ave

JMH1111

(305) 585-1111

Jackson Hospital

1611 NW 12th Ave

Explanation / Answer

here we are selecting the patient name belonging to particular doctor then we can right

select p.name from PATIENT JOIN DOCTOR

on Doctor_id=doctor_id where check_in_date=12/3/15

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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