Oracle Database Tables with Queries

Table Creation and Loading

a) Provide (see submission area) the Create statements for tables Doctor, Patient and Appointment with appropriate primary and foreign keys.

b) Develop tables in Oracle and load the data, provide table structure and content  (see submission) provide table content and table structure of each table

All queries MUST be based SOLELY on the information provided and each question must use a SINGLE query .

No views or separate queries, unless otherwise stated. You should have one single composite query for each question.

As always all queries should be data independent. (you can only use data value given in the question in creating query, can not “look up” data from table to answer query.

Ex: Give the names of patient of Dr. Patel

SQL> select pid

from appointment

where did = 155;  2 3

This is incorrect since patel ID (155) is NOT given in the query. To make it data independent you need to get Patel’s ID from his name from DOCTOR table. see below:

PID
-----
P560

SQL> select pid

from appointment

where did in (select did from doctor where upper(name) = 'PATEL');  2  3  4  5 PID

1. Give the names of doctors  (make sure to remove duplicates)

2. Give the id’s of patients that have appointments with a doctor.

3. Give the average age of Cardiologist

4. Give the patients_Id of patients and their treatment before July 9, 2020.

5. Give the Patient_ID of patients treated more than two times.

6. Give the names of doctors who have at least two P’s in their names

7. Give the count of doctor by their specialties.

8. Give the count of doctors with missing specialties?

9. Doctor LEWIS has his specialty listed as cardiology, change it to lung specialist show the change by listing table content, revert back the specialty

10. What were the total charges for each patient? Show output in descending order

11. What was the total revenue for CT Scan and Blood Test?

12. Give the id’s of doctors who treated either Joze or Gobi

13. Give the names of patients whose names contain o and i in any order. (i.e., it could be i and o)

14. Give the charges for each treatment type (ex: CT Scan $535)

15. A new doctor  LAPTA with GP specialty,  is given an ID of 950 and assigned to patient 750 for treatment using appointment id of B400. Process this transaction. You may need several statements for this.

1. Add the doctor

2. Relate the doctor to patient

(Do not actually save it, i.e., rollback after your output is done). Do NOT add patient 750 to Patient table, you may get an integrity constraint error, submit the query with error part and explain why this error is occurring

Appointment And Patient

Doctor And Appointment

And justify the relationship you select

e. Are there any redundancy issues related to this database?

f. If App_id is removed from Appointment table, how would it impact PK and FKs? What would be PK and FKs of this relation?