Đ THI TH C HÀNH MÔNỀ Ự
ORACLE
Đ bài: Cho c s d li u Qu n lý b nh vi n sau:ề ơ ở ữ ệ ả ệ ệ
- Create table BENHNHAN
(MaBN char(20) constraint PK_MaBN_BN primary key,
Hoten nvarchar2(50),
Gioitinh varchar2(5),
Diachi nvarchar2(50),
Namsinh Date,
CMND long
);
- Create table KHOA
(Makhoa char(20) constraint PK_Makhoa_KA primary key,
Tenkhoa nvarchar2(50),
MaKV varchar2(5),
);
- Create table BACSI
(MaBS char(20) ,
TenBS nvarchar2(50),
Gioitinh varchar2(5),
Chuyen_mon nvarchar2(50),
Namsinh Date,
CMND long
Makhoa char(20) constranint FK_Makhoa_BS foreign key (Makhoa) references
KHOA(Makhoa)
);
- Create table PHONG
(Maphong char(20) constraint PK_Maphong_PH primary key,
Tenphong nvarchar2(50),
Makhoa char(20) constranint FK_Makhoa_PH foreign key (Makhoa) references
KHOA(Makhoa)
);
- Create table GIUONGBENH
(Ma_giuong char(20) ,
Trang_thai nvarchar2(50),
Maphong char(20) constranint FK_Maphong_GB foreign key (Maphong) references
PHONG(Maphong)
);
Insert into BENHNHAN (‘BN01’, ‘Nguyen A’, ‘Nam’, ’12 Dinh Tien Hoang’, ‘1976’, ‘012345678’)
Insert into BENHNHAN (‘BN02’, ‘Nguyen B’, ‘Nu’, ’Dong Nai’, ‘1986’, ‘0123455678’)
Insert into BENHNHAN (‘BN03’, ‘Nguyen C’, ‘Nam’, ’Binh Duong’, ‘1978’, ‘019845678’)
Insert into BENHNHAN (‘BN04’, ‘Nguyen D’, ‘Nu’, ’Nha Trang’, ‘1956’, ‘098735678’)
Insert into BENHNHAN (‘BN05’, ‘Nguyen E’, ‘Nam’, ’Vung Tau’, ‘1976’, ‘04387678’)
Insert into KHOA (‘KH01’, ‘Hoi suc cap cuu’, ‘03’)
Insert into KHOA (‘KH02’, ‘Cap cuu’, ‘05’)
Insert into KHOA (‘KH03’, ‘Rang Ham Mat’, ‘09’)
Insert into KHOA (‘KH04’, ‘Phau Thuat’, ‘01’)
Insert into BACSI (‘BS01’, ‘Binh Thuan’, ‘Nam’, ’Phau thuat’, ‘1976’, ‘013245678’,’KH01’)
Insert into BACSI (‘BS02’, ‘Nhu Ngoc’, ‘Nu’, ’Chinh hinh’, ‘1985’, ‘08763278’,’KH01’)
Insert into BACSI (‘BS03’, ‘Dinh Ton’, ‘Nam’, ’Rang ham mat’, ‘1955’, ‘03762178’,’KH03’)
Insert into BACSI (‘BS04’, ‘Nguyet’, ‘Nu’, ’Phau thuat’, ‘1980’, ‘0134365678’,’KH02’)
Insert into BACSI (‘BS05’, ‘Duc’, ‘Nam’, ’Phau thuat’, ‘1987’, ‘0132324218’,’KH04’)
Insert into PHONG (‘PCC1’, ‘Phau thuat’, ’KH02’)
Insert into PHONG (‘PCC2’, ‘Hoi suc 01’, ’KH01’)
Insert into PHONG (‘PCC3’, ‘Phau thuat 02’, ’KH04’)
Insert into PHONG (‘PCC4’, ‘Benh 01’, ’KH03’)
Insert into PHONG (‘PCC5’, ‘Benh 02’, ’KH01’)
Insert into PHONG (‘PCC6’, ‘Benh 03’, ’KH02’)
Insert into GIUONGBENH (‘m1110’, ‘ON’, ‘PCC1’, ’BN01’)
Insert into GIUONGBENH (‘m1210’, ‘OFF’, ‘PCC2’, ’-’)
Insert into GIUONGBENH (‘m1510’, ‘ON’, ‘PCC3’, ’BN02’)
Insert into GIUONGBENH (‘m1320’, ‘OFF’, ‘PCC4’, ’BN03’)
Insert into GIUONGBENH (‘m1650’, ‘ON’, ‘PCC5’, ’BN04’)
Insert into GIUONGBENH (‘m3110’, ‘ON’, ‘PCC6’, ’BN05’)
Insert into GIUONGBENH (‘m1350’, ‘ON’, ‘PCC2’, ’BN02’)
1) T o c s d li u d a vào đ bài (1d)ạ ơ ở ữ ệ ự ề
2) T o View cho bi t danh sách các b nh nhân n m các phòng c p c u (bao g mạ ế ệ ằ ở ấ ứ ồ
PCC1, PCC2, PCC3, PCC4). (3d)
3) Vi t Function cho bi t s l ng bác sĩ c a khoa có nhi u b nh nhân n m vi n nh t.ế ế ố ượ ủ ề ệ ằ ệ ấ
(3d)
4) Vi t Frocedure cho bi t danh sách các bác sĩ (MaBS, TenBS) trên 40 tu i c a khoa Xế ế ổ ủ
(v i X là tham s nh p vào t bàn phím). (3d)ớ ố ậ ừ
Đây là đ thi mình t o l i t nh ng d li u ghi l i trong v , s có sai sót nh ng c b n 4 đề ạ ạ ừ ữ ữ ệ ạ ở ẽ ư ơ ả ề
thi th c hành đ u t ng t nh trên. M i ng i có g ng h c hi u d ng bài và thi t t nhé.ự ề ươ ự ư ọ ườ ằ ọ ể ạ ố