SQL

Lab Assignment # 10 Lab Exercises: 1. Create a user; the username is your yourName_university ID and the password is my123. 2. Change the password of the new user to abcPass. 3. Create role named clerk. 4. Give the clerk the ability to login, create table, insert any patient, update name of any doctor. 5. Grant clerk role to your user. 6. Grant your user the ability to delete any patient and the ability to grant this privilege to any user. 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'.'071111111911-ian-90'.123); 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)