data base sql

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

tables

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

drop table drug_supplier cascade constraints\\ drop table doctor cascade constraints\\ drop table patient cascade constraints\\ drop table drug cascade constraints\\ drop table drug_AmountStatus cascade constraints\\

create table drug_supplier(supplier_id number(5) primary key,supplier_name varchar2(15),address varchar2(15))\\ insert into drug_supplier values(201,'ali','Amman')\\ insert into drug_supplier values(202,'omar','Zarqa')\\ insert into drug_supplier values(203,'hend','Irbid')\\ insert into drug_supplier values(204,'bana','Amman')\\ insert into drug_supplier values(205,'zaid',null)\\ ----------------------------------------------------------------------------------------------------------------------- create table doctor(id number(5) primary key,name varchar2(15),specialty varchar2(25))\\ insert into doctor values(121,'said','dermatologist')\\ insert into doctor values(122,'reem','internist')\\ insert into doctor values(123,'ahmad','surgeon')\\ insert into doctor values(124,'lina','internist')\\ insert into doctor values(125,'saleem','dermatologist')\\ ----------------------------------------------------------------------------------------------------------------------- create table patient(patient_id number(5) primary key,first_name varchar2(13),last_name varchar2(13), mobile varchar2(10),bdate date, d_id number(5) references doctor(id))\\ insert into patient values(1,'asad','ahmad','0713333331','1-feb-1990',121)\\ insert into patient values(2,'areej','wajdi','0714444442','3-oct-93',122)\\ insert into patient values(3,'barra','saleem','0715566878','6-jan-96',122)\\ insert into patient values(4,'fadi','fadi','0716671888','7-jan-90',122)\\ insert into patient values(5,'fars','moneer','0711111119','11-jan-90',123)\\ insert into patient values(6,'lama','hani','0712222225','3-jun-99',124)\\ insert into patient values(7,'wael','wael','0713333912','7-dec-95',122)\\ ---------------------------------------------------------------------------------------------------------------------- create table drug(d_name varchar2(10) primary key,supplier_id number(5) references drug_supplier(supplier_id), amount number(3), price number(3))\\ insert into drug values('drug1',201,50,30)\\ insert into drug values('drug2',201,60,15)\\ insert into drug values('drug3',203,70,10)\\ insert into drug values('drug4',204,80,20)\\ insert into drug values('drug5',204,10,25)\\ insert into drug values('drug6',204,70,30)\\ ------------------------------------------------------------------------------------------------------------------- create table drug_AmountStatus(status varchar2(30) primary key,lower_amount number(3) , upper_amount number(3))\\ insert into drug_AmountStatus values('not available',0,0)\\ insert into drug_AmountStatus values('very few',1,10)\\ insert into drug_AmountStatus values('available',11,40)\\ insert into drug_AmountStatus values('abundant',41,150)\\

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1. Create a view for the patient table that contains the patient number, first name, D_ID for all patients where their doctor id is 122. Name the columns (Patient Number, Name, Doctor Id). 2. Insert a new patient with id = 80 to the view with D_ID = 122. Choose values for the other columns. 3. Insert a new patient with id = 90 to the view with D_ID = 123. Choose values for the other columns. 4. Recreate the view with "WITH CHECK OPTION. 5. Repeat (3) with Patient id = 100. Justify your results. 6. Recreate the view with "WITH READ ONLY 7. Repeat (3) with Patient id = 110. Justify your results. 8. Create a view that contains Doctor name and number of patients for each doctor. 9. Create index for patient name in patient table