Subject: DATABASE MANAGEMENT SYSTEM - SQL project, pls solve using oracle

Question

16. List the appt ID, date, patient ID, patient last name, doctor ID, doctor last name, appt status desc for all appointments on or after 9/2/18.Show the date formatted as mm/dd/yy.

22. List the appt ID, date, patient ID, last name, doctor ID, doctor last name, pay type description, and appointment status description for all appointments on or before 9/3/18.Format the date as mm-dd-yyyy and use the following column headings:Appt ID, Date, Patient ID, Patient Name, Dr ID, Dr Name, Pay Type, Appt Status.Sort by Appt ID.

25. For each appointment, list the appt ID, date, patient ID, patient first name, patient last name, doctor ID, doctor last name, and count of reason codes; combine the patient first and last name into one column, and sort by count of reason codes in descending order, then by appt ID in ascending order. Show the date formatted as mm-dd-yyyy. Use the following column headings: Appt, Date, Patient ID, Patient Name, Dr ID, Dr Name, Code Count. Hint: use a GROUP BY clause.

WHAT I HAVE DONE ( is showing ERROR, please fix it , or tell me what i need to change)

SQL> --16.

SQL>

SQL> SELECT ApptID, TO_CHAR(ApptDate, 'mm/dd/yy') as "Date", P.PatientID, P.PatientLName, D.DoctorID, D.DoctorLName, ApptStatusCode

2 FROM APPOINTMENT_xxx A, DOCTOR_xxx D, APPTSTATUS_xxx S, PATIENT_xxx P

3 WHERE A.PatientID = P.PatientID AND

4 A.DoctorID = D.DoctorID AND

5 ApptDate >= 'SEP-02-18';

SELECT ApptID, TO_CHAR(ApptDate, 'mm/dd/yy') as "Date", P.PatientID, P.PatientLName, D.DoctorID, D.DoctorLName, ApptStatusCode

*

ERROR at line 1:

ORA-00918: column ambiguously defined

SQL> --22.

SQL> COLUMN "Patient Name" FORMAT a12

SQL> COLUMN "Pay Type" FORMAT a14

SQL> SELECT ApptID as "Appt ID", TO_CHAR(ApptDate, 'mm-dd-yyyy') as "Date", A.PatientID "Patient ID", PatientLName as "Patient Name", A.DoctorID as "Dr ID", DoctorLName as "Dr Name",B.BillingTypeDesc as "Pay Type", S.ApptStatusDesc as "Appt Status"

2 FROM APPOINTMENT_xxx A, PATIENT_xxx P, DOCTOR_xxx D, APPTSTATUS_xxx S, BILLINGTYPE_xxx B

3 WHERE A.PatientID = P.PatientID AND

4 A.DoctorID = D.DoctorID AND

5 A.BillingType = B.BillingType AND

6 A.ApptStatusCode = S.ApptStatusCode AND

7 ApptDate <= 'SEP-03-2018'

8 ORDER BY ApptID;

SELECT ApptID as "Appt ID", TO_CHAR(ApptDate, 'mm-dd-yyyy') as "Date", A.PatientID "Patient ID", PatientLName as "Patient Name", A.DoctorID as "Dr ID", DoctorLName as "Dr Name",B.BillingTypeDesc as "Pay Type", S.ApptStatusDesc as "Appt Status"

*

ERROR at line 1:

ORA-01722: invalid number

SQL> --25.

SQL> COLUMN "Patient Name" FORMAT a16

SQL> SELECT MAX(AD.ApptID) as "Appt", TO_CHAR(MAX(ApptDate), 'mm/dd/yy') as "Date", MAX(P.PatientID) as "Patient ID", MAX(PatientFName || ' ' || PatientLName) as "Patient Name", MAX(D.DoctorID) as "Dr ID", MAX(DoctorLName) as "Dr Name", COUNT(ApptReasonCode) as "Code Count"

2 FROM APPOINTMENT_xxx A, DOCTOR_xxx D, PATIENT_xxx P, APPTDETAIL_xxx AD

3 WHERE A.ApptID = AD.ApptID AND

4 A.PatientID = P.PatientID AND

5 A.DoctorID = D.DoctorID

6 GROUP BY AD.ApptID

7 ORDER BY COUNT(ApptReasonCode) DESC, AD.ApptID;

SELECT MAX(AD.ApptID) as "Appt", TO_CHAR(MAX(ApptDate), 'mm/dd/yy') as "Date", MAX(P.PatientID) as "Patient ID", MAX(PatientFName || ' ' || PatientLName) as "Patient Name", MAX(D.DoctorID) as "Dr ID", MAX(DoctorLName) as "Dr Name", COUNT(ApptReasonCode) as "Code Count"

*

ERROR at line 1:

ORA-01722: invalid number