Category : C Source Code
Archive   : SQLVER1.ZIP
Filename : VIEW.CMD

CREATE VIEW VIEW0(NAME,DUTY,WARDNAME)
AS SELECT SNAME,SDUTY,WNAME FROM
WARD,STAFF WHERE
STAFF.WCODE=WARD.WCODE AND
STAFF.HCODE=WARD.HCODE;


CREATE VIEW VIEW1(STAFFNAME,DUTY,WARDNAME,HOSPITALNAME) AS
SELECT SNAME,SDUTY,WNAME,HNAME
FROM WARD,STAFF,HOSPITAL WHERE
STAFF.WCODE =WARD.WCODE AND
STAFF.HCODE =WARD.HCODE AND
STAFF.HCODE =HOSPITAL.HCODE AND
HOSPITAL.HCODE=WARD.HCODE;

CREATE VIEW VIEW2(NAME,DUTY,WARDNAME,HOSPITALNAME) AS
SELECT SNAME,
SDUTY,
WNAME,
HNAME FROM
WARD,
STAFF,
HOSPITAL WHERE
STAFF.WCODE =WARD.WCODE AND
STAFF.HCODE =WARD.HCODE AND
STAFF.HCODE =HOSPITAL.HCODE AND
HOSPITAL.HCODE=WARD.HCODE AND
'Psychiatric' =WNAME;

CREATE VIEW VIEW3(LABNAME,LABADDRESS,HOSPITALNAME,HOSPITALADDRESS) AS
SELECT LNAME,
LADDRESS,
HNAME,
HADDRESS FROM
LAB,
HOSPITAL,
HLAB WHERE
HLAB.HCODE=HOSPITAL.HCODE AND
HLAB.LABNO=LAB.LABNO;

CREATE VIEW VIEW4(HOSPITALNAME,WARDNAME,HOSPITALADDRESS) AS
SELECT HNAME,
WNAME,
HOSPITAL.HADDRESS FROM
HOSPITAL,
WARD WHERE
WARD.HCODE=HOSPITAL.HCODE;

CREATE VIEW VIEW5(DOCTORNAME,SPECIALTY,HOSPITALNAME,HOSPITALADDRESS) AS
SELECT DOCNAME,
DOCSPLTY,
HOSPITAL.HNAME,
HOSPITAL.HADDRESS FROM
DOCTOR,
HOSPITAL WHERE
HOSPITAL.HCODE=DOCTOR.HCODE;

CREATE VIEW VIEW6
AS
SELECT DOCTORNAME,
STAFFNAME,
WARDNAME,
VIEW5.HOSPITALNAME
FROM
VIEW5,
VIEW1
WHERE
VIEW5.HOSPITALNAME=VIEW1.HOSPITALNAME;


EXIT;