I need to write SQL statements based on the below tables, for the following scenarios. Can you please help? This is probally simple for you, however I know only a bit of VB and feel useless looking at this.
- Person Table (Person_id, Surname, First_name, Sex, Birth_date, Street, Town, Postcode, Next_of_kin)
- Admission Table (Admission_id, Patient_id, Admission_date, Expected_op, Admitted_by, Ward_code, Discharge_date)
- Operation Table (Operation_id, Actual_op, Admission_id, Op_date, Surgeon, Anaesthetist)
- Observation Table (Admission_id, Observ_date, Observ_time, Observ_type, Observ_value, Staff_id)
- Staff Table (Person_id, Start_date, Staff_type)
- Operation Table (Op_code, Operation_name, Theatre_fee, Days_in)
- Ward Table (Ward_code, Ward_name, Bed_count, Opened_date, Last_painted_date, Daily_charge)
1. List all data about all the women living in Main St. Young, plus the men living in High St. Cooma. (Note that these streets may appear in other towns.)
2. List the first name, surname, latest admission date and number of times admitted for all women. Sort the list so the woman who has been admitted the most times appears at the top.
3. Produce a report that gives for each ward its name, code and the percentage of beds currently occupied in the ward.
4. List all data from the Person table about persons who are neither staff nor patients.
5.You are required to add a Payments table to your database. It includes the following data. (You do not need to submit the Payments table.)
Admission-id Payment-date Amount-paid 205 16/03/92 1000 205 16/04/92 1000 274 10/10/94 600 274 10/12/94 600 279 22/11/94 390
- Only one payment can be made per day for a particular admission.
- A payment is for one admission only. If a patient wishes to pay for several admissions, or people at the same time, it is regarded as separate payments.
- A person may make a partial payment for the ward cost.
For patients who have been discharged but have not paid their ward bill, list the patient id, first name, surname and ward name, admission date and amount owing.
Why do I get the feeling that I am doing somebody's homework for them? Something about this doesn't seem right, so I'm going to answer the first three, and leave the others as an exercise for the student.
select * from Person where ( Sex='F' and Street='Main St.' and Town='Young' ) or ( Sex='M' and Street='High St.' and Town='Cooma' )
select First_name, Surname , max(Admission_date) as Last_admission_date , count(*) as Number_of_admissions from Person , Admission where Person_id = Patient_id and Sex='F' order by 4 descending
select Ward_name, Ward_code , 100 * count(*) / Bed_count as Percent_beds_occupied from Ward , Admissions where Ward.Ward_code = Admission.Ward_code and CURRENT_DATE() >= Admission_date and Discharge_date is null
For More Information
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.