PLs everyone, I really need help, the rest of the questions are really difficult and totally no idea how to do it. pls help me.... and I have pasted the oringial datebase in the bottom.... pls help me.............. I've spent so much time on it.................. pls 15. The hospital has prepared a survey to be sent to each patient. To save postage they wish to identify pairs of patients who live at the same address. SELECT patientID, address WHERE one.patient = two.patient? and then.........??? 16. To satisfy a Health department audit requirement concerning service experience, the hospital is required to identify, for all doctors performing treatments, their first recorded treatment of any patient. 17. List the patient ID, procedure code and date of administration of all out-patient treatments. Would your script work if the problem identified in requirement 4 had not been fixed? 18. In planning for hospital expansion, the hospital is aware that the bed capacity of each ward is 6, except for surgical where it is 12, although currently there are fewer beds than that actually provided in each ward. Add a field for this ward capacity to the database, and then show how many extra beds could be added to each ward to bring them up to full capacity? The orginial hospital database: DROP TABLE treatment; DROP TABLE admission; DROP TABLE patient; DROP TABLE ward; DROP TABLE bed; DROP TABLE doctor; DROP TABLE procedure; CREATE TABLE patient( patientID char(4) primary key, Surname varchar(15) not null, GivenNames varchar(15), address varchar(40), phone varchar(15), dateOfBirth date, gender char(1)); CREATE TABLE ward( wardNumber char(2) primary key, wardName varchar(20)); CREATE TABLE bed( wardNumber char(2), bedNumber number, constraint bed_pk primary key (wardNumber, bedNumber)); CREATE TABLE admission( patientID char(4), admissionDate date, dischargeDate date, reason char(1), wardNumber char(2), bedNumber number, constraint admission_pk primary key (patientID, admissionDate), constraint admission_bed_fk foreign key (wardNumber, bedNumber) references bed (wardNumber, bedNumber)); CREATE TABLE doctor( medibankNumber char(6) primary key, doctorName varchar(20), specialisation char(1)); CREATE TABLE procedure( code char(3) primary key, procedureName varchar(20), charge number(6,2)); CREATE TABLE treatment( treatmentCode char(3), patientID char(4), treatmentAdministered date, doctorNumber char(6), constraint treatment_pk primary key (treatmentCode, patientID, treatmentAdministered), constraint treatment_patient_fk foreign key (patientID) references patient(patientID), constraint treatment_doctor_fk foreign key (doctorNUmber) references doctor(medibankNumber), constraint treatment_procedure_fk foreign key (treatmentCode) references procedure(code));

Hi, is this your homework ? or just a case study? 15. use group by .. ex select address, count(*) from patient group by address having count(*) > 1 16. select medibankNumber, (select top 1 treatmentCode from treatment where doctorNumber = medibankNumber order by treatmentAdministered) as TreatmentCodeOfFirstTime from doctor 17. not requirement 4 .... @@" select a.patientID, b.treatmentCode, b.treatmentAdministered from patient a inner join treament b on a.patientID = b.patientID inner join admission c on a.patientID=c.patientID and dischargeDate > Now (or getdate()) 18. in this requirement , we need a cursor to traverse the min(admissionDate) to max(dischargeDate) date by date to count the beds in each ward to find out the maximum beds needed a day.

I'm using Oracle SQL yeah... it's my assignment, I have 20 questions, working on it day and night,really can't solve the last few questions.... I've tried so hard already, still can't make it.... no idea, no concept, nothing... really thx for all the ppl that help me... really thx