KINGDOM OF SAUDI ARABIA
Al–lmam Muhammad Ibn Saud Islamic University
Faculty of Computer Scince and Information
IS 320 : Introduction to Database
1stSemester 1432-1433 H
Project Title:
Database for New life Hospital
Section #:
247
Group Members:
Name / IDReem / ---- / 1
Bayan / ---- / 2
Amnah / ---- / 3
Raghad / ---- / 4
Wafa / ---- / 5
T.monera al-shebel
Project Description:
The goal of this project is to design electronic database to facilitate the process of appointment for patient and planning schedule of work for each person work in Hospital to organize the work. We design electronic database for Hospital depends on a large number of persons. These DB contain many tables.
•Each PERSON have the Birthday ,Id and name is composite(F_name,L_name) and Phone number is Multivalue and address, the address is composite(City and name of Street and Zip_code ) and there are two types of person is( EMPLOYEES and PATIONT ). Each PERSON belongs to exactly one of these type.
•Each EMPLOYEES have the Date hired and three type NURS , STAFF and DOCTOR .Each EMPLOYEES belongs to exactly one of these type.
•NURS have Certificate.
•STAFF has Jop class for each staff in hospital.
•DOCTOR have Specialty and Number of Pager.
•PATIONT have Number of file. And there are two types of PATIONT is (RESIDENT and OUT PATENT) .Each PATIONT belongs to exactly one of these type .
- OUT PATENT visit to CLINIC the visit have common date.
- NURS assign to (CARE CENTER and CLINIC(.
•CARE CENTER have name of center and Location.
•CLINIC have name of center and Location.
• BEDROOMS assign to RESIDENT and CARE CENTER .The BEDROOMS have number of room, number of Bed and name of center.
2-Enhanced Entity Relationship diagram (EER):
3- Relational Database Schemas:
4- Schema and Query Implementation:
4.1- Define your database:
Use appropriate naming for all of your tables and attributes. Write SQL DDL statements to create database tables. Primary keys and foreign keys must be defined as appropriate. Define attributes by a data type for each attribute in addition to specifying if it is NULL is permitted.
4.2- Data Entry:
Insert at least 10 rows into each table (unless you have cardinality constraints). The data values should be reasonable.
4.3- Data update/deletion:
-List 5different delete queries related to your tables.[Write the queries description and commands]
-List 5 different update queries related to your tables.[Write the queries description and commands]
4.4- Data Retrieval (Select) Queries:
-List 5 simple selectqueriesrelated to your tables. [Write the queries descriptionand commands]
-List 5 nested queries related to your tables. [Write the queries descriptionand commands]
-List 5 simple retrieval queries using group by, having clause, and aggregation functions related to your tables. [Write the queries descriptionand commands]
4.5- Views:
Create two different views.[Write the views descriptionand commands]
4.1- DB tables creation commands:
List all the DDL statements that are used to create your DB tables.
CREATE TABLE PERSON(
Id CHAR(4) NOT NULL,
F_Name VARCHAR(15) NOT NULL,
L_Name VARCHAR(15) NOT NULL,
B_Day Date NOT NULL,
City VARCHAR(11),
Street_Name VARCHAR(25),
Zip_Code CHAR(5),
PRIMARY KEY(Id));
CREATE TABLE EMPLOYEE(
Id CHAR(4) NOT NULL,
Date_Hired DATE ,
PRIMARY KEY(Id),
FOREIGN KEY (Id) REFERENCES PERSON ON DELETE SET NULL);
CREATE TABLE STAFF(
Id CHAR(4) NOT NULL,
Jop_Class VARCHAR(25),
PRIMARY KEY(Id),
FOREIGN KEY (Id) REFERENCES PERSON ON DELETE SET NULL);
CREATE TABLE DOCTOR(
Id CHAR(4) NOT NULL,
Pager_NumberCHAR(4),
SpecialtyVARCHAR(10),
PRIMARY KEY(Id),
FOREIGN KEY (Id) REFERENCES PERSON ON DELETE SET NULL);
CREATE TABLE NURSE(
Id CHAR(4) NOT NULL,
Certificate VARCHAR(10) NOT NULL,
C_Name VARCHAR(25) NOT NULL,
R_Name VARCHAR(25) NOT NULL,
PRIMARY KEY(Id),
FOREIGN KEY (Id) REFERENCES PERSON ON DELETE SET NULL,
FOREIGN KEY (C_Name) REFERENCES CLINIC ON DELETE SET NULL,
FOREIGN KEY (R_Name) REFERENCES CARE_CENTER ON DELETE SET NULL);
CREATE TABLE CLINIC(
C_Name VARCHAR(25) NOT NULL,
Location CHAR(4),
PRIMARY KEY(C_Name));
CREATE TABLE CARE_CENTER(
R_Name VARCHAR(25) NOT NULL,
Location CHAR(4),
PRIMARY KEY(R_Name));
CREATE TABLE BED_ROOM(
Bed_Num CHAR(4) NOT NULL,
Room_Num CHAR(2) NOT NULL,
R_Name VARCHAR(25) NOT NULL,
PRIMARY KEY(Bed_Num,Room_Num),
FOREIGN KEY (R_Name) REFERENCES CARE_CENTER ON DELETE SET NULL);
CREATE TABLE PATIENT(
Id CHAR(4) NOT NULL,
RId CHAR(4) NOT NULL,
Num_File CHAR(4) NOT NULL,
PRIMARY KEY (Id),
FOREIGN KEY (Id) REFERENCES PERSON ON DELETE SET NULL,
FOREIGN KEY (RId) REFERENCES DOCTOR (Id) ON DELETE SET NULL);
CREATE TABLE RESIDENT(
Id CHAR(4) NOT NULL,
Bed_Num CHAR(4) NOT NULL,
Room_Num CHAR(2) NOT NULL,
PRIMARY KEY (Id),
FOREIGN KEY (Id) REFERENCES PERSON ON DELETE SET NULL,
FOREIGN KEY (Bed_Num) REFERENCES BED_ROOM ON DELETE SET NULL,
FOREIGN KEY (Room_Num) REFERENCES BED_ROOM ON DELETE SET NULL);
CREATE TABLE OUT_PATIENT(
Id CHAR(4) NOT NULL,
DateCome DATE NOT NULL,
C_Name VARCHAR(25) NOT NULL,
PRIMARY KEY (Id),
FOREIGN KEY (Id) REFERENCES PERSON ON DELETE SET NULL,
FOREIGN KEY (C_Name) REFERENCES CLINIC ON DELETE SET NULL);
CREATE TABLE PER_PHONE(
IdCHAR(4)NOT NULL,
per_pho CHAR(7)NOT NULL,
PRIMARY KEY(Id),
FOREIGN KEY (Id) REFERENCES PERSON ON DELETE SET NULL);
4.2- Data insertion commands:
List all the DML statements that are used to insert data into the created DB tables.
INSERT INTO PERSON VALUES('1111','asma','ali','04-05-1977','Alriyadh','khaled_bin_alwaled','22355');
INSERT INTO PERSON VALUES('1122','mona', 'salm','2/6/1986', 'Alriyadh','altahlya','33688');
INSERT INTO PERSON VALUES('1133','rana', 'ali','01/08/1989' ,'Dammam','khryss','99555');
INSERT INTO PERSON VALUES('1144','rasha', 'salha','06/08/1978' ,'Alriyadh','hamzah street','66444');
INSERT INTO PERSON VALUES('1155','nada', 'omar','7/8/1975', 'Alriyadh','aljahedh street','88222');
INSERT INTO PERSON VALUES('1166','omar', 'salem','03/09/1982', 'Jeddah','aishah street','33777');
INSERT INTO PERSON VALUES('1177','ali', 'faisal','05/07/1989', 'Dammam','khaled bin alwaled','22333');
INSERT INTO PERSON VALUES('1188','saud', 'anor','02/05/1988', 'Alriyadh','king fahad street','55888');
INSERT INTO PERSON VALUES('1199','jmal', 'abd-rahaman','13/07/1989', 'Jeddah','aishah street','33777');
INSERT INTO PERSON VALUES('1100','alound', 'ali','04/06/1980' ,'Alriyadh','khaled bin alwaled','22333');
INSERT INTO PERSON VALUES('2211','rechard','deming','04/06/1980','Jeddah','king fahad street','33455') ;
INSERT INTO PERSON VALUES('2222','john','adam','22/07/1977','Dammam','king fahad street','33444') ;
INSERT INTO PERSON VALUES('2233','elin','barker','03/08/1978','Alriyadh','dhahran street','66122') ;
INSERT INTO PERSON VALUES('2244','susan','jue','21/05/1979','Jeddah','dhahran street','66133') ;
INSERT INTO PERSON VALUES('2255','abdullah','rabeah','03/02/1974','Alriyadh','algameah street','99111') ;
INSERT INTO PERSON VALUES('2266','salih','ahmad','13/09/1977','Jeddah','aldayree alghrbee street','44433') ;
INSERT INTO PERSON VALUES('2277','mobark','ali','23/06/1972','Alriyadh','khaled bin alwaled','22322') ;
INSERT INTO PERSON VALUES('2288','idward','malkulm','11/08/1975','Alriyadh','algameah street','99122') ;
INSERT INTO PERSON VALUES('2299','simon','biker','16/08/1971','Dammam','algameah street','99188') ;
INSERT INTO PERSON VALUES('2200','hend','mohamad','25/09/1973','Alriyadh','algameah street','99124') ;
INSERT INTO PERSON VALUES('3311','tamer','ahmab','24/05/1978','Alriyadh','salman alfarsy street','55588') ;
INSERT INTO PERSON VALUES('3322','hasan','ahmad','16/08/1978','Jeddah','salman alfarsy street','55544') ;
INSERT INTO PERSON VALUES('3333','ibrahim','ahmad','16/05/1974','Alriyadh','salman alfarsy street','55599') ;
INSERT INTO PERSON VALUES('3344','ali','mohamad','16/08/1976','Alriyadh','dayree ganuby street','55221') ;
INSERT INTO PERSON VALUES('3355','samir','shami','16/08/1980','Dammam','dayree ganuby street','55324') ;
INSERT INTO PERSON VALUES('3366','saaed','yaser','08/07/1981','Dammam','king abdullaziz street','33664') ;
INSERT INTO PERSON VALUES('3377','fahd','sqer','16/05/1982','Alriyadh','king abdullaziz street','33554') ;
INSERT INTO PERSON VALUES('3388','majed','hamed','16/08/1984','Jeddah','salman alfarsy street','55599') ;
INSERT INTO PERSON VALUES('3399','morad','iyad','16/09/1986','Alriyadh','aldayree alghrbee street','44588') ;
INSERT INTO PERSON VALUES('3300','walid','ahmad','14/05/1986','Alriyadh','aldayree alghrbee street','44566') ;
INSERT INTO PERSON VALUES('4411','haitham','ali','13/07/1982','Jeddah','khaled bin alwaled','22355') ;
INSERT INTO PERSON VALUES('4422','husam','bader','13/05/1987','Alriyadh','olya street','33441') ;
INSERT INTO PERSON VALUES('4433','salim','khaled','12/07/1984','Alriyadh','aishah street','33745') ;
INSERT INTO PERSON VALUES('4444','sami','iqbal','16/08/1981','Jeddah','olya street','33441') ;
INSERT INTO PERSON VALUES('4455','sultan','mohamad','19/08/1988','Dammam','aishah street','33711') ;
INSERT INTO PERSON VALUES('4466','mohanad','aied','25/12/1987','Alriyadh','khaled bin alwaled','22322') ;
INSERT INTO PERSON VALUES('4477','fahad','rashed','09/10/1979','Alriyadh','aljahedh street','88222');
INSERT INTO PERSON VALUES('4488','norah','salim','12/04/1985','Dammam','altahlya','33666') ;
INSERT INTO PERSON VALUES('4499','morad','ali','13/09/1987','Alriyadh','khaled bin alwaled','22333') ;
INSERT INTO PERSON VALUES('4400','bander','thamer','14/05/1989','Jeddah','jesr mualaq street','54422') ;
INSERT INTO PERSON VALUES('5511','samer','majed','25/04/1969','Alriyadh','jesr mualaq street','54456') ;
INSERT INTO PERSON VALUES('5522','tamer','ahmad','26/05/1967','Alriyadh','khryss','99885') ;
INSERT INTO PERSON VALUES('5533','khaled','ali','14/07/1974','Jeddah','khaled bin alwaled','22355') ;
INSERT INTO PERSON VALUES('5544','hamed','mohamad','15/02/1965','Alriyadh','khryss','99824') ;
INSERT INTO PERSON VALUES('5555','jamal','mobarak','13/07/1974','Alriyadh','jesr mualaq street','54332') ;
INSERT INTO PERSON VALUES('5566','turky','bader','16/07/1963','Dammam','jesr mualaq street','55662') ;
INSERT INTO PERSON VALUES('5577','mohamad','shahin','14/06/1977','Alriyadh','jesr mualaq street','55882') ;
INSERT INTO PERSON VALUES('5588','nader','ahmad','19/10/1974','Jeddah','salman alfarsy street','55599') ;
INSERT INTO PERSON VALUES('5599','hamed','ahmad','10/05/1981','Alriyadh','salman alfarsy street','55588') ;
INSERT INTO PERSON VALUES('5500','zyad','sultan','10/11/1982','Dammam','aishah street','33755') ;
INSERT INTO EMPLOYEE VALUES ('1111','04/07/2001');
INSERT INTO EMPLOYEE VALUES ('1122','24/11/2007');
INSERT INTO EMPLOYEE VALUES ('1133','06/08/2003');
INSERT INTO EMPLOYEE VALUES ('1144','11/09/2000');
INSERT INTO EMPLOYEE VALUES ('1155','04/01/2009');
INSERT INTO EMPLOYEE VALUES ('1166','12/03/2004');
INSERT INTO EMPLOYEE VALUES ('1177','23/08/2011');
INSERT INTO EMPLOYEE VALUES ('1188','02/07/2002');
INSERT INTO EMPLOYEE VALUES ('1199','19/05/2002');
INSERT INTO EMPLOYEE VALUES ('1100','08/05/2008');
INSERT INTO EMPLOYEE VALUES ('2211','04/07/2001');
INSERT INTO EMPLOYEE VALUES ('2222','24/11/2007');
INSERT INTO EMPLOYEE VALUES ('2233','06/08/2003');
INSERT INTO EMPLOYEE VALUES ('2244','11/09/2000');
INSERT INTO EMPLOYEE VALUES ('2255','04/01/2009');
INSERT INTO EMPLOYEE VALUES ('2266','12/03/2004');
INSERT INTO EMPLOYEE VALUES ('2277','23/08/2011');
INSERT INTO EMPLOYEE VALUES ('2288','02/07/2002');
INSERT INTO EMPLOYEE VALUES ('2299','19/05/2002');
INSERT INTO EMPLOYEE VALUES ('2200','08/05/2008');
INSERT INTO EMPLOYEE VALUES ('3311','04/07/2001');
INSERT INTO EMPLOYEE VALUES ('3322','24/11/2007');
INSERT INTO EMPLOYEE VALUES ('3333','06/08/2003');
INSERT INTO EMPLOYEE VALUES ('3344','11/09/2000');
INSERT INTO EMPLOYEE VALUES ('3355','04/01/2009');
INSERT INTO EMPLOYEE VALUES ('3366','12/03/2004');
INSERT INTO EMPLOYEE VALUES ('3377','23/08/2011');
INSERT INTO EMPLOYEE VALUES ('3388','02/07/2002');
INSERT INTO EMPLOYEE VALUES ('3399','19/05/2002');
INSERT INTO EMPLOYEE VALUES ('3300','08/05/2008');
INSERT INTO STAFF VALUES ('1111','RAY');
INSERT INTO STAFF VALUES ('1122','SECRETARY');
INSERT INTO STAFF VALUES ('1133','CLEANING');
INSERT INTO STAFF VALUES ('1144','MANAGER');
INSERT INTO STAFF VALUES ('1155','CLEANING AGENT');
INSERT INTO STAFF VALUES ('1166','SECURITY MAN');
INSERT INTO STAFF VALUES ('1177','PHARMACIST');
INSERT INTO STAFF VALUES ('1188','PHARMACIST');
INSERT INTO STAFF VALUES ('1199','SECURITY MAN');
INSERT INTO STAFF VALUES ('1100','MANAG');
INSERT INTO DOCTOR VALUES('2211','2220','Bone');
INSERT INTO DOCTOR VALUES('2222','2221','Children');
INSERT INTO DOCTOR VALUES('2233','2222','Heart');
INSERT INTO DOCTOR VALUES('2244','2223','Eyes');
INSERT INTO DOCTOR VALUES('2255','2224','Children');
INSERT INTO DOCTOR VALUES('2266','2225','Heart');
INSERT INTO DOCTOR VALUES('2277','2226','Children');
INSERT INTO DOCTOR VALUES('2288','2227','Bone');
INSERT INTO DOCTOR VALUES('2299','2228','Bone');
INSERT INTO DOCTOR VALUES('2200','2229','Eyes');
INSERT INTO CLINIC VALUES ('CLINIC OLE','1111');
INSERT INTO CLINIC VALUES ('CARDIOLOGY','1122');
INSERT INTO CLINIC VALUES ('NERVES','1133');
INSERT INTO CLINIC VALUES ('BONE','1144');
INSERT INTO CLINIC VALUES ('TEETH','1155');
INSERT INTO CLINIC VALUES ('EYES','1166');
INSERT INTO CLINIC VALUES ('Leather','1177');
INSERT INTO CLINIC VALUES ('Children','1188');
INSERT INTO CLINIC VALUES ('EarNoseThroat','2211');
INSERT INTO CLINIC VALUES ('ObstetricsGynecology','1100');
INSERT INTO CARE_CENTER VALUES ('CLINIC OLE','L111');
INSERT INTO CARE_CENTER VALUES ('CARDIOLOGY','L122');
INSERT INTO CARE_CENTER VALUES ('NERVES','L133');
INSERT INTO CARE_CENTER VALUES ('BONE','L111');
INSERT INTO CARE_CENTER VALUES ('TEETH','L155');
INSERT INTO CARE_CENTER VALUES ('EYES','L166');
INSERT INTO CARE_CENTER VALUES ('Leather','L188');
INSERT INTO CARE_CENTER VALUES ('Children','L188');
INSERT INTO CARE_CENTER VALUES ('EarNoseThroat','L111');
INSERT INTO CARE_CENTER VALUES ('ObstetricsGynecology','L155');
INSERT INTO NURSE VALUES ('3311','BACHELORS','CLINIC OLE','CLINIC OLE');
INSERT INTO NURSE VALUES ('3322','DIPIOMA','CARDIOLOGY','CARDIOLOGY');
INSERT INTO NURSE VALUES ('3333','DIPIOMA','NERVES','NERVES');
INSERT INTO NURSE VALUES ('3344','DIPIOMA','BONE','BONE');
INSERT INTO NURSE VALUES ('3355','BACHELORS','TEETH','TEETH');
INSERT INTO NURSE VALUES ('3366','DIPIOMA','EYES','EYES');
INSERT INTO NURSE VALUES ('3377','BACHELORS','Leather','Leather');
INSERT INTO NURSE VALUES ('3388','DIPIOMA','Children','Children');
INSERT INTO NURSE VALUES ('3399','BACHELORS','EarNoseThroat','EarNoseThroat');
INSERT INTO NURSE VALUES ('3300','BACHELORS','ObstetricsGynecology','ObstetricsGynecology');
INSERT INTO BED_ROOM VALUES('0000','11','CLINIC OLE');
INSERT INTO BED_ROOM VALUES('0011','12','CARDIOLOGY');
INSERT INTO BED_ROOM VALUES('0022','13','NERVES');
INSERT INTO BED_ROOM VALUES('0033','14','BONE');
INSERT INTO BED_ROOM VALUES('0044','15','TEETH');
INSERT INTO BED_ROOM VALUES('0055','16','EYES');
INSERT INTO BED_ROOM VALUES('0066','17','Leather');
INSERT INTO BED_ROOM VALUES('0077','18','Children');
INSERT INTO BED_ROOM VALUES('0088','19','EarNoseThroat');
INSERT INTO BED_ROOM VALUES('0099','20','ObstetricsGynecology');
INSERT INTO PATIENT VALUES('4411','2211','1112');
INSERT INTO PATIENT VALUES('4422','2222','1113');
INSERT INTO PATIENT VALUES('4433','2233','1114');
INSERT INTO PATIENT VALUES('4444','2244','1115');
INSERT INTO PATIENT VALUES('4455','2255','1116');
INSERT INTO PATIENT VALUES('4466','2266','1117');
INSERT INTO PATIENT VALUES('4477','2277','1118');
INSERT INTO PATIENT VALUES('4488','2288','1119');
INSERT INTO PATIENT VALUES('4499','2299','1120');
INSERT INTO PATIENT VALUES('4400','2200','1121');
INSERT INTO PATIENT VALUES('5511','2211','1112');
INSERT INTO PATIENT VALUES('5522','2222','1113');
INSERT INTO PATIENT VALUES('5533','2233','1114');
INSERT INTO PATIENT VALUES('5544','2244','1115');
INSERT INTO PATIENT VALUES('5555','2255','1116');
INSERT INTO PATIENT VALUES('5566','2266','1117');
INSERT INTO PATIENT VALUES('5577','2277','1118');
INSERT INTO PATIENT VALUES('5588','2288','1119');
INSERT INTO PATIENT VALUES('5599','2299','1120');
INSERT INTO PATIENT VALUES('5500','2200','1121');
INSERT INTO RESIDENT VALUES('4411','0000','11');
INSERT INTO RESIDENT VALUES('4422','0011','12');
INSERT INTO RESIDENT VALUES('4433','0022','13');
INSERT INTO RESIDENT VALUES('4444','0033','14');
INSERT INTO RESIDENT VALUES('4455','0044','15');
INSERT INTO RESIDENT VALUES('4466','0055','16');
INSERT INTO RESIDENT VALUES('4477','0066','17');
INSERT INTO RESIDENT VALUES('4488','0077','18');
INSERT INTO RESIDENT VALUES('4499','0088','19');
INSERT INTO RESIDENT VALUES('4400','0099','20');
INSERT INTO OUT_PATIENT VALUES('5511','12/05/2011','CLINIC OLE');
INSERT INTO OUT_PATIENT VALUES('5522','02/06/2011','CARDIOLOGY');
INSERT INTO OUT_PATIENT VALUES('5533','27/04/2011','BONE');
INSERT INTO OUT_PATIENT VALUES('5544','11/02/2011','TEETH');
INSERT INTO OUT_PATIENT VALUES('5555','11/02/2011','EYES');
INSERT INTO OUT_PATIENT VALUES('5566','11/02/2011','Leather');
INSERT INTO OUT_PATIENT VALUES('5577','11/02/2011','Children');
INSERT INTO OUT_PATIENT VALUES('5588','11/02/2011','EarNoseThroat');
INSERT INTO OUT_PATIENT VALUES('5599','11/02/2011','ObstetricsGynecology');
INSERT INTO OUT_PATIENT VALUES('5500','11/02/2011','Leather');
INSERT INTO PER_PHONE VALUES('1111','1111110');
INSERT INTO PER_PHONE VALUES('1122','1111111');
INSERT INTO PER_PHONE VALUES('1133','1111112');
INSERT INTO PER_PHONE VALUES('1144','1111113');
INSERT INTO PER_PHONE VALUES('1155','1111114');
INSERT INTO PER_PHONE VALUES('1166','1111115');
INSERT INTO PER_PHONE VALUES('1177','1111116');
INSERT INTO PER_PHONE VALUES('1188','1111117');
INSERT INTO PER_PHONE VALUES('1199','1111118');
INSERT INTO PER_PHONE VALUES('1100','1111119');
INSERT INTO PER_PHONE VALUES('2211','2222220') ;
INSERT INTO PER_PHONE VALUES('2222','2222221') ;
INSERT INTO PER_PHONE VALUES('2233','2222222') ;
INSERT INTO PER_PHONE VALUES('2244','2222223') ;
INSERT INTO PER_PHONE VALUES('2255','2222224') ;
INSERT INTO PER_PHONE VALUES('2266','2222225') ;
INSERT INTO PER_PHONE VALUES('2277','2222226') ;
INSERT INTO PER_PHONE VALUES('2288','2222227') ;
INSERT INTO PER_PHONE VALUES('2299','2222228') ;
INSERT INTO PER_PHONE VALUES('2200','2222229') ;
INSERT INTO PER_PHONE VALUES('3311','3333330') ;
INSERT INTO PER_PHONE VALUES('3322','3333331') ;
INSERT INTO PER_PHONE VALUES('3333','3333332') ;
INSERT INTO PER_PHONE VALUES('3344','3333333') ;
INSERT INTO PER_PHONE VALUES('3355','3333334') ;
INSERT INTO PER_PHONE VALUES('3366','3333335') ;
INSERT INTO PER_PHONE VALUES('3377','3333336') ;
INSERT INTO PER_PHONE VALUES('3388','3333337') ;
INSERT INTO PER_PHONE VALUES('3399','3333338') ;
INSERT INTO PER_PHONE VALUES('3300','3333339') ;
INSERT INTO PER_PHONE VALUES('4411','4444440') ;
INSERT INTO PER_PHONE VALUES('4422','4444441') ;
INSERT INTO PER_PHONE VALUES('4433','4444442') ;
INSERT INTO PER_PHONE VALUES('4444','4444443') ;
INSERT INTO PER_PHONE VALUES('4455','4444444') ;
INSERT INTO PER_PHONE VALUES('4466','4444445') ;
INSERT INTO PER_PHONE VALUES('4477','4444446') ;
INSERT INTO PER_PHONE VALUES('4488','4444447') ;
INSERT INTO PER_PHONE VALUES('4499','4444448') ;
INSERT INTO PER_PHONE VALUES('4400','4444449') ;
INSERT INTO PER_PHONE VALUES('5511','5555550') ;
INSERT INTO PER_PHONE VALUES('5522','5555551') ;
INSERT INTO PER_PHONE VALUES('5533','5555552') ;
INSERT INTO PER_PHONE VALUES('5544','5555553') ;
INSERT INTO PER_PHONE VALUES('5555','5555554') ;
INSERT INTO PER_PHONE VALUES('5566','5555555') ;
INSERT INTO PER_PHONE VALUES('5577','5555556') ;
INSERT INTO PER_PHONE VALUES('5588','5555557') ;
INSERT INTO PER_PHONE VALUES('5599','5555558') ;
INSERT INTO PER_PHONE VALUES('5500','5555559') ;
4.3- Data update/deletion commands:
List all the DML statements that are used to delete and update data into the DB tables
1-Deleting row of person have '1111110' phone number
DELETE FROM PER_PHONE
WHERE PER_PHO ='1111111';
2-Deleting row of employee have '2233' id
DELETE FROM Employee
WHERE id ='2233';
3- Deleing row of employee that have '4-7-2001' date hired
DELETE FROM EMPLOYEE
WHERE Date_Hired = '4-7-2001';
4- Deleing row of staff that have ' MANAGE ' job class
DELETE FROM STAFF
WHERE Jop_Class = 'MANAGER';
5- Deleing row of nurse that have ' 3322 ' id
DELETE FROM NURSE
WHERE ID='3322';
1) command update the city of person have ID '1122'
to make it 'Jeddah'.
UPDATE PERSON
SET CITY='JEDDAH'
WHERE ID='1122';
2) command update the Jop_Class of STAFF have ID '1122'
to make it 'RAY'.
UPDATE STAFF
SET Jop_Class='RAY'
WHERE ID='1122';
3) command update the Specialty of DOCTOR have Pager_Number '2223' to make it 'Bone'.
UPDATE DOCTOR
SET Specialty ='Bone'
WHERE Pager_Number ='2223';
4) command update the STREET_NAME of PERSON have ID '1177' to make it 'ALROUDH'.
UPDATE PERSON
SET STREET_NAME ='ALROUDH'
WHERE ID= '1177';
5) command update the Certificate of NURSE have Certificate 'BACHELORS' to make it 'DIPIOMA'.
UPDATE NURSE
SET Certificate ='DIPIOMA'
WHERE Certificate = 'BACHELORS';
4.4 - Data Retrieval Queriescommands:
List all the statements that are used to select data from the DB tables.
1: Simple selection for STAFF table.
select *
from STAFF;
2: Retrieve count from DOCTOR table using aggregate.
select COUNT (*)AS Number_Of_Doctors
from doctor;
3: Retrieve all the PERSON with the first character 'a' in their F_Namme.
select ALL f_name
from person
where f_name LIKE 'a%';
4: Retrieve all different Jop_Class names that available in the STAFF table.
select DISTINCT jop_class
fromstaff;
5: Retrieve from PER_PHONE table the per_phone with id: '1122'.
select perpho
from perphone
where id='1122';
NESTEAD :
1: retrieve name and city of person where they have Heart Specialty
SELECT F_NAME , City
FROM PERSON
WHERE Id IN ( SELECT ID
FROM DOCTOR
WHERE Specialty ='Heart');
2: retrieve F_name and city of person where they have Jop_Class ='PHARMACIST'
SELECT F_NAME , City
FROM PERSON
WHERE Id IN ( SELECT ID
FROM STAFF
WHERE Jop_Class ='PHARMACIST');
3: retrieve L_Name of person where they have DateCome ='12-05-2011'
SELECT L_NAME
FROM PERSON
WHERE Id IN ( SELECT Id
FROM OUT_PATIENT
WHERE DateCome ='12-05-2011');
4: retrieve L_Name & City of person where they have Certificate ='BACHELORS'
SELECT L_NAME,CITY
FROM PERSON
WHERE Id IN ( SELECT Id
FROM NURSE
WHERE Certificate ='BACHELORS');
5: retrieve Per_Pho of person where they have Id ='5511'
SELECT Per_Pho
FROM PER_PHONE
WHERE Id IN ( SELECT Id
FROM PERSON
WHERE Id ='5511' );
1-Selection number of person for each CITIES and having account greater than 1:
SELECT city, COUNT(*)
FROM PERSON
GROUP BY city
HAVING COUNT(*)>1;