Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Table: Ambulance
- CREATE TABLE Ambulance
- (
- Ambulance_Id integer NOT NULL,
- Plaque_No varchar2(10) NOT NULL,
- CONSTRAINT Ambulance_pk PRIMARY KEY (Ambulance_Id)
- );
- insert into Ambulance(Ambulance_Id, Plaque_No) values(1, '13AY755');
- insert into Ambulance(Ambulance_Id, Plaque_No) values(2, '06SAD23');
- insert into Ambulance(Ambulance_Id, Plaque_No) values(3, '06AY645');
- select * from Ambulance;
- -- Table: Appointment
- CREATE TABLE Appointment
- (
- Appointment_id integer NOT NULL,
- Department_name varchar2(50) NOT NULL,
- Doctor_Identify integer NOT NULL,
- Doctor_Gender varchar2(1) NOT NULL,
- Patient_number integer NOT NULL,
- Date_of_birth date NOT NULL,
- date_of_appointment date NOT NULL,
- Patient_complaint varchar(15) NOT NULL,
- CONSTRAINT Appointment_pk PRIMARY KEY (Appointment_id)
- );
- insert into Appointment(Appointment_id, Department_name, Doctor_Identify, Doctor_Gender, Patient_number,Date_of_birth, date_of_appointment,Patient_complaint) values(1, 'otorhinolaryngology', 123456, 'M', 123123, DATE '1955-01-03', DATE '2019-05-07', 'Ear' );
- insert into Appointment(Appointment_id, Department_name, Doctor_Identify, Doctor_Gender, Patient_number,Date_of_birth, date_of_appointment,Patient_complaint) values(2, 'dietitian', 123426, 'W', 112123, DATE '1979-02-05', DATE '2019-12-06', 'Over-weight');
- insert into Appointment(Appointment_id, Department_name, Doctor_Identify, Doctor_Gender, Patient_number,Date_of_birth, date_of_appointment,Patient_complaint) values(3, 'otorhinolaryngology', 123154, 'M', 122312, DATE '1965-12-06', DATE '2019-12-10', 'Nose' );
- select * from Appointment;
- --It will be show only man if I change m it will be show women
- select * from Appointment where Doctor_Gender = 'M';
- -- Table: Hospital
- CREATE TABLE Hospital
- (
- Hospital_Id integer NOT NULL,
- eName varchar2(150) NOT NULL,
- Address varchar2(200) NOT NULL,
- Phone integer NOT NULL,
- Fax integer NOT NULL,
- Email varchar2(100) NOT NULL,
- Standart_room_number integer NOT NULL,
- Private_room_number integer NOT NULL,
- Ambulance_Ambulance_Id integer NULL,
- Room_info_id integer NULL,
- Personel_Info_Personal_Id integer NULL,
- Patient_info_id integer NULL,
- Appointment_Appointment_id integer NULL,
- CONSTRAINT Hospital_pk PRIMARY KEY (Hospital_Id)
- );
- insert into Hospital (Hospital_Id, eName, Address, Phone, Fax, Email, Standart_room_number, Private_room_number) values (1, 'Gazi Hospital', 'xxxAddresxxx', 123123123, 123123, 'sdadada@gmail.com', 60,40);
- insert into Hospital (Hospital_Id, eName, Address, Phone, Fax, Email, Standart_room_number, Private_room_number) values (2, 'Hacettepe Hospital', 'xxxAddressxx', 123123123, 112123, 'sdadada@gmail.com', 70,50);
- insert into Hospital (Hospital_Id, eName, Address, Phone, Fax, Email, Standart_room_number, Private_room_number) values (3, 'Ankara university hospital','xxxAddressxx', 123123123, 122313, 'sdadada@gmail.com', 50,30);
- select * from Hospital;
- -- Table: Information_about_patient
- CREATE TABLE Information_about_patient
- (
- Patient_Id integer NOT NULL,
- Name_Surname varchar2(100) NOT NULL,
- Gender varchar2(15) NOT NULL,
- Hospital_Hospital_Id integer NULL,
- Ambulance_Ambulance_Id integer NULL,
- movement_information_Id integer NULL,
- Room_information_Id integer NULL,
- CONSTRAINT Information_about_patient_pk PRIMARY KEY (Patient_Id)
- );
- insert into Information_about_patient (Patient_Id, Name_Surname, Gender) values(1, 'Clark', 'Man');
- insert into Information_about_patient (Patient_Id, Name_Surname, Gender) values(2, 'Emma', 'Women');
- insert into Information_about_patient (Patient_Id, Name_Surname, Gender) values(3, 'Emily', 'Women');
- select * from Information_about_patient where Gender = 'Women';
- -- Table: Patient_movement_information
- CREATE TABLE Patient_movement_information
- (
- Patient_move_info_Id integer NOT NULL,
- Made_operation varchar2(100) NOT NULL,
- Price integer NOT NULL,
- CONSTRAINT Patient_movement_informatio_pk PRIMARY KEY (Patient_move_info_Id)
- );
- insert into Patient_movement_information(Patient_move_info_Id, Made_operation, Price) values(1, 'Test', 500);
- insert into Patient_movement_information(Patient_move_info_Id, Made_operation, Price) values(2, 'Surgery', 1000);
- insert into Patient_movement_information(Patient_move_info_Id, Made_operation, Price) values(3, 'Take blood', 300);
- select * from Patient_movement_information;
- --when you want see max value from patient you have to use select max
- Select max(price) from Patient_movement_information;
- --when you want see mix value from patient you have to use select min
- Select min(price) from Patient_movement_information;
- -- Table: Personel_Information
- CREATE TABLE Personel_Information
- (
- Personal_Id integer NOT NULL,
- Idenity_sumber integer NOT NULL,
- Name_surname varchar2(150) NOT NULL,
- Profession varchar2(150) NOT NULL,
- Start_date_to_job date NOT NULL,
- info_Patient_Id integer NULL,
- CONSTRAINT Personel_Information_pk PRIMARY KEY (Personal_Id)
- );
- insert into Personel_Information(Personal_Id, Idenity_sumber, Name_surname, Profession, Start_date_to_job) values(1, 123456, 'Clark surname', 'Ear', DATE '2018-05-10');
- insert into Personel_Information(Personal_Id, Idenity_sumber, Name_surname, Profession, Start_date_to_job) values(2, 234562, 'Michal qwrewrx', 'Surgeon', DATE '2018-10-05');
- insert into Personel_Information(Personal_Id, Idenity_sumber, Name_surname, Profession, Start_date_to_job) values(3, 345614, 'Emma ewrrwx', 'Throat', DATE '2018-10-05');
- select * from Personel_Information;
- -- Table: Room_Information
- CREATE TABLE Room_Information
- (
- Room_information_Id integer NOT NULL,
- Bed_number integer NOT NULL,
- Private_room varchar2(10) NOT NULL,
- Standart_room varchar2(10) NOT NULL,
- Hospital_name varchar2(150) NOT NULL,
- CONSTRAINT Room_Information_pk PRIMARY KEY (Room_information_Id)
- );
- insert into Room_Information(Room_information_Id, Bed_number, Private_room, Standart_room, Hospital_name) values(1, 2, 'No', 'Yes', 'Gazi Hospital');
- insert into Room_Information(Room_information_Id, Bed_number, Private_room, Standart_room, Hospital_name) values(2, 1, 'Yes', 'No', 'Hacettepe Hospital');
- insert into Room_Information(Room_information_Id, Bed_number, Private_room, Standart_room, Hospital_name) values(3, 2, 'No', 'Yes', 'Ankara university hospital');
- select * from Room_Information;
- -- foreign keys
- -- Reference: Hospital_Ambulance (table: Hospital)
- ALTER TABLE Hospital ADD CONSTRAINT Hospital_Ambulance
- FOREIGN KEY (Ambulance_Ambulance_Id)
- REFERENCES Ambulance (Ambulance_Id);
- -- Reference: Hospital_Appointment (table: Hospital)
- ALTER TABLE Hospital ADD CONSTRAINT Hospital_Appointment
- FOREIGN KEY (Appointment_Appointment_id)
- REFERENCES Appointment (Appointment_id);
- -- Reference: Hospital_Patient (table: Hospital)
- ALTER TABLE Hospital ADD CONSTRAINT Hospital_Patient
- FOREIGN KEY (Patient_info_id)
- REFERENCES Patient_movement_information (Patient_move_info_Id);
- -- Reference: Hospital_Personel (table: Hospital)
- ALTER TABLE Hospital ADD CONSTRAINT Hospital_Personel
- FOREIGN KEY (Personel_Info_Personal_Id)
- REFERENCES Personel_Information (Personal_Id);
- -- Reference: Hospital_Room (table: Hospital)
- ALTER TABLE Hospital ADD CONSTRAINT Hospital_Room
- FOREIGN KEY (Room_info_id)
- REFERENCES Room_Information (Room_information_Id);
- -- Reference: Info_patient_Ambulance (table: Information_about_patient)
- ALTER TABLE Information_about_patient ADD CONSTRAINT Info_patient_Ambulance
- FOREIGN KEY (Ambulance_Ambulance_Id)
- REFERENCES Ambulance (Ambulance_Id);
- -- Reference: Info_patient_Hospital (table: Information_about_patient)
- ALTER TABLE Information_about_patient ADD CONSTRAINT Info_patient_Hospital
- FOREIGN KEY (Hospital_Hospital_Id)
- REFERENCES Hospital (Hospital_Id);
- -- Reference: Personel_Information (table: Personel_Information)
- ALTER TABLE Personel_Information ADD CONSTRAINT Personel_Information
- FOREIGN KEY (info_Patient_Id)
- REFERENCES Information_about_patient (Patient_Id);
- -- Reference: patient_Room_info (table: Information_about_patient)
- ALTER TABLE Information_about_patient ADD CONSTRAINT patient_Room_info
- FOREIGN KEY (Room_information_Id)
- REFERENCES Room_Information (Room_information_Id);
- -- Reference: patient_move_info (table: Information_about_patient)
- ALTER TABLE Information_about_patient ADD CONSTRAINT patient_move_info
- FOREIGN KEY (movement_information_Id)
- REFERENCES Patient_movement_information (Patient_move_info_Id);
Add Comment
Please, Sign In to add comment