高分紧急求助!对于熟悉oracle的朋友来说应该是小菜一碟啦！！

[zznj1123]

Scenario
The Leith Emeare Geriatric Homes provides serviced apartments for elderly citizens at its mulit-story complex in Central Tasmania. Leith Emeare also operates the Knott Wheel Clinic and In-Care Unit where its residents can receive high quality health care without having to leave their building for treatment. Knott Wheel has both a clinic where residents can see a clinician (either a Doctor or a Nurse) and receive treatment, and a In-Care Unit for residence who need hospital-like care. The Knott Wheel Clinic and In-Care Unit has a number of doctors and nurses who can provide treatment both at the Clinic and in the In-Care Unit.
The In-Care Unit has four specialist areas, each located on a different floor of the centre. Each specialist area has several well-equipped rooms.
The Knott Wheel Clinic and In-Care Unit has had an Oracle database developed using some test data, but the developer has left leaving only minimal documentation. You have been asked to take over the database, and to provide facilities so that user queries can be answered.
Task Description
The Knott Wheel Clinic and In-Care Unit regrets that the previous developer failed to complete the database implementation as originally planned, and even more, the failure to document what little work was actually carried out. The only documentation that exists is the following E-R diagram, plus the table definitions and test data contained in the SQL script file Ass2Setup.sql.

The Knott Wheel Clinic and In-Care Unit regards it as essential that all further work done on the database be documented in a formal business report prepared in accordance with normal professional business practice.
The report is to have a one page Introduction that identifies what has been achieved, and what (if anything) remains to be done. Each specific requirement (or question) is to be documented on a fresh page, and should have the question number as the major heading, with sub-headings for (1) the SQL script, (2) the query results, and (3) comments which describe what your script does.
Each question is to be constructed through an SQL script with the following typical structure (this is in fact a model answer to requirement 1):

The file question.sql (and also Ass2Setup.sql) can be downloaded from the Assessment page on the unit’s MyLO site. Both files should be stored on the same drive, and in the same directory, as should your answer scripts to each question. It (question.sql) must be run at the start of each question answer (and provided with the appropriate question number) in order to identify you as the user, plus the date/time and location of your test run, plus identify which requirement you are attempting to meet. Your output for each question should be spooled to a file (here assumed to be on ‘D: drive’, but you can use whatever drive you wish). The content of these spool files should be listed as part of your report. The execution of the question.sql script results in the following typical introduction to each spool file:

The company is aware that some of the requests might prove difficult, and accordingly asks that if you encounter a problem, that you provide scripts that are partial solutions, on the basis that something is better than nothing. It does ask, however, that your report’s Introduction highlight any such problems.

---------------------------------------未完待续------------------------------------------

[zznj1123]

Requirements：作业题目
The following specific requirements have been identified:
1. Use the file Ass2Setup.sql (available through MyLO) to set up the database and then to display the content of each database table created.
2. Write an SQL script(s) to determine:
a) the cheapest treatment
b) the most expensive treatment
c) the charge for a vaccination
d) the total charge for each resident with visits to the Knott Wheel Clinic recorded in the database.
3. Write an SQL script to display, in name order in the form of: ‘Smithers, Waylon’, a list of:
a) all residents who have never been admitted to the In-Care Unit
b) all residents who are currently in the In-Care Unit
4. Write a script so when new residents are about to be admitted, the In-Care Unit is provided with a list showing how many rooms are currently unoccupied on each floor.
5. List the resident ID and name of any resident who was in the In-Care Unit on the most recent Christmas Day (25th December).
6. For each resident discharged from the In-Care Unit, how many days were they in the In-Care Unit on each occasion?
7. How many of the residents in the database are male, and how many female?
8. To satisfy the Department of Elderly Care’s audit, the Knott Wheel Clinic and In-Care Unit is required to identify for all clinicians performing treatments, their first recorded treatment of any resident.
9. List the resident ID, procedure code and date of visit of all residents who have received a treatment at the Knott Wheel Clinic, but not while they were in the In-Care Unit.
10. The current database design mixes up current and previous admissions to the In-Care Unit in the same table. If required, make changes and additions to tables, columns, and constraints so that the In-Care Unit can archive the record of an admission once the resident has been discharged from the In-Care Unit, into a separate table. (You do not need to change scripts already developed to work with this restructured database – that is, run this script last)

-------------------------------未完待续-------------------------------------

[zznj1123]

附件内容：
1、上述提到的Ass2Setup_sql.txt这个文件的内容是：
DROP TABLE visit;
DROP TABLE admission;
DROP TABLE resident;
DROP TABLE floor;
DROP TABLE room;
DROP TABLE clinician;
DROP TABLE treatment;

CREATE TABLE resident(
residentID char(4) primary key,
Surname varchar(15) not null,
GivenNames varchar(15),
apartment varchar(10),
phone varchar(15),
dateOfBirth date,
gender char(1));

INSERT INTO resident VALUES(1234,'Collins','Fred','310','[phone]','27-MAR-1938','M');
INSERT INTO resident VALUES(1248,'Wilson','Mary','309','[phone]','17-MAY-1968','F');
INSERT INTO resident VALUES(1291,'Collins','Mabel','310','[phone]','2-JAN-1939','F');
INSERT INTO resident VALUES(2386,'St.John','John','309',null,'4-SEP-1921','M');
INSERT INTO resident VALUES(1288,'Steer','Dino','301','[phone]','10-FEB-1957','M');
INSERT INTO resident VALUES(1491,'Cable','Kate','304','[phone]','2-JAN-1939','F');
INSERT INTO resident VALUES(1378,'Farthing','Penny','301','0418 123456','28-FEB-1948','F');
INSERT INTO resident VALUES(3386,'Staak','John','409',null,'4-OCT-1923','M');
INSERT INTO resident VALUES(3288,'Peters','Peter','401','[phone]','10-MAR-1946','M');
INSERT INTO resident VALUES(3491,'Chen','Chen','404','[phone]','2-FEB-1932','F');
INSERT INTO resident VALUES(3378,'Chan','Jackie','402','0410 123458','28-MAR-1941','M');
INSERT INTO resident VALUES(4386,'Downs','Heather','402','0412 123457','4-NOV-1924','F');
INSERT INTO resident VALUES(4288,'Rasberry','Jenny','408','[phone]','10-APR-1945','F');
INSERT INTO resident VALUES(4491,'Kable','Cate','407','[phone]','2-MAY-1934','F');
INSERT INTO resident VALUES(4378,'Fathers','Don','501','0438 123356','28-APR-1946','M');
INSERT INTO resident VALUES(5386,'Maskell','Denise','509',null,'4-DEC-1929','F');
INSERT INTO resident VALUES(5288,'Hernane','Jessie','501','[phone]','10-JUN-1955','F');
INSERT INTO resident VALUES(5491,'Council','Major','504','[phone]','2-JUL-1932','M');
INSERT INTO resident VALUES(5378,'Rigley','Karen','501','0468 123466','28-AUG-1943','F');
INSERT INTO resident VALUES(6386,'State','Kenny','509',null,'4-OCT-1924','M');
INSERT INTO resident VALUES(6288,'Steel','Dianne','505','[phone]','10-AUG-1926','F');
INSERT INTO resident VALUES(6491,'Richard','Richardo','404','[phone]','2-OCT-1924','M');
INSERT INTO resident VALUES(6378,'Zhou','Huanito','301','0413 333456','28-DEC-1923','F');
INSERT INTO resident VALUES(2378,'Brown','Bill','403','[phone]','24-AUG-1941','M');
INSERT INTO resident VALUES(1387,'Evans','John','302','0417 2341 6513','4-SEP-1945','M');
INSERT INTO resident VALUES(2498,'Brown','Roberta','402','[phone]','24-JUN-1938','F');
INSERT INTO resident VALUES(2231,'Green','Pat','502','[phone]','14-MAY-1955','F');
INSERT INTO resident VALUES(2368,'McGillicuddy','Sandy','503','[phone]','4-SEP-1936','M');

CREATE TABLE floor(
floorNumber char(2) primary key,
floorName varchar(20));

INSERT INTO floor VALUES('L6','General');
INSERT INTO floor VALUES('L7','Psychiatric');
INSERT INTO floor VALUES('L8','Surgical');
INSERT INTO floor VALUES('L9','Intensive Care');

CREATE TABLE room(
floorNumber char(2),
roomNumber number,
constraint room_pk primary key (floorNumber, roomNumber));

INSERT INTO room VALUES('L6',1);
INSERT INTO room VALUES('L6',2);
INSERT INTO room VALUES('L6',3);
INSERT INTO room VALUES('L6',4);
INSERT INTO room VALUES('L7',1);
INSERT INTO room VALUES('L7',2);
INSERT INTO room VALUES('L7',3);
INSERT INTO room VALUES('L7',4);
INSERT INTO room VALUES('L8',1);
INSERT INTO room VALUES('L8',2);
INSERT INTO room VALUES('L8',3);
INSERT INTO room VALUES('L8',4);
INSERT INTO room VALUES('L9',1);
INSERT INTO room VALUES('L9',2);
INSERT INTO room VALUES('L9',3);
INSERT INTO room VALUES('L9',4);
INSERT INTO room VALUES('L9',5);
INSERT INTO room VALUES('L9',6);
INSERT INTO room VALUES('L9',7);
INSERT INTO room VALUES('L9',8);

CREATE TABLE admission(
residentID char(4),
admissionDate date,
dischargeDate date,
floorNumber char(2),
roomNumber number,
constraint admission_pk primary key (residentID, admissionDate),
constraint admission_room_fk foreign key (floorNumber, roomNumber)
references room (floorNumber, roomNumber),
constraint residentID_fk foreign key (residentID)
references resident (residentID) );

INSERT INTO admission VALUES('3386','1-JAN-09','10-JAN-09','L6',2);
INSERT INTO admission VALUES('4288','2-FEB-09','28-FEB-09','L6',4);
INSERT INTO admission VALUES('5491','3-MAR-09','5-MAR-09','L6',2);
INSERT INTO admission VALUES('6288','4-APR-09','17-APR-09','L7',1);
INSERT INTO admission VALUES('2231','5-MAY-09','6-MAY-09','L7',3);
INSERT INTO admission VALUES('2231','6-JUN-09','30-JUN-09','L7',2);
INSERT INTO admission VALUES('6288','7-JUL-09','19-JUL-09','L8',1);
INSERT INTO admission VALUES('5491','8-AUG-09','10-AUG-09','L8',4);
INSERT INTO admission VALUES('4288','9-SEP-09','25-SEP-09','L8',4);
INSERT INTO admission VALUES('3386','10-OCT-09','26-DEC-09','L8',1);
INSERT INTO admission VALUES('3386','11-JAN-10','29-JAN-10','L9',1);
INSERT INTO admission VALUES('1234','24-JAN-09','29-JAN-09','L6',3);
INSERT INTO admission VALUES('1234','21-SEP-09','29-SEP-09','L6',4);
INSERT INTO admission VALUES('1234','24-JAN-10','28-JAN-10','L6',3);
INSERT INTO admission VALUES('1491','24-JAN-09','31-JAN-09','L7',2);
INSERT INTO admission VALUES('2378','22-SEP-09','27-SEP-09','L7',1);
INSERT INTO admission VALUES('1491','24-JAN-10','03-FEB-10','L7',4);
INSERT INTO admission VALUES('2386','22-DEC-09','25-DEC-09','L8',3);
INSERT INTO admission VALUES('2378','25-DEC-09','28-DEC-09','L8',2);
INSERT INTO admission VALUES('1378','21-DEC-09',null,'L9',1);
INSERT INTO admission VALUES('1491','24-FEB-10','24-FEB-10','L9',4);
INSERT INTO admission VALUES('1234','1-MAR-10',null,'L9',3);
INSERT INTO admission VALUES('2378','22-MAR-10',null,'L9',6);
INSERT INTO admission VALUES('3386','31-MAR-10',null,'L9',2);
INSERT INTO admission VALUES('4288','1-APR-10',null,'L9',7);
INSERT INTO admission VALUES('5491','10-APR-10',null,'L9',4);
INSERT INTO admission VALUES('6288','18-APR-10',null,'L9',5);
INSERT INTO admission VALUES('1288','18-APR-10',null,'L7',1);

---------------------------------未完待续---------------------------------

[zznj1123]

CREATE TABLE clinician(
registerNumber char(6) primary key,
clinicianName varchar(20),
profession char(1),
specialisation char(1));

INSERT INTO clinician VALUES('123987','Kildare','D','G');
INSERT INTO clinician VALUES('123123','Jeckyll','D','P');
INSERT INTO clinician VALUES('124187','Hyde','D','S');
INSERT INTO clinician VALUES('123417','Spock','D','I');
INSERT INTO clinician VALUES('233988','Wonderful','N','G');
INSERT INTO clinician VALUES('233124','Lovely','N','P');
INSERT INTO clinician VALUES('234188','Quickly','N','S');
INSERT INTO clinician VALUES('233418','Softly','N','I');


CREATE TABLE treatment(
code char(3) primary key,
treatmentName varchar(25),
charge number(6,2));

INSERT INTO treatment VALUES('IJ1','Vaccination - Flu',25.00);
INSERT INTO treatment VALUES('IJ2','Vaccination - Measles',35.00);
INSERT INTO treatment VALUES('VA1','Vitamin A',35.00);
INSERT INTO treatment VALUES('PC1','Plaster cast - Leg',95.00);
INSERT INTO treatment VALUES('PC2','Plaster cast - Arm',75.00);
INSERT INTO treatment VALUES('ET1','Eye test',45.00);
INSERT INTO treatment VALUES('BT1','Blood test',35.00);
INSERT INTO treatment VALUES('BP1','Blood pressure',10.00);

CREATE TABLE visit(
visitCode char(3),
residentID char(4),
treatmentAdministered date,
clinicianNumber char(6),
constraint visit_pk primary key (visitCode, residentID, treatmentAdministered),
constraint visit_resident_fk foreign key (residentID) references resident(residentID),
constraint treatment_clinician_fk foreign key (clinicianNumber) references clinician(registerNumber),
constraint treatment_procedure_fk foreign key (visitCode) references treatment(code));

INSERT INTO visit VALUES('BT1','1234','22-SEP-09','123123');
INSERT INTO visit VALUES('BP1','1234','22-SEP-09','123123');
INSERT INTO visit VALUES('BT1','1234','23-SEP-09','123123');
INSERT INTO visit VALUES('BP1','1234','23-SEP-09','233124');
INSERT INTO visit VALUES('BT1','1234','24-SEP-09','123123');
INSERT INTO visit VALUES('BP1','1234','24-SEP-09','123123');
INSERT INTO visit VALUES('BT1','1234','26-SEP-09','123123');
INSERT INTO visit VALUES('BP1','1234','26-SEP-09','123123');
INSERT INTO visit VALUES('BT1','1234','28-SEP-09','233124');
INSERT INTO visit VALUES('BP1','1234','28-SEP-09','123123');
INSERT INTO visit VALUES('BT1','1234','20-OCT-09','123123');
INSERT INTO visit VALUES('BP1','1234','22-OCT-09','123123');
INSERT INTO visit VALUES('BP1','1491','23-FEB-10','124187');
INSERT INTO visit VALUES('BP1','1491','26-FEB-10','124187');
INSERT INTO visit VALUES('IJ1','3386','1-JAN-09','233124');
INSERT INTO visit VALUES('IJ2','4288','1-FEB-09','233124');
INSERT INTO visit VALUES('VA1','5491','3-MAR-09','233124');
INSERT INTO visit VALUES('PC1','6288','4-APR-09','233418');
INSERT INTO visit VALUES('PC2','2231','5-MAY-09','233418');
INSERT INTO visit VALUES('ET1','2231','5-JUN-09','123417');
INSERT INTO visit VALUES('BP1','6288','9-JUL-09','123417');
INSERT INTO visit VALUES('BT1','5491','10-AUG-09','123417');
INSERT INTO visit VALUES('BT1','4288','5-SEP-09','233124');
INSERT INTO visit VALUES('BP1','3386','10-OCT-09','233988');
INSERT INTO visit VALUES('ET1','3386','10-OCT-09','233988');
INSERT INTO visit VALUES('PC2','1234','23-JAN-09','123417');
INSERT INTO visit VALUES('PC1','1234','2-SEP-09','233418');
INSERT INTO visit VALUES('VA1','1234','25-JAN-10','233124');
INSERT INTO visit VALUES('IJ1','1491','26-JAN-09','123123');
INSERT INTO visit VALUES('IJ2','2378','22-SEP-09','233418');
INSERT INTO visit VALUES('VA1','1491','25-JAN-10','233418');
INSERT INTO visit VALUES('VA1','2386','25-DEC-09','233418');
INSERT INTO visit VALUES('PC2','2378','26-DEC-09','123123');
INSERT INTO visit VALUES('PC1','1378','2-DEC-09','233418');
INSERT INTO visit VALUES('IJ1','1491','23-FEB-10','233124');
INSERT INTO visit VALUES('IJ2','1234','1-MAR-10','233124');
INSERT INTO visit VALUES('ET1','2378','22-MAR-10','233124');
INSERT INTO visit VALUES('VA1','3386','30-MAR-10','233124');
INSERT INTO visit VALUES('VA1','4288','2-APR-10','233124');
INSERT INTO visit VALUES('IJ1','5491','11-APR-10','233418');
INSERT INTO visit VALUES('IJ1','6288','19-APR-10','234188');
INSERT INTO visit VALUES('IJ2','1288','18-APR-10','233124');
INSERT INTO visit VALUES('IJ1','1288','18-APR-10','233124');

1、上述提到的Question_sql.txt这个文件的内容是：

rem -----------------------------------------------------------------------
rem Filename: Question.sql
rem Purpose: Reports information about your current database context
rem including the question number being answered
rem -----------------------------------------------------------------------

set termout off
set pause off
set head on
column user format A15
column global_name heading 'SERVER' format A30
column session format A10
set termout on
select user, global_name, USERENV('SESSIONID') session_id from global_name;
select USERENV('TERMINAL') terminal,
TO_CHAR(sysdate, 'DD-MON-YYYY') Used_on,
TO_CHAR(sysdate, 'HH:MI A.M.') Used_at
from global_name;
set head off
clear columns
set termout on
set verify off
select 'Question: ' || &1 from dual;
set head on
set verify on





--------------------------------------------------紧急求助！谢谢帮助！呵呵！-------------------------------

[碧水幽幽泉]

[Quote=引用 8 楼 dawugui 的回复:]
只有帮顶了.
[/Quote]
E文啊！难搞！

[zznj11232]

我是楼主，
对了，答案的格式是如下所示，分为三个部分：SQL Script，Results和Comments,comments就是题目内容。
答案示例如下所示：
1、SQL Script
SPOOL D:\documents\bsa206\assignment1\Q3.txt
@@Question 3
SET LINESIZE 250
SET PAGESIZE 150
PROMPT Members Who Didn't Have a Meal or Drink in February 2006
SELECT DISTINCT MEMBERID AS ID, LASTNAME||', '||FIRSTNAME AS NAME
FROM MEMBER
WHERE MEMBER.MEMBERID NOT IN(SELECT MEMBERID FROM DOCKET WHERE DOCKETDATE LIKE '%/FEB/06')
ORDER BY NAME;
SPOOL OFF
2、Results
USER SERVER SESSION_ID
--------------- ------------------------------ ----------
XXXXXXX ISPROD.INFOSYS.UTAS.EDU.AU 336156
TERMINAL USED_ON USED_AT
------------------------------ ----------- --------
SCLS-SLD-12309 17-APR-2006 05:30 PM
Question: 3
Members Who Didn't Have a Meal or Drink in February 2006
ID NAME
---------- ------------------
11 Anatidae, Donald
33 Bentine, Michael
27 Bhaskar, Sanjeev
24 Brooke-Taylor, Tim
2 DeGeneres, Ellen
23 Dench, Judi
6 Fine, Larry
19 French, Dawn
28 Ghir, Kulvinder
17 Hancock, Tony
9 Hardy, Oliver
7 Howard, Curly
36 Jakes, Hatty
18 James, Sydney
38 Keith, Penelope
37 Kendal, Felicity
20 Lumley, Joanna
14 Marx, Chico
12 Marx, Groucho
15 Marx, Gummo
13 Marx, Harpo
32 Milligan, Spike
26 Oddie, Bill
10 Rodentia, Mickey
21 Saunders, Jennifer
31 Secombe, Harry
22 Wanamaker, Zoe
3 Williams, Ester
40 Windsor, Barbara
1 Zhou, Juanita
30 rows selected.
8 REQUIREMENT 3
3、Comments
The specifications supplied required knowledge again of equality joins, and introduced comparison operators (NOT)IN and LIKE, wildcard characters (%). The DISTINCT keyword was required to ensure that only one occurrence of each member was listed and the ORDER BY clause was used to present results in a specified sequence.

[oraclemch]

关注下，全是英文吗？有点难搞啊！

[zznj1123]

很简单的吧 我在SQL server下面都实现了一部分。
但是要把答案格式化成 sql script,results和comment三个部分

[zznj1123]

贴出我在SQL server下面写的部分SQL 语句，希望大家帮忙咯，熟悉点oracle的应该很快就能搞定吧！！！
Requirements
The following specific requirements have been identified:
1. Use the file Ass2Setup.sql (available through MyLO) to set up the database and then to display the content of each database table created.
2. Write an SQL script(s) to determine:
a) the cheapest treatment
select * from (select * from treatment order by charge asc) where rownum=1
b) the most expensive treatment
select * from (select * from treatment order by charge desc) where rownum=1
c) the charge for a vaccination
[color=#000080]select charge from treatment where treatmentName like 'vaccination%'[/color]
d) the total charge for each resident with visits to the Knott Wheel Clinic recorded in the database.
select sum(charge) as totalCharge from
(
select a.visitCode,b.charge from visit a,treatment b where a.visitCode=b.code
) v
3. Write an SQL script to display, in name order in the form of: ‘Smithers, Waylon’, a list of:
a) all residents who have never been admitted to the In-Care Unit
select * from resident where residentID not in(select residentID from admission) order by Surname
b) all residents who are currently in the In-Care Unit
select * from resident where residentID in(select residentID from admission where dischargeDate is null) order by Surname
4. Write a script so when new residents are about to be admitted, the In-Care Unit is provided with a list showing how many rooms are currently unoccupied on each floor.
select floorNumber,count(*) as unoccupiedRoomCount from
(
select a.*,b.roomNumber as bRoomNumber from room a left outer join
(
select floorNumber,roomNumber from admission group by floorNumber,roomNumber
) b on a.floorNumber=b.floorNumber and a.roomNumber=b.roomNumber
)v where bRoomNumber is null group by floorNumber
5. List the resident ID and name of any resident who was in the In-Care Unit on the most recent Christmas Day (25th December).
6. For each resident discharged from the In-Care Unit, how many days were they in the In-Care Unit on each occasion?
select residentID,(dischargeDate-admissionDate)/(24*60*60*1000) from admission where dischargeDate is not null
7. How many of the residents in the database are male, and how many female?
Mail: select count(*) from resident where gender='M'
Femail: select count(*) from resident where gender='F'

8. To satisfy the Department of Elderly Care’s audit, the Knott Wheel Clinic and In-Care Unit is required to identify for all clinicians performing treatments, their first recorded treatment of any resident.
9. List the resident ID, procedure code and date of visit of all residents who have received a treatment at the Knott Wheel Clinic, but not while they were in the In-Care Unit.
10. The current database design mixes up current and previous admissions to the In-Care Unit in the same table. If required, make changes and additions to tables, columns, and constraints so that the In-Care Unit can archive the record of an admission once the resident has been discharged from the In-Care Unit, into a separate table. (You do not need to change scripts already developed to work with this restructured database – that is, run this script last)

[dawugui]

只有帮顶了.

[Leshami]

你回答的很好啊

[Leshami]

楼主回答的很好

[心中的彩虹]

全是英文 只有膜拜了