Problem 18.... last time.... pls.... someone help! |
尚未結案
|
tabobomber
一般會員 發表:8 回覆:9 積分:3 註冊:2005-04-07 發送簡訊給我 |
This is my last question.....and really don't know how to do it....pls, one last help.... pls.............. and I'm using Oracle..... 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? 醫院計劃廣大,醫院留意到每間房有六張病床,除了外科有十二張,現在的床數不是很多,少於本身應有的數目。在database裏加一個ward (病房)field,然後顯示出每間房應可以放多少床 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));
|
tabobomber
一般會員 發表:8 回覆:9 積分:3 註冊:2005-04-07 發送簡訊給我 |
I've tried, but still can't make it, either: SELECT T.patientID, T.treatmentCode, MAX(T.treatmentAdministered)
FROM Treatment T
WHERE patientID NOT IN
(SELECT UNIQUE A.patientID
FROM Admission A
WHERE A.dischargeDate IS NULL)
GROUP BY T.patientID, T.treatmentCode; or select treatmentcode, treatmentadministered
from admission a
full join treatment t
where t.treatmentadministered > a.admissiondate or t.treatmentadministered < a.dischargedate
order by a.patientid; pls help
|
yu_blake
一般會員 發表:0 回覆:23 積分:14 註冊:2003-01-16 發送簡訊給我 |
hi .. I'm not sure if u want this.
select distinct WARDNUMBER, count(BEDNUMBER) left_beds from BED where (WARDNUMBER, BEDNUMBER) not in (select WARDNUMBER, BEDNUMBER from admission where dischargeDate is Null) group by WARDNUMBERHope helpful. |
tabobomber
一般會員 發表:8 回覆:9 積分:3 註冊:2005-04-07 發送簡訊給我 |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |