using oracle sql

The question is to write the sql statements to generate the following 5 view.

This question covers Sections 2, 3 and 4 of Block 3. It assesses your ability to write SQL queries, including composite queries and those that involve NULL. This question uses the Hospital database. Consider the following tables, each of which was produced by a single query statement. Give a query statement that may be used to produce each table shown below, ensuring in each case that the table content, column names, and the ordering of columns and rows is identical. specialism staff_no doctor_name position 110 Liversage Consultant 131 Kalsi Consultant Cardiac Orthopaedic 156 Hollis Registrar (NULL) 174 Gibson Registrar (NULL) 178 Paxton Registrar (NULL) 200 Tamblin Consultant Paediatric 389 Wright House Officer (NULL) 462 Bell House Officer (NULL) 493 Rampton House Officer (NULL) 517 Goss Registrar (NULL) 603 Nolan House Officer (NULL) The details of each doctor employed at the hospital Table 1 team_code staff_no doctor_name position 104 110 Liversage Consultant 104 156 Hollis Registrar 104 178 Paxton Registrar 17 131 Kalsi Consultant t17 174 Gibson Registrar 389 Wright House Officer +29 200 Tamblin Consultant +29 462 Bell House Officer +29 493 Rampton House Officer L29 517 Goss Registrar t29 603 Nolan House Officer Table 2 The composition of each medical team reason inflamed joints low temperature vomiting Table 3 The medical conditions that did not require any drugs to be prescribed during treatment type year cost Antibiotic 2006 25.20 Antibiotic 2007 169.30 Antifungal 2007 34.20 Painkiller 2006 0.56 Painkiller 2007 8.00 Placebo 2006 0.28 Sedative 2006 14.49 Sedative 2007 20.16 Table 4 The total cost of the different types of drugs prescribed to patients undergoing treatment during 2006 and 2007 ward_no ward_name number_of_beds number_of_patients w2 Wessex 8 6 w3 Anglia 10 4 w4 Lakes 8 3 ws 5 Dales Pennines w7 14 0 Table 5 The number of beds available and the number of patients occupying beds on each ward (25 marks) Doctor StaffNo 110 131 156 174 178 200 389 462 493 517 603 DoctorName Liversage Kalsi Hollis Gibson Paxton Tamblin Wright Bell Rampton Goss Nolan Position Consultant Consultant Registrar Registrar Registrar Consultant House Officer House Officer House Officer Registrar House Officer Consists of StaffNo 156 174 178 389 462 493 517 603 TeamCode 104 t17 t04 t17 129 129 129 129 Team TeamCode 104 t17 t29 TelephoneNo 4382 8173 3492 StaffNo 110 131 Supervises StaffNo 192 206 362 424 789 823 834 Supervisor 523 113 838 126 153 834 126 200 Ward Ward No W2 W3 W4 w5 w7 WardName Wessex Anglia Lakes Dales Pennines NumberOfBeds 8 10 8 6 14 Specialist StaffNo 110 131 200 Specialism Cardiac Orthopaedic Paediatric Ward No w2 w3 Drug Drug Code B23 B48 B63 F28 w7 F29 PO2 Nurse StaffNo 113 126 153 192 206 362 424 523 789 823 834 838 NurseName Hutt Sesonske Cooke Wilkie Reay Colman Cooke Descartes Warnock Wright Boswell Brown w4 w2 w5 w3 w4 DrugName Type Scalagamene Antibiotic Oculustriturol Antibiotic Hallexanuran Antibiotic Disagaric Antifungal Cambogia Antifungal Agonese Painkiller Acheaway Painkiller Capillucanis Painkiller Dolensol Painkiller Dormito Sedative Soporatex Sedative Placebo Placebo Price 1.05 0.95 1.01 0.42 0.45 0.02 0.06 0.04 0.10 0.18 0.21 0.01 P27 P51 P73 w7 S38 w3 w3 w5 S90 X01 Treatment StaffNo 110 131 156 156 178 200 389 389 462 462 517 603 603 603 603 Patientin P68 p07 pog p15 p89 239 31 p98 pos 238 237 238 A38 239 p88 StartDate 2007-01-11 2007-02-09 2007-01-01 2007-02-05 2006-12-23 2006-03-11 2007-01-09 2007-02-05 2007-02-03 2006-12-27 2006-12-05 2007-01-01 2007-02-01 2007-01-01 2006-12-29 Reason vomiting marrow infection low temperature low temperature pain relief septicaemia pain relief inflamed joints high temperature inflamed joints pain relief low temperature pain relief high grade fever high grade fever Patient Patientid p01 Ward No W2 W3 PatientName Thornton Tennent James Kay Harris Rubinstein Boswell Ming Maher Monroe Harris Hunt Dixon Bell Reed Boswell Jarvis Cramer POZ pos pog p15 231 237 238 239 p68 p71 p78 p79 p80 p87 788 p89 Gender F M M F M F F M F F M M F F F M F M Height 162.3 176.8 167.9 164.7 180.6 155.6 172.9 186.3 161.9 165.0 186.3 179.9 163.9 171.3 160.0 168.4 172.9 169.6 Weight 71.6 70.9 70.5 53.2 64.3 70.1 52.6 85.4 73.0 62.6 76.7 74.3 56.5 49.2 59.1 91.4 53.4 74.1 Staff No 110 131 200 110 110 131 200 200 200 110 131 110 131 110 131 200 110 131 w5 W2 W2 W3 W2 w5 W2 W3 w5 W2 W3 w4 w5 w5 p98 Prescription PrescriptionNo P0001 PO005 P0101 P0234 P0328 P2113 P2121 P3342 P3465 P3472 P4231 P8123 P9734 Quantity Daily Dosage 56 8 30 100 20 112 8 120 48 4 48 3 24 3 21 3 80 10 1 28 2 1 StaffNo 462 603 462 603 131 517 389 178 603 603 603 462 200 Patientid p39 pos 239 po7 p37 31 p89 p88 p38 p38 p38 p39 StartDate 2007-02-03 2007-01-01 2007-02-03 2007-01-01 2007-02-09 2006-12-05 2007-01-09 2006-12-23 2006-12-29 2007-01-01 2007-02-01 2006-12-27 2006-03-11 Drug Code B23 F28 B63 S38 P51 S90 F29 B23 S90 P51 B48 X01 P51